diff options
Diffstat (limited to 'mysql-test/suite')
30 files changed, 413 insertions, 43 deletions
diff --git a/mysql-test/suite/galera/disabled.def b/mysql-test/suite/galera/disabled.def index 5b3a040c293..99e3012d947 100644 --- a/mysql-test/suite/galera/disabled.def +++ b/mysql-test/suite/galera/disabled.def @@ -32,7 +32,6 @@ galera_kill_nochanges : MDEV-18280 Galera test failure on galera_split_brain and galera_load_data : MDEV-19968 galera.galera_load_data galera_many_tables_nopk : MDEV-18182 Galera test failure on galera.galera_many_tables_nopk galera_mdl_race : MDEV-21524 galera.galera_mdl_race -galera_parallel_autoinc_largetrx : MDEV-20916 galera.galera_parallel_autoinc_largetrx galera_parallel_simple : MDEV-20318 galera.galera_parallel_simple fails galera_pc_ignore_sb : MDEV-20888 galera.galera_pc_ignore_sb galera_shutdown_nonprim : MDEV-21493 galera.galera_shutdown_nonprim diff --git a/mysql-test/suite/galera/r/galera_parallel_autoinc_largetrx.result b/mysql-test/suite/galera/r/galera_parallel_autoinc_largetrx.result index 034e23dcaad..6249edbe173 100644 --- a/mysql-test/suite/galera/r/galera_parallel_autoinc_largetrx.result +++ b/mysql-test/suite/galera/r/galera_parallel_autoinc_largetrx.result @@ -29,11 +29,11 @@ COUNT(DISTINCT f1) 30000 connection node_2; disconnect node_1a; -SELECT COUNT(*) FROM t1; -COUNT(*) +SELECT COUNT(*) AS EXPECT_30000 FROM t1; +EXPECT_30000 30000 -SELECT COUNT(DISTINCT f1) FROM t1; -COUNT(DISTINCT f1) +SELECT COUNT(DISTINCT f1) AS EXPECT_30000 FROM t1; +EXPECT_30000 30000 connection default; DROP TABLE t1; diff --git a/mysql-test/suite/galera/r/galera_var_cluster_conf_id.result b/mysql-test/suite/galera/r/galera_var_cluster_conf_id.result index 2507b1f96f2..c78d704d9b1 100644 --- a/mysql-test/suite/galera/r/galera_var_cluster_conf_id.result +++ b/mysql-test/suite/galera/r/galera_var_cluster_conf_id.result @@ -3,8 +3,8 @@ connection node_1; connection node_1; show status like 'wsrep_cluster_conf_id'; Variable_name Value -wsrep_cluster_conf_id 2 +wsrep_cluster_conf_id # connection node_2; show status like 'wsrep_cluster_conf_id'; Variable_name Value -wsrep_cluster_conf_id 2 +wsrep_cluster_conf_id # diff --git a/mysql-test/suite/galera/t/galera_parallel_autoinc_largetrx.test b/mysql-test/suite/galera/t/galera_parallel_autoinc_largetrx.test index 7f3a8e5f693..df0200fab55 100644 --- a/mysql-test/suite/galera/t/galera_parallel_autoinc_largetrx.test +++ b/mysql-test/suite/galera/t/galera_parallel_autoinc_largetrx.test @@ -53,10 +53,12 @@ SELECT COUNT(DISTINCT f1) FROM t1; --connection node_2 --reap --disconnect node_1a ---let $wait_condition = select count(*)=30000 from t1; + +--let $wait_condition = SELECT COUNT(*) = 30000 FROM t1; --source include/wait_condition.inc -SELECT COUNT(*) FROM t1; -SELECT COUNT(DISTINCT f1) FROM t1; + +SELECT COUNT(*) AS EXPECT_30000 FROM t1; +SELECT COUNT(DISTINCT f1) AS EXPECT_30000 FROM t1; --disable_query_log --eval SET GLOBAL wsrep_slave_threads = $wsrep_slave_threads_orig; diff --git a/mysql-test/suite/galera/t/galera_var_cluster_conf_id.test b/mysql-test/suite/galera/t/galera_var_cluster_conf_id.test index 95ade07f043..c569634823f 100644 --- a/mysql-test/suite/galera/t/galera_var_cluster_conf_id.test +++ b/mysql-test/suite/galera/t/galera_var_cluster_conf_id.test @@ -1,7 +1,9 @@ --source include/galera_cluster.inc --connection node_1 +--replace_regex /18446744073709551/ERROR/ /[0-9]/#/ show status like 'wsrep_cluster_conf_id'; --connection node_2 +--replace_regex /18446744073709551/ERROR/ /[0-9]/#/ show status like 'wsrep_cluster_conf_id'; diff --git a/mysql-test/suite/gcol/r/gcol_select_innodb.result b/mysql-test/suite/gcol/r/gcol_select_innodb.result index 392fb0de7a2..17acbcf2bb2 100644 --- a/mysql-test/suite/gcol/r/gcol_select_innodb.result +++ b/mysql-test/suite/gcol/r/gcol_select_innodb.result @@ -80,7 +80,7 @@ a b c explain select * from t1 where b in (select c from t3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where -1 PRIMARY t3 ref c c 5 test.t1.b 1 Using index +1 PRIMARY t3 eq_ref c c 5 test.t1.b 1 Using index # select_type=PRIMARY, type=range,ref select * from t1 where c in (select c from t3 where c between -2 and -1); a b c diff --git a/mysql-test/suite/gcol/r/gcol_select_myisam.result b/mysql-test/suite/gcol/r/gcol_select_myisam.result index 88623028b54..77ab0cdb926 100644 --- a/mysql-test/suite/gcol/r/gcol_select_myisam.result +++ b/mysql-test/suite/gcol/r/gcol_select_myisam.result @@ -80,7 +80,7 @@ a b c explain select * from t1 where b in (select c from t3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where -1 PRIMARY t3 ref c c 5 test.t1.b 1 Using index +1 PRIMARY t3 eq_ref c c 5 test.t1.b 1 Using index # select_type=PRIMARY, type=range,ref select * from t1 where c in (select c from t3 where c between -2 and -1); a b c @@ -1110,7 +1110,7 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT * FROM t1 AS t2 STRAIGHT_JOIN t1 FORCE INDEX(b) WHERE t1.b=t2.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL b NULL NULL NULL 2 Using where -1 SIMPLE t1 ref b b 5 test.t2.b 1 +1 SIMPLE t1 eq_ref b b 5 test.t2.b 1 EXPLAIN SELECT b FROM t1 FORCE INDEX(b); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL b 5 NULL 2 Using index diff --git a/mysql-test/suite/innodb/r/alter_algorithm,INPLACE.rdiff b/mysql-test/suite/innodb/r/alter_algorithm,INPLACE.rdiff index 805ccdb563c..a176a9af29e 100644 --- a/mysql-test/suite/innodb/r/alter_algorithm,INPLACE.rdiff +++ b/mysql-test/suite/innodb/r/alter_algorithm,INPLACE.rdiff @@ -1,6 +1,6 @@ ---- alter_algorithm.result -+++ alter_algorithm.reject -@@ -7,40 +7,40 @@ +--- alter_algorithm.result 2020-04-30 21:39:48.923115511 +0530 ++++ alter_algorithm.reject 2020-04-30 21:45:04.131642093 +0530 +@@ -7,43 +7,43 @@ INSERT INTO t1(f1, f2, f3) VALUES(1, 1, 1); SELECT @@alter_algorithm; @@alter_algorithm @@ -55,10 +55,15 @@ -info: Records: 1 Duplicates: 0 Warnings: 0 +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 + ALTER TABLE t1 FORCE, ALGORITHM=DEFAULT; +-affected rows: 1 +-info: Records: 1 Duplicates: 0 Warnings: 0 ++affected rows: 0 ++info: Records: 0 Duplicates: 0 Warnings: 0 DROP TABLE t1; affected rows: 0 CREATE TABLE t1(f1 INT PRIMARY KEY, f2 INT NOT NULL, -@@ -53,22 +53,22 @@ +@@ -56,22 +56,22 @@ FOREIGN KEY fidx(f1) REFERENCES t1(f1))ENGINE=INNODB; INSERT INTO t1(f1, f2, f4, f5) VALUES(1, 2, 3, 4); ALTER TABLE t1 ADD INDEX idx1(f4), page_compressed=1; @@ -91,7 +96,7 @@ DROP TABLE t2, t1; affected rows: 0 CREATE TABLE t1(f1 INT NOT NULL, -@@ -81,27 +81,27 @@ +@@ -84,28 +84,27 @@ INSERT INTO t1(f1, f2) VALUES(1, 1); # Add column at the end of the table ALTER TABLE t1 ADD COLUMN f4 char(100) default 'BIG WALL'; @@ -119,7 +124,9 @@ +info: Records: 0 Duplicates: 0 Warnings: 0 # Rename table ALTER TABLE t1 RENAME t3; - affected rows: 0 +-affected rows: 1 +-info: Records: 1 Duplicates: 0 Warnings: 0 ++affected rows: 0 # Drop Virtual Column ALTER TABLE t3 DROP COLUMN vcol; -affected rows: 1 @@ -129,7 +136,7 @@ # Column length varies ALTER TABLE t2 CHANGE f3 f3 VARCHAR(20); affected rows: 0 -@@ -109,12 +109,12 @@ +@@ -113,12 +112,12 @@ SET foreign_key_checks = 0; affected rows: 0 ALTER TABLE t3 ADD FOREIGN KEY fidx(f2) REFERENCES t2(f1); diff --git a/mysql-test/suite/innodb/r/alter_algorithm,INSTANT.rdiff b/mysql-test/suite/innodb/r/alter_algorithm,INSTANT.rdiff index 6d2ee160e46..414b7dc8b0c 100644 --- a/mysql-test/suite/innodb/r/alter_algorithm,INSTANT.rdiff +++ b/mysql-test/suite/innodb/r/alter_algorithm,INSTANT.rdiff @@ -1,6 +1,6 @@ ---- alter_algorithm.result -+++ alter_algorithm.reject -@@ -7,40 +7,32 @@ +--- alter_algorithm.result 2020-04-30 21:39:48.923115511 +0530 ++++ alter_algorithm.reject 2020-04-30 21:47:08.245465018 +0530 +@@ -7,43 +7,35 @@ INSERT INTO t1(f1, f2, f3) VALUES(1, 1, 1); SELECT @@alter_algorithm; @@alter_algorithm @@ -47,10 +47,15 @@ -affected rows: 1 -info: Records: 1 Duplicates: 0 Warnings: 0 +Got one of the listed errors + ALTER TABLE t1 FORCE, ALGORITHM=DEFAULT; +-affected rows: 1 +-info: Records: 1 Duplicates: 0 Warnings: 0 ++affected rows: 0 ++info: Records: 0 Duplicates: 0 Warnings: 0 DROP TABLE t1; affected rows: 0 CREATE TABLE t1(f1 INT PRIMARY KEY, f2 INT NOT NULL, -@@ -53,22 +45,17 @@ +@@ -56,22 +48,17 @@ FOREIGN KEY fidx(f1) REFERENCES t1(f1))ENGINE=INNODB; INSERT INTO t1(f1, f2, f4, f5) VALUES(1, 2, 3, 4); ALTER TABLE t1 ADD INDEX idx1(f4), page_compressed=1; @@ -78,7 +83,7 @@ DROP TABLE t2, t1; affected rows: 0 CREATE TABLE t1(f1 INT NOT NULL, -@@ -81,27 +68,27 @@ +@@ -84,28 +71,27 @@ INSERT INTO t1(f1, f2) VALUES(1, 1); # Add column at the end of the table ALTER TABLE t1 ADD COLUMN f4 char(100) default 'BIG WALL'; @@ -106,7 +111,9 @@ +info: Records: 0 Duplicates: 0 Warnings: 0 # Rename table ALTER TABLE t1 RENAME t3; - affected rows: 0 +-affected rows: 1 +-info: Records: 1 Duplicates: 0 Warnings: 0 ++affected rows: 0 # Drop Virtual Column ALTER TABLE t3 DROP COLUMN vcol; -affected rows: 1 @@ -116,7 +123,7 @@ # Column length varies ALTER TABLE t2 CHANGE f3 f3 VARCHAR(20); affected rows: 0 -@@ -109,12 +96,12 @@ +@@ -113,12 +99,12 @@ SET foreign_key_checks = 0; affected rows: 0 ALTER TABLE t3 ADD FOREIGN KEY fidx(f2) REFERENCES t2(f1); diff --git a/mysql-test/suite/innodb/r/alter_algorithm,NOCOPY.rdiff b/mysql-test/suite/innodb/r/alter_algorithm,NOCOPY.rdiff index c6ce83b5d9f..2aa8c72265a 100644 --- a/mysql-test/suite/innodb/r/alter_algorithm,NOCOPY.rdiff +++ b/mysql-test/suite/innodb/r/alter_algorithm,NOCOPY.rdiff @@ -1,6 +1,6 @@ ---- alter_algorithm.result -+++ alter_algorithm.reject -@@ -7,40 +7,32 @@ +--- alter_algorithm.result 2020-04-30 21:39:48.923115511 +0530 ++++ alter_algorithm.reject 2020-04-30 21:52:10.785967739 +0530 +@@ -7,43 +7,35 @@ INSERT INTO t1(f1, f2, f3) VALUES(1, 1, 1); SELECT @@alter_algorithm; @@alter_algorithm @@ -47,10 +47,15 @@ -affected rows: 1 -info: Records: 1 Duplicates: 0 Warnings: 0 +Got one of the listed errors + ALTER TABLE t1 FORCE, ALGORITHM=DEFAULT; +-affected rows: 1 +-info: Records: 1 Duplicates: 0 Warnings: 0 ++affected rows: 0 ++info: Records: 0 Duplicates: 0 Warnings: 0 DROP TABLE t1; affected rows: 0 CREATE TABLE t1(f1 INT PRIMARY KEY, f2 INT NOT NULL, -@@ -53,22 +45,22 @@ +@@ -56,22 +48,22 @@ FOREIGN KEY fidx(f1) REFERENCES t1(f1))ENGINE=INNODB; INSERT INTO t1(f1, f2, f4, f5) VALUES(1, 2, 3, 4); ALTER TABLE t1 ADD INDEX idx1(f4), page_compressed=1; @@ -83,7 +88,7 @@ DROP TABLE t2, t1; affected rows: 0 CREATE TABLE t1(f1 INT NOT NULL, -@@ -81,27 +73,27 @@ +@@ -84,28 +76,27 @@ INSERT INTO t1(f1, f2) VALUES(1, 1); # Add column at the end of the table ALTER TABLE t1 ADD COLUMN f4 char(100) default 'BIG WALL'; @@ -111,7 +116,9 @@ +info: Records: 0 Duplicates: 0 Warnings: 0 # Rename table ALTER TABLE t1 RENAME t3; - affected rows: 0 +-affected rows: 1 +-info: Records: 1 Duplicates: 0 Warnings: 0 ++affected rows: 0 # Drop Virtual Column ALTER TABLE t3 DROP COLUMN vcol; -affected rows: 1 @@ -121,7 +128,7 @@ # Column length varies ALTER TABLE t2 CHANGE f3 f3 VARCHAR(20); affected rows: 0 -@@ -109,12 +101,12 @@ +@@ -113,12 +104,12 @@ SET foreign_key_checks = 0; affected rows: 0 ALTER TABLE t3 ADD FOREIGN KEY fidx(f2) REFERENCES t2(f1); diff --git a/mysql-test/suite/innodb/r/alter_algorithm.result b/mysql-test/suite/innodb/r/alter_algorithm.result index bcfbe3355f9..717d31de6a0 100644 --- a/mysql-test/suite/innodb/r/alter_algorithm.result +++ b/mysql-test/suite/innodb/r/alter_algorithm.result @@ -41,6 +41,9 @@ info: Records: 1 Duplicates: 0 Warnings: 0 ALTER TABLE t1 ENGINE=INNODB; affected rows: 1 info: Records: 1 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 FORCE, ALGORITHM=DEFAULT; +affected rows: 1 +info: Records: 1 Duplicates: 0 Warnings: 0 DROP TABLE t1; affected rows: 0 CREATE TABLE t1(f1 INT PRIMARY KEY, f2 INT NOT NULL, @@ -97,7 +100,8 @@ affected rows: 1 info: Records: 1 Duplicates: 0 Warnings: 0 # Rename table ALTER TABLE t1 RENAME t3; -affected rows: 0 +affected rows: 1 +info: Records: 1 Duplicates: 0 Warnings: 0 # Drop Virtual Column ALTER TABLE t3 DROP COLUMN vcol; affected rows: 1 diff --git a/mysql-test/suite/innodb/r/alter_algorithm2.result b/mysql-test/suite/innodb/r/alter_algorithm2.result new file mode 100644 index 00000000000..f4443c80c77 --- /dev/null +++ b/mysql-test/suite/innodb/r/alter_algorithm2.result @@ -0,0 +1,81 @@ +CREATE TABLE t1 (a INT)ENGINE=InnoDB; +INSERT INTO t1 VALUES(1); +SET alter_algorithm='INPLACE'; +affected rows: 0 +PREPARE stmt FROM 'ALTER TABLE t1 ADD KEY idx(a)'; +affected rows: 0 +info: Statement prepared +PREPARE stmt1 FROM 'ALTER TABLE t1 DROP KEY idx'; +affected rows: 0 +info: Statement prepared +CREATE OR REPLACE PROCEDURE p1() +BEGIN +ALTER TABLE t1 ADD KEY idx2(a); +END| +affected rows: 0 +CREATE OR REPLACE PROCEDURE p2() +BEGIN +ALTER TABLE t1 DROP KEY idx2; +END| +affected rows: 0 +SET alter_algorithm='COPY'; +affected rows: 0 +EXECUTE stmt; +affected rows: 1 +info: Records: 1 Duplicates: 0 Warnings: 0 +EXECUTE stmt1; +affected rows: 1 +info: Records: 1 Duplicates: 0 Warnings: 0 +call p1(); +affected rows: 1 +call p2(); +affected rows: 1 +SET alter_algorithm='NOCOPY'; +affected rows: 0 +EXECUTE stmt; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +EXECUTE stmt1; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +call p1(); +affected rows: 0 +call p2(); +affected rows: 0 +SET alter_algorithm='INSTANT'; +affected rows: 0 +EXECUTE stmt; +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: ADD INDEX. Try ALGORITHM=NOCOPY +call p1(); +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: ADD INDEX. Try ALGORITHM=NOCOPY +DROP TABLE t1; +affected rows: 0 +DROP PROCEDURE p1; +affected rows: 0 +DROP PROCEDURE p2; +affected rows: 0 +SET @save_allowed= @@GLOBAL.innodb_instant_alter_column_allowed; +affected rows: 0 +SET GLOBAL innodb_instant_alter_column_allowed=never; +affected rows: 0 +CREATE TABLE t1(id INT PRIMARY KEY, +col1 INT UNSIGNED NOT NULL UNIQUE)ENGINE=InnoDB; +affected rows: 0 +INSERT INTO t1 VALUES(1,1),(2,2),(3,3); +affected rows: 3 +info: Records: 3 Duplicates: 0 Warnings: 0 +SET ALTER_ALGORITHM=INSTANT; +affected rows: 0 +ALTER TABLE t1 DROP COLUMN col1; +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_alter_column_allowed=never. Try ALGORITHM=INPLACE +ALTER TABLE t1 DROP COLUMN col1, ALGORITHM=NOCOPY; +ERROR 0A000: ALGORITHM=NOCOPY is not supported. Reason: innodb_instant_alter_column_allowed=never. Try ALGORITHM=INPLACE +ALTER TABLE t1 DROP COLUMN col1, ALGORITHM=DEFAULT; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 DROP PRIMARY KEY, ALGORITHM=DEFAULT; +affected rows: 3 +info: Records: 3 Duplicates: 0 Warnings: 0 +DROP TABLE t1; +affected rows: 0 +SET GLOBAL innodb_instant_alter_column_allowed=@save_allowed; diff --git a/mysql-test/suite/innodb/r/foreign_key.result b/mysql-test/suite/innodb/r/foreign_key.result index 2cb2d829e53..b3c662bc32a 100644 --- a/mysql-test/suite/innodb/r/foreign_key.result +++ b/mysql-test/suite/innodb/r/foreign_key.result @@ -276,6 +276,16 @@ ALTER TABLE t1 ADD FOREIGN KEY (a) REFERENCES tx(x); ALTER TABLE t1 DROP KEY idx; ALTER TABLE t1 CHANGE a c INT; DROP TABLE t1; +CREATE TABLE t1 (f1 INT, f2 INT, f3 INT, KEY idx(f1)) ENGINE=InnoDB; +ALTER TABLE t1 ADD FOREIGN KEY (f2) REFERENCES t1 (f1); +ALTER TABLE t1 ADD COLUMN f INT; +SET FOREIGN_KEY_CHECKS= OFF; +ALTER TABLE t1 DROP KEY idx; +ALTER TABLE t1 ADD KEY idx (f1); +SET FOREIGN_KEY_CHECKS= ON; +ALTER TABLE t1 DROP f3; +ALTER TABLE t1 CHANGE f f3 INT; +DROP TABLE t1; SET FOREIGN_KEY_CHECKS=1; # Start of 10.2 tests # diff --git a/mysql-test/suite/innodb/r/instant_alter,4k.rdiff b/mysql-test/suite/innodb/r/instant_alter,4k.rdiff index 71394d4b43b..14ef4971267 100644 --- a/mysql-test/suite/innodb/r/instant_alter,4k.rdiff +++ b/mysql-test/suite/innodb/r/instant_alter,4k.rdiff @@ -318,8 +318,8 @@ FROM information_schema.global_status WHERE variable_name = 'innodb_instant_alter_column'; instants --208 -+210 +-209 ++211 SET GLOBAL innodb_purge_rseg_truncate_frequency= @saved_frequency; SET GLOBAL innodb_instant_alter_column_allowed = @saved_allowance; # diff --git a/mysql-test/suite/innodb/r/instant_alter.result b/mysql-test/suite/innodb/r/instant_alter.result index 004f535291b..3ee879fd925 100644 --- a/mysql-test/suite/innodb/r/instant_alter.result +++ b/mysql-test/suite/innodb/r/instant_alter.result @@ -2848,11 +2848,19 @@ pk b connection default; DROP TABLE t1; disconnect analyze; +# +# MDEV-22465: DROP COLUMN is wrongly claimed to be ALGORITHM=INSTANT +# +CREATE TABLE t1(a INT PRIMARY KEY, b INT UNIQUE) ENGINE=InnoDB; +ALTER TABLE t1 DROP b, ALGORITHM=INSTANT; +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: DROP INDEX. Try ALGORITHM=NOCOPY +ALTER TABLE t1 DROP b, ALGORITHM=NOCOPY; +DROP TABLE t1; SELECT variable_value-@old_instant instants FROM information_schema.global_status WHERE variable_name = 'innodb_instant_alter_column'; instants -208 +209 SET GLOBAL innodb_purge_rseg_truncate_frequency= @saved_frequency; SET GLOBAL innodb_instant_alter_column_allowed = @saved_allowance; # diff --git a/mysql-test/suite/innodb/r/instant_alter_bugs.result b/mysql-test/suite/innodb/r/instant_alter_bugs.result index 53f2d4f1c60..84c9ba2d630 100644 --- a/mysql-test/suite/innodb/r/instant_alter_bugs.result +++ b/mysql-test/suite/innodb/r/instant_alter_bugs.result @@ -171,6 +171,41 @@ DROP FOREIGN KEY fk1, CHANGE b d INT UNSIGNED, ADD c INT; DROP TABLE t2, t1; +# +# MDEV-22446 InnoDB aborts while adding instant column +# for discarded tablespace +# +CREATE TABLE t1(c1 INT NOT NULL, c2 INT NOT NULL DEFAULT 0)ENGINE=InnoDB; +INSERT INTO t1(c1) VALUES(1); +ALTER TABLE t1 ADD COLUMN c3 INT DEFAULT 10; +FLUSH TABLES t1 FOR EXPORT; +backup: t1 +UNLOCK TABLES; +DROP TABLE t1; +CREATE TABLE t1(c1 INT NOT NULL)Engine=InnoDB; +ALTER TABLE t1 DISCARD TABLESPACE; +FLUSH TABLES; +ALTER TABLE t1 ADD COLUMN c2 INT NOT NULL; +Warnings: +Warning 1814 Tablespace has been discarded for table `t1` +ALTER TABLE t1 ADD COLUMN c3 INT DEFAULT 10; +Warnings: +Warning 1814 Tablespace has been discarded for table `t1` +restore: t1 .ibd and .cfg files +ALTER TABLE t1 IMPORT TABLESPACE; +Warnings: +Warning 1814 Tablespace has been discarded for table `t1` +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` int(11) NOT NULL, + `c2` int(11) NOT NULL, + `c3` int(11) DEFAULT 10 +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +SELECT * FROM t1; +c1 c2 c3 +1 0 10 +DROP TABLE t1; # End of 10.3 tests create table t ( a varchar(9), diff --git a/mysql-test/suite/innodb/r/log_file_name_debug.result b/mysql-test/suite/innodb/r/log_file_name_debug.result index 102db617a64..cb2ee68fc98 100644 --- a/mysql-test/suite/innodb/r/log_file_name_debug.result +++ b/mysql-test/suite/innodb/r/log_file_name_debug.result @@ -12,7 +12,7 @@ FOUND 1 /InnoDB: Tablespace 4294967280 was not found at .*, but there were no mo # restart: --debug=d,innodb_log_abort_3,ib_log --innodb-log-file-size=4194304 SELECT * FROM t1; ERROR 42000: Unknown storage engine 'InnoDB' -FOUND 1 /srv_prepare_to_delete_redo_log_file: ib_log: FILE_CHECKPOINT.* written/ in mysqld.1.err +FOUND 1 /ib_log: FILE_CHECKPOINT.* written/ in mysqld.1.err # restart # restart DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/alter_algorithm.test b/mysql-test/suite/innodb/t/alter_algorithm.test index d410a15154d..08c23b505f3 100644 --- a/mysql-test/suite/innodb/t/alter_algorithm.test +++ b/mysql-test/suite/innodb/t/alter_algorithm.test @@ -55,6 +55,10 @@ ALTER TABLE t1 ROW_FORMAT=COMPRESSED; --error $error_code ALTER TABLE t1 ENGINE=INNODB; +# Irrespective of alter_algorithm value, the following command +# should succeed because of explicitly mentioning ALGORTHM=DEFAULT +ALTER TABLE t1 FORCE, ALGORITHM=DEFAULT; + DROP TABLE t1; --disable_info diff --git a/mysql-test/suite/innodb/t/alter_algorithm2.test b/mysql-test/suite/innodb/t/alter_algorithm2.test new file mode 100644 index 00000000000..cdece2398f0 --- /dev/null +++ b/mysql-test/suite/innodb/t/alter_algorithm2.test @@ -0,0 +1,63 @@ +--source include/have_innodb.inc +CREATE TABLE t1 (a INT)ENGINE=InnoDB; +INSERT INTO t1 VALUES(1); + +# alter_algorithm variable doesn't affect when ALTER stmt +# during PREPARE PHASE or CREATE PROCEDURE +# Only when execution/call happens, alter uses the alter_algorithm +# variable when user does not mention algorithm explicitly. + +--enable_info +SET alter_algorithm='INPLACE'; +PREPARE stmt FROM 'ALTER TABLE t1 ADD KEY idx(a)'; +PREPARE stmt1 FROM 'ALTER TABLE t1 DROP KEY idx'; +DELIMITER |; +CREATE OR REPLACE PROCEDURE p1() +BEGIN + ALTER TABLE t1 ADD KEY idx2(a); +END| + +CREATE OR REPLACE PROCEDURE p2() +BEGIN + ALTER TABLE t1 DROP KEY idx2; +END| +DELIMITER ;| + +SET alter_algorithm='COPY'; +EXECUTE stmt; +EXECUTE stmt1; +call p1(); +call p2(); + +SET alter_algorithm='NOCOPY'; +EXECUTE stmt; +EXECUTE stmt1; +call p1(); +call p2(); + +SET alter_algorithm='INSTANT'; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +EXECUTE stmt; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +call p1(); + +DROP TABLE t1; +DROP PROCEDURE p1; +DROP PROCEDURE p2; + +SET @save_allowed= @@GLOBAL.innodb_instant_alter_column_allowed; +SET GLOBAL innodb_instant_alter_column_allowed=never; + +CREATE TABLE t1(id INT PRIMARY KEY, + col1 INT UNSIGNED NOT NULL UNIQUE)ENGINE=InnoDB; +INSERT INTO t1 VALUES(1,1),(2,2),(3,3); +SET ALTER_ALGORITHM=INSTANT; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +ALTER TABLE t1 DROP COLUMN col1; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +ALTER TABLE t1 DROP COLUMN col1, ALGORITHM=NOCOPY; +ALTER TABLE t1 DROP COLUMN col1, ALGORITHM=DEFAULT; +ALTER TABLE t1 DROP PRIMARY KEY, ALGORITHM=DEFAULT; +DROP TABLE t1; +--disable_info +SET GLOBAL innodb_instant_alter_column_allowed=@save_allowed; diff --git a/mysql-test/suite/innodb/t/foreign_key.test b/mysql-test/suite/innodb/t/foreign_key.test index 330d718e5e4..8e2ae0a5528 100644 --- a/mysql-test/suite/innodb/t/foreign_key.test +++ b/mysql-test/suite/innodb/t/foreign_key.test @@ -266,6 +266,19 @@ ALTER TABLE t1 DROP KEY idx; ALTER TABLE t1 CHANGE a c INT; # Cleanup DROP TABLE t1; + +CREATE TABLE t1 (f1 INT, f2 INT, f3 INT, KEY idx(f1)) ENGINE=InnoDB; +ALTER TABLE t1 ADD FOREIGN KEY (f2) REFERENCES t1 (f1); +ALTER TABLE t1 ADD COLUMN f INT; +SET FOREIGN_KEY_CHECKS= OFF; +ALTER TABLE t1 DROP KEY idx; +ALTER TABLE t1 ADD KEY idx (f1); +SET FOREIGN_KEY_CHECKS= ON; +ALTER TABLE t1 DROP f3; +ALTER TABLE t1 CHANGE f f3 INT; +# Cleanup +DROP TABLE t1; + SET FOREIGN_KEY_CHECKS=1; --echo # Start of 10.2 tests diff --git a/mysql-test/suite/innodb/t/instant_alter.test b/mysql-test/suite/innodb/t/instant_alter.test index 5f859d7f9a8..ab2636f7554 100644 --- a/mysql-test/suite/innodb/t/instant_alter.test +++ b/mysql-test/suite/innodb/t/instant_alter.test @@ -889,6 +889,17 @@ dec $format; let $redundant_4k= 0; } disconnect analyze; + +--echo # +--echo # MDEV-22465: DROP COLUMN is wrongly claimed to be ALGORITHM=INSTANT +--echo # + +CREATE TABLE t1(a INT PRIMARY KEY, b INT UNIQUE) ENGINE=InnoDB; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +ALTER TABLE t1 DROP b, ALGORITHM=INSTANT; +ALTER TABLE t1 DROP b, ALGORITHM=NOCOPY; +DROP TABLE t1; + SELECT variable_value-@old_instant instants FROM information_schema.global_status WHERE variable_name = 'innodb_instant_alter_column'; diff --git a/mysql-test/suite/innodb/t/instant_alter_bugs.test b/mysql-test/suite/innodb/t/instant_alter_bugs.test index d76a586cfa1..b6b4cadc654 100644 --- a/mysql-test/suite/innodb/t/instant_alter_bugs.test +++ b/mysql-test/suite/innodb/t/instant_alter_bugs.test @@ -172,6 +172,42 @@ ALTER TABLE t2 CHANGE b d INT UNSIGNED, ADD c INT; DROP TABLE t2, t1; + +--echo # +--echo # MDEV-22446 InnoDB aborts while adding instant column +--echo # for discarded tablespace +--echo # + +let MYSQLD_DATADIR =`SELECT @@datadir`; +CREATE TABLE t1(c1 INT NOT NULL, c2 INT NOT NULL DEFAULT 0)ENGINE=InnoDB; +INSERT INTO t1(c1) VALUES(1); + +ALTER TABLE t1 ADD COLUMN c3 INT DEFAULT 10; +--replace_regex /, .*\).*t1.cfg/, Bad file descriptor) t1.cfg/ +FLUSH TABLES t1 FOR EXPORT; + +perl; +do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl"; +ib_backup_tablespaces("test", "t1"); +EOF +UNLOCK TABLES; +DROP TABLE t1; + +# Restore of instant table +CREATE TABLE t1(c1 INT NOT NULL)Engine=InnoDB; +ALTER TABLE t1 DISCARD TABLESPACE; +FLUSH TABLES; +ALTER TABLE t1 ADD COLUMN c2 INT NOT NULL; +ALTER TABLE t1 ADD COLUMN c3 INT DEFAULT 10; +# Restore files +perl; +do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl"; +ib_restore_tablespaces("test", "t1"); +EOF +ALTER TABLE t1 IMPORT TABLESPACE; +SHOW CREATE TABLE t1; +SELECT * FROM t1; +DROP TABLE t1; --echo # End of 10.3 tests diff --git a/mysql-test/suite/innodb/t/log_file_name_debug.test b/mysql-test/suite/innodb/t/log_file_name_debug.test index 8b56ccaa55f..d90be6d8916 100644 --- a/mysql-test/suite/innodb/t/log_file_name_debug.test +++ b/mysql-test/suite/innodb/t/log_file_name_debug.test @@ -39,7 +39,7 @@ SELECT * FROM t1; --source include/restart_mysqld.inc --error ER_UNKNOWN_STORAGE_ENGINE SELECT * FROM t1; ---let SEARCH_PATTERN= srv_prepare_to_delete_redo_log_file: ib_log: FILE_CHECKPOINT.* written +--let SEARCH_PATTERN= ib_log: FILE_CHECKPOINT.* written --source include/search_pattern_in_file.inc --let $restart_parameters= diff --git a/mysql-test/suite/rpl/include/rpl_parallel_show_binlog_events_purge_logs.inc b/mysql-test/suite/rpl/include/rpl_parallel_show_binlog_events_purge_logs.inc index 7801498adb4..cddc9286bd2 100644 --- a/mysql-test/suite/rpl/include/rpl_parallel_show_binlog_events_purge_logs.inc +++ b/mysql-test/suite/rpl/include/rpl_parallel_show_binlog_events_purge_logs.inc @@ -15,6 +15,7 @@ # that with the fix local variable linfo is valid along all # mysql_show_binlog_events function scope. # +--source include/have_debug.inc --source include/have_debug_sync.inc --source include/master-slave.inc diff --git a/mysql-test/suite/rpl/r/rpl_conditional_comments.result b/mysql-test/suite/rpl/r/rpl_conditional_comments.result index 044f31427be..036824d60aa 100644 --- a/mysql-test/suite/rpl/r/rpl_conditional_comments.result +++ b/mysql-test/suite/rpl/r/rpl_conditional_comments.result @@ -71,5 +71,22 @@ include/diff_tables.inc [master:t1,slave:t1] connection master; SELECT c1 FROM /*!999999 t1 WHEREN; 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 '/*!999999 t1 WHEREN' at line 1 +insert t1 values (/*!50505 1 /* foo */ */ + 2); +insert t1 values (/*!999999 10 /* foo */ */ + 20); +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Query # # use `test`; insert t1 values (/*!50505 1 /* foo */ */ + 2) +master-bin.000001 # Query # # COMMIT +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Query # # use `test`; insert t1 values (/* 999999 10 (* foo *) */ + 20) +master-bin.000001 # Query # # COMMIT +connection slave; +select * from t1; +c1 +62 +3 +20 +connection master; DROP TABLE t1; include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_fail_register.result b/mysql-test/suite/rpl/r/rpl_fail_register.result new file mode 100644 index 00000000000..2cddc796314 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_fail_register.result @@ -0,0 +1,19 @@ +include/master-slave.inc +[connection master] +connection slave; +set @old_dbug=@@global.debug_dbug; +set global debug_dbug='d,fail_com_register_slave'; +stop slave; +reset slave; +include/wait_for_slave_to_stop.inc +start slave; +stop slave; +include/wait_for_slave_to_stop.inc +set global debug_dbug=@old_dbug; +connection master; +kill DUMP_THREAD; +show slave hosts; +Server_id Host Port Master_id +connection slave; +start slave; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_conditional_comments.test b/mysql-test/suite/rpl/t/rpl_conditional_comments.test index bcc964a92c7..6e4ec8745f4 100644 --- a/mysql-test/suite/rpl/t/rpl_conditional_comments.test +++ b/mysql-test/suite/rpl/t/rpl_conditional_comments.test @@ -68,7 +68,17 @@ sync_slave_with_master; --echo # comments --connection master --error 1064 -SELECT c1 FROM /*!999999 t1 WHEREN; +SELECT c1 FROM /*!999999 t1 WHEREN; #*/ + +# +# Bug#28388217 - SERVER CAN FAIL WHILE REPLICATING CONDITIONAL COMMENTS +# +insert t1 values (/*!50505 1 /* foo */ */ + 2); +insert t1 values (/*!999999 10 /* foo */ */ + 20); +source include/show_binlog_events.inc; +sync_slave_with_master; +select * from t1; +connection master; DROP TABLE t1; --source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_fail_register.test b/mysql-test/suite/rpl/t/rpl_fail_register.test new file mode 100644 index 00000000000..d0502e734e2 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_fail_register.test @@ -0,0 +1,34 @@ +source include/have_debug.inc; +source include/have_binlog_format_mixed.inc; +source include/master-slave.inc; + +connection slave; + +set @old_dbug=@@global.debug_dbug; +set global debug_dbug='d,fail_com_register_slave'; + +stop slave; +reset slave; +source include/wait_for_slave_to_stop.inc; +start slave; +stop slave; +source include/wait_for_slave_to_stop.inc; +set global debug_dbug=@old_dbug; + +connection master; + +### Dump thread is hanging despite slave has gracefully exited. +let $id=`SELECT id from information_schema.processlist where command='Binlog Dump'`; + +if ($id) { + replace_result $id DUMP_THREAD; + eval kill $id; + let $wait_condition= SELECT count(*)=0 from information_schema.processlist where command='Binlog Dump'; + source include/wait_condition.inc; +} + +show slave hosts; + +connection slave; +start slave; +source include/rpl_end.inc; diff --git a/mysql-test/suite/vcol/r/vcol_select_innodb.result b/mysql-test/suite/vcol/r/vcol_select_innodb.result index 1ac4bb32a49..e275a66091c 100644 --- a/mysql-test/suite/vcol/r/vcol_select_innodb.result +++ b/mysql-test/suite/vcol/r/vcol_select_innodb.result @@ -64,7 +64,7 @@ a b c explain select * from t1 where b in (select c from t3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where -1 PRIMARY t3 ref c c 5 test.t1.b 1 Using index +1 PRIMARY t3 eq_ref c c 5 test.t1.b 1 Using index # select_type=PRIMARY, type=range,ref select * from t1 where c in (select c from t3 where c between -2 and -1); a b c diff --git a/mysql-test/suite/vcol/r/vcol_select_myisam.result b/mysql-test/suite/vcol/r/vcol_select_myisam.result index fb6da695896..05f86347706 100644 --- a/mysql-test/suite/vcol/r/vcol_select_myisam.result +++ b/mysql-test/suite/vcol/r/vcol_select_myisam.result @@ -64,7 +64,7 @@ a b c explain select * from t1 where b in (select c from t3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where -1 PRIMARY t3 ref c c 5 test.t1.b 1 Using index +1 PRIMARY t3 eq_ref c c 5 test.t1.b 1 Using index # select_type=PRIMARY, type=range,ref select * from t1 where c in (select c from t3 where c between -2 and -1); a b c |