diff options
| author | Marko Mäkelä <marko.makela@mariadb.com> | 2023-02-16 13:34:45 +0200 |
|---|---|---|
| committer | Marko Mäkelä <marko.makela@mariadb.com> | 2023-02-16 13:34:45 +0200 |
| commit | 2e431ff7e69d9c887777cc597ccf5cfb446e13d8 (patch) | |
| tree | 98be0df7531bbe1f26e1f339cf18430556bca933 /mysql-test | |
| parent | 80b4fa54e1d38c8f90f6b94240c583aa9d4627b7 (diff) | |
| parent | 1fd00998390a7487e42f5e29472e99354159c8bc (diff) | |
| download | mariadb-git-2e431ff7e69d9c887777cc597ccf5cfb446e13d8.tar.gz | |
Merge 10.11 into 11.0
Diffstat (limited to 'mysql-test')
153 files changed, 4271 insertions, 479 deletions
diff --git a/mysql-test/include/ctype_casefolding.inc b/mysql-test/include/ctype_casefolding.inc new file mode 100644 index 00000000000..4ee402c95ad --- /dev/null +++ b/mysql-test/include/ctype_casefolding.inc @@ -0,0 +1,18 @@ +CREATE OR REPLACE TABLE case_folding AS SELECT 0 AS code, SPACE(32) AS c LIMIT 0; +SHOW CREATE TABLE case_folding; +# Uncode code points that have a variable length case mapping in utf8 +# (e.g. LOWER('2-byte-character') -> '3-byte-character' +INSERT INTO case_folding (code) VALUES +(0x23A), +(0x23E), +(0x23F), +(0x240), +(0x250), +(0x251), +(0x252), +(0x26B), +(0x271), +(0x27D); +UPDATE case_folding SET c=CHAR(code USING ucs2); +SELECT HEX(code), HEX(LOWER(c)), HEX(UPPER(c)), c FROM case_folding; +DROP TABLE case_folding; diff --git a/mysql-test/include/master-slave.inc b/mysql-test/include/master-slave.inc index 9ed206b2c22..17c8dd3ed4e 100644 --- a/mysql-test/include/master-slave.inc +++ b/mysql-test/include/master-slave.inc @@ -11,6 +11,7 @@ # [--let $rpl_skip_start_slave= 1] # [--let $rpl_debug= 1] # [--let $slave_timeout= NUMBER] +# [--let $rpl_server_skip_log_bin= 1] # --source include/master-slave.inc # # Parameters: diff --git a/mysql-test/include/rpl_init.inc b/mysql-test/include/rpl_init.inc index 4ee4cccdc20..acb9104ae6f 100644 --- a/mysql-test/include/rpl_init.inc +++ b/mysql-test/include/rpl_init.inc @@ -73,6 +73,7 @@ # before CHANGE MASTER and START SLAVE. RESET MASTER and RESET # SLAVE are suppressed if $rpl_skip_reset_master_and_slave is # set. +# Also see $rpl_server_skip_log_bin. # # $rpl_skip_change_master # By default, this script issues CHANGE MASTER so that all slaves @@ -94,6 +95,10 @@ # Timeout used when waiting for the slave threads to start. # See include/wait_for_slave_param.inc # +# $rpl_server_skip_log_bin +# When $rpl_skip_reset_master_and_slave is not set +# RESET MASTER does not report ER_FLUSH_MASTER_BINLOG_CLOSED +# on any server. # # ==== Side effects ==== # @@ -161,7 +166,16 @@ while ($_rpl_server) USE test; if (!$rpl_skip_reset_master_and_slave) { - RESET MASTER; + if (!$rpl_server_skip_log_bin) + { + --error 0 + RESET MASTER; + } + if ($rpl_server_skip_log_bin) + { + --error 0,ER_FLUSH_MASTER_BINLOG_CLOSED + RESET MASTER; + } SET GLOBAL gtid_slave_pos= ""; RESET SLAVE; } diff --git a/mysql-test/include/world.inc b/mysql-test/include/world.inc index a6f877ce0cd..91ea66ef731 100644 --- a/mysql-test/include/world.inc +++ b/mysql-test/include/world.inc @@ -215,7 +215,7 @@ INSERT IGNORE INTO Country VALUES ('TCD','Chad',1284000.00,7651000,3337), ('CZE','Czech Republic',78866.00,10278100,3339), ('TUN','Tunisia',163610.00,9586000,3349), -('TUR','Turkey',774815.00,66591000,3358), +('TUR','Türkiye',774815.00,66591000,3358), ('TKM','Turkmenistan',488100.00,4459000,3419), ('TCA','Turks and Caicos Islands',430.00,17000,3423), ('TUV','Tuvalu',26.00,12000,3424), diff --git a/mysql-test/main/cte_recursive.result b/mysql-test/main/cte_recursive.result index 7114ff1e072..ed416e7b355 100644 --- a/mysql-test/main/cte_recursive.result +++ b/mysql-test/main/cte_recursive.result @@ -5792,6 +5792,178 @@ r 3 drop table t1,t2,t3,x; # +# MDEV-30248: Embedded non-recursive CTE referring to base table 'x' +# within a CTE with name 'x' used in a subquery from +# select list of another CTE +# +CREATE TABLE x (a int) ENGINE=MyISAM; +INSERT INTO x VALUES (3),(7),(1); +CREATE TABLE t1 (b int) ENGINE=MYISAM; +INSERT INTO t1 VALUES (1); +WITH cte AS +( +SELECT +( +WITH x AS +(WITH x AS (SELECT a FROM x AS t) SELECT 1 AS b) +SELECT b FROM x AS r +) AS c +) +SELECT cte.c FROM cte; +c +1 +WITH cte AS +( +SELECT +( +WITH x AS +(WITH x AS (SELECT a FROM x AS t) SELECT b FROM t1) +SELECT b FROM x AS r +) AS c +) +SELECT cte.c FROM cte; +c +1 +WITH cte AS +( +SELECT +( +WITH x AS +(WITH y AS (SELECT a FROM x AS t) SELECT b FROM t1) +SELECT b FROM x AS r +) AS c +) +SELECT cte.c FROM cte; +c +1 +WITH cte AS +( +SELECT +( +WITH x AS +(WITH y(b) AS (SELECT a FROM x AS t LIMIT 1) SELECT b FROM y) +SELECT b FROM x AS r +) AS c +) +SELECT cte.c FROM cte; +c +3 +WITH cte AS +( +SELECT +( +WITH x AS +(WITH x(b) AS (SELECT a FROM x AS t LIMIT 1) SELECT b FROM x) +SELECT b FROM x AS r +) AS c +) +SELECT cte.c FROM cte; +c +3 +WITH x AS +( +SELECT +( +WITH x AS +(WITH x AS (SELECT a FROM x AS t) SELECT 1 AS b) +SELECT b FROM x AS r +) AS c +) +SELECT x.c from x; +c +1 +WITH cte AS +( +SELECT +( +WITH x AS +(WITH x AS (SELECT a FROM x AS t) SELECT 2 AS b) +SELECT r1.b FROM x AS r1, x AS r2 WHERE r1.b=r2.b +) AS c +) +SELECT cte.c from cte; +c +2 +DROP TABLE x; +WITH cte AS +( +SELECT +( +WITH x AS +(WITH x AS (SELECT a FROM x AS t) SELECT 1 AS b) +SELECT b FROM x AS r +) AS c +) +SELECT cte.c FROM cte; +ERROR 42S02: Table 'test.x' doesn't exist +WITH cte AS +( +SELECT +( +WITH x AS +(WITH x AS (SELECT a FROM x AS t) SELECT b FROM t1) +SELECT b FROM x AS r +) AS c +) +SELECT cte.c FROM cte; +ERROR 42S02: Table 'test.x' doesn't exist +WITH cte AS +( +SELECT +( +WITH x AS +(WITH y AS (SELECT a FROM x AS t) SELECT b FROM t1) +SELECT b FROM x AS r +) AS c +) +SELECT cte.c FROM cte; +ERROR 42S02: Table 'test.x' doesn't exist +WITH cte AS +( +SELECT +( +WITH x AS +(WITH y(b) AS (SELECT a FROM x AS t LIMIT 1) SELECT b FROM y) +SELECT b FROM x AS r +) AS c +) +SELECT cte.c FROM cte; +ERROR 42S02: Table 'test.x' doesn't exist +WITH cte AS +( +SELECT +( +WITH x AS +(WITH x(b) AS (SELECT a FROM x AS t LIMIT 1) SELECT b FROM x) +SELECT b FROM x AS r +) AS c +) +SELECT cte.c FROM cte; +ERROR 42S02: Table 'test.x' doesn't exist +WITH x AS +( +SELECT +( +WITH x AS +(WITH x AS (SELECT a FROM x AS t) SELECT 1 AS b) +SELECT b FROM x AS r +) AS c +) +SELECT x.c from x; +ERROR 42S02: Table 'test.x' doesn't exist +WITH cte AS +( +SELECT +( +WITH x AS +(WITH x AS (SELECT a FROM x AS t) SELECT 2 AS b) +SELECT r1.b FROM x AS r1, x AS r2 WHERE r1.b=r2.b +) AS c +) +SELECT cte.c from cte; +ERROR 42S02: Table 'test.x' doesn't exist +DROP TABLE t1; +# # End of 10.3 tests # # diff --git a/mysql-test/main/cte_recursive.test b/mysql-test/main/cte_recursive.test index 06237c7b5dc..4a21c12a9f8 100644 --- a/mysql-test/main/cte_recursive.test +++ b/mysql-test/main/cte_recursive.test @@ -3878,6 +3878,129 @@ select * from cte; drop table t1,t2,t3,x; --echo # +--echo # MDEV-30248: Embedded non-recursive CTE referring to base table 'x' +--echo # within a CTE with name 'x' used in a subquery from +--echo # select list of another CTE +--echo # + +CREATE TABLE x (a int) ENGINE=MyISAM; +INSERT INTO x VALUES (3),(7),(1); +CREATE TABLE t1 (b int) ENGINE=MYISAM; +INSERT INTO t1 VALUES (1); + +let $q1= +WITH cte AS +( + SELECT + ( + WITH x AS + (WITH x AS (SELECT a FROM x AS t) SELECT 1 AS b) + SELECT b FROM x AS r + ) AS c +) +SELECT cte.c FROM cte; +eval $q1; + +let $q2= +WITH cte AS +( + SELECT + ( + WITH x AS + (WITH x AS (SELECT a FROM x AS t) SELECT b FROM t1) + SELECT b FROM x AS r + ) AS c +) +SELECT cte.c FROM cte; +eval $q2; + +let $q3= +WITH cte AS +( + SELECT + ( + WITH x AS + (WITH y AS (SELECT a FROM x AS t) SELECT b FROM t1) + SELECT b FROM x AS r + ) AS c +) +SELECT cte.c FROM cte; +eval $q3; + + +let $q4= +WITH cte AS +( + SELECT + ( + WITH x AS + (WITH y(b) AS (SELECT a FROM x AS t LIMIT 1) SELECT b FROM y) + SELECT b FROM x AS r + ) AS c +) +SELECT cte.c FROM cte; +eval $q4; + +let $q5= +WITH cte AS +( + SELECT + ( + WITH x AS + (WITH x(b) AS (SELECT a FROM x AS t LIMIT 1) SELECT b FROM x) + SELECT b FROM x AS r + ) AS c +) +SELECT cte.c FROM cte; +eval $q5; + +let $q6= +WITH x AS +( + SELECT + ( + WITH x AS + (WITH x AS (SELECT a FROM x AS t) SELECT 1 AS b) + SELECT b FROM x AS r + ) AS c +) +SELECT x.c from x; +eval $q6; + +let $q7= +WITH cte AS +( + SELECT + ( + WITH x AS + (WITH x AS (SELECT a FROM x AS t) SELECT 2 AS b) + SELECT r1.b FROM x AS r1, x AS r2 WHERE r1.b=r2.b + ) AS c +) +SELECT cte.c from cte; +eval $q7; + + +DROP TABLE x; + +--ERROR ER_NO_SUCH_TABLE +eval $q1; +--ERROR ER_NO_SUCH_TABLE +eval $q2; +--ERROR ER_NO_SUCH_TABLE +eval $q3; +--ERROR ER_NO_SUCH_TABLE +eval $q4; +--ERROR ER_NO_SUCH_TABLE +eval $q5; +--ERROR ER_NO_SUCH_TABLE +eval $q6; +--ERROR ER_NO_SUCH_TABLE +eval $q7; + +DROP TABLE t1; + +--echo # --echo # End of 10.3 tests --echo # diff --git a/mysql-test/main/ctype_ldml.result b/mysql-test/main/ctype_ldml.result index df009b77bca..efafa9dc041 100644 --- a/mysql-test/main/ctype_ldml.result +++ b/mysql-test/main/ctype_ldml.result @@ -3041,6 +3041,51 @@ SELECT 'chž'< 'i'; SELECT 'a' COLLATE utf8_czech_test_bad_w2; ERROR HY000: Unknown collation: 'utf8_czech_test_bad_w2' # +# End of 10.2 tests +# +# +# Start of 10.3 tests +# +# +# MDEV-30556 UPPER() returns an empty string for U+0251 in Unicode-5.2.0+ collations for utf8 +# +SET NAMES utf8mb4 COLLATE utf8mb4_test_520_nopad_ci; +CREATE OR REPLACE TABLE case_folding AS SELECT 0 AS code, SPACE(32) AS c LIMIT 0; +SHOW CREATE TABLE case_folding; +Table Create Table +case_folding CREATE TABLE `case_folding` ( + `code` int(1) NOT NULL, + `c` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_test_520_nopad_ci DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +INSERT INTO case_folding (code) VALUES +(0x23A), +(0x23E), +(0x23F), +(0x240), +(0x250), +(0x251), +(0x252), +(0x26B), +(0x271), +(0x27D); +UPDATE case_folding SET c=CHAR(code USING ucs2); +SELECT HEX(code), HEX(LOWER(c)), HEX(UPPER(c)), c FROM case_folding; +HEX(code) HEX(LOWER(c)) HEX(UPPER(c)) c +23A E2B1A5 C8BA Ⱥ +23E E2B1A6 C8BE Ⱦ +23F C8BF E2B1BE È¿ +240 C980 E2B1BF É€ +250 C990 E2B1AF É +251 C991 E2B1AD É‘ +252 C992 E2B1B0 É’ +26B C9AB E2B1A2 É« +271 C9B1 E2B1AE ɱ +27D C9BD E2B1A4 ɽ +DROP TABLE case_folding; +# +# End of 10.3 tests +# +# # MDEV-7947 my_charset_same: strcmp() takes 0.37% in OLTP RO # SHOW COLLATION LIKE 'latin1_test_replace'; @@ -3048,6 +3093,9 @@ Collation Charset Id Default Compiled Sortlen SELECT 'foo' = 'foo ' COLLATE latin1_test_replace; ERROR HY000: Unknown collation: 'latin1_test_replace' # +# End of 10.5 tests +# +# # MDEV-27042 UCA: Resetting contractions to ignorable does not work well # CREATE TABLE t1 ( @@ -3082,6 +3130,9 @@ tèl.123 10020E8B0F2E025D0E2A0E2B0E2C tex.123 10020E8B105A025D0E2A0E2B0E2C DROP TABLE t1; # +# End of 10.8 tests +# +# # MDEV-27009 Add UCA-14.0.0 collations # CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_test01_as_ci, comment VARCHAR(128)); @@ -3178,3 +3229,6 @@ is_400 0 is_520 0 is_1400 1 DROP TABLE t1; +# +# End of 10.10 tests +# diff --git a/mysql-test/main/ctype_ldml.test b/mysql-test/main/ctype_ldml.test index ed3234970bb..5ee56c3b950 100644 --- a/mysql-test/main/ctype_ldml.test +++ b/mysql-test/main/ctype_ldml.test @@ -611,6 +611,27 @@ SELECT 'chž'< 'i'; --error ER_UNKNOWN_COLLATION SELECT 'a' COLLATE utf8_czech_test_bad_w2; +--echo # +--echo # End of 10.2 tests +--echo # + + +--echo # +--echo # Start of 10.3 tests +--echo # + +--echo # +--echo # MDEV-30556 UPPER() returns an empty string for U+0251 in Unicode-5.2.0+ collations for utf8 +--echo # + +SET NAMES utf8mb4 COLLATE utf8mb4_test_520_nopad_ci; +--source include/ctype_casefolding.inc + + +--echo # +--echo # End of 10.3 tests +--echo # + --echo # --echo # MDEV-7947 my_charset_same: strcmp() takes 0.37% in OLTP RO @@ -620,6 +641,9 @@ SHOW COLLATION LIKE 'latin1_test_replace'; --error ER_UNKNOWN_COLLATION SELECT 'foo' = 'foo ' COLLATE latin1_test_replace; +--echo # +--echo # End of 10.5 tests +--echo # --echo # --echo # MDEV-27042 UCA: Resetting contractions to ignorable does not work well @@ -640,6 +664,9 @@ SELECT * FROM t1 WHERE phone<>'123' ORDER BY BINARY phone; SELECT phone, HEX(WEIGHT_STRING(phone)) FROM t1 ORDER BY phone, BINARY phone; DROP TABLE t1; +--echo # +--echo # End of 10.8 tests +--echo # --echo # --echo # MDEV-27009 Add UCA-14.0.0 collations @@ -669,3 +696,7 @@ FROM t1 ORDER BY a, HEX(a); --horizontal_results DROP TABLE t1; + +--echo # +--echo # End of 10.10 tests +--echo # diff --git a/mysql-test/main/ctype_uca.result b/mysql-test/main/ctype_uca.result index a2f9822131b..b567137df1e 100644 --- a/mysql-test/main/ctype_uca.result +++ b/mysql-test/main/ctype_uca.result @@ -8204,7 +8204,7 @@ INSERT INTO t1 VALUES (_utf32 0x2CEE); SELECT hex(c), hex(lower(c)), hex(upper(c)), hex(weight_string(c)), c FROM t1 ORDER BY c, BINARY c; hex(c) hex(lower(c)) hex(upper(c)) hex(weight_string(c)) c -C8BA C8BA 1214 Ⱥ +C8BA E2B1A5 C8BA 1214 Ⱥ E2B1A5 E2B1A5 C8BA 1214 â±¥ C680 C680 C983 122D Æ€ C983 C680 C983 122D Ƀ @@ -8229,7 +8229,7 @@ E2B1AA E2B1AA E2B1A9 1328 ⱪ C8BD C69A C8BD 133B Ƚ E2B1A0 E2B1A1 E2B1A0 133F â± E2B1A1 E2B1A1 E2B1A0 133F ⱡ -C9AB C9AB 1340 É« +C9AB C9AB E2B1A2 1340 É« E2B1A2 C9AB E2B1A2 1340 â±¢ E1B5BD E1B5BD E2B1A3 13B8 áµ½ E2B1A3 E1B5BD E2B1A3 13B8 â±£ @@ -8237,11 +8237,11 @@ C98A C98B C98A 13D2 ÉŠ C98B C98B C98A 13D2 É‹ C98C C98D C98C 13E4 ÉŒ C98D C98D C98C 13E4 É -C9BD C9BD 13FC ɽ +C9BD C9BD E2B1A4 13FC ɽ E2B1A4 C9BD E2B1A4 13FC Ɽ EA9CA8 EA9CA9 EA9CA8 143314AD Ꜩ EA9CA9 EA9CA9 EA9CA8 143314AD ꜩ -C8BE C8BE 143C Ⱦ +C8BE E2B1A6 C8BE 143C Ⱦ E2B1A6 E2B1A6 C8BE 143C ⱦ C984 CA89 C984 145B É„ CA89 CA89 C984 145B ʉ diff --git a/mysql-test/main/ctype_utf8.result b/mysql-test/main/ctype_utf8.result index 6d50a6cee2e..7a40c361e81 100644 --- a/mysql-test/main/ctype_utf8.result +++ b/mysql-test/main/ctype_utf8.result @@ -11384,6 +11384,181 @@ a # End of 10.3 tests # # +# Start of 10.4 tests +# +# +# MDEV-27653 long uniques don't work with unicode collations +# +SET NAMES utf8mb3; +CREATE TABLE t1 ( +a CHAR(30) COLLATE utf8mb3_general_ci, +UNIQUE KEY(a) USING HASH +); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` char(30) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL, + UNIQUE KEY `a` (`a`) USING HASH +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +INSERT INTO t1 VALUES ('a'); +INSERT INTO t1 VALUES ('ä'); +ERROR 23000: Duplicate entry 'ä' for key 'a' +SELECT * FROM t1; +a +a +DROP TABLE t1; +CREATE TABLE t1 ( +a CHAR(30) COLLATE utf8mb3_general_ci, +UNIQUE KEY(a(10)) USING HASH +); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` char(30) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL, + UNIQUE KEY `a` (`a`(10)) USING HASH +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +INSERT INTO t1 VALUES ('a'); +INSERT INTO t1 VALUES ('ä'); +ERROR 23000: Duplicate entry 'ä' for key 'a' +SELECT * FROM t1; +a +a +DROP TABLE t1; +CREATE TABLE t1 ( +a VARCHAR(30) COLLATE utf8mb3_general_ci, +UNIQUE KEY(a) USING HASH +); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(30) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL, + UNIQUE KEY `a` (`a`) USING HASH +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +INSERT INTO t1 VALUES ('a'); +INSERT INTO t1 VALUES ('ä'); +ERROR 23000: Duplicate entry 'ä' for key 'a' +SELECT * FROM t1; +a +a +DROP TABLE t1; +CREATE TABLE t1 ( +a VARCHAR(30) COLLATE utf8mb3_general_ci, +UNIQUE KEY(a(10)) USING HASH +); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(30) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL, + UNIQUE KEY `a` (`a`(10)) USING HASH +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +INSERT INTO t1 VALUES ('a'); +INSERT INTO t1 VALUES ('ä'); +ERROR 23000: Duplicate entry 'ä' for key 'a' +SELECT * FROM t1; +a +a +DROP TABLE t1; +CREATE TABLE t1 (a TEXT COLLATE utf8mb3_general_ci UNIQUE); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` text CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL, + UNIQUE KEY `a` (`a`) USING HASH +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +INSERT INTO t1 VALUES ('a'); +INSERT INTO t1 VALUES ('ä'); +ERROR 23000: Duplicate entry 'ä' for key 'a' +SELECT * FROM t1; +a +a +DROP TABLE t1; +CREATE TABLE t1 ( +a LONGTEXT COLLATE utf8mb3_general_ci, +UNIQUE KEY(a(10)) USING HASH +); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` longtext CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL, + UNIQUE KEY `a` (`a`(10)) USING HASH +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +INSERT INTO t1 VALUES ('a'); +INSERT INTO t1 VALUES ('ä'); +ERROR 23000: Duplicate entry 'ä' for key 'a' +SELECT * FROM t1; +a +a +DROP TABLE t1; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` text CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL, + UNIQUE KEY `a` (`a`) USING HASH +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +SELECT a, OCTET_LENGTH(a) FROM t1 ORDER BY BINARY a; +a OCTET_LENGTH(a) +a 1 +ä 2 +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check error Upgrade required. Please do "REPAIR TABLE `t1`" or dump/reload to fix it! +INSERT INTO t1 VALUES ('A'); +ERROR 23000: Duplicate entry 'A' for key 'a' +INSERT INTO t1 VALUES ('Ä'); +ERROR 23000: Duplicate entry 'Ä' for key 'a' +INSERT INTO t1 VALUES ('Ấ'); +SELECT a, OCTET_LENGTH(a) FROM t1 ORDER BY BINARY a; +a OCTET_LENGTH(a) +a 1 +ä 2 +Ấ 3 +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check error Upgrade required. Please do "REPAIR TABLE `t1`" or dump/reload to fix it! +ALTER TABLE t1 FORCE; +ERROR 23000: Duplicate entry 'ä' for key 'a' +DELETE FROM t1 WHERE OCTET_LENGTH(a)>1; +ALTER TABLE t1 FORCE; +INSERT INTO t1 VALUES ('ä'); +ERROR 23000: Duplicate entry 'ä' for key 'a' +DROP TABLE t1; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` text CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL, + UNIQUE KEY `a` (`a`) USING HASH +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +SELECT a, OCTET_LENGTH(a) FROM t1 ORDER BY BINARY a; +a OCTET_LENGTH(a) +a 1 +ä 2 +ALTER IGNORE TABLE t1 FORCE; +SELECT a, OCTET_LENGTH(a) FROM t1 ORDER BY BINARY a; +a OCTET_LENGTH(a) +a 1 +DROP TABLE t1; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` text CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL, + UNIQUE KEY `a` (`a`) USING HASH +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +SELECT a, OCTET_LENGTH(a) FROM t1 ORDER BY BINARY a; +a OCTET_LENGTH(a) +a 1 +ä 2 +REPAIR TABLE t1; +Table Op Msg_type Msg_text +test.t1 repair Warning Number of rows changed from 2 to 1 +test.t1 repair status OK +SELECT a, OCTET_LENGTH(a) FROM t1 ORDER BY BINARY a; +a OCTET_LENGTH(a) +a 1 +DROP TABLE t1; +# +# End of 10.4 tests +# +# # Start of 10.5 tests # # diff --git a/mysql-test/main/ctype_utf8.test b/mysql-test/main/ctype_utf8.test index c27dc97a69f..a875fe51f3a 100644 --- a/mysql-test/main/ctype_utf8.test +++ b/mysql-test/main/ctype_utf8.test @@ -2314,6 +2314,164 @@ VALUES (_latin1 0xDF) UNION VALUES(_utf8'a' COLLATE utf8_bin); --echo # +--echo # Start of 10.4 tests +--echo # + +--echo # +--echo # MDEV-27653 long uniques don't work with unicode collations +--echo # + +SET NAMES utf8mb3; + +# CHAR + +CREATE TABLE t1 ( + a CHAR(30) COLLATE utf8mb3_general_ci, + UNIQUE KEY(a) USING HASH +); +SHOW CREATE TABLE t1; +INSERT INTO t1 VALUES ('a'); +--error ER_DUP_ENTRY +INSERT INTO t1 VALUES ('ä'); +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 ( + a CHAR(30) COLLATE utf8mb3_general_ci, + UNIQUE KEY(a(10)) USING HASH +); +SHOW CREATE TABLE t1; +INSERT INTO t1 VALUES ('a'); +--error ER_DUP_ENTRY +INSERT INTO t1 VALUES ('ä'); +SELECT * FROM t1; +DROP TABLE t1; + + +# VARCHAR + +CREATE TABLE t1 ( + a VARCHAR(30) COLLATE utf8mb3_general_ci, + UNIQUE KEY(a) USING HASH +); +SHOW CREATE TABLE t1; +INSERT INTO t1 VALUES ('a'); +--error ER_DUP_ENTRY +INSERT INTO t1 VALUES ('ä'); +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 ( + a VARCHAR(30) COLLATE utf8mb3_general_ci, + UNIQUE KEY(a(10)) USING HASH +); +SHOW CREATE TABLE t1; +INSERT INTO t1 VALUES ('a'); +--error ER_DUP_ENTRY +INSERT INTO t1 VALUES ('ä'); +SELECT * FROM t1; +DROP TABLE t1; + + +# TEXT + +CREATE TABLE t1 (a TEXT COLLATE utf8mb3_general_ci UNIQUE); +SHOW CREATE TABLE t1; +INSERT INTO t1 VALUES ('a'); +--error ER_DUP_ENTRY +INSERT INTO t1 VALUES ('ä'); +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 ( + a LONGTEXT COLLATE utf8mb3_general_ci, + UNIQUE KEY(a(10)) USING HASH +); +SHOW CREATE TABLE t1; +INSERT INTO t1 VALUES ('a'); +--error ER_DUP_ENTRY +INSERT INTO t1 VALUES ('ä'); +SELECT * FROM t1; +DROP TABLE t1; + + +# Testing upgrade: +# Prior to MDEV-27653, the UNIQUE HASH function errorneously +# took into account string octet length. +# Old tables should still open and work, but with wrong results. + +copy_file std_data/mysql_upgrade/mdev27653_100422_myisam_text.frm $MYSQLD_DATADIR/test/t1.frm; +copy_file std_data/mysql_upgrade/mdev27653_100422_myisam_text.MYD $MYSQLD_DATADIR/test/t1.MYD; +copy_file std_data/mysql_upgrade/mdev27653_100422_myisam_text.MYI $MYSQLD_DATADIR/test/t1.MYI; +SHOW CREATE TABLE t1; +SELECT a, OCTET_LENGTH(a) FROM t1 ORDER BY BINARY a; +CHECK TABLE t1; + +# There is already a one byte value 'a' in the table +--error ER_DUP_ENTRY +INSERT INTO t1 VALUES ('A'); + +# There is already a two-byte value 'ä' in the table +--error ER_DUP_ENTRY +INSERT INTO t1 VALUES ('Ä'); + +# There were no three-byte values in the table so far. +# The below value violates UNIQUE, but it gets inserted. +# This is wrong but expected for a pre-MDEV-27653 table. +INSERT INTO t1 VALUES ('Ấ'); +SELECT a, OCTET_LENGTH(a) FROM t1 ORDER BY BINARY a; +CHECK TABLE t1; + +# ALTER FORCE fails: it tries to rebuild the table +# with a correct UNIQUE HASH function, but there are duplicates! +--error ER_DUP_ENTRY +ALTER TABLE t1 FORCE; + +# Let's remove all duplicate values, so only the one-byte 'a' stays. +# ALTER..FORCE should work after that. +DELETE FROM t1 WHERE OCTET_LENGTH(a)>1; +ALTER TABLE t1 FORCE; + +# Make sure that 'a' and 'ä' cannot co-exists any more, +# because the table was recreated with a correct UNIQUE HASH function. +--error ER_DUP_ENTRY +INSERT INTO t1 VALUES ('ä'); +DROP TABLE t1; + +# +# Testing an old table with ALTER IGNORE. +# The table is expected to rebuild with a new hash function, +# duplicates go away. +# +copy_file std_data/mysql_upgrade/mdev27653_100422_myisam_text.frm $MYSQLD_DATADIR/test/t1.frm; +copy_file std_data/mysql_upgrade/mdev27653_100422_myisam_text.MYD $MYSQLD_DATADIR/test/t1.MYD; +copy_file std_data/mysql_upgrade/mdev27653_100422_myisam_text.MYI $MYSQLD_DATADIR/test/t1.MYI; +SHOW CREATE TABLE t1; +SELECT a, OCTET_LENGTH(a) FROM t1 ORDER BY BINARY a; +ALTER IGNORE TABLE t1 FORCE; +SELECT a, OCTET_LENGTH(a) FROM t1 ORDER BY BINARY a; +DROP TABLE t1; + +# +# Testing an old table with REPAIR. +# The table is expected to rebuild with a new hash function, +# duplicates go away. +# +copy_file std_data/mysql_upgrade/mdev27653_100422_myisam_text.frm $MYSQLD_DATADIR/test/t1.frm; +copy_file std_data/mysql_upgrade/mdev27653_100422_myisam_text.MYD $MYSQLD_DATADIR/test/t1.MYD; +copy_file std_data/mysql_upgrade/mdev27653_100422_myisam_text.MYI $MYSQLD_DATADIR/test/t1.MYI; +SHOW CREATE TABLE t1; +SELECT a, OCTET_LENGTH(a) FROM t1 ORDER BY BINARY a; +REPAIR TABLE t1; +SELECT a, OCTET_LENGTH(a) FROM t1 ORDER BY BINARY a; +DROP TABLE t1; + +--echo # +--echo # End of 10.4 tests +--echo # + + +--echo # --echo # Start of 10.5 tests --echo # diff --git a/mysql-test/main/ctype_utf8_uca.result b/mysql-test/main/ctype_utf8_uca.result index 700d4c40193..e31acf8edc4 100644 --- a/mysql-test/main/ctype_utf8_uca.result +++ b/mysql-test/main/ctype_utf8_uca.result @@ -588,6 +588,180 @@ DROP TABLE t1; # End of 10.2 tests # # +# Start of 10.3 tests +# +# +# MDEV-30556 UPPER() returns an empty string for U+0251 in Unicode-5.2.0+ collations for utf8 +# +SET NAMES utf8mb3 COLLATE utf8mb3_unicode_ci /*Unicode-4.0 folding*/; +CREATE OR REPLACE TABLE case_folding AS SELECT 0 AS code, SPACE(32) AS c LIMIT 0; +SHOW CREATE TABLE case_folding; +Table Create Table +case_folding CREATE TABLE `case_folding` ( + `code` int(1) NOT NULL, + `c` varchar(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +INSERT INTO case_folding (code) VALUES +(0x23A), +(0x23E), +(0x23F), +(0x240), +(0x250), +(0x251), +(0x252), +(0x26B), +(0x271), +(0x27D); +UPDATE case_folding SET c=CHAR(code USING ucs2); +SELECT HEX(code), HEX(LOWER(c)), HEX(UPPER(c)), c FROM case_folding; +HEX(code) HEX(LOWER(c)) HEX(UPPER(c)) c +23A C8BA C8BA Ⱥ +23E C8BE C8BE Ⱦ +23F C8BF C8BF È¿ +240 C980 C980 É€ +250 C990 C990 É +251 C991 C991 É‘ +252 C992 C992 É’ +26B C9AB C9AB É« +271 C9B1 C9B1 ɱ +27D C9BD C9BD ɽ +DROP TABLE case_folding; +SET NAMES utf8mb3 COLLATE utf8mb3_unicode_520_ci; +CREATE OR REPLACE TABLE case_folding AS SELECT 0 AS code, SPACE(32) AS c LIMIT 0; +SHOW CREATE TABLE case_folding; +Table Create Table +case_folding CREATE TABLE `case_folding` ( + `code` int(1) NOT NULL, + `c` varchar(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_520_ci DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +INSERT INTO case_folding (code) VALUES +(0x23A), +(0x23E), +(0x23F), +(0x240), +(0x250), +(0x251), +(0x252), +(0x26B), +(0x271), +(0x27D); +UPDATE case_folding SET c=CHAR(code USING ucs2); +SELECT HEX(code), HEX(LOWER(c)), HEX(UPPER(c)), c FROM case_folding; +HEX(code) HEX(LOWER(c)) HEX(UPPER(c)) c +23A E2B1A5 C8BA Ⱥ +23E E2B1A6 C8BE Ⱦ +23F C8BF E2B1BE È¿ +240 C980 E2B1BF É€ +250 C990 E2B1AF É +251 C991 E2B1AD É‘ +252 C992 E2B1B0 É’ +26B C9AB E2B1A2 É« +271 C9B1 E2B1AE ɱ +27D C9BD E2B1A4 ɽ +DROP TABLE case_folding; +SET NAMES utf8mb3 COLLATE utf8mb3_unicode_520_nopad_ci; +CREATE OR REPLACE TABLE case_folding AS SELECT 0 AS code, SPACE(32) AS c LIMIT 0; +SHOW CREATE TABLE case_folding; +Table Create Table +case_folding CREATE TABLE `case_folding` ( + `code` int(1) NOT NULL, + `c` varchar(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_520_nopad_ci DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +INSERT INTO case_folding (code) VALUES +(0x23A), +(0x23E), +(0x23F), +(0x240), +(0x250), +(0x251), +(0x252), +(0x26B), +(0x271), +(0x27D); +UPDATE case_folding SET c=CHAR(code USING ucs2); +SELECT HEX(code), HEX(LOWER(c)), HEX(UPPER(c)), c FROM case_folding; +HEX(code) HEX(LOWER(c)) HEX(UPPER(c)) c +23A E2B1A5 C8BA Ⱥ +23E E2B1A6 C8BE Ⱦ +23F C8BF E2B1BE È¿ +240 C980 E2B1BF É€ +250 C990 E2B1AF É +251 C991 E2B1AD É‘ +252 C992 E2B1B0 É’ +26B C9AB E2B1A2 É« +271 C9B1 E2B1AE ɱ +27D C9BD E2B1A4 ɽ +DROP TABLE case_folding; +SET NAMES utf8mb3 COLLATE utf8mb3_myanmar_ci; +CREATE OR REPLACE TABLE case_folding AS SELECT 0 AS code, SPACE(32) AS c LIMIT 0; +SHOW CREATE TABLE case_folding; +Table Create Table +case_folding CREATE TABLE `case_folding` ( + `code` int(1) NOT NULL, + `c` varchar(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_myanmar_ci DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +INSERT INTO case_folding (code) VALUES +(0x23A), +(0x23E), +(0x23F), +(0x240), +(0x250), +(0x251), +(0x252), +(0x26B), +(0x271), +(0x27D); +UPDATE case_folding SET c=CHAR(code USING ucs2); +SELECT HEX(code), HEX(LOWER(c)), HEX(UPPER(c)), c FROM case_folding; +HEX(code) HEX(LOWER(c)) HEX(UPPER(c)) c +23A E2B1A5 C8BA Ⱥ +23E E2B1A6 C8BE Ⱦ +23F C8BF E2B1BE È¿ +240 C980 E2B1BF É€ +250 C990 E2B1AF É +251 C991 E2B1AD É‘ +252 C992 E2B1B0 É’ +26B C9AB E2B1A2 É« +271 C9B1 E2B1AE ɱ +27D C9BD E2B1A4 ɽ +DROP TABLE case_folding; +SET NAMES utf8mb3 COLLATE utf8mb3_thai_520_w2; +CREATE OR REPLACE TABLE case_folding AS SELECT 0 AS code, SPACE(32) AS c LIMIT 0; +SHOW CREATE TABLE case_folding; +Table Create Table +case_folding CREATE TABLE `case_folding` ( + `code` int(1) NOT NULL, + `c` varchar(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_thai_520_w2 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +INSERT INTO case_folding (code) VALUES +(0x23A), +(0x23E), +(0x23F), +(0x240), +(0x250), +(0x251), +(0x252), +(0x26B), +(0x271), +(0x27D); +UPDATE case_folding SET c=CHAR(code USING ucs2); +SELECT HEX(code), HEX(LOWER(c)), HEX(UPPER(c)), c FROM case_folding; +HEX(code) HEX(LOWER(c)) HEX(UPPER(c)) c +23A E2B1A5 C8BA Ⱥ +23E E2B1A6 C8BE Ⱦ +23F C8BF E2B1BE È¿ +240 C980 E2B1BF É€ +250 C990 E2B1AF É +251 C991 E2B1AD É‘ +252 C992 E2B1B0 É’ +26B C9AB E2B1A2 É« +271 C9B1 E2B1AE ɱ +27D C9BD E2B1A4 ɽ +DROP TABLE case_folding; +# +# End of 10.3 tests +# +# # Start of 10.10 tests # # diff --git a/mysql-test/main/ctype_utf8_uca.test b/mysql-test/main/ctype_utf8_uca.test index 6ac5060a976..946649417ae 100644 --- a/mysql-test/main/ctype_utf8_uca.test +++ b/mysql-test/main/ctype_utf8_uca.test @@ -22,6 +22,35 @@ SET NAMES utf8 COLLATE utf8_unicode_nopad_ci; --echo # End of 10.2 tests --echo # + +--echo # +--echo # Start of 10.3 tests +--echo # + +--echo # +--echo # MDEV-30556 UPPER() returns an empty string for U+0251 in Unicode-5.2.0+ collations for utf8 +--echo # + +SET NAMES utf8mb3 COLLATE utf8mb3_unicode_ci /*Unicode-4.0 folding*/; +--source include/ctype_casefolding.inc + +SET NAMES utf8mb3 COLLATE utf8mb3_unicode_520_ci; +--source include/ctype_casefolding.inc + +SET NAMES utf8mb3 COLLATE utf8mb3_unicode_520_nopad_ci; +--source include/ctype_casefolding.inc + +SET NAMES utf8mb3 COLLATE utf8mb3_myanmar_ci; +--source include/ctype_casefolding.inc + +SET NAMES utf8mb3 COLLATE utf8mb3_thai_520_w2; +--source include/ctype_casefolding.inc + + +--echo # +--echo # End of 10.3 tests +--echo # + --echo # --echo # Start of 10.10 tests --echo # @@ -47,7 +76,6 @@ SELECT f1(); DROP FUNCTION f1; SET NAMES utf8mb3; - --echo # --echo # End of 10.10 tests --echo # diff --git a/mysql-test/main/ctype_utf8mb4_uca.result b/mysql-test/main/ctype_utf8mb4_uca.result index 658e84fa644..195c47e0360 100644 --- a/mysql-test/main/ctype_utf8mb4_uca.result +++ b/mysql-test/main/ctype_utf8mb4_uca.result @@ -6606,7 +6606,178 @@ SET NAMES utf8mb4; # End of 10.2 tests # # -# End of 10.9 tests +# Start of 10.3 tests +# +# +# MDEV-30556 UPPER() returns an empty string for U+0251 in Unicode-5.2.0+ collations for utf8 +# +SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci /*Unicode-4.0 folding*/; +CREATE OR REPLACE TABLE case_folding AS SELECT 0 AS code, SPACE(32) AS c LIMIT 0; +SHOW CREATE TABLE case_folding; +Table Create Table +case_folding CREATE TABLE `case_folding` ( + `code` int(1) NOT NULL, + `c` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +INSERT INTO case_folding (code) VALUES +(0x23A), +(0x23E), +(0x23F), +(0x240), +(0x250), +(0x251), +(0x252), +(0x26B), +(0x271), +(0x27D); +UPDATE case_folding SET c=CHAR(code USING ucs2); +SELECT HEX(code), HEX(LOWER(c)), HEX(UPPER(c)), c FROM case_folding; +HEX(code) HEX(LOWER(c)) HEX(UPPER(c)) c +23A C8BA C8BA Ⱥ +23E C8BE C8BE Ⱦ +23F C8BF C8BF È¿ +240 C980 C980 É€ +250 C990 C990 É +251 C991 C991 É‘ +252 C992 C992 É’ +26B C9AB C9AB É« +271 C9B1 C9B1 ɱ +27D C9BD C9BD ɽ +DROP TABLE case_folding; +SET NAMES utf8mb4 COLLATE utf8mb4_unicode_520_ci; +CREATE OR REPLACE TABLE case_folding AS SELECT 0 AS code, SPACE(32) AS c LIMIT 0; +SHOW CREATE TABLE case_folding; +Table Create Table +case_folding CREATE TABLE `case_folding` ( + `code` int(1) NOT NULL, + `c` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +INSERT INTO case_folding (code) VALUES +(0x23A), +(0x23E), +(0x23F), +(0x240), +(0x250), +(0x251), +(0x252), +(0x26B), +(0x271), +(0x27D); +UPDATE case_folding SET c=CHAR(code USING ucs2); +SELECT HEX(code), HEX(LOWER(c)), HEX(UPPER(c)), c FROM case_folding; +HEX(code) HEX(LOWER(c)) HEX(UPPER(c)) c +23A E2B1A5 C8BA Ⱥ +23E E2B1A6 C8BE Ⱦ +23F C8BF E2B1BE È¿ +240 C980 E2B1BF É€ +250 C990 E2B1AF É +251 C991 E2B1AD É‘ +252 C992 E2B1B0 É’ +26B C9AB E2B1A2 É« +271 C9B1 E2B1AE ɱ +27D C9BD E2B1A4 ɽ +DROP TABLE case_folding; +SET NAMES utf8mb4 COLLATE utf8mb4_unicode_520_nopad_ci; +CREATE OR REPLACE TABLE case_folding AS SELECT 0 AS code, SPACE(32) AS c LIMIT 0; +SHOW CREATE TABLE case_folding; +Table Create Table +case_folding CREATE TABLE `case_folding` ( + `code` int(1) NOT NULL, + `c` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_nopad_ci DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +INSERT INTO case_folding (code) VALUES +(0x23A), +(0x23E), +(0x23F), +(0x240), +(0x250), +(0x251), +(0x252), +(0x26B), +(0x271), +(0x27D); +UPDATE case_folding SET c=CHAR(code USING ucs2); +SELECT HEX(code), HEX(LOWER(c)), HEX(UPPER(c)), c FROM case_folding; +HEX(code) HEX(LOWER(c)) HEX(UPPER(c)) c +23A E2B1A5 C8BA Ⱥ +23E E2B1A6 C8BE Ⱦ +23F C8BF E2B1BE È¿ +240 C980 E2B1BF É€ +250 C990 E2B1AF É +251 C991 E2B1AD É‘ +252 C992 E2B1B0 É’ +26B C9AB E2B1A2 É« +271 C9B1 E2B1AE ɱ +27D C9BD E2B1A4 ɽ +DROP TABLE case_folding; +SET NAMES utf8mb4 COLLATE utf8mb4_myanmar_ci; +CREATE OR REPLACE TABLE case_folding AS SELECT 0 AS code, SPACE(32) AS c LIMIT 0; +SHOW CREATE TABLE case_folding; +Table Create Table +case_folding CREATE TABLE `case_folding` ( + `code` int(1) NOT NULL, + `c` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_myanmar_ci DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +INSERT INTO case_folding (code) VALUES +(0x23A), +(0x23E), +(0x23F), +(0x240), +(0x250), +(0x251), +(0x252), +(0x26B), +(0x271), +(0x27D); +UPDATE case_folding SET c=CHAR(code USING ucs2); +SELECT HEX(code), HEX(LOWER(c)), HEX(UPPER(c)), c FROM case_folding; +HEX(code) HEX(LOWER(c)) HEX(UPPER(c)) c +23A E2B1A5 C8BA Ⱥ +23E E2B1A6 C8BE Ⱦ +23F C8BF E2B1BE È¿ +240 C980 E2B1BF É€ +250 C990 E2B1AF É +251 C991 E2B1AD É‘ +252 C992 E2B1B0 É’ +26B C9AB E2B1A2 É« +271 C9B1 E2B1AE ɱ +27D C9BD E2B1A4 ɽ +DROP TABLE case_folding; +SET NAMES utf8mb4 COLLATE utf8mb4_thai_520_w2; +CREATE OR REPLACE TABLE case_folding AS SELECT 0 AS code, SPACE(32) AS c LIMIT 0; +SHOW CREATE TABLE case_folding; +Table Create Table +case_folding CREATE TABLE `case_folding` ( + `code` int(1) NOT NULL, + `c` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_thai_520_w2 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +INSERT INTO case_folding (code) VALUES +(0x23A), +(0x23E), +(0x23F), +(0x240), +(0x250), +(0x251), +(0x252), +(0x26B), +(0x271), +(0x27D); +UPDATE case_folding SET c=CHAR(code USING ucs2); +SELECT HEX(code), HEX(LOWER(c)), HEX(UPPER(c)), c FROM case_folding; +HEX(code) HEX(LOWER(c)) HEX(UPPER(c)) c +23A E2B1A5 C8BA Ⱥ +23E E2B1A6 C8BE Ⱦ +23F C8BF E2B1BE È¿ +240 C980 E2B1BF É€ +250 C990 E2B1AF É +251 C991 E2B1AD É‘ +252 C992 E2B1B0 É’ +26B C9AB E2B1A2 É« +271 C9B1 E2B1AE ɱ +27D C9BD E2B1A4 ɽ +DROP TABLE case_folding; +# +# End of 10.3 tests # # # MDEV-27009 Add UCA-14.0.0 collations @@ -11208,5 +11379,5 @@ DROP TABLE t1; DROP PROCEDURE exec_verbose; DROP PROCEDURE test_styles; # -# End of 10.9 tests +# End of 10.10 tests # diff --git a/mysql-test/main/ctype_utf8mb4_uca.test b/mysql-test/main/ctype_utf8mb4_uca.test index 9ef001fd378..b4cfc910336 100644 --- a/mysql-test/main/ctype_utf8mb4_uca.test +++ b/mysql-test/main/ctype_utf8mb4_uca.test @@ -109,8 +109,33 @@ SET NAMES utf8mb4; --echo # End of 10.2 tests --echo # + +--echo # +--echo # Start of 10.3 tests +--echo # + +--echo # +--echo # MDEV-30556 UPPER() returns an empty string for U+0251 in Unicode-5.2.0+ collations for utf8 +--echo # + +SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci /*Unicode-4.0 folding*/; +--source include/ctype_casefolding.inc + +SET NAMES utf8mb4 COLLATE utf8mb4_unicode_520_ci; +--source include/ctype_casefolding.inc + +SET NAMES utf8mb4 COLLATE utf8mb4_unicode_520_nopad_ci; +--source include/ctype_casefolding.inc + +SET NAMES utf8mb4 COLLATE utf8mb4_myanmar_ci; +--source include/ctype_casefolding.inc + +SET NAMES utf8mb4 COLLATE utf8mb4_thai_520_w2; +--source include/ctype_casefolding.inc + + --echo # ---echo # End of 10.9 tests +--echo # End of 10.3 tests --echo # --echo # @@ -402,5 +427,5 @@ DROP PROCEDURE exec_verbose; DROP PROCEDURE test_styles; --echo # ---echo # End of 10.9 tests +--echo # End of 10.10 tests --echo # diff --git a/mysql-test/main/debug_sync.result b/mysql-test/main/debug_sync.result index bb9ae1a417d..4c1711a6d6b 100644 --- a/mysql-test/main/debug_sync.result +++ b/mysql-test/main/debug_sync.result @@ -2,7 +2,7 @@ SET DEBUG_SYNC= 'RESET'; DROP TABLE IF EXISTS t1; SHOW VARIABLES LIKE 'DEBUG_SYNC'; Variable_name Value -debug_sync ON - current signal: '' +debug_sync ON - current signals: '' SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2 TIMEOUT 6 EXECUTE 2 HIT_LIMIT 3'; SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2 TIMEOUT 6 EXECUTE 2'; SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2 TIMEOUT 6 HIT_LIMIT 3'; @@ -150,34 +150,34 @@ SET @myvar= 'now SIGNAL from_myvar'; SET DEBUG_SYNC= @myvar; SHOW VARIABLES LIKE 'DEBUG_SYNC'; Variable_name Value -debug_sync ON - current signal: 'from_myvar' +debug_sync ON - current signals: 'from_myvar' SET DEBUG_SYNC= LEFT('now SIGNAL from_function_cut_here', 24); SHOW VARIABLES LIKE 'DEBUG_SYNC'; Variable_name Value -debug_sync ON - current signal: 'from_function' +debug_sync ON - current signals: 'from_myvar,from_function' SET DEBUG_SYNC= 'now SIGNAL something'; SHOW VARIABLES LIKE 'DEBUG_SYNC'; Variable_name Value -debug_sync ON - current signal: 'something' +debug_sync ON - current signals: 'something,from_function,from_myvar' SET DEBUG_SYNC= 'now WAIT_FOR nothing TIMEOUT 0'; Warnings: Warning #### debug sync point wait timed out SET DEBUG_SYNC= 'now SIGNAL nothing'; SHOW VARIABLES LIKE 'DEBUG_SYNC'; Variable_name Value -debug_sync ON - current signal: 'nothing' +debug_sync ON - current signals: 'something,from_function,nothing,from_myvar' SET DEBUG_SYNC= 'now WAIT_FOR nothing TIMEOUT 0'; SET DEBUG_SYNC= 'now SIGNAL something EXECUTE 0'; SHOW VARIABLES LIKE 'DEBUG_SYNC'; Variable_name Value -debug_sync ON - current signal: 'nothing' +debug_sync ON - current signals: 'something,from_function,from_myvar' SET DEBUG_SYNC= 'now WAIT_FOR anotherthing TIMEOUT 0 EXECUTE 0'; SET DEBUG_SYNC= 'now HIT_LIMIT 1'; ERROR HY000: debug sync point hit limit reached SET DEBUG_SYNC= 'RESET'; SHOW VARIABLES LIKE 'DEBUG_SYNC'; Variable_name Value -debug_sync ON - current signal: '' +debug_sync ON - current signals: '' SET DEBUG_SYNC= 'p1abcd SIGNAL s1 EXECUTE 2'; SET DEBUG_SYNC= 'p2abc SIGNAL s2 EXECUTE 2'; SET DEBUG_SYNC= 'p9abcdef SIGNAL s9 EXECUTE 2'; @@ -190,23 +190,30 @@ SET DEBUG_SYNC= 'p3abcdef SIGNAL s3 EXECUTE 2'; SET DEBUG_SYNC= 'p4a TEST'; SHOW VARIABLES LIKE 'DEBUG_SYNC'; Variable_name Value -debug_sync ON - current signal: 's4' +debug_sync ON - current signals: 's4' SET DEBUG_SYNC= 'p1abcd TEST'; SHOW VARIABLES LIKE 'DEBUG_SYNC'; Variable_name Value -debug_sync ON - current signal: 's1' +debug_sync ON - current signals: 's4,s1' SET DEBUG_SYNC= 'p7 TEST'; SHOW VARIABLES LIKE 'DEBUG_SYNC'; Variable_name Value -debug_sync ON - current signal: 's7' +debug_sync ON - current signals: 's1,s7,s4' SET DEBUG_SYNC= 'p9abcdef TEST'; SHOW VARIABLES LIKE 'DEBUG_SYNC'; Variable_name Value -debug_sync ON - current signal: 's9' +debug_sync ON - current signals: 's1,s7,s4,s9' SET DEBUG_SYNC= 'p3abcdef TEST'; SHOW VARIABLES LIKE 'DEBUG_SYNC'; Variable_name Value -debug_sync ON - current signal: 's3' +debug_sync ON - current signals: 's1,s3,s4,s9,s7' +SET DEBUG_SYNC= 'now WAIT_FOR s9'; +SET DEBUG_SYNC= 'now WAIT_FOR s1'; +SET DEBUG_SYNC= 'now WAIT_FOR s4'; +SET DEBUG_SYNC= 'now WAIT_FOR s7'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +Variable_name Value +debug_sync ON - current signals: 's3' SET DEBUG_SYNC= 'p1abcd CLEAR'; SET DEBUG_SYNC= 'p2abc CLEAR'; SET DEBUG_SYNC= 'p5abcde CLEAR'; @@ -219,19 +226,19 @@ SET DEBUG_SYNC= 'p7 CLEAR'; SET DEBUG_SYNC= 'p1abcd TEST'; SHOW VARIABLES LIKE 'DEBUG_SYNC'; Variable_name Value -debug_sync ON - current signal: 's3' +debug_sync ON - current signals: 's3' SET DEBUG_SYNC= 'p7 TEST'; SHOW VARIABLES LIKE 'DEBUG_SYNC'; Variable_name Value -debug_sync ON - current signal: 's3' +debug_sync ON - current signals: 's3' SET DEBUG_SYNC= 'p9abcdef TEST'; SHOW VARIABLES LIKE 'DEBUG_SYNC'; Variable_name Value -debug_sync ON - current signal: 's3' +debug_sync ON - current signals: 's3' SET DEBUG_SYNC= 'RESET'; SHOW VARIABLES LIKE 'DEBUG_SYNC'; Variable_name Value -debug_sync ON - current signal: '' +debug_sync ON - current signals: '' CREATE USER mysqltest_1@localhost; GRANT SUPER ON *.* TO mysqltest_1@localhost; connect con1,localhost,mysqltest_1,,; @@ -292,4 +299,24 @@ disconnect con1; disconnect con2; connection default; DROP TABLE t1; +# +# Test NO_CLEAR_EVENT flag. The signal should still be visible after +# the wait has completed succesfully. +# +SET DEBUG_SYNC= 'now SIGNAL s1'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +Variable_name Value +debug_sync ON - current signals: 's1' +SET DEBUG_SYNC= 'now WAIT_FOR s1 NO_CLEAR_EVENT'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +Variable_name Value +debug_sync ON - current signals: 's1' +SET DEBUG_SYNC= 'now WAIT_FOR s1'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +Variable_name Value +debug_sync ON - current signals: '' +SET DEBUG_SYNC= 'now SIGNAL s1,s2,s5,s7'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +Variable_name Value +debug_sync ON - current signals: 's2,s7,s1,s5' SET DEBUG_SYNC= 'RESET'; diff --git a/mysql-test/main/debug_sync.test b/mysql-test/main/debug_sync.test index 89414939f59..6e75ba9624c 100644 --- a/mysql-test/main/debug_sync.test +++ b/mysql-test/main/debug_sync.test @@ -231,15 +231,12 @@ SHOW VARIABLES LIKE 'DEBUG_SYNC'; # immediately after setting of the DEBUG_SYNC variable. # So it is executed before the SET statement ends. # -# NOTE: There is only one global signal (say "signal post" or "flag mast"). -# A SIGNAL action writes its signal into it ("sets a flag"). -# The signal persists until explicitly overwritten. +# NOTE: There can be multiple active signals at the same time. +# A SIGNAL action appends its signal into signals set. +# The signal persists until waited on. # To avoid confusion for later tests, it is recommended to clear -# the signal by signalling "empty" ("setting the 'empty' flag"): -# SET DEBUG_SYNC= 'now SIGNAL empty'; -# Preferably you can reset the whole facility with: +# the signal set by running # SET DEBUG_SYNC= 'RESET'; -# The signal is then '' (really empty) which connot be done otherwise. # # @@ -298,6 +295,16 @@ SET DEBUG_SYNC= 'p9abcdef TEST'; SHOW VARIABLES LIKE 'DEBUG_SYNC'; SET DEBUG_SYNC= 'p3abcdef TEST'; SHOW VARIABLES LIKE 'DEBUG_SYNC'; + +# +# Wait for all signals currently active except s3. +# +SET DEBUG_SYNC= 'now WAIT_FOR s9'; +SET DEBUG_SYNC= 'now WAIT_FOR s1'; +SET DEBUG_SYNC= 'now WAIT_FOR s4'; +SET DEBUG_SYNC= 'now WAIT_FOR s7'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; + # # Clear the actions. # @@ -320,7 +327,7 @@ SHOW VARIABLES LIKE 'DEBUG_SYNC'; SET DEBUG_SYNC= 'p9abcdef TEST'; SHOW VARIABLES LIKE 'DEBUG_SYNC'; # -# Now cleanup. Actions are clear already, but signal needs to be cleared. +# Now cleanup. Actions are clear already, but s3 signal needs to be cleared. # SET DEBUG_SYNC= 'RESET'; SHOW VARIABLES LIKE 'DEBUG_SYNC'; @@ -418,10 +425,24 @@ disconnect con2; connection default; DROP TABLE t1; +--echo # +--echo # Test NO_CLEAR_EVENT flag. The signal should still be visible after +--echo # the wait has completed succesfully. +--echo # +SET DEBUG_SYNC= 'now SIGNAL s1'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +SET DEBUG_SYNC= 'now WAIT_FOR s1 NO_CLEAR_EVENT'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; +SET DEBUG_SYNC= 'now WAIT_FOR s1'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; + +SET DEBUG_SYNC= 'now SIGNAL s1,s2,s5,s7'; +SHOW VARIABLES LIKE 'DEBUG_SYNC'; + + # # Cleanup after test case. -# Otherwise signal would contain 'flushed' here, -# which could confuse the next test. +# Otherwise signal would confuse the next test. # SET DEBUG_SYNC= 'RESET'; diff --git a/mysql-test/main/delete.result b/mysql-test/main/delete.result index 830f60aa3c7..900c14c5578 100644 --- a/mysql-test/main/delete.result +++ b/mysql-test/main/delete.result @@ -532,3 +532,35 @@ DELETE v2 FROM v2; ERROR HY000: Can not delete from join view 'test.v2' DROP VIEW v2, v1; DROP TABLE t1, t2; +End of 5.5 tests +# +# MDEV-30586: DELETE with WHERE containing nested subquery +# with set function aggregated in outer subquery +# +create table t1 (a int); +insert into t1 values (3), (7), (1); +create table t2 (b int); +insert into t2 values (2), (1), (4), (7); +create table t3 (a int, b int); +insert into t3 values (2,10), (7,30), (2,30), (1,10), (7,40); +select * from t1 +where t1.a in (select t3.a from t3 group by t3.a +having t3.a > any (select t2.b from t2 +where t2.b*10 < sum(t3.b))); +a +7 +delete from t1 +where t1.a in (select t3.a from t3 group by t3.a +having t3.a > any (select t2.b from t2 +where t2.b*10 < sum(t3.b))); +select * from t1 +where t1.a in (select t3.a from t3 group by t3.a +having t3.a > any (select t2.b from t2 +where t2.b*10 < sum(t3.b))); +a +update t1 set t1.a=t1.a+10 +where t1.a in (select t3.a from t3 group by t3.a +having t3.a > any (select t2.b from t2 +where t2.b*10 < sum(t3.b))); +drop table t1,t2,t3; +End of 10.4 tests diff --git a/mysql-test/main/delete.test b/mysql-test/main/delete.test index b95919ee4a4..733384193e5 100644 --- a/mysql-test/main/delete.test +++ b/mysql-test/main/delete.test @@ -583,3 +583,44 @@ DELETE v2 FROM v2; DROP VIEW v2, v1; DROP TABLE t1, t2; + +--echo End of 5.5 tests + +--echo # +--echo # MDEV-30586: DELETE with WHERE containing nested subquery +--echo # with set function aggregated in outer subquery +--echo # + +create table t1 (a int); +insert into t1 values (3), (7), (1); + +create table t2 (b int); +insert into t2 values (2), (1), (4), (7); + +create table t3 (a int, b int); +insert into t3 values (2,10), (7,30), (2,30), (1,10), (7,40); + +let $c= + t1.a in (select t3.a from t3 group by t3.a + having t3.a > any (select t2.b from t2 + where t2.b*10 < sum(t3.b))); + +eval +select * from t1 + where $c; + +eval +delete from t1 + where $c; + +eval +select * from t1 + where $c; + +eval +update t1 set t1.a=t1.a+10 + where $c; + +drop table t1,t2,t3; + +--echo End of 10.4 tests diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index acc52daed43..122c4c08520 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -22509,6 +22509,69 @@ DROP TABLE transaction_items; DROP TABLE transactions; DROP TABLE charges; DROP TABLE ledgers; +# +# MDEV-30081: Splitting from a constant mergeable derived table +# used in inner part of an outer join. +# +CREATE TABLE t1 ( id int PRIMARY KEY ) ENGINE=MyISAM; +INSERT INTO t1 VALUES (3),(4),(7); +CREATE TABLE t2 ( +id int, id1 int, wid int, PRIMARY KEY (id), KEY (id1), KEY (wid) +) ENGINE=MyISAM; +INSERT INTO t2 VALUES (4,4,6),(7,7,7); +CREATE TABLE t3 ( +wid int, wtid int, otid int, oid int, +PRIMARY KEY (wid), KEY (wtid), KEY (otid), KEY (oid) +) ENGINE=MyISAM; +INSERT INTO t3 VALUES (6,30,6,6),(7,17,7,7); +CREATE TABLE t4 ( id int, a int, PRIMARY KEY (id), KEY (a) ) ENGINE=MyISAM; +INSERT INTO t4 VALUES (1,17),(2,15),(3,49),(4,3),(5,45),(6,38),(7,17); +CREATE TABLE t5 ( +id int, id1 int, PRIMARY KEY (id), KEY id1 (id1) +) ENGINE=MyISAM ; +INSERT INTO t5 VALUES (1,17),(2,15),(3,49),(4,3),(5,45),(6,38),(7,17); +ANALYZE TABLE t1,t2,t3,t4,t5; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status OK +test.t3 analyze status Engine-independent statistics collected +test.t3 analyze status OK +test.t4 analyze status Engine-independent statistics collected +test.t4 analyze status OK +test.t5 analyze status Engine-independent statistics collected +test.t5 analyze status OK +CREATE VIEW v1 AS (SELECT id1 FROM t5 GROUP BY id1); +SELECT t3.*, t1.id AS t1_id, t2.id AS t2_id, dt.*, v1.* +FROM +t1, t2, t3 +LEFT JOIN +(SELECT t4.* FROM t4 WHERE t4.a=3) dt +ON t3.oid = dt.id AND t3.otid = 14 +LEFT JOIN v1 +ON (v1.id1 = dt.a) +WHERE t3.oid = t1.id AND t3.oid = t2.id AND t3.wid = 7; +wid wtid otid oid t1_id t2_id id a id1 +7 17 7 7 7 7 NULL NULL NULL +EXPLAIN SELECT t3.*, t1.id AS t1_id, t2.id AS t2_id, dt.*, v1.* +FROM +t1, t2, t3 +LEFT JOIN +(SELECT t4.* FROM t4 WHERE t4.a=3) dt +ON t3.oid = dt.id AND t3.otid = 14 +LEFT JOIN v1 +ON (v1.id1 = dt.a) +WHERE t3.oid = t1.id AND t3.oid = t2.id AND t3.wid = 7; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 const PRIMARY,oid PRIMARY 4 const 1 +1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 Using index +1 PRIMARY t2 const PRIMARY PRIMARY 4 const 1 Using index +1 PRIMARY t4 const PRIMARY,a NULL NULL NULL 0 Impossible ON condition +1 PRIMARY <derived3> ref key0 key0 5 const 0 Using where +3 LATERAL DERIVED t5 ref id1 id1 5 const 0 Using index +DROP VIEW v1; +DROP TABLE t1,t2,t3,t4,t5; # End of 10.3 tests # # MDEV-18679: materialized view with SELECT S containing materialized diff --git a/mysql-test/main/derived_cond_pushdown.test b/mysql-test/main/derived_cond_pushdown.test index 2f2e72fb09f..26b44488e1d 100644 --- a/mysql-test/main/derived_cond_pushdown.test +++ b/mysql-test/main/derived_cond_pushdown.test @@ -4071,6 +4071,55 @@ DROP TABLE transactions; DROP TABLE charges; DROP TABLE ledgers; + +--echo # +--echo # MDEV-30081: Splitting from a constant mergeable derived table +--echo # used in inner part of an outer join. +--echo # + + CREATE TABLE t1 ( id int PRIMARY KEY ) ENGINE=MyISAM; +INSERT INTO t1 VALUES (3),(4),(7); + +CREATE TABLE t2 ( + id int, id1 int, wid int, PRIMARY KEY (id), KEY (id1), KEY (wid) +) ENGINE=MyISAM; +INSERT INTO t2 VALUES (4,4,6),(7,7,7); + +CREATE TABLE t3 ( + wid int, wtid int, otid int, oid int, + PRIMARY KEY (wid), KEY (wtid), KEY (otid), KEY (oid) +) ENGINE=MyISAM; +INSERT INTO t3 VALUES (6,30,6,6),(7,17,7,7); + +CREATE TABLE t4 ( id int, a int, PRIMARY KEY (id), KEY (a) ) ENGINE=MyISAM; +INSERT INTO t4 VALUES (1,17),(2,15),(3,49),(4,3),(5,45),(6,38),(7,17); + +CREATE TABLE t5 ( + id int, id1 int, PRIMARY KEY (id), KEY id1 (id1) +) ENGINE=MyISAM ; +INSERT INTO t5 VALUES (1,17),(2,15),(3,49),(4,3),(5,45),(6,38),(7,17); + +ANALYZE TABLE t1,t2,t3,t4,t5; + +CREATE VIEW v1 AS (SELECT id1 FROM t5 GROUP BY id1); + +let $q= +SELECT t3.*, t1.id AS t1_id, t2.id AS t2_id, dt.*, v1.* +FROM + t1, t2, t3 + LEFT JOIN + (SELECT t4.* FROM t4 WHERE t4.a=3) dt + ON t3.oid = dt.id AND t3.otid = 14 + LEFT JOIN v1 + ON (v1.id1 = dt.a) +WHERE t3.oid = t1.id AND t3.oid = t2.id AND t3.wid = 7; + +eval $q; +eval EXPLAIN $q; + +DROP VIEW v1; +DROP TABLE t1,t2,t3,t4,t5; + --echo # End of 10.3 tests --echo # diff --git a/mysql-test/main/empty_table.result b/mysql-test/main/empty_table.result index 2bca3e792fa..90aec2eda3b 100644 --- a/mysql-test/main/empty_table.result +++ b/mysql-test/main/empty_table.result @@ -16,3 +16,61 @@ ERROR 42S02: Table 'test.t2' doesn't exist show status like "Empty_queries"; Variable_name Value Empty_queries 2 +# End of 4.1 tests +# +# MDEV-30333 Wrong result with not_null_range_scan and LEFT JOIN with empty table +# +set @save_optimizer_switch=@@optimizer_switch; +CREATE TABLE t1 (a INT, b INT) ENGINE=MyISAM; +INSERT INTO t1 (b) VALUES (1),(2); +CREATE TABLE t2 (c INT) ENGINE=MyISAM; +SET optimizer_switch= 'not_null_range_scan=off'; +explain extended SELECT b FROM t1 LEFT JOIN t2 ON t2.c = a WHERE a IS NULL ORDER BY b; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 system NULL NULL NULL NULL 0 0.00 Const row not found +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using filesort +Warnings: +Note 1003 select `test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` is null order by `test`.`t1`.`b` +SELECT b FROM t1 LEFT JOIN t2 ON t2.c = a WHERE a IS NULL ORDER BY b; +b +1 +2 +SET optimizer_switch = 'not_null_range_scan=on'; +explain extended SELECT b FROM t1 LEFT JOIN t2 ON t2.c = a WHERE a IS NULL ORDER BY b; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 system NULL NULL NULL NULL 0 0.00 Const row not found +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using filesort +Warnings: +Note 1003 select `test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` is null order by `test`.`t1`.`b` +SELECT b FROM t1 LEFT JOIN t2 ON t2.c = a WHERE a IS NULL ORDER BY b; +b +1 +2 +flush tables; +SELECT b FROM t1 LEFT JOIN t2 ON t2.c = a WHERE a IS NULL ORDER BY b; +b +1 +2 +drop table t1,t2; +# Second test in MDEV-30333 +CREATE TABLE t1 (a int, b varchar(10)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (69,'foo'),(71,'bar'); +CREATE TABLE t2 (c int) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1),(2); +CREATE TABLE t3 (d int, e int, KEY(e)) ENGINE=MyISAM; +SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON t3.e = t3.d ON 1; +a b c d e +69 foo 1 NULL NULL +71 bar 1 NULL NULL +69 foo 2 NULL NULL +71 bar 2 NULL NULL +SET optimizer_switch = 'not_null_range_scan=on'; +SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON t3.e = t3.d ON 1; +a b c d e +69 foo 1 NULL NULL +71 bar 1 NULL NULL +69 foo 2 NULL NULL +71 bar 2 NULL NULL +DROP TABLE t1, t2, t3; +set @@optimizer_switch=@save_optimizer_switch; +End of 10.5 tests diff --git a/mysql-test/main/empty_table.test b/mysql-test/main/empty_table.test index 754671868ba..a17b0c897d5 100644 --- a/mysql-test/main/empty_table.test +++ b/mysql-test/main/empty_table.test @@ -21,4 +21,38 @@ drop table t1; select * from t2; show status like "Empty_queries"; -# End of 4.1 tests +--echo # End of 4.1 tests + +--echo # +--echo # MDEV-30333 Wrong result with not_null_range_scan and LEFT JOIN with empty table +--echo # + +set @save_optimizer_switch=@@optimizer_switch; +CREATE TABLE t1 (a INT, b INT) ENGINE=MyISAM; +INSERT INTO t1 (b) VALUES (1),(2); +CREATE TABLE t2 (c INT) ENGINE=MyISAM; +SET optimizer_switch= 'not_null_range_scan=off'; # Default +explain extended SELECT b FROM t1 LEFT JOIN t2 ON t2.c = a WHERE a IS NULL ORDER BY b; +SELECT b FROM t1 LEFT JOIN t2 ON t2.c = a WHERE a IS NULL ORDER BY b; +SET optimizer_switch = 'not_null_range_scan=on'; +explain extended SELECT b FROM t1 LEFT JOIN t2 ON t2.c = a WHERE a IS NULL ORDER BY b; +SELECT b FROM t1 LEFT JOIN t2 ON t2.c = a WHERE a IS NULL ORDER BY b; +flush tables; +SELECT b FROM t1 LEFT JOIN t2 ON t2.c = a WHERE a IS NULL ORDER BY b; +drop table t1,t2; + +--echo # Second test in MDEV-30333 + +CREATE TABLE t1 (a int, b varchar(10)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (69,'foo'),(71,'bar'); +CREATE TABLE t2 (c int) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1),(2); +CREATE TABLE t3 (d int, e int, KEY(e)) ENGINE=MyISAM; +SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON t3.e = t3.d ON 1; +SET optimizer_switch = 'not_null_range_scan=on'; +SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON t3.e = t3.d ON 1; +DROP TABLE t1, t2, t3; +set @@optimizer_switch=@save_optimizer_switch; + +--echo End of 10.5 tests + diff --git a/mysql-test/main/fetch_first.result b/mysql-test/main/fetch_first.result index dde04bfb05b..1be71f24582 100644 --- a/mysql-test/main/fetch_first.result +++ b/mysql-test/main/fetch_first.result @@ -1432,3 +1432,31 @@ a bar foo DROP TABLE t; +# +# MDEV-30324: Wrong result upon SELECT DISTINCT .. WITH TIES using index +# +CREATE TABLE t1 (a int, b char(3), KEY (a)); +INSERT INTO t1 VALUES (2,'foo'),(3,'bar'),(3,'bar'),(3,'zzz'); +EXPLAIN SELECT DISTINCT a, b FROM t1 ORDER BY a FETCH FIRST 1 ROWS WITH TIES; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL a 5 NULL 1 Using temporary +SELECT DISTINCT a, b FROM t1 ORDER BY a FETCH FIRST 1 ROWS WITH TIES; +a b +2 foo +EXPLAIN SELECT DISTINCT a, b FROM t1 ORDER BY a FETCH FIRST 2 ROWS WITH TIES; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL a 5 NULL 2 Using temporary +SELECT DISTINCT a, b FROM t1 ORDER BY a FETCH FIRST 2 ROWS WITH TIES; +a b +2 foo +3 bar +3 zzz +EXPLAIN SELECT DISTINCT a, b FROM t1 ORDER BY a FETCH FIRST 3 ROWS WITH TIES; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL a 5 NULL 3 Using temporary +SELECT DISTINCT a, b FROM t1 ORDER BY a FETCH FIRST 3 ROWS WITH TIES; +a b +2 foo +3 bar +3 zzz +DROP TABLE t1; diff --git a/mysql-test/main/fetch_first.test b/mysql-test/main/fetch_first.test index 3cec50257fe..1f4b46011b5 100644 --- a/mysql-test/main/fetch_first.test +++ b/mysql-test/main/fetch_first.test @@ -1072,3 +1072,22 @@ SELECT a FROM t ORDER BY a FETCH FIRST 2 ROWS WITH TIES; # Cleanup DROP TABLE t; + +--echo # +--echo # MDEV-30324: Wrong result upon SELECT DISTINCT .. WITH TIES using index +--echo # +CREATE TABLE t1 (a int, b char(3), KEY (a)); +INSERT INTO t1 VALUES (2,'foo'),(3,'bar'),(3,'bar'),(3,'zzz'); + +EXPLAIN SELECT DISTINCT a, b FROM t1 ORDER BY a FETCH FIRST 1 ROWS WITH TIES; +--sorted_result +SELECT DISTINCT a, b FROM t1 ORDER BY a FETCH FIRST 1 ROWS WITH TIES; +EXPLAIN SELECT DISTINCT a, b FROM t1 ORDER BY a FETCH FIRST 2 ROWS WITH TIES; +--sorted_result +SELECT DISTINCT a, b FROM t1 ORDER BY a FETCH FIRST 2 ROWS WITH TIES; +EXPLAIN SELECT DISTINCT a, b FROM t1 ORDER BY a FETCH FIRST 3 ROWS WITH TIES; +--sorted_result +SELECT DISTINCT a, b FROM t1 ORDER BY a FETCH FIRST 3 ROWS WITH TIES; + +# Cleanup +DROP TABLE t1; diff --git a/mysql-test/main/func_json.result b/mysql-test/main/func_json.result index 8456b71c4dc..213cc798a6d 100644 --- a/mysql-test/main/func_json.result +++ b/mysql-test/main/func_json.result @@ -1623,6 +1623,21 @@ id doc {"$oid":"611c0a463b150154132f6636"} { "_id" : { "$oid" : "611c0a463b150154132f6636" }, "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : [ { "a" : 1.0 } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } DROP TABLE arrNestTest; # +# MDEV-30412 JSON_OBJECTAGG doesn't escape double quote in key +# +SELECT JSON_OBJECTAGG('"', 1); +JSON_OBJECTAGG('"', 1) +{"\"":1} +SELECT JSON_OBJECTAGG('\"', 1); +JSON_OBJECTAGG('\"', 1) +{"\"":1} +SELECT JSON_OBJECTAGG('\\', 1); +JSON_OBJECTAGG('\\', 1) +{"\\":1} +# +# End of 10.5 tests +# +# # MDEV-26054 Server crashes in Item_func_json_arrayagg::get_str_from_field # CREATE TABLE t (a VARCHAR(8)); @@ -1649,7 +1664,7 @@ JSON_INSERT(JSON_OBJECT(l1, l2, l3, l4), '$.k3', 'v3') JSON_SET(JSON_OBJECT(l1, {"k1": "v1", "k2": "v2", "k3": "v3"} {"k1": "v1", "k2": "new v2"} {"k1": "v1", "k2": "new v2"} DROP TABLE t; # -# End of 10.5 tests +# End of 10.6 tests # # # Beginning of 10.9 tests @@ -2536,9 +2551,6 @@ JSON_EXTRACT(j, '$[*]') [{"key1": 3, "key2": 3}, {"key3": 3, "key4": 3}, {"key5": 3}] DROP TABLE t1; # -# End of 10.9 Test -# -# # MDEV-29381: JSON paths containing dashes are reported as syntax errors in procedures # SELECT JSON_EXTRACT('{ "my-key": 1 }', '$."my-key"'); @@ -2547,3 +2559,6 @@ JSON_EXTRACT('{ "my-key": 1 }', '$."my-key"') SELECT JSON_EXTRACT('{ "my-key": 1 }', '$.my-key'); JSON_EXTRACT('{ "my-key": 1 }', '$.my-key') 1 +# +# End of 10.9 Test +# diff --git a/mysql-test/main/func_json.test b/mysql-test/main/func_json.test index d00ceeb83e2..93d8c2a7d6a 100644 --- a/mysql-test/main/func_json.test +++ b/mysql-test/main/func_json.test @@ -1068,6 +1068,18 @@ SELECT * FROM arrNestTest; DROP TABLE arrNestTest; --echo # +--echo # MDEV-30412 JSON_OBJECTAGG doesn't escape double quote in key +--echo # + +SELECT JSON_OBJECTAGG('"', 1); +SELECT JSON_OBJECTAGG('\"', 1); +SELECT JSON_OBJECTAGG('\\', 1); + +--echo # +--echo # End of 10.5 tests +--echo # + +--echo # --echo # MDEV-26054 Server crashes in Item_func_json_arrayagg::get_str_from_field --echo # @@ -1078,7 +1090,6 @@ SELECT JSON_ARRAYAGG(a) AS f FROM v; DROP VIEW v; DROP TABLE t; - --echo # --echo # MDEV-29264 JSON functions overflow error based ON LONGTEXT field --echo # @@ -1090,8 +1101,9 @@ SELECT JSON_INSERT(JSON_OBJECT(l1, l2, l3, l4), '$.k3', 'v3'),JSON_SET(JSON_OBJE DROP TABLE t; --echo # ---echo # End of 10.5 tests +--echo # End of 10.6 tests --echo # + --echo # --echo # Beginning of 10.9 tests --echo # @@ -1734,12 +1746,12 @@ SELECT JSON_EXTRACT(j, '$[*]') FROM t1 ; DROP TABLE t1; --echo # ---echo # End of 10.9 Test ---echo # - ---echo # --echo # MDEV-29381: JSON paths containing dashes are reported as syntax errors in procedures --echo # SELECT JSON_EXTRACT('{ "my-key": 1 }', '$."my-key"'); SELECT JSON_EXTRACT('{ "my-key": 1 }', '$.my-key'); + +--echo # +--echo # End of 10.9 Test +--echo # diff --git a/mysql-test/main/join_cache.result b/mysql-test/main/join_cache.result index 0965ac8a555..eddae76a724 100644 --- a/mysql-test/main/join_cache.result +++ b/mysql-test/main/join_cache.result @@ -1046,7 +1046,7 @@ Taiwan NULL Tanzania NULL Thailand NULL Czech Republic NULL -Turkey NULL +Türkiye NULL Uganda NULL Ukraine NULL Hungary NULL @@ -1254,7 +1254,7 @@ Taiwan NULL Tanzania NULL Thailand NULL Czech Republic NULL -Turkey NULL +Türkiye NULL Uganda NULL Ukraine NULL Hungary NULL @@ -1517,7 +1517,7 @@ Taiwan NULL Tanzania NULL Thailand NULL Czech Republic NULL -Turkey NULL +Türkiye NULL Uganda NULL Ukraine NULL Hungary NULL @@ -1714,7 +1714,7 @@ Taiwan NULL Tanzania NULL Thailand NULL Czech Republic NULL -Turkey NULL +Türkiye NULL Uganda NULL Ukraine NULL Hungary NULL @@ -1911,7 +1911,7 @@ Taiwan NULL Tanzania NULL Thailand NULL Czech Republic NULL -Turkey NULL +Türkiye NULL Uganda NULL Ukraine NULL Hungary NULL @@ -2108,7 +2108,7 @@ Taiwan NULL Tanzania NULL Thailand NULL Czech Republic NULL -Turkey NULL +Türkiye NULL Uganda NULL Ukraine NULL Hungary NULL @@ -2752,7 +2752,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND City.Population > 3000000; Name Name Alexandria Egypt -Ankara Turkey +Ankara Türkiye Baghdad Iraq Bangkok Thailand Berlin Germany @@ -2766,7 +2766,7 @@ Delhi India Dhaka Bangladesh Harbin China Ho Chi Minh City Vietnam -Istanbul Turkey +Istanbul Türkiye Jakarta Indonesia Jokohama [Yokohama] Japan Kanton [Guangzhou] China @@ -2809,7 +2809,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND City.Population > 3000000; Name Name Alexandria Egypt -Ankara Turkey +Ankara Türkiye Baghdad Iraq Bangkok Thailand Berlin Germany @@ -2823,7 +2823,7 @@ Delhi India Dhaka Bangladesh Harbin China Ho Chi Minh City Vietnam -Istanbul Turkey +Istanbul Türkiye Jakarta Indonesia Jokohama [Yokohama] Japan Kanton [Guangzhou] China diff --git a/mysql-test/main/long_unique_bugs.result b/mysql-test/main/long_unique_bugs.result index 4c2fbcf1dd6..283ea870a5f 100644 --- a/mysql-test/main/long_unique_bugs.result +++ b/mysql-test/main/long_unique_bugs.result @@ -1,3 +1,6 @@ +# +# MDEV-18707 Server crash in my_hash_sort_bin, ASAN heap-use-after-free in Field::is_null, server hang, corrupted double-linked list +# create table t1 (a int, b int, c int, d int, e int); insert into t1 () values (),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(), @@ -11,6 +14,9 @@ load data infile 'load.data' into table tmp; delete from tmp; drop table t1; drop table tmp; +# +# MDEV-18712 InnoDB indexes are inconsistent with what defined in .frm for table after rebuilding table with index on blob +# create table t1 (b blob) engine=innodb; alter table t1 add unique (b); alter table t1 force; @@ -21,12 +27,18 @@ t1 CREATE TABLE `t1` ( UNIQUE KEY `b` (`b`) USING HASH ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci drop table t1; +# +# MDEV-18713 Assertion `strcmp(share->unique_file_name,filename) || share->last_version' failed in test_if_reopen upon REPLACE into table with key on blob +# create table t1 (pk int, b blob, primary key(pk), unique(b)) engine=myisam; insert into t1 values (1,'foo'); replace into t1 (pk) values (1); alter table t1 force; replace into t1 (pk) values (1); drop table t1; +# +# MDEV-18722 Assertion `templ->mysql_null_bit_mask' failed in row_sel_store_mysql_rec upon modifying indexed column into blob +# create table t1 (t time, unique(t)) engine=innodb; insert into t1 values (null),(null); alter ignore table t1 modify t text not null default ''; @@ -34,6 +46,9 @@ Warnings: Warning 1265 Data truncated for column 't' at row 1 Warning 1265 Data truncated for column 't' at row 2 drop table t1; +# +# MDEV-18720 Assertion `inited==NONE' failed in ha_index_init upon update on versioned table with key on blob +# create table t1 ( pk int, f text, primary key (pk), unique(f)) with system versioning; insert into t1 values (1,'foo'); update t1 set f = 'bar'; @@ -50,20 +65,32 @@ pk f row_end > DATE'2030-01-01' 1 foo 0 1 bar 0 drop table t1; +# +# MDEV-18747 InnoDB: Failing assertion: table->get_ref_count() == 0 upon dropping temporary table with unique blob +# create temporary table t1 (f blob, unique(f)) engine=innodb; insert into t1 values (1); replace into t1 values (1); drop table t1; +# +# MDEV-18748 REPLACE doesn't work with unique blobs on MyISAM table +# create table t (b blob, unique(b)) engine=myisam; insert into t values ('foo'); replace into t values ('foo'); drop table t; +# +# MDEV-18790 Server crash in fields_in_hash_keyinfo after unsuccessful attempt to drop BLOB with long index +# CREATE TABLE t1 (f INT, x BLOB, UNIQUE (x)); INSERT INTO t1 VALUES (1,'foo'); ALTER TABLE t1 DROP x, ALGORITHM=INPLACE; ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY UPDATE t1 SET x = 'bar'; DROP TABLE t1; +# +# MDEV-18799 Long unique does not work after failed alter table +# create table t1(a blob unique , b blob); insert into t1 values(1,1),(2,1); alter table t1 add unique(b); @@ -86,16 +113,26 @@ Ignored NO insert into t1 values(1,1); ERROR 23000: Duplicate entry '1' for key 'a' DROP TABLE t1; +# +# MDEV-18792 ASAN unknown-crash in _mi_pack_key upon UPDATE after failed ALTER on a table with long BLOB key +# CREATE TABLE t1 (a TEXT, b INT, UNIQUE(a)) ENGINE=MyISAM; ALTER TABLE t1 DROP x; ERROR 42000: Can't DROP COLUMN `x`; check that it exists UPDATE t1 SET b = 0 WHERE a = 'foo'; DROP TABLE t1; +# +# MDEV-18793 Assertion `0' failed in row_sel_convert_mysql_key_to_innobase, ASAN unknown-crash in +# row_mysql_store_col_in_innobase_format, warning " InnoDB: Using a partial-field key prefix in search" +# CREATE TABLE t1 (a TEXT, b INT, UNIQUE(a)) ENGINE=InnoDB; ALTER TABLE t1 DROP x; ERROR 42000: Can't DROP COLUMN `x`; check that it exists UPDATE t1 SET b = 0 WHERE a = 'foo'; DROP TABLE t1; +# +# MDEV-18795 InnoDB: Failing assertion: field->prefix_len > 0 upon DML on table with BLOB index +# CREATE TEMPORARY TABLE t1 (f BLOB, UNIQUE(f)) ENGINE=InnoDB ROW_FORMAT=COMPACT; ALTER TABLE t1 ADD KEY (f); ERROR HY000: Index column size too large. The maximum column size is 767 bytes @@ -103,17 +140,29 @@ TRUNCATE TABLE t1; SELECT * FROM t1 WHERE f LIKE 'foo'; f DROP TABLE t1; +# +# MDEV-18798 InnoDB: No matching column for `DB_ROW_HASH_1`and server crash in +# ha_innobase::commit_inplace_alter_table upon ALTER on table with UNIQUE key +# CREATE TABLE t1 (a INT, UNIQUE ind USING HASH (a)) ENGINE=InnoDB; ALTER TABLE t1 CHANGE COLUMN IF EXISTS b a INT; Warnings: Note 1054 Unknown column 'b' in 't1' DROP TABLE t1; +# +# MDEV-18801 InnoDB: Failing assertion: field->col->mtype == type or ASAN heap-buffer-overflow +# in row_sel_convert_mysql_key_to_innobase upon SELECT on table with long index +# CREATE TABLE t1 (f VARCHAR(4096), UNIQUE(f)) ENGINE=InnoDB; ALTER TABLE t1 DROP x; ERROR 42000: Can't DROP COLUMN `x`; check that it exists SELECT * FROM t1 WHERE f LIKE 'foo'; f DROP TABLE t1; +# +# MDEV-18800 Server crash in instant_alter_column_possible or +# Assertion `!pk->has_virtual()' failed in instant_alter_column_possible upon adding key +# CREATE TABLE t1 (pk INT, PRIMARY KEY USING HASH (pk)) ENGINE=InnoDB; show keys from t1;; Table t1 @@ -132,6 +181,9 @@ Index_comment Ignored NO ALTER TABLE t1 ADD INDEX (pk); DROP TABLE t1; +# +# MDEV-18922 Alter on long unique varchar column makes result null +# CREATE TABLE t1 (b int, a varchar(4000)); INSERT INTO t1 VALUES (1, 2),(2,3),(3,4); ALTER TABLE t1 ADD UNIQUE INDEX (a); @@ -146,6 +198,10 @@ a 3 4 drop table t1; +# +# MDEV-18809 Server crash in fields_in_hash_keyinfo or Assertion `key_info->key_part->field->flags +# & (1<< 30)' failed in setup_keyinfo_hash +# CREATE TABLE t1 (f VARCHAR(4096), UNIQUE(f)) ENGINE=InnoDB; ALTER TABLE t1 DROP KEY f, ADD INDEX idx1(f), ALGORITHM=INSTANT; ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: ADD INDEX. Try ALGORITHM=NOCOPY @@ -161,6 +217,9 @@ insert into t1 values(1,1); ERROR 23000: Duplicate entry '1-1' for key 'a' alter table t1 add column c int; drop table t1; +# +# MDEV-18889 Long unique on virtual fields crashes server +# create table t1(a blob , b blob as (a) unique); insert into t1 values(1, default); insert into t1 values(1, default); @@ -174,6 +233,9 @@ insert into t1(a,b) values(2,2); insert into t1(a,b) values(2,3); insert into t1(a,b) values(3,2); drop table t1; +# +# MDEV-18888 Server crashes in Item_field::register_field_in_read_map upon MODIFY COLUMN +# CREATE TABLE t1 ( a CHAR(128), b CHAR(128) AS (a), @@ -189,6 +251,9 @@ c varchar(5000), UNIQUE(c,b(64)) ) ENGINE=InnoDB; drop table t1; +# +# MDEV-18967 Load data in system version with long unique does not work +# CREATE TABLE t1 (data VARCHAR(4), unique(data) using hash) with system versioning; INSERT INTO t1 VALUES ('A'); SELECT * INTO OUTFILE 'load.data' from t1; @@ -198,6 +263,9 @@ select * from t1; data A DROP TABLE t1; +# +# MDEV-18901 Wrong results after ADD UNIQUE INDEX(blob_column) +# CREATE TABLE t1 (data VARCHAR(7961)) ENGINE=InnoDB; INSERT INTO t1 VALUES ('f'), ('o'), ('o'); SELECT * INTO OUTFILE 'load.data' from t1; @@ -216,12 +284,16 @@ SELECT * FROM t1; data f o +# This should be equivalent to the REPLACE above LOAD DATA INFILE 'load.data' REPLACE INTO TABLE t1; SELECT * FROM t1; data f o DROP TABLE t1; +# +# MDEV-18953 Hash index on partial char field not working +# create table t1 ( c char(10) character set utf8mb4, unique key a using hash (c(1)) @@ -238,10 +310,16 @@ ERROR 23000: Duplicate entry 'Ð' for key 'a' insert into t1 values ('ббб'); ERROR 23000: Duplicate entry 'Ð' for key 'a' drop table t1; +# +# MDEV-18904 Assertion `m_part_spec.start_part >= m_part_spec.end_part' failed in ha_partition::index_read_idx_map +# CREATE TABLE t1 (a INT, UNIQUE USING HASH (a)) PARTITION BY HASH (a) PARTITIONS 2; INSERT INTO t1 VALUES (2); REPLACE INTO t1 VALUES (2); DROP TABLE t1; +# +# MDEV-18820 Assertion `lock_table_has(trx, index->table, LOCK_IX)' failed in lock_rec_insert_check_and_lock upon INSERT into table with blob key' +# set innodb_lock_wait_timeout= 10; CREATE TABLE t1 ( id int primary key, @@ -268,11 +346,20 @@ ERROR 40001: Deadlock found when trying to get lock; try restarting transaction disconnect con1; connection default; DROP TABLE t1, t2; +# +# MDEV-18791 Wrong error upon creating Aria table with long index on BLOB +# CREATE TABLE t1 (a TEXT, UNIQUE(a)) ENGINE=Aria; ERROR 42000: Specified key was too long; max key length is 2300 bytes +# +# MDEV-20001 Potential dangerous regression: INSERT INTO >=100 rows fail for myisam table with HASH indexes +# create table t1(a int, unique(a) using hash); #BULK insert > 100 rows (MI_MIN_ROWS_TO_DISABLE_INDEXES) drop table t1; +# +# MDEV-21804 Assertion `marked_for_read()' failed upon INSERT into table with long unique blob under binlog_row_image=NOBLOB +# SET binlog_row_image= NOBLOB; CREATE TABLE t1 (pk INT PRIMARY KEY, a text ,UNIQUE(a) using hash); INSERT INTO t1 VALUES (1,'foo'); @@ -280,6 +367,9 @@ create table t2(id int primary key, a blob, b varchar(20) as (LEFT(a,2))); INSERT INTO t2 VALUES (1, 'foo', default); DROP TABLE t1, t2; SET binlog_row_image= FULL; +# +# MDEV-22719 Long unique keys are not created when individual key_part->length < max_key_length but SUM(key_parts->length) > max_key_length +# CREATE TABLE t1 (a int, b VARCHAR(1000), UNIQUE (a,b)) ENGINE=MyISAM; show index from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored @@ -317,6 +407,9 @@ ERROR 23000: Duplicate entry '1' for key 'v2' update t1,t2 set v1 = v2 , v5 = 0; ERROR 23000: Duplicate entry '-128' for key 'v1' drop table t1, t2; +# +# MDEV-23264 Unique blobs allow duplicate values upon UPDATE +# CREATE TABLE t1 (f TEXT UNIQUE); INSERT INTO t1 VALUES (NULL),(NULL); UPDATE t1 SET f = ''; @@ -346,6 +439,18 @@ partition n0 values less than (10), partition n1 values less than (50)); drop table t1; # +# MDEV-29199 Unique hash key is ignored upon INSERT ... SELECT into non-empty MyISAM table +# +create table t1 (a int, b text, unique(b)) engine=MyISAM; +insert into t1 values (0,'aa'); +insert into t1 (a,b) select 1,'xxx' from seq_1_to_5; +ERROR 23000: Duplicate entry 'xxx' for key 'b' +select * from t1; +a b +0 aa +1 xxx +drop table t1; +# # End of 10.4 tests # # diff --git a/mysql-test/main/long_unique_bugs.test b/mysql-test/main/long_unique_bugs.test index 8d1accd6cd3..27bb402bd8b 100644 --- a/mysql-test/main/long_unique_bugs.test +++ b/mysql-test/main/long_unique_bugs.test @@ -1,9 +1,10 @@ --source include/have_innodb.inc --source include/have_partition.inc +--source include/have_sequence.inc -# -# MDEV-18707 Server crash in my_hash_sort_bin, ASAN heap-use-after-free in Field::is_null, server hang, corrupted double-linked list -# +--echo # +--echo # MDEV-18707 Server crash in my_hash_sort_bin, ASAN heap-use-after-free in Field::is_null, server hang, corrupted double-linked list +--echo # create table t1 (a int, b int, c int, d int, e int); insert into t1 () values (),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(), @@ -20,18 +21,18 @@ drop table t1; --remove_file $datadir/test/load.data drop table tmp; -# -# MDEV-18712 InnoDB indexes are inconsistent with what defined in .frm for table after rebuilding table with index on blob -# +--echo # +--echo # MDEV-18712 InnoDB indexes are inconsistent with what defined in .frm for table after rebuilding table with index on blob +--echo # create table t1 (b blob) engine=innodb; alter table t1 add unique (b); alter table t1 force; show create table t1; drop table t1; -# -# MDEV-18713 Assertion `strcmp(share->unique_file_name,filename) || share->last_version' failed in test_if_reopen upon REPLACE into table with key on blob -# +--echo # +--echo # MDEV-18713 Assertion `strcmp(share->unique_file_name,filename) || share->last_version' failed in test_if_reopen upon REPLACE into table with key on blob +--echo # create table t1 (pk int, b blob, primary key(pk), unique(b)) engine=myisam; insert into t1 values (1,'foo'); replace into t1 (pk) values (1); @@ -39,17 +40,17 @@ alter table t1 force; replace into t1 (pk) values (1); drop table t1; -# -# MDEV-18722 Assertion `templ->mysql_null_bit_mask' failed in row_sel_store_mysql_rec upon modifying indexed column into blob -# +--echo # +--echo # MDEV-18722 Assertion `templ->mysql_null_bit_mask' failed in row_sel_store_mysql_rec upon modifying indexed column into blob +--echo # create table t1 (t time, unique(t)) engine=innodb; insert into t1 values (null),(null); alter ignore table t1 modify t text not null default ''; drop table t1; -# -# MDEV-18720 Assertion `inited==NONE' failed in ha_index_init upon update on versioned table with key on blob -# +--echo # +--echo # MDEV-18720 Assertion `inited==NONE' failed in ha_index_init upon update on versioned table with key on blob +--echo # create table t1 ( pk int, f text, primary key (pk), unique(f)) with system versioning; insert into t1 values (1,'foo'); update t1 set f = 'bar'; @@ -59,25 +60,25 @@ select * from t1; select pk, f, row_end > DATE'2030-01-01' from t1 for system_time all; drop table t1; -# -# MDEV-18747 InnoDB: Failing assertion: table->get_ref_count() == 0 upon dropping temporary table with unique blob -# +--echo # +--echo # MDEV-18747 InnoDB: Failing assertion: table->get_ref_count() == 0 upon dropping temporary table with unique blob +--echo # create temporary table t1 (f blob, unique(f)) engine=innodb; insert into t1 values (1); replace into t1 values (1); drop table t1; -# -# MDEV-18748 REPLACE doesn't work with unique blobs on MyISAM table -# +--echo # +--echo # MDEV-18748 REPLACE doesn't work with unique blobs on MyISAM table +--echo # create table t (b blob, unique(b)) engine=myisam; insert into t values ('foo'); replace into t values ('foo'); drop table t; -# -# MDEV-18790 Server crash in fields_in_hash_keyinfo after unsuccessful attempt to drop BLOB with long index -# +--echo # +--echo # MDEV-18790 Server crash in fields_in_hash_keyinfo after unsuccessful attempt to drop BLOB with long index +--echo # CREATE TABLE t1 (f INT, x BLOB, UNIQUE (x)); INSERT INTO t1 VALUES (1,'foo'); --error ER_ALTER_OPERATION_NOT_SUPPORTED @@ -85,9 +86,9 @@ ALTER TABLE t1 DROP x, ALGORITHM=INPLACE; UPDATE t1 SET x = 'bar'; DROP TABLE t1; -# -# MDEV-18799 Long unique does not work after failed alter table -# +--echo # +--echo # MDEV-18799 Long unique does not work after failed alter table +--echo # create table t1(a blob unique , b blob); insert into t1 values(1,1),(2,1); --error ER_DUP_ENTRY @@ -97,28 +98,28 @@ alter table t1 add unique(b); insert into t1 values(1,1); DROP TABLE t1; -# -# MDEV-18792 ASAN unknown-crash in _mi_pack_key upon UPDATE after failed ALTER on a table with long BLOB key -# +--echo # +--echo # MDEV-18792 ASAN unknown-crash in _mi_pack_key upon UPDATE after failed ALTER on a table with long BLOB key +--echo # CREATE TABLE t1 (a TEXT, b INT, UNIQUE(a)) ENGINE=MyISAM; --error ER_CANT_DROP_FIELD_OR_KEY ALTER TABLE t1 DROP x; UPDATE t1 SET b = 0 WHERE a = 'foo'; DROP TABLE t1; -# -# MDEV-18793 Assertion `0' failed in row_sel_convert_mysql_key_to_innobase, ASAN unknown-crash in -# row_mysql_store_col_in_innobase_format, warning " InnoDB: Using a partial-field key prefix in search" -# +--echo # +--echo # MDEV-18793 Assertion `0' failed in row_sel_convert_mysql_key_to_innobase, ASAN unknown-crash in +--echo # row_mysql_store_col_in_innobase_format, warning " InnoDB: Using a partial-field key prefix in search" +--echo # CREATE TABLE t1 (a TEXT, b INT, UNIQUE(a)) ENGINE=InnoDB; --error ER_CANT_DROP_FIELD_OR_KEY ALTER TABLE t1 DROP x; UPDATE t1 SET b = 0 WHERE a = 'foo'; DROP TABLE t1; -# -# MDEV-18795 InnoDB: Failing assertion: field->prefix_len > 0 upon DML on table with BLOB index -# +--echo # +--echo # MDEV-18795 InnoDB: Failing assertion: field->prefix_len > 0 upon DML on table with BLOB index +--echo # CREATE TEMPORARY TABLE t1 (f BLOB, UNIQUE(f)) ENGINE=InnoDB ROW_FORMAT=COMPACT; --error ER_INDEX_COLUMN_TOO_LONG ALTER TABLE t1 ADD KEY (f); @@ -126,36 +127,36 @@ TRUNCATE TABLE t1; SELECT * FROM t1 WHERE f LIKE 'foo'; DROP TABLE t1; -# -# MDEV-18798 InnoDB: No matching column for `DB_ROW_HASH_1`and server crash in -# ha_innobase::commit_inplace_alter_table upon ALTER on table with UNIQUE key -# +--echo # +--echo # MDEV-18798 InnoDB: No matching column for `DB_ROW_HASH_1`and server crash in +--echo # ha_innobase::commit_inplace_alter_table upon ALTER on table with UNIQUE key +--echo # CREATE TABLE t1 (a INT, UNIQUE ind USING HASH (a)) ENGINE=InnoDB; ALTER TABLE t1 CHANGE COLUMN IF EXISTS b a INT; DROP TABLE t1; -# -# MDEV-18801 InnoDB: Failing assertion: field->col->mtype == type or ASAN heap-buffer-overflow -# in row_sel_convert_mysql_key_to_innobase upon SELECT on table with long index -# +--echo # +--echo # MDEV-18801 InnoDB: Failing assertion: field->col->mtype == type or ASAN heap-buffer-overflow +--echo # in row_sel_convert_mysql_key_to_innobase upon SELECT on table with long index +--echo # CREATE TABLE t1 (f VARCHAR(4096), UNIQUE(f)) ENGINE=InnoDB; --error ER_CANT_DROP_FIELD_OR_KEY ALTER TABLE t1 DROP x; SELECT * FROM t1 WHERE f LIKE 'foo'; DROP TABLE t1; -# -# MDEV-18800 Server crash in instant_alter_column_possible or -# Assertion `!pk->has_virtual()' failed in instant_alter_column_possible upon adding key -# +--echo # +--echo # MDEV-18800 Server crash in instant_alter_column_possible or +--echo # Assertion `!pk->has_virtual()' failed in instant_alter_column_possible upon adding key +--echo # CREATE TABLE t1 (pk INT, PRIMARY KEY USING HASH (pk)) ENGINE=InnoDB; --query_vertical show keys from t1; ALTER TABLE t1 ADD INDEX (pk); DROP TABLE t1; -# -# MDEV-18922 Alter on long unique varchar column makes result null -# +--echo # +--echo # MDEV-18922 Alter on long unique varchar column makes result null +--echo # CREATE TABLE t1 (b int, a varchar(4000)); INSERT INTO t1 VALUES (1, 2),(2,3),(3,4); ALTER TABLE t1 ADD UNIQUE INDEX (a); @@ -163,10 +164,10 @@ SELECT * FROM t1; SELECT a FROM t1; drop table t1; -# -# MDEV-18809 Server crash in fields_in_hash_keyinfo or Assertion `key_info->key_part->field->flags -# & (1<< 30)' failed in setup_keyinfo_hash -# +--echo # +--echo # MDEV-18809 Server crash in fields_in_hash_keyinfo or Assertion `key_info->key_part->field->flags +--echo # & (1<< 30)' failed in setup_keyinfo_hash +--echo # CREATE TABLE t1 (f VARCHAR(4096), UNIQUE(f)) ENGINE=InnoDB; --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON ALTER TABLE t1 DROP KEY f, ADD INDEX idx1(f), ALGORITHM=INSTANT; @@ -181,9 +182,9 @@ insert into t1 values(1,1); alter table t1 add column c int; drop table t1; -# -# MDEV-18889 Long unique on virtual fields crashes server -# +--echo # +--echo # MDEV-18889 Long unique on virtual fields crashes server +--echo # create table t1(a blob , b blob as (a) unique); insert into t1 values(1, default); --error ER_DUP_ENTRY @@ -199,9 +200,9 @@ insert into t1(a,b) values(2,3); insert into t1(a,b) values(3,2); drop table t1; -# -# MDEV-18888 Server crashes in Item_field::register_field_in_read_map upon MODIFY COLUMN -# +--echo # +--echo # MDEV-18888 Server crashes in Item_field::register_field_in_read_map upon MODIFY COLUMN +--echo # CREATE TABLE t1 ( a CHAR(128), b CHAR(128) AS (a), @@ -218,9 +219,9 @@ CREATE TABLE t1 ( ) ENGINE=InnoDB; drop table t1; -# -# MDEV-18967 Load data in system version with long unique does not work -# +--echo # +--echo # MDEV-18967 Load data in system version with long unique does not work +--echo # CREATE TABLE t1 (data VARCHAR(4), unique(data) using hash) with system versioning; INSERT INTO t1 VALUES ('A'); SELECT * INTO OUTFILE 'load.data' from t1; @@ -231,9 +232,9 @@ DROP TABLE t1; --let $datadir= `select @@datadir` --remove_file $datadir/test/load.data -# -# MDEV-18901 Wrong results after ADD UNIQUE INDEX(blob_column) -# +--echo # +--echo # MDEV-18901 Wrong results after ADD UNIQUE INDEX(blob_column) +--echo # CREATE TABLE t1 (data VARCHAR(7961)) ENGINE=InnoDB; INSERT INTO t1 VALUES ('f'), ('o'), ('o'); @@ -245,16 +246,16 @@ ALTER TABLE t1 ADD SYSTEM VERSIONING ; SELECT * FROM t1; REPLACE INTO t1 VALUES ('f'), ('o'), ('o'); SELECT * FROM t1; -# This should be equivalent to the REPLACE above +--echo # This should be equivalent to the REPLACE above LOAD DATA INFILE 'load.data' REPLACE INTO TABLE t1; SELECT * FROM t1; DROP TABLE t1; --let $datadir= `select @@datadir` --remove_file $datadir/test/load.data -# -# MDEV-18953 Hash index on partial char field not working -# +--echo # +--echo # MDEV-18953 Hash index on partial char field not working +--echo # create table t1 ( c char(10) character set utf8mb4, unique key a using hash (c(1)) @@ -267,17 +268,17 @@ insert into t1 values ('бб'); insert into t1 values ('ббб'); drop table t1; -# -# MDEV-18904 Assertion `m_part_spec.start_part >= m_part_spec.end_part' failed in ha_partition::index_read_idx_map -# +--echo # +--echo # MDEV-18904 Assertion `m_part_spec.start_part >= m_part_spec.end_part' failed in ha_partition::index_read_idx_map +--echo # CREATE TABLE t1 (a INT, UNIQUE USING HASH (a)) PARTITION BY HASH (a) PARTITIONS 2; INSERT INTO t1 VALUES (2); REPLACE INTO t1 VALUES (2); DROP TABLE t1; -# -# MDEV-18820 Assertion `lock_table_has(trx, index->table, LOCK_IX)' failed in lock_rec_insert_check_and_lock upon INSERT into table with blob key' -# +--echo # +--echo # MDEV-18820 Assertion `lock_table_has(trx, index->table, LOCK_IX)' failed in lock_rec_insert_check_and_lock upon INSERT into table with blob key' +--echo # set innodb_lock_wait_timeout= 10; @@ -317,15 +318,15 @@ INSERT IGNORE INTO t1 VALUES (4, 1)/*4*/; --connection default DROP TABLE t1, t2; -# -# MDEV-18791 Wrong error upon creating Aria table with long index on BLOB -# +--echo # +--echo # MDEV-18791 Wrong error upon creating Aria table with long index on BLOB +--echo # --error ER_TOO_LONG_KEY CREATE TABLE t1 (a TEXT, UNIQUE(a)) ENGINE=Aria; -# -# MDEV-20001 Potential dangerous regression: INSERT INTO >=100 rows fail for myisam table with HASH indexes -# +--echo # +--echo # MDEV-20001 Potential dangerous regression: INSERT INTO >=100 rows fail for myisam table with HASH indexes +--echo # create table t1(a int, unique(a) using hash); --let $count=150 --let insert_stmt= insert into t1 values(200) @@ -340,9 +341,9 @@ while ($count) --enable_query_log drop table t1; -# -# MDEV-21804 Assertion `marked_for_read()' failed upon INSERT into table with long unique blob under binlog_row_image=NOBLOB -# +--echo # +--echo # MDEV-21804 Assertion `marked_for_read()' failed upon INSERT into table with long unique blob under binlog_row_image=NOBLOB +--echo # --source include/have_binlog_format_row.inc SET binlog_row_image= NOBLOB; @@ -352,20 +353,17 @@ INSERT INTO t1 VALUES (1,'foo'); create table t2(id int primary key, a blob, b varchar(20) as (LEFT(a,2))); INSERT INTO t2 VALUES (1, 'foo', default); -# Cleanup DROP TABLE t1, t2; SET binlog_row_image= FULL; -# -# MDEV-22719 Long unique keys are not created when individual key_part->length < max_key_length but SUM(key_parts->length) > max_key_length -# +--echo # +--echo # MDEV-22719 Long unique keys are not created when individual key_part->length < max_key_length but SUM(key_parts->length) > max_key_length +--echo # CREATE TABLE t1 (a int, b VARCHAR(1000), UNIQUE (a,b)) ENGINE=MyISAM; show index from t1; CREATE TABLE t2 (a varchar(900), b VARCHAR(900), UNIQUE (a,b)) ENGINE=MyISAM; show index from t2; - -# Cleanup DROP TABLE t1,t2; --echo # @@ -397,9 +395,9 @@ update t1 set v2 = 1, v3 = -128; update t1,t2 set v1 = v2 , v5 = 0; drop table t1, t2; -# -# MDEV-23264 Unique blobs allow duplicate values upon UPDATE -# +--echo # +--echo # MDEV-23264 Unique blobs allow duplicate values upon UPDATE +--echo # CREATE TABLE t1 (f TEXT UNIQUE); INSERT INTO t1 VALUES (NULL),(NULL); @@ -436,6 +434,16 @@ alter table t1 reorganize partition p1 into ( drop table t1; --echo # +--echo # MDEV-29199 Unique hash key is ignored upon INSERT ... SELECT into non-empty MyISAM table +--echo # +create table t1 (a int, b text, unique(b)) engine=MyISAM; +insert into t1 values (0,'aa'); +--error ER_DUP_ENTRY +insert into t1 (a,b) select 1,'xxx' from seq_1_to_5; +select * from t1; +drop table t1; + +--echo # --echo # End of 10.4 tests --echo # diff --git a/mysql-test/main/multi_update.result b/mysql-test/main/multi_update.result index 674dc79fe12..bf011f66be0 100644 --- a/mysql-test/main/multi_update.result +++ b/mysql-test/main/multi_update.result @@ -1269,3 +1269,120 @@ EXPLAIN } } DROP TABLES t1, t2; +# End of 10.3 tests +# +# MDEV-28538: multi-table UPDATE/DELETE with possible exists-to-in +# +create table t1 (c1 int, c2 int, c3 int, index idx(c2)); +insert into t1 values +(1,1,1),(3,2,2),(1,3,3), +(2,1,4),(2,2,5),(4,3,6), +(2,4,7),(2,5,8); +create table t2 (c1 int, c2 int, c3 int, index idx(c2)); +insert into t2 values +(1,7,1),(1,8,2),(1,3,3), +(2,1,4),(2,2,5),(2,3,6), +(2,4,7),(2,5,8); +create table t3 (c1 int, c2 int, c3 int, index idx(c2)); +insert into t3 values +(1,1,1),(1,2,2),(1,3,3), +(2,1,4),(2,2,5),(2,3,6), +(2,4,7),(2,5,8); +insert into t3 select c1+1, c2+2, c3 from t3; +insert into t3 select c1, c2+2, c3 from t3; +analyze table t1,t2,t3 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status OK +test.t3 analyze status Engine-independent statistics collected +test.t3 analyze status OK +explain select * from t1,t3 +where t1.c2 = t3.c2 and +t1.c1 > 1 and +exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL idx NULL NULL NULL 8 Using where +1 PRIMARY t2 range idx idx 5 NULL 3 Using index condition; Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +1 PRIMARY t3 ref idx idx 5 test.t1.c2 3 +explain delete from t1 using t1,t3 +where t1.c2 = t3.c2 and +t1.c1 > 1 and +exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL idx NULL NULL NULL 8 Using where +1 PRIMARY t2 range idx idx 5 NULL 3 Using where; FirstMatch(t1) +1 PRIMARY t3 ref idx idx 5 test.t1.c2 3 Using index +explain update t1,t3 set t1.c1 = t1.c1+10 +where t1.c2 = t3.c2 and +t1.c1 > 1 and +exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL idx NULL NULL NULL 8 Using where +1 PRIMARY t2 range idx idx 5 NULL 3 Using where; FirstMatch(t1) +1 PRIMARY t3 ref idx idx 5 test.t1.c2 3 Using index +create table t as select * from t1; +select * from t1,t3 +where t1.c2 = t3.c2 and +t1.c1 > 1 and +exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4); +c1 c2 c3 c1 c2 c3 +2 1 4 1 1 1 +2 1 4 2 1 4 +2 2 5 1 2 2 +2 2 5 2 2 5 +2 4 7 2 4 7 +2 4 7 2 4 2 +2 4 7 3 4 5 +2 4 7 1 4 2 +2 4 7 2 4 5 +2 5 8 2 5 8 +2 5 8 2 5 3 +2 5 8 3 5 6 +2 5 8 1 5 3 +2 5 8 2 5 6 +2 5 8 2 5 1 +2 5 8 3 5 4 +select * from t1; +c1 c2 c3 +1 1 1 +3 2 2 +1 3 3 +2 1 4 +2 2 5 +4 3 6 +2 4 7 +2 5 8 +delete from t1 using t1,t3 +where t1.c2 = t3.c2 and +t1.c1 > 1 and +exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4); +select * from t1; +c1 c2 c3 +1 1 1 +3 2 2 +1 3 3 +4 3 6 +truncate table t1; +insert into t1 select * from t; +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status Table is already up to date +update t1,t3 set t1.c1 = t1.c1+10 +where t1.c2 = t3.c2 and +t1.c1 > 1 and +exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4); +select * from t1; +c1 c2 c3 +1 1 1 +3 2 2 +1 3 3 +12 1 4 +12 2 5 +4 3 6 +12 4 7 +12 5 8 +drop table t1,t2,t3,t; +# End of 10.4 tests diff --git a/mysql-test/main/multi_update.test b/mysql-test/main/multi_update.test index 6ec3de6a78b..b9ceb458db6 100644 --- a/mysql-test/main/multi_update.test +++ b/mysql-test/main/multi_update.test @@ -1133,3 +1133,73 @@ EXPLAIN FORMAT=JSON UPDATE t2 JOIN t1 USING(a) SET t2.part=2 WHERE t2.part=1 AND EXPLAIN FORMAT=JSON UPDATE t2 JOIN t1 USING(a) SET t2.part=3 WHERE t2.part=2 AND t1.part=2; DROP TABLES t1, t2; + +--echo # End of 10.3 tests + +--echo # +--echo # MDEV-28538: multi-table UPDATE/DELETE with possible exists-to-in +--echo # + +create table t1 (c1 int, c2 int, c3 int, index idx(c2)); +insert into t1 values +(1,1,1),(3,2,2),(1,3,3), +(2,1,4),(2,2,5),(4,3,6), +(2,4,7),(2,5,8); + +create table t2 (c1 int, c2 int, c3 int, index idx(c2)); +insert into t2 values +(1,7,1),(1,8,2),(1,3,3), +(2,1,4),(2,2,5),(2,3,6), +(2,4,7),(2,5,8); + +create table t3 (c1 int, c2 int, c3 int, index idx(c2)); +insert into t3 values +(1,1,1),(1,2,2),(1,3,3), +(2,1,4),(2,2,5),(2,3,6), +(2,4,7),(2,5,8); +insert into t3 select c1+1, c2+2, c3 from t3; +insert into t3 select c1, c2+2, c3 from t3; + +analyze table t1,t2,t3 persistent for all; + +let $c= + t1.c2 = t3.c2 and + t1.c1 > 1 and + exists (select 'X' from t2 where t2.c1 = t1.c1 and t2.c2 > 4); + +let $q1= +select * from t1,t3 +where $c; + +eval explain $q1; + +let $q2= +delete from t1 using t1,t3 +where $c; + +eval explain $q2; + +let $q3= +update t1,t3 set t1.c1 = t1.c1+10 +where $c; + +eval explain $q3; + +create table t as select * from t1; + +eval $q1; +select * from t1; + +eval $q2; +select * from t1; + +truncate table t1; +insert into t1 select * from t; +analyze table t1 persistent for all; + +eval $q3; +select * from t1; + +drop table t1,t2,t3,t; + +--echo # End of 10.4 tests diff --git a/mysql-test/main/select,ps.rdiff b/mysql-test/main/select,ps.rdiff new file mode 100644 index 00000000000..7cbcd2be98e --- /dev/null +++ b/mysql-test/main/select,ps.rdiff @@ -0,0 +1,12 @@ +--- mysql-test/main/select.result 2023-01-31 09:30:58.151377805 +0100 ++++ mysql-test/main/select.reject 2023-02-01 13:44:11.026958614 +0100 +@@ -5661,6 +5661,8 @@ + Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1 + Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1 + Note 1249 Select 2 was reduced during optimization ++Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1 ++Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1 + Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1 and (1 or <expr_cache><`test`.`t1`.`a`>((/* select#3 */ select 3 from DUAL where `test`.`t1`.`a` = `test`.`t1`.`a`)) = 3) + PREPARE stmt FROM 'SELECT * FROM t1 WHERE a = 1 AND + (3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3))'; + diff --git a/mysql-test/main/select.result b/mysql-test/main/select.result index bf19d5e9b4e..bdeeb494a14 100644 --- a/mysql-test/main/select.result +++ b/mysql-test/main/select.result @@ -5689,4 +5689,60 @@ EXECUTE stmt; COUNT(DISTINCT a) 3 DROP TABLE t1; +# +# MDEV-29294: Assertion `functype() == ((Item_cond *) new_item)->functype()' +# failed in Item_cond::remove_eq_conds on SELECT +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2),(3); +# Test for nested OR conditions: +SELECT * FROM t1 WHERE a = 1 AND +(3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3)); +a +1 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE a = 1 AND +(3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using temporary +3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 +Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1 +Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1 +Note 1249 Select 2 was reduced during optimization +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1 and (1 or <expr_cache><`test`.`t1`.`a`>((/* select#3 */ select 3 from DUAL where `test`.`t1`.`a` = `test`.`t1`.`a`)) = 3) +PREPARE stmt FROM 'SELECT * FROM t1 WHERE a = 1 AND + (3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3))'; +EXECUTE stmt; +a +1 +EXECUTE stmt; +a +1 +CREATE VIEW v1 AS SELECT * FROM t1 WHERE a = 1 AND +(3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3)); +SELECT * FROM v1; +a +1 +# Test for nested AND conditions: +SELECT * FROM t1 WHERE a = 1 OR +(3 = 3 AND (SELECT a = 1 AND (SELECT 3 WHERE a = a) = 3)); +a +1 +PREPARE stmt FROM 'SELECT * FROM t1 WHERE a = 1 OR + (3 = 3 AND (SELECT a = 1 AND (SELECT 3 WHERE a = a) = 3))'; +EXECUTE stmt; +a +1 +EXECUTE stmt; +a +1 +CREATE VIEW v2 AS SELECT * FROM t1 WHERE a = 1 OR +(3 = 3 AND (SELECT a = 1 AND (SELECT 3 WHERE a = a) = 3)); +SELECT * FROM v2; +a +1 +DROP TABLE t1; +DROP VIEW v1, v2; End of 10.0 tests diff --git a/mysql-test/main/select.test b/mysql-test/main/select.test index 95b66d69255..bc06431a05a 100644 --- a/mysql-test/main/select.test +++ b/mysql-test/main/select.test @@ -3,6 +3,7 @@ # --source include/no_valgrind_without_big.inc +--source include/protocol.inc # # Simple select test @@ -4765,4 +4766,46 @@ EXECUTE stmt; --enable_warnings DROP TABLE t1; +--echo # +--echo # MDEV-29294: Assertion `functype() == ((Item_cond *) new_item)->functype()' +--echo # failed in Item_cond::remove_eq_conds on SELECT +--echo # +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2),(3); + +--echo # Test for nested OR conditions: +SELECT * FROM t1 WHERE a = 1 AND + (3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3)); + +--enable_prepare_warnings +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE a = 1 AND + (3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3)); +--disable_prepare_warnings + +PREPARE stmt FROM 'SELECT * FROM t1 WHERE a = 1 AND + (3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3))'; +EXECUTE stmt; +EXECUTE stmt; + +CREATE VIEW v1 AS SELECT * FROM t1 WHERE a = 1 AND + (3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3)); +SELECT * FROM v1; + +--echo # Test for nested AND conditions: +SELECT * FROM t1 WHERE a = 1 OR + (3 = 3 AND (SELECT a = 1 AND (SELECT 3 WHERE a = a) = 3)); + +PREPARE stmt FROM 'SELECT * FROM t1 WHERE a = 1 OR + (3 = 3 AND (SELECT a = 1 AND (SELECT 3 WHERE a = a) = 3))'; +EXECUTE stmt; +EXECUTE stmt; + +CREATE VIEW v2 AS SELECT * FROM t1 WHERE a = 1 OR + (3 = 3 AND (SELECT a = 1 AND (SELECT 3 WHERE a = a) = 3)); +SELECT * FROM v2; + +DROP TABLE t1; +DROP VIEW v1, v2; + --echo End of 10.0 tests diff --git a/mysql-test/main/select_jcl6,ps.rdiff b/mysql-test/main/select_jcl6,ps.rdiff new file mode 100644 index 00000000000..18652a0683b --- /dev/null +++ b/mysql-test/main/select_jcl6,ps.rdiff @@ -0,0 +1,12 @@ +--- mysql-test/main/select_jcl6.result 2023-01-31 09:30:58.151377805 +0100 ++++ mysql-test/main/select_jcl6.reject 2023-02-01 13:44:10.722958771 +0100 +@@ -5672,6 +5672,8 @@ + Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1 + Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1 + Note 1249 Select 2 was reduced during optimization ++Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1 ++Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1 + Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1 and (1 or <expr_cache><`test`.`t1`.`a`>((/* select#3 */ select 3 from DUAL where `test`.`t1`.`a` = `test`.`t1`.`a`)) = 3) + PREPARE stmt FROM 'SELECT * FROM t1 WHERE a = 1 AND + (3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3))'; + diff --git a/mysql-test/main/select_jcl6.result b/mysql-test/main/select_jcl6.result index 4001a6db81a..791e7983508 100644 --- a/mysql-test/main/select_jcl6.result +++ b/mysql-test/main/select_jcl6.result @@ -5700,6 +5700,62 @@ EXECUTE stmt; COUNT(DISTINCT a) 3 DROP TABLE t1; +# +# MDEV-29294: Assertion `functype() == ((Item_cond *) new_item)->functype()' +# failed in Item_cond::remove_eq_conds on SELECT +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2),(3); +# Test for nested OR conditions: +SELECT * FROM t1 WHERE a = 1 AND +(3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3)); +a +1 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE a = 1 AND +(3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using temporary +3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 +Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1 +Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1 +Note 1249 Select 2 was reduced during optimization +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1 and (1 or <expr_cache><`test`.`t1`.`a`>((/* select#3 */ select 3 from DUAL where `test`.`t1`.`a` = `test`.`t1`.`a`)) = 3) +PREPARE stmt FROM 'SELECT * FROM t1 WHERE a = 1 AND + (3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3))'; +EXECUTE stmt; +a +1 +EXECUTE stmt; +a +1 +CREATE VIEW v1 AS SELECT * FROM t1 WHERE a = 1 AND +(3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3)); +SELECT * FROM v1; +a +1 +# Test for nested AND conditions: +SELECT * FROM t1 WHERE a = 1 OR +(3 = 3 AND (SELECT a = 1 AND (SELECT 3 WHERE a = a) = 3)); +a +1 +PREPARE stmt FROM 'SELECT * FROM t1 WHERE a = 1 OR + (3 = 3 AND (SELECT a = 1 AND (SELECT 3 WHERE a = a) = 3))'; +EXECUTE stmt; +a +1 +EXECUTE stmt; +a +1 +CREATE VIEW v2 AS SELECT * FROM t1 WHERE a = 1 OR +(3 = 3 AND (SELECT a = 1 AND (SELECT 3 WHERE a = a) = 3)); +SELECT * FROM v2; +a +1 +DROP TABLE t1; +DROP VIEW v1, v2; End of 10.0 tests set join_cache_level=default; set @@optimizer_switch=@save_optimizer_switch_jcl6; diff --git a/mysql-test/main/select_pkeycache,ps.rdiff b/mysql-test/main/select_pkeycache,ps.rdiff new file mode 100644 index 00000000000..320a402fefc --- /dev/null +++ b/mysql-test/main/select_pkeycache,ps.rdiff @@ -0,0 +1,12 @@ +--- mysql-test/main/select_pkeycache.result 2023-01-31 09:30:58.151377805 +0100 ++++ mysql-test/main/select_pkeycache.reject 2023-02-01 13:43:21.742985365 +0100 +@@ -5661,6 +5661,8 @@ + Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1 + Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1 + Note 1249 Select 2 was reduced during optimization ++Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1 ++Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1 + Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1 and (1 or <expr_cache><`test`.`t1`.`a`>((/* select#3 */ select 3 from DUAL where `test`.`t1`.`a` = `test`.`t1`.`a`)) = 3) + PREPARE stmt FROM 'SELECT * FROM t1 WHERE a = 1 AND + (3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3))'; + diff --git a/mysql-test/main/select_pkeycache.result b/mysql-test/main/select_pkeycache.result index bf19d5e9b4e..bdeeb494a14 100644 --- a/mysql-test/main/select_pkeycache.result +++ b/mysql-test/main/select_pkeycache.result @@ -5689,4 +5689,60 @@ EXECUTE stmt; COUNT(DISTINCT a) 3 DROP TABLE t1; +# +# MDEV-29294: Assertion `functype() == ((Item_cond *) new_item)->functype()' +# failed in Item_cond::remove_eq_conds on SELECT +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2),(3); +# Test for nested OR conditions: +SELECT * FROM t1 WHERE a = 1 AND +(3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3)); +a +1 +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE a = 1 AND +(3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using temporary +3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 +Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1 +Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1 +Note 1249 Select 2 was reduced during optimization +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1 and (1 or <expr_cache><`test`.`t1`.`a`>((/* select#3 */ select 3 from DUAL where `test`.`t1`.`a` = `test`.`t1`.`a`)) = 3) +PREPARE stmt FROM 'SELECT * FROM t1 WHERE a = 1 AND + (3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3))'; +EXECUTE stmt; +a +1 +EXECUTE stmt; +a +1 +CREATE VIEW v1 AS SELECT * FROM t1 WHERE a = 1 AND +(3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3)); +SELECT * FROM v1; +a +1 +# Test for nested AND conditions: +SELECT * FROM t1 WHERE a = 1 OR +(3 = 3 AND (SELECT a = 1 AND (SELECT 3 WHERE a = a) = 3)); +a +1 +PREPARE stmt FROM 'SELECT * FROM t1 WHERE a = 1 OR + (3 = 3 AND (SELECT a = 1 AND (SELECT 3 WHERE a = a) = 3))'; +EXECUTE stmt; +a +1 +EXECUTE stmt; +a +1 +CREATE VIEW v2 AS SELECT * FROM t1 WHERE a = 1 OR +(3 = 3 AND (SELECT a = 1 AND (SELECT 3 WHERE a = a) = 3)); +SELECT * FROM v2; +a +1 +DROP TABLE t1; +DROP VIEW v1, v2; End of 10.0 tests diff --git a/mysql-test/main/statistics.result b/mysql-test/main/statistics.result index 5ecb439bacc..3aa9fd1d306 100644 --- a/mysql-test/main/statistics.result +++ b/mysql-test/main/statistics.result @@ -1368,7 +1368,7 @@ WORLD CITY Name A Coruña (La Coruña) Ãœrgenc 0.0000 8.6416 1.0195 WORLD CITY Population 42 10500000 0.0000 4.0000 1.0467 WORLD COUNTRY Capital 1 4074 0.0293 4.0000 1.0000 WORLD COUNTRY Code ABW ZWE 0.0000 3.0000 1.0000 -WORLD COUNTRY Name Afghanistan Zimbabwe 0.0000 10.1088 1.0000 +WORLD COUNTRY Name Afghanistan Zimbabwe 0.0000 10.1172 1.0000 WORLD COUNTRY Population 0 1277558000 0.0000 4.0000 1.0575 WORLD COUNTRY SurfaceArea 0.40 17075400.00 0.0000 4.0000 1.0042 WORLD COUNTRYLANGUAGE Country ABW ZWE 0.0000 3.0000 4.2232 @@ -1439,7 +1439,7 @@ WORLD CITY Name A Coruña (La Coruña) Ãœrgenc 0.0000 8.6416 1.0195 WORLD CITY Population 42 10500000 0.0000 4.0000 1.0467 WORLD COUNTRY Capital 1 4074 0.0293 4.0000 1.0000 WORLD COUNTRY Code ABW ZWE 0.0000 3.0000 1.0000 -WORLD COUNTRY Name Afghanistan Zimbabwe 0.0000 10.1088 1.0000 +WORLD COUNTRY Name Afghanistan Zimbabwe 0.0000 10.1172 1.0000 WORLD COUNTRY Population 0 1277558000 0.0000 4.0000 1.0575 WORLD COUNTRY SurfaceArea 0.40 17075400.00 0.0000 4.0000 1.0042 WORLD COUNTRYLANGUAGE Country ABW ZWE 0.0000 3.0000 4.2232 @@ -1451,7 +1451,7 @@ WORLD_INNODB CITY Name A Coruña (La Coruña) Ãœrgenc 0.0000 8.6416 1.0195 WORLD_INNODB CITY Population 42 10500000 0.0000 4.0000 1.0467 WORLD_INNODB COUNTRY Capital 1 4074 0.0293 4.0000 1.0000 WORLD_INNODB COUNTRY Code ABW ZWE 0.0000 3.0000 1.0000 -WORLD_INNODB COUNTRY Name Afghanistan Zimbabwe 0.0000 10.1088 1.0000 +WORLD_INNODB COUNTRY Name Afghanistan Zimbabwe 0.0000 10.1172 1.0000 WORLD_INNODB COUNTRY Population 0 1277558000 0.0000 4.0000 1.0575 WORLD_INNODB COUNTRY SurfaceArea 0.40 17075400.00 0.0000 4.0000 1.0042 WORLD_INNODB COUNTRYLANGUAGE Country ABW ZWE 0.0000 3.0000 4.2232 @@ -1530,7 +1530,7 @@ WORLD_INNODB CITY Name A Coruña (La Coruña) Ãœrgenc 0.0000 8.6416 1.0195 WORLD_INNODB CITY Population 42 10500000 0.0000 4.0000 1.0467 WORLD_INNODB COUNTRY Capital 1 4074 0.0293 4.0000 1.0000 WORLD_INNODB COUNTRY Code ABW ZWE 0.0000 3.0000 1.0000 -WORLD_INNODB COUNTRY Name Afghanistan Zimbabwe 0.0000 10.1088 1.0000 +WORLD_INNODB COUNTRY Name Afghanistan Zimbabwe 0.0000 10.1172 1.0000 WORLD_INNODB COUNTRY Population 0 1277558000 0.0000 4.0000 1.0575 WORLD_INNODB COUNTRY SurfaceArea 0.40 17075400.00 0.0000 4.0000 1.0042 WORLD_INNODB COUNTRYLANGUAGE Country ABW ZWE 0.0000 3.0000 4.2232 diff --git a/mysql-test/main/statistics_json.result b/mysql-test/main/statistics_json.result index a4cf71f9806..7b7cc86642c 100644 --- a/mysql-test/main/statistics_json.result +++ b/mysql-test/main/statistics_json.result @@ -1728,7 +1728,7 @@ WORLD CITY Name A Coruña (La Coruña) Ãœrgenc 0.0000 8.6416 1.0195 WORLD CITY Population 42 10500000 0.0000 4.0000 1.0467 WORLD COUNTRY Capital 1 4074 0.0293 4.0000 1.0000 WORLD COUNTRY Code ABW ZWE 0.0000 3.0000 1.0000 -WORLD COUNTRY Name Afghanistan Zimbabwe 0.0000 10.1088 1.0000 +WORLD COUNTRY Name Afghanistan Zimbabwe 0.0000 10.1172 1.0000 WORLD COUNTRY Population 0 1277558000 0.0000 4.0000 1.0575 WORLD COUNTRY SurfaceArea 0.40 17075400.00 0.0000 4.0000 1.0042 WORLD COUNTRYLANGUAGE Country ABW ZWE 0.0000 3.0000 4.2232 @@ -1799,7 +1799,7 @@ WORLD CITY Name A Coruña (La Coruña) Ãœrgenc 0.0000 8.6416 1.0195 WORLD CITY Population 42 10500000 0.0000 4.0000 1.0467 WORLD COUNTRY Capital 1 4074 0.0293 4.0000 1.0000 WORLD COUNTRY Code ABW ZWE 0.0000 3.0000 1.0000 -WORLD COUNTRY Name Afghanistan Zimbabwe 0.0000 10.1088 1.0000 +WORLD COUNTRY Name Afghanistan Zimbabwe 0.0000 10.1172 1.0000 WORLD COUNTRY Population 0 1277558000 0.0000 4.0000 1.0575 WORLD COUNTRY SurfaceArea 0.40 17075400.00 0.0000 4.0000 1.0042 WORLD COUNTRYLANGUAGE Country ABW ZWE 0.0000 3.0000 4.2232 @@ -1811,7 +1811,7 @@ WORLD_INNODB CITY Name A Coruña (La Coruña) Ãœrgenc 0.0000 8.6416 1.0195 WORLD_INNODB CITY Population 42 10500000 0.0000 4.0000 1.0467 WORLD_INNODB COUNTRY Capital 1 4074 0.0293 4.0000 1.0000 WORLD_INNODB COUNTRY Code ABW ZWE 0.0000 3.0000 1.0000 -WORLD_INNODB COUNTRY Name Afghanistan Zimbabwe 0.0000 10.1088 1.0000 +WORLD_INNODB COUNTRY Name Afghanistan Zimbabwe 0.0000 10.1172 1.0000 WORLD_INNODB COUNTRY Population 0 1277558000 0.0000 4.0000 1.0575 WORLD_INNODB COUNTRY SurfaceArea 0.40 17075400.00 0.0000 4.0000 1.0042 WORLD_INNODB COUNTRYLANGUAGE Country ABW ZWE 0.0000 3.0000 4.2232 @@ -3529,7 +3529,7 @@ WORLD_INNODB CITY Name A Coruña (La Coruña) Ãœrgenc 0.0000 8.6416 1.0195 WORLD_INNODB CITY Population 42 10500000 0.0000 4.0000 1.0467 WORLD_INNODB COUNTRY Capital 1 4074 0.0293 4.0000 1.0000 WORLD_INNODB COUNTRY Code ABW ZWE 0.0000 3.0000 1.0000 -WORLD_INNODB COUNTRY Name Afghanistan Zimbabwe 0.0000 10.1088 1.0000 +WORLD_INNODB COUNTRY Name Afghanistan Zimbabwe 0.0000 10.1172 1.0000 WORLD_INNODB COUNTRY Population 0 1277558000 0.0000 4.0000 1.0575 WORLD_INNODB COUNTRY SurfaceArea 0.40 17075400.00 0.0000 4.0000 1.0042 WORLD_INNODB COUNTRYLANGUAGE Country ABW ZWE 0.0000 3.0000 4.2232 diff --git a/mysql-test/main/type_timestamp.result b/mysql-test/main/type_timestamp.result index d69ebf736d2..a64d393ee66 100644 --- a/mysql-test/main/type_timestamp.result +++ b/mysql-test/main/type_timestamp.result @@ -1320,6 +1320,28 @@ CASE WHEN a THEN DEFAULT(a) END DROP TABLE t1; SET timestamp=DEFAULT; # +# MDEV-27653 long uniques don't work with unicode collations +# +CREATE TABLE t1 (a timestamp, UNIQUE KEY(a) USING HASH); +SET time_zone='+00:00'; +INSERT INTO t1 VALUES ('2001-01-01 10:20:30'); +SET time_zone='+01:00'; +INSERT INTO t1 SELECT MAX(a) FROM t1; +ERROR 23000: Duplicate entry '2001-01-01 11:20:30' for key 'a' +SELECT * FROM t1; +a +2001-01-01 11:20:30 +DROP TABLE t1; +CREATE TABLE t1 (a timestamp, UNIQUE KEY(a) USING HASH); +SET time_zone='+00:00'; +INSERT INTO t1 VALUES ('2001-01-01 10:20:30'); +SET time_zone='+01:00'; +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +DROP TABLE t1; +SET time_zone=DEFAULT; +# # End of 10.4 tests # # diff --git a/mysql-test/main/type_timestamp.test b/mysql-test/main/type_timestamp.test index b5b1fb02f40..8edd52fec2a 100644 --- a/mysql-test/main/type_timestamp.test +++ b/mysql-test/main/type_timestamp.test @@ -878,6 +878,27 @@ DROP TABLE t1; SET timestamp=DEFAULT; --echo # +--echo # MDEV-27653 long uniques don't work with unicode collations +--echo # + +CREATE TABLE t1 (a timestamp, UNIQUE KEY(a) USING HASH); +SET time_zone='+00:00'; +INSERT INTO t1 VALUES ('2001-01-01 10:20:30'); +SET time_zone='+01:00'; +--error ER_DUP_ENTRY +INSERT INTO t1 SELECT MAX(a) FROM t1; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a timestamp, UNIQUE KEY(a) USING HASH); +SET time_zone='+00:00'; +INSERT INTO t1 VALUES ('2001-01-01 10:20:30'); +SET time_zone='+01:00'; +CHECK TABLE t1; +DROP TABLE t1; +SET time_zone=DEFAULT; + +--echo # --echo # End of 10.4 tests --echo # diff --git a/mysql-test/main/update_use_source.result b/mysql-test/main/update_use_source.result index 91b2f7c8890..d61bff04e0d 100644 --- a/mysql-test/main/update_use_source.result +++ b/mysql-test/main/update_use_source.result @@ -76,7 +76,7 @@ rollback; explain update t1 set c1=0 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 > 3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 8 Using where -2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 8 Using where +1 PRIMARY a ALL NULL NULL NULL NULL 8 Using where; FirstMatch(t1) start transaction; update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3; affected rows: 4 @@ -317,7 +317,7 @@ rollback; explain update t1 set c1=0 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 > 3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 range t1_c2 t1_c2 5 NULL 2 Using where -2 DEPENDENT SUBQUERY a ref t1_c2 t1_c2 5 test.t1.c2 8 Using index +1 PRIMARY a ref t1_c2 t1_c2 5 test.t1.c2 8 Using index; FirstMatch(t1) start transaction; update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3; affected rows: 4 @@ -557,8 +557,8 @@ rollback; # explain update t1 set c1=0 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 > 3; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range t1_c2 t1_c2 5 NULL 2 Using where -2 DEPENDENT SUBQUERY a ref t1_c2 t1_c2 5 test.t1.c2 1 Using index +1 PRIMARY a index t1_c2 t1_c2 10 NULL 8 Using where; Using index; LooseScan +1 PRIMARY t1 ref t1_c2 t1_c2 5 test.a.c2 1 start transaction; update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3; affected rows: 4 @@ -799,8 +799,8 @@ rollback; # explain update t1 set c1=0 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 > 3; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range t1_c2 t1_c2 5 NULL 2 Using where -2 DEPENDENT SUBQUERY a ref t1_c2 t1_c2 5 test.t1.c2 1 Using index +1 PRIMARY a index t1_c2 t1_c2 10 NULL 8 Using where; Using index; LooseScan +1 PRIMARY t1 ref t1_c2 t1_c2 5 test.a.c2 1 start transaction; update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3; affected rows: 4 diff --git a/mysql-test/main/win.result b/mysql-test/main/win.result index 04fa4c0bf16..e8d24994140 100644 --- a/mysql-test/main/win.result +++ b/mysql-test/main/win.result @@ -4459,6 +4459,46 @@ pk a bit_or DROP TABLE t2; DROP TABLE t1; # +# MDEV-15178: Filesort::make_sortorder: Assertion `pos->field != __null | +# +CREATE TABLE t1 (i1 int, a int); +INSERT INTO t1 VALUES (1, 1), (2, 2),(3, 3); +CREATE TABLE t2 (i2 int); +INSERT INTO t2 VALUES (1),(2),(5),(1),(7),(4),(3); +SELECT +a, +RANK() OVER (ORDER BY SUM(DISTINCT i1)) +FROM +t1, t2 WHERE t2.i2 = t1.i1 +GROUP BY +a; +a RANK() OVER (ORDER BY SUM(DISTINCT i1)) +1 1 +2 2 +3 3 +DROP TABLE t1, t2; +# +# MDEV-17014: Crash server using ROW_NUMBER() OVER (PARTITION ..) +# +CREATE TABLE t1 (UID BIGINT); +CREATE TABLE t2 (UID BIGINT); +CREATE TABLE t3 (UID BIGINT); +insert into t1 VALUES (1),(2); +insert into t2 VALUES (1),(2); +insert into t3 VALUES (1),(2); +SELECT +ROW_NUMBER() OVER (PARTITION BY GROUP_CONCAT(TT1.UID)) +FROM t1 TT1, +t2 TT2, +t3 TT3 +WHERE TT3.UID = TT1.UID AND TT2.UID = TT3.UID +GROUP BY TT1.UID +; +ROW_NUMBER() OVER (PARTITION BY GROUP_CONCAT(TT1.UID)) +1 +1 +DROP TABLE t1, t2, t3; +# # End of 10.3 tests # # diff --git a/mysql-test/main/win.test b/mysql-test/main/win.test index 56663a0ad88..480eca274cf 100644 --- a/mysql-test/main/win.test +++ b/mysql-test/main/win.test @@ -2828,6 +2828,46 @@ DROP TABLE t2; DROP TABLE t1; +--echo # +--echo # MDEV-15178: Filesort::make_sortorder: Assertion `pos->field != __null | +--echo # + +CREATE TABLE t1 (i1 int, a int); +INSERT INTO t1 VALUES (1, 1), (2, 2),(3, 3); + +CREATE TABLE t2 (i2 int); +INSERT INTO t2 VALUES (1),(2),(5),(1),(7),(4),(3); + +SELECT + a, + RANK() OVER (ORDER BY SUM(DISTINCT i1)) +FROM + t1, t2 WHERE t2.i2 = t1.i1 +GROUP BY + a; + +DROP TABLE t1, t2; + +--echo # +--echo # MDEV-17014: Crash server using ROW_NUMBER() OVER (PARTITION ..) +--echo # +CREATE TABLE t1 (UID BIGINT); +CREATE TABLE t2 (UID BIGINT); +CREATE TABLE t3 (UID BIGINT); + +insert into t1 VALUES (1),(2); +insert into t2 VALUES (1),(2); +insert into t3 VALUES (1),(2); +SELECT +ROW_NUMBER() OVER (PARTITION BY GROUP_CONCAT(TT1.UID)) +FROM t1 TT1, + t2 TT2, + t3 TT3 +WHERE TT3.UID = TT1.UID AND TT2.UID = TT3.UID +GROUP BY TT1.UID +; + +DROP TABLE t1, t2, t3; --echo # --echo # End of 10.3 tests diff --git a/mysql-test/main/win_orderby.result b/mysql-test/main/win_orderby.result index bf4a40a4db3..1a9860c1c76 100644 --- a/mysql-test/main/win_orderby.result +++ b/mysql-test/main/win_orderby.result @@ -24,3 +24,66 @@ pk count(a) over (order by pk rows between 2 preceding and 2 following) 28 5 27 5 drop table t0,t1; +# +# MDEV-30052: Crash with a query containing nested WINDOW clauses +# +CREATE TABLE t1 (c INT); +insert into t1 values (1),(2); +UPDATE t1 SET c=1 +WHERE c=2 +ORDER BY +(1 IN (( +SELECT * +FROM (SELECT * FROM t1) AS v1 +GROUP BY c +WINDOW v2 AS (ORDER BY +(SELECT * +FROM t1 +GROUP BY c +WINDOW v3 AS (PARTITION BY c) +) +) +)) +); +drop table t1; +# +# MDEV-29359: Server crashed with heap-use-after-free in +# Field::is_null(long long) const (Just testcase) +# +CREATE TABLE t1 (id int); +INSERT INTO t1 VALUES (-1),(0),(84); +SELECT +id IN (SELECT id +FROM t1 +WINDOW w AS (ORDER BY (SELECT 1 +FROM t1 +WHERE +EXISTS ( SELECT id +FROM t1 +GROUP BY id +WINDOW w2 AS (ORDER BY id) +) +) +) +) +FROM t1; +id IN (SELECT id +FROM t1 +WINDOW w AS (ORDER BY (SELECT 1 +FROM t1 +WHERE +EXISTS ( SELECT id +FROM t1 +GROUP BY id +WINDOW w2 AS (ORDER BY id) +) +) +) +) +1 +1 +1 +DROP TABLE t1; +# +# End of 10.3 tests +# diff --git a/mysql-test/main/win_orderby.test b/mysql-test/main/win_orderby.test index 7f02a582ea0..65421fc095c 100644 --- a/mysql-test/main/win_orderby.test +++ b/mysql-test/main/win_orderby.test @@ -33,3 +33,58 @@ limit 4; --disable_view_protocol drop table t0,t1; + + +--echo # +--echo # MDEV-30052: Crash with a query containing nested WINDOW clauses +--echo # + +CREATE TABLE t1 (c INT); +insert into t1 values (1),(2); +UPDATE t1 SET c=1 +WHERE c=2 +ORDER BY + (1 IN (( + SELECT * + FROM (SELECT * FROM t1) AS v1 + GROUP BY c + WINDOW v2 AS (ORDER BY + (SELECT * + FROM t1 + GROUP BY c + WINDOW v3 AS (PARTITION BY c) + ) + ) + )) + ); +drop table t1; + +--echo # +--echo # MDEV-29359: Server crashed with heap-use-after-free in +--echo # Field::is_null(long long) const (Just testcase) +--echo # + +CREATE TABLE t1 (id int); +INSERT INTO t1 VALUES (-1),(0),(84); + +SELECT + id IN (SELECT id + FROM t1 + WINDOW w AS (ORDER BY (SELECT 1 + FROM t1 + WHERE + EXISTS ( SELECT id + FROM t1 + GROUP BY id + WINDOW w2 AS (ORDER BY id) + ) + ) + ) + ) +FROM t1; + +DROP TABLE t1; + +--echo # +--echo # End of 10.3 tests +--echo # diff --git a/mysql-test/mariadb-test-run.pl b/mysql-test/mariadb-test-run.pl index 5ab50a4f88b..65c17801b16 100755 --- a/mysql-test/mariadb-test-run.pl +++ b/mysql-test/mariadb-test-run.pl @@ -145,6 +145,7 @@ my $opt_start_exit; my $start_only; my $file_wsrep_provider; my $num_saved_cores= 0; # Number of core files saved in vardir/log/ so far. +my $test_name_for_report; our @global_suppressions; @@ -515,12 +516,9 @@ sub main { } if ( not @$completed ) { - my $test_name= mtr_grab_file($path_testlog); - if (defined($test_name)) + if ($test_name_for_report) { - $test_name =~ s/^CURRENT_TEST:\s//; - chomp($test_name); - my $tinfo = My::Test->new(name => $test_name); + my $tinfo = My::Test->new(name => $test_name_for_report); $tinfo->{result}= 'MTR_RES_FAILED'; $tinfo->{comment}=' '; mtr_report_test($tinfo); @@ -3744,8 +3742,8 @@ sub resfile_report_test ($) { sub run_testcase ($$) { my ($tinfo, $server_socket)= @_; my $print_freq=20; - - mtr_verbose("Running test:", $tinfo->{name}); + $test_name_for_report= $tinfo->{name}; + mtr_verbose("Running test:", $test_name_for_report); $ENV{'MTR_TEST_NAME'} = $tinfo->{name}; resfile_report_test($tinfo) if $opt_resfile; @@ -5134,12 +5132,10 @@ sub mysqld_start ($$) { if (!$rc) { # Report failure about the last test case before exit - my $test_name= mtr_grab_file($path_current_testlog); - $test_name =~ s/^CURRENT_TEST:\s//; - my $tinfo = My::Test->new(name => $test_name); + my $tinfo = My::Test->new(name => $test_name_for_report); $tinfo->{result}= 'MTR_RES_FAILED'; $tinfo->{failures}= 1; - $tinfo->{logfile}=get_log_from_proc($mysqld->{'proc'}, $tinfo->{name}); + $tinfo->{logfile}=get_log_from_proc($mysqld->{'proc'}, $test_name_for_report); report_option('verbose', 1); mtr_report_test($tinfo); } diff --git a/mysql-test/std_data/mysql_upgrade/mdev27653_100422_myisam_text.MYD b/mysql-test/std_data/mysql_upgrade/mdev27653_100422_myisam_text.MYD Binary files differnew file mode 100644 index 00000000000..5f1ad998c00 --- /dev/null +++ b/mysql-test/std_data/mysql_upgrade/mdev27653_100422_myisam_text.MYD diff --git a/mysql-test/std_data/mysql_upgrade/mdev27653_100422_myisam_text.MYI b/mysql-test/std_data/mysql_upgrade/mdev27653_100422_myisam_text.MYI Binary files differnew file mode 100644 index 00000000000..7a748abbae5 --- /dev/null +++ b/mysql-test/std_data/mysql_upgrade/mdev27653_100422_myisam_text.MYI diff --git a/mysql-test/std_data/mysql_upgrade/mdev27653_100422_myisam_text.frm b/mysql-test/std_data/mysql_upgrade/mdev27653_100422_myisam_text.frm Binary files differnew file mode 100644 index 00000000000..5a4cec72324 --- /dev/null +++ b/mysql-test/std_data/mysql_upgrade/mdev27653_100422_myisam_text.frm diff --git a/mysql-test/suite/binlog/r/innodb_rc_insert_before_delete.result b/mysql-test/suite/binlog/r/innodb_rc_insert_before_delete.result new file mode 100644 index 00000000000..d4bd5ee07bf --- /dev/null +++ b/mysql-test/suite/binlog/r/innodb_rc_insert_before_delete.result @@ -0,0 +1,53 @@ +RESET MASTER; +connect pause_purge,localhost,root; +START TRANSACTION WITH CONSISTENT SNAPSHOT; +connection default; +CREATE TABLE t (pk int PRIMARY KEY, sk INT UNIQUE) ENGINE=InnoDB; +INSERT INTO t VALUES (10, 100); +connect con1,localhost,root; +BEGIN; +SELECT * FROM t WHERE sk = 100 FOR UPDATE; +pk sk +10 100 +connect con2,localhost,root; +SET DEBUG_SYNC="lock_wait_start SIGNAL insert_wait_started"; +INSERT INTO t VALUES (5, 100) # trx 1; +connect con3,localhost,root; +SET TRANSACTION ISOLATION LEVEL READ COMMITTED; +SET DEBUG_SYNC="now WAIT_FOR insert_wait_started"; +SET DEBUG_SYNC="lock_wait_start SIGNAL delete_started_waiting"; +BEGIN; +UPDATE t SET sk = 200 WHERE sk = 100; # trx 2; +connection con1; +SET DEBUG_SYNC="now WAIT_FOR delete_started_waiting"; +DELETE FROM t WHERE sk=100; +COMMIT; +disconnect con1; +connection con2; +disconnect con2; +connection con3; +must be logged in ROW format as the only event of trx 2 (con3) +INSERT INTO t VALUES (11, 101); +COMMIT; +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`; DELETE FROM t WHERE sk=100 +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Query # # use `test`; INSERT INTO t VALUES (5, 100) # trx 1 +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Annotate_rows # # INSERT INTO t VALUES (11, 101) +master-bin.000001 # Table_map # # table_id: # (test.t) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +disconnect con3; +connection default; +SELECT * FROM t; +pk sk +5 100 +11 101 +disconnect pause_purge; +SET DEBUG_SYNC="RESET"; +DROP TABLE t; diff --git a/mysql-test/suite/binlog/t/innodb_rc_insert_before_delete.test b/mysql-test/suite/binlog/t/innodb_rc_insert_before_delete.test new file mode 100644 index 00000000000..228d9778f56 --- /dev/null +++ b/mysql-test/suite/binlog/t/innodb_rc_insert_before_delete.test @@ -0,0 +1,92 @@ +--source include/have_innodb.inc +--source include/have_debug.inc +--source include/have_debug_sync.inc +--source include/have_binlog_format_mixed.inc +--source include/count_sessions.inc + +RESET MASTER; + +# MDEV-30010 merely adds is a Read-Committed version MDEV-30225 test +# solely to prove the RC isolation yields ROW binlog format as it is +# supposed to: +# https://mariadb.com/kb/en/unsafe-statements-for-statement-based-replication/#isolation-levels. +# The original MDEV-30225 test is adapted to the RC to create +# a similar safisticated scenario which does not lead to any deadlock though. + +--connect (pause_purge,localhost,root) +START TRANSACTION WITH CONSISTENT SNAPSHOT; + +--connection default +CREATE TABLE t (pk int PRIMARY KEY, sk INT UNIQUE) ENGINE=InnoDB; +INSERT INTO t VALUES (10, 100); + +--connect (con1,localhost,root) +BEGIN; # trx 0 +SELECT * FROM t WHERE sk = 100 FOR UPDATE; + +--connect (con2,localhost,root) +SET DEBUG_SYNC="lock_wait_start SIGNAL insert_wait_started"; +# trx 1 is locked on try to read the record in secondary index during duplicates +# check. It's the first in waiting queue, that's why it will be woken up firstly +# when trx 0 commits. +--send INSERT INTO t VALUES (5, 100) # trx 1 + +--connect (con3,localhost,root) +SET TRANSACTION ISOLATION LEVEL READ COMMITTED; +SET DEBUG_SYNC="now WAIT_FOR insert_wait_started"; +SET DEBUG_SYNC="lock_wait_start SIGNAL delete_started_waiting"; +# trx 2 can delete (5, 100) on master, but not on slave, as on slave trx 1 +# can insert (5, 100) after trx 2 positioned it's cursor. Trx 2 lock is placed +# in waiting queue after trx 1 lock, but its persistent cursor position was +# stored on (100, 10) record in secondary index before suspending. After trx 1 +# is committed, trx 2 will restore persistent cursor position on (100, 10). As +# (100, 5) secondary index record was inserted before (100, 10) in logical +# order, and (100, 10) record is delete-marked, trx 2 just continues scanning. +# +# Note. There can be several records with the same key in unique secondary +# index, but only one of them must be non-delete-marked. That's why when we do +# point query, cursor position is set in the first record in logical order, and +# then records are iterated until either non-delete-marked record is found or +# all records with the same unique fields are iterated. + +# to prepare showing interesting binlog events +--let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1) +--let $binlog_file=query_get_value(SHOW MASTER STATUS, File, 1) +BEGIN; +--send UPDATE t SET sk = 200 WHERE sk = 100; # trx 2 + +--connection con1 +SET DEBUG_SYNC="now WAIT_FOR delete_started_waiting"; +DELETE FROM t WHERE sk=100; # trx 0 +COMMIT; +--disconnect con1 + +--connection con2 +--reap +--disconnect con2 + +--connection con3 +--error 0 +--reap +if (`SELECT ROW_COUNT() > 0`) +{ + --echo unexpected effective UPDATE + --die +} +--echo must be logged in ROW format as the only event of trx 2 (con3) +INSERT INTO t VALUES (11, 101); +COMMIT; +--source include/show_binlog_events.inc +--disconnect con3 + +--connection default +# If the bug is not fixed, we will see the row inserted by trx 1 here. This can +# cause duplicate key error on slave, when some other trx tries in insert row +# with the same secondary key, as was inserted by trx 1, and not deleted by trx +# 2. +SELECT * FROM t; + +--disconnect pause_purge +SET DEBUG_SYNC="RESET"; +DROP TABLE t; +--source include/wait_until_count_sessions.inc diff --git a/mysql-test/suite/encryption/r/innodb-redo-nokeys.result b/mysql-test/suite/encryption/r/innodb-redo-nokeys.result index fd6ce1d979a..6482a5d960d 100644 --- a/mysql-test/suite/encryption/r/innodb-redo-nokeys.result +++ b/mysql-test/suite/encryption/r/innodb-redo-nokeys.result @@ -4,6 +4,7 @@ call mtr.add_suppression("InnoDB: Recovery cannot access file"); call mtr.add_suppression("InnoDB: Plugin initialization aborted"); call mtr.add_suppression("Plugin 'InnoDB' init function returned error\\."); call mtr.add_suppression("Plugin 'InnoDB' registration as a STORAGE ENGINE failed."); +call mtr.add_suppression("InnoDB: (Unable to apply log to|Discarding log for) corrupted page "); call mtr.add_suppression("InnoDB: Cannot apply log to \\[page id: space=[1-9][0-9]*, page number=0\\] of corrupted file '.*test.t[1-5]\\.ibd'"); call mtr.add_suppression("InnoDB: Failed to read page .* from file '.*'"); call mtr.add_suppression("InnoDB: OPT_PAGE_CHECKSUM mismatch"); diff --git a/mysql-test/suite/encryption/r/tempfiles_encrypted.result b/mysql-test/suite/encryption/r/tempfiles_encrypted.result index 103d8740087..5654946d786 100644 --- a/mysql-test/suite/encryption/r/tempfiles_encrypted.result +++ b/mysql-test/suite/encryption/r/tempfiles_encrypted.result @@ -4465,6 +4465,46 @@ pk a bit_or DROP TABLE t2; DROP TABLE t1; # +# MDEV-15178: Filesort::make_sortorder: Assertion `pos->field != __null | +# +CREATE TABLE t1 (i1 int, a int); +INSERT INTO t1 VALUES (1, 1), (2, 2),(3, 3); +CREATE TABLE t2 (i2 int); +INSERT INTO t2 VALUES (1),(2),(5),(1),(7),(4),(3); +SELECT +a, +RANK() OVER (ORDER BY SUM(DISTINCT i1)) +FROM +t1, t2 WHERE t2.i2 = t1.i1 +GROUP BY +a; +a RANK() OVER (ORDER BY SUM(DISTINCT i1)) +1 1 +2 2 +3 3 +DROP TABLE t1, t2; +# +# MDEV-17014: Crash server using ROW_NUMBER() OVER (PARTITION ..) +# +CREATE TABLE t1 (UID BIGINT); +CREATE TABLE t2 (UID BIGINT); +CREATE TABLE t3 (UID BIGINT); +insert into t1 VALUES (1),(2); +insert into t2 VALUES (1),(2); +insert into t3 VALUES (1),(2); +SELECT +ROW_NUMBER() OVER (PARTITION BY GROUP_CONCAT(TT1.UID)) +FROM t1 TT1, +t2 TT2, +t3 TT3 +WHERE TT3.UID = TT1.UID AND TT2.UID = TT3.UID +GROUP BY TT1.UID +; +ROW_NUMBER() OVER (PARTITION BY GROUP_CONCAT(TT1.UID)) +1 +1 +DROP TABLE t1, t2, t3; +# # End of 10.3 tests # # diff --git a/mysql-test/suite/encryption/t/innodb-redo-nokeys.test b/mysql-test/suite/encryption/t/innodb-redo-nokeys.test index 2624bb6ff9c..1eca1d8cf73 100644 --- a/mysql-test/suite/encryption/t/innodb-redo-nokeys.test +++ b/mysql-test/suite/encryption/t/innodb-redo-nokeys.test @@ -9,6 +9,7 @@ call mtr.add_suppression("InnoDB: Recovery cannot access file"); call mtr.add_suppression("InnoDB: Plugin initialization aborted"); call mtr.add_suppression("Plugin 'InnoDB' init function returned error\\."); call mtr.add_suppression("Plugin 'InnoDB' registration as a STORAGE ENGINE failed."); +call mtr.add_suppression("InnoDB: (Unable to apply log to|Discarding log for) corrupted page "); call mtr.add_suppression("InnoDB: Cannot apply log to \\[page id: space=[1-9][0-9]*, page number=0\\] of corrupted file '.*test.t[1-5]\\.ibd'"); call mtr.add_suppression("InnoDB: Failed to read page .* from file '.*'"); call mtr.add_suppression("InnoDB: OPT_PAGE_CHECKSUM mismatch"); diff --git a/mysql-test/suite/galera/disabled.def b/mysql-test/suite/galera/disabled.def index 849dffc7018..bcf6078f624 100644 --- a/mysql-test/suite/galera/disabled.def +++ b/mysql-test/suite/galera/disabled.def @@ -26,3 +26,4 @@ galera_var_ignore_apply_errors : 28: "Server did not transition to READY state" galera_bf_kill_debug : timeout after 900 seconds galera_ssl_upgrade : [Warning] Failed to load slave replication state from table mysql.gtid_slave_pos: 130: Incorrect file format 'gtid_slave_pos' galera_parallel_simple : timeout related to wsrep_sync_wait +galera_insert_bulk : MDEV-30536 no expected deadlock in galera_insert_bulk test diff --git a/mysql-test/suite/galera/r/MDEV-24143.result b/mysql-test/suite/galera/r/MDEV-24143.result deleted file mode 100644 index 860d8a35834..00000000000 --- a/mysql-test/suite/galera/r/MDEV-24143.result +++ /dev/null @@ -1,23 +0,0 @@ -connection node_2; -connection node_1; -CREATE TABLE t1 (c1 BIGINT NOT NULL PRIMARY KEY, c2 BINARY (10), c3 DATETIME); -SELECT get_lock ('test2', 0); -get_lock ('test2', 0) -1 -DROP TABLE t1; -CREATE TABLE t1 (c1 SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY); -INSERT INTO t1 VALUES (1); -SET SESSION wsrep_trx_fragment_size=10; -SET SESSION autocommit=0; -SELECT * FROM t1 WHERE c1 <=0 ORDER BY c1 DESC; -c1 -INSERT INTO t1 VALUES (4),(3),(1),(2); -ERROR 40001: Deadlock found when trying to get lock; try restarting transaction -CREATE TABLE t1 (pk INT PRIMARY KEY, b INT) ENGINE=SEQUENCE; -ERROR 42S01: Table 't1' already exists -ALTER TABLE t1 DROP COLUMN c2; -ERROR 42000: Can't DROP COLUMN `c2`; check that it exists -SELECT get_lock ('test', 1.5); -get_lock ('test', 1.5) -1 -DROP TABLE t1; diff --git a/mysql-test/suite/galera/r/galera_UK_conflict.result b/mysql-test/suite/galera/r/galera_UK_conflict.result index 402289d7ef8..b0420d58ede 100644 --- a/mysql-test/suite/galera/r/galera_UK_conflict.result +++ b/mysql-test/suite/galera/r/galera_UK_conflict.result @@ -144,7 +144,6 @@ SET debug_sync='RESET'; connection node_1; SET GLOBAL wsrep_slave_threads = DEFAULT; connection node_2; -SET SESSION wsrep_sync_wait=15; SELECT * FROM t1; f1 f2 f3 1 1 0 diff --git a/mysql-test/suite/galera/r/galera_backup_start.result b/mysql-test/suite/galera/r/galera_backup_start.result new file mode 100644 index 00000000000..253a0ce7416 --- /dev/null +++ b/mysql-test/suite/galera/r/galera_backup_start.result @@ -0,0 +1,6 @@ +connection node_2; +connection node_1; +BACKUP STAGE START; +START TRANSACTION; +COMMIT; +BACKUP STAGE END; diff --git a/mysql-test/suite/galera/r/galera_bf_abort_get_lock.result b/mysql-test/suite/galera/r/galera_bf_abort_get_lock.result deleted file mode 100644 index 0ef2a1a72c6..00000000000 --- a/mysql-test/suite/galera/r/galera_bf_abort_get_lock.result +++ /dev/null @@ -1,18 +0,0 @@ -connection node_2; -connection node_1; -CREATE TABLE t1 (f1 INTEGER PRIMARY KEY) ENGINE=InnoDB; -connection node_2a; -SELECT GET_LOCK("foo", 1000); -GET_LOCK("foo", 1000) -1 -connection node_2; -SET AUTOCOMMIT=OFF; -INSERT INTO t1 VALUES (1); -SELECT GET_LOCK("foo", 1000);; -connection node_1; -INSERT INTO t1 VALUES (1); -connection node_2; -ERROR 40001: Deadlock found when trying to get lock; try restarting transaction -wsrep_local_aborts_increment -1 -DROP TABLE t1; diff --git a/mysql-test/suite/galera/r/galera_locks_funcs.result b/mysql-test/suite/galera/r/galera_locks_funcs.result new file mode 100644 index 00000000000..25d3bbe28f5 --- /dev/null +++ b/mysql-test/suite/galera/r/galera_locks_funcs.result @@ -0,0 +1,24 @@ +connection node_2; +connection node_1; +CREATE TABLE t (c DOUBLE,c2 INT,PRIMARY KEY(c)) ENGINE=InnoDB; +INSERT INTO t values (1,1); +SELECT GET_LOCK('a',1); +ERROR 42000: This version of MariaDB doesn't yet support 'GET_LOCK in cluster (WSREP_ON=ON)' +SHOW WARNINGS; +Level Code Message +Error 1235 This version of MariaDB doesn't yet support 'GET_LOCK in cluster (WSREP_ON=ON)' +SELECT * FROM t; +c c2 +1 1 +SELECT RELEASE_LOCK('a'); +ERROR 42000: This version of MariaDB doesn't yet support 'RELEASE_LOCK in cluster (WSREP_ON=ON)' +SHOW WARNINGS; +Level Code Message +Error 1235 This version of MariaDB doesn't yet support 'RELEASE_LOCK in cluster (WSREP_ON=ON)' +SELECT RELEASE_ALL_LOCKS(); +ERROR 42000: This version of MariaDB doesn't yet support 'RELEASE_ALL_LOCKS in cluster (WSREP_ON=ON)' +SHOW WARNINGS; +Level Code Message +Error 1235 This version of MariaDB doesn't yet support 'RELEASE_ALL_LOCKS in cluster (WSREP_ON=ON)' +COMMIT; +DROP TABLE t; diff --git a/mysql-test/suite/galera/t/MDEV-24143.test b/mysql-test/suite/galera/t/MDEV-24143.test deleted file mode 100644 index e58f147cb7c..00000000000 --- a/mysql-test/suite/galera/t/MDEV-24143.test +++ /dev/null @@ -1,20 +0,0 @@ ---source include/galera_cluster.inc ---source include/have_sequence.inc - -CREATE TABLE t1 (c1 BIGINT NOT NULL PRIMARY KEY, c2 BINARY (10), c3 DATETIME); -SELECT get_lock ('test2', 0); -DROP TABLE t1; -CREATE TABLE t1 (c1 SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY); -INSERT INTO t1 VALUES (1); -SET SESSION wsrep_trx_fragment_size=10; -SET SESSION autocommit=0; -SELECT * FROM t1 WHERE c1 <=0 ORDER BY c1 DESC; ---error ER_LOCK_DEADLOCK -INSERT INTO t1 VALUES (4),(3),(1),(2); ---error ER_TABLE_EXISTS_ERROR -CREATE TABLE t1 (pk INT PRIMARY KEY, b INT) ENGINE=SEQUENCE; ---error ER_CANT_DROP_FIELD_OR_KEY -ALTER TABLE t1 DROP COLUMN c2; -SELECT get_lock ('test', 1.5); -DROP TABLE t1; - diff --git a/mysql-test/suite/galera/t/galera_UK_conflict.test b/mysql-test/suite/galera/t/galera_UK_conflict.test index fb4cdb416c3..7a14821cc32 100644 --- a/mysql-test/suite/galera/t/galera_UK_conflict.test +++ b/mysql-test/suite/galera/t/galera_UK_conflict.test @@ -140,6 +140,14 @@ SELECT * FROM t1; # original state in node 1 INSERT INTO t1 VALUES (7,7,7); INSERT INTO t1 VALUES (8,8,8); +SELECT COUNT(*) FROM t1; +SELECT * FROM t1; + +--connection node_1 +--let $wait_condition = SELECT COUNT(*) = 7 FROM t1 +--source include/wait_condition.inc +SELECT COUNT(*) FROM t1; +SELECT * FROM t1; DROP TABLE t1; @@ -268,7 +276,6 @@ SET debug_sync='RESET'; SET GLOBAL wsrep_slave_threads = DEFAULT; --connection node_2 -SET SESSION wsrep_sync_wait=15; SELECT * FROM t1; # replicate some transactions, so that wsrep slave thread count can reach @@ -276,4 +283,13 @@ SELECT * FROM t1; INSERT INTO t1 VALUES (7,7,7); INSERT INTO t1 VALUES (8,8,8); +SELECT COUNT(*) FROM t1; +SELECT * FROM t1; + +--connection node_1 +--let $wait_condition = SELECT COUNT(*) = 7 FROM t1 +--source include/wait_condition.inc +SELECT COUNT(*) FROM t1; +SELECT * FROM t1; + DROP TABLE t1; diff --git a/mysql-test/suite/galera/t/galera_backup_start.test b/mysql-test/suite/galera/t/galera_backup_start.test new file mode 100644 index 00000000000..4489e9ff582 --- /dev/null +++ b/mysql-test/suite/galera/t/galera_backup_start.test @@ -0,0 +1,6 @@ +--source include/galera_cluster.inc + +BACKUP STAGE START; +START TRANSACTION; +COMMIT; +BACKUP STAGE END; diff --git a/mysql-test/suite/galera/t/galera_bf_abort_get_lock.test b/mysql-test/suite/galera/t/galera_bf_abort_get_lock.test deleted file mode 100644 index 72fc1c5b583..00000000000 --- a/mysql-test/suite/galera/t/galera_bf_abort_get_lock.test +++ /dev/null @@ -1,36 +0,0 @@ ---source include/galera_cluster.inc ---source include/have_innodb.inc - -# -# Test a local transaction being aborted by a slave one while it is running a GET_LOCK() -# - -CREATE TABLE t1 (f1 INTEGER PRIMARY KEY) ENGINE=InnoDB; - ---let $galera_connection_name = node_2a ---let $galera_server_number = 2 ---source include/galera_connect.inc ---connection node_2a -SELECT GET_LOCK("foo", 1000); - ---connection node_2 -SET AUTOCOMMIT=OFF; ---let $wsrep_local_bf_aborts_before = `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_local_bf_aborts'` -INSERT INTO t1 VALUES (1); ---send SELECT GET_LOCK("foo", 1000); - ---connection node_1 -INSERT INTO t1 VALUES (1); - ---connection node_2 ---error ER_LOCK_DEADLOCK ---reap - ---let $wsrep_local_bf_aborts_after = `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_local_bf_aborts'` - -# Check that wsrep_local_bf_aborts has been incremented by exactly 1 ---disable_query_log ---eval SELECT $wsrep_local_bf_aborts_after - $wsrep_local_bf_aborts_before = 1 AS wsrep_local_aborts_increment; ---enable_query_log - -DROP TABLE t1; diff --git a/mysql-test/suite/galera/t/galera_bf_kill_debug.test b/mysql-test/suite/galera/t/galera_bf_kill_debug.test index c322f283757..6706734cc36 100644 --- a/mysql-test/suite/galera/t/galera_bf_kill_debug.test +++ b/mysql-test/suite/galera/t/galera_bf_kill_debug.test @@ -84,7 +84,7 @@ SET DEBUG_SYNC = "now SIGNAL continue_kill"; --reap --connection node_2a ---error 0,1213,2013 +--error 0,1213,2013,2026 select * from t1; --connection node_2 diff --git a/mysql-test/suite/galera/t/galera_gcache_recover_manytrx.test b/mysql-test/suite/galera/t/galera_gcache_recover_manytrx.test index 8f0f0ed65ea..214d5166781 100644 --- a/mysql-test/suite/galera/t/galera_gcache_recover_manytrx.test +++ b/mysql-test/suite/galera/t/galera_gcache_recover_manytrx.test @@ -205,8 +205,3 @@ CALL mtr.add_suppression("conflict state 7 after post commit"); # Warning happens when the cluster is started for the first time CALL mtr.add_suppression("Skipped GCache ring buffer recovery"); - ---connection node_2 -call mtr.add_suppression("Error in Log_event::read_log_event():.*"); -CALL mtr.add_suppression("Skipped GCache ring buffer recovery"); - diff --git a/mysql-test/suite/galera/t/galera_locks_funcs.test b/mysql-test/suite/galera/t/galera_locks_funcs.test new file mode 100644 index 00000000000..42838e9585c --- /dev/null +++ b/mysql-test/suite/galera/t/galera_locks_funcs.test @@ -0,0 +1,18 @@ +--source include/galera_cluster.inc + +CREATE TABLE t (c DOUBLE,c2 INT,PRIMARY KEY(c)) ENGINE=InnoDB; +INSERT INTO t values (1,1); +--error ER_NOT_SUPPORTED_YET +SELECT GET_LOCK('a',1); +SHOW WARNINGS; +SELECT * FROM t; +--error ER_NOT_SUPPORTED_YET +SELECT RELEASE_LOCK('a'); +SHOW WARNINGS; +# New in 10.5 +--error ER_NOT_SUPPORTED_YET +SELECT RELEASE_ALL_LOCKS(); +SHOW WARNINGS; +COMMIT; +DROP TABLE t; + diff --git a/mysql-test/suite/galera/t/galera_var_reject_queries.test b/mysql-test/suite/galera/t/galera_var_reject_queries.test index eefa154a2d9..fb86b69d95a 100644 --- a/mysql-test/suite/galera/t/galera_var_reject_queries.test +++ b/mysql-test/suite/galera/t/galera_var_reject_queries.test @@ -30,7 +30,7 @@ SELECT * FROM t1; SET GLOBAL wsrep_reject_queries = ALL_KILL; --connection node_1a ---error ER_CONNECTION_KILLED,2013,2006 +--error ER_CONNECTION_KILLED,2013,2006,2026 SELECT * FROM t1; --connect node_1b, 127.0.0.1, root, , test, $NODE_MYPORT_1 diff --git a/mysql-test/suite/innodb/r/alter_copy.result b/mysql-test/suite/innodb/r/alter_copy.result index 72ae28e9652..8c9e5966b2e 100644 --- a/mysql-test/suite/innodb/r/alter_copy.result +++ b/mysql-test/suite/innodb/r/alter_copy.result @@ -51,7 +51,7 @@ ADD INDEX(a,b,d), ADD INDEX(a,d,b), ADD INDEX(b,c,d), ADD INDEX(b,d,c), ALGORITHM=COPY; connection default; SET DEBUG_SYNC='now WAIT_FOR hung'; -# restart: --innodb-force-recovery=3 +# restart: --innodb-force-recovery=3 --debug_dbug=+d,recv_ran_out_of_buffer disconnect hang; FTS_INDEX_1.ibd FTS_INDEX_2.ibd diff --git a/mysql-test/suite/innodb/r/innodb_scrub.result b/mysql-test/suite/innodb/r/innodb_scrub.result index 1a4db0b541e..f824a1d0858 100644 --- a/mysql-test/suite/innodb/r/innodb_scrub.result +++ b/mysql-test/suite/innodb/r/innodb_scrub.result @@ -10,3 +10,22 @@ FLUSH TABLE t1 FOR EXPORT; UNLOCK TABLES; NOT FOUND /unicycle|repairman/ in t1.ibd DROP TABLE t1; +# +# MDEV-30527 Assertion !m_freed_pages in mtr_t::start() +# on DROP TEMPORARY TABLE +# +SET @scrub= @@GLOBAL.innodb_immediate_scrub_data_uncompressed; +SET GLOBAL innodb_immediate_scrub_data_uncompressed= 1; +SET @fpt=@@GLOBAL.innodb_file_per_table; +SET GLOBAL innodb_file_per_table=0; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release +CREATE TABLE t ENGINE=InnoDB AS SELECT 1; +DROP TABLE t; +SET GLOBAL innodb_file_per_table=@fpt; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release +CREATE TEMPORARY TABLE tmp ENGINE=InnoDB AS SELECT 1; +DROP TABLE tmp; +SET GLOBAL INNODB_IMMEDIATE_SCRUB_DATA_UNCOMPRESSED= @scrub; +# End of 10.6 tests diff --git a/mysql-test/suite/innodb/r/insert_into_empty.result b/mysql-test/suite/innodb/r/insert_into_empty.result index 429e0174f9d..eedfb681929 100644 --- a/mysql-test/suite/innodb/r/insert_into_empty.result +++ b/mysql-test/suite/innodb/r/insert_into_empty.result @@ -405,3 +405,21 @@ nb_corrupted_rows 0 DROP TABLE t1; # End of 10.7 tests +# +# MDEV-30426 Assertion !rec_offs_nth_extern(offsets2, n) +# during bulk insert +# +CREATE TABLE t1(f1 TEXT NOT NULL, f2 TEXT NOT NULL, +f3 TEXT NOT NULL, f4 TEXT NOT NULL, +f5 TEXT NOT NULL, f6 TEXT NOT NULL, +PRIMARY KEY(f6(10)))ENGINE=InnoDB; +BEGIN; +INSERT INTO t1 VALUES +(repeat('a', 200), repeat('b', 200), repeat('c', 200), +repeat('d', 200), repeat('e', 200), repeat('f', 200)), +(repeat('b', 20000), repeat('c', 16000), repeat('d', 12000), +repeat('e', 12000), repeat('f', 12000), repeat('f', 12000)); +ERROR HY000: Got error 1 "Operation not permitted" during COMMIT +COMMIT; +DROP TABLE t1; +# End of 10.8 tests diff --git a/mysql-test/suite/innodb/r/mdev-14846.result b/mysql-test/suite/innodb/r/mdev-14846.result index b41f6e6bf97..b5d8dcbec19 100644 --- a/mysql-test/suite/innodb/r/mdev-14846.result +++ b/mysql-test/suite/innodb/r/mdev-14846.result @@ -31,6 +31,7 @@ pk f1 f2 f3 3 t q 1 5 z t NULL SET DEBUG_SYNC='now SIGNAL default_dml'; +SET DEBUG_SYNC='now SIGNAL con2_dml'; connection default; SET DEBUG_SYNC='now WAIT_FOR default_dml'; explain UPDATE t3 AS alias1 LEFT JOIN t3 AS alias2 ON ( alias1.f1 <> alias1.f2 ) SET alias1.f3 = 59 WHERE ( EXISTS ( SELECT t1.f3 FROM t1 IGNORE INDEX (f1) WHERE t1.f1 = alias1.f1 ) ) OR alias2.f1 = 'h'; @@ -40,7 +41,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL # Using where UPDATE t3 AS alias1 LEFT JOIN t3 AS alias2 ON ( alias1.f1 <> alias1.f2 ) SET alias1.f3 = 59 WHERE ( EXISTS ( SELECT t1.f3 FROM t1 IGNORE INDEX (f1) WHERE t1.f1 = alias1.f1 ) ) OR alias2.f1 = 'h'; connect con2,localhost,root,,test; -set debug_sync='now WAIT_FOR default_dml'; +set debug_sync='now WAIT_FOR con2_dml'; SET DEBUG_SYNC='now SIGNAL con1_dml2'; disconnect con2; connection con1; diff --git a/mysql-test/suite/innodb/r/purge_thread_shutdown.result b/mysql-test/suite/innodb/r/purge_thread_shutdown.result index 85ac77e6d49..747fe91c966 100644 --- a/mysql-test/suite/innodb/r/purge_thread_shutdown.result +++ b/mysql-test/suite/innodb/r/purge_thread_shutdown.result @@ -22,6 +22,6 @@ delete from t1 where a=3; set global innodb_fast_shutdown=0; ERROR 42000: Variable 'innodb_fast_shutdown' can't be set to the value of '0' kill ID; -ERROR 70100: Connection was killed +Got one of the listed errors # restart drop table t1; diff --git a/mysql-test/suite/innodb/r/recovery_memory.result b/mysql-test/suite/innodb/r/recovery_memory.result index 4fa31009130..9aba9bccdb3 100644 --- a/mysql-test/suite/innodb/r/recovery_memory.result +++ b/mysql-test/suite/innodb/r/recovery_memory.result @@ -1,3 +1,7 @@ +call mtr.add_suppression("InnoDB: The change buffer is corrupted"); +call mtr.add_suppression("InnoDB: Plugin initialization aborted at srv0start.cc"); +call mtr.add_suppression("Plugin 'InnoDB' init function returned error"); +call mtr.add_suppression("Plugin 'InnoDB' registration as a STORAGE ENGINE failed."); CREATE TABLE t1(c TEXT, KEY(c(3072)))ENGINE=InnoDB; CREATE PROCEDURE dorepeat() LOOP @@ -10,3 +14,19 @@ CALL dorepeat(); connection default; # restart: --innodb_buffer_pool_size=5242880 DROP TABLE t1; +DROP PROCEDURE dorepeat; +# +# MDEV-30552 InnoDB recovery crashes when error +# handling scenario +# +SET DEBUG_DBUG="+d,ib_log_checkpoint_avoid_hard"; +CREATE TABLE t1(f1 INT NOT NULL)ENGINE=InnoDB; +INSERT INTO t1 SELECT * FROM seq_1_to_65536; +# restart: with restart_parameters +# restart +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f1` int(11) NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/alter_copy.test b/mysql-test/suite/innodb/t/alter_copy.test index 90f2171d10b..b62f812f4b7 100644 --- a/mysql-test/suite/innodb/t/alter_copy.test +++ b/mysql-test/suite/innodb/t/alter_copy.test @@ -57,7 +57,7 @@ ALTER TABLE t ADD INDEX(b,c,d,a),ADD INDEX(b,c,a,d),ADD INDEX(b,a,c,d),ADD INDEX connection default; SET DEBUG_SYNC='now WAIT_FOR hung'; let $shutdown_timeout=0; ---let $restart_parameters= --innodb-force-recovery=3 +--let $restart_parameters= --innodb-force-recovery=3 --debug_dbug="+d,recv_ran_out_of_buffer" --source include/restart_mysqld.inc disconnect hang; let $shutdown_timeout=; diff --git a/mysql-test/suite/innodb/t/innodb_bug51920.test b/mysql-test/suite/innodb/t/innodb_bug51920.test index 0a9839b612a..84977925548 100644 --- a/mysql-test/suite/innodb/t/innodb_bug51920.test +++ b/mysql-test/suite/innodb/t/innodb_bug51920.test @@ -38,7 +38,7 @@ let $wait_condition = # depending on platform. # connection con1; --- error 1317, 2006, 2013, ER_CONNECTION_KILLED +-- error 1317, 2006, 2013, ER_CONNECTION_KILLED, 2026 reap; connection default; DROP TABLE bug51920; diff --git a/mysql-test/suite/innodb/t/innodb_scrub.test b/mysql-test/suite/innodb/t/innodb_scrub.test index c7d06187e9f..8fe460da4d3 100644 --- a/mysql-test/suite/innodb/t/innodb_scrub.test +++ b/mysql-test/suite/innodb/t/innodb_scrub.test @@ -27,3 +27,19 @@ FLUSH TABLE t1 FOR EXPORT; UNLOCK TABLES; -- source include/search_pattern_in_file.inc DROP TABLE t1; + +--echo # +--echo # MDEV-30527 Assertion !m_freed_pages in mtr_t::start() +--echo # on DROP TEMPORARY TABLE +--echo # +SET @scrub= @@GLOBAL.innodb_immediate_scrub_data_uncompressed; +SET GLOBAL innodb_immediate_scrub_data_uncompressed= 1; +SET @fpt=@@GLOBAL.innodb_file_per_table; +SET GLOBAL innodb_file_per_table=0; +CREATE TABLE t ENGINE=InnoDB AS SELECT 1; +DROP TABLE t; +SET GLOBAL innodb_file_per_table=@fpt; +CREATE TEMPORARY TABLE tmp ENGINE=InnoDB AS SELECT 1; +DROP TABLE tmp; +SET GLOBAL INNODB_IMMEDIATE_SCRUB_DATA_UNCOMPRESSED= @scrub; +--echo # End of 10.6 tests diff --git a/mysql-test/suite/innodb/t/insert_into_empty.opt b/mysql-test/suite/innodb/t/insert_into_empty.opt new file mode 100644 index 00000000000..c856c2d215a --- /dev/null +++ b/mysql-test/suite/innodb/t/insert_into_empty.opt @@ -0,0 +1 @@ +--innodb_sort_buffer_size=65536 diff --git a/mysql-test/suite/innodb/t/insert_into_empty.test b/mysql-test/suite/innodb/t/insert_into_empty.test index 64043e383dc..ee32a2d7cac 100644 --- a/mysql-test/suite/innodb/t/insert_into_empty.test +++ b/mysql-test/suite/innodb/t/insert_into_empty.test @@ -424,3 +424,22 @@ SELECT COUNT(*) AS nb_corrupted_rows FROM t1 WHERE data != REPEAT('X', @@innodb_ DROP TABLE t1; --echo # End of 10.7 tests + +--echo # +--echo # MDEV-30426 Assertion !rec_offs_nth_extern(offsets2, n) +--echo # during bulk insert +--echo # +CREATE TABLE t1(f1 TEXT NOT NULL, f2 TEXT NOT NULL, + f3 TEXT NOT NULL, f4 TEXT NOT NULL, + f5 TEXT NOT NULL, f6 TEXT NOT NULL, + PRIMARY KEY(f6(10)))ENGINE=InnoDB; +BEGIN; +--error ER_ERROR_DURING_COMMIT +INSERT INTO t1 VALUES + (repeat('a', 200), repeat('b', 200), repeat('c', 200), + repeat('d', 200), repeat('e', 200), repeat('f', 200)), + (repeat('b', 20000), repeat('c', 16000), repeat('d', 12000), + repeat('e', 12000), repeat('f', 12000), repeat('f', 12000)); +COMMIT; +DROP TABLE t1; +--echo # End of 10.8 tests diff --git a/mysql-test/suite/innodb/t/mdev-14846.test b/mysql-test/suite/innodb/t/mdev-14846.test index 079a066cec5..a576d244007 100644 --- a/mysql-test/suite/innodb/t/mdev-14846.test +++ b/mysql-test/suite/innodb/t/mdev-14846.test @@ -35,6 +35,7 @@ SET DEBUG_SYNC='now WAIT_FOR con1_dml'; begin; SELECT * FROM t1 for update; # Holds x lock of all records in the table t1 SET DEBUG_SYNC='now SIGNAL default_dml'; +SET DEBUG_SYNC='now SIGNAL con2_dml'; --connection default SET DEBUG_SYNC='now WAIT_FOR default_dml'; @@ -44,7 +45,7 @@ explain UPDATE t3 AS alias1 LEFT JOIN t3 AS alias2 ON ( alias1.f1 <> alias1.f2 ) # It holds the lock of all record in t3 and tries to acquire record lock for the table t1. --connect (con2,localhost,root,,test) -set debug_sync='now WAIT_FOR default_dml'; +set debug_sync='now WAIT_FOR con2_dml'; let $wait_condition= select count(*) > 0 from information_schema.innodb_lock_waits; --source include/wait_condition.inc diff --git a/mysql-test/suite/innodb/t/purge_thread_shutdown.test b/mysql-test/suite/innodb/t/purge_thread_shutdown.test index 5be29b7a6a3..447f1fd3804 100644 --- a/mysql-test/suite/innodb/t/purge_thread_shutdown.test +++ b/mysql-test/suite/innodb/t/purge_thread_shutdown.test @@ -36,7 +36,7 @@ set global innodb_fast_shutdown=0; # the error code let $me=`select concat(' ', connection_id())`; replace_result $me ID; -error ER_CONNECTION_KILLED; +error ER_CONNECTION_KILLED, 2026; eval kill $me; source include/start_mysqld.inc; diff --git a/mysql-test/suite/innodb/t/recovery_memory.test b/mysql-test/suite/innodb/t/recovery_memory.test index d9afd52c499..145b39d56f6 100644 --- a/mysql-test/suite/innodb/t/recovery_memory.test +++ b/mysql-test/suite/innodb/t/recovery_memory.test @@ -1,5 +1,11 @@ --source include/have_innodb.inc --source include/big_test.inc +--source include/have_sequence.inc +--source include/maybe_debug.inc +call mtr.add_suppression("InnoDB: The change buffer is corrupted"); +call mtr.add_suppression("InnoDB: Plugin initialization aborted at srv0start.cc"); +call mtr.add_suppression("Plugin 'InnoDB' init function returned error"); +call mtr.add_suppression("Plugin 'InnoDB' registration as a STORAGE ENGINE failed."); CREATE TABLE t1(c TEXT, KEY(c(3072)))ENGINE=InnoDB; DELIMITER |; @@ -19,3 +25,27 @@ let $shutdown_timeout=0; let $restart_parameters=--innodb_buffer_pool_size=5242880; --source include/restart_mysqld.inc DROP TABLE t1; +DROP PROCEDURE dorepeat; + +--echo # +--echo # MDEV-30552 InnoDB recovery crashes when error +--echo # handling scenario +--echo # +if ($have_debug) { +SET DEBUG_DBUG="+d,ib_log_checkpoint_avoid_hard"; +let $restart_parameters=--innodb_buffer_pool_size=5242880 --debug_dbug="+d,ibuf_init_corrupt"; +} +if (!$have_debug) { +--echo SET DEBUG_DBUG="+d,ib_log_checkpoint_avoid_hard"; +let $restart_parameters=--innodb_buffer_pool_size=5242880; +} +CREATE TABLE t1(f1 INT NOT NULL)ENGINE=InnoDB; +INSERT INTO t1 SELECT * FROM seq_1_to_65536; +let $restart_noprint=1; +let $shutdown_timeout=0; +--source include/restart_mysqld.inc +let $restart_noprint=0; +let $restart_parameters=; +--source include/restart_mysqld.inc +SHOW CREATE TABLE t1; +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/temporary_table.test b/mysql-test/suite/innodb/t/temporary_table.test index 5d7e5d51696..6b2cd6b9b2c 100644 --- a/mysql-test/suite/innodb/t/temporary_table.test +++ b/mysql-test/suite/innodb/t/temporary_table.test @@ -143,7 +143,7 @@ let $counter= 5000; let $mysql_errno= 9999; while ($mysql_errno) { - --error 0,ER_SERVER_SHUTDOWN,ER_CONNECTION_KILLED,2002,2006,2013 + --error 0,ER_SERVER_SHUTDOWN,ER_CONNECTION_KILLED,2002,2006,2013,2026 select 1; dec $counter; diff --git a/mysql-test/suite/mariabackup/full_backup_win.result b/mysql-test/suite/mariabackup/full_backup_win.result new file mode 100644 index 00000000000..940c7056d3f --- /dev/null +++ b/mysql-test/suite/mariabackup/full_backup_win.result @@ -0,0 +1,12 @@ +# +# MDEV-30492 Crash when use mariabackup.exe with config 'innodb_flush_method=async_unbuffered' +# +# xtrabackup backup +# xtrabackup prepare +# shutdown server +# remove datadir +# xtrabackup move back +# restart +# +# End of 10.4 tests +# diff --git a/mysql-test/suite/mariabackup/full_backup_win.test b/mysql-test/suite/mariabackup/full_backup_win.test new file mode 100644 index 00000000000..5a1d1c38026 --- /dev/null +++ b/mysql-test/suite/mariabackup/full_backup_win.test @@ -0,0 +1,24 @@ +--source include/windows.inc + +let $targetdir=$MYSQLTEST_VARDIR/tmp/backup; + +--echo # +--echo # MDEV-30492 Crash when use mariabackup.exe with config 'innodb_flush_method=async_unbuffered' +--echo # + +echo # xtrabackup backup; +--disable_result_log +exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --innodb_flush_method=normal --backup --target-dir=$targetdir; +--enable_result_log + +echo # xtrabackup prepare; +--disable_result_log +exec $XTRABACKUP --prepare --innodb-flush-method=async_unbuffered --target-dir=$targetdir; +-- source include/restart_and_restore.inc +--enable_result_log + +rmdir $targetdir; + +--echo # +--echo # End of 10.4 tests +--echo # diff --git a/mysql-test/suite/mariabackup/incremental_drop_db.result b/mysql-test/suite/mariabackup/incremental_drop_db.result new file mode 100644 index 00000000000..3a6c89f8de0 --- /dev/null +++ b/mysql-test/suite/mariabackup/incremental_drop_db.result @@ -0,0 +1,30 @@ +call mtr.add_suppression("InnoDB: New log files created"); +# +# Start of 10.3 tests +# +# +# MDEV-23335 MariaBackup Incremental Does Not Reflect Dropped/Created Databases +# +CREATE DATABASE db1; +CREATE DATABASE db2; +CREATE TABLE db1.t1 (a INT) ENGINE=MyISAM; +CREATE TABLE db1.t2 (a INT) ENGINE=InnoDB; +# Create base backup +DROP DATABASE db1; +# Create incremental backup +# Remove incremental_dir/db2/db.opt file to make incremental_dir/db2/ empty +# Prepare base backup, apply incremental one +# shutdown server +# remove datadir +# xtrabackup move back +# restart +# Expect no 'db1' in the output, because it was really dropped. +# Expect 'db2' in the ouput, because it was not dropped! +# (its incremental directory was emptied only) +SHOW DATABASES LIKE 'db%'; +Database (db%) +db2 +DROP DATABASE db2; +# +# End of 10.3 tests +# diff --git a/mysql-test/suite/mariabackup/incremental_drop_db.test b/mysql-test/suite/mariabackup/incremental_drop_db.test new file mode 100644 index 00000000000..de270435e9d --- /dev/null +++ b/mysql-test/suite/mariabackup/incremental_drop_db.test @@ -0,0 +1,68 @@ +--source include/have_innodb.inc +call mtr.add_suppression("InnoDB: New log files created"); + +--echo # +--echo # Start of 10.3 tests +--echo # + +--echo # +--echo # MDEV-23335 MariaBackup Incremental Does Not Reflect Dropped/Created Databases +--echo # + +--let $datadir=`SELECT @@datadir` +--let $basedir=$MYSQLTEST_VARDIR/tmp/backup +--let $incremental_dir=$MYSQLTEST_VARDIR/tmp/backup_inc1 + +# Create two databases: +# - db1 is dropped normally below +# - db2 is used to cover a corner case: its db.opt file is removed + +# Incremental backup contains: +# - no directory for db1 +# - an empty directory for db2 (after we remove db2/db.opt) + + +CREATE DATABASE db1; +CREATE DATABASE db2; + +# Add some tables to db1 +CREATE TABLE db1.t1 (a INT) ENGINE=MyISAM; +CREATE TABLE db1.t2 (a INT) ENGINE=InnoDB; + +--echo # Create base backup +--disable_result_log +--exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --backup --target-dir=$basedir +--enable_result_log + +DROP DATABASE db1; + +--echo # Create incremental backup +--exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --backup --target-dir=$incremental_dir --incremental-basedir=$basedir + +--echo # Remove incremental_dir/db2/db.opt file to make incremental_dir/db2/ empty +--remove_file $incremental_dir/db2/db.opt + + +--echo # Prepare base backup, apply incremental one +--disable_result_log +--exec $XTRABACKUP --prepare --target-dir=$basedir +--exec $XTRABACKUP --prepare --target-dir=$basedir --incremental-dir=$incremental_dir +--enable_result_log + +--let $targetdir=$basedir +--source include/restart_and_restore.inc +--enable_result_log + +--echo # Expect no 'db1' in the output, because it was really dropped. +--echo # Expect 'db2' in the ouput, because it was not dropped! +--echo # (its incremental directory was emptied only) + +SHOW DATABASES LIKE 'db%'; +DROP DATABASE db2; + +--rmdir $basedir +--rmdir $incremental_dir + +--echo # +--echo # End of 10.3 tests +--echo # diff --git a/mysql-test/suite/multi_source/gtid.result b/mysql-test/suite/multi_source/gtid.result index dde53ca67b4..6530bb744ac 100644 --- a/mysql-test/suite/multi_source/gtid.result +++ b/mysql-test/suite/multi_source/gtid.result @@ -47,12 +47,8 @@ connection master2; INSERT INTO t2 VALUES (2, "switch1"); INSERT INTO t3 VALUES (202, "switch1 b"); connection slave2; -CHANGE MASTER 'slave1' TO master_port=MYPORT_1, master_host='127.0.0.1', master_user='root', master_use_gtid=current_pos; -Warnings: -Warning 1681 'master_use_gtid=current_pos' is deprecated and will be removed in a future release. Please use master_demote_to_slave=1 instead -CHANGE MASTER 'slave2' TO master_port=MYPORT_2, master_host='127.0.0.1', master_user='root', master_use_gtid=current_pos; -Warnings: -Warning 1681 'master_use_gtid=current_pos' is deprecated and will be removed in a future release. Please use master_demote_to_slave=1 instead +CHANGE MASTER 'slave1' TO master_port=MYPORT_1, master_host='127.0.0.1', master_user='root'; +CHANGE MASTER 'slave2' TO master_port=MYPORT_2, master_host='127.0.0.1', master_user='root'; SET default_master_connection = 'slave1'; START SLAVE; include/wait_for_slave_to_start.inc @@ -79,9 +75,7 @@ INSERT INTO t3 VALUES (204, "switch 3 b"); connection slave2; include/sync_with_master_gtid.inc connection slave1; -CHANGE MASTER TO master_port=MYPORT_4, master_host='127.0.0.1', master_user='root', master_use_gtid=current_pos; -Warnings: -Warning 1681 'master_use_gtid=current_pos' is deprecated and will be removed in a future release. Please use master_demote_to_slave=1 instead +CHANGE MASTER TO master_port=MYPORT_4, master_host='127.0.0.1', master_user='root'; START SLAVE; SELECT * FROM t1 ORDER BY a; a b diff --git a/mysql-test/suite/multi_source/gtid.test b/mysql-test/suite/multi_source/gtid.test index c81ca20a254..9fc7a66893c 100644 --- a/mysql-test/suite/multi_source/gtid.test +++ b/mysql-test/suite/multi_source/gtid.test @@ -73,9 +73,9 @@ INSERT INTO t3 VALUES (202, "switch1 b"); --connection slave2 --replace_result $SERVER_MYPORT_1 MYPORT_1 -eval CHANGE MASTER 'slave1' TO master_port=$SERVER_MYPORT_1, master_host='127.0.0.1', master_user='root', master_use_gtid=current_pos; +eval CHANGE MASTER 'slave1' TO master_port=$SERVER_MYPORT_1, master_host='127.0.0.1', master_user='root'; --replace_result $SERVER_MYPORT_2 MYPORT_2 -eval CHANGE MASTER 'slave2' TO master_port=$SERVER_MYPORT_2, master_host='127.0.0.1', master_user='root', master_use_gtid=current_pos; +eval CHANGE MASTER 'slave2' TO master_port=$SERVER_MYPORT_2, master_host='127.0.0.1', master_user='root'; SET default_master_connection = 'slave1'; START SLAVE; --source include/wait_for_slave_to_start.inc @@ -125,7 +125,7 @@ INSERT INTO t3 VALUES (204, "switch 3 b"); --connection slave1 --replace_result $SERVER_MYPORT_4 MYPORT_4 -eval CHANGE MASTER TO master_port=$SERVER_MYPORT_4, master_host='127.0.0.1', master_user='root', master_use_gtid=current_pos; +eval CHANGE MASTER TO master_port=$SERVER_MYPORT_4, master_host='127.0.0.1', master_user='root'; START SLAVE; --let $wait_condition= SELECT (SELECT COUNT(*) FROM t1)=3 AND (SELECT COUNT(*) FROM t2)=4 AND (SELECT COUNT(*) FROM t3)=7 --source include/wait_condition.inc diff --git a/mysql-test/suite/plugins/r/locales.result b/mysql-test/suite/plugins/r/locales.result index 2ea88493705..e906d27c21e 100644 --- a/mysql-test/suite/plugins/r/locales.result +++ b/mysql-test/suite/plugins/r/locales.result @@ -1,3 +1,4 @@ +SET names utf8; select * from information_schema.locales; ID NAME DESCRIPTION MAX_MONTH_NAME_LENGTH MAX_DAY_NAME_LENGTH DECIMAL_POINT THOUSAND_SEP ERROR_MESSAGE_LANGUAGE 0 en_US English - United States 9 9 . , english @@ -52,7 +53,7 @@ ID NAME DESCRIPTION MAX_MONTH_NAME_LENGTH MAX_DAY_NAME_LENGTH DECIMAL_POINT THOU 49 ta_IN Tamil - India 10 8 . , english 50 te_IN Telugu - India 10 9 . , english 51 th_TH Thai - Thailand 10 8 . , english -52 tr_TR Turkish - Turkey 7 9 , . english +52 tr_TR Turkish - Türkiye 7 9 , . english 53 uk_UA Ukrainian - Ukraine 8 9 , . ukrainian 54 ur_PK Urdu - Pakistan 6 6 . , english 55 vi_VN Vietnamese - Vietnam 16 11 , . english @@ -165,7 +166,7 @@ Id Name Description Error_Message_Language 49 ta_IN Tamil - India english 50 te_IN Telugu - India english 51 th_TH Thai - Thailand english -52 tr_TR Turkish - Turkey english +52 tr_TR Turkish - Türkiye english 53 uk_UA Ukrainian - Ukraine ukrainian 54 ur_PK Urdu - Pakistan english 55 vi_VN Vietnamese - Vietnam english diff --git a/mysql-test/suite/plugins/r/server_audit.result b/mysql-test/suite/plugins/r/server_audit.result index ae40d54e8df..212f27fdf84 100644 --- a/mysql-test/suite/plugins/r/server_audit.result +++ b/mysql-test/suite/plugins/r/server_audit.result @@ -521,3 +521,10 @@ TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'show status like \'server_audit_ TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'show variables like \'server_audit%\'',0 TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,plugin, TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'uninstall plugin server_audit',0 +# +# MDEV-27631 Assertion `global_status_var.global_memory_used == 0' failed in mysqld_exit +# +install plugin server_audit soname 'server_audit'; +uninstall plugin server_audit; +Warnings: +Warning 1620 Plugin is busy and will be uninstalled on shutdown diff --git a/mysql-test/suite/plugins/t/locales.test b/mysql-test/suite/plugins/t/locales.test index a3afe75046f..2b4d56433d9 100644 --- a/mysql-test/suite/plugins/t/locales.test +++ b/mysql-test/suite/plugins/t/locales.test @@ -2,6 +2,7 @@ if (`select count(*) = 0 from information_schema.plugins where plugin_name = 'lo { --skip LOCALES plugin is not active } +SET names utf8; select * from information_schema.locales; show locales; diff --git a/mysql-test/suite/plugins/t/server_audit.test b/mysql-test/suite/plugins/t/server_audit.test index 3a78a4049e8..1beeaff7538 100644 --- a/mysql-test/suite/plugins/t/server_audit.test +++ b/mysql-test/suite/plugins/t/server_audit.test @@ -235,3 +235,8 @@ uninstall plugin server_audit; cat_file $MYSQLD_DATADIR/server_audit.log; remove_file $MYSQLD_DATADIR/server_audit.log; +--echo # +--echo # MDEV-27631 Assertion `global_status_var.global_memory_used == 0' failed in mysqld_exit +--echo # +install plugin server_audit soname 'server_audit'; +uninstall plugin server_audit; diff --git a/mysql-test/suite/rpl/include/create_or_drop_sync_func.inc b/mysql-test/suite/rpl/include/create_or_drop_sync_func.inc new file mode 100644 index 00000000000..74cde5de1db --- /dev/null +++ b/mysql-test/suite/rpl/include/create_or_drop_sync_func.inc @@ -0,0 +1,75 @@ +# Creates or drops a stored function as a part of debug-sync based +# synchronization mechanism between replication servers. +# +# Parameters: +# $create_or_drop= [create] +# $server_master = [master] +# $server_slave = [slave] +if (!$create_or_drop) +{ + --let $create_or_drop=create +} + +if (`select strcmp('$create_or_drop', 'create') = 0`) +{ + if (!$server_master) + { + --let $server_master=master + } + if (!$server_slave) + { + --let $server_slave=slave + } + + --connection $server_master + # Use a stored function to inject a debug_sync into the appropriate THD. + # The function does nothing on the master, and on the slave it injects the + # desired debug_sync action(s). + SET sql_log_bin=0; + --delimiter || + CREATE FUNCTION foo(x INT, d1 VARCHAR(500), d2 VARCHAR(500)) + RETURNS INT DETERMINISTIC + BEGIN + RETURN x; + END + || + --delimiter ; + SET sql_log_bin=1; + + --connection $server_slave + + SET sql_log_bin=0; + --delimiter || + CREATE FUNCTION foo(x INT, d1 VARCHAR(500), d2 VARCHAR(500)) + RETURNS INT DETERMINISTIC + BEGIN + IF d1 != '' THEN + SET debug_sync = d1; + END IF; + IF d2 != '' THEN + SET debug_sync = d2; + END IF; + RETURN x; + END + || + --delimiter ; +SET sql_log_bin=1; +} + +if (`select strcmp('$create_or_drop', 'drop') = 0`) +{ + if (!$server_slave) + { + --let $server_slave=slave= + } + if (!$server_master) + { + --let $server_master=master + } + --connection $server_slave + SET DEBUG_SYNC='RESET'; + + --connection $server_master + SET DEBUG_SYNC='RESET'; + DROP FUNCTION foo; +} diff --git a/mysql-test/suite/rpl/r/parallel_backup.result b/mysql-test/suite/rpl/r/parallel_backup.result index 9d394a220d5..7cf72ed15e4 100644 --- a/mysql-test/suite/rpl/r/parallel_backup.result +++ b/mysql-test/suite/rpl/r/parallel_backup.result @@ -8,6 +8,8 @@ connection master; CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE = innodb; INSERT INTO t1 VALUES(100); connection slave; +call mtr.add_suppression("Deadlock found when trying to get lock"); +call mtr.add_suppression("Commit failed due to failure of an earlier commit"); include/stop_slave.inc SET @old_parallel_threads= @@GLOBAL.slave_parallel_threads; SET @old_parallel_mode = @@GLOBAL.slave_parallel_mode; @@ -31,6 +33,167 @@ connection backup_slave; BACKUP STAGE END; connection slave; include/diff_tables.inc [master:t1,slave:t1] +# MDEV-30423: dealock XA COMMIT vs BACKUP +# +# Normal XA COMMIT +connection slave; +include/stop_slave.inc +connection master; +connection aux_slave; +BEGIN; +INSERT INTO t1 VALUES (102); +connection master; +XA START '1'; +INSERT INTO t1 VALUES (101); +XA END '1'; +XA PREPARE '1'; +connection master1; +INSERT INTO t1 VALUES (102); +connection master; +XA COMMIT '1'; +include/save_master_gtid.inc +connection slave; +include/start_slave.inc +connection aux_slave; +# Xid '1' must be in the output: +XA RECOVER; +formatID gtrid_length bqual_length data +1 1 0 1 +connection backup_slave; +BACKUP STAGE START; +BACKUP STAGE BLOCK_COMMIT; +connection aux_slave; +ROLLBACK; +connection backup_slave; +BACKUP STAGE END; +connection slave; +include/sync_with_master_gtid.inc +include/stop_slave.inc +# +# Normal XA ROLLBACK +connection slave; +include/stop_slave.inc +Warnings: +Note 1255 Slave already has been stopped +connection master; +connection aux_slave; +BEGIN; +INSERT INTO t1 VALUES (104); +connection master; +XA START '1'; +INSERT INTO t1 VALUES (103); +XA END '1'; +XA PREPARE '1'; +connection master1; +INSERT INTO t1 VALUES (104); +connection master; +XA ROLLBACK '1'; +include/save_master_gtid.inc +connection slave; +include/start_slave.inc +connection aux_slave; +# Xid '1' must be in the output: +XA RECOVER; +formatID gtrid_length bqual_length data +1 1 0 1 +connection backup_slave; +BACKUP STAGE START; +BACKUP STAGE BLOCK_COMMIT; +connection aux_slave; +ROLLBACK; +connection backup_slave; +BACKUP STAGE END; +connection slave; +include/sync_with_master_gtid.inc +include/stop_slave.inc +# +# Errored out XA COMMIT +connection slave; +include/stop_slave.inc +Warnings: +Note 1255 Slave already has been stopped +connection master; +connection aux_slave; +BEGIN; +INSERT INTO t1 VALUES (106); +connection master; +XA START '1'; +INSERT INTO t1 VALUES (105); +XA END '1'; +XA PREPARE '1'; +connection master1; +INSERT INTO t1 VALUES (106); +connection master; +XA COMMIT '1'; +include/save_master_gtid.inc +connection slave; +SET @sav_innodb_lock_wait_timeout = @@global.innodb_lock_wait_timeout; +SET @sav_slave_transaction_retries = @@global.slave_transaction_retries; +SET @@global.innodb_lock_wait_timeout =1; +SET @@global.slave_transaction_retries=0; +include/start_slave.inc +connection aux_slave; +# Xid '1' must be in the output: +XA RECOVER; +formatID gtrid_length bqual_length data +1 1 0 1 +connection backup_slave; +BACKUP STAGE START; +BACKUP STAGE BLOCK_COMMIT; +connection aux_slave; +ROLLBACK; +connection backup_slave; +BACKUP STAGE END; +connection slave; +include/stop_slave.inc +SET @@global.innodb_lock_wait_timeout = @sav_innodb_lock_wait_timeout; +SET @@global.slave_transaction_retries= @sav_slave_transaction_retries; +connection slave; +include/start_slave.inc +include/sync_with_master_gtid.inc +# +# Errored out XA ROLLBACK +connection slave; +include/stop_slave.inc +connection master; +connection aux_slave; +BEGIN; +INSERT INTO t1 VALUES (108); +connection master; +XA START '1'; +INSERT INTO t1 VALUES (107); +XA END '1'; +XA PREPARE '1'; +connection master1; +INSERT INTO t1 VALUES (108); +connection master; +XA ROLLBACK '1'; +include/save_master_gtid.inc +connection slave; +SET @sav_innodb_lock_wait_timeout = @@global.innodb_lock_wait_timeout; +SET @sav_slave_transaction_retries = @@global.slave_transaction_retries; +SET @@global.innodb_lock_wait_timeout =1; +SET @@global.slave_transaction_retries=0; +include/start_slave.inc +connection aux_slave; +# Xid '1' must be in the output: +XA RECOVER; +formatID gtrid_length bqual_length data +1 1 0 1 +connection backup_slave; +BACKUP STAGE START; +BACKUP STAGE BLOCK_COMMIT; +connection aux_slave; +ROLLBACK; +connection backup_slave; +BACKUP STAGE END; +connection slave; +include/stop_slave.inc +SET @@global.innodb_lock_wait_timeout = @sav_innodb_lock_wait_timeout; +SET @@global.slave_transaction_retries= @sav_slave_transaction_retries; +connection slave; +include/start_slave.inc +include/sync_with_master_gtid.inc connection slave; include/stop_slave.inc SET @@global.slave_parallel_threads= @old_parallel_threads; diff --git a/mysql-test/suite/rpl/r/parallel_backup_lsu_off.result b/mysql-test/suite/rpl/r/parallel_backup_lsu_off.result new file mode 100644 index 00000000000..b89cb154f24 --- /dev/null +++ b/mysql-test/suite/rpl/r/parallel_backup_lsu_off.result @@ -0,0 +1,207 @@ +# Specialized --log-slave-updates = 0 version of parallel_backup test. +# MDEV-21953: deadlock between BACKUP STAGE BLOCK_COMMIT and parallel +# MDEV-30423: dealock XA COMMIT vs BACKUP +include/master-slave.inc +[connection master] +# +# MDEV-21953: deadlock between BACKUP STAGE BLOCK_COMMIT and parallel +# replication +# +connection master; +CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE = innodb; +INSERT INTO t1 VALUES(100); +connection slave; +call mtr.add_suppression("Deadlock found when trying to get lock"); +call mtr.add_suppression("Commit failed due to failure of an earlier commit"); +include/stop_slave.inc +SET @old_parallel_threads= @@GLOBAL.slave_parallel_threads; +SET @old_parallel_mode = @@GLOBAL.slave_parallel_mode; +SET @@global.slave_parallel_threads= 2; +SET @@global.slave_parallel_mode = 'optimistic'; +connection master; +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (2); +connect aux_slave,127.0.0.1,root,,test,$SLAVE_MYPORT,; +BEGIN; +INSERT INTO t1 VALUES (1); +connection slave; +include/start_slave.inc +connection aux_slave; +connect backup_slave,127.0.0.1,root,,test,$SLAVE_MYPORT,; +BACKUP STAGE START; +BACKUP STAGE BLOCK_COMMIT; +connection aux_slave; +ROLLBACK; +connection backup_slave; +BACKUP STAGE END; +connection slave; +include/diff_tables.inc [master:t1,slave:t1] +# MDEV-30423: dealock XA COMMIT vs BACKUP +# +# Normal XA COMMIT +connection slave; +include/stop_slave.inc +connection master; +connection aux_slave; +BEGIN; +INSERT INTO t1 VALUES (102); +connection master; +XA START '1'; +INSERT INTO t1 VALUES (101); +XA END '1'; +XA PREPARE '1'; +connection master1; +INSERT INTO t1 VALUES (102); +connection master; +XA COMMIT '1'; +include/save_master_gtid.inc +connection slave; +include/start_slave.inc +connection aux_slave; +# Xid '1' must be in the output: +XA RECOVER; +formatID gtrid_length bqual_length data +1 1 0 1 +connection backup_slave; +BACKUP STAGE START; +BACKUP STAGE BLOCK_COMMIT; +connection aux_slave; +ROLLBACK; +connection backup_slave; +BACKUP STAGE END; +connection slave; +include/sync_with_master_gtid.inc +include/stop_slave.inc +# +# Normal XA ROLLBACK +connection slave; +include/stop_slave.inc +Warnings: +Note 1255 Slave already has been stopped +connection master; +connection aux_slave; +BEGIN; +INSERT INTO t1 VALUES (104); +connection master; +XA START '1'; +INSERT INTO t1 VALUES (103); +XA END '1'; +XA PREPARE '1'; +connection master1; +INSERT INTO t1 VALUES (104); +connection master; +XA ROLLBACK '1'; +include/save_master_gtid.inc +connection slave; +include/start_slave.inc +connection aux_slave; +# Xid '1' must be in the output: +XA RECOVER; +formatID gtrid_length bqual_length data +1 1 0 1 +connection backup_slave; +BACKUP STAGE START; +BACKUP STAGE BLOCK_COMMIT; +connection aux_slave; +ROLLBACK; +connection backup_slave; +BACKUP STAGE END; +connection slave; +include/sync_with_master_gtid.inc +include/stop_slave.inc +# +# Errored out XA COMMIT +connection slave; +include/stop_slave.inc +Warnings: +Note 1255 Slave already has been stopped +connection master; +connection aux_slave; +BEGIN; +INSERT INTO t1 VALUES (106); +connection master; +XA START '1'; +INSERT INTO t1 VALUES (105); +XA END '1'; +XA PREPARE '1'; +connection master1; +INSERT INTO t1 VALUES (106); +connection master; +XA COMMIT '1'; +include/save_master_gtid.inc +connection slave; +SET @sav_innodb_lock_wait_timeout = @@global.innodb_lock_wait_timeout; +SET @sav_slave_transaction_retries = @@global.slave_transaction_retries; +SET @@global.innodb_lock_wait_timeout =1; +SET @@global.slave_transaction_retries=0; +include/start_slave.inc +connection aux_slave; +# Xid '1' must be in the output: +XA RECOVER; +formatID gtrid_length bqual_length data +1 1 0 1 +connection backup_slave; +BACKUP STAGE START; +BACKUP STAGE BLOCK_COMMIT; +connection aux_slave; +ROLLBACK; +connection backup_slave; +BACKUP STAGE END; +connection slave; +include/stop_slave.inc +SET @@global.innodb_lock_wait_timeout = @sav_innodb_lock_wait_timeout; +SET @@global.slave_transaction_retries= @sav_slave_transaction_retries; +connection slave; +include/start_slave.inc +include/sync_with_master_gtid.inc +# +# Errored out XA ROLLBACK +connection slave; +include/stop_slave.inc +connection master; +connection aux_slave; +BEGIN; +INSERT INTO t1 VALUES (108); +connection master; +XA START '1'; +INSERT INTO t1 VALUES (107); +XA END '1'; +XA PREPARE '1'; +connection master1; +INSERT INTO t1 VALUES (108); +connection master; +XA ROLLBACK '1'; +include/save_master_gtid.inc +connection slave; +SET @sav_innodb_lock_wait_timeout = @@global.innodb_lock_wait_timeout; +SET @sav_slave_transaction_retries = @@global.slave_transaction_retries; +SET @@global.innodb_lock_wait_timeout =1; +SET @@global.slave_transaction_retries=0; +include/start_slave.inc +connection aux_slave; +# Xid '1' must be in the output: +XA RECOVER; +formatID gtrid_length bqual_length data +1 1 0 1 +connection backup_slave; +BACKUP STAGE START; +BACKUP STAGE BLOCK_COMMIT; +connection aux_slave; +ROLLBACK; +connection backup_slave; +BACKUP STAGE END; +connection slave; +include/stop_slave.inc +SET @@global.innodb_lock_wait_timeout = @sav_innodb_lock_wait_timeout; +SET @@global.slave_transaction_retries= @sav_slave_transaction_retries; +connection slave; +include/start_slave.inc +include/sync_with_master_gtid.inc +connection slave; +include/stop_slave.inc +SET @@global.slave_parallel_threads= @old_parallel_threads; +SET @@global.slave_parallel_mode = @old_parallel_mode; +include/start_slave.inc +connection server_1; +DROP TABLE t1; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/parallel_backup_slave_binlog_off.result b/mysql-test/suite/rpl/r/parallel_backup_slave_binlog_off.result new file mode 100644 index 00000000000..111bc7fb76f --- /dev/null +++ b/mysql-test/suite/rpl/r/parallel_backup_slave_binlog_off.result @@ -0,0 +1,207 @@ +# Specialized --skip-log-bin slave version of parallel_backup test. +# MDEV-21953: deadlock between BACKUP STAGE BLOCK_COMMIT and parallel +# MDEV-30423: dealock XA COMMIT vs BACKUP +include/master-slave.inc +[connection master] +# +# MDEV-21953: deadlock between BACKUP STAGE BLOCK_COMMIT and parallel +# replication +# +connection master; +CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE = innodb; +INSERT INTO t1 VALUES(100); +connection slave; +call mtr.add_suppression("Deadlock found when trying to get lock"); +call mtr.add_suppression("Commit failed due to failure of an earlier commit"); +include/stop_slave.inc +SET @old_parallel_threads= @@GLOBAL.slave_parallel_threads; +SET @old_parallel_mode = @@GLOBAL.slave_parallel_mode; +SET @@global.slave_parallel_threads= 2; +SET @@global.slave_parallel_mode = 'optimistic'; +connection master; +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (2); +connect aux_slave,127.0.0.1,root,,test,$SLAVE_MYPORT,; +BEGIN; +INSERT INTO t1 VALUES (1); +connection slave; +include/start_slave.inc +connection aux_slave; +connect backup_slave,127.0.0.1,root,,test,$SLAVE_MYPORT,; +BACKUP STAGE START; +BACKUP STAGE BLOCK_COMMIT; +connection aux_slave; +ROLLBACK; +connection backup_slave; +BACKUP STAGE END; +connection slave; +include/diff_tables.inc [master:t1,slave:t1] +# MDEV-30423: dealock XA COMMIT vs BACKUP +# +# Normal XA COMMIT +connection slave; +include/stop_slave.inc +connection master; +connection aux_slave; +BEGIN; +INSERT INTO t1 VALUES (102); +connection master; +XA START '1'; +INSERT INTO t1 VALUES (101); +XA END '1'; +XA PREPARE '1'; +connection master1; +INSERT INTO t1 VALUES (102); +connection master; +XA COMMIT '1'; +include/save_master_gtid.inc +connection slave; +include/start_slave.inc +connection aux_slave; +# Xid '1' must be in the output: +XA RECOVER; +formatID gtrid_length bqual_length data +1 1 0 1 +connection backup_slave; +BACKUP STAGE START; +BACKUP STAGE BLOCK_COMMIT; +connection aux_slave; +ROLLBACK; +connection backup_slave; +BACKUP STAGE END; +connection slave; +include/sync_with_master_gtid.inc +include/stop_slave.inc +# +# Normal XA ROLLBACK +connection slave; +include/stop_slave.inc +Warnings: +Note 1255 Slave already has been stopped +connection master; +connection aux_slave; +BEGIN; +INSERT INTO t1 VALUES (104); +connection master; +XA START '1'; +INSERT INTO t1 VALUES (103); +XA END '1'; +XA PREPARE '1'; +connection master1; +INSERT INTO t1 VALUES (104); +connection master; +XA ROLLBACK '1'; +include/save_master_gtid.inc +connection slave; +include/start_slave.inc +connection aux_slave; +# Xid '1' must be in the output: +XA RECOVER; +formatID gtrid_length bqual_length data +1 1 0 1 +connection backup_slave; +BACKUP STAGE START; +BACKUP STAGE BLOCK_COMMIT; +connection aux_slave; +ROLLBACK; +connection backup_slave; +BACKUP STAGE END; +connection slave; +include/sync_with_master_gtid.inc +include/stop_slave.inc +# +# Errored out XA COMMIT +connection slave; +include/stop_slave.inc +Warnings: +Note 1255 Slave already has been stopped +connection master; +connection aux_slave; +BEGIN; +INSERT INTO t1 VALUES (106); +connection master; +XA START '1'; +INSERT INTO t1 VALUES (105); +XA END '1'; +XA PREPARE '1'; +connection master1; +INSERT INTO t1 VALUES (106); +connection master; +XA COMMIT '1'; +include/save_master_gtid.inc +connection slave; +SET @sav_innodb_lock_wait_timeout = @@global.innodb_lock_wait_timeout; +SET @sav_slave_transaction_retries = @@global.slave_transaction_retries; +SET @@global.innodb_lock_wait_timeout =1; +SET @@global.slave_transaction_retries=0; +include/start_slave.inc +connection aux_slave; +# Xid '1' must be in the output: +XA RECOVER; +formatID gtrid_length bqual_length data +1 1 0 1 +connection backup_slave; +BACKUP STAGE START; +BACKUP STAGE BLOCK_COMMIT; +connection aux_slave; +ROLLBACK; +connection backup_slave; +BACKUP STAGE END; +connection slave; +include/stop_slave.inc +SET @@global.innodb_lock_wait_timeout = @sav_innodb_lock_wait_timeout; +SET @@global.slave_transaction_retries= @sav_slave_transaction_retries; +connection slave; +include/start_slave.inc +include/sync_with_master_gtid.inc +# +# Errored out XA ROLLBACK +connection slave; +include/stop_slave.inc +connection master; +connection aux_slave; +BEGIN; +INSERT INTO t1 VALUES (108); +connection master; +XA START '1'; +INSERT INTO t1 VALUES (107); +XA END '1'; +XA PREPARE '1'; +connection master1; +INSERT INTO t1 VALUES (108); +connection master; +XA ROLLBACK '1'; +include/save_master_gtid.inc +connection slave; +SET @sav_innodb_lock_wait_timeout = @@global.innodb_lock_wait_timeout; +SET @sav_slave_transaction_retries = @@global.slave_transaction_retries; +SET @@global.innodb_lock_wait_timeout =1; +SET @@global.slave_transaction_retries=0; +include/start_slave.inc +connection aux_slave; +# Xid '1' must be in the output: +XA RECOVER; +formatID gtrid_length bqual_length data +1 1 0 1 +connection backup_slave; +BACKUP STAGE START; +BACKUP STAGE BLOCK_COMMIT; +connection aux_slave; +ROLLBACK; +connection backup_slave; +BACKUP STAGE END; +connection slave; +include/stop_slave.inc +SET @@global.innodb_lock_wait_timeout = @sav_innodb_lock_wait_timeout; +SET @@global.slave_transaction_retries= @sav_slave_transaction_retries; +connection slave; +include/start_slave.inc +include/sync_with_master_gtid.inc +connection slave; +include/stop_slave.inc +SET @@global.slave_parallel_threads= @old_parallel_threads; +SET @@global.slave_parallel_mode = @old_parallel_mode; +include/start_slave.inc +connection server_1; +DROP TABLE t1; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_binlog_dump_slave_gtid_state_info.result b/mysql-test/suite/rpl/r/rpl_binlog_dump_slave_gtid_state_info.result index 96b14bc1d9c..b913a49b509 100644 --- a/mysql-test/suite/rpl/r/rpl_binlog_dump_slave_gtid_state_info.result +++ b/mysql-test/suite/rpl/r/rpl_binlog_dump_slave_gtid_state_info.result @@ -4,9 +4,7 @@ connection master; SET GLOBAL LOG_WARNINGS=2; connection slave; include/stop_slave.inc -CHANGE MASTER TO MASTER_USE_GTID=current_pos; -Warnings: -Warning 1681 'master_use_gtid=current_pos' is deprecated and will be removed in a future release. Please use master_demote_to_slave=1 instead +CHANGE MASTER TO MASTER_USE_GTID=slave_pos; include/start_slave.inc connection master; "Test Case 1: Start binlog_dump to slave_server(#), pos(master-bin.000001, ###), using_gtid(1), gtid('')" diff --git a/mysql-test/suite/rpl/r/rpl_delayed_parallel_slave_sbm.result b/mysql-test/suite/rpl/r/rpl_delayed_parallel_slave_sbm.result new file mode 100644 index 00000000000..b00a8a5e1d7 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_delayed_parallel_slave_sbm.result @@ -0,0 +1,72 @@ +include/master-slave.inc +[connection master] +connection slave; +include/stop_slave.inc +change master to master_delay=3, master_use_gtid=Slave_Pos; +set @@GLOBAL.slave_parallel_threads=2; +include/start_slave.inc +connection master; +create table t1 (a int); +include/sync_slave_sql_with_master.inc +# +# Pt 1) Ensure SBM is updated immediately upon arrival of the next event +# Lock t1 on slave so the first received transaction does not complete/commit +connection slave; +LOCK TABLES t1 WRITE; +connection master; +# Sleep 2 to allow a buffer between events for SBM check +insert into t1 values (0); +include/save_master_gtid.inc +connection slave; +# Waiting for transaction to arrive on slave and begin SQL Delay.. +# Validating SBM is updated on event arrival.. +# ..done +connection slave; +UNLOCK TABLES; +include/sync_with_master_gtid.inc +# +# Pt 2) If the SQL thread has not entered an idle state, ensure +# following events do not update SBM +# Stop slave IO thread so it receives both events together on restart +connection slave; +include/stop_slave_io.inc +connection master; +# Sleep 2 to allow a buffer between events for SBM check +insert into t1 values (1); +# Sleep 3 to create gap between events +insert into t1 values (2); +include/save_master_pos.inc +connection slave; +LOCK TABLES t1 WRITE; +SET @@global.debug_dbug="+d,pause_sql_thread_on_next_event"; +START SLAVE IO_THREAD; +# Before we start processing the events, we ensure both transactions +# were written into the relay log. Otherwise, if the IO thread takes too +# long to queue the events, the sql thread can think it has caught up +# too quickly. +SET DEBUG_SYNC='now WAIT_FOR paused_on_event'; +include/sync_io_with_master.inc +SET @@global.debug_dbug="-d,pause_sql_thread_on_next_event"; +SET DEBUG_SYNC='now SIGNAL sql_thread_continue'; +# Wait for first transaction to complete SQL delay and begin execution.. +# Validate SBM calculation doesn't use the second transaction because SQL thread shouldn't have gone idle.. +# ..and that SBM wasn't calculated using prior committed transactions +# ..done +connection slave; +UNLOCK TABLES; +# +# Cleanup +# Reset master_delay +include/stop_slave.inc +CHANGE MASTER TO master_delay=0; +set @@GLOBAL.slave_parallel_threads=4; +SET @@global.debug_dbug=""; +SET DEBUG_SYNC='RESET'; +include/start_slave.inc +connection master; +DROP TABLE t1; +include/save_master_gtid.inc +connection slave; +include/sync_with_master_gtid.inc +include/rpl_end.inc +# End of rpl_delayed_parallel_slave_sbm.test diff --git a/mysql-test/suite/rpl/r/rpl_gtid_crash.result b/mysql-test/suite/rpl/r/rpl_gtid_crash.result index fc8e69af861..2f764fe4a0d 100644 --- a/mysql-test/suite/rpl/r/rpl_gtid_crash.result +++ b/mysql-test/suite/rpl/r/rpl_gtid_crash.result @@ -14,9 +14,7 @@ call mtr.add_suppression('Master command COM_REGISTER_SLAVE failed: failed regis SET sql_log_bin=1; include/stop_slave.inc CHANGE MASTER TO master_host = '127.0.0.1', master_port = MASTER_PORT, -MASTER_USE_GTID=CURRENT_POS; -Warnings: -Warning 1681 'master_use_gtid=current_pos' is deprecated and will be removed in a future release. Please use master_demote_to_slave=1 instead +MASTER_USE_GTID=SLAVE_POS; connection server_1; INSERT INTO t1 VALUES (2,1); INSERT INTO t1 VALUES (3,1); diff --git a/mysql-test/suite/rpl/r/rpl_gtid_excess_initial_delay.result b/mysql-test/suite/rpl/r/rpl_gtid_excess_initial_delay.result index 302bf182ac2..4b2652ece8f 100644 --- a/mysql-test/suite/rpl/r/rpl_gtid_excess_initial_delay.result +++ b/mysql-test/suite/rpl/r/rpl_gtid_excess_initial_delay.result @@ -3,9 +3,7 @@ include/master-slave.inc CREATE TABLE t1 (i INT); connection slave; include/stop_slave.inc -CHANGE MASTER TO MASTER_USE_GTID= current_pos, MASTER_DELAY= 10; -Warnings: -Warning 1681 'master_use_gtid=current_pos' is deprecated and will be removed in a future release. Please use master_demote_to_slave=1 instead +CHANGE MASTER TO MASTER_DELAY= 10; include/start_slave.inc connection master; INSERT INTO t1 VALUES (1); @@ -16,7 +14,7 @@ connection slave; # Asserted this: One row shoule be found in table t1. "======= Clean up ========" STOP SLAVE; -CHANGE MASTER TO MASTER_USE_GTID=no, MASTER_DELAY=0; +CHANGE MASTER TO MASTER_DELAY=0; START SLAVE; connection master; DROP TABLE t1; diff --git a/mysql-test/suite/rpl/r/rpl_gtid_master_promote.result b/mysql-test/suite/rpl/r/rpl_gtid_master_promote.result index 19229a3c6c2..92430be199a 100644 --- a/mysql-test/suite/rpl/r/rpl_gtid_master_promote.result +++ b/mysql-test/suite/rpl/r/rpl_gtid_master_promote.result @@ -12,24 +12,12 @@ RETURN s; END| connection server_2; include/stop_slave.inc -CHANGE MASTER TO master_use_gtid=current_pos; -Warnings: -Warning 1681 'master_use_gtid=current_pos' is deprecated and will be removed in a future release. Please use master_demote_to_slave=1 instead connection server_3; include/stop_slave.inc -CHANGE MASTER TO master_use_gtid=current_pos; -Warnings: -Warning 1681 'master_use_gtid=current_pos' is deprecated and will be removed in a future release. Please use master_demote_to_slave=1 instead connection server_4; include/stop_slave.inc -CHANGE MASTER TO master_use_gtid=current_pos; -Warnings: -Warning 1681 'master_use_gtid=current_pos' is deprecated and will be removed in a future release. Please use master_demote_to_slave=1 instead connection server_5; include/stop_slave.inc -CHANGE MASTER TO master_use_gtid=current_pos; -Warnings: -Warning 1681 'master_use_gtid=current_pos' is deprecated and will be removed in a future release. Please use master_demote_to_slave=1 instead connection server_1; SET gtid_domain_id= 1; CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; @@ -347,9 +335,7 @@ a b *** Now let the old master join up as slave. *** connection server_1; CHANGE MASTER TO master_host = '127.0.0.1', master_port = SERVER_MYPORT_2, -master_user = "root", master_use_gtid = current_pos; -Warnings: -Warning 1681 'master_use_gtid=current_pos' is deprecated and will be removed in a future release. Please use master_demote_to_slave=1 instead +master_user = "root", master_use_gtid = slave_pos, master_demote_to_slave=1; include/start_slave.inc SELECT * FROM t1 ORDER BY a; a @@ -387,8 +373,6 @@ a b connection server_1; include/stop_slave.inc RESET SLAVE ALL; -Warnings: -Note 4190 RESET SLAVE is implicitly changing the value of 'Using_Gtid' from 'Current_Pos' to 'Slave_Pos' connection server_2; CHANGE MASTER TO master_host = '127.0.0.1', master_port = SERVER_MYPORT_1; include/start_slave.inc diff --git a/mysql-test/suite/rpl/r/rpl_gtid_mdev4473.result b/mysql-test/suite/rpl/r/rpl_gtid_mdev4473.result index e61df488d62..9524dafb524 100644 --- a/mysql-test/suite/rpl/r/rpl_gtid_mdev4473.result +++ b/mysql-test/suite/rpl/r/rpl_gtid_mdev4473.result @@ -12,9 +12,7 @@ include/wait_for_slave_to_stop.inc reset slave all; connection server_1; CHANGE MASTER TO master_host = '127.0.0.1', master_port = SERVER_MYPORT_2, -master_user='root', MASTER_USE_GTID=CURRENT_POS; -Warnings: -Warning 1681 'master_use_gtid=current_pos' is deprecated and will be removed in a future release. Please use master_demote_to_slave=1 instead +master_user='root', MASTER_USE_GTID=SLAVE_POS, master_demote_to_slave=1; include/start_slave.inc include/wait_for_slave_to_start.inc connection server_2; @@ -24,9 +22,7 @@ insert into t1 values (4); flush logs; connection server_3; CHANGE MASTER TO master_host = '127.0.0.1', master_port = SERVER_MYPORT_2, -MASTER_USE_GTID=CURRENT_POS; -Warnings: -Warning 1681 'master_use_gtid=current_pos' is deprecated and will be removed in a future release. Please use master_demote_to_slave=1 instead +MASTER_USE_GTID=SLAVE_POS; include/start_slave.inc select * from t1 order by n; n @@ -57,20 +53,14 @@ connection server_1; include/stop_slave.inc include/wait_for_slave_to_stop.inc reset slave all; -Warnings: -Note 4190 RESET SLAVE is implicitly changing the value of 'Using_Gtid' from 'Current_Pos' to 'Slave_Pos' connection server_2; CHANGE MASTER TO master_host = '127.0.0.1', master_port = SERVER_MYPORT_1, -master_user = 'root', MASTER_USE_GTID=CURRENT_POS; -Warnings: -Warning 1681 'master_use_gtid=current_pos' is deprecated and will be removed in a future release. Please use master_demote_to_slave=1 instead +master_user = 'root', MASTER_USE_GTID=SLAVE_POS, master_demote_to_slave=1; include/start_slave.inc connection server_3; include/stop_slave.inc CHANGE MASTER TO master_host = '127.0.0.1', master_port = SERVER_MYPORT_1, -MASTER_USE_GTID=CURRENT_POS; -Warnings: -Warning 1681 'master_use_gtid=current_pos' is deprecated and will be removed in a future release. Please use master_demote_to_slave=1 instead +MASTER_USE_GTID=SLAVE_POS; include/start_slave.inc connection server_1; drop table t1; diff --git a/mysql-test/suite/rpl/r/rpl_gtid_misc.result b/mysql-test/suite/rpl/r/rpl_gtid_misc.result index ad77aa2350e..c4a64a13753 100644 --- a/mysql-test/suite/rpl/r/rpl_gtid_misc.result +++ b/mysql-test/suite/rpl/r/rpl_gtid_misc.result @@ -9,9 +9,6 @@ include/stop_slave.inc SET sql_log_bin= 0; INSERT INTO t1 VALUES (1); SET sql_log_bin= 1; -CHANGE MASTER TO master_use_gtid= current_pos; -Warnings: -Warning 1681 'master_use_gtid=current_pos' is deprecated and will be removed in a future release. Please use master_demote_to_slave=1 instead connection master; CREATE TEMPORARY TABLE t2 LIKE t1; INSERT INTO t2 VALUE (1); diff --git a/mysql-test/suite/rpl/r/rpl_gtid_nobinlog.result b/mysql-test/suite/rpl/r/rpl_gtid_nobinlog.result index de5f30696fe..8b896560d39 100644 --- a/mysql-test/suite/rpl/r/rpl_gtid_nobinlog.result +++ b/mysql-test/suite/rpl/r/rpl_gtid_nobinlog.result @@ -21,9 +21,7 @@ a b include/stop_slave.inc connection server_1; CHANGE MASTER TO master_host = '127.0.0.1', master_port = SLAVE_PORT, -master_user = 'root', master_use_gtid = current_pos; -Warnings: -Warning 1681 'master_use_gtid=current_pos' is deprecated and will be removed in a future release. Please use master_demote_to_slave=1 instead +master_user = 'root'; START SLAVE; include/wait_for_slave_to_start.inc connection server_2; @@ -48,16 +46,11 @@ a b 4 2 include/stop_slave.inc RESET SLAVE; -Warnings: -Note 4190 RESET SLAVE is implicitly changing the value of 'Using_Gtid' from 'Current_Pos' to 'Slave_Pos' INSERT INTO t1 VALUES (5, 1); INSERT INTO t1 VALUES (6, 1); include/save_master_gtid.inc connection server_2; -CHANGE MASTER TO master_host = '127.0.0.1', master_port = MASTER_PORT, -master_use_gtid = current_pos; -Warnings: -Warning 1681 'master_use_gtid=current_pos' is deprecated and will be removed in a future release. Please use master_demote_to_slave=1 instead +CHANGE MASTER TO master_host = '127.0.0.1', master_port = MASTER_PORT; START SLAVE; include/sync_with_master_gtid.inc SELECT * FROM t1 ORDER BY a; diff --git a/mysql-test/suite/rpl/r/rpl_gtid_reconnect.result b/mysql-test/suite/rpl/r/rpl_gtid_reconnect.result index 6282e116733..f9d6cd3b743 100644 --- a/mysql-test/suite/rpl/r/rpl_gtid_reconnect.result +++ b/mysql-test/suite/rpl/r/rpl_gtid_reconnect.result @@ -1,10 +1,4 @@ include/rpl_init.inc [topology=1->2] -connection server_2; -include/stop_slave.inc -CHANGE MASTER TO master_use_gtid= current_pos; -Warnings: -Warning 1681 'master_use_gtid=current_pos' is deprecated and will be removed in a future release. Please use master_demote_to_slave=1 instead -include/start_slave.inc connection server_1; CREATE TABLE t1 (a INT); FLUSH LOGS; diff --git a/mysql-test/suite/rpl/r/rpl_gtid_sort.result b/mysql-test/suite/rpl/r/rpl_gtid_sort.result index bb1fb28e0e9..46a65e7d865 100644 --- a/mysql-test/suite/rpl/r/rpl_gtid_sort.result +++ b/mysql-test/suite/rpl/r/rpl_gtid_sort.result @@ -58,9 +58,7 @@ SHOW VARIABLES LIKE 'gtid_binlog_state'; Variable_name Value gtid_binlog_state CHANGE MASTER TO master_host = '127.0.0.1', master_port = MASTER_PORT, -MASTER_USE_GTID=CURRENT_POS; -Warnings: -Warning 1681 'master_use_gtid=current_pos' is deprecated and will be removed in a future release. Please use master_demote_to_slave=1 instead +MASTER_USE_GTID=SLAVE_POS; include/start_slave.inc SHOW VARIABLES LIKE 'gtid_binlog_pos'; Variable_name Value diff --git a/mysql-test/suite/rpl/r/rpl_gtid_startpos.result b/mysql-test/suite/rpl/r/rpl_gtid_startpos.result index 846854f8668..e38eddcc97a 100644 --- a/mysql-test/suite/rpl/r/rpl_gtid_startpos.result +++ b/mysql-test/suite/rpl/r/rpl_gtid_startpos.result @@ -11,9 +11,7 @@ CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; connection server_2; SET GLOBAL gtid_slave_pos=""; CHANGE MASTER TO master_host = '127.0.0.1', master_port = MASTER_PORT, -MASTER_USE_GTID=CURRENT_POS; -Warnings: -Warning 1681 'master_use_gtid=current_pos' is deprecated and will be removed in a future release. Please use master_demote_to_slave=1 instead +MASTER_USE_GTID=SLAVE_POS; include/start_slave.inc SELECT * FROM t1; a @@ -35,9 +33,7 @@ master-bin.000003 # connection server_2; SET GLOBAL gtid_slave_pos=""; CHANGE MASTER TO master_host = '127.0.0.1', master_port = MASTER_PORT, -MASTER_USE_GTID=CURRENT_POS; -Warnings: -Warning 1681 'master_use_gtid=current_pos' is deprecated and will be removed in a future release. Please use master_demote_to_slave=1 instead +MASTER_USE_GTID=SLAVE_POS; START SLAVE; include/wait_for_slave_io_error.inc [errno=1236] include/stop_slave.inc @@ -59,9 +55,7 @@ include/save_master_gtid.inc connection server_2; SET GLOBAL gtid_slave_pos='0-1-3'; CHANGE MASTER TO master_host = '127.0.0.1', master_port = MASTER_PORT, -MASTER_USE_GTID=CURRENT_POS; -Warnings: -Warning 1681 'master_use_gtid=current_pos' is deprecated and will be removed in a future release. Please use master_demote_to_slave=1 instead +MASTER_USE_GTID=SLAVE_POS; include/start_slave.inc include/sync_with_master_gtid.inc SELECT * FROM t1 ORDER by a; @@ -96,16 +90,12 @@ connection server_2; connection server_2; include/stop_slave.inc RESET SLAVE ALL; -Warnings: -Note 4190 RESET SLAVE is implicitly changing the value of 'Using_Gtid' from 'Current_Pos' to 'Slave_Pos' RESET MASTER; connection server_1; RESET MASTER; connection server_2; SET GLOBAL gtid_slave_pos=''; -CHANGE MASTER TO master_host='127.0.0.1', master_port=MASTER_PORT, master_user='root', master_use_gtid=current_pos; -Warnings: -Warning 1681 'master_use_gtid=current_pos' is deprecated and will be removed in a future release. Please use master_demote_to_slave=1 instead +CHANGE MASTER TO master_host='127.0.0.1', master_port=MASTER_PORT, master_user='root', master_use_gtid=slave_pos; include/start_slave.inc connection server_1; CREATE TABLE t1 (a INT PRIMARY KEY); @@ -129,8 +119,6 @@ connection server_2; include/stop_slave.inc DROP TABLE t1; RESET SLAVE; -Warnings: -Note 4190 RESET SLAVE is implicitly changing the value of 'Using_Gtid' from 'Current_Pos' to 'Slave_Pos' SET GLOBAL gtid_slave_pos=""; Warnings: Warning 1948 Specified value for @@gtid_slave_pos contains no value for replication domain 0. This conflicts with the binary log which contains GTID 0-2-4. If MASTER_GTID_POS=CURRENT_POS is used, the binlog position will override the new value of @@gtid_slave_pos @@ -162,9 +150,7 @@ Using_Gtid = 'No' START SLAVE; include/wait_for_slave_sql_error.inc [errno=1050] STOP SLAVE IO_THREAD; -CHANGE MASTER TO MASTER_USE_GTID=CURRENT_POS; -Warnings: -Warning 1681 'master_use_gtid=current_pos' is deprecated and will be removed in a future release. Please use master_demote_to_slave=1 instead +CHANGE MASTER TO MASTER_USE_GTID=SLAVE_POS; include/start_slave.inc connection server_1; INSERT INTO t1 VALUES(3); @@ -223,9 +209,9 @@ include/sync_with_master_gtid.inc SELECT * FROM t1; a 10 -SELECT 'Current_Pos' AS Using_Gtid; +SELECT 'Slave_Pos' AS Using_Gtid; Using_Gtid -Current_Pos +Slave_Pos SELECT '0-1-2' AS Gtid_Slave_Pos; Gtid_Slave_Pos 0-1-2 @@ -252,8 +238,6 @@ connection server_2; include/stop_slave.inc DROP TABLE t1; RESET SLAVE ALL; -Warnings: -Note 4190 RESET SLAVE is implicitly changing the value of 'Using_Gtid' from 'Current_Pos' to 'Slave_Pos' CHANGE MASTER TO MASTER_USE_GTID=NO; RESET MASTER; SET GLOBAL gtid_slave_pos= ""; diff --git a/mysql-test/suite/rpl/r/rpl_gtid_stop_start.result b/mysql-test/suite/rpl/r/rpl_gtid_stop_start.result index 2bdfcb14123..ae0050c353a 100644 --- a/mysql-test/suite/rpl/r/rpl_gtid_stop_start.result +++ b/mysql-test/suite/rpl/r/rpl_gtid_stop_start.result @@ -7,9 +7,6 @@ connection server_2; include/stop_slave.inc Master_Log_File = 'master-bin.000001' Using_Gtid = 'Slave_Pos' -CHANGE MASTER TO master_use_gtid=current_pos; -Warnings: -Warning 1681 'master_use_gtid=current_pos' is deprecated and will be removed in a future release. Please use master_demote_to_slave=1 instead FLUSH LOGS; connection server_1; FLUSH LOGS; diff --git a/mysql-test/suite/rpl/r/rpl_gtid_until.result b/mysql-test/suite/rpl/r/rpl_gtid_until.result index 85b066608e5..029d2367e50 100644 --- a/mysql-test/suite/rpl/r/rpl_gtid_until.result +++ b/mysql-test/suite/rpl/r/rpl_gtid_until.result @@ -29,9 +29,7 @@ ERROR HY000: Slave is already running include/stop_slave_io.inc START SLAVE UNTIL master_gtid_pos = ""; ERROR HY000: START SLAVE UNTIL master_gtid_pos requires that slave is using GTID -CHANGE MASTER TO master_use_gtid=current_pos; -Warnings: -Warning 1681 'master_use_gtid=current_pos' is deprecated and will be removed in a future release. Please use master_demote_to_slave=1 instead +CHANGE MASTER TO master_use_gtid=slave_pos; connection server_1; CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; INSERT INTO t1 VALUES(1); @@ -60,7 +58,7 @@ a include/stop_slave.inc START SLAVE UNTIL master_gtid_pos = "1-10-100,2-20-200,0-1-300"; include/wait_for_slave_to_start.inc -Using_Gtid = 'Current_Pos' +Using_Gtid = 'Slave_Pos' Until_Condition = 'Gtid' connection server_1; INSERT INTO t1 VALUES (3); @@ -205,8 +203,6 @@ include/start_slave.inc connection server_2; include/stop_slave.inc RESET SLAVE ALL; -Warnings: -Note 4190 RESET SLAVE is implicitly changing the value of 'Using_Gtid' from 'Current_Pos' to 'Slave_Pos' RESET MASTER; SET GLOBAL gtid_slave_pos=''; connection server_1; @@ -219,9 +215,7 @@ RESET MASTER; INSERT INTO t1 VALUES (10); connection server_2; CHANGE MASTER TO master_host = '127.0.0.1', master_port = SERVER_MYPORT_1, -master_user = "root", master_use_gtid = current_pos; -Warnings: -Warning 1681 'master_use_gtid=current_pos' is deprecated and will be removed in a future release. Please use master_demote_to_slave=1 instead +master_user = "root", master_use_gtid = slave_pos; START SLAVE UNTIL master_gtid_pos = '0-1-2'; include/wait_for_slave_to_start.inc connection server_1; diff --git a/mysql-test/suite/rpl/r/rpl_mdev6386.result b/mysql-test/suite/rpl/r/rpl_mdev6386.result index d62543d8488..63ce594a604 100644 --- a/mysql-test/suite/rpl/r/rpl_mdev6386.result +++ b/mysql-test/suite/rpl/r/rpl_mdev6386.result @@ -10,9 +10,6 @@ SET sql_log_bin= 0; CALL mtr.add_suppression("Commit failed due to failure of an earlier commit on which this one depends"); INSERT INTO t1 VALUES (1, 2); SET sql_log_bin= 1; -CHANGE MASTER TO master_use_gtid= current_pos; -Warnings: -Warning 1681 'master_use_gtid=current_pos' is deprecated and will be removed in a future release. Please use master_demote_to_slave=1 instead Contents on slave before: SELECT * FROM t1 ORDER BY a; a b diff --git a/mysql-test/suite/rpl/r/rpl_parallel_analyze.result b/mysql-test/suite/rpl/r/rpl_parallel_analyze.result new file mode 100644 index 00000000000..c0a2abbc1ee --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_parallel_analyze.result @@ -0,0 +1,76 @@ +include/master-slave.inc +[connection master] +# Initialize +connection slave; +ALTER TABLE mysql.gtid_slave_pos ENGINE=InnoDB; +# Setup data +connection master; +CREATE TABLE t1 (a int PRIMARY KEY) ENGINE=InnoDB; +CREATE TABLE ta (a int); +include/save_master_gtid.inc +connection slave; +include/sync_with_master_gtid.inc +connection master; +SET sql_log_bin=0; +CREATE FUNCTION foo(x INT, d1 VARCHAR(500), d2 VARCHAR(500)) +RETURNS INT DETERMINISTIC +BEGIN +RETURN x; +END +|| +SET sql_log_bin=1; +connection slave; +SET sql_log_bin=0; +CREATE FUNCTION foo(x INT, d1 VARCHAR(500), d2 VARCHAR(500)) +RETURNS INT DETERMINISTIC +BEGIN +IF d1 != '' THEN +SET debug_sync = d1; +END IF; +IF d2 != '' THEN +SET debug_sync = d2; +END IF; +RETURN x; +END +|| +SET sql_log_bin=1; +include/stop_slave.inc +SET @old_parallel_threads =@@GLOBAL.slave_parallel_threads; +SET @old_parallel_mode =@@GLOBAL.slave_parallel_mode; +SET @old_gtid_strict_mode =@@GLOBAL.gtid_strict_mode; +SET GLOBAL slave_parallel_threads=10; +SET GLOBAL slave_parallel_mode=conservative; +SET GLOBAL gtid_strict_mode=ON; +include/start_slave.inc +connection master; +SET @old_format= @@SESSION.binlog_format; +SET binlog_format=statement; +INSERT INTO t1 VALUES (foo(1, 'rpl_parallel_after_mark_start_commit WAIT_FOR sig_go', '')); +ANALYZE TABLE ta; +Table Op Msg_type Msg_text +test.ta analyze status Engine-independent statistics collected +test.ta analyze status Table is already up to date +include/save_master_gtid.inc +connection slave; +SELECT info FROM information_schema.processlist WHERE state = "Waiting for prior transaction to commit"; +info +ANALYZE TABLE ta +set @@debug_sync="now signal sig_go"; +include/sync_with_master_gtid.inc +# Cleanup +connection master; +DROP TABLE t1,ta; +connection slave; +SET DEBUG_SYNC='RESET'; +connection master; +SET DEBUG_SYNC='RESET'; +DROP FUNCTION foo; +include/save_master_gtid.inc +connection slave; +include/sync_with_master_gtid.inc +include/stop_slave.inc +SET @@GLOBAL.slave_parallel_threads=@old_parallel_threads; +SET @@GLOBAL.slave_parallel_mode =@old_parallel_mode; +SET @@GLOBAL.gtid_strict_mode =@old_gtid_strict_mode; +include/start_slave.inc +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_parallel_mdev6589.result b/mysql-test/suite/rpl/r/rpl_parallel_mdev6589.result index ed297d10f2a..2e5ac97bf4d 100644 --- a/mysql-test/suite/rpl/r/rpl_parallel_mdev6589.result +++ b/mysql-test/suite/rpl/r/rpl_parallel_mdev6589.result @@ -4,9 +4,6 @@ connection server_2; SET @old_parallel_threads=@@GLOBAL.slave_parallel_threads; include/stop_slave.inc SET GLOBAL slave_parallel_threads=10; -CHANGE MASTER TO master_use_gtid=current_pos; -Warnings: -Warning 1681 'master_use_gtid=current_pos' is deprecated and will be removed in a future release. Please use master_demote_to_slave=1 instead include/start_slave.inc *** MDEV-6589: Incorrect relay log start position when restarting SQL thread after error in parallel replication *** connection server_1; diff --git a/mysql-test/suite/rpl/r/rpl_parallel_multilevel2.result b/mysql-test/suite/rpl/r/rpl_parallel_multilevel2.result index 13508815877..ba59650983b 100644 --- a/mysql-test/suite/rpl/r/rpl_parallel_multilevel2.result +++ b/mysql-test/suite/rpl/r/rpl_parallel_multilevel2.result @@ -17,16 +17,10 @@ SET GLOBAL binlog_commit_wait_usec=2000000; SET @old_updates= @@GLOBAL.binlog_direct_non_transactional_updates; SET GLOBAL binlog_direct_non_transactional_updates=OFF; SET SESSION binlog_direct_non_transactional_updates=OFF; -CHANGE MASTER TO master_use_gtid=current_pos; -Warnings: -Warning 1681 'master_use_gtid=current_pos' is deprecated and will be removed in a future release. Please use master_demote_to_slave=1 instead connection server_3; SET @old_parallel_threads=@@GLOBAL.slave_parallel_threads; include/stop_slave.inc SET GLOBAL slave_parallel_threads=10; -CHANGE MASTER TO master_use_gtid=current_pos; -Warnings: -Warning 1681 'master_use_gtid=current_pos' is deprecated and will be removed in a future release. Please use master_demote_to_slave=1 instead connection server_1; BEGIN; CREATE TEMPORARY TABLE t2 (a INT PRIMARY KEY) ENGINE=MEMORY; diff --git a/mysql-test/suite/rpl/r/rpl_parallel_temptable.result b/mysql-test/suite/rpl/r/rpl_parallel_temptable.result index 9519bffaa27..e9bff03bd41 100644 --- a/mysql-test/suite/rpl/r/rpl_parallel_temptable.result +++ b/mysql-test/suite/rpl/r/rpl_parallel_temptable.result @@ -4,9 +4,6 @@ connection server_2; SET @old_parallel_threads=@@GLOBAL.slave_parallel_threads; include/stop_slave.inc SET GLOBAL slave_parallel_threads=5; -CHANGE MASTER TO master_use_gtid= current_pos; -Warnings: -Warning 1681 'master_use_gtid=current_pos' is deprecated and will be removed in a future release. Please use master_demote_to_slave=1 instead include/start_slave.inc connection server_1; CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(100) CHARACTER SET utf8); diff --git a/mysql-test/suite/rpl/r/rpl_start_alter_mysqlbinlog_1.result b/mysql-test/suite/rpl/r/rpl_start_alter_mysqlbinlog_1.result index 2c3b694c45d..bf9d9be0ec6 100644 --- a/mysql-test/suite/rpl/r/rpl_start_alter_mysqlbinlog_1.result +++ b/mysql-test/suite/rpl/r/rpl_start_alter_mysqlbinlog_1.result @@ -4,9 +4,6 @@ connection master; set global binlog_alter_two_phase=true; connection slave; include/stop_slave.inc -change master to master_use_gtid= current_pos; -Warnings: -Warning 1681 'master_use_gtid=current_pos' is deprecated and will be removed in a future release. Please use master_demote_to_slave=1 instead set global gtid_strict_mode=1; # Legacy Master Slave connect master_node,127.0.0.1,root,,$db_name, $M_port; diff --git a/mysql-test/suite/rpl/r/rpl_upgrade_master_info.result b/mysql-test/suite/rpl/r/rpl_upgrade_master_info.result index e58ea5eb0f2..1babddf9a6e 100644 --- a/mysql-test/suite/rpl/r/rpl_upgrade_master_info.result +++ b/mysql-test/suite/rpl/r/rpl_upgrade_master_info.result @@ -3,9 +3,6 @@ include/master-slave.inc *** MDEV-9383: Server fails to read master.info after upgrade 10.0 -> 10.1 *** connection slave; include/stop_slave.inc -CHANGE MASTER TO master_use_gtid=CURRENT_POS; -Warnings: -Warning 1681 'master_use_gtid=current_pos' is deprecated and will be removed in a future release. Please use master_demote_to_slave=1 instead include/rpl_stop_server.inc [server_number=2] include/rpl_start_server.inc [server_number=2] connection master; diff --git a/mysql-test/suite/rpl/t/parallel_backup.test b/mysql-test/suite/rpl/t/parallel_backup.test index 964e2a30309..4f5ffa9f291 100644 --- a/mysql-test/suite/rpl/t/parallel_backup.test +++ b/mysql-test/suite/rpl/t/parallel_backup.test @@ -15,6 +15,9 @@ CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE = innodb; INSERT INTO t1 VALUES(100); --sync_slave_with_master +call mtr.add_suppression("Deadlock found when trying to get lock"); +call mtr.add_suppression("Commit failed due to failure of an earlier commit"); + --source include/stop_slave.inc SET @old_parallel_threads= @@GLOBAL.slave_parallel_threads; SET @old_parallel_mode = @@GLOBAL.slave_parallel_mode; @@ -64,6 +67,32 @@ BACKUP STAGE END; --let $diff_tables= master:t1,slave:t1 --source include/diff_tables.inc +# +--echo # MDEV-30423: dealock XA COMMIT vs BACKUP +# Prove XA "COMPLETE" 'xid' does not dealock similary to the normal trx case. +# The slave binlog group commit leader is blocked by a local trx like in +# the above normal trx case. +# [Notice a reuse of t1,aux_conn from above.] +# +--let $complete = COMMIT +--source parallel_backup_xa.inc +--let $complete = ROLLBACK +--source parallel_backup_xa.inc + +--let $slave_ooo_error = 1 +--let $complete = COMMIT +--source parallel_backup_xa.inc +--connection slave +--source include/start_slave.inc +--source include/sync_with_master_gtid.inc + +--let $slave_ooo_error = 1 +--let $complete = ROLLBACK +--source parallel_backup_xa.inc +--connection slave +--source include/start_slave.inc +--source include/sync_with_master_gtid.inc + # Clean up. --connection slave diff --git a/mysql-test/suite/rpl/t/parallel_backup_lsu_off-slave.opt b/mysql-test/suite/rpl/t/parallel_backup_lsu_off-slave.opt new file mode 100644 index 00000000000..2f2a9c436fc --- /dev/null +++ b/mysql-test/suite/rpl/t/parallel_backup_lsu_off-slave.opt @@ -0,0 +1,2 @@ +--log-slave-updates=0 + diff --git a/mysql-test/suite/rpl/t/parallel_backup_lsu_off.test b/mysql-test/suite/rpl/t/parallel_backup_lsu_off.test new file mode 100644 index 00000000000..8e2663077b2 --- /dev/null +++ b/mysql-test/suite/rpl/t/parallel_backup_lsu_off.test @@ -0,0 +1,7 @@ +# +--echo # Specialized --log-slave-updates = 0 version of parallel_backup test. +# +--echo # MDEV-21953: deadlock between BACKUP STAGE BLOCK_COMMIT and parallel +--echo # MDEV-30423: dealock XA COMMIT vs BACKUP +--let $rpl_skip_reset_master_and_slave = 1 +--source parallel_backup.test diff --git a/mysql-test/suite/rpl/t/parallel_backup_slave_binlog_off-slave.opt b/mysql-test/suite/rpl/t/parallel_backup_slave_binlog_off-slave.opt new file mode 100644 index 00000000000..a02b6d05829 --- /dev/null +++ b/mysql-test/suite/rpl/t/parallel_backup_slave_binlog_off-slave.opt @@ -0,0 +1 @@ +--skip-log-bin
\ No newline at end of file diff --git a/mysql-test/suite/rpl/t/parallel_backup_slave_binlog_off.test b/mysql-test/suite/rpl/t/parallel_backup_slave_binlog_off.test new file mode 100644 index 00000000000..7cefbdb3ddd --- /dev/null +++ b/mysql-test/suite/rpl/t/parallel_backup_slave_binlog_off.test @@ -0,0 +1,7 @@ +# +--echo # Specialized --skip-log-bin slave version of parallel_backup test. +# +--echo # MDEV-21953: deadlock between BACKUP STAGE BLOCK_COMMIT and parallel +--echo # MDEV-30423: dealock XA COMMIT vs BACKUP +--let $rpl_server_skip_log_bin= 1 +--source parallel_backup.test diff --git a/mysql-test/suite/rpl/t/parallel_backup_xa.inc b/mysql-test/suite/rpl/t/parallel_backup_xa.inc new file mode 100644 index 00000000000..2d831199aa8 --- /dev/null +++ b/mysql-test/suite/rpl/t/parallel_backup_xa.inc @@ -0,0 +1,79 @@ +# Invoked from parallel_backup.test +# Parameters: +# $complete = COMMIT or ROLLBACK +# $slave_ooo_error = 1 means slave group commit did not succeed +# +--let $kind = Normal +if ($slave_ooo_error) +{ + --let $kind = Errored out +} +--echo # +--echo # $kind XA $complete + +--connection slave +--source include/stop_slave.inc + +--connection master +# val_0 is the first value to insert on master in prepared xa +# val_1 is the next one to insert which is the value to block on slave +--let $val_0 = `SELECT max(a)+1 FROM t1` +--let $val_1 = $val_0 +--inc $val_1 + +--connection aux_slave +BEGIN; +--eval INSERT INTO t1 VALUES ($val_1) + +--connection master +XA START '1'; +--eval INSERT INTO t1 VALUES ($val_0) +XA END '1'; +XA PREPARE '1'; +--connection master1 +--eval INSERT INTO t1 VALUES ($val_1) +--connection master +--eval XA $complete '1' +--source include/save_master_gtid.inc + +--connection slave +if ($slave_ooo_error) +{ + SET @sav_innodb_lock_wait_timeout = @@global.innodb_lock_wait_timeout; + SET @sav_slave_transaction_retries = @@global.slave_transaction_retries; + SET @@global.innodb_lock_wait_timeout =1; + SET @@global.slave_transaction_retries=0; +} +--source include/start_slave.inc +--connection aux_slave +--let $wait_condition= SELECT COUNT(*) = 1 FROM information_schema.processlist WHERE state = "Waiting for prior transaction to commit" +--source include/wait_condition.inc +--echo # Xid '1' must be in the output: +XA RECOVER; +--connection backup_slave + BACKUP STAGE START; +--send BACKUP STAGE BLOCK_COMMIT +--connection aux_slave + --sleep 1 + if ($slave_ooo_error) + { + --let $wait_condition= SELECT COUNT(*) = 0 FROM information_schema.processlist WHERE state = "Waiting for prior transaction to commit" + --source include/wait_condition.inc + } + ROLLBACK; +--let $wait_condition= SELECT COUNT(*) = 1 FROM information_schema.processlist WHERE state = "Waiting for backup lock" +--source include/wait_condition.inc +--connection backup_slave + --reap + BACKUP STAGE END; +--connection slave +if (!$slave_ooo_error) +{ + --source include/sync_with_master_gtid.inc +} +--source include/stop_slave.inc +if ($slave_ooo_error) +{ + SET @@global.innodb_lock_wait_timeout = @sav_innodb_lock_wait_timeout; + SET @@global.slave_transaction_retries= @sav_slave_transaction_retries; +} diff --git a/mysql-test/suite/rpl/t/rpl_binlog_dump_slave_gtid_state_info.test b/mysql-test/suite/rpl/t/rpl_binlog_dump_slave_gtid_state_info.test index f26e9565671..bba41295d16 100644 --- a/mysql-test/suite/rpl/t/rpl_binlog_dump_slave_gtid_state_info.test +++ b/mysql-test/suite/rpl/t/rpl_binlog_dump_slave_gtid_state_info.test @@ -8,7 +8,7 @@ # Steps: # 0 - Have LOG_WARNINGS=2 # 1 - On a fresh slave server which has not replicated any GTIDs execute -# "CHANGE MASTER TO MASTER_USE_GTID=current_pos;" command. Start the +# "CHANGE MASTER TO MASTER_USE_GTID=slave_pos;" command. Start the # slave. # 2 - In Master error log verify that pattern "using_gtid(1), gtid('')" is # present. @@ -43,7 +43,7 @@ SET GLOBAL LOG_WARNINGS=2; --connection slave --source include/stop_slave.inc -CHANGE MASTER TO MASTER_USE_GTID=current_pos; +CHANGE MASTER TO MASTER_USE_GTID=slave_pos; --source include/start_slave.inc --connection master diff --git a/mysql-test/suite/rpl/t/rpl_delayed_parallel_slave_sbm-slave.opt b/mysql-test/suite/rpl/t/rpl_delayed_parallel_slave_sbm-slave.opt new file mode 100644 index 00000000000..9eea6a54b68 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_delayed_parallel_slave_sbm-slave.opt @@ -0,0 +1 @@ +--slave-parallel-threads=4 diff --git a/mysql-test/suite/rpl/t/rpl_delayed_parallel_slave_sbm.test b/mysql-test/suite/rpl/t/rpl_delayed_parallel_slave_sbm.test new file mode 100644 index 00000000000..1ac1bc14468 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_delayed_parallel_slave_sbm.test @@ -0,0 +1,150 @@ +# +# This test ensures that after a delayed parallel slave has idled, i.e. +# executed everything in its relay log, the next event group that the SQL +# thread reads from the relay log will immediately be used in the +# Seconds_Behind_Master. In particular, it ensures that the calculation for +# Seconds_Behind_Master is based on the timestamp of the new transaction, +# rather than the last committed transaction. +# +# References: +# MDEV-29639: Seconds_Behind_Master is incorrect for Delayed, Parallel +# Replicas +# + +--source include/master-slave.inc +--source include/have_debug.inc +--source include/have_debug_sync.inc + +--connection slave +--source include/stop_slave.inc +--let $old_debug_dbug= `SELECT @@global.debug_dbug` +--let $master_delay= 3 +--eval change master to master_delay=$master_delay, master_use_gtid=Slave_Pos +--let $old_slave_threads= `SELECT @@GLOBAL.slave_parallel_threads` +set @@GLOBAL.slave_parallel_threads=2; +--source include/start_slave.inc + +--connection master +create table t1 (a int); +--source include/sync_slave_sql_with_master.inc + +--echo # +--echo # Pt 1) Ensure SBM is updated immediately upon arrival of the next event + +--echo # Lock t1 on slave so the first received transaction does not complete/commit +--connection slave +LOCK TABLES t1 WRITE; + +--connection master +--echo # Sleep 2 to allow a buffer between events for SBM check +sleep 2; + +--let $ts_trx_before_ins= `SELECT UNIX_TIMESTAMP()` +--let insert_ctr= 0 +--eval insert into t1 values ($insert_ctr) +--inc $insert_ctr +--source include/save_master_gtid.inc + +--connection slave + +--echo # Waiting for transaction to arrive on slave and begin SQL Delay.. +--let $wait_condition= SELECT count(*) FROM information_schema.processlist WHERE state LIKE 'Waiting until MASTER_DELAY seconds after master executed event'; +--source include/wait_condition.inc + +--echo # Validating SBM is updated on event arrival.. +--let $sbm_trx1_arrive= query_get_value(SHOW SLAVE STATUS, Seconds_Behind_Master, 1) +--let $seconds_since_idling= `SELECT UNIX_TIMESTAMP() - $ts_trx_before_ins` +if (`SELECT $sbm_trx1_arrive > ($seconds_since_idling + 1)`) +{ + --echo # SBM was $sbm_trx1_arrive yet shouldn't have been larger than $seconds_since_idling + 1 (for possible negative clock_diff_with_master) + --die Seconds_Behind_Master should reset after idling +} +--echo # ..done + +--connection slave +UNLOCK TABLES; +--source include/sync_with_master_gtid.inc + +--echo # +--echo # Pt 2) If the SQL thread has not entered an idle state, ensure +--echo # following events do not update SBM + +--echo # Stop slave IO thread so it receives both events together on restart +--connection slave +--source include/stop_slave_io.inc + +--connection master + +--echo # Sleep 2 to allow a buffer between events for SBM check +sleep 2; +--let $ts_trxpt2_before_ins= `SELECT UNIX_TIMESTAMP()` +--eval insert into t1 values ($insert_ctr) +--inc $insert_ctr +--echo # Sleep 3 to create gap between events +sleep 3; +--eval insert into t1 values ($insert_ctr) +--inc $insert_ctr +--let $ts_trx_after_ins= `SELECT UNIX_TIMESTAMP()` +--source include/save_master_pos.inc + +--connection slave +LOCK TABLES t1 WRITE; + +SET @@global.debug_dbug="+d,pause_sql_thread_on_next_event"; + +START SLAVE IO_THREAD; + +--echo # Before we start processing the events, we ensure both transactions +--echo # were written into the relay log. Otherwise, if the IO thread takes too +--echo # long to queue the events, the sql thread can think it has caught up +--echo # too quickly. +SET DEBUG_SYNC='now WAIT_FOR paused_on_event'; +--source include/sync_io_with_master.inc +SET @@global.debug_dbug="-d,pause_sql_thread_on_next_event"; +SET DEBUG_SYNC='now SIGNAL sql_thread_continue'; + +--echo # Wait for first transaction to complete SQL delay and begin execution.. +--let $wait_condition= SELECT count(*) FROM information_schema.processlist WHERE state LIKE 'Waiting for table metadata lock%' AND command LIKE 'Slave_Worker'; +--source include/wait_condition.inc + +--echo # Validate SBM calculation doesn't use the second transaction because SQL thread shouldn't have gone idle.. +--let $sbm_after_trx_no_idle= query_get_value(SHOW SLAVE STATUS, Seconds_Behind_Master, 1) +--let $timestamp_trxpt2_arrive= `SELECT UNIX_TIMESTAMP()` +if (`SELECT $sbm_after_trx_no_idle < $timestamp_trxpt2_arrive - $ts_trx_after_ins`) +{ + --let $cmpv= `SELECT $timestamp_trxpt2_arrive - $ts_trx_after_ins` + --echo # SBM $sbm_after_trx_no_idle was more recent than time since last transaction ($cmpv seconds) + --die Seconds_Behind_Master should not have used second transaction timestamp +} +--let $seconds_since_idling= `SELECT ($timestamp_trxpt2_arrive - $ts_trxpt2_before_ins)` +--echo # ..and that SBM wasn't calculated using prior committed transactions +if (`SELECT $sbm_after_trx_no_idle > ($seconds_since_idling + 1)`) +{ + --echo # SBM was $sbm_after_trx_no_idle yet shouldn't have been larger than $seconds_since_idling + 1 (for possible negative clock_diff_with_master) + --die Seconds_Behind_Master calculation should not have used prior committed transaction +} +--echo # ..done + +--connection slave +UNLOCK TABLES; + +--echo # +--echo # Cleanup + +--echo # Reset master_delay +--source include/stop_slave.inc +--eval CHANGE MASTER TO master_delay=0 +--eval set @@GLOBAL.slave_parallel_threads=$old_slave_threads +--eval SET @@global.debug_dbug="$old_debug_dbug" +SET DEBUG_SYNC='RESET'; +--source include/start_slave.inc + +--connection master +DROP TABLE t1; +--source include/save_master_gtid.inc + +--connection slave +--source include/sync_with_master_gtid.inc + +--source include/rpl_end.inc +--echo # End of rpl_delayed_parallel_slave_sbm.test diff --git a/mysql-test/suite/rpl/t/rpl_gtid_crash.test b/mysql-test/suite/rpl/t/rpl_gtid_crash.test index d0af69a65ed..1249a0f619e 100644 --- a/mysql-test/suite/rpl/t/rpl_gtid_crash.test +++ b/mysql-test/suite/rpl/t/rpl_gtid_crash.test @@ -27,7 +27,7 @@ SET sql_log_bin=1; --source include/stop_slave.inc --replace_result $MASTER_MYPORT MASTER_PORT eval CHANGE MASTER TO master_host = '127.0.0.1', master_port = $MASTER_MYPORT, - MASTER_USE_GTID=CURRENT_POS; + MASTER_USE_GTID=SLAVE_POS; --connection server_1 INSERT INTO t1 VALUES (2,1); diff --git a/mysql-test/suite/rpl/t/rpl_gtid_excess_initial_delay.test b/mysql-test/suite/rpl/t/rpl_gtid_excess_initial_delay.test index d840b67e9e8..6b1eec1984b 100644 --- a/mysql-test/suite/rpl/t/rpl_gtid_excess_initial_delay.test +++ b/mysql-test/suite/rpl/t/rpl_gtid_excess_initial_delay.test @@ -7,7 +7,7 @@ # # Steps: # 0 - Stop the slave and execute CHANGE MASTER command with -# master_use_gtid= curren_pos and master_delay= 10 +# master_delay= 10 # 1 - On slave introduce a sleep of 15 seconds and check that the # Seconds_Behind_Master is within specified master_delay limit. It should # not be more that "10" seconds. @@ -23,7 +23,7 @@ CREATE TABLE t1 (i INT); --sync_slave_with_master --source include/stop_slave.inc -CHANGE MASTER TO MASTER_USE_GTID= current_pos, MASTER_DELAY= 10; +CHANGE MASTER TO MASTER_DELAY= 10; --source include/start_slave.inc --connection master @@ -47,7 +47,7 @@ INSERT INTO t1 VALUES (1); --echo "======= Clean up ========" STOP SLAVE; -CHANGE MASTER TO MASTER_USE_GTID=no, MASTER_DELAY=0; +CHANGE MASTER TO MASTER_DELAY=0; START SLAVE; --connection master diff --git a/mysql-test/suite/rpl/t/rpl_gtid_master_promote.test b/mysql-test/suite/rpl/t/rpl_gtid_master_promote.test index bd5343d7558..064c70b123d 100644 --- a/mysql-test/suite/rpl/t/rpl_gtid_master_promote.test +++ b/mysql-test/suite/rpl/t/rpl_gtid_master_promote.test @@ -31,22 +31,18 @@ delimiter ;| --connection server_2 --sync_with_master --source include/stop_slave.inc -CHANGE MASTER TO master_use_gtid=current_pos; --connection server_3 --sync_with_master --source include/stop_slave.inc -CHANGE MASTER TO master_use_gtid=current_pos; --connection server_4 --sync_with_master --source include/stop_slave.inc -CHANGE MASTER TO master_use_gtid=current_pos; --connection server_5 --sync_with_master --source include/stop_slave.inc -CHANGE MASTER TO master_use_gtid=current_pos; # Create three separate replication streams on master server_1. @@ -225,7 +221,7 @@ SELECT * FROM t4 ORDER BY a,b; --connection server_1 --replace_result $SERVER_MYPORT_2 SERVER_MYPORT_2 eval CHANGE MASTER TO master_host = '127.0.0.1', master_port = $SERVER_MYPORT_2, - master_user = "root", master_use_gtid = current_pos; + master_user = "root", master_use_gtid = slave_pos, master_demote_to_slave=1; --source include/start_slave.inc --sync_with_master SELECT * FROM t1 ORDER BY a; diff --git a/mysql-test/suite/rpl/t/rpl_gtid_mdev4473.test b/mysql-test/suite/rpl/t/rpl_gtid_mdev4473.test index 01259eb5765..f52e51c2413 100644 --- a/mysql-test/suite/rpl/t/rpl_gtid_mdev4473.test +++ b/mysql-test/suite/rpl/t/rpl_gtid_mdev4473.test @@ -24,7 +24,7 @@ reset slave all; connection server_1; --replace_result $SERVER_MYPORT_2 SERVER_MYPORT_2 eval CHANGE MASTER TO master_host = '127.0.0.1', master_port = $SERVER_MYPORT_2, - master_user='root', MASTER_USE_GTID=CURRENT_POS; + master_user='root', MASTER_USE_GTID=SLAVE_POS, master_demote_to_slave=1; source include/start_slave.inc; source include/wait_for_slave_to_start.inc; @@ -38,7 +38,7 @@ save_master_pos; connection server_3; --replace_result $SERVER_MYPORT_2 SERVER_MYPORT_2 eval CHANGE MASTER TO master_host = '127.0.0.1', master_port = $SERVER_MYPORT_2, - MASTER_USE_GTID=CURRENT_POS; + MASTER_USE_GTID=SLAVE_POS; source include/start_slave.inc; sync_with_master; @@ -56,14 +56,14 @@ reset slave all; connection server_2; --replace_result $SERVER_MYPORT_1 SERVER_MYPORT_1 eval CHANGE MASTER TO master_host = '127.0.0.1', master_port = $SERVER_MYPORT_1, - master_user = 'root', MASTER_USE_GTID=CURRENT_POS; + master_user = 'root', MASTER_USE_GTID=SLAVE_POS, master_demote_to_slave=1; source include/start_slave.inc; connection server_3; source include/stop_slave.inc; --replace_result $SERVER_MYPORT_1 SERVER_MYPORT_1 eval CHANGE MASTER TO master_host = '127.0.0.1', master_port = $SERVER_MYPORT_1, - MASTER_USE_GTID=CURRENT_POS; + MASTER_USE_GTID=SLAVE_POS; source include/start_slave.inc; connection server_1; diff --git a/mysql-test/suite/rpl/t/rpl_gtid_misc.test b/mysql-test/suite/rpl/t/rpl_gtid_misc.test index 66d98ec8025..a78b74714a5 100644 --- a/mysql-test/suite/rpl/t/rpl_gtid_misc.test +++ b/mysql-test/suite/rpl/t/rpl_gtid_misc.test @@ -13,7 +13,6 @@ CREATE TABLE t1 (a INT PRIMARY KEY); SET sql_log_bin= 0; INSERT INTO t1 VALUES (1); SET sql_log_bin= 1; -CHANGE MASTER TO master_use_gtid= current_pos; --connection master diff --git a/mysql-test/suite/rpl/t/rpl_gtid_nobinlog.test b/mysql-test/suite/rpl/t/rpl_gtid_nobinlog.test index a5caebf0276..e140f335963 100644 --- a/mysql-test/suite/rpl/t/rpl_gtid_nobinlog.test +++ b/mysql-test/suite/rpl/t/rpl_gtid_nobinlog.test @@ -25,7 +25,7 @@ SELECT * FROM t1 ORDER BY a; --connection server_1 --replace_result $SLAVE_MYPORT SLAVE_PORT eval CHANGE MASTER TO master_host = '127.0.0.1', master_port = $SLAVE_MYPORT, - master_user = 'root', master_use_gtid = current_pos; + master_user = 'root'; START SLAVE; --source include/wait_for_slave_to_start.inc @@ -49,8 +49,7 @@ INSERT INTO t1 VALUES (6, 1); --connection server_2 --replace_result $MASTER_MYPORT MASTER_PORT -eval CHANGE MASTER TO master_host = '127.0.0.1', master_port = $MASTER_MYPORT, - master_use_gtid = current_pos; +eval CHANGE MASTER TO master_host = '127.0.0.1', master_port = $MASTER_MYPORT; START SLAVE; --source include/sync_with_master_gtid.inc diff --git a/mysql-test/suite/rpl/t/rpl_gtid_reconnect.test b/mysql-test/suite/rpl/t/rpl_gtid_reconnect.test index bc28ebddf5e..1452d6b01f0 100644 --- a/mysql-test/suite/rpl/t/rpl_gtid_reconnect.test +++ b/mysql-test/suite/rpl/t/rpl_gtid_reconnect.test @@ -4,11 +4,6 @@ --source include/have_debug.inc ---connection server_2 ---source include/stop_slave.inc -CHANGE MASTER TO master_use_gtid= current_pos; ---source include/start_slave.inc - --connection server_1 CREATE TABLE t1 (a INT); FLUSH LOGS; diff --git a/mysql-test/suite/rpl/t/rpl_gtid_sort.test b/mysql-test/suite/rpl/t/rpl_gtid_sort.test index dc3b18aa897..c31ba877bbf 100644 --- a/mysql-test/suite/rpl/t/rpl_gtid_sort.test +++ b/mysql-test/suite/rpl/t/rpl_gtid_sort.test @@ -47,7 +47,7 @@ SHOW VARIABLES LIKE 'gtid_slave_pos'; SHOW VARIABLES LIKE 'gtid_binlog_state'; --replace_result $MASTER_MYPORT MASTER_PORT eval CHANGE MASTER TO master_host = '127.0.0.1', master_port = $MASTER_MYPORT, - MASTER_USE_GTID=CURRENT_POS; + MASTER_USE_GTID=SLAVE_POS; --source include/start_slave.inc --sync_with_master SHOW VARIABLES LIKE 'gtid_binlog_pos'; diff --git a/mysql-test/suite/rpl/t/rpl_gtid_startpos.test b/mysql-test/suite/rpl/t/rpl_gtid_startpos.test index a7c93ff7ed5..c7bcc1bb97e 100644 --- a/mysql-test/suite/rpl/t/rpl_gtid_startpos.test +++ b/mysql-test/suite/rpl/t/rpl_gtid_startpos.test @@ -19,7 +19,7 @@ CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; SET GLOBAL gtid_slave_pos=""; --replace_result $MASTER_MYPORT MASTER_PORT eval CHANGE MASTER TO master_host = '127.0.0.1', master_port = $MASTER_MYPORT, - MASTER_USE_GTID=CURRENT_POS; + MASTER_USE_GTID=SLAVE_POS; --source include/start_slave.inc --sync_with_master SELECT * FROM t1; @@ -46,7 +46,7 @@ INSERT INTO t1 VALUES (2); SET GLOBAL gtid_slave_pos=""; --replace_result $MASTER_MYPORT MASTER_PORT eval CHANGE MASTER TO master_host = '127.0.0.1', master_port = $MASTER_MYPORT, - MASTER_USE_GTID=CURRENT_POS; + MASTER_USE_GTID=SLAVE_POS; START SLAVE; --let $slave_io_errno= 1236 --source include/wait_for_slave_io_error.inc @@ -73,7 +73,7 @@ INSERT INTO t1 VALUES(3); SET GLOBAL gtid_slave_pos='0-1-3'; --replace_result $MASTER_MYPORT MASTER_PORT eval CHANGE MASTER TO master_host = '127.0.0.1', master_port = $MASTER_MYPORT, - MASTER_USE_GTID=CURRENT_POS; + MASTER_USE_GTID=SLAVE_POS; --source include/start_slave.inc --source include/sync_with_master_gtid.inc SELECT * FROM t1 ORDER by a; @@ -118,7 +118,7 @@ RESET MASTER; --connection server_2 SET GLOBAL gtid_slave_pos=''; --replace_result $MASTER_MYPORT MASTER_PORT -eval CHANGE MASTER TO master_host='127.0.0.1', master_port=$MASTER_MYPORT, master_user='root', master_use_gtid=current_pos; +eval CHANGE MASTER TO master_host='127.0.0.1', master_port=$MASTER_MYPORT, master_user='root', master_use_gtid=slave_pos; --source include/start_slave.inc --connection server_1 @@ -199,7 +199,7 @@ START SLAVE; # Going back to using GTID should fix things. STOP SLAVE IO_THREAD; -CHANGE MASTER TO MASTER_USE_GTID=CURRENT_POS; +CHANGE MASTER TO MASTER_USE_GTID=SLAVE_POS; --source include/start_slave.inc --connection server_1 diff --git a/mysql-test/suite/rpl/t/rpl_gtid_stop_start.test b/mysql-test/suite/rpl/t/rpl_gtid_stop_start.test index 2cf184a3401..7fb457f6d7f 100644 --- a/mysql-test/suite/rpl/t/rpl_gtid_stop_start.test +++ b/mysql-test/suite/rpl/t/rpl_gtid_stop_start.test @@ -21,7 +21,6 @@ INSERT INTO t1 VALUES (1); --let $status_items= Master_Log_File,Using_Gtid --source include/show_slave_status.inc -CHANGE MASTER TO master_use_gtid=current_pos; # Now try to restart the slave mysqld server without starting the slave first # threads after the CHANGE MASTER. diff --git a/mysql-test/suite/rpl/t/rpl_gtid_until.test b/mysql-test/suite/rpl/t/rpl_gtid_until.test index 49baf7e68e6..c89cea23e93 100644 --- a/mysql-test/suite/rpl/t/rpl_gtid_until.test +++ b/mysql-test/suite/rpl/t/rpl_gtid_until.test @@ -45,7 +45,7 @@ START SLAVE UNTIL master_gtid_pos = ""; --error ER_UNTIL_REQUIRES_USING_GTID START SLAVE UNTIL master_gtid_pos = ""; -CHANGE MASTER TO master_use_gtid=current_pos; +CHANGE MASTER TO master_use_gtid=slave_pos; --connection server_1 CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; @@ -224,7 +224,7 @@ INSERT INTO t1 VALUES (10); --connection server_2 --replace_result $SERVER_MYPORT_1 SERVER_MYPORT_1 eval CHANGE MASTER TO master_host = '127.0.0.1', master_port = $SERVER_MYPORT_1, - master_user = "root", master_use_gtid = current_pos; + master_user = "root", master_use_gtid = slave_pos; eval START SLAVE UNTIL master_gtid_pos = '$until_condition'; --source include/wait_for_slave_to_start.inc diff --git a/mysql-test/suite/rpl/t/rpl_mdev6386.test b/mysql-test/suite/rpl/t/rpl_mdev6386.test index e6fb72dc788..f969d656ffb 100644 --- a/mysql-test/suite/rpl/t/rpl_mdev6386.test +++ b/mysql-test/suite/rpl/t/rpl_mdev6386.test @@ -13,7 +13,6 @@ SET sql_log_bin= 0; CALL mtr.add_suppression("Commit failed due to failure of an earlier commit on which this one depends"); INSERT INTO t1 VALUES (1, 2); SET sql_log_bin= 1; -CHANGE MASTER TO master_use_gtid= current_pos; --echo Contents on slave before: SELECT * FROM t1 ORDER BY a; diff --git a/mysql-test/suite/rpl/t/rpl_parallel_analyze.test b/mysql-test/suite/rpl/t/rpl_parallel_analyze.test new file mode 100644 index 00000000000..921c6f02904 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_parallel_analyze.test @@ -0,0 +1,84 @@ +# The test file is created to prove fixes to +# MDEV-30323 Some DDLs like ANALYZE can complete on parallel slave out of order +# Debug-sync tests aiming at parallel replication of ADMIN commands +# are welcome here. + +--source include/have_innodb.inc +--source include/have_debug_sync.inc +--source include/master-slave.inc + +--echo # Initialize +--connection slave +ALTER TABLE mysql.gtid_slave_pos ENGINE=InnoDB; + +--echo # Setup data +--connection master +CREATE TABLE t1 (a int PRIMARY KEY) ENGINE=InnoDB; +CREATE TABLE ta (a int); +--let $pre_load_gtid=`SELECT @@last_gtid` +--source include/save_master_gtid.inc + +--connection slave +--source include/sync_with_master_gtid.inc + +--source suite/rpl/include/create_or_drop_sync_func.inc + +# configure MDEV-30323 slave +--source include/stop_slave.inc +SET @old_parallel_threads =@@GLOBAL.slave_parallel_threads; +SET @old_parallel_mode =@@GLOBAL.slave_parallel_mode; +SET @old_gtid_strict_mode =@@GLOBAL.gtid_strict_mode; +SET GLOBAL slave_parallel_threads=10; +SET GLOBAL slave_parallel_mode=conservative; +SET GLOBAL gtid_strict_mode=ON; +--source include/start_slave.inc + +# MDEV-30323 setup needs two group of events the first of which is a DML +# and ANALYZE is the 2nd. +# The latter is made to race in slave execution over the DML thanks +# to a DML latency simulation. +# In the fixed case the race-over should not be a problem: ultimately +# ANALYZE must wait for its turn to update slave@@global.gtid_binlog_pos. +# Otherwise the reported OOO error must be issued. + +--connection master +SET @old_format= @@SESSION.binlog_format; +SET binlog_format=statement; +INSERT INTO t1 VALUES (foo(1, 'rpl_parallel_after_mark_start_commit WAIT_FOR sig_go', '')); + +ANALYZE TABLE ta; +--source include/save_master_gtid.inc + +--connection slave +--let $wait_condition= SELECT COUNT(*) = 1 FROM information_schema.processlist WHERE state = "Waiting for prior transaction to commit" +--source include/wait_condition.inc + +SELECT info FROM information_schema.processlist WHERE state = "Waiting for prior transaction to commit"; +if (`select strcmp(@@global.gtid_binlog_pos, '$pre_load_gtid') <> 0 or strcmp(@@global.gtid_slave_pos, '$pre_load_gtid') <> 0`) +{ + --let $bs=`SELECT @@global.gtid_binlog_pos` + --let $es=`SELECT @@global.gtid_slave_pos` + --echo Mismatch between expected $pre_load_gtid state and the actual binlog state " @@global.gtid_binlog_pos = $bs or/and slave execution state @@global.gtid_slave_pos = $es. + --die +} + +set @@debug_sync="now signal sig_go"; +--source include/sync_with_master_gtid.inc + +--echo # Cleanup +--connection master +DROP TABLE t1,ta; +--let $create_or_drop=drop +--source suite/rpl/include/create_or_drop_sync_func.inc + +--source include/save_master_gtid.inc + +--connection slave +--source include/sync_with_master_gtid.inc +--source include/stop_slave.inc +SET @@GLOBAL.slave_parallel_threads=@old_parallel_threads; +SET @@GLOBAL.slave_parallel_mode =@old_parallel_mode; +SET @@GLOBAL.gtid_strict_mode =@old_gtid_strict_mode; +--source include/start_slave.inc + +--source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_parallel_mdev6589.test b/mysql-test/suite/rpl/t/rpl_parallel_mdev6589.test index 981c6216376..3a5af7bd4a9 100644 --- a/mysql-test/suite/rpl/t/rpl_parallel_mdev6589.test +++ b/mysql-test/suite/rpl/t/rpl_parallel_mdev6589.test @@ -7,7 +7,6 @@ SET @old_parallel_threads=@@GLOBAL.slave_parallel_threads; --source include/stop_slave.inc SET GLOBAL slave_parallel_threads=10; -CHANGE MASTER TO master_use_gtid=current_pos; --source include/start_slave.inc diff --git a/mysql-test/suite/rpl/t/rpl_parallel_multilevel2.test b/mysql-test/suite/rpl/t/rpl_parallel_multilevel2.test index 4125394ef80..da8a996f570 100644 --- a/mysql-test/suite/rpl/t/rpl_parallel_multilevel2.test +++ b/mysql-test/suite/rpl/t/rpl_parallel_multilevel2.test @@ -25,7 +25,6 @@ SET GLOBAL binlog_commit_wait_usec=2000000; SET @old_updates= @@GLOBAL.binlog_direct_non_transactional_updates; SET GLOBAL binlog_direct_non_transactional_updates=OFF; SET SESSION binlog_direct_non_transactional_updates=OFF; -CHANGE MASTER TO master_use_gtid=current_pos; --connection server_3 --sync_with_master @@ -33,7 +32,6 @@ CHANGE MASTER TO master_use_gtid=current_pos; SET @old_parallel_threads=@@GLOBAL.slave_parallel_threads; --source include/stop_slave.inc SET GLOBAL slave_parallel_threads=10; -CHANGE MASTER TO master_use_gtid=current_pos; --connection server_1 diff --git a/mysql-test/suite/rpl/t/rpl_parallel_temptable.test b/mysql-test/suite/rpl/t/rpl_parallel_temptable.test index edb854842e1..3684763dad7 100644 --- a/mysql-test/suite/rpl/t/rpl_parallel_temptable.test +++ b/mysql-test/suite/rpl/t/rpl_parallel_temptable.test @@ -12,7 +12,6 @@ SET @old_parallel_threads=@@GLOBAL.slave_parallel_threads; --source include/stop_slave.inc SET GLOBAL slave_parallel_threads=5; -CHANGE MASTER TO master_use_gtid= current_pos; --source include/start_slave.inc --connection server_1 diff --git a/mysql-test/suite/rpl/t/rpl_start_alter_mysqlbinlog_1.test b/mysql-test/suite/rpl/t/rpl_start_alter_mysqlbinlog_1.test index 5c78eb290c8..f655d3c10ba 100644 --- a/mysql-test/suite/rpl/t/rpl_start_alter_mysqlbinlog_1.test +++ b/mysql-test/suite/rpl/t/rpl_start_alter_mysqlbinlog_1.test @@ -1,6 +1,6 @@ # # Start Alter with binlog applied using mysqlbinlog -# single maser with only one domain id +# single master with only one domain id # --source include/have_innodb.inc --source include/have_debug.inc @@ -10,7 +10,6 @@ set global binlog_alter_two_phase=true; --connection slave --source include/stop_slave.inc -change master to master_use_gtid= current_pos; set global gtid_strict_mode=1; --echo # Legacy Master Slave diff --git a/mysql-test/suite/rpl/t/rpl_upgrade_master_info.test b/mysql-test/suite/rpl/t/rpl_upgrade_master_info.test index 42b375c4579..fb0e3cf48f7 100644 --- a/mysql-test/suite/rpl/t/rpl_upgrade_master_info.test +++ b/mysql-test/suite/rpl/t/rpl_upgrade_master_info.test @@ -4,7 +4,6 @@ --connection slave --source include/stop_slave.inc -CHANGE MASTER TO master_use_gtid=CURRENT_POS; --let $datadir= `SELECT @@datadir` --let $rpl_server_number= 2 diff --git a/mysql-test/suite/stress/r/deadlock_drop_table.result b/mysql-test/suite/stress/r/deadlock_drop_table.result index 326f694e8d9..7e549157c81 100644 --- a/mysql-test/suite/stress/r/deadlock_drop_table.result +++ b/mysql-test/suite/stress/r/deadlock_drop_table.result @@ -19,11 +19,12 @@ a b c 1 NULL NULL set debug_sync='now SIGNAL go'; set debug_sync='now WAIT_FOR parked2'; -set debug_sync='before_wait_for_refs SIGNAL waiting WAIT_FOR go2'; +set debug_sync='before_wait_for_refs SIGNAL waiting WAIT_FOR go3'; drop table t1;; connection con2; set debug_sync='now WAIT_FOR waiting'; set debug_sync='now SIGNAL go2'; +set debug_sync='now SIGNAL go3'; connection default; connection con1; connection default; diff --git a/mysql-test/suite/stress/t/deadlock_drop_table.test b/mysql-test/suite/stress/t/deadlock_drop_table.test index 0ec19f87389..b4f8f2201e0 100644 --- a/mysql-test/suite/stress/t/deadlock_drop_table.test +++ b/mysql-test/suite/stress/t/deadlock_drop_table.test @@ -20,15 +20,12 @@ set debug_sync='now WAIT_FOR parked'; select * from t1; set debug_sync='now SIGNAL go'; set debug_sync='now WAIT_FOR parked2'; -set debug_sync='before_wait_for_refs SIGNAL waiting WAIT_FOR go2'; +set debug_sync='before_wait_for_refs SIGNAL waiting WAIT_FOR go3'; --send drop table t1; --connection con2 set debug_sync='now WAIT_FOR waiting'; set debug_sync='now SIGNAL go2'; - -# Write out show processlist if the debug sync point times out -let $wait_condition= select count(*)=0 from information_schema.processlist where state like "%debug%"; -source include/wait_condition.inc; +set debug_sync='now SIGNAL go3'; --connection default --reap diff --git a/mysql-test/suite/sys_vars/r/debug_sync_basic.result b/mysql-test/suite/sys_vars/r/debug_sync_basic.result index 6ebb46dd662..11506cac2c2 100644 --- a/mysql-test/suite/sys_vars/r/debug_sync_basic.result +++ b/mysql-test/suite/sys_vars/r/debug_sync_basic.result @@ -2,17 +2,17 @@ select @@global.debug_sync; ERROR HY000: Variable 'debug_sync' is a SESSION variable select @@session.debug_sync; @@session.debug_sync -ON - current signal: '' +ON - current signals: '' show global variables like "debug_sync"; Variable_name Value show session variables like "debug_sync"; Variable_name Value -debug_sync ON - current signal: '' +debug_sync ON - current signals: '' select * from information_schema.global_variables where variable_name="debug_sync"; VARIABLE_NAME VARIABLE_VALUE select * from information_schema.session_variables where variable_name="debug_sync"; VARIABLE_NAME VARIABLE_VALUE -DEBUG_SYNC ON - current signal: '' +DEBUG_SYNC ON - current signals: '' set @@session.debug_sync=1; ERROR 42000: Incorrect argument type to variable 'debug_sync' set @@session.debug_sync=1.1; diff --git a/mysql-test/suite/sys_vars/r/sysvars_debug.result b/mysql-test/suite/sys_vars/r/sysvars_debug.result index 0d77b0211a1..fc04ac83210 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_debug.result +++ b/mysql-test/suite/sys_vars/r/sysvars_debug.result @@ -77,7 +77,7 @@ READ_ONLY YES COMMAND_LINE_ARGUMENT OPTIONAL GLOBAL_VALUE_PATH NULL VARIABLE_NAME DEBUG_SYNC -SESSION_VALUE ON - current signal: '' +SESSION_VALUE ON - current signals: '' GLOBAL_VALUE NULL GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE diff --git a/mysql-test/suite/sys_vars/t/completion_type_func.test b/mysql-test/suite/sys_vars/t/completion_type_func.test index 5c343cee9ab..1de4ae88cfb 100644 --- a/mysql-test/suite/sys_vars/t/completion_type_func.test +++ b/mysql-test/suite/sys_vars/t/completion_type_func.test @@ -146,7 +146,7 @@ COMMIT; --echo ## Inserting rows should give error here because connection should ## --echo ## disconnect after using COMMIT ## ---Error 2006,2013,ER_QUERY_INTERRUPTED,ER_CONNECTION_KILLED,5014 +--Error 2006,2013,ER_QUERY_INTERRUPTED,ER_CONNECTION_KILLED,5014,2026 INSERT INTO t1 VALUES(4,'Record_4'); connection test_con2; @@ -160,7 +160,7 @@ INSERT INTO t1 VALUES(12,'Record_12'); ROLLBACK; --echo ## Expect a failure due to COMMIT/ROLLBACK AND RELEASE behavior ## ---Error 2006,2013,ER_QUERY_INTERRUPTED,ER_CONNECTION_KILLED,5014 +--Error 2006,2013,ER_QUERY_INTERRUPTED,ER_CONNECTION_KILLED,5014,2026 INSERT INTO t1 VALUES(4,'Record_4'); connection default; diff --git a/mysql-test/suite/sys_vars/t/innodb_fatal_semaphore_wait_threshold.test b/mysql-test/suite/sys_vars/t/innodb_fatal_semaphore_wait_threshold.test index b1b2f9fdda9..cce150aec02 100644 --- a/mysql-test/suite/sys_vars/t/innodb_fatal_semaphore_wait_threshold.test +++ b/mysql-test/suite/sys_vars/t/innodb_fatal_semaphore_wait_threshold.test @@ -43,7 +43,7 @@ let $counter= 80; let $mysql_errno= 0; while (!$mysql_errno) { - --error 0,ER_SERVER_SHUTDOWN,ER_CONNECTION_KILLED,2002,2006,2013,5014 + --error 0,ER_SERVER_SHUTDOWN,ER_CONNECTION_KILLED,2002,2006,2013,5014,2026 show status; dec $counter; |