diff options
Diffstat (limited to 'mysql-test')
29 files changed, 722 insertions, 81 deletions
diff --git a/mysql-test/main/func_json.result b/mysql-test/main/func_json.result index 13922ed95f1..dc855259a9b 100644 --- a/mysql-test/main/func_json.result +++ b/mysql-test/main/func_json.result @@ -942,6 +942,15 @@ def json_depnth 3 10 1 N 32897 0 63 json_length json_depnth 2 3 # +# MDEV-19670 json escaped unicode parse error +# +SELECT json_valid('{"value":"\\ud83d\\ude0a"}'); +json_valid('{"value":"\\ud83d\\ude0a"}') +1 +SELECT json_valid('{"test": "\\ud83d\\ude0b"}'); +json_valid('{"test": "\\ud83d\\ude0b"}') +1 +# # End of 10.3 tests # # diff --git a/mysql-test/main/func_json.test b/mysql-test/main/func_json.test index 55f3fbae9ef..3e695934ef2 100644 --- a/mysql-test/main/func_json.test +++ b/mysql-test/main/func_json.test @@ -562,6 +562,13 @@ SELECT --disable_metadata --echo # +--echo # MDEV-19670 json escaped unicode parse error +--echo # + +SELECT json_valid('{"value":"\\ud83d\\ude0a"}'); +SELECT json_valid('{"test": "\\ud83d\\ude0b"}'); + +--echo # --echo # End of 10.3 tests --echo # diff --git a/mysql-test/main/func_misc.result b/mysql-test/main/func_misc.result index 1d284e45545..f4645ed089d 100644 --- a/mysql-test/main/func_misc.result +++ b/mysql-test/main/func_misc.result @@ -1557,6 +1557,15 @@ numgtfmt DROP VIEW v1; DROP TABLE t1; # +# MDEV-20517 Assertion `!is_expensive()' failed in Item::value_depends_on_sql_mode_const_item +# +SELECT ( 1 LIKE GET_LOCK( 'foo', 0 ) ) - 2; +( 1 LIKE GET_LOCK( 'foo', 0 ) ) - 2 +-1 +SELECT RELEASE_LOCK('foo'); +RELEASE_LOCK('foo') +1 +# # End of 10.2 tests # # diff --git a/mysql-test/main/func_misc.test b/mysql-test/main/func_misc.test index a2805f0438b..ea76bce3f6e 100644 --- a/mysql-test/main/func_misc.test +++ b/mysql-test/main/func_misc.test @@ -1192,6 +1192,12 @@ SELECT * FROM v1 WHERE numgtfmt = NAME_CONST('wnumgtfmt',_utf8'QEDITIONS' COLLA DROP VIEW v1; DROP TABLE t1; +--echo # +--echo # MDEV-20517 Assertion `!is_expensive()' failed in Item::value_depends_on_sql_mode_const_item +--echo # + +SELECT ( 1 LIKE GET_LOCK( 'foo', 0 ) ) - 2; +SELECT RELEASE_LOCK('foo'); --echo # --echo # End of 10.2 tests diff --git a/mysql-test/main/sp.result b/mysql-test/main/sp.result index 35e58f3ad62..f994f6f7fd6 100644 --- a/mysql-test/main/sp.result +++ b/mysql-test/main/sp.result @@ -7636,7 +7636,7 @@ SELECT 1; 1 1 DROP PROCEDURE sp; -CREATE PROCEDURE sp() SHOW USER_STATISTICS; +CREATE PROCEDURE sp() SET STATEMENT SQL_SELECT_LIMIT=0 FOR SHOW USER_STATISTICS; CALL sp; User Total_connections Concurrent_connections Connected_time Busy_time Cpu_time Bytes_received Bytes_sent Binlog_bytes_written Rows_read Rows_sent Rows_deleted Rows_inserted Rows_updated Select_commands Update_commands Other_commands Commit_transactions Rollback_transactions Denied_connections Lost_connections Access_denied Empty_queries Total_ssl_connections Max_statement_time_exceeded SELECT 1; diff --git a/mysql-test/main/sp.test b/mysql-test/main/sp.test index 9aec4627c2c..7d1e8e2a8f9 100644 --- a/mysql-test/main/sp.test +++ b/mysql-test/main/sp.test @@ -9070,7 +9070,7 @@ CREATE PROCEDURE sp() ALTER TABLE non_existing_table OPTIMIZE PARTITION p0; CALL sp; SELECT 1; DROP PROCEDURE sp; -CREATE PROCEDURE sp() SHOW USER_STATISTICS; +CREATE PROCEDURE sp() SET STATEMENT SQL_SELECT_LIMIT=0 FOR SHOW USER_STATISTICS; CALL sp; SELECT 1; DROP PROCEDURE sp; diff --git a/mysql-test/suite/galera/disabled.def b/mysql-test/suite/galera/disabled.def index 1ba6bfe3077..7951b4511ac 100644 --- a/mysql-test/suite/galera/disabled.def +++ b/mysql-test/suite/galera/disabled.def @@ -21,7 +21,6 @@ galera_as_master_gtid_change_master : Requires MySQL GTID galera_as_slave_gtid_replicate_do_db_cc : Requires MySQL GTID galera_as_slave_preordered : wsrep-preordered feature not merged to MariaDB galera_as_slave_replication_bundle : MDEV-15785 OPTION_GTID_BEGIN is set in Gtid_log_event::do_apply_event() -galera_autoinc_sst_mariabackup : MDEV-19926 Galera SST tests fail galera_bf_abort_group_commit : MDEV-18282 Galera test failure on galera.galera_bf_abort_group_commit galera_binlog_rows_query_log_events: MariaDB does not support binlog_rows_query_log_events galera_binlog_stmt_autoinc: MDEV-19959 Galera test failure on galera_binlog_stmt_autoinc @@ -35,15 +34,10 @@ galera_kill_largechanges : MDEV-18179 Galera test failure on galera.galera_kill_ galera_kill_nochanges : MDEV-18280 Galera test failure on galera_split_brain and galera_kill_nochanges galera_many_tables_nopk : MDEV-18182 Galera test failure on galera.galera_many_tables_nopk galera_migrate : MariaDB does not support START SLAVE USER -galera_pc_ignore_sb : MDEV-15811/MDEV-17357 Test failure galera_split_brain : MDEV-18280 Galera test failure on galera_split_brain and galera_kill_nochanges -galera_ssl_upgrade : MDEV-13549 Galera test failures -galera_sst_mysqldump_with_key : MDEV-16890 Galera test failure -galera_var_node_address : MDEV-17151 Galera test failure -galera_var_notify_cmd : MDEV-13549 Galera test failures +galera_ssl_upgrade : MDEV-19950 Galera test failure on galera_ssl_upgrade galera_var_reject_queries : assertion in inline_mysql_socket_send galera_var_retry_autocommit: MDEV-18181 Galera test failure on galera.galera_var_retry_autocommit -galera_sst_mariabackup_encrypt_with_key : MDEV-19926 Galera SST tests fail galera_var_node_address : MDEV-20485 Galera test failure on galera.galera_var_node_address galera_wan : MDEV-17259 Test failure on galera.galera_wan mysql-wsrep#198 : MDEV-18935 Galera test mysql-wsrep#198 sporaric assertion transaction.cpp:362: int wsrep::transaction::before_commit(): Assertion `state() == s_executing || state() == s_committing || state() == s_must_abort || state() == s_replaying' failed. diff --git a/mysql-test/suite/galera/r/galera_events2.result b/mysql-test/suite/galera/r/galera_events2.result new file mode 100644 index 00000000000..fa33e75ff57 --- /dev/null +++ b/mysql-test/suite/galera/r/galera_events2.result @@ -0,0 +1,122 @@ +connection node_1; +connection node_2; +connection node_1; +CREATE TABLE event_table(a int) engine=innodb; +CREATE EVENT event_2 ON SCHEDULE EVERY 1 SECOND +ENDS NOW() + INTERVAL 6 SECOND +ON COMPLETION NOT PRESERVE +DO +INSERT INTO event_table VALUES (1); +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +# node_1 event should be there +SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='event_2'; +EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_DEFINITION EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT +def test event_2 root@localhost SQL INSERT INTO event_table VALUES (1) RECURRING NULL 1 SECOND ENABLED NOT PRESERVE +connection node_2; +# node_2 event should be there +SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='event_2'; +EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_DEFINITION EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT +def test event_2 root@localhost SQL INSERT INTO event_table VALUES (1) RECURRING NULL 1 SECOND SLAVESIDE_DISABLED NOT PRESERVE +connection node_1; +SET GLOBAL event_scheduler=ON; +SHOW VARIABLES LIKE 'event_scheduler'; +Variable_name Value +event_scheduler ON +# node_1 event should be removed +SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='event_2'; +EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_DEFINITION EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT +connection node_2; +# node_2 event should be removed +SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='event_2'; +EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_DEFINITION EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT +connection node_1; +SET GLOBAL event_scheduler=OFF; +DROP TABLE event_table; +connection node_1; +CREATE DATABASE IF NOT EXISTS events_test; +use events_test; +CREATE USER ev_test@localhost; +GRANT ALL ON events_test.* to ev_test@localhost; +connect ev_con1,localhost,ev_test,,events_test; +CREATE EVENT one_event ON SCHEDULE EVERY 10 SECOND DO SELECT 123; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +SHOW EVENTS; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation +events_test one_event ev_test@localhost SYSTEM RECURRING NULL 10 # # NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci +SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS ORDER BY EVENT_SCHEMA, EVENT_NAME; +EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_DEFINITION EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT +def events_test one_event ev_test@localhost SQL SELECT 123 RECURRING NULL 10 SECOND ENABLED NOT PRESERVE +ALTER EVENT one_event ON SCHEDULE EVERY 10 SECOND; +"The definer should be ev_test@localhost" +SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='one_event'; +EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_DEFINITION EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT +def events_test one_event ev_test@localhost SQL SELECT 123 RECURRING NULL 10 SECOND ENABLED NOT PRESERVE +connection node_2; +use events_test; +"The definer should be ev_test@localhost" +SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='one_event'; +EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_DEFINITION EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT +def events_test one_event ev_test@localhost SQL SELECT 123 RECURRING NULL 10 SECOND SLAVESIDE_DISABLED NOT PRESERVE +connection node_1; +disconnect ev_con1; +use test; +DROP EVENT events_test.one_event; +DROP USER ev_test@localhost; +DROP DATABASE events_test; +connection node_1; +use test; +CREATE EVENT one_event ON SCHEDULE EVERY 10 SECOND DO SELECT 123; +Warnings: +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. +# node_1 Event should be enabled +SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='one_event'; +EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_DEFINITION EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT +def test one_event root@localhost SQL SELECT 123 RECURRING NULL 10 SECOND ENABLED NOT PRESERVE +connection node_2; +use test; +# node_2 Event should be SERVERSIDE_DISABLED +SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='one_event'; +EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_DEFINITION EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT +def test one_event root@localhost SQL SELECT 123 RECURRING NULL 10 SECOND SLAVESIDE_DISABLED NOT PRESERVE +CREATE TABLE t1 (f1 INTEGER PRIMARY KEY, f2 CHAR(1)) engine=InnoDB; +INSERT INTO t1 VALUES (1, 'a'), (2, 'a'), (3, 'a'); +SELECT * FROM t1; +f1 f2 +1 a +2 a +3 a +Shutting down server ... +connection node_1; +UPDATE t1 SET f2 = 'b' WHERE f1 > 1; +UPDATE t1 SET f2 = 'c' WHERE f1 > 2; +SELECT * FROM t1; +f1 f2 +1 a +2 b +3 c +connection node_2; +# Force SST from node_1 to node_2 +Starting server ... +SELECT * FROM t1; +f1 f2 +1 a +2 b +3 c +# node_2 Event should be SERVERSIDE_DISABLED +SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='one_event'; +EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_DEFINITION EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT +def test one_event root@localhost SQL SELECT 123 RECURRING NULL 10 SECOND SLAVESIDE_DISABLED NOT PRESERVE +connection node_1; +SELECT * FROM t1; +f1 f2 +1 a +2 b +3 c +# node_1 Event should be ENABLED +SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='one_event'; +EVENT_CATALOG EVENT_SCHEMA EVENT_NAME DEFINER EVENT_BODY EVENT_DEFINITION EVENT_TYPE EXECUTE_AT INTERVAL_VALUE INTERVAL_FIELD STATUS ON_COMPLETION EVENT_COMMENT +def test one_event root@localhost SQL SELECT 123 RECURRING NULL 10 SECOND ENABLED NOT PRESERVE +DROP TABLE t1; +DROP EVENT one_event; diff --git a/mysql-test/suite/galera/r/galera_var_node_address.result b/mysql-test/suite/galera/r/galera_var_node_address.result index cf45861c8ad..6b91a9f3ea3 100644 --- a/mysql-test/suite/galera/r/galera_var_node_address.result +++ b/mysql-test/suite/galera/r/galera_var_node_address.result @@ -1,19 +1,22 @@ connection node_2; connection node_1; -call mtr.add_suppression("WSREP: Stray state UUID msg: .* current group state WAIT_STATE_UUID .*"); -call mtr.add_suppression("WSREP: Protocol violation. JOIN message sender .* is not in state transfer (.*). Message ignored."); -call mtr.add_suppression("WSREP: Sending JOIN failed: -[0-9]+ (Transport endpoint is not connected). Will retry in new primary component."); -SELECT VARIABLE_VALUE = 4 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_size'; -VARIABLE_VALUE = 4 -1 +call mtr.add_suppression("WSREP: Stray state UUID msg: .*"); +call mtr.add_suppression("WSREP: Protocol violation. JOIN message sender .*"); +call mtr.add_suppression("WSREP: Sending JOIN failed: .*"); +flush tables; +SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_size'; +VARIABLE_VALUE +4 connection node_1; CREATE TABLE t1 (f1 INTEGER) ENGINE=INNODB; connection node_2; +set global wsrep_sync_wait=15; INSERT INTO t1 VALUES (1); connect node_3, 127.0.0.1, root, , test, $NODE_MYPORT_3; connection node_3; -SELECT COUNT(*) = 1 FROM t1; -COUNT(*) = 1 +set global wsrep_sync_wait=15; +SELECT COUNT(*) FROM t1; +COUNT(*) 1 connection node_1; DROP TABLE t1; diff --git a/mysql-test/suite/galera/t/MW-328B.test b/mysql-test/suite/galera/t/MW-328B.test index 11969dd0b47..04503ce31e3 100644 --- a/mysql-test/suite/galera/t/MW-328B.test +++ b/mysql-test/suite/galera/t/MW-328B.test @@ -8,6 +8,7 @@ # --source include/galera_cluster.inc +--source include/big_test.inc --source suite/galera/t/MW-328-header.inc --connection node_2 diff --git a/mysql-test/suite/galera/t/galera_events2.test b/mysql-test/suite/galera/t/galera_events2.test new file mode 100644 index 00000000000..54b90386851 --- /dev/null +++ b/mysql-test/suite/galera/t/galera_events2.test @@ -0,0 +1,144 @@ +--source include/galera_cluster.inc + +# Save original auto_increment_offset values. +--let $node_1=node_1 +--let $node_2=node_2 +--source include/auto_increment_offset_save.inc + +# +# Test case 1: "ONE TIME" events should be dropped on slave nodes after expiring on master (event creator node) +# + +--connection node_1 + +CREATE TABLE event_table(a int) engine=innodb; + +CREATE EVENT event_2 ON SCHEDULE EVERY 1 SECOND +ENDS NOW() + INTERVAL 6 SECOND +ON COMPLETION NOT PRESERVE +DO + INSERT INTO event_table VALUES (1); + +--echo # node_1 event should be there +SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='event_2'; + +--connection node_2 +--echo # node_2 event should be there +SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='event_2'; + +--connection node_1 +SET GLOBAL event_scheduler=ON; +SHOW VARIABLES LIKE 'event_scheduler'; + +# Let event_2 reach the end of its execution interval +let $wait_condition=select count(*) = 0 from information_schema.events where event_name='event_2'; +--source include/wait_condition.inc + +--echo # node_1 event should be removed +SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='event_2'; + +--connection node_2 +--echo # node_2 event should be removed +SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='event_2'; + +--connection node_1 +SET GLOBAL event_scheduler=OFF; +DROP TABLE event_table; + +# +# Test case 2: After doing ALTER EVENT, slave nodes should have same definer as master +# + +--connection node_1 + +CREATE DATABASE IF NOT EXISTS events_test; +use events_test; +CREATE USER ev_test@localhost; +GRANT ALL ON events_test.* to ev_test@localhost; +connect (ev_con1,localhost,ev_test,,events_test); + +CREATE EVENT one_event ON SCHEDULE EVERY 10 SECOND DO SELECT 123; +--replace_column 8 # 9 # +SHOW EVENTS; +SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS ORDER BY EVENT_SCHEMA, EVENT_NAME; +ALTER EVENT one_event ON SCHEDULE EVERY 10 SECOND; +--echo "The definer should be ev_test@localhost" +SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='one_event'; + +--connection node_2 +use events_test; +--echo "The definer should be ev_test@localhost" +SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='one_event'; + +--connection node_1 +--disconnect ev_con1 +use test; +DROP EVENT events_test.one_event; +DROP USER ev_test@localhost; +DROP DATABASE events_test; + +# +# Test case 3: After SST from master node (the one where event is ENABLED) , slave event status should be 'SLAVESIDE_DISABLED' +# + +--connection node_1 +use test; +CREATE EVENT one_event ON SCHEDULE EVERY 10 SECOND DO SELECT 123; + +--echo # node_1 Event should be enabled +SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='one_event'; + +--connection node_2 +use test; +--echo # node_2 Event should be SERVERSIDE_DISABLED +SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='one_event'; + +CREATE TABLE t1 (f1 INTEGER PRIMARY KEY, f2 CHAR(1)) engine=InnoDB; +INSERT INTO t1 VALUES (1, 'a'), (2, 'a'), (3, 'a'); + +SELECT * FROM t1; + +# Initiate normal shutdown on the node 2 and wait until shutdown has been completed: + +--echo Shutting down server ... +--source include/shutdown_mysqld.inc + +--connection node_1 + +--let $wait_condition = SELECT VARIABLE_VALUE = 1 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_size' +--source include/wait_condition.inc + +UPDATE t1 SET f2 = 'b' WHERE f1 > 1; +UPDATE t1 SET f2 = 'c' WHERE f1 > 2; + +SELECT * FROM t1; + +--connection node_2 + +# Remove the "grastate.dat" file (to initiate new SST) and restart node 2 + +--remove_file $MYSQLTEST_VARDIR/mysqld.2/data/grastate.dat + +--echo # Force SST from node_1 to node_2 +--let $start_mysqld_params= +--echo Starting server ... +--source include/start_mysqld.inc + +--let $wait_condition = SELECT VARIABLE_VALUE = 2 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_size' +--source include/wait_condition.inc + +# Sanity check (node 2 is running now and can perform SQL operators): + +SELECT * FROM t1; +--echo # node_2 Event should be SERVERSIDE_DISABLED +SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='one_event'; + +--connection node_1 +SELECT * FROM t1; +--echo # node_1 Event should be ENABLED +SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_BODY, EVENT_DEFINITION, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STATUS,ON_COMPLETION, EVENT_COMMENT FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='one_event'; + +DROP TABLE t1; +DROP EVENT one_event; + +--source include/auto_increment_offset_restore.inc diff --git a/mysql-test/suite/galera/t/galera_var_node_address.test b/mysql-test/suite/galera/t/galera_var_node_address.test index b50265be5ae..99cb30e7b91 100644 --- a/mysql-test/suite/galera/t/galera_var_node_address.test +++ b/mysql-test/suite/galera/t/galera_var_node_address.test @@ -6,27 +6,27 @@ --source include/galera_cluster.inc --source include/have_innodb.inc -call mtr.add_suppression("WSREP: Stray state UUID msg: .* current group state WAIT_STATE_UUID .*"); -call mtr.add_suppression("WSREP: Protocol violation. JOIN message sender .* is not in state transfer (.*). Message ignored."); -call mtr.add_suppression("WSREP: Sending JOIN failed: -[0-9]+ (Transport endpoint is not connected). Will retry in new primary component."); +call mtr.add_suppression("WSREP: Stray state UUID msg: .*"); +call mtr.add_suppression("WSREP: Protocol violation. JOIN message sender .*"); +call mtr.add_suppression("WSREP: Sending JOIN failed: .*"); +flush tables; -SELECT VARIABLE_VALUE = 4 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_size'; +--let $wait_condition = SELECT VARIABLE_VALUE = 4 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_size'; +--source include/wait_condition.inc + +SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_size'; --connection node_1 CREATE TABLE t1 (f1 INTEGER) ENGINE=INNODB; --connection node_2 -let $wait_condition= SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE 'test/t1'; ---source include/wait_condition.inc +set global wsrep_sync_wait=15; INSERT INTO t1 VALUES (1); --connect node_3, 127.0.0.1, root, , test, $NODE_MYPORT_3 --connection node_3 -let $wait_condition= SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE 'test/t1'; ---source include/wait_condition.inc -let $wait_condition= SELECT COUNT(*) = 1 FROM t1; ---source include/wait_condition.inc -SELECT COUNT(*) = 1 FROM t1; +set global wsrep_sync_wait=15; +SELECT COUNT(*) FROM t1; --connection node_1 DROP TABLE t1; diff --git a/mysql-test/suite/innodb/r/innodb-virtual-columns-debug.result b/mysql-test/suite/innodb/r/innodb-virtual-columns-debug.result index efe43945c69..df6bf83d24f 100644 --- a/mysql-test/suite/innodb/r/innodb-virtual-columns-debug.result +++ b/mysql-test/suite/innodb/r/innodb-virtual-columns-debug.result @@ -6,11 +6,11 @@ SET debug_sync= "ib_open_after_dict_open SIGNAL delete_open WAIT_FOR another_ope DELETE FROM t1; connection default; SET debug_sync= "now WAIT_FOR delete_open"; -SET debug_sync= "ib_open_after_dict_open SIGNAL another_open"; SELECT a FROM t1; a NULL NULL +SET debug_sync= "now SIGNAL another_open"; connection con1; disconnect con1; connection default; diff --git a/mysql-test/suite/innodb/r/temporary_table_optimization.result b/mysql-test/suite/innodb/r/temporary_table_optimization.result index 63c4f388bf7..c3325d86386 100644 --- a/mysql-test/suite/innodb/r/temporary_table_optimization.result +++ b/mysql-test/suite/innodb/r/temporary_table_optimization.result @@ -112,7 +112,7 @@ count(*) drop table t1; drop procedure populate_t1; create temporary table t1 (t1_i int, t1_f float) engine = innodb; -insert into t1 values (1, 1.1), (2, 2.2), (3, 2.2), (4, 4.4); +insert into t1 values (1, 1.1), (2, 2.5), (3, 2.5), (4, 4.4); explain select * from t1 where t1_i = 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where @@ -124,22 +124,22 @@ select * from t1 where t1_i = 1; t1_i t1_f 1 1.1 alter table t1 add unique index sec_index(t1_f); -ERROR 23000: Duplicate entry '2.2' for key 'sec_index' +ERROR 23000: Duplicate entry '2.5' for key 'sec_index' alter table t1 add index sec_index(t1_f); -explain select * from t1 where t1_f > 2.2; +explain select * from t1 where t1_f >= 2.5; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL sec_index NULL NULL NULL 4 Using where -select * from t1 where t1_f > 2.2; +select * from t1 where t1_f >= 2.5; t1_i t1_f -2 2.2 -3 2.2 +2 2.5 +3 2.5 4 4.4 alter table t1 add column (t1_c char(10)); select * from t1; t1_i t1_f t1_c 1 1.1 NULL -2 2.2 NULL -3 2.2 NULL +2 2.5 NULL +3 2.5 NULL 4 4.4 NULL insert into t1 values (5, 5.5, 'krunal'); alter table t1 drop column t1_f; @@ -150,7 +150,7 @@ t1 CREATE TEMPORARY TABLE `t1` ( `t1_c` char(10) DEFAULT NULL, UNIQUE KEY `pri_index` (`t1_i`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 -select * from t1 where t1_f > 2.2; +select * from t1 where t1_f >= 2.5; ERROR 42S22: Unknown column 't1_f' in 'where clause' alter table t1 add index sec_index2(t1_c), algorithm=inplace; ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY diff --git a/mysql-test/suite/innodb/t/innodb-virtual-columns-debug.test b/mysql-test/suite/innodb/t/innodb-virtual-columns-debug.test index 6de43dd80f8..d4947ca4d8b 100644 --- a/mysql-test/suite/innodb/t/innodb-virtual-columns-debug.test +++ b/mysql-test/suite/innodb/t/innodb-virtual-columns-debug.test @@ -15,8 +15,8 @@ SET debug_sync= "ib_open_after_dict_open SIGNAL delete_open WAIT_FOR another_ope DELETE FROM t1; --connection default SET debug_sync= "now WAIT_FOR delete_open"; -SET debug_sync= "ib_open_after_dict_open SIGNAL another_open"; SELECT a FROM t1; +SET debug_sync= "now SIGNAL another_open"; --connection con1 --reap diff --git a/mysql-test/suite/innodb/t/temporary_table_optimization.test b/mysql-test/suite/innodb/t/temporary_table_optimization.test index ae41c87839b..967965f9998 100644 --- a/mysql-test/suite/innodb/t/temporary_table_optimization.test +++ b/mysql-test/suite/innodb/t/temporary_table_optimization.test @@ -100,7 +100,7 @@ drop procedure populate_t1; # 3. Alter of temp-table. # create temporary table t1 (t1_i int, t1_f float) engine = innodb; -insert into t1 values (1, 1.1), (2, 2.2), (3, 2.2), (4, 4.4); +insert into t1 values (1, 1.1), (2, 2.5), (3, 2.5), (4, 4.4); # explain select * from t1 where t1_i = 1; alter table t1 add unique index pri_index(t1_i); @@ -110,8 +110,8 @@ select * from t1 where t1_i = 1; --error ER_DUP_ENTRY alter table t1 add unique index sec_index(t1_f); alter table t1 add index sec_index(t1_f); -explain select * from t1 where t1_f > 2.2; -select * from t1 where t1_f > 2.2; +explain select * from t1 where t1_f >= 2.5; +select * from t1 where t1_f >= 2.5; # alter table t1 add column (t1_c char(10)); select * from t1; @@ -120,7 +120,7 @@ insert into t1 values (5, 5.5, 'krunal'); alter table t1 drop column t1_f; show create table t1; --error ER_BAD_FIELD_ERROR -select * from t1 where t1_f > 2.2; +select * from t1 where t1_f >= 2.5; # --error ER_ALTER_OPERATION_NOT_SUPPORTED alter table t1 add index sec_index2(t1_c), algorithm=inplace; diff --git a/mysql-test/suite/parts/r/alter_data_directory_innodb.result b/mysql-test/suite/parts/r/alter_data_directory_innodb.result index 8a43588ea52..de2da0b9a05 100644 --- a/mysql-test/suite/parts/r/alter_data_directory_innodb.result +++ b/mysql-test/suite/parts/r/alter_data_directory_innodb.result @@ -57,6 +57,9 @@ ALTER TABLE t REORGANIZE PARTITION p1,p2 INTO ( PARTITION p1 DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/' ENGINE = INNODB, PARTITION p2 DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/' ENGINE = INNODB ); +Warnings: +Warning 1982 <DATA DIRECTORY> option ignored for InnoDB partition +Warning 1982 <DATA DIRECTORY> option ignored for InnoDB partition SHOW CREATE TABLE t; Table Create Table t CREATE TABLE `t` ( diff --git a/mysql-test/suite/parts/r/reorganize_partition_innodb.result b/mysql-test/suite/parts/r/reorganize_partition_innodb.result new file mode 100644 index 00000000000..a0a3344a39d --- /dev/null +++ b/mysql-test/suite/parts/r/reorganize_partition_innodb.result @@ -0,0 +1,160 @@ +# +# MDEV-15953 Alter InnoDB Partitioned Table Moves Files (which were originally not in the datadir) to the datadir +# +CREATE TABLE t ( +a INT NOT NULL +) ENGINE=INNODB +PARTITION BY HASH (a) ( +PARTITION p1 DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here/' ENGINE = INNODB, +PARTITION p2 DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here/' ENGINE = INNODB +); +INSERT INTO t VALUES (1); +SHOW CREATE TABLE t; +Table Create Table +t CREATE TABLE `t` ( + `a` int(11) NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 + PARTITION BY HASH (`a`) +(PARTITION `p1` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here' ENGINE = InnoDB, + PARTITION `p2` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here' ENGINE = InnoDB) +ALTER TABLE t ADD PRIMARY KEY pk(a), ALGORITHM=INPLACE; +Warnings: +Warning 1280 Name 'pk' ignored for PRIMARY key. +SHOW CREATE TABLE t; +Table Create Table +t CREATE TABLE `t` ( + `a` int(11) NOT NULL, + PRIMARY KEY (`a`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 + PARTITION BY HASH (`a`) +(PARTITION `p1` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here' ENGINE = InnoDB, + PARTITION `p2` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here' ENGINE = InnoDB) +ALTER TABLE t DROP PRIMARY KEY, ALGORITHM=COPY; +SHOW CREATE TABLE t; +Table Create Table +t CREATE TABLE `t` ( + `a` int(11) NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 + PARTITION BY HASH (`a`) +(PARTITION `p1` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here' ENGINE = InnoDB, + PARTITION `p2` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here' ENGINE = InnoDB) +SET @TMP = @@GLOBAL.INNODB_FILE_PER_TABLE; +SET GLOBAL INNODB_FILE_PER_TABLE=OFF; +ALTER TABLE t ADD PRIMARY KEY pk(a), ALGORITHM=INPLACE; +Warnings: +Warning 1280 Name 'pk' ignored for PRIMARY key. +SHOW CREATE TABLE t; +Table Create Table +t CREATE TABLE `t` ( + `a` int(11) NOT NULL, + PRIMARY KEY (`a`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 + PARTITION BY HASH (`a`) +(PARTITION `p1` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here' ENGINE = InnoDB, + PARTITION `p2` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here' ENGINE = InnoDB) +SET GLOBAL INNODB_FILE_PER_TABLE=@TMP; +ALTER TABLE t REORGANIZE PARTITION p1,p2 INTO ( +PARTITION p1 DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/' ENGINE = INNODB, +PARTITION p2 DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/' ENGINE = INNODB +); +Warnings: +Warning 1982 <DATA DIRECTORY> option ignored for InnoDB partition +Warning 1982 <DATA DIRECTORY> option ignored for InnoDB partition +SHOW CREATE TABLE t; +Table Create Table +t CREATE TABLE `t` ( + `a` int(11) NOT NULL, + PRIMARY KEY (`a`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 + PARTITION BY HASH (`a`) +(PARTITION `p1` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here' ENGINE = InnoDB, + PARTITION `p2` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here' ENGINE = InnoDB) +ALTER TABLE t REORGANIZE PARTITION p1,p2 INTO ( +PARTITION p1 DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/', +PARTITION p2 DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/' +); +Warnings: +Warning 1982 <DATA DIRECTORY> option ignored for InnoDB partition +Warning 1982 <DATA DIRECTORY> option ignored for InnoDB partition +SHOW CREATE TABLE t; +Table Create Table +t CREATE TABLE `t` ( + `a` int(11) NOT NULL, + PRIMARY KEY (`a`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 + PARTITION BY HASH (`a`) +(PARTITION `p1` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here' ENGINE = InnoDB, + PARTITION `p2` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here' ENGINE = InnoDB) +ALTER TABLE t REORGANIZE PARTITION p1,p2 INTO ( +PARTITION p1 INDEX DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/' ENGINE = INNODB, +PARTITION p2 INDEX DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/' ENGINE = INNODB +); +Warnings: +Warning 1982 <INDEX DIRECTORY> option ignored for InnoDB partition +Warning 1982 <INDEX DIRECTORY> option ignored for InnoDB partition +SHOW CREATE TABLE t; +Table Create Table +t CREATE TABLE `t` ( + `a` int(11) NOT NULL, + PRIMARY KEY (`a`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 + PARTITION BY HASH (`a`) +(PARTITION `p1` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here' ENGINE = InnoDB, + PARTITION `p2` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here' ENGINE = InnoDB) +DROP TABLE t; +SET @strict = @@innodb_strict_mode; +SET innodb_strict_mode=OFF; +CREATE TABLE t ( +a INT NOT NULL +) ENGINE=INNODB +PARTITION BY RANGE (a) +SUBPARTITION BY HASH (a) +SUBPARTITIONS 2 +( +PARTITION p1 VALUES LESS THAN (7) +DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here/' + INDEX DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here/' + ENGINE = INNODB, +PARTITION p2 VALUES LESS THAN MAXVALUE +DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here/' + INDEX DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here/' + ENGINE = INNODB +); +Warnings: +Warning 1618 <INDEX DIRECTORY> option ignored +Warning 1618 <INDEX DIRECTORY> option ignored +Warning 1618 <INDEX DIRECTORY> option ignored +Warning 1618 <INDEX DIRECTORY> option ignored +SET innodb_strict_mode=@strict; +ALTER TABLE t +REORGANIZE PARTITION p1,p2 INTO +( +PARTITION p1 VALUES LESS THAN (7) +DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/' + INDEX DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/' + ENGINE = INNODB, +PARTITION p2 VALUES LESS THAN MAXVALUE +DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/' + INDEX DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/' + ENGINE = INNODB +); +Warnings: +Warning 1982 <DATA DIRECTORY> option ignored for InnoDB partition +Warning 1982 <INDEX DIRECTORY> option ignored for InnoDB partition +Warning 1982 <DATA DIRECTORY> option ignored for InnoDB partition +Warning 1982 <INDEX DIRECTORY> option ignored for InnoDB partition +Warning 1982 <DATA DIRECTORY> option ignored for InnoDB partition +Warning 1982 <INDEX DIRECTORY> option ignored for InnoDB partition +Warning 1982 <DATA DIRECTORY> option ignored for InnoDB partition +Warning 1982 <INDEX DIRECTORY> option ignored for InnoDB partition +SHOW CREATE TABLE t; +Table Create Table +t CREATE TABLE `t` ( + `a` int(11) NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 + PARTITION BY RANGE (`a`) +SUBPARTITION BY HASH (`a`) +SUBPARTITIONS 2 +(PARTITION `p1` VALUES LESS THAN (7) DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/' ENGINE = InnoDB, + PARTITION `p2` VALUES LESS THAN MAXVALUE DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/' ENGINE = InnoDB) +DROP TABLE t; diff --git a/mysql-test/suite/parts/t/reorganize_partition_innodb.test b/mysql-test/suite/parts/t/reorganize_partition_innodb.test new file mode 100644 index 00000000000..db73650c54b --- /dev/null +++ b/mysql-test/suite/parts/t/reorganize_partition_innodb.test @@ -0,0 +1,103 @@ +--source include/have_innodb.inc +--source include/have_partition.inc + +--echo # +--echo # MDEV-15953 Alter InnoDB Partitioned Table Moves Files (which were originally not in the datadir) to the datadir +--echo # + +mkdir $MYSQLTEST_VARDIR/tmp/partitions_here; + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +eval CREATE TABLE t ( + a INT NOT NULL + ) ENGINE=INNODB +PARTITION BY HASH (a) ( + PARTITION p1 DATA DIRECTORY = '$MYSQLTEST_VARDIR/tmp/partitions_here/' ENGINE = INNODB, + PARTITION p2 DATA DIRECTORY = '$MYSQLTEST_VARDIR/tmp/partitions_here/' ENGINE = INNODB +); +INSERT INTO t VALUES (1); + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +SHOW CREATE TABLE t; +ALTER TABLE t ADD PRIMARY KEY pk(a), ALGORITHM=INPLACE; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +SHOW CREATE TABLE t; +ALTER TABLE t DROP PRIMARY KEY, ALGORITHM=COPY; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +SHOW CREATE TABLE t; +SET @TMP = @@GLOBAL.INNODB_FILE_PER_TABLE; +SET GLOBAL INNODB_FILE_PER_TABLE=OFF; +ALTER TABLE t ADD PRIMARY KEY pk(a), ALGORITHM=INPLACE; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +SHOW CREATE TABLE t; +SET GLOBAL INNODB_FILE_PER_TABLE=@TMP; + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +eval ALTER TABLE t REORGANIZE PARTITION p1,p2 INTO ( + PARTITION p1 DATA DIRECTORY = '$MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/' ENGINE = INNODB, + PARTITION p2 DATA DIRECTORY = '$MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/' ENGINE = INNODB +); +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +SHOW CREATE TABLE t; + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +eval ALTER TABLE t REORGANIZE PARTITION p1,p2 INTO ( + PARTITION p1 DATA DIRECTORY = '$MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/', + PARTITION p2 DATA DIRECTORY = '$MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/' +); +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +SHOW CREATE TABLE t; + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +eval ALTER TABLE t REORGANIZE PARTITION p1,p2 INTO ( + PARTITION p1 INDEX DIRECTORY = '$MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/' ENGINE = INNODB, + PARTITION p2 INDEX DIRECTORY = '$MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/' ENGINE = INNODB +); +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +SHOW CREATE TABLE t; + +DROP TABLE t; + +SET @strict = @@innodb_strict_mode; +SET innodb_strict_mode=OFF; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +eval CREATE TABLE t ( + a INT NOT NULL + ) ENGINE=INNODB +PARTITION BY RANGE (a) + SUBPARTITION BY HASH (a) + SUBPARTITIONS 2 + ( + PARTITION p1 VALUES LESS THAN (7) + DATA DIRECTORY = '$MYSQLTEST_VARDIR/tmp/partitions_here/' + INDEX DIRECTORY = '$MYSQLTEST_VARDIR/tmp/partitions_here/' + ENGINE = INNODB, + PARTITION p2 VALUES LESS THAN MAXVALUE + DATA DIRECTORY = '$MYSQLTEST_VARDIR/tmp/partitions_here/' + INDEX DIRECTORY = '$MYSQLTEST_VARDIR/tmp/partitions_here/' + ENGINE = INNODB + ); +SET innodb_strict_mode=@strict; + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +eval ALTER TABLE t +REORGANIZE PARTITION p1,p2 INTO +( + PARTITION p1 VALUES LESS THAN (7) + DATA DIRECTORY = '$MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/' + INDEX DIRECTORY = '$MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/' + ENGINE = INNODB, + PARTITION p2 VALUES LESS THAN MAXVALUE + DATA DIRECTORY = '$MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/' + INDEX DIRECTORY = '$MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/' + ENGINE = INNODB +); + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +SHOW CREATE TABLE t; + +DROP TABLE t; + +rmdir $MYSQLTEST_VARDIR/tmp/partitions_here/test; +rmdir $MYSQLTEST_VARDIR/tmp/partitions_here; + diff --git a/mysql-test/suite/sys_vars/r/delayed_insert_limit_func.result b/mysql-test/suite/sys_vars/r/delayed_insert_limit_func.result index 2e6b730f104..9362a213a07 100644 --- a/mysql-test/suite/sys_vars/r/delayed_insert_limit_func.result +++ b/mysql-test/suite/sys_vars/r/delayed_insert_limit_func.result @@ -52,14 +52,12 @@ INSERT DELAYED INTO t1 VALUES('39','1','1'); INSERT DELAYED INTO t1 VALUES('40','1','1'); INSERT DELAYED INTO t1 VALUES('41','1','1'); INSERT DELAYED INTO t1 VALUES('42','1','1'); -INSERT DELAYED INTO t1 VALUES('43','1','1');| +INSERT DELAYED INTO t1 VALUES('43','1','1'); connection con0; SELECT COUNT(*) FROM t1; connection default; ** Wait till con0 is blocked ** UNLOCK TABLES; -connection con1; -Asynchronous "reap" result connection con0; Asynchronous "reap" result The next result suffers from @@ -85,7 +83,6 @@ INSERT INTO t1 VALUES('5'); INSERT INTO t1 VALUES('6'); LOCK TABLE v1 READ; connection con1; -Asynchronous execute INSERT DELAYED INTO t1 VALUES('7'); INSERT DELAYED INTO t1 VALUES('8'); INSERT DELAYED INTO t1 VALUES('9'); @@ -101,23 +98,22 @@ INSERT DELAYED INTO t1 VALUES('18'); INSERT DELAYED INTO t1 VALUES('19'); INSERT DELAYED INTO t1 VALUES('20'); INSERT DELAYED INTO t1 VALUES('21'); -INSERT DELAYED INTO t1 VALUES('22');| +INSERT DELAYED INTO t1 VALUES('22'); connection con0; Asynchronous execute -SELECT COUNT(*) BETWEEN 6 AND 22 FROM t1; +SELECT COUNT(*) FROM t1; connection default; ** Wait till con0 is blocked ** UNLOCK TABLES; -connection con1; connection con0; Asynchronous "reap" result -COUNT(*) BETWEEN 6 AND 22 -1 +COUNT(*) +22 connection default; Checking if the delayed insert gives the same result afterwards -SELECT COUNT(*) BETWEEN 6 AND 22 FROM t1; -COUNT(*) BETWEEN 6 AND 22 -1 +SELECT COUNT(*) FROM t1; +COUNT(*) +22 connection default; DROP TABLE t1; DROP VIEW v1; diff --git a/mysql-test/suite/sys_vars/r/innodb_change_buffering_debug_basic.result b/mysql-test/suite/sys_vars/r/innodb_change_buffering_debug_basic.result index fc0078581fb..2cb3fc76e30 100644 --- a/mysql-test/suite/sys_vars/r/innodb_change_buffering_debug_basic.result +++ b/mysql-test/suite/sys_vars/r/innodb_change_buffering_debug_basic.result @@ -55,6 +55,12 @@ Warnings: Warning 1292 Truncated incorrect innodb_change_buffering_debug value: '-2' set global innodb_change_buffering_debug=1e1; ERROR 42000: Incorrect argument type to variable 'innodb_change_buffering_debug' +set global innodb_change_buffering_debug=2; +Warnings: +Warning 1292 Truncated incorrect innodb_change_buffering_debug value: '2' +select @@global.innodb_change_buffering_debug; +@@global.innodb_change_buffering_debug +1 SET @@global.innodb_change_buffering_debug = @start_global_value; SELECT @@global.innodb_change_buffering_debug; @@global.innodb_change_buffering_debug diff --git a/mysql-test/suite/sys_vars/r/sysvars_innodb.result b/mysql-test/suite/sys_vars/r/sysvars_innodb.result index 8ba71efdbd9..e379bae5167 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_innodb.result +++ b/mysql-test/suite/sys_vars/r/sysvars_innodb.result @@ -326,9 +326,9 @@ SESSION_VALUE NULL DEFAULT_VALUE 0 VARIABLE_SCOPE GLOBAL VARIABLE_TYPE INT UNSIGNED -VARIABLE_COMMENT Debug flags for InnoDB change buffering (0=none, 2=crash at merge) +VARIABLE_COMMENT Debug flags for InnoDB change buffering (0=none, 1=try to buffer) NUMERIC_MIN_VALUE 0 -NUMERIC_MAX_VALUE 2 +NUMERIC_MAX_VALUE 1 NUMERIC_BLOCK_SIZE 0 ENUM_VALUE_LIST NULL READ_ONLY NO diff --git a/mysql-test/suite/sys_vars/t/delayed_insert_limit_func.test b/mysql-test/suite/sys_vars/t/delayed_insert_limit_func.test index fe0c579acf4..6a95dda3e57 100644 --- a/mysql-test/suite/sys_vars/t/delayed_insert_limit_func.test +++ b/mysql-test/suite/sys_vars/t/delayed_insert_limit_func.test @@ -63,8 +63,6 @@ LOCK TABLE v1 READ; connection con1; -delimiter |; -send INSERT DELAYED INTO t1 VALUES('7','1','1'); INSERT DELAYED INTO t1 VALUES('8','1','1'); INSERT DELAYED INTO t1 VALUES('9','1','1'); @@ -101,8 +99,7 @@ INSERT DELAYED INTO t1 VALUES('39','1','1'); INSERT DELAYED INTO t1 VALUES('40','1','1'); INSERT DELAYED INTO t1 VALUES('41','1','1'); INSERT DELAYED INTO t1 VALUES('42','1','1'); -INSERT DELAYED INTO t1 VALUES('43','1','1');| -delimiter ;| +INSERT DELAYED INTO t1 VALUES('43','1','1'); connection con0; let $wait_condition= @@ -121,10 +118,6 @@ let $wait_condition= --source include/wait_condition.inc UNLOCK TABLES; -connection con1; ---echo Asynchronous "reap" result -reap; - connection con0; --echo Asynchronous "reap" result --echo The next result suffers from @@ -161,10 +154,6 @@ LOCK TABLE v1 READ; connection con1; ---echo Asynchronous execute -delimiter |; - -send INSERT DELAYED INTO t1 VALUES('7'); INSERT DELAYED INTO t1 VALUES('8'); INSERT DELAYED INTO t1 VALUES('9'); @@ -180,9 +169,7 @@ INSERT DELAYED INTO t1 VALUES('18'); INSERT DELAYED INTO t1 VALUES('19'); INSERT DELAYED INTO t1 VALUES('20'); INSERT DELAYED INTO t1 VALUES('21'); -INSERT DELAYED INTO t1 VALUES('22');| - -delimiter ;| +INSERT DELAYED INTO t1 VALUES('22'); connection con0; let $wait_condition= @@ -192,7 +179,7 @@ let $wait_condition= --echo Asynchronous execute # Due to performance and server behaveiour the test observes values between 6 and 22. # In any case the value must not be outside of that range. -let $my_select= SELECT COUNT(*) BETWEEN 6 AND 22 FROM t1; +let $my_select= SELECT COUNT(*) FROM t1; send; eval $my_select; @@ -204,9 +191,6 @@ let $wait_condition= --source include/wait_condition.inc UNLOCK TABLES; -connection con1; -reap; - connection con0; --echo Asynchronous "reap" result reap; diff --git a/mysql-test/suite/sys_vars/t/innodb_change_buffering_debug_basic.test b/mysql-test/suite/sys_vars/t/innodb_change_buffering_debug_basic.test index a6fc09f767e..70f8bee1523 100644 --- a/mysql-test/suite/sys_vars/t/innodb_change_buffering_debug_basic.test +++ b/mysql-test/suite/sys_vars/t/innodb_change_buffering_debug_basic.test @@ -48,9 +48,8 @@ set global innodb_change_buffering_debug='foo'; set global innodb_change_buffering_debug=-2; --error ER_WRONG_TYPE_FOR_VAR set global innodb_change_buffering_debug=1e1; -# The value 2 is supposed to kill the server if there are unmerged changes. -# Do not try to set the value to 2 or anything that can be clamped to 2. -#set global innodb_change_buffering_debug=2; +set global innodb_change_buffering_debug=2; +select @@global.innodb_change_buffering_debug; # # Cleanup diff --git a/mysql-test/suite/versioning/r/alter.result b/mysql-test/suite/versioning/r/alter.result index e380c207555..d7ebb4dd096 100644 --- a/mysql-test/suite/versioning/r/alter.result +++ b/mysql-test/suite/versioning/r/alter.result @@ -642,3 +642,45 @@ create or replace table t1 (f1 int) with system versioning; alter table t1 drop system versioning, add f2 int with system versioning; ERROR HY000: Table `t1` is not system-versioned drop table t1; +# MDEV-16490 It's possible to make a system versioned table without any versioning field +set @@system_versioning_alter_history=keep; +create or replace table t (a int) with system versioning engine=innodb; +alter table t change column a a int without system versioning; +ERROR HY000: Table `t` must have at least one versioned column +alter table t +change column a a int without system versioning, +add column b int with system versioning; +show create table t; +Table Create Table +t CREATE TABLE `t` ( + `a` int(11) DEFAULT NULL WITHOUT SYSTEM VERSIONING, + `b` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING +alter table t +change column a new_a int, +drop system versioning; +show create table t; +Table Create Table +t CREATE TABLE `t` ( + `new_a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +alter table t add system versioning; +alter table t change column new_a a int without system versioning; +show create table t; +Table Create Table +t CREATE TABLE `t` ( + `a` int(11) DEFAULT NULL WITHOUT SYSTEM VERSIONING, + `b` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING +alter table t +add column c int, +change column c c int without system versioning, +change column b b int without system versioning; +ERROR HY000: Table `t` must have at least one versioned column +alter table t +add column c int without system versioning, +change column c c int, +change column b b int without system versioning; +drop database test; +create database test; diff --git a/mysql-test/suite/versioning/r/create.result b/mysql-test/suite/versioning/r/create.result index 47d069fc4ef..747da5483ec 100644 --- a/mysql-test/suite/versioning/r/create.result +++ b/mysql-test/suite/versioning/r/create.result @@ -527,3 +527,13 @@ row_end datetime(6) generated always as row end, period for system_time(row_start, row_end) ) with system versioning; ERROR HY000: `row_start` must be of type TIMESTAMP(6) for system-versioned table `t` +# MDEV-16490 It's possible to make a system versioned table without any versioning field +create or replace table t1 (x int without system versioning) +with system versioning +select 1 as y; +create or replace table t1 (x int without system versioning) +with system versioning +select 1 as x; +ERROR HY000: Table `t1` must have at least one versioned column +drop database test; +create database test; diff --git a/mysql-test/suite/versioning/t/alter.test b/mysql-test/suite/versioning/t/alter.test index b7e623b2897..4cab4798777 100644 --- a/mysql-test/suite/versioning/t/alter.test +++ b/mysql-test/suite/versioning/t/alter.test @@ -543,3 +543,37 @@ alter table t1 drop system versioning, add f2 int with system versioning; drop table t1; --source suite/versioning/common_finish.inc +--echo # MDEV-16490 It's possible to make a system versioned table without any versioning field + +set @@system_versioning_alter_history=keep; +create or replace table t (a int) with system versioning engine=innodb; +--error ER_VERS_TABLE_MUST_HAVE_COLUMNS +alter table t change column a a int without system versioning; + +alter table t + change column a a int without system versioning, + add column b int with system versioning; +show create table t; + +alter table t + change column a new_a int, + drop system versioning; +show create table t; + +alter table t add system versioning; +alter table t change column new_a a int without system versioning; +show create table t; + +--error ER_VERS_TABLE_MUST_HAVE_COLUMNS +alter table t + add column c int, + change column c c int without system versioning, + change column b b int without system versioning; + +alter table t + add column c int without system versioning, + change column c c int, + change column b b int without system versioning; + +drop database test; +create database test; diff --git a/mysql-test/suite/versioning/t/create.test b/mysql-test/suite/versioning/t/create.test index 64858bbe0ec..c98c23f4c05 100644 --- a/mysql-test/suite/versioning/t/create.test +++ b/mysql-test/suite/versioning/t/create.test @@ -396,3 +396,14 @@ create table t ( ) with system versioning; --source suite/versioning/common_finish.inc +--echo # MDEV-16490 It's possible to make a system versioned table without any versioning field +create or replace table t1 (x int without system versioning) +with system versioning +select 1 as y; +--error ER_VERS_TABLE_MUST_HAVE_COLUMNS +create or replace table t1 (x int without system versioning) +with system versioning +select 1 as x; + +drop database test; +create database test; diff --git a/mysql-test/suite/wsrep/disabled.def b/mysql-test/suite/wsrep/disabled.def index 276ed34f747..af99868fbed 100644 --- a/mysql-test/suite/wsrep/disabled.def +++ b/mysql-test/suite/wsrep/disabled.def @@ -11,6 +11,4 @@ ############################################################################## foreign_key : Sporadic failure "WSREP has not yet prepared node for application use" -variables : MDEV-19746 Galera test failures because of wsrep_slave_threads identification wsrep.pool_of_threads : Sporadic failure "WSREP has not yet prepared node for application use" -wsrep.variables : Global wsrep_on manipulation causes debug asserts |