diff options
| author | Oleksandr Byelkin <sanja@mariadb.com> | 2022-02-01 20:33:04 +0100 |
|---|---|---|
| committer | Oleksandr Byelkin <sanja@mariadb.com> | 2022-02-01 20:33:04 +0100 |
| commit | cf63eecef44f189ce2d221612dee9dfc1885ba4e (patch) | |
| tree | 93b4e6645a1d371bd8012a0aa8e6e3a3d541b2a6 /mysql-test/suite | |
| parent | fb40a2fabf8d8cf765c83a0b8e609dd893c75ec3 (diff) | |
| parent | c04a203a10e282e1f33fd04d8a1b7ff0b076bce5 (diff) | |
| download | mariadb-git-cf63eecef44f189ce2d221612dee9dfc1885ba4e.tar.gz | |
Merge branch '10.4' into 10.5
Diffstat (limited to 'mysql-test/suite')
103 files changed, 2276 insertions, 572 deletions
diff --git a/mysql-test/suite/binlog/include/mysqlbinlog_row_engine.inc b/mysql-test/suite/binlog/include/mysqlbinlog_row_engine.inc index ce919550b51..874be1dac61 100644 --- a/mysql-test/suite/binlog/include/mysqlbinlog_row_engine.inc +++ b/mysql-test/suite/binlog/include/mysqlbinlog_row_engine.inc @@ -1921,3 +1921,28 @@ let $MYSQLD_DATADIR= `select @@datadir`; DROP TABLE t1; +--echo # +--echo # Beginning of 10.2 test +--echo # +--echo # MDEV-25460: Assertion `!is_set() || (m_status == DA_OK_BULK && +--echo # is_bulk_op())' failed in Diagnostics_area::set_ok_status in my_ok +--echo # from mysql_sql_stmt_prepare +--echo # + +CREATE TEMPORARY TABLE a (c INT) ENGINE=InnoDB; +CREATE TABLE b (c INT) ENGINE=InnoDB; + +--error ER_TEMP_TABLE_PREVENTS_SWITCH_OUT_OF_RBR +PREPARE s FROM 'SET STATEMENT binlog_format=ROW FOR SELECT * FROM b'; + +DROP TABLE b; +DROP TEMPORARY TABLE a; + +CREATE TEMPORARY TABLE t (c INT); +--error ER_TEMP_TABLE_PREVENTS_SWITCH_OUT_OF_RBR +PREPARE s FROM 'SET STATEMENT binlog_format=ROW FOR SELECT 1'; +DROP TEMPORARY TABLE t; + +--echo # +--echo # End of 10.2 test +--echo # diff --git a/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row_innodb.result b/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row_innodb.result index 5fce079ef24..a3741867208 100644 --- a/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row_innodb.result +++ b/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row_innodb.result @@ -6372,3 +6372,23 @@ ROLLBACK /* added by mysqlbinlog */; # Cleanup. # DROP TABLE t1; +# +# Beginning of 10.2 test +# +# MDEV-25460: Assertion `!is_set() || (m_status == DA_OK_BULK && +# is_bulk_op())' failed in Diagnostics_area::set_ok_status in my_ok +# from mysql_sql_stmt_prepare +# +CREATE TEMPORARY TABLE a (c INT) ENGINE=InnoDB; +CREATE TABLE b (c INT) ENGINE=InnoDB; +PREPARE s FROM 'SET STATEMENT binlog_format=ROW FOR SELECT * FROM b'; +ERROR HY000: Cannot switch out of the row-based binary log format when the session has open temporary tables +DROP TABLE b; +DROP TEMPORARY TABLE a; +CREATE TEMPORARY TABLE t (c INT); +PREPARE s FROM 'SET STATEMENT binlog_format=ROW FOR SELECT 1'; +ERROR HY000: Cannot switch out of the row-based binary log format when the session has open temporary tables +DROP TEMPORARY TABLE t; +# +# End of 10.2 test +# diff --git a/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row_myisam.result b/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row_myisam.result index 47b366fc61b..9a0e4127d16 100644 --- a/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row_myisam.result +++ b/mysql-test/suite/binlog/r/binlog_mysqlbinlog_row_myisam.result @@ -6413,3 +6413,29 @@ ROLLBACK /* added by mysqlbinlog */; # Cleanup. # DROP TABLE t1; +# +# Beginning of 10.2 test +# +# MDEV-25460: Assertion `!is_set() || (m_status == DA_OK_BULK && +# is_bulk_op())' failed in Diagnostics_area::set_ok_status in my_ok +# from mysql_sql_stmt_prepare +# +CREATE TEMPORARY TABLE a (c INT) ENGINE=InnoDB; +Warnings: +Warning 1286 Unknown storage engine 'InnoDB' +Warning 1266 Using storage engine MyISAM for table 'a' +CREATE TABLE b (c INT) ENGINE=InnoDB; +Warnings: +Warning 1286 Unknown storage engine 'InnoDB' +Warning 1266 Using storage engine MyISAM for table 'b' +PREPARE s FROM 'SET STATEMENT binlog_format=ROW FOR SELECT * FROM b'; +ERROR HY000: Cannot switch out of the row-based binary log format when the session has open temporary tables +DROP TABLE b; +DROP TEMPORARY TABLE a; +CREATE TEMPORARY TABLE t (c INT); +PREPARE s FROM 'SET STATEMENT binlog_format=ROW FOR SELECT 1'; +ERROR HY000: Cannot switch out of the row-based binary log format when the session has open temporary tables +DROP TEMPORARY TABLE t; +# +# End of 10.2 test +# diff --git a/mysql-test/suite/binlog/r/binlog_parallel_replication_ddl.result b/mysql-test/suite/binlog/r/binlog_parallel_replication_ddl.result new file mode 100644 index 00000000000..34d8bbf5999 --- /dev/null +++ b/mysql-test/suite/binlog/r/binlog_parallel_replication_ddl.result @@ -0,0 +1,8 @@ +RESET MASTER; +CREATE OR REPLACE SEQUENCE s1; +DROP SEQUENCE s1; +FLUSH LOGS; +FOUND 2 /GTID [0-9]+-[0-9]+-[0-9]+/ in mysqlbinlog.sql +The same as above number of samples must be found: +FOUND 2 /GTID [0-9]+-[0-9]+-[0-9]+ ddl/ in mysqlbinlog.sql +End of the tests diff --git a/mysql-test/suite/binlog/r/binlog_xa_handling.result b/mysql-test/suite/binlog/r/binlog_xa_handling.result new file mode 100644 index 00000000000..7a60fb59b9a --- /dev/null +++ b/mysql-test/suite/binlog/r/binlog_xa_handling.result @@ -0,0 +1,11 @@ +connection default; +CREATE TABLE t1(f1 int) ENGINE=Innodb; +XA START 'xa'; +INSERT INTO t1 VALUES(10); +BINLOG ' +SOgWTg8BAAAAbgAAAHIAAAAAAAQANS42LjMtbTUtZGVidWctbG9nAAAAAAAAAAAAAAAAAAAAAAAA +AAAAAAAAAAAAAAAAAABI6BZOEzgNAAgAEgAEBAQEEgAAVgAEGggAAAAICAgCAAAAAAVAYI8='; +XA END 'xa'; +XA PREPARE 'xa'; +XA ROLLBACK 'xa'; +DROP TABLE t1; diff --git a/mysql-test/suite/binlog/t/binlog_parallel_replication_ddl.test b/mysql-test/suite/binlog/t/binlog_parallel_replication_ddl.test new file mode 100644 index 00000000000..d861ecc96df --- /dev/null +++ b/mysql-test/suite/binlog/t/binlog_parallel_replication_ddl.test @@ -0,0 +1,30 @@ +# Check binlog properties of various DDL:s. +# Motivated by MDEV-27365. +# +--source include/have_log_bin.inc +--source include/have_binlog_format_mixed.inc + +RESET MASTER; + +# MDEV-27365 CREATE-or-REPLACE SEQUENCE bilogged without DDL flag +# Prove it is logged with the DDL flag. +CREATE OR REPLACE SEQUENCE s1; + +# This one has been always correct. +DROP SEQUENCE s1; +FLUSH LOGS; + +--let $MYSQLD_DATADIR= `select @@datadir` +--exec $MYSQL_BINLOG --base64-output=decode-rows $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog.sql + +--let SEARCH_PATTERN= GTID [0-9]+-[0-9]+-[0-9]+ +--let SEARCH_FILE= $MYSQLTEST_VARDIR/tmp/mysqlbinlog.sql +--source include/search_pattern_in_file.inc + +--echo The same as above number of samples must be found: +--let SEARCH_PATTERN= GTID [0-9]+-[0-9]+-[0-9]+ ddl +--let SEARCH_FILE= $MYSQLTEST_VARDIR/tmp/mysqlbinlog.sql +--source include/search_pattern_in_file.inc +--remove_file $MYSQLTEST_VARDIR/tmp/mysqlbinlog.sql + +--echo End of the tests diff --git a/mysql-test/suite/binlog/t/binlog_xa_handling.test b/mysql-test/suite/binlog/t/binlog_xa_handling.test new file mode 100644 index 00000000000..c454e83169e --- /dev/null +++ b/mysql-test/suite/binlog/t/binlog_xa_handling.test @@ -0,0 +1,28 @@ +############################################################################### +# Bug#19928622: ASSERTION `! IS_SET()' FAILED. | ABORT IN +# DIAGNOSTICS_AREA::SET_OK_STATUS +# +# MDEV-27536 Invalid BINLOG_BASE64_EVENT and assertion Diagnostics_area:: !is_set() +# +# Test: +# ===== +# Begin an XA transaction and execte a DML statement so that XA state becomes +# XA_ACTIVE. Execute the BINLOG command it should not cause any assert. +# Execution should be successful. +############################################################################### +--source include/have_log_bin.inc +--source include/have_innodb.inc + +--connection default +CREATE TABLE t1(f1 int) ENGINE=Innodb; + +XA START 'xa'; +INSERT INTO t1 VALUES(10); +BINLOG ' +SOgWTg8BAAAAbgAAAHIAAAAAAAQANS42LjMtbTUtZGVidWctbG9nAAAAAAAAAAAAAAAAAAAAAAAA +AAAAAAAAAAAAAAAAAABI6BZOEzgNAAgAEgAEBAQEEgAAVgAEGggAAAAICAgCAAAAAAVAYI8='; +XA END 'xa'; +XA PREPARE 'xa'; +XA ROLLBACK 'xa'; + +DROP TABLE t1; diff --git a/mysql-test/suite/federated/optimizer.result b/mysql-test/suite/federated/optimizer.result new file mode 100644 index 00000000000..5d7072e0b35 --- /dev/null +++ b/mysql-test/suite/federated/optimizer.result @@ -0,0 +1,52 @@ +connect master,127.0.0.1,root,,test,$MASTER_MYPORT,; +connect slave,127.0.0.1,root,,test,$SLAVE_MYPORT,; +connection master; +CREATE DATABASE federated; +connection slave; +CREATE DATABASE federated; +connection default; +# +# MDEV-14907 FEDERATEDX doesn't respect DISTINCT +# +CREATE TABLE t1 ( +`foo_id` bigint(20) unsigned NOT NULL, +`foo_name` varchar(255) DEFAULT NULL, +`parent_foo_id` bigint(20) unsigned DEFAULT NULL, +PRIMARY KEY (`foo_id`), +KEY `foo_name` (`foo_name`), +KEY `parent_foo_id` (`parent_foo_id`) +) DEFAULT CHARSET=utf8; +CREATE TABLE `fed_t1` ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://root@127.0.0.1:MASTER_PORT/test/t1'; +INSERT INTO t1 VALUES (968903, 'STRING - 0', 822857); +INSERT INTO t1 VALUES (968953, 'STRING - 1', 822857); +INSERT INTO t1 VALUES (971603, 'STRING - 2', 822857); +INSERT INTO t1 VALUES (971803, 'STRING - 3', 822857); +INSERT INTO t1 VALUES (975103, 'STRING - 4', 822857); +INSERT INTO t1 VALUES (822857, 'STRING', NULL); +select foo_id,parent_foo_id,foo_name from t1 where parent_foo_id = 822857 or foo_name like 'STRING%'; +foo_id parent_foo_id foo_name +968903 822857 STRING - 0 +968953 822857 STRING - 1 +971603 822857 STRING - 2 +971803 822857 STRING - 3 +975103 822857 STRING - 4 +822857 NULL STRING +explain +select foo_id,parent_foo_id,foo_name from fed_t1 where parent_foo_id = 822857 or foo_name like 'STRING%'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE fed_t1 ALL foo_name,parent_foo_id NULL NULL NULL 6 Using where +select foo_id,parent_foo_id,foo_name from fed_t1 where parent_foo_id = 822857 or foo_name like 'STRING%'; +foo_id parent_foo_id foo_name +968903 822857 STRING - 0 +968953 822857 STRING - 1 +971603 822857 STRING - 2 +971803 822857 STRING - 3 +975103 822857 STRING - 4 +822857 NULL STRING +DROP TABLE fed_t1, t1; +connection master; +DROP TABLE IF EXISTS federated.t1; +DROP DATABASE IF EXISTS federated; +connection slave; +DROP TABLE IF EXISTS federated.t1; +DROP DATABASE IF EXISTS federated; diff --git a/mysql-test/suite/federated/optimizer.test b/mysql-test/suite/federated/optimizer.test new file mode 100644 index 00000000000..4263060fab5 --- /dev/null +++ b/mysql-test/suite/federated/optimizer.test @@ -0,0 +1,39 @@ +# +#Test optimizer flags related to federated tables +# + +--source have_federatedx.inc +--source include/federated.inc + +connection default; + +--echo # +--echo # MDEV-14907 FEDERATEDX doesn't respect DISTINCT +--echo # + +CREATE TABLE t1 ( + `foo_id` bigint(20) unsigned NOT NULL, + `foo_name` varchar(255) DEFAULT NULL, + `parent_foo_id` bigint(20) unsigned DEFAULT NULL, + PRIMARY KEY (`foo_id`), + KEY `foo_name` (`foo_name`), + KEY `parent_foo_id` (`parent_foo_id`) +) DEFAULT CHARSET=utf8; + +--replace_result $MASTER_MYPORT MASTER_PORT +eval CREATE TABLE `fed_t1` ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://root@127.0.0.1:$MASTER_MYPORT/test/t1'; +INSERT INTO t1 VALUES (968903, 'STRING - 0', 822857); +INSERT INTO t1 VALUES (968953, 'STRING - 1', 822857); +INSERT INTO t1 VALUES (971603, 'STRING - 2', 822857); +INSERT INTO t1 VALUES (971803, 'STRING - 3', 822857); +INSERT INTO t1 VALUES (975103, 'STRING - 4', 822857); +INSERT INTO t1 VALUES (822857, 'STRING', NULL); + +select foo_id,parent_foo_id,foo_name from t1 where parent_foo_id = 822857 or foo_name like 'STRING%'; + +explain +select foo_id,parent_foo_id,foo_name from fed_t1 where parent_foo_id = 822857 or foo_name like 'STRING%'; +select foo_id,parent_foo_id,foo_name from fed_t1 where parent_foo_id = 822857 or foo_name like 'STRING%'; +DROP TABLE fed_t1, t1; + +source include/federated_cleanup.inc; diff --git a/mysql-test/suite/federated/rpl.result b/mysql-test/suite/federated/rpl.result new file mode 100644 index 00000000000..71821411c91 --- /dev/null +++ b/mysql-test/suite/federated/rpl.result @@ -0,0 +1,18 @@ +include/master-slave.inc +[connection master] +create table t1 (a int primary key, b int); +connection slave; +rename table t1 to t2; +create table t1 (a int primary key, b int) engine=federated connection='mysql://root@127.0.0.1:$SLAVE_MYPORT/test/t2'; +connection master; +insert t1 values (1,1),(2,2),(3,1); +delete from t1 where a=2; +connection slave; +select * from t1; +a b +1 1 +3 1 +drop table t2; +connection master; +drop table t1; +include/rpl_end.inc diff --git a/mysql-test/suite/federated/rpl.test b/mysql-test/suite/federated/rpl.test new file mode 100644 index 00000000000..6ec4bec5a1a --- /dev/null +++ b/mysql-test/suite/federated/rpl.test @@ -0,0 +1,19 @@ +source include/have_binlog_format_row.inc; +source include/master-slave.inc; + +create table t1 (a int primary key, b int); + +sync_slave_with_master; +rename table t1 to t2; +evalp create table t1 (a int primary key, b int) engine=federated connection='mysql://root@127.0.0.1:$SLAVE_MYPORT/test/t2'; +connection master; + +insert t1 values (1,1),(2,2),(3,1); +delete from t1 where a=2; +sync_slave_with_master; +select * from t1; +drop table t2; + +connection master; +drop table t1; +source include/rpl_end.inc; diff --git a/mysql-test/suite/funcs_1/datadict/columns.inc b/mysql-test/suite/funcs_1/datadict/columns.inc index 64318492b00..a03fef9c0dd 100644 --- a/mysql-test/suite/funcs_1/datadict/columns.inc +++ b/mysql-test/suite/funcs_1/datadict/columns.inc @@ -25,6 +25,10 @@ # --source suite/funcs_1/datadict/datadict_bug_12777.inc + +# The following is needed as embedded server can be compiled with and without +# privlege tables + eval SELECT * FROM information_schema.columns $my_where diff --git a/mysql-test/suite/funcs_1/t/is_columns_is_embedded.test b/mysql-test/suite/funcs_1/t/is_columns_is_embedded.test index 9db247fd6fd..bb9cc2a6ffb 100644 --- a/mysql-test/suite/funcs_1/t/is_columns_is_embedded.test +++ b/mysql-test/suite/funcs_1/t/is_columns_is_embedded.test @@ -14,7 +14,7 @@ # --source include/have_innodb.inc ---source include/is_embedded.inc +--source include/is_embedded_no_privileges.inc let $my_where = WHERE table_schema = 'information_schema' AND table_name <> 'profiling' AND table_name not like 'innodb_%'; diff --git a/mysql-test/suite/funcs_1/t/is_columns_myisam_embedded.test b/mysql-test/suite/funcs_1/t/is_columns_myisam_embedded.test index 3d0cca24474..bb2200604f3 100644 --- a/mysql-test/suite/funcs_1/t/is_columns_myisam_embedded.test +++ b/mysql-test/suite/funcs_1/t/is_columns_myisam_embedded.test @@ -10,10 +10,8 @@ # 2008-06-06 mleich Create this this variant for the embedded server. # -if (`SELECT VERSION() NOT LIKE '%embedded%'`) -{ - --skip Test requires: embedded server -} +--source include/is_embedded_no_privileges.inc + let $engine_type= MyISAM; SET @@session.sql_mode = 'NO_ENGINE_SUBSTITUTION'; --source suite/funcs_1/datadict/datadict_load.inc diff --git a/mysql-test/suite/funcs_1/t/is_columns_mysql_embedded.test b/mysql-test/suite/funcs_1/t/is_columns_mysql_embedded.test index d1ed45425ec..9ef1b6d869a 100644 --- a/mysql-test/suite/funcs_1/t/is_columns_mysql_embedded.test +++ b/mysql-test/suite/funcs_1/t/is_columns_mysql_embedded.test @@ -10,10 +10,7 @@ # 2008-06-06 mleich Create this variant for the embedded server # -if (`SELECT VERSION() NOT LIKE '%embedded%'`) -{ - --skip Test requires: embedded server -} +--source include/is_embedded_no_privileges.inc let $my_where = WHERE table_schema = 'mysql'; --source suite/funcs_1/datadict/columns.inc diff --git a/mysql-test/suite/galera/disabled.def b/mysql-test/suite/galera/disabled.def index c934a7c14f4..5f83dc1b43b 100644 --- a/mysql-test/suite/galera/disabled.def +++ b/mysql-test/suite/galera/disabled.def @@ -12,7 +12,6 @@ GCF-1081 : MDEV-18283 Galera test failure on galera.GCF-1081 GCF-939 : MDEV-21520 galera.GCF-939 -MDEV-20225 : MDEV-20886 galera.MDEV-20225 MW-328A : MDEV-22666 galera.MW-328A MTR failed: "Semaphore wait has lasted > 600 seconds" and do not release port 16002 MW-328B : MDEV-22666 galera.MW-328A MTR failed: "Semaphore wait has lasted > 600 seconds" and do not release port 16002 MW-328D : MDEV-27550 ER_LOCK_DEADLOCK is gone after MDEV-27025 @@ -36,7 +35,6 @@ galera_var_ignore_apply_errors : MDEV-26770 galera_var_ignore_apply_errors fails galera_var_node_address : MDEV-20485 Galera test failure galera_var_notify_cmd : MDEV-21905 Galera test galera_var_notify_cmd causes hang galera_var_retry_autocommit: MDEV-18181 Galera test failure on galera.galera_var_retry_autocommit -mysql-wsrep#198 : MDEV-24446: galera.mysql-wsrep#198 MTR failed: query 'reap' failed: 2000: Unknown MySQL error partition : MDEV-19958 Galera test failure on galera.partition query_cache: MDEV-15805 Test failure on galera.query_cache versioning_trx_id: MDEV-18590: galera.versioning_trx_id: Test failure: mysqltest: Result content mismatch diff --git a/mysql-test/suite/galera/r/MDEV-20225.result b/mysql-test/suite/galera/r/MDEV-20225.result index 9f20daed9de..245051309d1 100644 --- a/mysql-test/suite/galera/r/MDEV-20225.result +++ b/mysql-test/suite/galera/r/MDEV-20225.result @@ -11,11 +11,12 @@ DROP TRIGGER tr1; connection node_2; connection node_1; INSERT INTO t1 VALUES (NULL); -connection node_2; +connection node_2a; SET GLOBAL debug_dbug = 'RESET'; SET DEBUG_SYNC = 'now SIGNAL signal.mdev_20225_continue'; SET DEBUG_SYNC = 'RESET'; SET GLOBAL wsrep_slave_threads = 1; +connection node_2; SHOW TRIGGERS; Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation DROP TABLE t1; diff --git a/mysql-test/suite/galera/r/MDEV-25494.result b/mysql-test/suite/galera/r/MDEV-25494.result new file mode 100644 index 00000000000..e889fecc42e --- /dev/null +++ b/mysql-test/suite/galera/r/MDEV-25494.result @@ -0,0 +1,14 @@ +connection node_2; +connection node_1; +SET SESSION binlog_format=STATEMENT; +Warnings: +Warning 1105 MariaDB Galera and flashback do not support binlog format: STATEMENT +CREATE TEMPORARY TABLE t (i INT) UNION=(t); +ALTER TABLE t ADD extrac CHAR(1); +SHOW CREATE TABLE t; +Table Create Table +t CREATE TEMPORARY TABLE `t` ( + `i` int(11) DEFAULT NULL, + `extrac` char(1) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +DROP TABLE t; diff --git a/mysql-test/suite/galera/r/enforce_storage_engine2.result b/mysql-test/suite/galera/r/enforce_storage_engine2.result index 8b174139eae..e0fb8d867dc 100644 --- a/mysql-test/suite/galera/r/enforce_storage_engine2.result +++ b/mysql-test/suite/galera/r/enforce_storage_engine2.result @@ -9,6 +9,7 @@ CREATE TABLE t1(i INT) ENGINE=INNODB; CREATE TABLE t2(i INT) ENGINE=MYISAM; Warnings: Note 1266 Using storage engine InnoDB for table 't2' +Note 1266 Using storage engine InnoDB for table 't2' connection node_2; SHOW TABLES; Tables_in_test diff --git a/mysql-test/suite/galera/r/galera_create_table_like.result b/mysql-test/suite/galera/r/galera_create_table_like.result index 82543331ad5..d1633077dcb 100644 --- a/mysql-test/suite/galera/r/galera_create_table_like.result +++ b/mysql-test/suite/galera/r/galera_create_table_like.result @@ -49,3 +49,8 @@ DROP TABLE schema2.real_table2; DROP TABLE schema2.real_table3; DROP SCHEMA schema1; DROP SCHEMA schema2; +use test; +CREATE TEMPORARY TABLE t (c INT) ENGINE=mrg_myisam UNION=(t,t2) insert_method=FIRST; +CREATE TABLE t2 LIKE t; +ERROR HY000: Table 't' is differently defined or of non-MyISAM type or doesn't exist +DROP TABLE t; diff --git a/mysql-test/suite/galera/r/galera_ddl_fk_conflict.result b/mysql-test/suite/galera/r/galera_ddl_fk_conflict.result index 5f09345b79b..03e84f9facd 100644 --- a/mysql-test/suite/galera/r/galera_ddl_fk_conflict.result +++ b/mysql-test/suite/galera/r/galera_ddl_fk_conflict.result @@ -7,389 +7,6 @@ connect node_1b, 127.0.0.1, root, , test, $NODE_MYPORT_1; connection node_1b; SET SESSION wsrep_sync_wait=0; ###################################################################### -# Test for OPTIMIZE -###################################################################### -###################################################################### -# -# Scenario #1: DML working on FK parent table BF aborted by DDL -# over child table -# -###################################################################### -connection node_1; -SET SESSION wsrep_sync_wait=0; -CREATE TABLE p1 (pk INTEGER PRIMARY KEY, f2 CHAR(30)); -INSERT INTO p1 VALUES (1, 'INITIAL VALUE'); -CREATE TABLE p2 (pk INTEGER PRIMARY KEY, f2 CHAR(30)); -INSERT INTO p2 VALUES (1, 'INITIAL VALUE'); -INSERT INTO p2 VALUES (2, 'INITIAL VALUE'); -CREATE TABLE c1 (pk INTEGER PRIMARY KEY, fk INTEGER, FOREIGN KEY (fk) REFERENCES p1(pk)); -INSERT INTO c1 VALUES (1,1); -CREATE TABLE c2 (pk INTEGER PRIMARY KEY, fk1 INTEGER, fk2 INTEGER, FOREIGN KEY (fk1) REFERENCES p1(pk), FOREIGN KEY (fk2) REFERENCES p2(pk)); -INSERT INTO c2 VALUES (1,1,1), (2,1,2); -connection node_1; -SET AUTOCOMMIT=ON; -START TRANSACTION; -UPDATE p1 SET f2 = 'TO DEADLOCK' WHERE pk = 1; -connection node_2; -SET SESSION wsrep_sync_wait=0; -OPTIMIZE TABLE c1 ; -Table Op Msg_type Msg_text -test.c1 optimize note Table does not support optimize, doing recreate + analyze instead -test.c1 optimize status OK -connection node_1; -COMMIT; -ERROR 40001: Deadlock found when trying to get lock; try restarting transaction -SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE'; -EXPECT_1 -1 -SELECT COUNT(*) AS EXPECT_2 FROM p2 WHERE f2 = 'INITIAL VALUE'; -EXPECT_2 -2 -connection node_2; -SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE'; -EXPECT_1 -1 -SELECT COUNT(*) AS EXPECT_2 FROM p2 WHERE f2 = 'INITIAL VALUE'; -EXPECT_2 -2 -###################################################################### -# -# Scenario #2: DML working on FK parent table tries to replicate, but -# fails in certification for earlier DDL on child table -# -###################################################################### -connection node_1; -BEGIN; -SET GLOBAL wsrep_provider_options = 'dbug=d,apply_monitor_slave_enter_sync'; -connection node_2; -OPTIMIZE TABLE c1 ; -Table Op Msg_type Msg_text -test.c1 optimize note Table does not support optimize, doing recreate + analyze instead -test.c1 optimize status OK -connection node_1a; -SET SESSION wsrep_on = 0; -SET SESSION wsrep_on = 1; -SET GLOBAL wsrep_provider_options = 'dbug='; -connection node_1; -UPDATE p1 SET f2 = 'TO DEADLOCK' WHERE pk = 1; -COMMIT; -connection node_1a; -SET GLOBAL wsrep_provider_options = 'signal=apply_monitor_slave_enter_sync'; -connection node_1; -ERROR 40001: Deadlock found when trying to get lock; try restarting transaction -SELECT 'I deadlocked'; -I deadlocked -I deadlocked -SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE'; -EXPECT_1 -1 -SELECT COUNT(*) AS EXPECT_2 FROM p2 WHERE f2 = 'INITIAL VALUE'; -EXPECT_2 -2 -connection node_2; -SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE'; -EXPECT_1 -1 -SELECT COUNT(*) AS EXPECT_2 FROM p2 WHERE f2 = 'INITIAL VALUE'; -EXPECT_2 -2 -###################################################################### -# -# Scenario #3: 2 DMLs working on two FK parent tables try to replicate, -# but fails in certification for earlier DDL on child table -# which is child to both FK parents -# -###################################################################### -connection node_1; -BEGIN; -connection node_1b; -BEGIN; -connection node_1a; -SET GLOBAL wsrep_provider_options = 'dbug=d,apply_monitor_slave_enter_sync'; -connection node_2; -OPTIMIZE TABLE c2 ; -Table Op Msg_type Msg_text -test.c2 optimize note Table does not support optimize, doing recreate + analyze instead -test.c2 optimize status OK -connection node_1a; -SET SESSION wsrep_on = 0; -SET SESSION wsrep_on = 1; -SET GLOBAL wsrep_provider_options = 'dbug='; -connection node_1; -UPDATE p1 SET f2 = 'TO DEADLOCK' WHERE pk = 1; -COMMIT; -connection node_1b; -UPDATE p2 SET f2 = 'TO DEADLOCK' WHERE pk = 2; -COMMIT; -connection node_1a; -SET GLOBAL wsrep_provider_options = 'signal=apply_monitor_slave_enter_sync'; -connection node_1; -ERROR 40001: Deadlock found when trying to get lock; try restarting transaction -SELECT 'I deadlocked'; -I deadlocked -I deadlocked -connection node_1b; -ERROR 40001: Deadlock found when trying to get lock; try restarting transaction -SELECT 'I deadlocked'; -I deadlocked -I deadlocked -SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE'; -EXPECT_1 -1 -SELECT COUNT(*) AS EXPECT_2 FROM p2 WHERE f2 = 'INITIAL VALUE'; -EXPECT_2 -2 -connection node_2; -SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE'; -EXPECT_1 -1 -SELECT COUNT(*) AS EXPECT_2 FROM p2 WHERE f2 = 'INITIAL VALUE'; -EXPECT_2 -2 -DROP TABLE c1, c2; -DROP TABLE p1, p2; -###################################################################### -# Test for OPTIMIZE -###################################################################### -connection node_1; -SET SESSION wsrep_sync_wait=0; -CREATE TABLE p1 (pk INTEGER PRIMARY KEY, f2 CHAR(30)); -INSERT INTO p1 VALUES (1, 'INITIAL VALUE'); -CREATE TABLE c1 (pk INTEGER PRIMARY KEY, fk INTEGER, FOREIGN KEY (fk) REFERENCES p1(pk)); -INSERT INTO c1 VALUES (1,1); -###################################################################### -# -# Scenario #4: DML working on FK parent table tries to replicate, but -# fails in certification for earlier DDL on child table -# and another temporary table. TMP table should be skipped -# but FK child table should be replicated with proper keys -# -###################################################################### -connection node_1; -BEGIN; -SET GLOBAL wsrep_provider_options = 'dbug=d,apply_monitor_slave_enter_sync'; -connection node_2; -CREATE TEMPORARY TABLE tmp (i int); -OPTIMIZE TABLE c1, tmp ; -Table Op Msg_type Msg_text -test.c1 optimize note Table does not support optimize, doing recreate + analyze instead -test.c1 optimize status OK -test.tmp optimize note Table does not support optimize, doing recreate + analyze instead -test.tmp optimize status OK -DROP TABLE tmp; -connection node_1a; -SET SESSION wsrep_on = 0; -SET SESSION wsrep_on = 1; -SET GLOBAL wsrep_provider_options = 'dbug='; -connection node_1; -UPDATE p1 SET f2 = 'TO DEADLOCK' WHERE pk = 1; -COMMIT; -connection node_1a; -SET GLOBAL wsrep_provider_options = 'signal=apply_monitor_slave_enter_sync'; -connection node_1; -ERROR 40001: Deadlock found when trying to get lock; try restarting transaction -SELECT 'I deadlocked'; -I deadlocked -I deadlocked -SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE'; -EXPECT_1 -1 -connection node_2; -SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE'; -EXPECT_1 -1 -DROP TABLE c1; -DROP TABLE p1; -###################################################################### -# Test for REPAIR -###################################################################### -###################################################################### -# -# Scenario #1: DML working on FK parent table BF aborted by DDL -# over child table -# -###################################################################### -connection node_1; -SET SESSION wsrep_sync_wait=0; -CREATE TABLE p1 (pk INTEGER PRIMARY KEY, f2 CHAR(30)); -INSERT INTO p1 VALUES (1, 'INITIAL VALUE'); -CREATE TABLE p2 (pk INTEGER PRIMARY KEY, f2 CHAR(30)); -INSERT INTO p2 VALUES (1, 'INITIAL VALUE'); -INSERT INTO p2 VALUES (2, 'INITIAL VALUE'); -CREATE TABLE c1 (pk INTEGER PRIMARY KEY, fk INTEGER, FOREIGN KEY (fk) REFERENCES p1(pk)); -INSERT INTO c1 VALUES (1,1); -CREATE TABLE c2 (pk INTEGER PRIMARY KEY, fk1 INTEGER, fk2 INTEGER, FOREIGN KEY (fk1) REFERENCES p1(pk), FOREIGN KEY (fk2) REFERENCES p2(pk)); -INSERT INTO c2 VALUES (1,1,1), (2,1,2); -connection node_1; -SET AUTOCOMMIT=ON; -START TRANSACTION; -UPDATE p1 SET f2 = 'TO DEADLOCK' WHERE pk = 1; -connection node_2; -SET SESSION wsrep_sync_wait=0; -REPAIR TABLE c1 ; -Table Op Msg_type Msg_text -test.c1 repair note The storage engine for the table doesn't support repair -connection node_1; -COMMIT; -ERROR 40001: Deadlock found when trying to get lock; try restarting transaction -SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE'; -EXPECT_1 -1 -SELECT COUNT(*) AS EXPECT_2 FROM p2 WHERE f2 = 'INITIAL VALUE'; -EXPECT_2 -2 -connection node_2; -SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE'; -EXPECT_1 -1 -SELECT COUNT(*) AS EXPECT_2 FROM p2 WHERE f2 = 'INITIAL VALUE'; -EXPECT_2 -2 -###################################################################### -# -# Scenario #2: DML working on FK parent table tries to replicate, but -# fails in certification for earlier DDL on child table -# -###################################################################### -connection node_1; -BEGIN; -SET GLOBAL wsrep_provider_options = 'dbug=d,apply_monitor_slave_enter_sync'; -connection node_2; -REPAIR TABLE c1 ; -Table Op Msg_type Msg_text -test.c1 repair note The storage engine for the table doesn't support repair -connection node_1a; -SET SESSION wsrep_on = 0; -SET SESSION wsrep_on = 1; -SET GLOBAL wsrep_provider_options = 'dbug='; -connection node_1; -UPDATE p1 SET f2 = 'TO DEADLOCK' WHERE pk = 1; -COMMIT; -connection node_1a; -SET GLOBAL wsrep_provider_options = 'signal=apply_monitor_slave_enter_sync'; -connection node_1; -ERROR 40001: Deadlock found when trying to get lock; try restarting transaction -SELECT 'I deadlocked'; -I deadlocked -I deadlocked -SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE'; -EXPECT_1 -1 -SELECT COUNT(*) AS EXPECT_2 FROM p2 WHERE f2 = 'INITIAL VALUE'; -EXPECT_2 -2 -connection node_2; -SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE'; -EXPECT_1 -1 -SELECT COUNT(*) AS EXPECT_2 FROM p2 WHERE f2 = 'INITIAL VALUE'; -EXPECT_2 -2 -###################################################################### -# -# Scenario #3: 2 DMLs working on two FK parent tables try to replicate, -# but fails in certification for earlier DDL on child table -# which is child to both FK parents -# -###################################################################### -connection node_1; -BEGIN; -connection node_1b; -BEGIN; -connection node_1a; -SET GLOBAL wsrep_provider_options = 'dbug=d,apply_monitor_slave_enter_sync'; -connection node_2; -REPAIR TABLE c2 ; -Table Op Msg_type Msg_text -test.c2 repair note The storage engine for the table doesn't support repair -connection node_1a; -SET SESSION wsrep_on = 0; -SET SESSION wsrep_on = 1; -SET GLOBAL wsrep_provider_options = 'dbug='; -connection node_1; -UPDATE p1 SET f2 = 'TO DEADLOCK' WHERE pk = 1; -COMMIT; -connection node_1b; -UPDATE p2 SET f2 = 'TO DEADLOCK' WHERE pk = 2; -COMMIT; -connection node_1a; -SET GLOBAL wsrep_provider_options = 'signal=apply_monitor_slave_enter_sync'; -connection node_1; -ERROR 40001: Deadlock found when trying to get lock; try restarting transaction -SELECT 'I deadlocked'; -I deadlocked -I deadlocked -connection node_1b; -ERROR 40001: Deadlock found when trying to get lock; try restarting transaction -SELECT 'I deadlocked'; -I deadlocked -I deadlocked -SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE'; -EXPECT_1 -1 -SELECT COUNT(*) AS EXPECT_2 FROM p2 WHERE f2 = 'INITIAL VALUE'; -EXPECT_2 -2 -connection node_2; -SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE'; -EXPECT_1 -1 -SELECT COUNT(*) AS EXPECT_2 FROM p2 WHERE f2 = 'INITIAL VALUE'; -EXPECT_2 -2 -DROP TABLE c1, c2; -DROP TABLE p1, p2; -###################################################################### -# Test for REPAIR -###################################################################### -connection node_1; -SET SESSION wsrep_sync_wait=0; -CREATE TABLE p1 (pk INTEGER PRIMARY KEY, f2 CHAR(30)); -INSERT INTO p1 VALUES (1, 'INITIAL VALUE'); -CREATE TABLE c1 (pk INTEGER PRIMARY KEY, fk INTEGER, FOREIGN KEY (fk) REFERENCES p1(pk)); -INSERT INTO c1 VALUES (1,1); -###################################################################### -# -# Scenario #4: DML working on FK parent table tries to replicate, but -# fails in certification for earlier DDL on child table -# and another temporary table. TMP table should be skipped -# but FK child table should be replicated with proper keys -# -###################################################################### -connection node_1; -BEGIN; -SET GLOBAL wsrep_provider_options = 'dbug=d,apply_monitor_slave_enter_sync'; -connection node_2; -CREATE TEMPORARY TABLE tmp (i int); -REPAIR TABLE c1, tmp ; -Table Op Msg_type Msg_text -test.c1 repair note The storage engine for the table doesn't support repair -test.tmp repair note The storage engine for the table doesn't support repair -DROP TABLE tmp; -connection node_1a; -SET SESSION wsrep_on = 0; -SET SESSION wsrep_on = 1; -SET GLOBAL wsrep_provider_options = 'dbug='; -connection node_1; -UPDATE p1 SET f2 = 'TO DEADLOCK' WHERE pk = 1; -COMMIT; -connection node_1a; -SET GLOBAL wsrep_provider_options = 'signal=apply_monitor_slave_enter_sync'; -connection node_1; -ERROR 40001: Deadlock found when trying to get lock; try restarting transaction -SELECT 'I deadlocked'; -I deadlocked -I deadlocked -SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE'; -EXPECT_1 -1 -connection node_2; -SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE'; -EXPECT_1 -1 -DROP TABLE c1; -DROP TABLE p1; -###################################################################### # Test for ALTER ENGINE=INNODB ###################################################################### ###################################################################### diff --git a/mysql-test/suite/galera/r/galera_ddl_fk_no_conflict.result b/mysql-test/suite/galera/r/galera_ddl_fk_no_conflict.result new file mode 100644 index 00000000000..43046922143 --- /dev/null +++ b/mysql-test/suite/galera/r/galera_ddl_fk_no_conflict.result @@ -0,0 +1,330 @@ +connection node_2; +connection node_1; +CREATE TABLE parent (pk INTEGER PRIMARY KEY); +CREATE TABLE child (pk INTEGER PRIMARY KEY, parent_id INTEGER, FOREIGN KEY(parent_id) REFERENCES parent(pk) ); +INSERT INTO parent VALUES (1), (2), (3), (4); +INSERT INTO child VALUES (1,1), (2,2), (3,3), (4,4); +connection node_1; +START TRANSACTION; +SELECT * FROM parent FOR UPDATE; +pk +1 +2 +3 +4 +connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1; +OPTIMIZE TABLE child;; +Table Op Msg_type Msg_text +test.child optimize note Table does not support optimize, doing recreate + analyze instead +test.child optimize status OK +connection node_1; +COMMIT; +DROP TABLE child, parent; +disconnect node_1a; +CREATE TABLE parent (pk INTEGER PRIMARY KEY); +CREATE TABLE child (pk INTEGER PRIMARY KEY, parent_id INTEGER, FOREIGN KEY(parent_id) REFERENCES parent(pk) ON UPDATE CASCADE); +INSERT INTO parent VALUES (1), (2), (3), (4); +INSERT INTO child VALUES (1,1), (2,2), (3,3), (4,4); +connection node_1; +START TRANSACTION; +SELECT * FROM parent FOR UPDATE; +pk +1 +2 +3 +4 +connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1; +OPTIMIZE TABLE child;; +Table Op Msg_type Msg_text +test.child optimize note Table does not support optimize, doing recreate + analyze instead +test.child optimize status OK +connection node_1; +COMMIT; +DROP TABLE child, parent; +disconnect node_1a; +CREATE TABLE parent (pk INTEGER PRIMARY KEY); +CREATE TABLE child (pk INTEGER PRIMARY KEY, parent_id INTEGER, FOREIGN KEY(parent_id) REFERENCES parent(pk) ON DELETE CASCADE); +INSERT INTO parent VALUES (1), (2), (3), (4); +INSERT INTO child VALUES (1,1), (2,2), (3,3), (4,4); +connection node_1; +START TRANSACTION; +SELECT * FROM parent FOR UPDATE; +pk +1 +2 +3 +4 +connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1; +OPTIMIZE TABLE child;; +Table Op Msg_type Msg_text +test.child optimize note Table does not support optimize, doing recreate + analyze instead +test.child optimize status OK +connection node_1; +COMMIT; +DROP TABLE child, parent; +disconnect node_1a; +CREATE TABLE parent (pk INTEGER PRIMARY KEY); +CREATE TABLE child (pk INTEGER PRIMARY KEY, parent_id INTEGER, FOREIGN KEY(parent_id) REFERENCES parent(pk) ON UPDATE CASCADE ON DELETE CASCADE); +INSERT INTO parent VALUES (1), (2), (3), (4); +INSERT INTO child VALUES (1,1), (2,2), (3,3), (4,4); +connection node_1; +START TRANSACTION; +SELECT * FROM parent FOR UPDATE; +pk +1 +2 +3 +4 +connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1; +OPTIMIZE TABLE child;; +Table Op Msg_type Msg_text +test.child optimize note Table does not support optimize, doing recreate + analyze instead +test.child optimize status OK +connection node_1; +COMMIT; +DROP TABLE child, parent; +disconnect node_1a; +CREATE TABLE parent (pk INTEGER PRIMARY KEY); +CREATE TABLE child (pk INTEGER PRIMARY KEY, parent_id INTEGER, FOREIGN KEY(parent_id) REFERENCES parent(pk) ); +INSERT INTO parent VALUES (1), (2), (3), (4); +INSERT INTO child VALUES (1,1), (2,2), (3,3), (4,4); +connection node_1; +START TRANSACTION; +SELECT * FROM parent FOR UPDATE; +pk +1 +2 +3 +4 +connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1; +REPAIR TABLE child;; +Table Op Msg_type Msg_text +test.child repair note The storage engine for the table doesn't support repair +connection node_1; +COMMIT; +DROP TABLE child, parent; +disconnect node_1a; +CREATE TABLE parent (pk INTEGER PRIMARY KEY); +CREATE TABLE child (pk INTEGER PRIMARY KEY, parent_id INTEGER, FOREIGN KEY(parent_id) REFERENCES parent(pk) ON UPDATE CASCADE); +INSERT INTO parent VALUES (1), (2), (3), (4); +INSERT INTO child VALUES (1,1), (2,2), (3,3), (4,4); +connection node_1; +START TRANSACTION; +SELECT * FROM parent FOR UPDATE; +pk +1 +2 +3 +4 +connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1; +REPAIR TABLE child;; +Table Op Msg_type Msg_text +test.child repair note The storage engine for the table doesn't support repair +connection node_1; +COMMIT; +DROP TABLE child, parent; +disconnect node_1a; +CREATE TABLE parent (pk INTEGER PRIMARY KEY); +CREATE TABLE child (pk INTEGER PRIMARY KEY, parent_id INTEGER, FOREIGN KEY(parent_id) REFERENCES parent(pk) ON DELETE CASCADE); +INSERT INTO parent VALUES (1), (2), (3), (4); +INSERT INTO child VALUES (1,1), (2,2), (3,3), (4,4); +connection node_1; +START TRANSACTION; +SELECT * FROM parent FOR UPDATE; +pk +1 +2 +3 +4 +connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1; +REPAIR TABLE child;; +Table Op Msg_type Msg_text +test.child repair note The storage engine for the table doesn't support repair +connection node_1; +COMMIT; +DROP TABLE child, parent; +disconnect node_1a; +CREATE TABLE parent (pk INTEGER PRIMARY KEY); +CREATE TABLE child (pk INTEGER PRIMARY KEY, parent_id INTEGER, FOREIGN KEY(parent_id) REFERENCES parent(pk) ON UPDATE CASCADE ON DELETE CASCADE); +INSERT INTO parent VALUES (1), (2), (3), (4); +INSERT INTO child VALUES (1,1), (2,2), (3,3), (4,4); +connection node_1; +START TRANSACTION; +SELECT * FROM parent FOR UPDATE; +pk +1 +2 +3 +4 +connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1; +REPAIR TABLE child;; +Table Op Msg_type Msg_text +test.child repair note The storage engine for the table doesn't support repair +connection node_1; +COMMIT; +DROP TABLE child, parent; +disconnect node_1a; +CREATE TABLE parent (pk INTEGER PRIMARY KEY); +CREATE TABLE child (pk INTEGER PRIMARY KEY, parent_id INTEGER, FOREIGN KEY(parent_id) REFERENCES parent(pk) ); +INSERT INTO parent VALUES (1), (2), (3), (4); +INSERT INTO child VALUES (1,1), (2,2), (3,3), (4,4); +connection node_1; +START TRANSACTION; +SELECT * FROM parent FOR UPDATE; +pk +1 +2 +3 +4 +connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1; +CHECK TABLE child;; +Table Op Msg_type Msg_text +test.child check status OK +connection node_1; +COMMIT; +DROP TABLE child, parent; +disconnect node_1a; +CREATE TABLE parent (pk INTEGER PRIMARY KEY); +CREATE TABLE child (pk INTEGER PRIMARY KEY, parent_id INTEGER, FOREIGN KEY(parent_id) REFERENCES parent(pk) ON UPDATE CASCADE); +INSERT INTO parent VALUES (1), (2), (3), (4); +INSERT INTO child VALUES (1,1), (2,2), (3,3), (4,4); +connection node_1; +START TRANSACTION; +SELECT * FROM parent FOR UPDATE; +pk +1 +2 +3 +4 +connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1; +CHECK TABLE child;; +Table Op Msg_type Msg_text +test.child check status OK +connection node_1; +COMMIT; +DROP TABLE child, parent; +disconnect node_1a; +CREATE TABLE parent (pk INTEGER PRIMARY KEY); +CREATE TABLE child (pk INTEGER PRIMARY KEY, parent_id INTEGER, FOREIGN KEY(parent_id) REFERENCES parent(pk) ON DELETE CASCADE); +INSERT INTO parent VALUES (1), (2), (3), (4); +INSERT INTO child VALUES (1,1), (2,2), (3,3), (4,4); +connection node_1; +START TRANSACTION; +SELECT * FROM parent FOR UPDATE; +pk +1 +2 +3 +4 +connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1; +CHECK TABLE child;; +Table Op Msg_type Msg_text +test.child check status OK +connection node_1; +COMMIT; +DROP TABLE child, parent; +disconnect node_1a; +CREATE TABLE parent (pk INTEGER PRIMARY KEY); +CREATE TABLE child (pk INTEGER PRIMARY KEY, parent_id INTEGER, FOREIGN KEY(parent_id) REFERENCES parent(pk) ON UPDATE CASCADE ON DELETE CASCADE); +INSERT INTO parent VALUES (1), (2), (3), (4); +INSERT INTO child VALUES (1,1), (2,2), (3,3), (4,4); +connection node_1; +START TRANSACTION; +SELECT * FROM parent FOR UPDATE; +pk +1 +2 +3 +4 +connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1; +CHECK TABLE child;; +Table Op Msg_type Msg_text +test.child check status OK +connection node_1; +COMMIT; +DROP TABLE child, parent; +disconnect node_1a; +CREATE TABLE parent (pk INTEGER PRIMARY KEY); +CREATE TABLE child (pk INTEGER PRIMARY KEY, parent_id INTEGER, FOREIGN KEY(parent_id) REFERENCES parent(pk) ); +INSERT INTO parent VALUES (1), (2), (3), (4); +INSERT INTO child VALUES (1,1), (2,2), (3,3), (4,4); +connection node_1; +START TRANSACTION; +SELECT * FROM parent FOR UPDATE; +pk +1 +2 +3 +4 +connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1; +ANALYZE TABLE child;; +Table Op Msg_type Msg_text +test.child analyze status Engine-independent statistics collected +test.child analyze status OK +connection node_1; +COMMIT; +DROP TABLE child, parent; +disconnect node_1a; +CREATE TABLE parent (pk INTEGER PRIMARY KEY); +CREATE TABLE child (pk INTEGER PRIMARY KEY, parent_id INTEGER, FOREIGN KEY(parent_id) REFERENCES parent(pk) ON UPDATE CASCADE); +INSERT INTO parent VALUES (1), (2), (3), (4); +INSERT INTO child VALUES (1,1), (2,2), (3,3), (4,4); +connection node_1; +START TRANSACTION; +SELECT * FROM parent FOR UPDATE; +pk +1 +2 +3 +4 +connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1; +ANALYZE TABLE child;; +Table Op Msg_type Msg_text +test.child analyze status Engine-independent statistics collected +test.child analyze status OK +connection node_1; +COMMIT; +DROP TABLE child, parent; +disconnect node_1a; +CREATE TABLE parent (pk INTEGER PRIMARY KEY); +CREATE TABLE child (pk INTEGER PRIMARY KEY, parent_id INTEGER, FOREIGN KEY(parent_id) REFERENCES parent(pk) ON DELETE CASCADE); +INSERT INTO parent VALUES (1), (2), (3), (4); +INSERT INTO child VALUES (1,1), (2,2), (3,3), (4,4); +connection node_1; +START TRANSACTION; +SELECT * FROM parent FOR UPDATE; +pk +1 +2 +3 +4 +connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1; +ANALYZE TABLE child;; +Table Op Msg_type Msg_text +test.child analyze status Engine-independent statistics collected +test.child analyze status OK +connection node_1; +COMMIT; +DROP TABLE child, parent; +disconnect node_1a; +CREATE TABLE parent (pk INTEGER PRIMARY KEY); +CREATE TABLE child (pk INTEGER PRIMARY KEY, parent_id INTEGER, FOREIGN KEY(parent_id) REFERENCES parent(pk) ON UPDATE CASCADE ON DELETE CASCADE); +INSERT INTO parent VALUES (1), (2), (3), (4); +INSERT INTO child VALUES (1,1), (2,2), (3,3), (4,4); +connection node_1; +START TRANSACTION; +SELECT * FROM parent FOR UPDATE; +pk +1 +2 +3 +4 +connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1; +ANALYZE TABLE child;; +Table Op Msg_type Msg_text +test.child analyze status Engine-independent statistics collected +test.child analyze status OK +connection node_1; +COMMIT; +DROP TABLE child, parent; +disconnect node_1a; diff --git a/mysql-test/suite/galera/r/galera_myisam_transactions.result b/mysql-test/suite/galera/r/galera_myisam_transactions.result index 091c5ffb6f6..8a30e265d28 100644 --- a/mysql-test/suite/galera/r/galera_myisam_transactions.result +++ b/mysql-test/suite/galera/r/galera_myisam_transactions.result @@ -37,3 +37,14 @@ SELECT COUNT(*) = 0 FROM t2; COUNT(*) = 0 1 DROP TABLE t1, t2, t3; +SET SESSION lock_wait_timeout=2; +SET GLOBAL wsrep_replicate_myisam= ON; +CREATE TEMPORARY TABLE t1 (i INT, PRIMARY KEY pk (i)) ENGINE=MyISAM; +Warnings: +Warning 1280 Name 'pk' ignored for PRIMARY key. +PREPARE stmt FROM "INSERT INTO t1 (id) SELECT * FROM (SELECT 4 AS i) AS y"; +INSERT INTO t1 VALUES(4); +DEALLOCATE PREPARE stmt; +COMMIT; +DROP TABLE t1; +SET GLOBAL wsrep_replicate_myisam=OFF; diff --git a/mysql-test/suite/galera/r/galera_repair_view.result b/mysql-test/suite/galera/r/galera_repair_view.result new file mode 100644 index 00000000000..8dfe8c8db52 --- /dev/null +++ b/mysql-test/suite/galera/r/galera_repair_view.result @@ -0,0 +1,17 @@ +connection node_2; +connection node_1; +CREATE TABLE t1(a int not null primary key) engine=innodb; +REPAIR TABLE t1; +Table Op Msg_type Msg_text +test.t1 repair note The storage engine for the table doesn't support repair +CREATE VIEW v1 AS SELECT a FROM t1; +REPAIR VIEW v1; +Table Op Msg_type Msg_text +test.v1 repair status OK +DROP VIEW v1; +DROP TABLE t1; +CREATE VIEW v1 AS SELECT table_name FROM information_schema.tables; +REPAIR VIEW v1; +Table Op Msg_type Msg_text +test.v1 repair status OK +DROP VIEW v1; diff --git a/mysql-test/suite/galera/r/galera_sequences.result b/mysql-test/suite/galera/r/galera_sequences.result new file mode 100644 index 00000000000..48593d2a258 --- /dev/null +++ b/mysql-test/suite/galera/r/galera_sequences.result @@ -0,0 +1,49 @@ +connection node_2; +connection node_1; +connection node_1; +CREATE SEQUENCE `seq` start with 1 minvalue 1 maxvalue 1000000 increment by 0 cache 1000 nocycle ENGINE=InnoDB; +SHOW CREATE SEQUENCE seq; +Table Create Table +seq CREATE SEQUENCE `seq` start with 1 minvalue 1 maxvalue 1000000 increment by 0 cache 1000 nocycle ENGINE=InnoDB +connection node_2; +SHOW CREATE SEQUENCE seq; +Table Create Table +seq CREATE SEQUENCE `seq` start with 1 minvalue 1 maxvalue 1000000 increment by 0 cache 1000 nocycle ENGINE=InnoDB +connection node_1; +ALTER SEQUENCE seq MAXVALUE = 10000; +SHOW CREATE SEQUENCE seq; +Table Create Table +seq CREATE SEQUENCE `seq` start with 1 minvalue 1 maxvalue 10000 increment by 0 cache 1000 nocycle ENGINE=InnoDB +connection node_2; +SHOW CREATE SEQUENCE seq; +Table Create Table +seq CREATE SEQUENCE `seq` start with 1 minvalue 1 maxvalue 10000 increment by 0 cache 1000 nocycle ENGINE=InnoDB +connection node_1; +DROP SEQUENCE seq; +SHOW CREATE SEQUENCE seq; +ERROR 42S02: Table 'test.seq' doesn't exist +connection node_2; +SHOW CREATE SEQUENCE seq; +ERROR 42S02: Table 'test.seq' doesn't exist +connection node_1; +CREATE SEQUENCE Seq1_1 START WITH 1 INCREMENT BY 1; +select NEXT VALUE FOR Seq1_1; +NEXT VALUE FOR Seq1_1 +1 +alter table Seq1_1 engine=myisam; +select NEXT VALUE FOR Seq1_1; +NEXT VALUE FOR Seq1_1 +1001 +alter table Seq1_1 engine=innodb; +select NEXT VALUE FOR Seq1_1; +NEXT VALUE FOR Seq1_1 +2001 +connection node_2; +SHOW CREATE SEQUENCE Seq1_1; +Table Create Table +Seq1_1 CREATE SEQUENCE `Seq1_1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB +select NEXT VALUE FOR Seq1_1; +NEXT VALUE FOR Seq1_1 +1 +connection node_1; +DROP SEQUENCE Seq1_1; diff --git a/mysql-test/suite/sys_vars/r/wsrep_slave_threads_basic.result b/mysql-test/suite/galera/r/wsrep_slave_threads_basic.result index 62be5a42416..edfb4747d92 100644 --- a/mysql-test/suite/sys_vars/r/wsrep_slave_threads_basic.result +++ b/mysql-test/suite/galera/r/wsrep_slave_threads_basic.result @@ -1,3 +1,5 @@ +connection node_2; +connection node_1; # # wsrep_slave_threads # diff --git a/mysql-test/suite/galera/t/MDEV-20225.test b/mysql-test/suite/galera/t/MDEV-20225.test index 2124e499530..60ab1c53e40 100644 --- a/mysql-test/suite/galera/t/MDEV-20225.test +++ b/mysql-test/suite/galera/t/MDEV-20225.test @@ -34,12 +34,16 @@ INSERT INTO t1 VALUES (NULL); # so there is no sync point or condition to wait. --sleep 1 ---connection node_2 +--let $galera_connection_name = node_2a +--let $galera_server_number = 2 +--source include/galera_connect.inc +--connection node_2a SET GLOBAL debug_dbug = 'RESET'; SET DEBUG_SYNC = 'now SIGNAL signal.mdev_20225_continue'; SET DEBUG_SYNC = 'RESET'; SET GLOBAL wsrep_slave_threads = 1; +--connection node_2 # Trigger should now be dropped on node_2. --let $wait_condition = SELECT COUNT(*) = 0 FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME like '%tr1' --source include/wait_condition.inc diff --git a/mysql-test/suite/galera/t/MDEV-25494.test b/mysql-test/suite/galera/t/MDEV-25494.test new file mode 100644 index 00000000000..a6a70145469 --- /dev/null +++ b/mysql-test/suite/galera/t/MDEV-25494.test @@ -0,0 +1,7 @@ +--source include/galera_cluster.inc + +SET SESSION binlog_format=STATEMENT; +CREATE TEMPORARY TABLE t (i INT) UNION=(t); +ALTER TABLE t ADD extrac CHAR(1); +SHOW CREATE TABLE t; +DROP TABLE t; diff --git a/mysql-test/suite/galera/t/galera_create_table_like.test b/mysql-test/suite/galera/t/galera_create_table_like.test index 0e0e8b0ffcf..3cf51521be1 100644 --- a/mysql-test/suite/galera/t/galera_create_table_like.test +++ b/mysql-test/suite/galera/t/galera_create_table_like.test @@ -48,3 +48,12 @@ DROP TABLE schema2.real_table3; DROP SCHEMA schema1; DROP SCHEMA schema2; + +# +# MDEV-25856: SIGSEGV in ha_myisammrg::append_create_info +# +use test; +CREATE TEMPORARY TABLE t (c INT) ENGINE=mrg_myisam UNION=(t,t2) insert_method=FIRST; +--error 1472 +CREATE TABLE t2 LIKE t; +DROP TABLE t; diff --git a/mysql-test/suite/galera/t/galera_ddl_fk_conflict.test b/mysql-test/suite/galera/t/galera_ddl_fk_conflict.test index 88837933e5a..4ec866a9f74 100644 --- a/mysql-test/suite/galera/t/galera_ddl_fk_conflict.test +++ b/mysql-test/suite/galera/t/galera_ddl_fk_conflict.test @@ -17,14 +17,6 @@ SET SESSION wsrep_sync_wait=0; --connection node_1b SET SESSION wsrep_sync_wait=0; ---let $table_admin_command = OPTIMIZE ---source galera_ddl_fk_conflict.inc ---source galera_ddl_fk_conflict_with_tmp.inc - ---let $table_admin_command = REPAIR ---source galera_ddl_fk_conflict.inc ---source galera_ddl_fk_conflict_with_tmp.inc - --let $table_admin_command = ALTER --let $table_admin_command_end = ENGINE=INNODB --source galera_ddl_fk_conflict.inc diff --git a/mysql-test/suite/galera/t/galera_ddl_fk_no_conflict.inc b/mysql-test/suite/galera/t/galera_ddl_fk_no_conflict.inc new file mode 100644 index 00000000000..9e2dd391678 --- /dev/null +++ b/mysql-test/suite/galera/t/galera_ddl_fk_no_conflict.inc @@ -0,0 +1,34 @@ +# This test attempts to show that OPTIMIZE on a child table does NOT +# acquire MDL locks on the parent table. # +# param: $table_admin_command +# DDL table command to test, script will build full SQL statement: +# $table_admin_command TABLE c; +# +# param: $FK_constraint +# Foreign key constraint to use when creating the child table. +# + +CREATE TABLE parent (pk INTEGER PRIMARY KEY); +--eval CREATE TABLE child (pk INTEGER PRIMARY KEY, parent_id INTEGER, FOREIGN KEY(parent_id) REFERENCES parent(pk) $fk_constraint) + +INSERT INTO parent VALUES (1), (2), (3), (4); +INSERT INTO child VALUES (1,1), (2,2), (3,3), (4,4); + +--connection node_1 +# Start a transaction that uses the parent table, +# so that we acquire MDL lock on parent. +START TRANSACTION; +SELECT * FROM parent FOR UPDATE; + +# In a different connection, execute the table +# admin command (OPTIMIZE / REPAIR ...) on the child table. +--connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1 +--eval $table_admin_command TABLE child; + +# Expect no conflict. +--connection node_1 +COMMIT; + +DROP TABLE child, parent; + +--disconnect node_1a diff --git a/mysql-test/suite/galera/t/galera_ddl_fk_no_conflict.test b/mysql-test/suite/galera/t/galera_ddl_fk_no_conflict.test new file mode 100644 index 00000000000..6307b629e2b --- /dev/null +++ b/mysql-test/suite/galera/t/galera_ddl_fk_no_conflict.test @@ -0,0 +1,57 @@ +# +# This test attempts to show that table admin commands +# (OPTIMIZE / REPAIR / ANALYZE ...) on child table, +# do NOT cause conflicts on parent table operations. +# Contrary to what is said in MDEV-21577, which claimed +# that OPTIMIZE and REPAIR do take MDL locks on parent +# table. +# +# Should this test start failing due to a conflict +# (i.e. ER_LOCK_DEADLOCK), then it might be that we +# are missing additional keys for certification +# on the corresponding table admin command. + +--source include/galera_cluster.inc + +--let $table_admin_command = OPTIMIZE +--let $fk_constraint = +--source galera_ddl_fk_no_conflict.inc +--let $fk_constraint = ON UPDATE CASCADE +--source galera_ddl_fk_no_conflict.inc +--let $fk_constraint = ON DELETE CASCADE +--source galera_ddl_fk_no_conflict.inc +--let $fk_constraint = ON UPDATE CASCADE ON DELETE CASCADE +--source galera_ddl_fk_no_conflict.inc + + +--let $table_admin_command = REPAIR +--let $fk_constraint = +--source galera_ddl_fk_no_conflict.inc +--let $fk_constraint = ON UPDATE CASCADE +--source galera_ddl_fk_no_conflict.inc +--let $fk_constraint = ON DELETE CASCADE +--source galera_ddl_fk_no_conflict.inc +--let $fk_constraint = ON UPDATE CASCADE ON DELETE CASCADE +--source galera_ddl_fk_no_conflict.inc + + +--let $table_admin_command = CHECK +--let $fk_constraint = +--source galera_ddl_fk_no_conflict.inc +--let $fk_constraint = ON UPDATE CASCADE +--source galera_ddl_fk_no_conflict.inc +--let $fk_constraint = ON DELETE CASCADE +--source galera_ddl_fk_no_conflict.inc +--let $fk_constraint = ON UPDATE CASCADE ON DELETE CASCADE +--source galera_ddl_fk_no_conflict.inc + + +--let $table_admin_command = ANALYZE +--let $fk_constraint = +--source galera_ddl_fk_no_conflict.inc +--let $fk_constraint = ON UPDATE CASCADE +--source galera_ddl_fk_no_conflict.inc +--let $fk_constraint = ON DELETE CASCADE +--source galera_ddl_fk_no_conflict.inc +--let $fk_constraint = ON UPDATE CASCADE ON DELETE CASCADE +--source galera_ddl_fk_no_conflict.inc diff --git a/mysql-test/suite/galera/t/galera_myisam_transactions.test b/mysql-test/suite/galera/t/galera_myisam_transactions.test index 00e0bf3fdca..30677ddbe4d 100644 --- a/mysql-test/suite/galera/t/galera_myisam_transactions.test +++ b/mysql-test/suite/galera/t/galera_myisam_transactions.test @@ -34,3 +34,16 @@ SELECT COUNT(*) = 0 FROM t2; SELECT COUNT(*) = 0 FROM t2; DROP TABLE t1, t2, t3; + +# +# MDEV-25201 : Assertion `thd->wsrep_trx_meta.gtid.seqno == (-1)' failed in int wsrep_to_isolation_begin(THD*, const char*, const char*, const TABLE_LIST*, Alter_info*) +# +SET SESSION lock_wait_timeout=2; +SET GLOBAL wsrep_replicate_myisam= ON; +CREATE TEMPORARY TABLE t1 (i INT, PRIMARY KEY pk (i)) ENGINE=MyISAM; +PREPARE stmt FROM "INSERT INTO t1 (id) SELECT * FROM (SELECT 4 AS i) AS y"; +INSERT INTO t1 VALUES(4); +DEALLOCATE PREPARE stmt; +COMMIT; +DROP TABLE t1; +SET GLOBAL wsrep_replicate_myisam=OFF; diff --git a/mysql-test/suite/galera/t/galera_repair_view.test b/mysql-test/suite/galera/t/galera_repair_view.test new file mode 100644 index 00000000000..0010d90c7ec --- /dev/null +++ b/mysql-test/suite/galera/t/galera_repair_view.test @@ -0,0 +1,12 @@ +--source include/galera_cluster.inc + +CREATE TABLE t1(a int not null primary key) engine=innodb; +REPAIR TABLE t1; +CREATE VIEW v1 AS SELECT a FROM t1; +REPAIR VIEW v1; +DROP VIEW v1; +DROP TABLE t1; + +CREATE VIEW v1 AS SELECT table_name FROM information_schema.tables; +REPAIR VIEW v1; +DROP VIEW v1; diff --git a/mysql-test/suite/galera/t/galera_sequences.test b/mysql-test/suite/galera/t/galera_sequences.test new file mode 100644 index 00000000000..480366f6a6f --- /dev/null +++ b/mysql-test/suite/galera/t/galera_sequences.test @@ -0,0 +1,46 @@ +--source include/galera_cluster.inc + +# +# MDEV-19353 : Alter Sequence do not replicate to another nodes with in Galera Cluster +# + +--connection node_1 +CREATE SEQUENCE `seq` start with 1 minvalue 1 maxvalue 1000000 increment by 0 cache 1000 nocycle ENGINE=InnoDB; +SHOW CREATE SEQUENCE seq; + +--connection node_2 +SHOW CREATE SEQUENCE seq; + +--connection node_1 +ALTER SEQUENCE seq MAXVALUE = 10000; +SHOW CREATE SEQUENCE seq; + +--connection node_2 +SHOW CREATE SEQUENCE seq; + +--connection node_1 +DROP SEQUENCE seq; +--error ER_NO_SUCH_TABLE +SHOW CREATE SEQUENCE seq; + +--connection node_2 +--error ER_NO_SUCH_TABLE +SHOW CREATE SEQUENCE seq; + +# +# MDEV-18848 : Galera: 10.4 node crashed with Assertion `client_state.transaction().active()` after altering SEQUENCE table's engine to myisam and back to innodb +# +--connection node_1 +CREATE SEQUENCE Seq1_1 START WITH 1 INCREMENT BY 1; +select NEXT VALUE FOR Seq1_1; +alter table Seq1_1 engine=myisam; +select NEXT VALUE FOR Seq1_1; +alter table Seq1_1 engine=innodb; +select NEXT VALUE FOR Seq1_1; + +--connection node_2 +SHOW CREATE SEQUENCE Seq1_1; +select NEXT VALUE FOR Seq1_1; + +--connection node_1 +DROP SEQUENCE Seq1_1; diff --git a/mysql-test/suite/sys_vars/t/wsrep_slave_threads_basic.test b/mysql-test/suite/galera/t/wsrep_slave_threads_basic.test index 80b4648982d..ecf159f8365 100644 --- a/mysql-test/suite/sys_vars/t/wsrep_slave_threads_basic.test +++ b/mysql-test/suite/galera/t/wsrep_slave_threads_basic.test @@ -1,4 +1,4 @@ ---source include/have_wsrep.inc +--source include/galera_cluster.inc --echo # --echo # wsrep_slave_threads diff --git a/mysql-test/suite/galera_sr/r/MDEV-27553.result b/mysql-test/suite/galera_sr/r/MDEV-27553.result new file mode 100644 index 00000000000..f6f81bd13f1 --- /dev/null +++ b/mysql-test/suite/galera_sr/r/MDEV-27553.result @@ -0,0 +1,23 @@ +connection node_2; +connection node_1; +CREATE TABLE t1 (f1 INTEGER PRIMARY KEY); +connection node_1; +SET SESSION wsrep_trx_fragment_size=1; +START TRANSACTION; +INSERT INTO t1 VALUES (1); +SET @@global.debug_dbug="+d,ha_index_init_fail"; +ROLLBACK; +connection node_2; +SELECT COUNT(*) `Expect 0` FROM mysql.wsrep_streaming_log; +Expect 0 +0 +connection node_1; +SET @@global.debug_dbug=""; +SELECT COUNT(*) `Expect 1` FROM mysql.wsrep_streaming_log; +Expect 1 +1 +SET SESSION wsrep_on=OFF; +DELETE FROM mysql.wsrep_streaming_log; +SET SESSION wsrep_on=ON; +DROP TABLE t1; +CALL mtr.add_suppression("WSREP: Failed to init table for index scan"); diff --git a/mysql-test/suite/galera_sr/r/MDEV-27615.result b/mysql-test/suite/galera_sr/r/MDEV-27615.result new file mode 100644 index 00000000000..a3475811285 --- /dev/null +++ b/mysql-test/suite/galera_sr/r/MDEV-27615.result @@ -0,0 +1,27 @@ +connection node_2; +connection node_1; +connection node_1; +connection node_2; +connect node_2a, 127.0.0.1, root, , test, $NODE_MYPORT_2; +connection node_2; +CREATE TABLE t1 (f1 INTEGER PRIMARY KEY) Engine=InnoDB; +SET DEBUG_SYNC='wsrep_before_fragment_certification SIGNAL before_fragment WAIT_FOR continue'; +SET SESSION wsrep_trx_fragment_size=1; +START TRANSACTION; +INSERT INTO t1 VALUES (1);; +connection node_2a; +SET SESSION wsrep_sync_wait = 0; +SET DEBUG_SYNC='now WAIT_FOR before_fragment'; +SET GLOBAL wsrep_cluster_address = ''; +SET DEBUG_SYNC = 'now SIGNAL continue'; +connection node_2; +ERROR HY000: Lost connection to MySQL server during query +connection node_2a; +SELECT * FROM mysql.wsrep_streaming_log; +node_uuid trx_id seqno flags frag +SELECT * FROM t1; +f1 +DROP TABLE t1; +SET DEBUG_SYNC = 'RESET'; +disconnect node_2; +connect node_2, 127.0.0.1, root, , test, $NODE_MYPORT_2; diff --git a/mysql-test/suite/galera_sr/t/MDEV-27553.test b/mysql-test/suite/galera_sr/t/MDEV-27553.test new file mode 100644 index 00000000000..d17af175512 --- /dev/null +++ b/mysql-test/suite/galera_sr/t/MDEV-27553.test @@ -0,0 +1,33 @@ +# +# MDEV-27553 Assertion `inited==INDEX' failed: int handler::ha_index_end() +# + +--source include/galera_cluster.inc +--source include/have_debug.inc + +CREATE TABLE t1 (f1 INTEGER PRIMARY KEY); + +--connection node_1 +--let $wsrep_cluster_address_orig = `SELECT @@wsrep_cluster_address` +SET SESSION wsrep_trx_fragment_size=1; +START TRANSACTION; +INSERT INTO t1 VALUES (1); +# This will result in failure to remove fragments +# from streaming log, in the following ROLLBACK. +SET @@global.debug_dbug="+d,ha_index_init_fail"; +ROLLBACK; + +--connection node_2 +SELECT COUNT(*) `Expect 0` FROM mysql.wsrep_streaming_log; + + +--connection node_1 +SET @@global.debug_dbug=""; +SELECT COUNT(*) `Expect 1` FROM mysql.wsrep_streaming_log; + +SET SESSION wsrep_on=OFF; +DELETE FROM mysql.wsrep_streaming_log; +SET SESSION wsrep_on=ON; +DROP TABLE t1; + +CALL mtr.add_suppression("WSREP: Failed to init table for index scan"); diff --git a/mysql-test/suite/galera_sr/t/MDEV-27615.test b/mysql-test/suite/galera_sr/t/MDEV-27615.test new file mode 100644 index 00000000000..121a85fb20c --- /dev/null +++ b/mysql-test/suite/galera_sr/t/MDEV-27615.test @@ -0,0 +1,72 @@ +# +# MDEV-27615 - Assertion `server_id.is_undefined() == false' +# failed in wsrep::transaction::certify_fragment() +# + +--source include/galera_cluster.inc +--source include/have_debug_sync.inc + +--let $node_1=node_1 +--let $node_2=node_2 +--source suite/galera/include/auto_increment_offset_save.inc + +--connect node_2a, 127.0.0.1, root, , test, $NODE_MYPORT_2 + +--connection node_2 +CREATE TABLE t1 (f1 INTEGER PRIMARY KEY) Engine=InnoDB; + +# +# Set debug sync point right before the assert +# +SET DEBUG_SYNC='wsrep_before_fragment_certification SIGNAL before_fragment WAIT_FOR continue'; + +SET SESSION wsrep_trx_fragment_size=1; +START TRANSACTION; +--send INSERT INTO t1 VALUES (1); + + +# +# Disconnect node_2 from cluster +# +--connection node_2a +SET SESSION wsrep_sync_wait = 0; +--let $node_2_cluster_address = `SELECT @@wsrep_cluster_address` +SET DEBUG_SYNC='now WAIT_FOR before_fragment'; +SET GLOBAL wsrep_cluster_address = ''; + +--let $wait_condition = SELECT VARIABLE_VALUE = 'Disconnected' FROM information_schema.global_status WHERE VARIABLE_NAME = 'wsrep_cluster_status'; +--source include/wait_condition.inc +SET DEBUG_SYNC = 'now SIGNAL continue'; + + +# +# Disconnect causes connection to node_2 to be closed +# +--connection node_2 +--error 2013 # CR_SERVER_LOST +--reap + + +# +# Reconnect node 2 +# +--connection node_2a +--disable_query_log +--eval SET GLOBAL wsrep_cluster_address = '$node_2_cluster_address'; +--enable_query_log +--source include/wait_wsrep_ready.inc + +# +# Expect the transaction to be rolled back and cleanup +# +SELECT * FROM mysql.wsrep_streaming_log; +SELECT * FROM t1; + +DROP TABLE t1; +SET DEBUG_SYNC = 'RESET'; + +--disconnect node_2 +--connect node_2, 127.0.0.1, root, , test, $NODE_MYPORT_2 + + +--source suite/galera/include/auto_increment_offset_restore.inc diff --git a/mysql-test/suite/innodb/r/innodb-wl5522.result b/mysql-test/suite/innodb/r/innodb-wl5522.result index 16cbc113267..3e6725138f3 100644 --- a/mysql-test/suite/innodb/r/innodb-wl5522.result +++ b/mysql-test/suite/innodb/r/innodb-wl5522.result @@ -336,7 +336,7 @@ SELECT * FROM t1; ERROR HY000: Tablespace has been discarded for table `t1` restore: t1 .ibd and .cfg files ALTER TABLE t1 IMPORT TABLESPACE; -ERROR HY000: Schema mismatch (Number of columns don't match, table has 6 columns but the tablespace meta-data file has 5 columns) +ERROR HY000: Schema mismatch (Column c3 not found in tablespace.) unlink: t1.ibd unlink: t1.cfg DROP TABLE t1; @@ -921,6 +921,22 @@ c1 c2 15 1 16 1 DROP TABLE t1; +CREATE TABLE t1 ( id INT NOT NULL, i1 INT, i2 INT, i3 INT, PRIMARY KEY (id)) engine=innodb; +CREATE TABLE t2 ( id INT NOT NULL, i1 INT, i2 INT, PRIMARY KEY (id)) engine=innodb; +ALTER TABLE t2 DISCARD TABLESPACE; +FLUSH TABLES t1 FOR EXPORT; +UNLOCK TABLES; +ALTER TABLE t2 IMPORT TABLESPACE; +ERROR HY000: Schema mismatch (Column DB_ROW_ID ordinal value mismatch, it's at 3 in the table and 4 in the tablespace meta-data file) +DROP TABLE t1, t2; +CREATE TABLE t1 ( id INT NOT NULL, i1 INT, i2 INT, PRIMARY KEY (id)) engine=innodb; +CREATE TABLE t2 ( id INT NOT NULL, i1 INT, i2 INT, i3 INT, PRIMARY KEY (id)) engine=innodb; +ALTER TABLE t2 DISCARD TABLESPACE; +FLUSH TABLES t1 FOR EXPORT; +UNLOCK TABLES; +ALTER TABLE t2 IMPORT TABLESPACE; +ERROR HY000: Schema mismatch (Column i3 not found in tablespace.) +DROP TABLE t1, t2; call mtr.add_suppression("Got error -1 when reading table '.*'"); call mtr.add_suppression("InnoDB: Error: tablespace id and flags in file '.*'"); call mtr.add_suppression("InnoDB: The table .* doesn't have a corresponding tablespace, it was discarded"); diff --git a/mysql-test/suite/innodb/r/innodb_buffer_pool_resize_with_chunks.result b/mysql-test/suite/innodb/r/innodb_buffer_pool_resize_with_chunks.result index 4bf244c9588..efb652091bf 100644 --- a/mysql-test/suite/innodb/r/innodb_buffer_pool_resize_with_chunks.result +++ b/mysql-test/suite/innodb/r/innodb_buffer_pool_resize_with_chunks.result @@ -1,6 +1,6 @@ select @@innodb_buffer_pool_chunk_size; @@innodb_buffer_pool_chunk_size -2097152 +4194304 create table t1 (id int not null, val int not null default '0', primary key (id)) ENGINE=InnoDB ROW_FORMAT=COMPRESSED; create or replace view view0 as select 1 union all select 1; set @`v_id` := 0; @@ -18,9 +18,9 @@ count(val) 262144 drop table t1; drop view view0; -set global innodb_buffer_pool_size = 1048576; +set global innodb_buffer_pool_size = 2*1048576; Warnings: -Warning 1292 Truncated incorrect innodb_buffer_pool_size value: '1048576' +Warning 1292 Truncated incorrect innodb_buffer_pool_size value: '2097152' select @@innodb_buffer_pool_size; @@innodb_buffer_pool_size -6291456 +4194304 diff --git a/mysql-test/suite/innodb/r/innodb_information_schema.result b/mysql-test/suite/innodb/r/innodb_information_schema.result index 1c96bcfc665..921b062eae3 100644 --- a/mysql-test/suite/innodb/r/innodb_information_schema.result +++ b/mysql-test/suite/innodb/r/innodb_information_schema.result @@ -1,18 +1,18 @@ lock_mode lock_type lock_table lock_index lock_rec lock_data -X RECORD `test`.```t'\"_str` PRIMARY 2 '1', 'abc', '''abc', 'abc''', 'a''bc', 'a''bc''', '''abc''''' -X RECORD `test`.```t'\"_str` PRIMARY 2 '1', 'abc', '''abc', 'abc''', 'a''bc', 'a''bc''', '''abc''''' -X RECORD `test`.```t'\"_str` PRIMARY 3 '2', 'abc', '"abc', 'abc"', 'a"bc', 'a"bc"', '"abc""' -X RECORD `test`.```t'\"_str` PRIMARY 3 '2', 'abc', '"abc', 'abc"', 'a"bc', 'a"bc"', '"abc""' -X RECORD `test`.```t'\"_str` PRIMARY 4 '3', 'abc', '\\abc', 'abc\\', 'a\\bc', 'a\\bc\\', '\\abc\\\\' -X RECORD `test`.```t'\"_str` PRIMARY 4 '3', 'abc', '\\abc', 'abc\\', 'a\\bc', 'a\\bc\\', '\\abc\\\\' -X RECORD `test`.```t'\"_str` PRIMARY 5 '4', 'abc', '\0abc', 'abc\0', 'a\0bc', 'a\0bc\0', 'a\0bc\0\0' -X RECORD `test`.```t'\"_str` PRIMARY 5 '4', 'abc', '\0abc', 'abc\0', 'a\0bc', 'a\0bc\0', 'a\0bc\0\0' -X RECORD `test`.`t_min` PRIMARY 2 -128, 0, -32768, 0, -8388608, 0, -2147483648, 0, -9223372036854775808, 0 -X RECORD `test`.`t_min` PRIMARY 2 -128, 0, -32768, 0, -8388608, 0, -2147483648, 0, -9223372036854775808, 0 -X RECORD `test`.`t_max` PRIMARY 2 127, 255, 32767, 65535, 8388607, 16777215, 2147483647, 4294967295, 9223372036854775807, 18446744073709551615 -X RECORD `test`.`t_max` PRIMARY 2 127, 255, 32767, 65535, 8388607, 16777215, 2147483647, 4294967295, 9223372036854775807, 18446744073709551615 -X RECORD `test`.```t'\"_str` PRIMARY 1 supremum pseudo-record -X RECORD `test`.```t'\"_str` PRIMARY 1 supremum pseudo-record +X RECORD `test`.```t'\"_str` PRIMARY 1 # +X RECORD `test`.```t'\"_str` PRIMARY 1 # +X RECORD `test`.```t'\"_str` PRIMARY 2 # +X RECORD `test`.```t'\"_str` PRIMARY 2 # +X RECORD `test`.```t'\"_str` PRIMARY 3 # +X RECORD `test`.```t'\"_str` PRIMARY 3 # +X RECORD `test`.```t'\"_str` PRIMARY 4 # +X RECORD `test`.```t'\"_str` PRIMARY 4 # +X RECORD `test`.```t'\"_str` PRIMARY 5 # +X RECORD `test`.```t'\"_str` PRIMARY 5 # +X RECORD `test`.`t_max` PRIMARY 2 # +X RECORD `test`.`t_max` PRIMARY 2 # +X RECORD `test`.`t_min` PRIMARY 2 # +X RECORD `test`.`t_min` PRIMARY 2 # lock_table COUNT(*) `test`.`t_max` 2 `test`.`t_min` 2 diff --git a/mysql-test/suite/innodb/r/instant_alter_import.result b/mysql-test/suite/innodb/r/instant_alter_import.result index c31e9c8e656..fad2fd99685 100644 --- a/mysql-test/suite/innodb/r/instant_alter_import.result +++ b/mysql-test/suite/innodb/r/instant_alter_import.result @@ -118,3 +118,12 @@ FLUSH TABLE t1 FOR EXPORT; UNLOCK TABLES; ALTER TABLE t2 IMPORT TABLESPACE; DROP TABLE t2, t1; +CREATE TABLE t1 ( id INT NOT NULL, i1 INT, i2 INT, PRIMARY KEY (id)) engine=innodb; +CREATE TABLE t2 ( id INT NOT NULL, i1 INT, i2 INT, PRIMARY KEY (id)) engine=innodb; +ALTER TABLE test.t1 add COLUMN i3 INT AFTER i1; +ALTER TABLE t2 DISCARD TABLESPACE; +FLUSH TABLES t1 FOR EXPORT; +UNLOCK TABLES; +ALTER TABLE t2 IMPORT TABLESPACE; +ERROR HY000: Index for table 't2' is corrupt; try to repair it +DROP TABLE t1, t2; diff --git a/mysql-test/suite/innodb/r/no_pad.result b/mysql-test/suite/innodb/r/no_pad.result new file mode 100644 index 00000000000..0c039c30a5e --- /dev/null +++ b/mysql-test/suite/innodb/r/no_pad.result @@ -0,0 +1,7 @@ +CREATE TABLE t1 (a CHAR(8), id INT, PRIMARY KEY (a,id)) COLLATE utf8_nopad_bin +ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +INSERT INTO t1 VALUES ('',1); +ALTER TABLE t1 ROW_FORMAT=DYNAMIC; +INSERT INTO t1 VALUES ('',2); +ALTER TABLE t1 ROW_FORMAT=REDUNDANT; +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/r/restart,16k,innodb.rdiff b/mysql-test/suite/innodb/r/restart,16k,innodb.rdiff new file mode 100644 index 00000000000..b36ed067913 --- /dev/null +++ b/mysql-test/suite/innodb/r/restart,16k,innodb.rdiff @@ -0,0 +1,16 @@ +--- ./suite/innodb/r/restart.result 2022-01-18 20:36:56.054653376 +1100 ++++ suite/innodb/r/restart.reject 2022-01-19 08:12:28.602794678 +1100 +@@ -32,10 +32,10 @@ + SELECT @@innodb_buffer_pool_size INTO @innodb_buffer_pool_size_orig; + SELECT CEILING((256 + 64) * @@innodb_page_size / 1048576) * 1048576 INTO @min_pool_size; + EXECUTE IMMEDIATE 'SET GLOBAL innodb_buffer_pool_size = ?' USING (@min_pool_size -1); +-ERROR 42000: Variable 'innodb_buffer_pool_size' can't be set to the value of 'WRONG_VALUE' ++ERROR 42000: Variable 'innodb_buffer_pool_size' can't be set to the value of '5242879' + SHOW WARNINGS; + Level Code Message +-Warning 1210 innodb_buffer_pool_size must be at least MIN_VAL for innodb_page_size=PAGE_SIZE +-Error 1231 Variable 'innodb_buffer_pool_size' can't be set to the value of 'WRONG_VALUE' ++Warning 1210 innodb_buffer_pool_size must be at least 5242880 for innodb_page_size=16384 ++Error 1231 Variable 'innodb_buffer_pool_size' can't be set to the value of '5242879' + EXECUTE IMMEDIATE 'SET GLOBAL innodb_buffer_pool_size = ?' USING (@min_pool_size); + SET GLOBAL innodb_buffer_pool_size = @innodb_buffer_pool_size_orig; diff --git a/mysql-test/suite/innodb/r/restart,32k,innodb.rdiff b/mysql-test/suite/innodb/r/restart,32k,innodb.rdiff new file mode 100644 index 00000000000..8fa057814c4 --- /dev/null +++ b/mysql-test/suite/innodb/r/restart,32k,innodb.rdiff @@ -0,0 +1,16 @@ +--- ./suite/innodb/r/restart.result 2022-01-18 20:36:56.054653376 +1100 ++++ suite/innodb/r/restart.reject 2022-01-19 08:07:57.402230887 +1100 +@@ -32,10 +32,10 @@ + SELECT @@innodb_buffer_pool_size INTO @innodb_buffer_pool_size_orig; + SELECT CEILING((256 + 64) * @@innodb_page_size / 1048576) * 1048576 INTO @min_pool_size; + EXECUTE IMMEDIATE 'SET GLOBAL innodb_buffer_pool_size = ?' USING (@min_pool_size -1); +-ERROR 42000: Variable 'innodb_buffer_pool_size' can't be set to the value of 'WRONG_VALUE' ++ERROR 42000: Variable 'innodb_buffer_pool_size' can't be set to the value of '10485759' + SHOW WARNINGS; + Level Code Message +-Warning 1210 innodb_buffer_pool_size must be at least MIN_VAL for innodb_page_size=PAGE_SIZE +-Error 1231 Variable 'innodb_buffer_pool_size' can't be set to the value of 'WRONG_VALUE' ++Warning 1210 innodb_buffer_pool_size must be at least 10485760 for innodb_page_size=32768 ++Error 1231 Variable 'innodb_buffer_pool_size' can't be set to the value of '10485759' + EXECUTE IMMEDIATE 'SET GLOBAL innodb_buffer_pool_size = ?' USING (@min_pool_size); + SET GLOBAL innodb_buffer_pool_size = @innodb_buffer_pool_size_orig; diff --git a/mysql-test/suite/innodb/r/restart,4k,innodb.rdiff b/mysql-test/suite/innodb/r/restart,4k,innodb.rdiff new file mode 100644 index 00000000000..7d0846360e0 --- /dev/null +++ b/mysql-test/suite/innodb/r/restart,4k,innodb.rdiff @@ -0,0 +1,16 @@ +--- ./suite/innodb/r/restart.result 2022-01-18 20:36:56.054653376 +1100 ++++ suite/innodb/r/restart.reject 2022-01-19 08:13:56.397475513 +1100 +@@ -32,10 +32,10 @@ + SELECT @@innodb_buffer_pool_size INTO @innodb_buffer_pool_size_orig; + SELECT CEILING((256 + 64) * @@innodb_page_size / 1048576) * 1048576 INTO @min_pool_size; + EXECUTE IMMEDIATE 'SET GLOBAL innodb_buffer_pool_size = ?' USING (@min_pool_size -1); +-ERROR 42000: Variable 'innodb_buffer_pool_size' can't be set to the value of 'WRONG_VALUE' ++ERROR 42000: Variable 'innodb_buffer_pool_size' can't be set to the value of '2097151' + SHOW WARNINGS; + Level Code Message +-Warning 1210 innodb_buffer_pool_size must be at least MIN_VAL for innodb_page_size=PAGE_SIZE +-Error 1231 Variable 'innodb_buffer_pool_size' can't be set to the value of 'WRONG_VALUE' ++Warning 1210 innodb_buffer_pool_size must be at least 2097152 for innodb_page_size=4096 ++Error 1231 Variable 'innodb_buffer_pool_size' can't be set to the value of '2097151' + EXECUTE IMMEDIATE 'SET GLOBAL innodb_buffer_pool_size = ?' USING (@min_pool_size); + SET GLOBAL innodb_buffer_pool_size = @innodb_buffer_pool_size_orig; diff --git a/mysql-test/suite/innodb/r/restart,64k,innodb.rdiff b/mysql-test/suite/innodb/r/restart,64k,innodb.rdiff new file mode 100644 index 00000000000..3ac9f45b196 --- /dev/null +++ b/mysql-test/suite/innodb/r/restart,64k,innodb.rdiff @@ -0,0 +1,16 @@ +--- ./suite/innodb/r/restart.result 2022-01-18 20:36:56.054653376 +1100 ++++ suite/innodb/r/restart.reject 2022-01-19 08:11:32.418759095 +1100 +@@ -32,10 +32,10 @@ + SELECT @@innodb_buffer_pool_size INTO @innodb_buffer_pool_size_orig; + SELECT CEILING((256 + 64) * @@innodb_page_size / 1048576) * 1048576 INTO @min_pool_size; + EXECUTE IMMEDIATE 'SET GLOBAL innodb_buffer_pool_size = ?' USING (@min_pool_size -1); +-ERROR 42000: Variable 'innodb_buffer_pool_size' can't be set to the value of 'WRONG_VALUE' ++ERROR 42000: Variable 'innodb_buffer_pool_size' can't be set to the value of '20971519' + SHOW WARNINGS; + Level Code Message +-Warning 1210 innodb_buffer_pool_size must be at least MIN_VAL for innodb_page_size=PAGE_SIZE +-Error 1231 Variable 'innodb_buffer_pool_size' can't be set to the value of 'WRONG_VALUE' ++Warning 1210 innodb_buffer_pool_size must be at least 20971520 for innodb_page_size=65536 ++Error 1231 Variable 'innodb_buffer_pool_size' can't be set to the value of '20971519' + EXECUTE IMMEDIATE 'SET GLOBAL innodb_buffer_pool_size = ?' USING (@min_pool_size); + SET GLOBAL innodb_buffer_pool_size = @innodb_buffer_pool_size_orig; diff --git a/mysql-test/suite/innodb/r/restart,8k,innodb.rdiff b/mysql-test/suite/innodb/r/restart,8k,innodb.rdiff new file mode 100644 index 00000000000..4da55ebfcef --- /dev/null +++ b/mysql-test/suite/innodb/r/restart,8k,innodb.rdiff @@ -0,0 +1,16 @@ +--- ./suite/innodb/r/restart.result 2022-01-18 20:36:56.054653376 +1100 ++++ suite/innodb/r/restart.reject 2022-01-19 08:13:11.027788852 +1100 +@@ -32,10 +32,10 @@ + SELECT @@innodb_buffer_pool_size INTO @innodb_buffer_pool_size_orig; + SELECT CEILING((256 + 64) * @@innodb_page_size / 1048576) * 1048576 INTO @min_pool_size; + EXECUTE IMMEDIATE 'SET GLOBAL innodb_buffer_pool_size = ?' USING (@min_pool_size -1); +-ERROR 42000: Variable 'innodb_buffer_pool_size' can't be set to the value of 'WRONG_VALUE' ++ERROR 42000: Variable 'innodb_buffer_pool_size' can't be set to the value of '3145727' + SHOW WARNINGS; + Level Code Message +-Warning 1210 innodb_buffer_pool_size must be at least MIN_VAL for innodb_page_size=PAGE_SIZE +-Error 1231 Variable 'innodb_buffer_pool_size' can't be set to the value of 'WRONG_VALUE' ++Warning 1210 innodb_buffer_pool_size must be at least 3145728 for innodb_page_size=8192 ++Error 1231 Variable 'innodb_buffer_pool_size' can't be set to the value of '3145727' + EXECUTE IMMEDIATE 'SET GLOBAL innodb_buffer_pool_size = ?' USING (@min_pool_size); + SET GLOBAL innodb_buffer_pool_size = @innodb_buffer_pool_size_orig; diff --git a/mysql-test/suite/innodb/r/restart.result b/mysql-test/suite/innodb/r/restart.result index b2132bed267..2c786572cd8 100644 --- a/mysql-test/suite/innodb/r/restart.result +++ b/mysql-test/suite/innodb/r/restart.result @@ -28,3 +28,16 @@ a SELECT * FROM td; a DROP TABLE tr,tc,td; +# +# MDEV-27467 innodb to enfore the minimum innodb_buffer_pool_size in SET (resize) the same as startup +# +SELECT @@innodb_buffer_pool_size INTO @innodb_buffer_pool_size_orig; +SELECT CEILING((256 + 64) * @@innodb_page_size / 1048576) * 1048576 INTO @min_pool_size; +EXECUTE IMMEDIATE 'SET GLOBAL innodb_buffer_pool_size = ?' USING (@min_pool_size -1); +ERROR 42000: Variable 'innodb_buffer_pool_size' can't be set to the value of 'WRONG_VALUE' +SHOW WARNINGS; +Level Code Message +Warning 1210 innodb_buffer_pool_size must be at least MIN_VAL for innodb_page_size=PAGE_SIZE +Error 1231 Variable 'innodb_buffer_pool_size' can't be set to the value of 'WRONG_VALUE' +EXECUTE IMMEDIATE 'SET GLOBAL innodb_buffer_pool_size = ?' USING (@min_pool_size); +SET GLOBAL innodb_buffer_pool_size = @innodb_buffer_pool_size_orig; diff --git a/mysql-test/suite/innodb/r/skip_symbolic_links.result b/mysql-test/suite/innodb/r/skip_symbolic_links.result new file mode 100644 index 00000000000..e7a8a3cf86b --- /dev/null +++ b/mysql-test/suite/innodb/r/skip_symbolic_links.result @@ -0,0 +1,56 @@ +SELECT @@have_symlink; +@@have_symlink +DISABLED +CREATE TABLE t1(a INT) ENGINE=InnoDB DATA DIRECTORY 'MYSQL_TMP_DIR'; +Warnings: +Warning 1618 <DATA DIRECTORY> option ignored +DROP TABLE t1; +CREATE TABLE t1(a INT) ENGINE=InnoDB; +ALTER TABLE t1 DATA DIRECTORY 'MYSQL_TMP_DIR'; +Warnings: +Warning 1618 <DATA DIRECTORY> option ignored +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +DROP TABLE t1; +# restart: --symbolic-links +CREATE TABLE t1(a INT PRIMARY KEY, b INT) ENGINE=InnoDB +DATA DIRECTORY 'MYSQL_TMP_DIR'; +CREATE TABLE t2(a INT PRIMARY KEY, b INT) ENGINE=InnoDB +DATA DIRECTORY 'MYSQL_TMP_DIR'; +TRUNCATE TABLE t1; +TRUNCATE TABLE t2; +# restart +ALTER TABLE t1 FORCE, ALGORITHM=INPLACE; +Warnings: +Warning 1618 <DATA DIRECTORY> option ignored +ALTER TABLE t2 FORCE, ALGORITHM=COPY; +Warnings: +Warning 1618 <DATA DIRECTORY> option ignored +OPTIMIZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 optimize note Table does not support optimize, doing recreate + analyze instead +test.t1 optimize status OK +Warnings: +Warning 1618 <DATA DIRECTORY> option ignored +t1.ibd +DROP TABLE t2; +RENAME TABLE t1 TO t2; +ALTER TABLE t2 ADD UNIQUE INDEX(b), RENAME TO t3; +Warnings: +Warning 1618 <DATA DIRECTORY> option ignored +ALTER TABLE t3 RENAME TO t2; +ALTER TABLE t2 DROP INDEX b, RENAME TO t1; +Warnings: +Warning 1618 <DATA DIRECTORY> option ignored +ALTER TABLE t1 CHANGE b c INT; +Warnings: +Warning 1618 <DATA DIRECTORY> option ignored +ALTER TABLE t1 CHANGE c b INT NOT NULL; +Warnings: +Warning 1618 <DATA DIRECTORY> option ignored +t1.ibd +TRUNCATE TABLE t1; +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/101_compatibility.test b/mysql-test/suite/innodb/t/101_compatibility.test index eb1ec3b26ac..878daad2756 100644 --- a/mysql-test/suite/innodb/t/101_compatibility.test +++ b/mysql-test/suite/innodb/t/101_compatibility.test @@ -1,5 +1,6 @@ --source include/innodb_page_size.inc --source include/not_embedded.inc +--source include/have_symlink.inc -- echo # -- echo # MDEV-11623 MariaDB 10.1 fails to start datadir created with diff --git a/mysql-test/suite/innodb/t/create_isl_with_direct.test b/mysql-test/suite/innodb/t/create_isl_with_direct.test index 1427264e13e..2092d03b72f 100644 --- a/mysql-test/suite/innodb/t/create_isl_with_direct.test +++ b/mysql-test/suite/innodb/t/create_isl_with_direct.test @@ -1,5 +1,6 @@ --source include/not_embedded.inc --source include/have_innodb.inc +--source include/have_symlink.inc --disable_query_log CALL mtr.add_suppression(".*Failed to set O_DIRECT on file.*"); diff --git a/mysql-test/suite/innodb/t/innodb-wl5522.test b/mysql-test/suite/innodb/t/innodb-wl5522.test index 204c71ef3f1..7bad2ae9002 100644 --- a/mysql-test/suite/innodb/t/innodb-wl5522.test +++ b/mysql-test/suite/innodb/t/innodb-wl5522.test @@ -1072,6 +1072,43 @@ SELECT * FROM t1; DROP TABLE t1; + +CREATE TABLE t1 ( id INT NOT NULL, i1 INT, i2 INT, i3 INT, PRIMARY KEY (id)) engine=innodb; +CREATE TABLE t2 ( id INT NOT NULL, i1 INT, i2 INT, PRIMARY KEY (id)) engine=innodb; + +ALTER TABLE t2 DISCARD TABLESPACE; +FLUSH TABLES t1 FOR EXPORT; + +--copy_file $MYSQLD_DATADIR/test/t1.ibd $MYSQLD_DATADIR/test/t2.ibd +--copy_file $MYSQLD_DATADIR/test/t1.cfg $MYSQLD_DATADIR/test/t2.cfg + +UNLOCK TABLES; +--error ER_TABLE_SCHEMA_MISMATCH +ALTER TABLE t2 IMPORT TABLESPACE; + +DROP TABLE t1, t2; + + +CREATE TABLE t1 ( id INT NOT NULL, i1 INT, i2 INT, PRIMARY KEY (id)) engine=innodb; +CREATE TABLE t2 ( id INT NOT NULL, i1 INT, i2 INT, i3 INT, PRIMARY KEY (id)) engine=innodb; + +ALTER TABLE t2 DISCARD TABLESPACE; +FLUSH TABLES t1 FOR EXPORT; + +--copy_file $MYSQLD_DATADIR/test/t1.ibd $MYSQLD_DATADIR/test/t2.ibd +--copy_file $MYSQLD_DATADIR/test/t1.cfg $MYSQLD_DATADIR/test/t2.cfg + +UNLOCK TABLES; +--error ER_TABLE_SCHEMA_MISMATCH +ALTER TABLE t2 IMPORT TABLESPACE; + +--remove_file $MYSQLD_DATADIR/test/t2.ibd +--remove_file $MYSQLD_DATADIR/test/t2.cfg + + +DROP TABLE t1, t2; + + call mtr.add_suppression("Got error -1 when reading table '.*'"); call mtr.add_suppression("InnoDB: Error: tablespace id and flags in file '.*'"); call mtr.add_suppression("InnoDB: The table .* doesn't have a corresponding tablespace, it was discarded"); diff --git a/mysql-test/suite/innodb/t/innodb-wl5980-alter.test b/mysql-test/suite/innodb/t/innodb-wl5980-alter.test index 1a32b94c140..22b1306e902 100644 --- a/mysql-test/suite/innodb/t/innodb-wl5980-alter.test +++ b/mysql-test/suite/innodb/t/innodb-wl5980-alter.test @@ -4,6 +4,7 @@ --echo # --source include/have_innodb.inc +--source include/have_symlink.inc SET @innodb_file_per_table_orig=@@GLOBAL.innodb_file_per_table; LET $regexp=/FTS_[0-9a-f_]+([A-Z0-9_]+)\.([islbd]{3})/FTS_AUX_\1.\2/ /#sql-ib[1-9][0-9]*\.ibd\n//; diff --git a/mysql-test/suite/innodb/t/innodb-wl5980-debug.test b/mysql-test/suite/innodb/t/innodb-wl5980-debug.test index 2c5e2b48870..dbb8ad33676 100644 --- a/mysql-test/suite/innodb/t/innodb-wl5980-debug.test +++ b/mysql-test/suite/innodb/t/innodb-wl5980-debug.test @@ -8,6 +8,7 @@ --source include/not_embedded.inc --source include/have_debug.inc --source include/have_innodb.inc +--source include/have_symlink.inc # These messages are expected in the log call mtr.add_suppression("Cannot find space id [0-9]+ in the tablespace memory cache"); diff --git a/mysql-test/suite/innodb/t/innodb_buffer_pool_resize_with_chunks.opt b/mysql-test/suite/innodb/t/innodb_buffer_pool_resize_with_chunks.opt index b97a3995457..ade197de338 100644 --- a/mysql-test/suite/innodb/t/innodb_buffer_pool_resize_with_chunks.opt +++ b/mysql-test/suite/innodb/t/innodb_buffer_pool_resize_with_chunks.opt @@ -1,3 +1,3 @@ --innodb-buffer-pool-size=16M ---innodb-buffer-pool-chunk-size=2M +--innodb-buffer-pool-chunk-size=4M --innodb-page-size=4k diff --git a/mysql-test/suite/innodb/t/innodb_buffer_pool_resize_with_chunks.test b/mysql-test/suite/innodb/t/innodb_buffer_pool_resize_with_chunks.test index b04b0306bf1..7a26db65d33 100644 --- a/mysql-test/suite/innodb/t/innodb_buffer_pool_resize_with_chunks.test +++ b/mysql-test/suite/innodb/t/innodb_buffer_pool_resize_with_chunks.test @@ -49,7 +49,7 @@ drop table t1; drop view view0; # Try to shrink buffer pool to smaller than chunk size -set global innodb_buffer_pool_size = 1048576; +set global innodb_buffer_pool_size = 2*1048576; --source include/wait_condition.inc select @@innodb_buffer_pool_size; diff --git a/mysql-test/suite/innodb/t/innodb_information_schema.test b/mysql-test/suite/innodb/t/innodb_information_schema.test index 395fa779773..88e2d3601a6 100644 --- a/mysql-test/suite/innodb/t/innodb_information_schema.test +++ b/mysql-test/suite/innodb/t/innodb_information_schema.test @@ -148,6 +148,8 @@ if (!$success) -- echo Timeout waiting for rows in INNODB_LOCKS to appear } +--replace_column 6 # +--sorted_result SELECT lock_mode, lock_type, lock_table, lock_index, lock_rec, lock_data FROM INFORMATION_SCHEMA.INNODB_LOCKS ORDER BY lock_data; diff --git a/mysql-test/suite/innodb/t/instant_alter_import.test b/mysql-test/suite/innodb/t/instant_alter_import.test index 4bec3f8b7f5..fdf9f8e6cd9 100644 --- a/mysql-test/suite/innodb/t/instant_alter_import.test +++ b/mysql-test/suite/innodb/t/instant_alter_import.test @@ -2,6 +2,11 @@ --source include/have_sequence.inc --source include/innodb_checksum_algorithm.inc +--disable_query_log +call mtr.add_suppression("Table `test`.`t2` contains unrecognizable instant ALTER metadata"); +call mtr.add_suppression("Index for table 't2' is corrupt; try to repair it"); +--enable_query_log + set default_storage_engine=innodb; --echo # @@ -180,3 +185,21 @@ UNLOCK TABLES; ALTER TABLE t2 IMPORT TABLESPACE; DROP TABLE t2, t1; + + +CREATE TABLE t1 ( id INT NOT NULL, i1 INT, i2 INT, PRIMARY KEY (id)) engine=innodb; +CREATE TABLE t2 ( id INT NOT NULL, i1 INT, i2 INT, PRIMARY KEY (id)) engine=innodb; + +ALTER TABLE test.t1 add COLUMN i3 INT AFTER i1; + +ALTER TABLE t2 DISCARD TABLESPACE; +FLUSH TABLES t1 FOR EXPORT; + +--copy_file $MYSQLD_DATADIR/test/t1.ibd $MYSQLD_DATADIR/test/t2.ibd +--copy_file $MYSQLD_DATADIR/test/t1.cfg $MYSQLD_DATADIR/test/t2.cfg + +UNLOCK TABLES; +--error ER_NOT_KEYFILE +ALTER TABLE t2 IMPORT TABLESPACE; + +DROP TABLE t1, t2; diff --git a/mysql-test/suite/innodb/t/no_pad.test b/mysql-test/suite/innodb/t/no_pad.test new file mode 100644 index 00000000000..1be1972c9ca --- /dev/null +++ b/mysql-test/suite/innodb/t/no_pad.test @@ -0,0 +1,10 @@ +--source include/have_innodb.inc + +CREATE TABLE t1 (a CHAR(8), id INT, PRIMARY KEY (a,id)) COLLATE utf8_nopad_bin +ENGINE=InnoDB ROW_FORMAT=REDUNDANT; + +INSERT INTO t1 VALUES ('',1); +ALTER TABLE t1 ROW_FORMAT=DYNAMIC; +INSERT INTO t1 VALUES ('',2); +ALTER TABLE t1 ROW_FORMAT=REDUNDANT; +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/restart.opt b/mysql-test/suite/innodb/t/restart.opt new file mode 100644 index 00000000000..ce43e89cb2b --- /dev/null +++ b/mysql-test/suite/innodb/t/restart.opt @@ -0,0 +1,2 @@ +--loose-innodb_disable_resize_buffer_pool_debug=0 +--innodb-buffer-pool-chunk-size=1M diff --git a/mysql-test/suite/innodb/t/restart.test b/mysql-test/suite/innodb/t/restart.test index a7a7855ba7b..cd5e7532354 100644 --- a/mysql-test/suite/innodb/t/restart.test +++ b/mysql-test/suite/innodb/t/restart.test @@ -1,5 +1,6 @@ --source include/innodb_page_size.inc --source include/not_embedded.inc +--source include/have_symlink.inc let datadir= `select @@datadir`; let page_size= `select @@innodb_page_size`; @@ -77,3 +78,26 @@ SELECT * FROM tr; SELECT * FROM tc; SELECT * FROM td; DROP TABLE tr,tc,td; + +--echo # +--echo # MDEV-27467 innodb to enfore the minimum innodb_buffer_pool_size in SET (resize) the same as startup +--echo # + +let $wait_timeout = 180; +let $wait_condition = + SELECT SUBSTR(variable_value, 1, 34) = 'Completed resizing buffer pool at ' + FROM information_schema.global_status + WHERE LOWER(variable_name) = 'innodb_buffer_pool_resize_status'; + +SELECT @@innodb_buffer_pool_size INTO @innodb_buffer_pool_size_orig; +SELECT CEILING((256 + 64) * @@innodb_page_size / 1048576) * 1048576 INTO @min_pool_size; +--error ER_WRONG_VALUE_FOR_VAR +EXECUTE IMMEDIATE 'SET GLOBAL innodb_buffer_pool_size = ?' USING (@min_pool_size -1); + +SHOW WARNINGS; + +EXECUTE IMMEDIATE 'SET GLOBAL innodb_buffer_pool_size = ?' USING (@min_pool_size); + +--source include/wait_condition.inc + +SET GLOBAL innodb_buffer_pool_size = @innodb_buffer_pool_size_orig; diff --git a/mysql-test/suite/innodb/t/skip_symbolic_links.opt b/mysql-test/suite/innodb/t/skip_symbolic_links.opt new file mode 100644 index 00000000000..c7844699cdb --- /dev/null +++ b/mysql-test/suite/innodb/t/skip_symbolic_links.opt @@ -0,0 +1 @@ +--skip-symbolic-links diff --git a/mysql-test/suite/innodb/t/skip_symbolic_links.test b/mysql-test/suite/innodb/t/skip_symbolic_links.test new file mode 100644 index 00000000000..b5274d220c8 --- /dev/null +++ b/mysql-test/suite/innodb/t/skip_symbolic_links.test @@ -0,0 +1,51 @@ +--source include/have_innodb.inc +--source include/not_windows.inc + +SELECT @@have_symlink; +--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR +eval CREATE TABLE t1(a INT) ENGINE=InnoDB DATA DIRECTORY '$MYSQL_TMP_DIR'; +DROP TABLE t1; +CREATE TABLE t1(a INT) ENGINE=InnoDB; + +--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR +eval ALTER TABLE t1 DATA DIRECTORY '$MYSQL_TMP_DIR'; +SHOW CREATE TABLE t1; + +DROP TABLE t1; + +--let $restart_parameters=--symbolic-links +--source include/restart_mysqld.inc + +--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR +eval CREATE TABLE t1(a INT PRIMARY KEY, b INT) ENGINE=InnoDB +DATA DIRECTORY '$MYSQL_TMP_DIR'; +--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR +eval CREATE TABLE t2(a INT PRIMARY KEY, b INT) ENGINE=InnoDB +DATA DIRECTORY '$MYSQL_TMP_DIR'; + +TRUNCATE TABLE t1; +TRUNCATE TABLE t2; + +--let $restart_parameters= +--source include/restart_mysqld.inc + +# Native ALTER will retain DATA DIRECTORY +ALTER TABLE t1 FORCE, ALGORITHM=INPLACE; +ALTER TABLE t2 FORCE, ALGORITHM=COPY; +OPTIMIZE TABLE t1; +--list_files $MYSQL_TMP_DIR/test +DROP TABLE t2; + +RENAME TABLE t1 TO t2; +ALTER TABLE t2 ADD UNIQUE INDEX(b), RENAME TO t3; + +ALTER TABLE t3 RENAME TO t2; +ALTER TABLE t2 DROP INDEX b, RENAME TO t1; +ALTER TABLE t1 CHANGE b c INT; +ALTER TABLE t1 CHANGE c b INT NOT NULL; + +--list_files $MYSQL_TMP_DIR/test +# TRUNCATE TABLE will discard DATA DIRECTORY. +TRUNCATE TABLE t1; +--list_files $MYSQL_TMP_DIR/test +DROP TABLE t1; diff --git a/mysql-test/suite/innodb_fts/r/innodb_fts_plugin.result b/mysql-test/suite/innodb_fts/r/innodb_fts_plugin.result index ec417af97c5..0a408ab4ce7 100644 --- a/mysql-test/suite/innodb_fts/r/innodb_fts_plugin.result +++ b/mysql-test/suite/innodb_fts/r/innodb_fts_plugin.result @@ -161,6 +161,7 @@ id title body SELECT COUNT(*) FROM articles; COUNT(*) 5 +INSERT INTO articles (title, body) VALUES ('111', '1234 1234 1234'); DROP TABLE articles; # Test Part 5: Test Uninstall Plugin After Index is Built CREATE TABLE articles ( diff --git a/mysql-test/suite/innodb_fts/t/innodb_fts_plugin.test b/mysql-test/suite/innodb_fts/t/innodb_fts_plugin.test index cd31500b23f..b22ac456668 100644 --- a/mysql-test/suite/innodb_fts/t/innodb_fts_plugin.test +++ b/mysql-test/suite/innodb_fts/t/innodb_fts_plugin.test @@ -158,6 +158,8 @@ SELECT * FROM articles WHERE SELECT * FROM articles WHERE MATCH(title, body) AGAINST('full text search'); SELECT COUNT(*) FROM articles; + +INSERT INTO articles (title, body) VALUES ('111', '1234 1234 1234'); DROP TABLE articles; -- echo # Test Part 5: Test Uninstall Plugin After Index is Built diff --git a/mysql-test/suite/innodb_gis/r/rtree_split.result b/mysql-test/suite/innodb_gis/r/rtree_split.result index df88960ba3d..2d6e8a1dfbe 100644 --- a/mysql-test/suite/innodb_gis/r/rtree_split.result +++ b/mysql-test/suite/innodb_gis/r/rtree_split.result @@ -61,3 +61,10 @@ select count(*) from t1 where MBRWithin(t1.c2, @g1); count(*) 57344 drop table t1; +# +# MDEV-27417 Spatial index tries to update +# change buffer bookkeeping page +# +CREATE TEMPORARY TABLE t1 (c POINT NOT NULL, SPATIAL(c)) ENGINE=InnoDB; +INSERT INTO t1 SELECT PointFromText('POINT(0 0)') FROM seq_1_to_366; +DROP TABLE t1; diff --git a/mysql-test/suite/innodb_gis/t/rtree_split.test b/mysql-test/suite/innodb_gis/t/rtree_split.test index 6f285187508..8697141ddea 100644 --- a/mysql-test/suite/innodb_gis/t/rtree_split.test +++ b/mysql-test/suite/innodb_gis/t/rtree_split.test @@ -73,3 +73,11 @@ select count(*) from t1 where MBRWithin(t1.c2, @g1); # Clean up. drop table t1; + +--echo # +--echo # MDEV-27417 Spatial index tries to update +--echo # change buffer bookkeeping page +--echo # +CREATE TEMPORARY TABLE t1 (c POINT NOT NULL, SPATIAL(c)) ENGINE=InnoDB; +INSERT INTO t1 SELECT PointFromText('POINT(0 0)') FROM seq_1_to_366; +DROP TABLE t1; diff --git a/mysql-test/suite/innodb_zip/r/wl5522_zip.result b/mysql-test/suite/innodb_zip/r/wl5522_zip.result index c0fddf7e8ed..20666e33db1 100644 --- a/mysql-test/suite/innodb_zip/r/wl5522_zip.result +++ b/mysql-test/suite/innodb_zip/r/wl5522_zip.result @@ -319,7 +319,7 @@ SELECT * FROM t1; ERROR HY000: Tablespace has been discarded for table `t1` restore: t1 .ibd and .cfg files ALTER TABLE t1 IMPORT TABLESPACE; -ERROR HY000: Schema mismatch (Number of columns don't match, table has 6 columns but the tablespace meta-data file has 5 columns) +ERROR HY000: Schema mismatch (Column c3 not found in tablespace.) unlink: t1.ibd unlink: t1.cfg DROP TABLE t1; diff --git a/mysql-test/suite/innodb_zip/t/restart.test b/mysql-test/suite/innodb_zip/t/restart.test index 6a0e60f3ce6..0c1f11363e6 100644 --- a/mysql-test/suite/innodb_zip/t/restart.test +++ b/mysql-test/suite/innodb_zip/t/restart.test @@ -5,6 +5,7 @@ --source include/have_partition.inc --source include/not_embedded.inc --source include/innodb_checksum_algorithm.inc +--source include/have_symlink.inc SET default_storage_engine=InnoDB; LET $MYSQLD_DATADIR = `select @@datadir`; diff --git a/mysql-test/suite/maria/fulltext2.result b/mysql-test/suite/maria/fulltext2.result index 1e4e6636ef6..6cffc5cf404 100644 --- a/mysql-test/suite/maria/fulltext2.result +++ b/mysql-test/suite/maria/fulltext2.result @@ -84,3 +84,10 @@ select count(*) from t1 where match a against ('aaayyy' in boolean mode); count(*) 0 drop table t1; +set names utf8mb4; +create table t1 (a int, b text, fulltext (b)) charset=utf8mb4 collate=utf8mb4_unicode_ci engine=aria; +insert t1 values (1000, 'C͓̙̯͔̩ͅͅi̩̘̜̲a̯̲̬̳̜̖̤o͕͓̜͓̺̖̗,̠̬͚ ̺T͇̲h͈̱e ̬̜D̖o̦̖͔̗͖̩̘c̣̼t̝͉̫̮̗o͉̫̭r̙͎̗.͓̪̥'); +select a from t1 where match(b) against ('ciao' in boolean mode); +a +1000 +drop table t1; diff --git a/mysql-test/suite/maria/fulltext2.test b/mysql-test/suite/maria/fulltext2.test index 060b748eb4f..7bfbe600ff7 100644 --- a/mysql-test/suite/maria/fulltext2.test +++ b/mysql-test/suite/maria/fulltext2.test @@ -75,3 +75,12 @@ select count(*) from t1 where match a against ('aaaxxx' in boolean mode); select count(*) from t1 where match a against ('aaayyy' in boolean mode); drop table t1; + +# +# MDEV-11241 Certain combining marks cause MariaDB to crash when doing Full-Text searches +# +set names utf8mb4; +create table t1 (a int, b text, fulltext (b)) charset=utf8mb4 collate=utf8mb4_unicode_ci engine=aria; +insert t1 values (1000, 'C͓̙̯͔̩ͅͅi̩̘̜̲a̯̲̬̳̜̖̤o͕͓̜͓̺̖̗,̠̬͚ ̺T͇̲h͈̱e ̬̜D̖o̦̖͔̗͖̩̘c̣̼t̝͉̫̮̗o͉̫̭r̙͎̗.͓̪̥'); +select a from t1 where match(b) against ('ciao' in boolean mode); +drop table t1; diff --git a/mysql-test/suite/mariabackup/create_with_data_directory_during_backup.test b/mysql-test/suite/mariabackup/create_with_data_directory_during_backup.test index d0cb83d069f..f01028b6494 100644 --- a/mysql-test/suite/mariabackup/create_with_data_directory_during_backup.test +++ b/mysql-test/suite/mariabackup/create_with_data_directory_during_backup.test @@ -1,4 +1,5 @@ --source include/have_debug.inc +--source include/have_symlink.inc let $table_data_dir=$MYSQLTEST_VARDIR/tmp/ddir; let $targetdir=$MYSQLTEST_VARDIR/tmp/backup; mkdir $table_data_dir; diff --git a/mysql-test/suite/mariabackup/data_directory.test b/mysql-test/suite/mariabackup/data_directory.test index 50789a34c78..a89b7bdccc4 100644 --- a/mysql-test/suite/mariabackup/data_directory.test +++ b/mysql-test/suite/mariabackup/data_directory.test @@ -1,3 +1,4 @@ +--source include/have_symlink.inc let $table_data_dir=$MYSQLTEST_VARDIR/ddir; mkdir $table_data_dir; --replace_result $table_data_dir table_data_dir diff --git a/mysql-test/suite/mariabackup/partition_datadir.test b/mysql-test/suite/mariabackup/partition_datadir.test index c525d34a02c..36520d331bf 100644 --- a/mysql-test/suite/mariabackup/partition_datadir.test +++ b/mysql-test/suite/mariabackup/partition_datadir.test @@ -1,4 +1,5 @@ --source include/have_partition.inc +--source include/have_symlink.inc let $targetdir=$MYSQLTEST_VARDIR/backup; mkdir $targetdir; mkdir $MYSQLTEST_VARDIR/partitdata; diff --git a/mysql-test/suite/parts/t/alter_data_directory_innodb.test b/mysql-test/suite/parts/t/alter_data_directory_innodb.test index ac15e9bec6c..def04e14173 100644 --- a/mysql-test/suite/parts/t/alter_data_directory_innodb.test +++ b/mysql-test/suite/parts/t/alter_data_directory_innodb.test @@ -1,5 +1,6 @@ --source include/have_innodb.inc --source include/have_partition.inc +--source include/have_symlink.inc --echo # --echo # MDEV-15953 Alter InnoDB Partitioned Table Moves Files (which were originally not in the datadir) to the datadir diff --git a/mysql-test/suite/parts/t/reorganize_partition_innodb.test b/mysql-test/suite/parts/t/reorganize_partition_innodb.test index db73650c54b..77109c38c96 100644 --- a/mysql-test/suite/parts/t/reorganize_partition_innodb.test +++ b/mysql-test/suite/parts/t/reorganize_partition_innodb.test @@ -1,5 +1,6 @@ --source include/have_innodb.inc --source include/have_partition.inc +--source include/have_symlink.inc --echo # --echo # MDEV-15953 Alter InnoDB Partitioned Table Moves Files (which were originally not in the datadir) to the datadir diff --git a/mysql-test/suite/plugins/r/locales.result b/mysql-test/suite/plugins/r/locales.result index 106bf22923c..2ea88493705 100644 --- a/mysql-test/suite/plugins/r/locales.result +++ b/mysql-test/suite/plugins/r/locales.result @@ -17,7 +17,7 @@ ID NAME DESCRIPTION MAX_MONTH_NAME_LENGTH MAX_DAY_NAME_LENGTH DECIMAL_POINT THOU 14 cs_CZ Czech - Czech Republic 8 7 , czech 15 da_DK Danish - Denmark 9 7 , . danish 16 de_AT German - Austria 9 10 , german -17 es_ES Spanish - Spain 10 9 , spanish +17 es_ES Spanish - Spain 10 9 , . spanish 18 et_EE Estonian - Estonia 9 9 , estonian 19 eu_ES Basque - Basque 9 10 , english 20 fi_FI Finnish - Finland 9 11 , english @@ -82,24 +82,24 @@ ID NAME DESCRIPTION MAX_MONTH_NAME_LENGTH MAX_DAY_NAME_LENGTH DECIMAL_POINT THOU 79 en_ZA English - South Africa 9 9 . , english 80 en_ZW English - Zimbabwe 9 9 . , english 81 es_AR Spanish - Argentina 10 9 , . spanish -82 es_BO Spanish - Bolivia 10 9 , spanish -83 es_CL Spanish - Chile 10 9 , spanish -84 es_CO Spanish - Columbia 10 9 , spanish -85 es_CR Spanish - Costa Rica 10 9 . spanish -86 es_DO Spanish - Dominican Republic 10 9 . spanish -87 es_EC Spanish - Ecuador 10 9 , spanish -88 es_GT Spanish - Guatemala 10 9 . spanish -89 es_HN Spanish - Honduras 10 9 . spanish -90 es_MX Spanish - Mexico 10 9 . spanish -91 es_NI Spanish - Nicaragua 10 9 . spanish -92 es_PA Spanish - Panama 10 9 . spanish -93 es_PE Spanish - Peru 10 9 . spanish -94 es_PR Spanish - Puerto Rico 10 9 . spanish -95 es_PY Spanish - Paraguay 10 9 , spanish -96 es_SV Spanish - El Salvador 10 9 . spanish +82 es_BO Spanish - Bolivia 10 9 , . spanish +83 es_CL Spanish - Chile 10 9 , . spanish +84 es_CO Spanish - Columbia 10 9 , . spanish +85 es_CR Spanish - Costa Rica 10 9 , spanish +86 es_DO Spanish - Dominican Republic 10 9 . , spanish +87 es_EC Spanish - Ecuador 10 9 , . spanish +88 es_GT Spanish - Guatemala 10 9 . , spanish +89 es_HN Spanish - Honduras 10 9 . , spanish +90 es_MX Spanish - Mexico 10 9 . , spanish +91 es_NI Spanish - Nicaragua 10 9 . , spanish +92 es_PA Spanish - Panama 10 9 . , spanish +93 es_PE Spanish - Peru 10 9 . , spanish +94 es_PR Spanish - Puerto Rico 10 9 . , spanish +95 es_PY Spanish - Paraguay 10 9 , . spanish +96 es_SV Spanish - El Salvador 10 9 . , spanish 97 es_US Spanish - United States 10 9 . , spanish -98 es_UY Spanish - Uruguay 10 9 , spanish -99 es_VE Spanish - Venezuela 10 9 , spanish +98 es_UY Spanish - Uruguay 10 9 , . spanish +99 es_VE Spanish - Venezuela 10 9 , . spanish 100 fr_BE French - Belgium 9 8 , . french 101 fr_CA French - Canada 9 8 , french 102 fr_CH French - Switzerland 9 8 , french diff --git a/mysql-test/suite/plugins/r/pam.result b/mysql-test/suite/plugins/r/pam.result index 40075245d0c..b8588916169 100644 --- a/mysql-test/suite/plugins/r/pam.result +++ b/mysql-test/suite/plugins/r/pam.result @@ -40,6 +40,35 @@ test_pam@localhost pam_test@% test # Now, the magic number! PIN: 9212 +# +# MDEV-26339 Account specifics to be handled before proxying +# +alter user pam_test account lock; +alter user pam_test require subject 'foobar'; +alter user pam_test password expire; +Now, the magic number! +PIN: 9212 +select user(), current_user(), database(); +user() current_user() database() +test_pam@localhost pam_test@% test +alter user pam_test account unlock; +alter user pam_test require none; +alter user pam_test identified by ''; +show create user pam_test; +CREATE USER for pam_test@% +CREATE USER `pam_test`@`%` +alter user test_pam account lock; +Now, the magic number! +PIN: 9212 +alter user test_pam account unlock; +alter user test_pam require subject 'foobar'; +Now, the magic number! +PIN: 9212 +alter user test_pam require none; +alter user test_pam password expire; +Now, the magic number! +PIN: 9212 +select user(), current_user(), database(); drop user test_pam; drop user pam_test; create user PAM_TEST identified via pam using 'mariadb_mtr'; @@ -62,4 +91,21 @@ select user(), current_user(), database(); user() current_user() database() PAM_TEST@localhost PAM_TEST@% test drop user PAM_TEST; +# +# MDEV-27341 Use SET PASSWORD to change PAM service +# +create user pam_test identified via pam using 'mariadb_mtr'; +Challenge input first. +Enter: ************************* +Now, the magic number! +PIN: 9225 +select user(), current_user(), database(); +user() current_user() database() +pam_test@localhost pam_test@% test +set password='foo'; +ERROR HY000: SET PASSWORD is ignored for users authenticating via pam plugin +show create user; +CREATE USER for pam_test@% +CREATE USER `pam_test`@`%` IDENTIFIED VIA pam USING 'mariadb_mtr' +drop user pam_test; uninstall plugin pam; diff --git a/mysql-test/suite/plugins/t/pam.test b/mysql-test/suite/plugins/t/pam.test index 2611f25ee63..dbe108d1efa 100644 --- a/mysql-test/suite/plugins/t/pam.test +++ b/mysql-test/suite/plugins/t/pam.test @@ -45,7 +45,6 @@ EOF --echo # --echo # athentication is successful --echo # ---error 0 --exec $MYSQL_TEST -u test_pam -pgoodpassword < $MYSQLTEST_VARDIR/tmp/pam_good2.txt --echo # @@ -54,6 +53,36 @@ EOF --error 1 --exec $MYSQL_TEST -u test_pam -pbadpassword < $MYSQLTEST_VARDIR/tmp/pam_good2.txt +--echo # +--echo # MDEV-26339 Account specifics to be handled before proxying +--echo # + +# one can connect if the proxy account is locked +alter user pam_test account lock; +alter user pam_test require subject 'foobar'; +alter user pam_test password expire; +--error 0 +--exec $MYSQL_TEST -u test_pam -pgoodpassword < $MYSQLTEST_VARDIR/tmp/pam_good2.txt +alter user pam_test account unlock; +alter user pam_test require none; +alter user pam_test identified by ''; +show create user pam_test; + +#one cannot connect if the proxied account is locked +alter user test_pam account lock; +--error 1 +--exec $MYSQL_TEST -u test_pam -pgoodpassword < $MYSQLTEST_VARDIR/tmp/pam_good2.txt +alter user test_pam account unlock; + +alter user test_pam require subject 'foobar'; +--error 1 +--exec $MYSQL_TEST -u test_pam -pgoodpassword < $MYSQLTEST_VARDIR/tmp/pam_good2.txt +alter user test_pam require none; + +alter user test_pam password expire; +--error 1 +--exec $MYSQL_TEST -u test_pam -pgoodpassword < $MYSQLTEST_VARDIR/tmp/pam_good2.txt + drop user test_pam; drop user pam_test; create user PAM_TEST identified via pam using 'mariadb_mtr'; @@ -76,6 +105,22 @@ set global pam_winbind_workaround=1; --remove_file $MYSQLTEST_VARDIR/tmp/pam_ugly.txt drop user PAM_TEST; +--echo # +--echo # MDEV-27341 Use SET PASSWORD to change PAM service +--echo # +create user pam_test identified via pam using 'mariadb_mtr'; +--write_file $MYSQLTEST_VARDIR/tmp/setpwd.txt +not very secret challenge +9225 +select user(), current_user(), database(); +error ER_SET_PASSWORD_AUTH_PLUGIN; +set password='foo'; +show create user; +EOF +--exec $MYSQL_TEST -u pam_test < $MYSQLTEST_VARDIR/tmp/setpwd.txt +--remove_file $MYSQLTEST_VARDIR/tmp/setpwd.txt +drop user pam_test; + let $count_sessions= 1; --source include/wait_until_count_sessions.inc uninstall plugin pam; diff --git a/mysql-test/suite/rpl/r/rpl_seconds_behind_master_spike.result b/mysql-test/suite/rpl/r/rpl_seconds_behind_master_spike.result new file mode 100644 index 00000000000..be18f95c2c0 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_seconds_behind_master_spike.result @@ -0,0 +1,40 @@ +include/master-slave.inc +[connection master] +connection slave; +include/stop_slave.inc +SET @save_dbug= @@GLOBAL.debug_dbug; +SET @@global.debug_dbug="+d,pause_sql_thread_on_fde"; +include/start_slave.inc +# Future events must be logged at least 2 seconds after +# the slave starts +connection master; +# Write events to ensure slave will be consistent with master +create table t1 (a int); +insert into t1 values (1); +# Flush logs on master forces slave to generate a Format description +# event in its relay log +flush logs; +connection slave; +# Ignore FDEs that happen before the CREATE/INSERT commands +SET DEBUG_SYNC='now WAIT_FOR paused_on_fde'; +SET DEBUG_SYNC='now SIGNAL sql_thread_continue'; +SET DEBUG_SYNC='now WAIT_FOR paused_on_fde'; +SET DEBUG_SYNC='now SIGNAL sql_thread_continue'; +# On the next FDE, the slave should have the master CREATE/INSERT events +SET DEBUG_SYNC='now WAIT_FOR paused_on_fde'; +select count(*)=1 from t1; +count(*)=1 +1 +# The relay log FDE has been processed - here we check to ensure it was +# not considered in Seconds_Behind_Master calculation +connection slave1; +# Safely resume slave SQL thread +SET @@global.debug_dbug=''; +SET DEBUG_SYNC='pause_sql_thread_on_fde CLEAR'; +SET DEBUG_SYNC='now SIGNAL sql_thread_continue'; +SET DEBUG_SYNC='RESET'; +connection master; +DROP TABLE t1; +connection slave; +SET @@global.debug_dbug=$save_dbug; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_seconds_behind_master_spike.test b/mysql-test/suite/rpl/t/rpl_seconds_behind_master_spike.test new file mode 100644 index 00000000000..029625a09ad --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_seconds_behind_master_spike.test @@ -0,0 +1,88 @@ +# +# Purpose: +# This test validates that a slave's relay log format description event is +# not used to calculate the Seconds_Behind_Master time displayed by +# SHOW SLAVE STATUS. +# +# Methodology: +# Ensure that a slave's reported Seconds_Behind_Master does not point before +# a time in which we can prove that it has progressed beyond. The slave's +# relay log events are created using the timestamp at which the IO thread was +# created. Therefore, after starting the slave's IO thread, we sleep so any +# proceeding events are forced to have later timestamps. After sleeping, we run +# MDL statements on the master and save the time at which they are binlogged. +# Once the slave executes these MDL commands, we have proven that the slave has +# caught up to this saved timestamp. At this point, if the value of +# Seconds_Behind_Master points before the time in which the MDL events were +# logged, it is invalid. +# +# References: +# MDEV-16091: Seconds_Behind_Master spikes to millions of seconds +# +--source include/have_debug.inc +--source include/have_innodb.inc +--source include/master-slave.inc + +--connection slave +--source include/stop_slave.inc +SET @save_dbug= @@GLOBAL.debug_dbug; +SET @@global.debug_dbug="+d,pause_sql_thread_on_fde"; +--source include/start_slave.inc + +--let $sleep_time=2 +--echo # Future events must be logged at least $sleep_time seconds after +--echo # the slave starts +--sleep $sleep_time + +--connection master +--echo # Write events to ensure slave will be consistent with master +create table t1 (a int); +insert into t1 values (1); +--let $t_master_events_logged= `SELECT UNIX_TIMESTAMP()` + +--echo # Flush logs on master forces slave to generate a Format description +--echo # event in its relay log +flush logs; + +--connection slave +--echo # Ignore FDEs that happen before the CREATE/INSERT commands +SET DEBUG_SYNC='now WAIT_FOR paused_on_fde'; +SET DEBUG_SYNC='now SIGNAL sql_thread_continue'; +SET DEBUG_SYNC='now WAIT_FOR paused_on_fde'; +SET DEBUG_SYNC='now SIGNAL sql_thread_continue'; + +--echo # On the next FDE, the slave should have the master CREATE/INSERT events +SET DEBUG_SYNC='now WAIT_FOR paused_on_fde'; +select count(*)=1 from t1; + +--echo # The relay log FDE has been processed - here we check to ensure it was +--echo # not considered in Seconds_Behind_Master calculation +--connection slave1 +let $sbm= query_get_value(SHOW SLAVE STATUS, Seconds_Behind_Master, 1); +--let $t_now= `SELECT UNIX_TIMESTAMP()` + +# Ensure Seconds_Behind_Master does not point beyond when we have proven the +# events we have proven to have executed. The extra second is needed as a +# buffer because the recorded times are not exact with when the events were +# recorded on the master. +if(`select $sbm > $t_now - $t_master_events_logged + 1`) +{ + die "A relay log event was incorrectly used to set Seconds_Behind_Master"; +} + +--echo # Safely resume slave SQL thread +SET @@global.debug_dbug=''; +SET DEBUG_SYNC='pause_sql_thread_on_fde CLEAR'; +SET DEBUG_SYNC='now SIGNAL sql_thread_continue'; + +# Reset last sql_thread_continue signal +SET DEBUG_SYNC='RESET'; + +# Cleanup +--connection master +DROP TABLE t1; + +--connection slave +SET @@global.debug_dbug=$save_dbug; + +--source include/rpl_end.inc diff --git a/mysql-test/suite/sys_vars/r/sysvars_innodb.result b/mysql-test/suite/sys_vars/r/sysvars_innodb.result index ecd107f67c8..c0803ed765b 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_innodb.result +++ b/mysql-test/suite/sys_vars/r/sysvars_innodb.result @@ -279,7 +279,7 @@ DEFAULT_VALUE 134217728 VARIABLE_SCOPE GLOBAL VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT The size of the memory buffer InnoDB uses to cache data and indexes of its tables. -NUMERIC_MIN_VALUE 5242880 +NUMERIC_MIN_VALUE 2097152 NUMERIC_MAX_VALUE 9223372036854775807 NUMERIC_BLOCK_SIZE 1048576 ENUM_VALUE_LIST NULL diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_embedded,32bit.rdiff b/mysql-test/suite/sys_vars/r/sysvars_server_embedded,32bit.rdiff index c8049d28c35..129305dafb7 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded,32bit.rdiff +++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded,32bit.rdiff @@ -1,7 +1,7 @@ ---- suite/sys_vars/r/sysvars_server_embedded.result 2021-02-02 14:07:21.616784062 +0200 -+++ suite/sys_vars/r/sysvars_server_embedded,32bit.reject 2021-02-02 18:56:53.727764717 +0200 -@@ -35,7 +35,7 @@ - COMMAND_LINE_ARGUMENT REQUIRED +--- ../../mysql-test/suite/sys_vars/r/sysvars_server_embedded.result 2022-01-27 20:42:19.039084441 +0200 ++++ ../../mysql-test/suite/sys_vars/r/sysvars_server_embedded,32bit.result~ 2022-01-28 16:12:40.038627481 +0200 +@@ -14,7 +14,7 @@ + order by variable_name; VARIABLE_NAME ARIA_BLOCK_SIZE VARIABLE_SCOPE GLOBAL -VARIABLE_TYPE BIGINT UNSIGNED @@ -782,7 +782,6 @@ VARIABLE_COMMENT Size of the statement digest. Use 0 to disable, -1 for automated sizing. NUMERIC_MIN_VALUE -1 NUMERIC_MAX_VALUE 1048576 -@@ -2345,7 +2345,7 @@ COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME PERFORMANCE_SCHEMA_EVENTS_STAGES_HISTORY_LONG_SIZE VARIABLE_SCOPE GLOBAL diff --git a/mysql-test/suite/vcol/r/not_supported.result b/mysql-test/suite/vcol/r/not_supported.result index c804cf220d2..d8703f755da 100644 --- a/mysql-test/suite/vcol/r/not_supported.result +++ b/mysql-test/suite/vcol/r/not_supported.result @@ -34,3 +34,11 @@ select * from t8; a b v 1234567890 2 2009-02-14 00:31:30 drop table t1, t3_ok, t8; +# +# Bug#33141966 - INCONSISTENT BEHAVIOR IF A COLUMN OF TYPE SERIAL IS SET AS GENERATED +# +create table t1 (a int, b serial as (a+1)); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'as (a+1))' at line 1 +# +# End of 10.2 tests +# diff --git a/mysql-test/suite/vcol/t/not_supported.test b/mysql-test/suite/vcol/t/not_supported.test index 1ea7970523a..2b5baf4ff4b 100644 --- a/mysql-test/suite/vcol/t/not_supported.test +++ b/mysql-test/suite/vcol/t/not_supported.test @@ -39,3 +39,13 @@ select * from t1; select * from t8; drop table t1, t3_ok, t8; + +--echo # +--echo # Bug#33141966 - INCONSISTENT BEHAVIOR IF A COLUMN OF TYPE SERIAL IS SET AS GENERATED +--echo # +--error ER_PARSE_ERROR +create table t1 (a int, b serial as (a+1)); + +--echo # +--echo # End of 10.2 tests +--echo # diff --git a/mysql-test/suite/versioning/r/create.result b/mysql-test/suite/versioning/r/create.result index 165f33b4547..dbef4546043 100644 --- a/mysql-test/suite/versioning/r/create.result +++ b/mysql-test/suite/versioning/r/create.result @@ -631,8 +631,32 @@ Table Create Table t1 CREATE TABLE `t1` ( `x` int(11) DEFAULT NULL WITHOUT SYSTEM VERSIONING, `y` int(11) DEFAULT NULL, - `row_start` timestamp(6) GENERATED ALWAYS AS ROW START WITHOUT SYSTEM VERSIONING, - `row_end` timestamp(6) GENERATED ALWAYS AS ROW END WITHOUT SYSTEM VERSIONING, + `row_start` timestamp(6) GENERATED ALWAYS AS ROW START, + `row_end` timestamp(6) GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (`row_start`, `row_end`) ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING drop table t1; +# +# MDEV-27452 TIMESTAMP(0) system field is allowed for certain creation of system-versioned table +# +create or replace table t ( +a int, +s timestamp as row start, +e timestamp as row end, +period for system_time (s, e)) +with system versioning; +ERROR HY000: `s` must be of type TIMESTAMP(6) for system-versioned table `t` +create or replace table t ( +a int with system versioning, +s timestamp as row start, +e timestamp as row end, +period for system_time (s, e)); +ERROR HY000: `s` must be of type TIMESTAMP(6) for system-versioned table `t` +create or replace table t ( +a int with system versioning, +b int with system versioning, +s timestamp(6) as row start, +e timestamp(6) as row end, +period for system_time (s, e)); +insert into t () values (),(); +drop table t; diff --git a/mysql-test/suite/versioning/r/truncate_privilege.result b/mysql-test/suite/versioning/r/not_embedded.result index e2b48c494be..f17ab18349c 100644 --- a/mysql-test/suite/versioning/r/truncate_privilege.result +++ b/mysql-test/suite/versioning/r/not_embedded.result @@ -1,3 +1,39 @@ +# +# SYSTEM_VERSIONING_ASOF sysvar +# +create table t (a int) with system versioning; +set @before= UNIX_TIMESTAMP(now(6)); +insert into t values (1); +set @after= UNIX_TIMESTAMP(now(6)); +update t set a= 2; +set global system_versioning_asof= FROM_UNIXTIME(@after); +set system_versioning_asof= FROM_UNIXTIME(@after); +select * from t as nonempty; +a +1 +connect subcon,127.0.0.1,root,,,$SERVER_MYPORT_1; +connection subcon; +select * from t as nonempty; +a +1 +disconnect subcon; +connection default; +set global system_versioning_asof= FROM_UNIXTIME(@before); +select * from t as nonempty; +a +1 +connect subcon,127.0.0.1,root,,,$SERVER_MYPORT_1; +connection subcon; +select * from t as empty; +a +disconnect subcon; +connection default; +drop table t; +set global system_versioning_asof= DEFAULT; +set system_versioning_asof= DEFAULT; +# +# DELETE HISTORY and privileges +# connect root,localhost,root,,test; connection root; create database mysqltest; @@ -31,3 +67,6 @@ GRANT DELETE HISTORY ON `mysqltest`.* TO `mysqltest_1`@`localhost` GRANT DELETE HISTORY ON `mysqltest`.`t` TO `mysqltest_1`@`localhost` drop user mysqltest_1@localhost; drop database mysqltest; +disconnect user1; +disconnect root; +connection default; diff --git a/mysql-test/suite/versioning/r/partition.result b/mysql-test/suite/versioning/r/partition.result index 7b359007408..97e53908e60 100644 --- a/mysql-test/suite/versioning/r/partition.result +++ b/mysql-test/suite/versioning/r/partition.result @@ -912,6 +912,18 @@ t1 CREATE TABLE `t1` ( PARTITION `ver_pn` CURRENT ENGINE = DEFAULT_ENGINE) set timestamp= default; # +# MDEV-18794 Assertion `!m_innodb' failed in ha_partition::cmp_ref upon SELECT from partitioned table +# +create or replace table t1 (pk int auto_increment, i int, c char(1), primary key (pk), key(i)) +engine=innodb with system versioning partition by key() partitions 2; +insert into t1 (i, c) values (1, 'a'), (2, 'b'), (null, 'c'), (null, 'b'); +alter table t1 drop system versioning; +replace into t1 select * from t1; +select * from t1 where i > 0 or pk = 1000 limit 1; +pk i c +1 1 a +drop table t1; +# # MDEV-19175 Server crashes in ha_partition::vers_can_native upon INSERT DELAYED into versioned partitioned table # create or replace table t1 (f int) with system versioning partition by hash(f); @@ -1148,6 +1160,38 @@ alter table t1 add x serial; alter table t1 add partition (partition p1 history); alter table t1 add partition (partition p2 history); drop table t1; +# +# MDEV-27217 DELETE partition selection doesn't work for history partitions +# +create table t1 (f char) with system versioning +partition by system_time limit 10 ( +partition p0 history, +partition p1 history, +partition p2 history, +partition pn current); +delete from t1 partition (p1); +ERROR HY000: Not allowed for system-versioned table `test`.`t1` +delete from t1 partition (p0, pn); +ERROR HY000: Not allowed for system-versioned table `test`.`t1` +delete from t1 partition (p0, p1); +ERROR HY000: Not allowed for system-versioned table `test`.`t1` +delete from t1 partition (p0, p1, pn); +ERROR HY000: Not allowed for system-versioned table `test`.`t1` +drop table t1; +set timestamp=unix_timestamp('2000-01-01 00:00:00'); +create or replace table t1 (i int) with system versioning +partition by system_time interval 1 day ( +partition p0 history, +partition p1 history, +partition pn current); +set timestamp=unix_timestamp('2000-01-02 00:00:00'); +insert t1 values (1); +delete from t1 partition (p0, pn); +ERROR HY000: Not allowed for system-versioned table `test`.`t1` +delete from t1 partition (p0, p1, pn); +ERROR HY000: Not allowed for system-versioned table `test`.`t1` +drop table t1; +set timestamp= default; # End of 10.3 tests # # MDEV-22283 Server crashes in key_copy or unexpected error 156: The table already existed in the storage engine diff --git a/mysql-test/suite/versioning/r/sysvars-notembedded.result b/mysql-test/suite/versioning/r/sysvars-notembedded.result deleted file mode 100644 index 8b1ad6cfc58..00000000000 --- a/mysql-test/suite/versioning/r/sysvars-notembedded.result +++ /dev/null @@ -1,30 +0,0 @@ -create table t (a int) with system versioning; -set @before= UNIX_TIMESTAMP(now(6)); -insert into t values (1); -set @after= UNIX_TIMESTAMP(now(6)); -update t set a= 2; -set global system_versioning_asof= FROM_UNIXTIME(@after); -set system_versioning_asof= FROM_UNIXTIME(@after); -select * from t as nonempty; -a -1 -connect subcon,127.0.0.1,root,,,$SERVER_MYPORT_1; -connection subcon; -select * from t as nonempty; -a -1 -disconnect subcon; -connection default; -set global system_versioning_asof= FROM_UNIXTIME(@before); -select * from t as nonempty; -a -1 -connect subcon,127.0.0.1,root,,,$SERVER_MYPORT_1; -connection subcon; -select * from t as empty; -a -disconnect subcon; -connection default; -drop table t; -set global system_versioning_asof= DEFAULT; -set system_versioning_asof= DEFAULT; diff --git a/mysql-test/suite/versioning/r/trx_id.result b/mysql-test/suite/versioning/r/trx_id.result index f09284a61ae..5182da32d4d 100644 --- a/mysql-test/suite/versioning/r/trx_id.result +++ b/mysql-test/suite/versioning/r/trx_id.result @@ -175,7 +175,78 @@ select x, row_start < row_end from t1 for system_time all; x row_start < row_end 4 1 2 1 +# +# MDEV-15951 system versioning by trx id doesn't work with partitioning +# currently trx_id does not support partitioning by system_time +# +create or replace table t1( +i int, +row_start bigint unsigned generated always as row start, +row_end bigint unsigned generated always as row end, +period for system_time(row_start, row_end) +) engine=InnoDB with system versioning partition by system_time ( +partition p0 history, +partition pn current +); +ERROR HY000: `row_start` must be of type TIMESTAMP(6) for system-versioned table `t1` +create or replace table t1( +i int, +row_start bigint unsigned generated always as row start, +row_end bigint unsigned generated always as row end, +period for system_time(row_start, row_end) +) engine=InnoDB with system versioning; +alter table t1 partition by system_time ( +partition p0 history, +partition pn current +); +ERROR HY000: `row_start` must be of type TIMESTAMP(6) for system-versioned table `t1` +drop table t1; +create or replace table t ( +a int primary key, +row_start bigint unsigned as row start invisible, +row_end bigint unsigned as row end invisible, +period for system_time(row_start, row_end) +) engine=innodb with system versioning +partition by key() ( +partition p1, +partition p2 +); +ERROR HY000: Transaction-precise system-versioned tables do not support partitioning by ROW START or ROW END +create or replace table t ( +a int primary key, +row_start bigint unsigned as row start invisible, +row_end bigint unsigned as row end invisible, +period for system_time(row_start, row_end) +) engine=innodb with system versioning +partition by key(a, row_start) ( +partition p1, +partition p2 +); +ERROR HY000: Transaction-precise system-versioned tables do not support partitioning by ROW START or ROW END +create or replace table t ( +a int primary key, +row_start bigint unsigned as row start invisible, +row_end bigint unsigned as row end invisible, +period for system_time(row_start, row_end) +) engine=innodb with system versioning +partition by hash(a + row_end * 2) ( +partition p1, +partition p2 +); +ERROR HY000: Transaction-precise system-versioned tables do not support partitioning by ROW START or ROW END +create or replace table t ( +a int primary key, +row_start bigint unsigned as row start invisible, +row_end bigint unsigned as row end invisible, +period for system_time(row_start, row_end) +) engine=innodb with system versioning +partition by range columns (a, row_start) ( +partition p1 values less than (100, 100) +); +ERROR HY000: Transaction-precise system-versioned tables do not support partitioning by ROW START or ROW END +# # MDEV-16010 Too many rows with AS OF point_in_the_past_or_NULL +# create or replace table t1 ( x int, row_start bigint unsigned as row start invisible, @@ -505,3 +576,31 @@ drop table t; uninstall plugin test_versioning; select trt_begin_ts(0); ERROR 42000: FUNCTION test.trt_begin_ts does not exist +# +# MDEV-21650 Non-empty statement transaction on global rollback after TRT update error +# +create table t1 (s date, e date, period for app(s,e)) engine=innodb; +alter table t1 +add row_start bigint unsigned as row start, +add row_end bigint unsigned as row end, +add period for system_time(row_start,row_end), +with system versioning, +add period if not exists for app(x,y); +Warnings: +Note 1060 Duplicate column name 'app' +set transaction isolation level serializable; +start transaction; +insert into t1 (s,e) values ('2021-07-04','2024-08-18'); +connect con1,localhost,root,,test; +start transaction; +insert into t1 (s,e) values ('2018-06-01','2021-09-15'); +connection default; +select * from t1 for system_time as of now(); +ERROR HY000: TRX_ID ... not found in `mysql.transaction_registry` +connection con1; +set innodb_lock_wait_timeout= 1, lock_wait_timeout= 1; +alter table xx; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +disconnect con1; +connection default; +drop table t1; diff --git a/mysql-test/suite/versioning/r/update.result b/mysql-test/suite/versioning/r/update.result index 18872928174..addf1cf4577 100644 --- a/mysql-test/suite/versioning/r/update.result +++ b/mysql-test/suite/versioning/r/update.result @@ -399,8 +399,19 @@ a check_row(row_start, row_end) 1 CURRENT ROW drop tables t1, t2, t3; # +# MDEV-26778 row_start is not updated in current row for InnoDB +# +create or replace table t1 (x int) with system versioning; +insert t1 values (1); +update t1 set x= 1; +select row_start into @r from t1; +select check_row_ts(row_start, row_end) from t1 for system_time all where row_start = @r; +check_row_ts(row_start, row_end) +CURRENT ROW +drop table t1; +# # MDEV-24522 Assertion `inited==NONE' fails upon UPDATE on versioned table with unique blob - +# create table t1 (a int, b int, c text, unique(c), key (b)) engine=myisam with system versioning; insert into t1 values (1, 1, 'foo'), (2, 11, 'bar'); update t1 set a = 3 where b <= 9; diff --git a/mysql-test/suite/versioning/t/create.test b/mysql-test/suite/versioning/t/create.test index 61fac4d6a1f..f50d75ded9e 100644 --- a/mysql-test/suite/versioning/t/create.test +++ b/mysql-test/suite/versioning/t/create.test @@ -468,3 +468,32 @@ create or replace table t1 ( show create table t1; drop table t1; + +--echo # +--echo # MDEV-27452 TIMESTAMP(0) system field is allowed for certain creation of system-versioned table +--echo # +--error ER_VERS_FIELD_WRONG_TYPE +create or replace table t ( + a int, + s timestamp as row start, + e timestamp as row end, + period for system_time (s, e)) +with system versioning; + +--error ER_VERS_FIELD_WRONG_TYPE +create or replace table t ( + a int with system versioning, + s timestamp as row start, + e timestamp as row end, + period for system_time (s, e)); + +create or replace table t ( + a int with system versioning, + b int with system versioning, + s timestamp(6) as row start, + e timestamp(6) as row end, + period for system_time (s, e)); +insert into t () values (),(); + +# cleanup +drop table t; diff --git a/mysql-test/suite/versioning/t/not_embedded.test b/mysql-test/suite/versioning/t/not_embedded.test new file mode 100644 index 00000000000..2afae013e70 --- /dev/null +++ b/mysql-test/suite/versioning/t/not_embedded.test @@ -0,0 +1,79 @@ +--source include/not_embedded.inc +--source include/have_innodb.inc + +--echo # +--echo # SYSTEM_VERSIONING_ASOF sysvar +--echo # +create table t (a int) with system versioning; +set @before= UNIX_TIMESTAMP(now(6)); +insert into t values (1); +set @after= UNIX_TIMESTAMP(now(6)); +update t set a= 2; + +set global system_versioning_asof= FROM_UNIXTIME(@after); +set system_versioning_asof= FROM_UNIXTIME(@after); +select * from t as nonempty; + +--connect (subcon,127.0.0.1,root,,,$SERVER_MYPORT_1) +--connection subcon +select * from t as nonempty; +--disconnect subcon +--connection default + +set global system_versioning_asof= FROM_UNIXTIME(@before); +select * from t as nonempty; + +--connect (subcon,127.0.0.1,root,,,$SERVER_MYPORT_1) +--connection subcon +select * from t as empty; +--disconnect subcon +--connection default + +drop table t; + +set global system_versioning_asof= DEFAULT; +set system_versioning_asof= DEFAULT; + +--echo # +--echo # DELETE HISTORY and privileges +--echo # + +# Save the initial number of concurrent sessions +--source include/count_sessions.inc + +connect (root,localhost,root,,test); +connection root; + +--disable_warnings +create database mysqltest; +--enable_warnings + +create user mysqltest_1@localhost; +connect (user1,localhost,mysqltest_1,,test); +connection user1; + +connection root; +create table mysqltest.t (a int) with system versioning; + +connection user1; +show grants; +--error ER_TABLEACCESS_DENIED_ERROR +delete history from mysqltest.t before system_time now(); + +connection root; +grant delete history on mysqltest.* to mysqltest_1@localhost; +grant delete history on mysqltest.t to mysqltest_1@localhost; + +connection user1; +show grants; +delete history from mysqltest.t before system_time now(); + +connection root; +grant all on *.* to mysqltest_1@localhost; +show grants for mysqltest_1@localhost; + +drop user mysqltest_1@localhost; +drop database mysqltest; +--disconnect user1 +--disconnect root +--connection default diff --git a/mysql-test/suite/versioning/t/partition.test b/mysql-test/suite/versioning/t/partition.test index d13993042c8..92e5272ed9f 100644 --- a/mysql-test/suite/versioning/t/partition.test +++ b/mysql-test/suite/versioning/t/partition.test @@ -772,6 +772,17 @@ show create table t1; set timestamp= default; --echo # +--echo # MDEV-18794 Assertion `!m_innodb' failed in ha_partition::cmp_ref upon SELECT from partitioned table +--echo # +create or replace table t1 (pk int auto_increment, i int, c char(1), primary key (pk), key(i)) +engine=innodb with system versioning partition by key() partitions 2; +insert into t1 (i, c) values (1, 'a'), (2, 'b'), (null, 'c'), (null, 'b'); +alter table t1 drop system versioning; +replace into t1 select * from t1; +select * from t1 where i > 0 or pk = 1000 limit 1; +drop table t1; + +--echo # --echo # MDEV-19175 Server crashes in ha_partition::vers_can_native upon INSERT DELAYED into versioned partitioned table --echo # create or replace table t1 (f int) with system versioning partition by hash(f); @@ -1024,6 +1035,40 @@ alter table t1 add partition (partition p1 history); alter table t1 add partition (partition p2 history); drop table t1; +--echo # +--echo # MDEV-27217 DELETE partition selection doesn't work for history partitions +--echo # +create table t1 (f char) with system versioning +partition by system_time limit 10 ( + partition p0 history, + partition p1 history, + partition p2 history, + partition pn current); + +--error ER_VERS_NOT_ALLOWED +delete from t1 partition (p1); +--error ER_VERS_NOT_ALLOWED +delete from t1 partition (p0, pn); +--error ER_VERS_NOT_ALLOWED +delete from t1 partition (p0, p1); +--error ER_VERS_NOT_ALLOWED +delete from t1 partition (p0, p1, pn); +drop table t1; + +set timestamp=unix_timestamp('2000-01-01 00:00:00'); +create or replace table t1 (i int) with system versioning +partition by system_time interval 1 day ( + partition p0 history, + partition p1 history, + partition pn current); +set timestamp=unix_timestamp('2000-01-02 00:00:00'); +insert t1 values (1); +--error ER_VERS_NOT_ALLOWED +delete from t1 partition (p0, pn); +--error ER_VERS_NOT_ALLOWED +delete from t1 partition (p0, p1, pn); +drop table t1; +set timestamp= default; --echo # End of 10.3 tests --echo # diff --git a/mysql-test/suite/versioning/t/sysvars-notembedded.test b/mysql-test/suite/versioning/t/sysvars-notembedded.test deleted file mode 100644 index 314972bc375..00000000000 --- a/mysql-test/suite/versioning/t/sysvars-notembedded.test +++ /dev/null @@ -1,31 +0,0 @@ -source include/not_embedded.inc; - -create table t (a int) with system versioning; -set @before= UNIX_TIMESTAMP(now(6)); -insert into t values (1); -set @after= UNIX_TIMESTAMP(now(6)); -update t set a= 2; - -set global system_versioning_asof= FROM_UNIXTIME(@after); -set system_versioning_asof= FROM_UNIXTIME(@after); -select * from t as nonempty; - ---connect (subcon,127.0.0.1,root,,,$SERVER_MYPORT_1) ---connection subcon -select * from t as nonempty; ---disconnect subcon ---connection default - -set global system_versioning_asof= FROM_UNIXTIME(@before); -select * from t as nonempty; - ---connect (subcon,127.0.0.1,root,,,$SERVER_MYPORT_1) ---connection subcon -select * from t as empty; ---disconnect subcon ---connection default - -drop table t; - -set global system_versioning_asof= DEFAULT; -set system_versioning_asof= DEFAULT; diff --git a/mysql-test/suite/versioning/t/truncate_privilege.test b/mysql-test/suite/versioning/t/truncate_privilege.test deleted file mode 100644 index dcdad59039a..00000000000 --- a/mysql-test/suite/versioning/t/truncate_privilege.test +++ /dev/null @@ -1,41 +0,0 @@ -# Can't test with embedded server --- source include/not_embedded.inc - ---source include/have_innodb.inc - -# Save the initial number of concurrent sessions ---source include/count_sessions.inc - -connect (root,localhost,root,,test); -connection root; - ---disable_warnings -create database mysqltest; ---enable_warnings - -create user mysqltest_1@localhost; -connect (user1,localhost,mysqltest_1,,test); -connection user1; - -connection root; -create table mysqltest.t (a int) with system versioning; - -connection user1; -show grants; ---error ER_TABLEACCESS_DENIED_ERROR -delete history from mysqltest.t before system_time now(); - -connection root; -grant delete history on mysqltest.* to mysqltest_1@localhost; -grant delete history on mysqltest.t to mysqltest_1@localhost; - -connection user1; -show grants; -delete history from mysqltest.t before system_time now(); - -connection root; -grant all on *.* to mysqltest_1@localhost; -show grants for mysqltest_1@localhost; - -drop user mysqltest_1@localhost; -drop database mysqltest; diff --git a/mysql-test/suite/versioning/t/trx_id.test b/mysql-test/suite/versioning/t/trx_id.test index 7c22aa2a8be..a0da7714f50 100644 --- a/mysql-test/suite/versioning/t/trx_id.test +++ b/mysql-test/suite/versioning/t/trx_id.test @@ -3,6 +3,7 @@ if (!$TEST_VERSIONING_SO) --skip needs test_versioning plugin } --source include/have_innodb.inc +--source include/have_partition.inc --source include/default_charset.inc --disable_query_log @@ -155,7 +156,87 @@ update t1 set x= 4; commit; select x, row_start < row_end from t1 for system_time all; +--echo # +--echo # MDEV-15951 system versioning by trx id doesn't work with partitioning +--echo # currently trx_id does not support partitioning by system_time +--echo # +--error ER_VERS_FIELD_WRONG_TYPE +create or replace table t1( + i int, + row_start bigint unsigned generated always as row start, + row_end bigint unsigned generated always as row end, + period for system_time(row_start, row_end) +) engine=InnoDB with system versioning partition by system_time ( + partition p0 history, + partition pn current +); + +create or replace table t1( + i int, + row_start bigint unsigned generated always as row start, + row_end bigint unsigned generated always as row end, + period for system_time(row_start, row_end) +) engine=InnoDB with system versioning; + +--replace_regex /#sql-[0-9a-f_]*/#sql-temporary/ +--error ER_VERS_FIELD_WRONG_TYPE +alter table t1 partition by system_time ( + partition p0 history, + partition pn current +); + +drop table t1; + +--error ER_VERS_TRX_PART_HISTORIC_ROW_NOT_SUPPORTED +create or replace table t ( + a int primary key, + row_start bigint unsigned as row start invisible, + row_end bigint unsigned as row end invisible, + period for system_time(row_start, row_end) +) engine=innodb with system versioning +partition by key() ( + partition p1, + partition p2 +); + +--error ER_VERS_TRX_PART_HISTORIC_ROW_NOT_SUPPORTED +create or replace table t ( + a int primary key, + row_start bigint unsigned as row start invisible, + row_end bigint unsigned as row end invisible, + period for system_time(row_start, row_end) +) engine=innodb with system versioning +partition by key(a, row_start) ( + partition p1, + partition p2 +); + +--error ER_VERS_TRX_PART_HISTORIC_ROW_NOT_SUPPORTED +create or replace table t ( + a int primary key, + row_start bigint unsigned as row start invisible, + row_end bigint unsigned as row end invisible, + period for system_time(row_start, row_end) +) engine=innodb with system versioning +partition by hash(a + row_end * 2) ( + partition p1, + partition p2 +); + +--error ER_VERS_TRX_PART_HISTORIC_ROW_NOT_SUPPORTED +create or replace table t ( + a int primary key, + row_start bigint unsigned as row start invisible, + row_end bigint unsigned as row end invisible, + period for system_time(row_start, row_end) +) engine=innodb with system versioning +partition by range columns (a, row_start) ( + partition p1 values less than (100, 100) +); + +--echo # --echo # MDEV-16010 Too many rows with AS OF point_in_the_past_or_NULL +--echo # create or replace table t1 ( x int, row_start bigint unsigned as row start invisible, @@ -507,3 +588,38 @@ drop table t; uninstall plugin test_versioning; --error ER_SP_DOES_NOT_EXIST select trt_begin_ts(0); + +--echo # +--echo # MDEV-21650 Non-empty statement transaction on global rollback after TRT update error +--echo # +create table t1 (s date, e date, period for app(s,e)) engine=innodb; +alter table t1 + add row_start bigint unsigned as row start, + add row_end bigint unsigned as row end, + add period for system_time(row_start,row_end), + with system versioning, + add period if not exists for app(x,y); + +set transaction isolation level serializable; +start transaction; +insert into t1 (s,e) values ('2021-07-04','2024-08-18'); + +--connect (con1,localhost,root,,test) +start transaction; +insert into t1 (s,e) values ('2018-06-01','2021-09-15'); + +--connection default +--replace_regex /TRX_ID \d+/TRX_ID .../ +--error ER_VERS_NO_TRX_ID +select * from t1 for system_time as of now(); + +--connection con1 +set innodb_lock_wait_timeout= 1, lock_wait_timeout= 1; +# can be existing or non-existing table, does not matter +--error ER_LOCK_WAIT_TIMEOUT +alter table xx; + +# cleanup +--disconnect con1 +--connection default +drop table t1; diff --git a/mysql-test/suite/versioning/t/update.test b/mysql-test/suite/versioning/t/update.test index 5e3c5581abf..add65cf0463 100644 --- a/mysql-test/suite/versioning/t/update.test +++ b/mysql-test/suite/versioning/t/update.test @@ -329,8 +329,18 @@ select *, check_row(row_start, row_end) from t2 for system_time all order by row drop tables t1, t2, t3; --echo # +--echo # MDEV-26778 row_start is not updated in current row for InnoDB +--echo # +create or replace table t1 (x int) with system versioning; +insert t1 values (1); +update t1 set x= 1; +select row_start into @r from t1; +select check_row_ts(row_start, row_end) from t1 for system_time all where row_start = @r; +drop table t1; + +--echo # --echo # MDEV-24522 Assertion `inited==NONE' fails upon UPDATE on versioned table with unique blob ---echo +--echo # create table t1 (a int, b int, c text, unique(c), key (b)) engine=myisam with system versioning; insert into t1 values (1, 1, 'foo'), (2, 11, 'bar'); diff --git a/mysql-test/suite/wsrep/r/mysql_tzinfo_to_sql_symlink.result b/mysql-test/suite/wsrep/r/mysql_tzinfo_to_sql_symlink.result index d5bbecfa7c2..5e4e0bd3598 100644 --- a/mysql-test/suite/wsrep/r/mysql_tzinfo_to_sql_symlink.result +++ b/mysql-test/suite/wsrep/r/mysql_tzinfo_to_sql_symlink.result @@ -9,13 +9,18 @@ ALTER TABLE time_zone ENGINE=InnoDB; ALTER TABLE time_zone_name ENGINE=InnoDB; ALTER TABLE time_zone_transition ENGINE=InnoDB; ALTER TABLE time_zone_transition_type ENGINE=InnoDB; -END IF| -\d ; TRUNCATE TABLE time_zone; TRUNCATE TABLE time_zone_name; TRUNCATE TABLE time_zone_transition; TRUNCATE TABLE time_zone_transition_type; START TRANSACTION; +ELSE +TRUNCATE TABLE time_zone; +TRUNCATE TABLE time_zone_name; +TRUNCATE TABLE time_zone_transition; +TRUNCATE TABLE time_zone_transition_type; +END IF| +\d ; INSERT INTO time_zone (Use_leap_seconds) VALUES ('N'); SET @time_zone_id= LAST_INSERT_ID(); INSERT INTO time_zone_name (Name, Time_zone_id) VALUES ('GMT', @time_zone_id); @@ -33,6 +38,7 @@ INSERT INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset, Warning: Unable to load 'MYSQLTEST_VARDIR/zoneinfo/posix/garbage' as time zone. Skipping it. Warning: Unable to load 'MYSQLTEST_VARDIR/zoneinfo/posix/ignored.tab' as time zone. Skipping it. Warning: Skipping directory 'MYSQLTEST_VARDIR/zoneinfo/posix/posix': to avoid infinite symlink recursion. +UNLOCK TABLES; COMMIT; ALTER TABLE time_zone_transition ORDER BY Time_zone_id, Transition_time; ALTER TABLE time_zone_transition_type ORDER BY Time_zone_id, Transition_type_id; @@ -41,8 +47,8 @@ IF (select count(*) from information_schema.global_variables where variable_name='wsrep_on' and variable_value='ON') = 1 THEN ALTER TABLE time_zone ENGINE=Aria; ALTER TABLE time_zone_name ENGINE=Aria; -ALTER TABLE time_zone_transition ENGINE=Aria; -ALTER TABLE time_zone_transition_type ENGINE=Aria; +ALTER TABLE time_zone_transition ENGINE=Aria, ORDER BY Time_zone_id, Transition_time; +ALTER TABLE time_zone_transition_type ENGINE=Aria, ORDER BY Time_zone_id, Transition_type_id; END IF| \d ; # Silent run @@ -53,13 +59,18 @@ ALTER TABLE time_zone ENGINE=InnoDB; ALTER TABLE time_zone_name ENGINE=InnoDB; ALTER TABLE time_zone_transition ENGINE=InnoDB; ALTER TABLE time_zone_transition_type ENGINE=InnoDB; -END IF| -\d ; TRUNCATE TABLE time_zone; TRUNCATE TABLE time_zone_name; TRUNCATE TABLE time_zone_transition; TRUNCATE TABLE time_zone_transition_type; START TRANSACTION; +ELSE +TRUNCATE TABLE time_zone; +TRUNCATE TABLE time_zone_name; +TRUNCATE TABLE time_zone_transition; +TRUNCATE TABLE time_zone_transition_type; +END IF| +\d ; INSERT INTO time_zone (Use_leap_seconds) VALUES ('N'); SET @time_zone_id= LAST_INSERT_ID(); INSERT INTO time_zone_name (Name, Time_zone_id) VALUES ('GMT', @time_zone_id); @@ -74,6 +85,7 @@ INSERT INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset, (@time_zone_id, 0, 0, 0, 'GMT') ; Warning: Unable to load 'MYSQLTEST_VARDIR/zoneinfo/posix/garbage' as time zone. Skipping it. +UNLOCK TABLES; COMMIT; ALTER TABLE time_zone_transition ORDER BY Time_zone_id, Transition_time; ALTER TABLE time_zone_transition_type ORDER BY Time_zone_id, Transition_type_id; @@ -82,8 +94,8 @@ IF (select count(*) from information_schema.global_variables where variable_name='wsrep_on' and variable_value='ON') = 1 THEN ALTER TABLE time_zone ENGINE=Aria; ALTER TABLE time_zone_name ENGINE=Aria; -ALTER TABLE time_zone_transition ENGINE=Aria; -ALTER TABLE time_zone_transition_type ENGINE=Aria; +ALTER TABLE time_zone_transition ENGINE=Aria, ORDER BY Time_zone_id, Transition_time; +ALTER TABLE time_zone_transition_type ENGINE=Aria, ORDER BY Time_zone_id, Transition_type_id; END IF| \d ; # @@ -96,6 +108,10 @@ ALTER TABLE time_zone ENGINE=InnoDB; ALTER TABLE time_zone_name ENGINE=InnoDB; ALTER TABLE time_zone_transition ENGINE=InnoDB; ALTER TABLE time_zone_transition_type ENGINE=InnoDB; +SELECT 'skip truncate tables'; +START TRANSACTION; +ELSE +SELECT 'skip truncate tables'; END IF| \d ; INSERT INTO time_zone (Use_leap_seconds) VALUES ('N'); @@ -104,13 +120,15 @@ INSERT INTO time_zone_name (Name, Time_zone_id) VALUES ('XXX', @time_zone_id); INSERT INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset, Is_DST, Abbreviation) VALUES (@time_zone_id, 0, 0, 0, 'GMT') ; +UNLOCK TABLES; +COMMIT; \d | IF (select count(*) from information_schema.global_variables where variable_name='wsrep_on' and variable_value='ON') = 1 THEN ALTER TABLE time_zone ENGINE=Aria; ALTER TABLE time_zone_name ENGINE=Aria; -ALTER TABLE time_zone_transition ENGINE=Aria; -ALTER TABLE time_zone_transition_type ENGINE=Aria; +ALTER TABLE time_zone_transition ENGINE=Aria, ORDER BY Time_zone_id, Transition_time; +ALTER TABLE time_zone_transition_type ENGINE=Aria, ORDER BY Time_zone_id, Transition_type_id; END IF| \d ; # @@ -123,6 +141,10 @@ ALTER TABLE time_zone ENGINE=InnoDB; ALTER TABLE time_zone_name ENGINE=InnoDB; ALTER TABLE time_zone_transition ENGINE=InnoDB; ALTER TABLE time_zone_transition_type ENGINE=InnoDB; +SELECT 'skip truncate tables'; +START TRANSACTION; +ELSE +SELECT 'skip truncate tables'; END IF| \d ; \d | @@ -139,12 +161,14 @@ ALTER TABLE time_zone_leap_second ENGINE=Aria; END IF| \d ; ALTER TABLE time_zone_leap_second ORDER BY Transition_time; +UNLOCK TABLES; +COMMIT; \d | IF (select count(*) from information_schema.global_variables where variable_name='wsrep_on' and variable_value='ON') = 1 THEN ALTER TABLE time_zone ENGINE=Aria; ALTER TABLE time_zone_name ENGINE=Aria; -ALTER TABLE time_zone_transition ENGINE=Aria; -ALTER TABLE time_zone_transition_type ENGINE=Aria; +ALTER TABLE time_zone_transition ENGINE=Aria, ORDER BY Time_zone_id, Transition_time; +ALTER TABLE time_zone_transition_type ENGINE=Aria, ORDER BY Time_zone_id, Transition_type_id; END IF| \d ; diff --git a/mysql-test/suite/wsrep/r/mysql_tzinfo_to_sql_symlink_skip.result b/mysql-test/suite/wsrep/r/mysql_tzinfo_to_sql_symlink_skip.result index aff02cb413e..20e986c10ed 100644 --- a/mysql-test/suite/wsrep/r/mysql_tzinfo_to_sql_symlink_skip.result +++ b/mysql-test/suite/wsrep/r/mysql_tzinfo_to_sql_symlink_skip.result @@ -9,7 +9,11 @@ TRUNCATE TABLE time_zone; TRUNCATE TABLE time_zone_name; TRUNCATE TABLE time_zone_transition; TRUNCATE TABLE time_zone_transition_type; -START TRANSACTION; +LOCK TABLES time_zone WRITE, + time_zone_leap_second WRITE, + time_zone_name WRITE, + time_zone_transition WRITE, + time_zone_transition_type WRITE; INSERT INTO time_zone (Use_leap_seconds) VALUES ('N'); SET @time_zone_id= LAST_INSERT_ID(); INSERT INTO time_zone_name (Name, Time_zone_id) VALUES ('GMT', @time_zone_id); @@ -27,6 +31,7 @@ INSERT INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset, Warning: Unable to load 'MYSQLTEST_VARDIR/zoneinfo/posix/garbage' as time zone. Skipping it. Warning: Unable to load 'MYSQLTEST_VARDIR/zoneinfo/posix/ignored.tab' as time zone. Skipping it. Warning: Skipping directory 'MYSQLTEST_VARDIR/zoneinfo/posix/posix': to avoid infinite symlink recursion. +UNLOCK TABLES; COMMIT; ALTER TABLE time_zone_transition ORDER BY Time_zone_id, Transition_time; ALTER TABLE time_zone_transition_type ORDER BY Time_zone_id, Transition_type_id; @@ -38,7 +43,11 @@ TRUNCATE TABLE time_zone; TRUNCATE TABLE time_zone_name; TRUNCATE TABLE time_zone_transition; TRUNCATE TABLE time_zone_transition_type; -START TRANSACTION; +LOCK TABLES time_zone WRITE, + time_zone_leap_second WRITE, + time_zone_name WRITE, + time_zone_transition WRITE, + time_zone_transition_type WRITE; INSERT INTO time_zone (Use_leap_seconds) VALUES ('N'); SET @time_zone_id= LAST_INSERT_ID(); INSERT INTO time_zone_name (Name, Time_zone_id) VALUES ('GMT', @time_zone_id); @@ -53,6 +62,7 @@ INSERT INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset, (@time_zone_id, 0, 0, 0, 'GMT') ; Warning: Unable to load 'MYSQLTEST_VARDIR/zoneinfo/posix/garbage' as time zone. Skipping it. +UNLOCK TABLES; COMMIT; ALTER TABLE time_zone_transition ORDER BY Time_zone_id, Transition_time; ALTER TABLE time_zone_transition_type ORDER BY Time_zone_id, Transition_type_id; @@ -62,17 +72,33 @@ ALTER TABLE time_zone_transition_type ORDER BY Time_zone_id, Transition_type_id; set @prep1=if((select count(*) from information_schema.global_variables where variable_name='wsrep_on' and variable_value='ON'), 'SET SESSION SQL_LOG_BIN=?, WSREP_ON=OFF;', 'do ?'); prepare set_wsrep_write_binlog from @prep1; set @toggle=0; execute set_wsrep_write_binlog using @toggle; +SELECT 'skip truncate tables'; +LOCK TABLES time_zone WRITE, + time_zone_leap_second WRITE, + time_zone_name WRITE, + time_zone_transition WRITE, + time_zone_transition_type WRITE; INSERT INTO time_zone (Use_leap_seconds) VALUES ('N'); SET @time_zone_id= LAST_INSERT_ID(); INSERT INTO time_zone_name (Name, Time_zone_id) VALUES ('XXX', @time_zone_id); INSERT INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset, Is_DST, Abbreviation) VALUES (@time_zone_id, 0, 0, 0, 'GMT') ; +UNLOCK TABLES; +COMMIT; # # Testing --leap # set @prep1=if((select count(*) from information_schema.global_variables where variable_name='wsrep_on' and variable_value='ON'), 'SET SESSION SQL_LOG_BIN=?, WSREP_ON=OFF;', 'do ?'); prepare set_wsrep_write_binlog from @prep1; set @toggle=0; execute set_wsrep_write_binlog using @toggle; +SELECT 'skip truncate tables'; +LOCK TABLES time_zone WRITE, + time_zone_leap_second WRITE, + time_zone_name WRITE, + time_zone_transition WRITE, + time_zone_transition_type WRITE; TRUNCATE TABLE time_zone_leap_second; ALTER TABLE time_zone_leap_second ORDER BY Transition_time; +UNLOCK TABLES; +COMMIT; |