diff options
Diffstat (limited to 'mysql-test')
126 files changed, 4219 insertions, 1099 deletions
diff --git a/mysql-test/collections/10.0-compatible.list b/mysql-test/collections/10.0-compatible.list index 415c74ab00c..4778bde3b78 100644 --- a/mysql-test/collections/10.0-compatible.list +++ b/mysql-test/collections/10.0-compatible.list @@ -1,3 +1,5 @@ +# All lines which have a hash sign *anywhere* will be ignored + main.1st main.adddate_454 main.almost_full @@ -500,7 +502,8 @@ main.subselect-crash_15755 main.subselect_exists2in main.subselect_exists2in_costmat main.subselect_extra -main.subselect_extra_no_semijoin +# Disabled due to connect log output +# main.subselect_extra_no_semijoin main.subselect_gis main.subselect_innodb main.subselect_mat @@ -579,7 +582,8 @@ main.win_empty_over main.win_first_last_value main.win_insert_select main.win_i_s -main.win_lead_lag +# Disabled due to unknown SQL error name +# main.win_lead_lag main.win_min_max main.win_nth_value main.win_orderby diff --git a/mysql-test/main/constraints.result b/mysql-test/main/constraints.result index 57cfbfb3d37..3c061989fd3 100644 --- a/mysql-test/main/constraints.result +++ b/mysql-test/main/constraints.result @@ -74,3 +74,40 @@ CREATE TABLE t_illegal (col_1 INT CHECK something (whatever)); ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'something (whatever))' at line 1 CREATE TABLE t_illegal (col_1 INT CHECK something); ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'something)' at line 1 +CREATE TABLE long_enough_name ( +pk int(11) NOT NULL, +f1 int(11) DEFAULT NULL, +f2 int(11) NOT NULL, +f3 int(11) DEFAULT NULL, +f4 timestamp NOT NULL DEFAULT current_timestamp(), +f5 varchar(32) COLLATE utf8_bin NOT NULL DEFAULT 'foo', +f6 smallint(6) NOT NULL DEFAULT 1, +f7 int(11) DEFAULT NULL, +PRIMARY KEY (pk), +KEY idx1 (f7), +KEY idx2 (f1), +KEY idx3 (f2), +KEY idx4 (f3), +CONSTRAINT constr CHECK (f6 >= 0) +); +SELECT * FROM long_enough_name AS tbl; +pk f1 f2 f3 f4 f5 f6 f7 +SHOW CREATE TABLE long_enough_name; +Table Create Table +long_enough_name CREATE TABLE `long_enough_name` ( + `pk` int(11) NOT NULL, + `f1` int(11) DEFAULT NULL, + `f2` int(11) NOT NULL, + `f3` int(11) DEFAULT NULL, + `f4` timestamp NOT NULL DEFAULT current_timestamp(), + `f5` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT 'foo', + `f6` smallint(6) NOT NULL DEFAULT 1, + `f7` int(11) DEFAULT NULL, + PRIMARY KEY (`pk`), + KEY `idx1` (`f7`), + KEY `idx2` (`f1`), + KEY `idx3` (`f2`), + KEY `idx4` (`f3`), + CONSTRAINT `constr` CHECK (`f6` >= 0) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE long_enough_name; diff --git a/mysql-test/main/constraints.test b/mysql-test/main/constraints.test index 1997c23bfa9..fe51e5060dc 100644 --- a/mysql-test/main/constraints.test +++ b/mysql-test/main/constraints.test @@ -77,3 +77,28 @@ CREATE TABLE t_illegal (col_1 INT CHECK something (whatever)); --error ER_PARSE_ERROR CREATE TABLE t_illegal (col_1 INT CHECK something); +# +# MDEV-17065 Crash on SHOW CREATE TABLE with CHECK CONSTRAINT +# + +CREATE TABLE long_enough_name ( +pk int(11) NOT NULL, +f1 int(11) DEFAULT NULL, +f2 int(11) NOT NULL, +f3 int(11) DEFAULT NULL, +f4 timestamp NOT NULL DEFAULT current_timestamp(), +f5 varchar(32) COLLATE utf8_bin NOT NULL DEFAULT 'foo', +f6 smallint(6) NOT NULL DEFAULT 1, +f7 int(11) DEFAULT NULL, +PRIMARY KEY (pk), +KEY idx1 (f7), +KEY idx2 (f1), +KEY idx3 (f2), +KEY idx4 (f3), +CONSTRAINT constr CHECK (f6 >= 0) +); + +SELECT * FROM long_enough_name AS tbl; +SHOW CREATE TABLE long_enough_name; + +DROP TABLE long_enough_name; diff --git a/mysql-test/main/contributors.result b/mysql-test/main/contributors.result index 36d033f4cb3..3e4bf5f0d43 100644 --- a/mysql-test/main/contributors.result +++ b/mysql-test/main/contributors.result @@ -8,12 +8,14 @@ MariaDB Corporation https://mariadb.com Founding member, Platinum Sponsor of the Visma https://visma.com Gold Sponsor of the MariaDB Foundation DBS https://dbs.com Gold Sponsor of the MariaDB Foundation IBM https://www.ibm.com Gold Sponsor of the MariaDB Foundation +Tencent Games http://game.qq.com/ Gold Sponsor of the MariaDB Foundation Nexedi https://www.nexedi.com Silver Sponsor of the MariaDB Foundation -Acronis http://www.acronis.com Silver Sponsor of the MariaDB Foundation +Acronis https://www.acronis.com Silver Sponsor of the MariaDB Foundation Verkkokauppa.com https://www.verkkokauppa.com Bronze Sponsor of the MariaDB Foundation Virtuozzo https://virtuozzo.com Bronze Sponsor of the MariaDB Foundation Tencent Game DBA http://tencentdba.com/about Bronze Sponsor of the MariaDB Foundation Tencent TDSQL http://tdsql.org Bronze Sponsor of the MariaDB Foundation +Percona https://www.percona.com/ Bronze Sponsor of the MariaDB Foundation Google USA Sponsoring encryption, parallel replication and GTID Facebook USA Sponsoring non-blocking API, LIMIT ROWS EXAMINED etc Ronald Bradford Brisbane, Australia EFF contribution for UC2006 Auction diff --git a/mysql-test/main/create_or_replace.result b/mysql-test/main/create_or_replace.result index 0c1bccb861a..ab8e8f27f4a 100644 --- a/mysql-test/main/create_or_replace.result +++ b/mysql-test/main/create_or_replace.result @@ -479,6 +479,21 @@ UNLOCK TABLES; DROP FUNCTION f1; DROP TABLE t1; # +# MDEV-14410 - Assertion `table->pos_in_locked_tables == __null || +# table->pos_in_locked_tables->table == table' failed in +# mark_used_tables_as_free_for_reuse +# +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (b INT); +CREATE TABLE t3 (c INT); +CREATE TRIGGER tr1 BEFORE INSERT ON t3 FOR EACH ROW INSERT INTO t1 VALUES (); +CREATE TRIGGER tr2 BEFORE INSERT ON t2 FOR EACH ROW INSERT INTO t3 SELECT * FROM t1; +LOCK TABLE t1 WRITE, t2 WRITE; +CREATE OR REPLACE TABLE t1 (i INT); +UNLOCK TABLES; +INSERT INTO t2 VALUES (1); +DROP TABLE t1, t2, t3; +# # MDEV-11071 - Assertion `thd->transaction.stmt.is_empty()' failed in # Locked_tables_list::unlock_locked_tables # diff --git a/mysql-test/main/create_or_replace.test b/mysql-test/main/create_or_replace.test index 3ae882139bc..9f718ab88ce 100644 --- a/mysql-test/main/create_or_replace.test +++ b/mysql-test/main/create_or_replace.test @@ -422,6 +422,27 @@ UNLOCK TABLES; DROP FUNCTION f1; DROP TABLE t1; + +--echo # +--echo # MDEV-14410 - Assertion `table->pos_in_locked_tables == __null || +--echo # table->pos_in_locked_tables->table == table' failed in +--echo # mark_used_tables_as_free_for_reuse +--echo # +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (b INT); +CREATE TABLE t3 (c INT); + +CREATE TRIGGER tr1 BEFORE INSERT ON t3 FOR EACH ROW INSERT INTO t1 VALUES (); +CREATE TRIGGER tr2 BEFORE INSERT ON t2 FOR EACH ROW INSERT INTO t3 SELECT * FROM t1; + +LOCK TABLE t1 WRITE, t2 WRITE; +CREATE OR REPLACE TABLE t1 (i INT); +UNLOCK TABLES; +INSERT INTO t2 VALUES (1); + +# Cleanup +DROP TABLE t1, t2, t3; + --echo # --echo # MDEV-11071 - Assertion `thd->transaction.stmt.is_empty()' failed in --echo # Locked_tables_list::unlock_locked_tables diff --git a/mysql-test/main/cte_nonrecursive.result b/mysql-test/main/cte_nonrecursive.result index 879d837f9f1..4711a540f99 100644 --- a/mysql-test/main/cte_nonrecursive.result +++ b/mysql-test/main/cte_nonrecursive.result @@ -1512,3 +1512,132 @@ a a 1 1 drop database db_mdev_16473; use test; +# +# MDEV-17154: using parameter markers for PS within CTEs more than once +# using local variables in SP within CTEs more than once +# +prepare stmt from " +with cte(c) as (select ? ) select r.c, s.c+10 from cte as r, cte as s; +"; +set @a=2; +execute stmt using @a; +c s.c+10 +2 12 +set @a=5; +execute stmt using @a; +c s.c+10 +5 15 +deallocate prepare stmt; +prepare stmt from " +with cte(c) as (select ? ) select c from cte union select c+10 from cte; +"; +set @a=2; +execute stmt using @a; +c +2 +12 +set @a=5; +execute stmt using @a; +c +5 +15 +deallocate prepare stmt; +prepare stmt from " +with cte_e(a,b) as +( + with cte_o(c) as (select ?) + select r.c+10, s.c+20 from cte_o as r, cte_o as s +) +select * from cte_e as cte_e1 where a > 12 +union all +select * from cte_e as cte_e2; +"; +set @a=2; +execute stmt using @a; +a b +12 22 +set @a=5; +execute stmt using @a; +a b +15 25 +15 25 +deallocate prepare stmt; +create table t1 (a int, b int); +insert into t1 values +(3,33), (1,17), (7,72), (4,45), (2,27), (3,35), (4,47), (3,38), (2,22); +prepare stmt from " +with cte as (select * from t1 where a < ? and b > ?) + select r.a, r.b+10, s.a, s.b+20 from cte as r, cte as s where r.a=s.a+1; +"; +set @a=4, @b=20; +execute stmt using @a,@b; +a r.b+10 a s.b+20 +3 43 2 47 +3 45 2 47 +3 48 2 47 +3 43 2 42 +3 45 2 42 +3 48 2 42 +set @a=5, @b=20; +execute stmt using @a,@b; +a r.b+10 a s.b+20 +4 55 3 53 +4 57 3 53 +3 43 2 47 +3 45 2 47 +3 48 2 47 +4 55 3 55 +4 57 3 55 +4 55 3 58 +4 57 3 58 +3 43 2 42 +3 45 2 42 +3 48 2 42 +deallocate prepare stmt; +create procedure p1() +begin +declare i int; +set i = 0; +while i < 4 do +insert into t1 +with cte(a) as (select i) select r.a-1, s.a+1 from cte as r, cte as s; +set i = i+1; +end while; +end| +create procedure p2(in i int) +begin +insert into t1 +with cte(a) as (select i) select r.a-1, s.a+1 from cte as r, cte as s; +end| +delete from t1; +call p1(); +select * from t1; +a b +-1 1 +0 2 +1 3 +2 4 +call p1(); +select * from t1; +a b +-1 1 +0 2 +1 3 +2 4 +-1 1 +0 2 +1 3 +2 4 +delete from t1; +call p2(3); +select * from t1; +a b +2 4 +call p2(7); +select * from t1; +a b +2 4 +6 8 +drop procedure p1; +drop procedure p2; +drop table t1; diff --git a/mysql-test/main/cte_nonrecursive.test b/mysql-test/main/cte_nonrecursive.test index 11c864bcac1..648fc89975c 100644 --- a/mysql-test/main/cte_nonrecursive.test +++ b/mysql-test/main/cte_nonrecursive.test @@ -1057,3 +1057,93 @@ select * from cte, db_mdev_16473.t1 as t where cte.a=t.a; drop database db_mdev_16473; use test; + +--echo # +--echo # MDEV-17154: using parameter markers for PS within CTEs more than once +--echo # using local variables in SP within CTEs more than once +--echo # + +prepare stmt from " +with cte(c) as (select ? ) select r.c, s.c+10 from cte as r, cte as s; +"; +set @a=2; +execute stmt using @a; +set @a=5; +execute stmt using @a; +deallocate prepare stmt; + +prepare stmt from " +with cte(c) as (select ? ) select c from cte union select c+10 from cte; +"; +set @a=2; +execute stmt using @a; +set @a=5; +execute stmt using @a; +deallocate prepare stmt; + +prepare stmt from " +with cte_e(a,b) as +( + with cte_o(c) as (select ?) + select r.c+10, s.c+20 from cte_o as r, cte_o as s +) +select * from cte_e as cte_e1 where a > 12 +union all +select * from cte_e as cte_e2; +"; +set @a=2; +execute stmt using @a; +set @a=5; +execute stmt using @a; +deallocate prepare stmt; + +create table t1 (a int, b int); +insert into t1 values + (3,33), (1,17), (7,72), (4,45), (2,27), (3,35), (4,47), (3,38), (2,22); + +prepare stmt from " +with cte as (select * from t1 where a < ? and b > ?) + select r.a, r.b+10, s.a, s.b+20 from cte as r, cte as s where r.a=s.a+1; +"; +set @a=4, @b=20; +execute stmt using @a,@b; +set @a=5, @b=20; +execute stmt using @a,@b; +deallocate prepare stmt; + +delimiter |; + +create procedure p1() +begin + declare i int; + set i = 0; + while i < 4 do + insert into t1 + with cte(a) as (select i) select r.a-1, s.a+1 from cte as r, cte as s; + set i = i+1; + end while; +end| + +create procedure p2(in i int) +begin + insert into t1 + with cte(a) as (select i) select r.a-1, s.a+1 from cte as r, cte as s; +end| + +delimiter ;| + +delete from t1; +call p1(); +select * from t1; +call p1(); +select * from t1; + +delete from t1; +call p2(3); +select * from t1; +call p2(7); +select * from t1; + +drop procedure p1; +drop procedure p2; +drop table t1; diff --git a/mysql-test/main/cte_recursive.result b/mysql-test/main/cte_recursive.result index 4becc038543..32c8ea95abf 100644 --- a/mysql-test/main/cte_recursive.result +++ b/mysql-test/main/cte_recursive.result @@ -3306,7 +3306,148 @@ SELECT func(); func() 1 DROP FUNCTION func; -# Start of 10.3 tests +# +# MDEV-17024: two materialized CTEs using the same recursive CTE +# +create table t1 (id int); +insert into t1 values (1), (2), (3); +with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), +cte2 as +(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) +select * from cte1, cte2; +c1 c2 +2 1 +explain extended with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), +cte2 as +(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) +select * from cte1, cte2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived4> ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <derived5> ALL NULL NULL NULL NULL 6 100.00 Using join buffer (flat, BNL join) +4 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where +4 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +5 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where +5 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 with recursive rcte as (/* select#2 */ select 1 AS `a` union /* select#3 */ select cast(`rcte`.`a` + 1 as unsigned) AS `cast(a+1 as unsigned)` from `rcte` where `rcte`.`a` < 10), cte1 as (/* select#4 */ select count(0) AS `c1` from `rcte` join `test`.`t1` where `rcte`.`a` between 3 and 5 and `test`.`t1`.`id` = `rcte`.`a` - 3), cte2 as (/* select#5 */ select count(0) AS `c2` from `rcte` join `test`.`t1` where `rcte`.`a` between 7 and 8 and `test`.`t1`.`id` = `rcte`.`a` - 7)/* select#1 */ select `cte1`.`c1` AS `c1`,`cte2`.`c2` AS `c2` from `cte1` join `cte2` +prepare stmt from "with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), +cte2 as +(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) +select * from cte1, cte2"; +execute stmt; +c1 c2 +2 1 +execute stmt; +c1 c2 +2 1 +create table t2 (c1 int, c2 int); +create procedure p() insert into t2 with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), +cte2 as +(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) +select * from cte1, cte2; +call p(); +select * from t2; +c1 c2 +2 1 +with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), +cte2 as +(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) +select * from cte1; +c1 +2 +with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from t1), +cte2 as +(select count(*) as c2 from t2) +select * from cte1,cte2; +c1 c2 +3 1 +with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), +cte2 as +(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) +select * from cte1, cte2 where cte1.c1 = 3; +c1 c2 +drop procedure p; +drop table t1,t2; +# +# MDEV-17201: recursive part with LIMIT +# +CREATE TABLE purchases ( +id int unsigned NOT NULL AUTO_INCREMENT, +pdate date NOT NULL, +quantity int unsigned NOT NULL, +p_id int unsigned NOT NULL, +PRIMARY KEY (id) +); +INSERT INTO purchases(pdate, quantity, p_id) VALUES +('2014-11-01',5 ,1),('2014-11-03', 3 ,1), +('2014-11-01',2 ,2),('2014-11-03', 4 ,2); +CREATE TABLE expired ( +edate date NOT NULL, +quantity int unsigned NOT NULL, +p_id int unsigned NOT NULL, +PRIMARY KEY (edate,p_id) +); +INSERT INTO expired VALUES ('2014-11-12', 5 ,1),('2014-11-08', 1 ,2); +WITH RECURSIVE expired_map AS ( +SELECT edate AS expired_date, +CAST(NULL AS date) AS purchase_date, +0 AS quantity, +e.p_id, +(SELECT MAX(id)+1 FROM purchases p +WHERE pdate <= edate AND p.p_id =p_id) AS purchase_processed, +quantity AS unresolved +FROM expired e +UNION +( SELECT expired_date, +pdate, +IF(p.quantity < m.unresolved, p.quantity, m.unresolved), +p.p_id, +p.id, +IF(p.quantity < m.unresolved, m.unresolved - p.quantity, 0) +FROM purchases p JOIN expired_map m ON p.p_id = m.p_id +WHERE p.id < m.purchase_processed AND m.unresolved > 0 +ORDER BY p.id DESC +LIMIT 1 +) +) +SELECT * FROM expired_map; +expired_date purchase_date quantity p_id purchase_processed unresolved +2014-11-12 NULL 0 1 5 5 +2014-11-08 NULL 0 2 5 1 +2014-11-08 2014-11-03 1 2 4 0 +DROP TABLE purchases, expired; +# End of 10.2 tests # # MDEV-14217 [db crash] Recursive CTE when SELECT includes new field # @@ -3595,3 +3736,4 @@ a 0 NULL 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 109f7d73011..2e95296ef57 100644 --- a/mysql-test/main/cte_recursive.test +++ b/mysql-test/main/cte_recursive.test @@ -2324,7 +2324,124 @@ SELECT func(); DROP FUNCTION func; ---echo # Start of 10.3 tests +--echo # +--echo # MDEV-17024: two materialized CTEs using the same recursive CTE +--echo # + +create table t1 (id int); +insert into t1 values (1), (2), (3); + +let $q= +with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), +cte2 as +(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) +select * from cte1, cte2; + +eval $q; +eval explain extended $q; +eval prepare stmt from "$q"; +execute stmt; +execute stmt; + +create table t2 (c1 int, c2 int); +eval create procedure p() insert into t2 $q; +call p(); +select * from t2; + +let $q1= +with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), +cte2 as +(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) +select * from cte1; + +eval $q1; + +let $q2= +with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from t1), +cte2 as +(select count(*) as c2 from t2) +select * from cte1,cte2; + +eval $q2; + +let $q3= +with recursive +rcte(a) as +(select 1 union select cast(a+1 as unsigned) from rcte where a < 10), +cte1 as +(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3), +cte2 as +(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7) +select * from cte1, cte2 where cte1.c1 = 3; + +eval $q3; + +drop procedure p; +drop table t1,t2; + +--echo # +--echo # MDEV-17201: recursive part with LIMIT +--echo # + +CREATE TABLE purchases ( + id int unsigned NOT NULL AUTO_INCREMENT, + pdate date NOT NULL, + quantity int unsigned NOT NULL, + p_id int unsigned NOT NULL, + PRIMARY KEY (id) +); +INSERT INTO purchases(pdate, quantity, p_id) VALUES + ('2014-11-01',5 ,1),('2014-11-03', 3 ,1), + ('2014-11-01',2 ,2),('2014-11-03', 4 ,2); + +CREATE TABLE expired ( + edate date NOT NULL, + quantity int unsigned NOT NULL, + p_id int unsigned NOT NULL, + PRIMARY KEY (edate,p_id) +); + +INSERT INTO expired VALUES ('2014-11-12', 5 ,1),('2014-11-08', 1 ,2); + +WITH RECURSIVE expired_map AS ( + SELECT edate AS expired_date, + CAST(NULL AS date) AS purchase_date, + 0 AS quantity, + e.p_id, + (SELECT MAX(id)+1 FROM purchases p + WHERE pdate <= edate AND p.p_id =p_id) AS purchase_processed, + quantity AS unresolved + FROM expired e + UNION + ( SELECT expired_date, + pdate, + IF(p.quantity < m.unresolved, p.quantity, m.unresolved), + p.p_id, + p.id, + IF(p.quantity < m.unresolved, m.unresolved - p.quantity, 0) + FROM purchases p JOIN expired_map m ON p.p_id = m.p_id + WHERE p.id < m.purchase_processed AND m.unresolved > 0 + ORDER BY p.id DESC + LIMIT 1 + ) +) +SELECT * FROM expired_map; + +DROP TABLE purchases, expired; + +--echo # End of 10.2 tests --echo # --echo # MDEV-14217 [db crash] Recursive CTE when SELECT includes new field @@ -2553,3 +2670,5 @@ WITH RECURSIVE cte AS SELECT * FROM cte; DROP TABLE t1; + +--echo # End of 10.3 tests diff --git a/mysql-test/main/flush.result b/mysql-test/main/flush.result index 5cd4fde477d..0336c10c92d 100644 --- a/mysql-test/main/flush.result +++ b/mysql-test/main/flush.result @@ -542,3 +542,27 @@ flush relay logs,relay logs; ERROR HY000: Incorrect usage of FLUSH and RELAY LOGS flush slave,slave; ERROR HY000: Incorrect usage of FLUSH and SLAVE +# +# MDEV-15890 Strange error message if you try to +# FLUSH TABLES <view> after LOCK TABLES <view>. +# +CREATE TABLE t1 (qty INT, price INT); +CREATE VIEW v1 AS SELECT qty, price, qty*price AS value FROM t1; +LOCK TABLES v1 READ; +FLUSH TABLES v1; +ERROR HY000: Table 't1' was locked with a READ lock and can't be updated +UNLOCK TABLES; +LOCK TABLES v1 WRITE; +FLUSH TABLES v1; +ERROR HY000: Table 't1' was locked with a READ lock and can't be updated +UNLOCK TABLES; +LOCK TABLES v1 READ; +FLUSH TABLES t1; +ERROR HY000: Table 't1' was locked with a READ lock and can't be updated +UNLOCK TABLES; +LOCK TABLES t1 READ; +FLUSH TABLES v1; +ERROR HY000: Table 'v1' was not locked with LOCK TABLES +UNLOCK TABLES; +DROP VIEW v1; +DROP TABLE t1; diff --git a/mysql-test/main/flush.test b/mysql-test/main/flush.test index 81834b7de10..03332c06b08 100644 --- a/mysql-test/main/flush.test +++ b/mysql-test/main/flush.test @@ -673,3 +673,35 @@ DROP TABLE t1; flush relay logs,relay logs; --error ER_WRONG_USAGE flush slave,slave; + +--echo # +--echo # MDEV-15890 Strange error message if you try to +--echo # FLUSH TABLES <view> after LOCK TABLES <view>. +--echo # + +CREATE TABLE t1 (qty INT, price INT); +CREATE VIEW v1 AS SELECT qty, price, qty*price AS value FROM t1; + +LOCK TABLES v1 READ; +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +FLUSH TABLES v1; +UNLOCK TABLES; + +LOCK TABLES v1 WRITE; +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +FLUSH TABLES v1; +UNLOCK TABLES; + +LOCK TABLES v1 READ; +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +FLUSH TABLES t1; +UNLOCK TABLES; + +LOCK TABLES t1 READ; +--error ER_TABLE_NOT_LOCKED +FLUSH TABLES v1; +UNLOCK TABLES; + +DROP VIEW v1; +DROP TABLE t1; + diff --git a/mysql-test/main/func_json.result b/mysql-test/main/func_json.result index 5687df0902f..4d62bda95c0 100644 --- a/mysql-test/main/func_json.result +++ b/mysql-test/main/func_json.result @@ -791,6 +791,26 @@ SELECT JSON_SET('{}', '$.a', _utf8 X'C3B6', '$.x', 1, '$.b', _utf8 X'C3B6'); JSON_SET('{}', '$.a', _utf8 X'C3B6', '$.x', 1, '$.b', _utf8 X'C3B6') {"a": "ö", "x": 1, "b": "ö"} # +# MDEV-17121 JSON_ARRAY_APPEND +# +select json_array_append('[ ]', '$', 'aue'); +json_array_append('[ ]', '$', 'aue') +["aue"] +# +# MDEV-17018 JSON_SEARCH and User-Defined Variables. +# +SET @`json` := '["A", [{"B": "1"}], {"C": "AB"}, {"D": "BC"}]', @`value` := 'AB'; +SELECT JSON_SEARCH(@`json`, 'one', @`value`); +JSON_SEARCH(@`json`, 'one', @`value`) +"$[2].C" +SET @`json` := NULL, @`value` := NULL; +# +# MDEV-17001 JSON_MERGE returns nullwhen merging empty array. +# +SELECT JSON_MERGE('[1]', '[]'); +JSON_MERGE('[1]', '[]') +[1] +# # End of 10.2 tests # # diff --git a/mysql-test/main/func_json.test b/mysql-test/main/func_json.test index b244935ce48..088b4b445fc 100644 --- a/mysql-test/main/func_json.test +++ b/mysql-test/main/func_json.test @@ -447,6 +447,26 @@ SELECT JSON_SET('{}', '$.a', _utf8 0xC3B6, '$.b', _utf8 0xC3B6); SELECT JSON_SET('{}', '$.a', _utf8 X'C3B6', '$.x', 1, '$.b', _utf8 X'C3B6'); --echo # +--echo # MDEV-17121 JSON_ARRAY_APPEND +--echo # + +select json_array_append('[ ]', '$', 'aue'); + +--echo # +--echo # MDEV-17018 JSON_SEARCH and User-Defined Variables. +--echo # + +SET @`json` := '["A", [{"B": "1"}], {"C": "AB"}, {"D": "BC"}]', @`value` := 'AB'; +SELECT JSON_SEARCH(@`json`, 'one', @`value`); +SET @`json` := NULL, @`value` := NULL; + +--echo # +--echo # MDEV-17001 JSON_MERGE returns nullwhen merging empty array. +--echo # + +SELECT JSON_MERGE('[1]', '[]'); + +--echo # --echo # End of 10.2 tests --echo # diff --git a/mysql-test/main/func_time.result b/mysql-test/main/func_time.result index 6bf9565831e..8418db604df 100644 --- a/mysql-test/main/func_time.result +++ b/mysql-test/main/func_time.result @@ -2803,6 +2803,164 @@ PERIOD_DIFF(2018, AES_ENCRYPT('Rae Bareli', 'Rae Bareli')) Warnings: Warning 1292 Truncated incorrect INTEGER value: '-3S\xFA\xDE?\x00\x00\xCA\xB3\xEEE\xA4\xD1\xC1\xA8' # +# MDEV-17249 MAKETIME(-1e50,0,0) returns a wrong result +# +SELECT +MAKETIME(1e10,0,0), +MAKETIME(-1e10,0,0), +MAKETIME(1e50,0,0), +MAKETIME(-1e50,0,0), +MAKETIME(COALESCE(1e50),0,0), +MAKETIME(COALESCE(-1e50),0,0); +MAKETIME(1e10,0,0) 838:59:59 +MAKETIME(-1e10,0,0) -838:59:59 +MAKETIME(1e50,0,0) 838:59:59 +MAKETIME(-1e50,0,0) -838:59:59 +MAKETIME(COALESCE(1e50),0,0) 838:59:59 +MAKETIME(COALESCE(-1e50),0,0) -838:59:59 +Warnings: +Level Warning +Code 1292 +Message Truncated incorrect time value: '10000000000:00:00' +Level Warning +Code 1292 +Message Truncated incorrect time value: '-10000000000:00:00' +Level Warning +Code 1292 +Message Truncated incorrect time value: '9223372036854775807:00:00' +Level Warning +Code 1292 +Message Truncated incorrect time value: '-9223372036854775808:00:00' +Level Warning +Code 1292 +Message Truncated incorrect time value: '9223372036854775807:00:00' +Level Warning +Code 1292 +Message Truncated incorrect time value: '-9223372036854775808:00:00' +CREATE TABLE t1 (a FLOAT); +INSERT INTO t1 VALUES (1e30),(-1e30); +SELECT MAKETIME(a,0,0) FROM t1; +MAKETIME(a,0,0) +838:59:59 +-838:59:59 +Warnings: +Warning 1292 Truncated incorrect time value: '9223372036854775807:00:00' +Warning 1292 Truncated incorrect time value: '-9223372036854775808:00:00' +DROP TABLE t1; +# +# MDEV-17244 MAKETIME(900,0,0.111) returns a wrong result +# +SELECT MAKETIME(900,0,0); +MAKETIME(900,0,0) +838:59:59 +Warnings: +Warning 1292 Truncated incorrect time value: '900:00:00' +SELECT MAKETIME(900,0,0.1); +MAKETIME(900,0,0.1) +838:59:59.9 +Warnings: +Warning 1292 Truncated incorrect time value: '900:00:00' +SELECT MAKETIME(900,0,0.11); +MAKETIME(900,0,0.11) +838:59:59.99 +Warnings: +Warning 1292 Truncated incorrect time value: '900:00:00' +SELECT MAKETIME(900,0,0.111); +MAKETIME(900,0,0.111) +838:59:59.999 +Warnings: +Warning 1292 Truncated incorrect time value: '900:00:00' +SELECT MAKETIME(900,0,0.1111); +MAKETIME(900,0,0.1111) +838:59:59.9999 +Warnings: +Warning 1292 Truncated incorrect time value: '900:00:00' +SELECT MAKETIME(900,0,0.11111); +MAKETIME(900,0,0.11111) +838:59:59.99999 +Warnings: +Warning 1292 Truncated incorrect time value: '900:00:00' +SELECT MAKETIME(900,0,0.111111); +MAKETIME(900,0,0.111111) +838:59:59.999999 +Warnings: +Warning 1292 Truncated incorrect time value: '900:00:00' +SELECT MAKETIME(900,0,0.1111111); +MAKETIME(900,0,0.1111111) +838:59:59.999999 +Warnings: +Warning 1292 Truncated incorrect time value: '900:00:00' +SELECT MAKETIME(900,0,0.11111111); +MAKETIME(900,0,0.11111111) +838:59:59.999999 +Warnings: +Warning 1292 Truncated incorrect time value: '900:00:00' +SELECT MAKETIME(900,0,0.111111111); +MAKETIME(900,0,0.111111111) +838:59:59.999999 +Warnings: +Warning 1292 Truncated incorrect time value: '900:00:00' +SELECT MAKETIME(900,0,EXP(1)); +MAKETIME(900,0,EXP(1)) +838:59:59.999999 +Warnings: +Warning 1292 Truncated incorrect time value: '900:00:02' +SELECT MAKETIME(-900,0,0); +MAKETIME(-900,0,0) +-838:59:59 +Warnings: +Warning 1292 Truncated incorrect time value: '-900:00:00' +SELECT MAKETIME(-900,0,0.1); +MAKETIME(-900,0,0.1) +-838:59:59.9 +Warnings: +Warning 1292 Truncated incorrect time value: '-900:00:00' +SELECT MAKETIME(-900,0,0.11); +MAKETIME(-900,0,0.11) +-838:59:59.99 +Warnings: +Warning 1292 Truncated incorrect time value: '-900:00:00' +SELECT MAKETIME(-900,0,0.111); +MAKETIME(-900,0,0.111) +-838:59:59.999 +Warnings: +Warning 1292 Truncated incorrect time value: '-900:00:00' +SELECT MAKETIME(-900,0,0.1111); +MAKETIME(-900,0,0.1111) +-838:59:59.9999 +Warnings: +Warning 1292 Truncated incorrect time value: '-900:00:00' +SELECT MAKETIME(-900,0,0.11111); +MAKETIME(-900,0,0.11111) +-838:59:59.99999 +Warnings: +Warning 1292 Truncated incorrect time value: '-900:00:00' +SELECT MAKETIME(-900,0,0.111111); +MAKETIME(-900,0,0.111111) +-838:59:59.999999 +Warnings: +Warning 1292 Truncated incorrect time value: '-900:00:00' +SELECT MAKETIME(-900,0,0.1111111); +MAKETIME(-900,0,0.1111111) +-838:59:59.999999 +Warnings: +Warning 1292 Truncated incorrect time value: '-900:00:00' +SELECT MAKETIME(-900,0,0.11111111); +MAKETIME(-900,0,0.11111111) +-838:59:59.999999 +Warnings: +Warning 1292 Truncated incorrect time value: '-900:00:00' +SELECT MAKETIME(-900,0,0.111111111); +MAKETIME(-900,0,0.111111111) +-838:59:59.999999 +Warnings: +Warning 1292 Truncated incorrect time value: '-900:00:00' +SELECT MAKETIME(-900,0,EXP(1)); +MAKETIME(-900,0,EXP(1)) +-838:59:59.999999 +Warnings: +Warning 1292 Truncated incorrect time value: '-900:00:02' +# # End of 5.5 tests # # diff --git a/mysql-test/main/func_time.test b/mysql-test/main/func_time.test index 9927f8799b3..442d85cb6ef 100644 --- a/mysql-test/main/func_time.test +++ b/mysql-test/main/func_time.test @@ -1707,6 +1707,55 @@ SELECT SEC_TO_TIME(MAKEDATE(0,RAND(~0))); # SELECT PERIOD_DIFF(2018, AES_ENCRYPT('Rae Bareli', 'Rae Bareli')); + +--echo # +--echo # MDEV-17249 MAKETIME(-1e50,0,0) returns a wrong result +--echo # + +--vertical_results +SELECT + MAKETIME(1e10,0,0), + MAKETIME(-1e10,0,0), + MAKETIME(1e50,0,0), + MAKETIME(-1e50,0,0), + MAKETIME(COALESCE(1e50),0,0), + MAKETIME(COALESCE(-1e50),0,0); +--horizontal_results + +CREATE TABLE t1 (a FLOAT); +INSERT INTO t1 VALUES (1e30),(-1e30); +SELECT MAKETIME(a,0,0) FROM t1; +DROP TABLE t1; + +--echo # +--echo # MDEV-17244 MAKETIME(900,0,0.111) returns a wrong result +--echo # + +SELECT MAKETIME(900,0,0); +SELECT MAKETIME(900,0,0.1); +SELECT MAKETIME(900,0,0.11); +SELECT MAKETIME(900,0,0.111); +SELECT MAKETIME(900,0,0.1111); +SELECT MAKETIME(900,0,0.11111); +SELECT MAKETIME(900,0,0.111111); +SELECT MAKETIME(900,0,0.1111111); +SELECT MAKETIME(900,0,0.11111111); +SELECT MAKETIME(900,0,0.111111111); +SELECT MAKETIME(900,0,EXP(1)); + +SELECT MAKETIME(-900,0,0); +SELECT MAKETIME(-900,0,0.1); +SELECT MAKETIME(-900,0,0.11); +SELECT MAKETIME(-900,0,0.111); +SELECT MAKETIME(-900,0,0.1111); +SELECT MAKETIME(-900,0,0.11111); +SELECT MAKETIME(-900,0,0.111111); +SELECT MAKETIME(-900,0,0.1111111); +SELECT MAKETIME(-900,0,0.11111111); +SELECT MAKETIME(-900,0,0.111111111); +SELECT MAKETIME(-900,0,EXP(1)); + + --echo # --echo # End of 5.5 tests --echo # diff --git a/mysql-test/main/gis-precise.result b/mysql-test/main/gis-precise.result index 292dfe0462c..2d78b6e53de 100644 --- a/mysql-test/main/gis-precise.result +++ b/mysql-test/main/gis-precise.result @@ -504,6 +504,9 @@ GEOMETRYFROMTEXT('POINT(4599 60359)'), ) as relate_res; relate_res 0 +with cte1 as( select (st_symdifference(point(1,1),point(1,1))) as a1 ), cte2 as(select 1 as a2) select 1 from cte1 where cte1.a1 < '1'; +1 +1 DROP TABLE IF EXISTS p1; CREATE PROCEDURE p1(dist DOUBLE, geom TEXT) BEGIN diff --git a/mysql-test/main/gis-precise.test b/mysql-test/main/gis-precise.test index 07fabae6025..0bb445924b8 100644 --- a/mysql-test/main/gis-precise.test +++ b/mysql-test/main/gis-precise.test @@ -382,5 +382,9 @@ SELECT ST_RELATE( 'F*FFFF**F' ) as relate_res; +# MDEV- 16050 cte + geometry functions lead to crash. + +with cte1 as( select (st_symdifference(point(1,1),point(1,1))) as a1 ), cte2 as(select 1 as a2) select 1 from cte1 where cte1.a1 < '1'; + --source include/gis_debug.inc diff --git a/mysql-test/main/gis.result b/mysql-test/main/gis.result index 2fb2d32f447..be70d0d3b72 100644 --- a/mysql-test/main/gis.result +++ b/mysql-test/main/gis.result @@ -1721,6 +1721,22 @@ c2 DROP TABLE t1; SET optimizer_switch=@save_optimizer_switch; # +# MDEV-16995: ER_CANT_CREATE_GEOMETRY_OBJECT encountered for a query with +# optimizer_use_condition_selectivity>=3 +# +CREATE TABLE t1 (a POINT); +INSERT INTO t1 VALUES (POINT(1,1)),(POINT(1,2)),(POINT(1,3)); +set @save_use_stat_tables= @@use_stat_tables; +set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; +set @@use_stat_tables= PREFERABLY; +set @@optimizer_use_condition_selectivity=3; +SELECT COUNT(*) FROM t1 WHERE a IN ('test','test1'); +COUNT(*) +0 +set @@use_stat_tables= @save_use_stat_tables; +set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; +drop table t1; +# # End 10.0 tests # SHOW CREATE TABLE information_schema.geometry_columns; diff --git a/mysql-test/main/gis.test b/mysql-test/main/gis.test index ca806778f0e..d22591bbe69 100644 --- a/mysql-test/main/gis.test +++ b/mysql-test/main/gis.test @@ -1481,6 +1481,21 @@ SELECT LENGTH(CONCAT(t2,'--',t2)) c2 FROM (SELECT ST_BUFFER(POINT(x,y), 0) t2 FR DROP TABLE t1; SET optimizer_switch=@save_optimizer_switch; +--echo # +--echo # MDEV-16995: ER_CANT_CREATE_GEOMETRY_OBJECT encountered for a query with +--echo # optimizer_use_condition_selectivity>=3 +--echo # + +CREATE TABLE t1 (a POINT); +INSERT INTO t1 VALUES (POINT(1,1)),(POINT(1,2)),(POINT(1,3)); +set @save_use_stat_tables= @@use_stat_tables; +set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; +set @@use_stat_tables= PREFERABLY; +set @@optimizer_use_condition_selectivity=3; +SELECT COUNT(*) FROM t1 WHERE a IN ('test','test1'); +set @@use_stat_tables= @save_use_stat_tables; +set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; +drop table t1; --echo # --echo # End 10.0 tests diff --git a/mysql-test/main/grant.result b/mysql-test/main/grant.result index ba2d595a50e..b2f6bafd291 100644 --- a/mysql-test/main/grant.result +++ b/mysql-test/main/grant.result @@ -2670,6 +2670,38 @@ disconnect conn2; disconnect conn3; DROP USER foo@'127.0.0.1'; # End of Bug#12766319 +create user foo@localhost; +create database foodb; +grant create routine on foodb.* to foo@localhost; +connect con1,localhost,foo; +create procedure fooproc() select 'i am fooproc'; +show grants; +Grants for foo@localhost +GRANT USAGE ON *.* TO 'foo'@'localhost' +GRANT CREATE ROUTINE ON `foodb`.* TO 'foo'@'localhost' +GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `test`.`fooproc` TO 'foo'@'localhost' +disconnect con1; +connection default; +rename table mysql.procs_priv to mysql.procs_priv1; +flush privileges; +show grants for foo@localhost; +Grants for foo@localhost +GRANT USAGE ON *.* TO 'foo'@'localhost' +GRANT CREATE ROUTINE ON `foodb`.* TO 'foo'@'localhost' +rename table mysql.procs_priv1 to mysql.procs_priv; +show grants for foo@localhost; +Grants for foo@localhost +GRANT USAGE ON *.* TO 'foo'@'localhost' +GRANT CREATE ROUTINE ON `foodb`.* TO 'foo'@'localhost' +flush privileges; +show grants for foo@localhost; +Grants for foo@localhost +GRANT USAGE ON *.* TO 'foo'@'localhost' +GRANT CREATE ROUTINE ON `foodb`.* TO 'foo'@'localhost' +GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `test`.`fooproc` TO 'foo'@'localhost' +drop user foo@localhost; +drop procedure fooproc; +drop database foodb; # # Bug#11756966 - 48958: STORED PROCEDURES CAN BE LEVERAGED TO BYPASS # DATABASE SECURITY diff --git a/mysql-test/main/grant.test b/mysql-test/main/grant.test index c3bb987acc8..f54c4bd981d 100644 --- a/mysql-test/main/grant.test +++ b/mysql-test/main/grant.test @@ -2175,6 +2175,28 @@ DROP USER foo@'127.0.0.1'; --echo # End of Bug#12766319 +# +# Bug#27230925: HANDLE_FATAL_SIGNAL (SIG=11) IN SHOW_ROUTINE_GRANTS +# +create user foo@localhost; +create database foodb; +grant create routine on foodb.* to foo@localhost; +connect con1,localhost,foo; +create procedure fooproc() select 'i am fooproc'; +show grants; +disconnect con1; +connection default; +rename table mysql.procs_priv to mysql.procs_priv1; +flush privileges; +show grants for foo@localhost; +rename table mysql.procs_priv1 to mysql.procs_priv; +show grants for foo@localhost; +flush privileges; +show grants for foo@localhost; +drop user foo@localhost; +drop procedure fooproc; +drop database foodb; + --echo # --echo # Bug#11756966 - 48958: STORED PROCEDURES CAN BE LEVERAGED TO BYPASS diff --git a/mysql-test/main/group_min_max.result b/mysql-test/main/group_min_max.result index b3b660c4170..cfdf9ef9865 100644 --- a/mysql-test/main/group_min_max.result +++ b/mysql-test/main/group_min_max.result @@ -3733,6 +3733,34 @@ id MIN(a) MAX(a) 4 2001-01-04 2001-01-04 DROP TABLE t1; # +# MDEV-17039: Query plan changes when we use GROUP BY optimization with optimizer_use_condition_selectivity=4 +# and use_stat_tables= PREFERABLY +# +CREATE TABLE t1 (a INT, b INT,c INT DEFAULT 0, INDEX (a,b)); +INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3), (1,4), (1,5), +(2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6); +set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; +set @save_use_stat_tables= @@use_stat_tables; +set @@optimizer_use_condition_selectivity=4; +set @@use_stat_tables=PREFERABLY; +explain extended SELECT a FROM t1 AS t1_outer WHERE a IN (SELECT max(b) FROM t1 GROUP BY a); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 8 100.00 +1 PRIMARY t1_outer ref a a 5 <subquery2>.max(b) 2 100.00 Using index +2 MATERIALIZED t1 range NULL a 5 NULL 8 100.00 Using index for group-by +Warnings: +Note 1003 /* select#1 */ select `test`.`t1_outer`.`a` AS `a` from <materialize> (/* select#2 */ select max(`test`.`t1`.`b`) from `test`.`t1` group by `test`.`t1`.`a`) join `test`.`t1` `t1_outer` where `test`.`t1_outer`.`a` = `<subquery2>`.`max(b)` +set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +set @@use_stat_tables=@save_use_stat_tables; +explain extended SELECT a FROM t1 AS t1_outer WHERE a IN (SELECT max(b) FROM t1 GROUP BY a); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 8 100.00 +1 PRIMARY t1_outer ref a a 5 <subquery2>.max(b) 2 100.00 Using index +2 MATERIALIZED t1 range NULL a 5 NULL 8 100.00 Using index for group-by +Warnings: +Note 1003 /* select#1 */ select `test`.`t1_outer`.`a` AS `a` from <materialize> (/* select#2 */ select max(`test`.`t1`.`b`) from `test`.`t1` group by `test`.`t1`.`a`) join `test`.`t1` `t1_outer` where `test`.`t1_outer`.`a` = `<subquery2>`.`max(b)` +drop table t1; +# # End of 10.0 tests # # diff --git a/mysql-test/main/group_min_max.test b/mysql-test/main/group_min_max.test index adad9073235..e8245dd2898 100644 --- a/mysql-test/main/group_min_max.test +++ b/mysql-test/main/group_min_max.test @@ -1519,6 +1519,23 @@ ALTER TABLE t1 ADD KEY(id,a); SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=DATE'2001-01-04' GROUP BY id; DROP TABLE t1; +--echo # +--echo # MDEV-17039: Query plan changes when we use GROUP BY optimization with optimizer_use_condition_selectivity=4 +--echo # and use_stat_tables= PREFERABLY +--echo # + +CREATE TABLE t1 (a INT, b INT,c INT DEFAULT 0, INDEX (a,b)); +INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3), (1,4), (1,5), +(2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6); +set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; +set @save_use_stat_tables= @@use_stat_tables; +set @@optimizer_use_condition_selectivity=4; +set @@use_stat_tables=PREFERABLY; +explain extended SELECT a FROM t1 AS t1_outer WHERE a IN (SELECT max(b) FROM t1 GROUP BY a); +set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +set @@use_stat_tables=@save_use_stat_tables; +explain extended SELECT a FROM t1 AS t1_outer WHERE a IN (SELECT max(b) FROM t1 GROUP BY a); +drop table t1; --echo # --echo # End of 10.0 tests diff --git a/mysql-test/main/join.result b/mysql-test/main/join.result index 1fc47041dfd..cc8e174c8e6 100644 --- a/mysql-test/main/join.result +++ b/mysql-test/main/join.result @@ -1522,11 +1522,13 @@ ERROR 42S22: Unknown column 'f' in 'from clause' DROP TABLE t; CREATE TABLE t (f INT); CALL p; -ERROR 42S22: Unknown column 'f' in 'from clause' +f DROP TABLE t; CREATE TABLE t (i INT); CALL p; -ERROR 42S22: Unknown column 'f' in 'from clause' +ERROR 42S22: Unknown column 't1.f' in 'field list' +CALL p; +ERROR 42S22: Unknown column 't1.f' in 'field list' DROP PROCEDURE p; DROP TABLE t; CREATE TABLE t1 (a INT, b INT); diff --git a/mysql-test/main/join.test b/mysql-test/main/join.test index 8a088de91cc..3d2a02e2346 100644 --- a/mysql-test/main/join.test +++ b/mysql-test/main/join.test @@ -1185,12 +1185,13 @@ CREATE TABLE t (f INT); # # The following shouldn't fail as the table is now matching the using # ---error ER_BAD_FIELD_ERROR CALL p; DROP TABLE t; CREATE TABLE t (i INT); --error ER_BAD_FIELD_ERROR CALL p; +--error ER_BAD_FIELD_ERROR +CALL p; DROP PROCEDURE p; DROP TABLE t; diff --git a/mysql-test/main/query_cache_innodb.result b/mysql-test/main/query_cache_innodb.result index 643a065612f..146a6fbc289 100644 --- a/mysql-test/main/query_cache_innodb.result +++ b/mysql-test/main/query_cache_innodb.result @@ -84,7 +84,7 @@ t2id id use test; drop database `#mysql50#-`; SET NAMES default; -FOUND 12 /\[ERROR\] Invalid \(old\?\) table or database name/ in mysqld.1.err +FOUND 8 /\[ERROR\] Invalid \(old\?\) table or database name/ in mysqld.1.err set global query_cache_type=DEFAULT; set global query_cache_size=DEFAULT; End of 10.2 tests diff --git a/mysql-test/main/selectivity.result b/mysql-test/main/selectivity.result index aff14b23ccb..00907235ecc 100644 --- a/mysql-test/main/selectivity.result +++ b/mysql-test/main/selectivity.result @@ -782,9 +782,9 @@ set optimizer_use_condition_selectivity=3; explain extended select * from t1 where a < 1 and a > 7; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 100.00 Using where Warnings: -Note 1003 select 7 AS `a` from `test`.`t1` where 0 +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 1 and `test`.`t1`.`a` > 7 select * from t1 where a < 1 and a > 7; a drop table t1; @@ -1506,9 +1506,9 @@ col1 explain extended select * from t2 where col1 < 'b' and col1 > 'd'; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 SIMPLE t2 ALL NULL NULL NULL NULL 8 100.00 Using where Warnings: -Note 1003 select 'd' AS `col1` from `test`.`t2` where 0 +Note 1003 select `test`.`t2`.`col1` AS `col1` from `test`.`t2` where `test`.`t2`.`col1` < 'b' and `test`.`t2`.`col1` > 'd' drop table t1,t2; set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; set use_stat_tables=@save_use_stat_tables; @@ -1595,3 +1595,43 @@ drop table t1,t0; set histogram_size=@save_histogram_size; set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; set use_stat_tables=@save_use_stat_tables; +# +# MDEV-15306: Wrong/Unexpected result with the value +# optimizer_use_condition_selectivity set to 4 +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); +CREATE FUNCTION f1() RETURNS INT DETERMINISTIC +BEGIN +SET @cnt := @cnt + 1; +RETURN 1; +END;| +set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; +set @save_use_stat_tables= @@use_stat_tables; +set @@use_stat_tables='complementary'; +set @@optimizer_use_condition_selectivity=4; +SET @cnt= 0; +SELECT * FROM t1 WHERE a = f1(); +a +1 +SELECT @cnt; +@cnt +1 +set @@use_stat_tables='preferably'; +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +SET @cnt := 0; +set @@optimizer_use_condition_selectivity=4; +SELECT * FROM t1 WHERE a = f1(); +a +1 +SELECT @cnt; +@cnt +2 +alter table t1 force; +set @@use_stat_tables= @save_use_stat_tables; +set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +drop table t1; +drop function f1; diff --git a/mysql-test/main/selectivity.test b/mysql-test/main/selectivity.test index cf12bdaea21..3df49456332 100644 --- a/mysql-test/main/selectivity.test +++ b/mysql-test/main/selectivity.test @@ -1066,3 +1066,39 @@ drop table t1,t0; set histogram_size=@save_histogram_size; set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; set use_stat_tables=@save_use_stat_tables; + +--echo # +--echo # MDEV-15306: Wrong/Unexpected result with the value +--echo # optimizer_use_condition_selectivity set to 4 +--echo # + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); + +delimiter |; +CREATE FUNCTION f1() RETURNS INT DETERMINISTIC +BEGIN + SET @cnt := @cnt + 1; + RETURN 1; +END;| +delimiter ;| +set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; +set @save_use_stat_tables= @@use_stat_tables; +set @@use_stat_tables='complementary'; +set @@optimizer_use_condition_selectivity=4; +SET @cnt= 0; +SELECT * FROM t1 WHERE a = f1(); +SELECT @cnt; + +set @@use_stat_tables='preferably'; +analyze table t1 persistent for all; +SET @cnt := 0; +set @@optimizer_use_condition_selectivity=4; +SELECT * FROM t1 WHERE a = f1(); +SELECT @cnt; +alter table t1 force; +set @@use_stat_tables= @save_use_stat_tables; +set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +drop table t1; +drop function f1; + diff --git a/mysql-test/main/selectivity_innodb.result b/mysql-test/main/selectivity_innodb.result index ab698760c55..921bd20fc69 100644 --- a/mysql-test/main/selectivity_innodb.result +++ b/mysql-test/main/selectivity_innodb.result @@ -789,9 +789,9 @@ set optimizer_use_condition_selectivity=3; explain extended select * from t1 where a < 1 and a > 7; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 100.00 Using where Warnings: -Note 1003 select 7 AS `a` from `test`.`t1` where 0 +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 1 and `test`.`t1`.`a` > 7 select * from t1 where a < 1 and a > 7; a drop table t1; @@ -1517,9 +1517,9 @@ col1 explain extended select * from t2 where col1 < 'b' and col1 > 'd'; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 SIMPLE t2 ALL NULL NULL NULL NULL 8 100.00 Using where Warnings: -Note 1003 select 'd' AS `col1` from `test`.`t2` where 0 +Note 1003 select `test`.`t2`.`col1` AS `col1` from `test`.`t2` where `test`.`t2`.`col1` < 'b' and `test`.`t2`.`col1` > 'd' drop table t1,t2; set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; set use_stat_tables=@save_use_stat_tables; @@ -1606,6 +1606,46 @@ drop table t1,t0; set histogram_size=@save_histogram_size; set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; set use_stat_tables=@save_use_stat_tables; +# +# MDEV-15306: Wrong/Unexpected result with the value +# optimizer_use_condition_selectivity set to 4 +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); +CREATE FUNCTION f1() RETURNS INT DETERMINISTIC +BEGIN +SET @cnt := @cnt + 1; +RETURN 1; +END;| +set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; +set @save_use_stat_tables= @@use_stat_tables; +set @@use_stat_tables='complementary'; +set @@optimizer_use_condition_selectivity=4; +SET @cnt= 0; +SELECT * FROM t1 WHERE a = f1(); +a +1 +SELECT @cnt; +@cnt +1 +set @@use_stat_tables='preferably'; +analyze table t1 persistent for all; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +SET @cnt := 0; +set @@optimizer_use_condition_selectivity=4; +SELECT * FROM t1 WHERE a = f1(); +a +1 +SELECT @cnt; +@cnt +2 +alter table t1 force; +set @@use_stat_tables= @save_use_stat_tables; +set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +drop table t1; +drop function f1; set optimizer_switch=@save_optimizer_switch_for_selectivity_test; set @tmp_ust= @@use_stat_tables; set @tmp_oucs= @@optimizer_use_condition_selectivity; diff --git a/mysql-test/main/sp-security.result b/mysql-test/main/sp-security.result index 7813ab6a192..4487528210f 100644 --- a/mysql-test/main/sp-security.result +++ b/mysql-test/main/sp-security.result @@ -710,6 +710,32 @@ connection default; disconnect con2; DROP USER user2@localhost; DROP DATABASE db1; +create user foo@local_ost; +create user foo@`local\_ost`; +update mysql.user set plugin='foobar' where host='local\\_ost'; +flush privileges; +create database foodb; +grant create routine on foodb.* to foo@local_ost; +connect con1,localhost,foo; +select user(), current_user(); +user() current_user() +foo@localhost foo@local_ost +show grants; +Grants for foo@local_ost +GRANT USAGE ON *.* TO 'foo'@'local_ost' +GRANT CREATE ROUTINE ON `foodb`.* TO 'foo'@'local_ost' +create procedure fooproc() select 'i am fooproc'; +show grants; +Grants for foo@local_ost +GRANT USAGE ON *.* TO 'foo'@'local_ost' +GRANT CREATE ROUTINE ON `foodb`.* TO 'foo'@'local_ost' +GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `test`.`fooproc` TO 'foo'@'local_ost' +disconnect con1; +connection default; +drop user foo@local_ost; +drop user foo@`local\_ost`; +drop procedure fooproc; +drop database foodb; # # Test for bug#12602983 - User without privilege on routine can discover # its existence by executing "select non_existing_func();" or by diff --git a/mysql-test/main/sp-security.test b/mysql-test/main/sp-security.test index 73d0263dd69..4f645ce2cd3 100644 --- a/mysql-test/main/sp-security.test +++ b/mysql-test/main/sp-security.test @@ -973,6 +973,34 @@ disconnect con2; DROP USER user2@localhost; DROP DATABASE db1; +# +# Bug#27407480: AUTOMATIC_SP_PRIVILEGES REQUIRES NEED THE INSERT PRIVILEGES FOR MYSQL.USER TABLE +# +create user foo@local_ost; +# +# Create a user with an authentification plugin 'foobar'. +# Instead of using a normal "CREATE USER <user> IDENTIFIED VIA <plugin>" +# we do CREATE (without VIA) followed by UPDATE and FLUSH. +# This is to avoid installing a real plugin and thus avoid the test dependency. +# We won't login under this user in the below test, so this is fine. +# +create user foo@`local\_ost`; +update mysql.user set plugin='foobar' where host='local\\_ost'; +flush privileges; +create database foodb; +grant create routine on foodb.* to foo@local_ost; +connect con1,localhost,foo; +select user(), current_user(); +show grants; +create procedure fooproc() select 'i am fooproc'; +show grants; +disconnect con1; +connection default; +drop user foo@local_ost; +drop user foo@`local\_ost`; +drop procedure fooproc; +drop database foodb; + --echo # --echo # Test for bug#12602983 - User without privilege on routine can discover --echo # its existence by executing "select non_existing_func();" or by diff --git a/mysql-test/main/sp.result b/mysql-test/main/sp.result index 4315cc60925..128dccc58eb 100644 --- a/mysql-test/main/sp.result +++ b/mysql-test/main/sp.result @@ -7910,6 +7910,23 @@ SET S.CLOSE_YN = '' where 1=1; drop function if exists f1; drop table t1,t2; +# +# MDEV-16957: Server crashes in Field_iterator_natural_join::next +# upon 2nd execution of SP +# +CREATE TABLE t1 (a INT, b VARCHAR(32)); +CREATE PROCEDURE sp() SELECT * FROM t1 AS t1x JOIN t1 AS t1y USING (c); +CALL sp; +ERROR 42S22: Unknown column 'c' in 'from clause' +CALL sp; +ERROR 42S22: Unknown column 'c' in 'from clause' +CALL sp; +ERROR 42S22: Unknown column 'c' in 'from clause' +alter table t1 add column c int; +CALL sp; +c a b a b +DROP PROCEDURE sp; +DROP TABLE t1; # End of 5.5 test # # MDEV-7040: Crash in field_conv, memcpy_field_possible, part#2 diff --git a/mysql-test/main/sp.test b/mysql-test/main/sp.test index c4d85f63498..72e6ad6bcba 100644 --- a/mysql-test/main/sp.test +++ b/mysql-test/main/sp.test @@ -9344,6 +9344,27 @@ where 1=1; drop function if exists f1; drop table t1,t2; +--echo # +--echo # MDEV-16957: Server crashes in Field_iterator_natural_join::next +--echo # upon 2nd execution of SP +--echo # + +CREATE TABLE t1 (a INT, b VARCHAR(32)); +CREATE PROCEDURE sp() SELECT * FROM t1 AS t1x JOIN t1 AS t1y USING (c); +--error ER_BAD_FIELD_ERROR +CALL sp; +--error ER_BAD_FIELD_ERROR +CALL sp; +--error ER_BAD_FIELD_ERROR +CALL sp; +alter table t1 add column c int; +CALL sp; + +# Cleanup +DROP PROCEDURE sp; +DROP TABLE t1; + + --echo # End of 5.5 test --echo # diff --git a/mysql-test/main/stat_tables.result b/mysql-test/main/stat_tables.result index ceadb61feea..40290ca9879 100644 --- a/mysql-test/main/stat_tables.result +++ b/mysql-test/main/stat_tables.result @@ -578,57 +578,17 @@ db_name table_name column_name min_value max_value nulls_ratio avg_length avg_fr DROP TABLE t1; set use_stat_tables=@save_use_stat_tables; # -# MDEV-16757: manual addition of min/max statistics for BLOB +# MDEV-17023: Crash during read_histogram_for_table with optimizer_use_condition_selectivity set to 4 # -SET use_stat_tables= PREFERABLY; -CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT); -INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); -ANALYZE TABLE t1; -Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -test.t1 analyze Warning Engine-independent statistics are not collected for column 't' -test.t1 analyze status OK -SELECT * FROM mysql.column_stats; -db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram -test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL -DELETE FROM mysql.column_stats -WHERE db_name='test' AND table_name='t1' AND column_name='t'; -INSERT INTO mysql.column_stats VALUES -('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL); -SELECT * FROM mysql.column_stats; -db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram -test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL -test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL -SELECT pk FROM t1; -pk -1 -2 -DROP TABLE t1; -set use_stat_tables=@save_use_stat_tables; -# -# MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE -# -SET use_stat_tables= PREFERABLY; -CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32)); -INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); -ANALYZE TABLE t1; -Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -test.t1 analyze status OK -SELECT * FROM t1; -pk c -1 foo -2 bar -SELECT * FROM mysql.column_stats; -db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram -test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL -test t1 c bar foo 0.0000 3.0000 1.0000 0 NULL NULL -CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7)); -SELECT * FROM t1; -pk a -SELECT * FROM mysql.column_stats; -db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram -DROP TABLE t1; +set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; +set @@optimizer_use_condition_selectivity=4; +set @@use_stat_tables= PREFERABLY; +explain +SELECT * FROM INFORMATION_SCHEMA.PROFILING, mysql.user; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE PROFILING ALL NULL NULL NULL NULL NULL +1 SIMPLE user ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join) +set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; set use_stat_tables=@save_use_stat_tables; # # MDEV-16711:CREATE OR REPLACE TABLE introducing BLOB column diff --git a/mysql-test/main/stat_tables.test b/mysql-test/main/stat_tables.test index 2c9c1eca7d3..85a10987300 100644 --- a/mysql-test/main/stat_tables.test +++ b/mysql-test/main/stat_tables.test @@ -358,50 +358,17 @@ DROP TABLE t1; set use_stat_tables=@save_use_stat_tables; --echo # ---echo # MDEV-16757: manual addition of min/max statistics for BLOB ---echo # - -SET use_stat_tables= PREFERABLY; - -CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT); -INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); -ANALYZE TABLE t1; ---sorted_result -SELECT * FROM mysql.column_stats; -DELETE FROM mysql.column_stats - WHERE db_name='test' AND table_name='t1' AND column_name='t'; -INSERT INTO mysql.column_stats VALUES - ('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL); ---sorted_result -SELECT * FROM mysql.column_stats; - -SELECT pk FROM t1; - -DROP TABLE t1; - -set use_stat_tables=@save_use_stat_tables; - ---echo # ---echo # MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE +--echo # MDEV-17023: Crash during read_histogram_for_table with optimizer_use_condition_selectivity set to 4 --echo # -SET use_stat_tables= PREFERABLY; - -CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32)); -INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); -ANALYZE TABLE t1; -SELECT * FROM t1; -SELECT * FROM mysql.column_stats; - -CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7)); -SELECT * FROM t1; -SELECT * FROM mysql.column_stats; - -DROP TABLE t1; - +set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; +set @@optimizer_use_condition_selectivity=4; +set @@use_stat_tables= PREFERABLY; +explain +SELECT * FROM INFORMATION_SCHEMA.PROFILING, mysql.user; +set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; set use_stat_tables=@save_use_stat_tables; - --echo # --echo # MDEV-16711:CREATE OR REPLACE TABLE introducing BLOB column --echo # diff --git a/mysql-test/main/stat_tables_innodb.result b/mysql-test/main/stat_tables_innodb.result index 7b98ca4259f..070d13d9bb1 100644 --- a/mysql-test/main/stat_tables_innodb.result +++ b/mysql-test/main/stat_tables_innodb.result @@ -605,57 +605,17 @@ db_name table_name column_name min_value max_value nulls_ratio avg_length avg_fr DROP TABLE t1; set use_stat_tables=@save_use_stat_tables; # -# MDEV-16757: manual addition of min/max statistics for BLOB +# MDEV-17023: Crash during read_histogram_for_table with optimizer_use_condition_selectivity set to 4 # -SET use_stat_tables= PREFERABLY; -CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT); -INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); -ANALYZE TABLE t1; -Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -test.t1 analyze Warning Engine-independent statistics are not collected for column 't' -test.t1 analyze status OK -SELECT * FROM mysql.column_stats; -db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram -test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL -DELETE FROM mysql.column_stats -WHERE db_name='test' AND table_name='t1' AND column_name='t'; -INSERT INTO mysql.column_stats VALUES -('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL); -SELECT * FROM mysql.column_stats; -db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram -test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL -test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL -SELECT pk FROM t1; -pk -1 -2 -DROP TABLE t1; -set use_stat_tables=@save_use_stat_tables; -# -# MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE -# -SET use_stat_tables= PREFERABLY; -CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32)); -INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); -ANALYZE TABLE t1; -Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -test.t1 analyze status OK -SELECT * FROM t1; -pk c -1 foo -2 bar -SELECT * FROM mysql.column_stats; -db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram -test t1 pk 1 2 0.0000 4.0000 1.0000 0 NULL NULL -test t1 c bar foo 0.0000 3.0000 1.0000 0 NULL NULL -CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7)); -SELECT * FROM t1; -pk a -SELECT * FROM mysql.column_stats; -db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram -DROP TABLE t1; +set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; +set @@optimizer_use_condition_selectivity=4; +set @@use_stat_tables= PREFERABLY; +explain +SELECT * FROM INFORMATION_SCHEMA.PROFILING, mysql.user; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE PROFILING ALL NULL NULL NULL NULL NULL +1 SIMPLE user ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join) +set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; set use_stat_tables=@save_use_stat_tables; # # MDEV-16711:CREATE OR REPLACE TABLE introducing BLOB column diff --git a/mysql-test/main/type_float.result b/mysql-test/main/type_float.result index 57cdd1561df..8743b6c2b42 100644 --- a/mysql-test/main/type_float.result +++ b/mysql-test/main/type_float.result @@ -448,6 +448,46 @@ select format(truncate('1.7976931348623157E+308',-12),1,'fr_BE') as foo; foo 0 # +# MDEV-17249 MAKETIME(-1e50,0,0) returns a wrong result +# +SELECT LEFT('a',EXP(50)); +LEFT('a',EXP(50)) +a +SELECT LEFT('a', COALESCE(1e30)); +LEFT('a', COALESCE(1e30)) +a +CREATE TABLE t1 (a FLOAT); +INSERT INTO t1 VALUES (1e30); +SELECT LEFT('a',a), LEFT('a',1e30) FROM t1; +LEFT('a',a) LEFT('a',1e30) +a a +DROP TABLE t1; +PREPARE stmt FROM 'SELECT LEFT(111,?)'; +SET @a=1e30; +EXECUTE stmt USING @a; +LEFT(111,?) +111 +DEALLOCATE PREPARE stmt; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2),(3); +SELECT LEFT('a',(SELECT 1e30 FROM t1 LIMIT 1)); +LEFT('a',(SELECT 1e30 FROM t1 LIMIT 1)) +a +DROP TABLE t1; +CREATE TABLE t1 (a DOUBLE); +INSERT INTO t1 VALUES (1e30),(0); +SELECT LEFT('a', SUM(a)) FROM t1; +LEFT('a', SUM(a)) +a +Warnings: +Warning 1916 Got overflow when converting '1e30' to INT. Value truncated +SELECT LEFT('a', AVG(a)) FROM t1; +LEFT('a', AVG(a)) +a +Warnings: +Warning 1916 Got overflow when converting '5e29' to INT. Value truncated +DROP TABLE t1; +# # Bug #13500371 63704: CONVERSION OF '1.' TO A NUMBER GIVES ERROR 1265 # (WARN_DATA_TRUNCATED) # diff --git a/mysql-test/main/type_float.test b/mysql-test/main/type_float.test index 2d7c4428507..9dba1c709d5 100644 --- a/mysql-test/main/type_float.test +++ b/mysql-test/main/type_float.test @@ -332,6 +332,36 @@ eval select concat((truncate((-1.7976931348623157E+307),(0x1e))), select format(truncate('1.7976931348623157E+308',-12),1,'fr_BE') as foo; + +--echo # +--echo # MDEV-17249 MAKETIME(-1e50,0,0) returns a wrong result +--echo # + +SELECT LEFT('a',EXP(50)); +SELECT LEFT('a', COALESCE(1e30)); + +CREATE TABLE t1 (a FLOAT); +INSERT INTO t1 VALUES (1e30); +SELECT LEFT('a',a), LEFT('a',1e30) FROM t1; +DROP TABLE t1; + +PREPARE stmt FROM 'SELECT LEFT(111,?)'; +SET @a=1e30; +EXECUTE stmt USING @a; +DEALLOCATE PREPARE stmt; + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2),(3); +SELECT LEFT('a',(SELECT 1e30 FROM t1 LIMIT 1)); +DROP TABLE t1; + +CREATE TABLE t1 (a DOUBLE); +INSERT INTO t1 VALUES (1e30),(0); +SELECT LEFT('a', SUM(a)) FROM t1; +SELECT LEFT('a', AVG(a)) FROM t1; +DROP TABLE t1; + + --echo # --echo # Bug #13500371 63704: CONVERSION OF '1.' TO A NUMBER GIVES ERROR 1265 --echo # (WARN_DATA_TRUNCATED) diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index 6586e51dcd2..09738ddd244 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -1456,7 +1456,7 @@ sub command_line_setup { foreach my $fs (@tmpfs_locations) { - if ( -d $fs && ! -l $fs ) + if ( -d $fs && ! -l $fs && -w $fs ) { my $template= "var_${opt_build_thread}_XXXX"; $opt_mem= tempdir( $template, DIR => $fs, CLEANUP => 0); @@ -3992,14 +3992,14 @@ sub run_testcase ($$) { } my $test= $tinfo->{suite}->start_test($tinfo); - # Set only when we have to keep waiting after expectedly died server - my $keep_waiting_proc = 0; + # Set to a list of processes we have to keep waiting (expectedly died servers) + my %keep_waiting_proc = (); my $print_timeout= start_timer($print_freq * 60); while (1) { my $proc; - if ($keep_waiting_proc) + if (%keep_waiting_proc) { # Any other process exited? $proc = My::SafeProcess->check_any(); @@ -4009,48 +4009,34 @@ sub run_testcase ($$) { } else { - $proc = $keep_waiting_proc; # Also check if timer has expired, if so cancel waiting if ( has_expired($test_timeout) ) { - $keep_waiting_proc = 0; + %keep_waiting_proc = (); } } } - if (! $keep_waiting_proc) + if (!%keep_waiting_proc && !$proc) { - if($test_timeout > $print_timeout) + if ($test_timeout > $print_timeout) { - $proc= My::SafeProcess->wait_any_timeout($print_timeout); - if ( $proc->{timeout} ) - { - #print out that the test is still on - mtr_print("Test still running: $tinfo->{name}"); - #reset the timer - $print_timeout= start_timer($print_freq * 60); - next; - } + $proc= My::SafeProcess->wait_any_timeout($print_timeout); + if ($proc->{timeout}) + { + #print out that the test is still on + mtr_print("Test still running: $tinfo->{name}"); + #reset the timer + $print_timeout= start_timer($print_freq * 60); + next; + } } else { - $proc= My::SafeProcess->wait_any_timeout($test_timeout); + $proc= My::SafeProcess->wait_any_timeout($test_timeout); } } - # Will be restored if we need to keep waiting - $keep_waiting_proc = 0; - - unless ( defined $proc ) - { - mtr_error("wait_any failed"); - } - mtr_verbose("Got $proc"); - - mark_time_used('test'); - # ---------------------------------------------------- - # Was it the test program that exited - # ---------------------------------------------------- - if ($proc eq $test) + if ($proc and $proc eq $test) # mysqltest itself exited { my $res= $test->exit_status(); @@ -4065,12 +4051,12 @@ sub run_testcase ($$) { if ( $res == 0 ) { - my $check_res; - if ( $opt_check_testcases and - $check_res= check_testcase($tinfo, "after")) - { - if ($check_res == 1) { - # Test case had sideeffects, not fatal error, just continue + my $check_res; + if ( $opt_check_testcases and + $check_res= check_testcase($tinfo, "after")) + { + if ($check_res == 1) { + # Test case had sideeffects, not fatal error, just continue if ($opt_warnings) { # Checking error logs for warnings, so need to stop server # gracefully so that memory leaks etc. can be properly detected. @@ -4081,93 +4067,110 @@ sub run_testcase ($$) { # test. } else { # Not checking warnings, so can do a hard shutdown. - stop_all_servers($opt_shutdown_timeout); + stop_all_servers($opt_shutdown_timeout); } - mtr_report("Resuming tests...\n"); - resfile_output($tinfo->{'check'}) if $opt_resfile; - } - else { - # Test case check failed fatally, probably a server crashed - report_failure_and_restart($tinfo); - return 1; - } - } - mtr_report_test_passed($tinfo); + mtr_report("Resuming tests...\n"); + resfile_output($tinfo->{'check'}) if $opt_resfile; + } + else { + # Test case check failed fatally, probably a server crashed + report_failure_and_restart($tinfo); + return 1; + } + } + mtr_report_test_passed($tinfo); } elsif ( $res == 62 ) { - # Testcase itself tell us to skip this one - $tinfo->{skip_detected_by_test}= 1; - # Try to get reason from test log file - find_testcase_skipped_reason($tinfo); - mtr_report_test_skipped($tinfo); - # Restart if skipped due to missing perl, it may have had side effects - if ( $tinfo->{'comment'} =~ /^perl not found/ ) - { - stop_all_servers($opt_shutdown_timeout); - } + # Testcase itself tell us to skip this one + $tinfo->{skip_detected_by_test}= 1; + # Try to get reason from test log file + find_testcase_skipped_reason($tinfo); + mtr_report_test_skipped($tinfo); + # Restart if skipped due to missing perl, it may have had side effects + if ( $tinfo->{'comment'} =~ /^perl not found/ ) + { + stop_all_servers($opt_shutdown_timeout); + } } elsif ( $res == 65 ) { - # Testprogram killed by signal - $tinfo->{comment}= - "testprogram crashed(returned code $res)"; - report_failure_and_restart($tinfo); + # Testprogram killed by signal + $tinfo->{comment}= + "testprogram crashed(returned code $res)"; + report_failure_and_restart($tinfo); } elsif ( $res == 1 ) { - # Check if the test tool requests that - # an analyze script should be run - my $analyze= find_analyze_request(); - if ($analyze){ - run_on_all($tinfo, "analyze-$analyze"); - } + # Check if the test tool requests that + # an analyze script should be run + my $analyze= find_analyze_request(); + if ($analyze){ + run_on_all($tinfo, "analyze-$analyze"); + } - # Wait a bit and see if a server died, if so report that instead - mtr_milli_sleep(100); - my $srvproc= My::SafeProcess::check_any(); - if ($srvproc && grep($srvproc eq $_, started(all_servers()))) { - $proc= $srvproc; - goto SRVDIED; - } + # Wait a bit and see if a server died, if so report that instead + mtr_milli_sleep(100); + my $srvproc= My::SafeProcess::check_any(); + if ($srvproc && grep($srvproc eq $_, started(all_servers()))) { + $proc= $srvproc; + goto SRVDIED; + } - # Test case failure reported by mysqltest - report_failure_and_restart($tinfo); + # Test case failure reported by mysqltest + report_failure_and_restart($tinfo); } else { - # mysqltest failed, probably crashed - $tinfo->{comment}= - "mysqltest failed with unexpected return code $res\n"; - report_failure_and_restart($tinfo); + # mysqltest failed, probably crashed + $tinfo->{comment}= + "mysqltest failed with unexpected return code $res\n"; + report_failure_and_restart($tinfo); } # Save info from this testcase run to mysqltest.log if( -f $path_current_testlog) { - if ($opt_resfile && $res && $res != 62) { - resfile_output_file($path_current_testlog); - } - mtr_appendfile_to_file($path_current_testlog, $path_testlog); - unlink($path_current_testlog); + if ($opt_resfile && $res && $res != 62) { + resfile_output_file($path_current_testlog); + } + mtr_appendfile_to_file($path_current_testlog, $path_testlog); + unlink($path_current_testlog); } return ($res == 62) ? 0 : $res; - } - # ---------------------------------------------------- - # Check if it was an expected crash - # ---------------------------------------------------- - my $check_crash = check_expected_crash_and_restart($proc); - if ($check_crash) + if ($proc) { - # Keep waiting if it returned 2, if 1 don't wait or stop waiting. - $keep_waiting_proc = 0 if $check_crash == 1; - $keep_waiting_proc = $proc if $check_crash == 2; - next; + # It was not mysqltest that exited, add to a wait-to-be-started-again list. + $keep_waiting_proc{$proc} = 1; + } + + mtr_verbose("Got " . join(",", keys(%keep_waiting_proc))); + + mark_time_used('test'); + foreach my $wait_for_proc (keys(%keep_waiting_proc)) { + # ---------------------------------------------------- + # Check if it was an expected crash + # ---------------------------------------------------- + my $check_crash = check_expected_crash_and_restart($wait_for_proc); + if ($check_crash == 0) # unexpected exit/crash of $wait_for_proc + { + goto SRVDIED; + } + elsif ($check_crash == 1) # $wait_for_proc was started again by check_expected_crash_and_restart() + { + delete $keep_waiting_proc{$wait_for_proc}; + } + elsif ($check_crash == 2) # we must keep waiting + { + # do nothing + } } + next; + SRVDIED: # ---------------------------------------------------- # Stop the test case timer diff --git a/mysql-test/suite/binlog/include/binlog.test b/mysql-test/suite/binlog/include/binlog.test index 92fcb3a792b..40befc9d3d1 100644 --- a/mysql-test/suite/binlog/include/binlog.test +++ b/mysql-test/suite/binlog/include/binlog.test @@ -5,7 +5,6 @@ -- source include/have_log_bin.inc -- source include/not_embedded.inc -- source include/have_innodb.inc --- source include/have_debug.inc --disable_warnings drop table if exists t1, t2; diff --git a/mysql-test/suite/galera/disabled.def b/mysql-test/suite/galera/disabled.def index 0bc4da39991..840e69db71f 100644 --- a/mysql-test/suite/galera/disabled.def +++ b/mysql-test/suite/galera/disabled.def @@ -24,14 +24,11 @@ galera_gcache_recover : MDEV-13549 Galera test failures galera_gcache_recover_full_gcache : MDEV-13549 Galera test failures galera_gcache_recover_manytrx : MDEV-13549 Galera test failures galera_ssl_upgrade : MDEV-13549 Galera test failures -galera.MW-329 : wsrep_local_replays not stable -galera.MW-328A : have_deadlocks test not stable -query_cache : MDEV-15805 Test failure on galera.query_cache +MW-329 : wsrep_local_replays not stable MW-416 : MDEV-13549 Galera test failures -galera_wan : MDEV-13549 Galera test failures MW-388 : MDEV-13549 Galera test failures -galera.MW-44 : MDEV-15809 Test failure on galera.MW-44 -galera.galera_pc_ignore_sb : MDEV-15811 Test failure on galera_pc_ignore_sb +MW-44 : MDEV-15809 Test failure on galera.MW-44 +galera_pc_ignore_sb : MDEV-15811 Test failure on galera_pc_ignore_sb galera_kill_applier : race condition at the start of the test galera_ist_progress: MDEV-15236 galera_ist_progress fails when trying to read transfer status pxc-421: Lock timeout exceeded @@ -41,5 +38,12 @@ MW-328C : Timeouts galera_gcs_fc_limit : Timeouts pool_of_threads: WSREP has not yet prepared node for application use galera_var_innodb_disallow_writes : Timeout -galera.galera_kill_ddl : MDEV-17108 Test failure on galera.galera_kill_ddl MW-336 : nondeterministic wsrep_thread_count +galera_binlog_stmt_autoinc : MDEV-17106 Test failure on galera.galera_binlog_stmt_autoinc +galera_kill_ddl : MDEV-17108 Test failure on galera.galera_kill_ddl +galera_var_node_address : MDEV-17151 Galera test failure on galera.galera_var_node_address +galera_binlog_stmt_autoinc: MDEV-17106 Test failure on galera.galera_binlog_stmt_autoinc +galera_gc_fc_limit : MDEV-17061 Test failure on galera.galera_gc_fc_limit +partition : MDEV-13881 galera.partition failed in buildbot with wrong result +galera_as_slave_replication_budle : MDEV-15785 Test case galera_as_slave_replication_bundle caused debug assertion +galera_wan : MDEV-17259: Test failure on galera.galera_wan diff --git a/mysql-test/suite/galera/include/reset_query_cache.inc b/mysql-test/suite/galera/include/reset_query_cache.inc new file mode 100644 index 00000000000..6e33b886f08 --- /dev/null +++ b/mysql-test/suite/galera/include/reset_query_cache.inc @@ -0,0 +1,11 @@ +--disable_query_log +--disable_result_log +--connection node_1 +flush query cache; +reset query cache; + +--connection node_2 +flush query cache; +reset query cache; +--enable_result_log +--enable_query_log diff --git a/mysql-test/suite/galera/r/MW-286.result b/mysql-test/suite/galera/r/MW-286.result index adc996c1cbe..f3bef6f7516 100644 --- a/mysql-test/suite/galera/r/MW-286.result +++ b/mysql-test/suite/galera/r/MW-286.result @@ -1,13 +1,15 @@ -CREATE TABLE ten (f1 INTEGER); +connection node_1; +CREATE TABLE ten (f1 INTEGER) Engine=InnoDB; INSERT INTO ten VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); CREATE TABLE t1 (f1 INTEGER) Engine=InnoDB; INSERT INTO t1 (f1) SELECT 000000 + (10000 * a1.f1) + (1000 * a2.f1) + (100 * a3.f1) + (10 * a4.f1) + a5.f1 FROM ten AS a1, ten AS a2, ten AS a3, ten AS a4, ten AS a5; INSERT INTO t1 (f1) SELECT 100000 + (10000 * a1.f1) + (1000 * a2.f1) + (100 * a3.f1) + (10 * a4.f1) + a5.f1 FROM ten AS a1, ten AS a2, ten AS a3, ten AS a4, ten AS a5;; +connection node_2; SET GLOBAL wsrep_desync = TRUE; SET wsrep_on = FALSE; ALTER TABLE t1 ADD PRIMARY KEY (f1); -ERROR 70100: Query execution was interrupted SET wsrep_on = TRUE; SET GLOBAL wsrep_desync = FALSE; +connection node_1; DROP TABLE t1; DROP TABLE ten; diff --git a/mysql-test/suite/galera/r/MW-336.result b/mysql-test/suite/galera/r/MW-336.result index 1b5d2984e39..e35044d872b 100644 --- a/mysql-test/suite/galera/r/MW-336.result +++ b/mysql-test/suite/galera/r/MW-336.result @@ -1,7 +1,9 @@ CREATE TABLE t1 (f1 INTEGER) Engine=InnoDB; +INSERT INTO t1 values(0); connection node_1; SET GLOBAL wsrep_slave_threads = 10; SET GLOBAL wsrep_slave_threads = 1; +<<<<<<< HEAD SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'system user' AND (STATE IS NULL OR STATE NOT LIKE 'InnoDB%'); COUNT(*) 11 @@ -20,11 +22,23 @@ INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (1); +======= +# Wait 10 slave threads to start 1 +connection node_2; +# Generate 12 replication events +>>>>>>> 10.2 connection node_1; +SELECT COUNT(*) FROM t1; +COUNT(*) +13 +# Wait 9 slave threads to exit 1 SET GLOBAL wsrep_slave_threads = 10; +# Wait 10 slave threads to start 2 SET GLOBAL wsrep_slave_threads = 20; +# Wait 20 slave threads to start 3 SET GLOBAL wsrep_slave_threads = 1; connection node_2; +<<<<<<< HEAD INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (1); @@ -46,11 +60,21 @@ INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (1); +======= +# Generate 40 replication events +connection node_1; +SELECT COUNT(*) FROM t1; +COUNT(*) +53 +# Wait 10 slave threads to exit 3 +>>>>>>> 10.2 SET GLOBAL wsrep_slave_threads = 10; SET GLOBAL wsrep_slave_threads = 0; Warnings: Warning 1292 Truncated incorrect wsrep_slave_threads value: '0' +# Wait 10 slave threads to start 3 connection node_2; +<<<<<<< HEAD INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (1); @@ -97,4 +121,13 @@ INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (1); connection node_1; +======= +# Generate 12 replication events +connection node_1; +SELECT COUNT(*) FROM t1; +COUNT(*) +65 +# Wait 10 slave threads to exit 4 +connection node_1; +>>>>>>> 10.2 DROP TABLE t1; diff --git a/mysql-test/suite/galera/r/MW-44.result b/mysql-test/suite/galera/r/MW-44.result index a1e55318422..a07719daca1 100644 --- a/mysql-test/suite/galera/r/MW-44.result +++ b/mysql-test/suite/galera/r/MW-44.result @@ -1,20 +1,19 @@ -SET GLOBAL general_log='OFF'; TRUNCATE TABLE mysql.general_log; -SET GLOBAL general_log='OFF'; TRUNCATE TABLE mysql.general_log; +SELECT Argument FROM mysql.general_log; +Argument SET GLOBAL general_log='ON'; -SELECT argument from mysql.general_log WHERE argument NOT LIKE 'SELECT%'; -argument SET SESSION wsrep_osu_method=TOI; CREATE TABLE t1 (f1 INTEGER) ENGINE=InnoDB; SET SESSION wsrep_osu_method=RSU; ALTER TABLE t1 ADD COLUMN f2 INTEGER; SET SESSION wsrep_osu_method=TOI; -SELECT COUNT(*) = 2 FROM mysql.general_log WHERE argument LIKE 'CREATE%' OR argument LIKE 'ALTER%'; -COUNT(*) = 2 -1 -SET GLOBAL general_log='ON'; -SELECT COUNT(*) = 0 FROM mysql.general_log WHERE argument NOT LIKE 'SELECT%'; -COUNT(*) = 0 -1 +SELECT argument FROM mysql.general_log WHERE argument LIKE 'CREATE%' OR argument LIKE 'ALTER%'; +argument +CREATE TABLE t1 (f1 INTEGER) ENGINE=InnoDB +ALTER TABLE t1 ADD COLUMN f2 INTEGER +SELECT Argument FROM mysql.general_log; +Argument DROP TABLE t1; +SET GLOBAL general_log='OFF'; +SET GLOBAL general_log='OFF'; diff --git a/mysql-test/suite/galera/r/galera#505.result b/mysql-test/suite/galera/r/galera#505.result new file mode 100644 index 00000000000..8d3e3ec072a --- /dev/null +++ b/mysql-test/suite/galera/r/galera#505.result @@ -0,0 +1,8 @@ +connection node_1; +SET SESSION wsrep_sync_wait=0; +SET SESSION wsrep_sync_wait=DEFAULT; +SET GLOBAL wsrep_provider_options = 'pc.weight=3'; +SHOW GLOBAL VARIABLES LIKE 'wsrep_provider_options'; +Variable_name Value +wsrep_provider_options pc.weight = 3 +SET GLOBAL wsrep_provider_options = 'pc.weight=1'; diff --git a/mysql-test/suite/galera/r/galera_binlog_stmt_autoinc.result b/mysql-test/suite/galera/r/galera_binlog_stmt_autoinc.result new file mode 100644 index 00000000000..8e8b79b168f --- /dev/null +++ b/mysql-test/suite/galera/r/galera_binlog_stmt_autoinc.result @@ -0,0 +1,147 @@ +SET GLOBAL wsrep_forced_binlog_format='STATEMENT'; +SET GLOBAL wsrep_forced_binlog_format='STATEMENT'; +CREATE TABLE t1 ( +i int(11) NOT NULL AUTO_INCREMENT, +c char(32) DEFAULT 'dummy_text', +PRIMARY KEY (i) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +insert into t1(i) values(null); +select * from t1; +i c +3 dummy_text +insert into t1(i) values(null), (null), (null); +select * from t1; +i c +3 dummy_text +5 dummy_text +7 dummy_text +9 dummy_text +select * from t1; +i c +3 dummy_text +5 dummy_text +7 dummy_text +9 dummy_text +SET GLOBAL wsrep_forced_binlog_format='none'; +SET GLOBAL wsrep_forced_binlog_format='none'; +drop table t1; +SET SESSION binlog_format='STATEMENT'; +show variables like 'binlog_format'; +Variable_name Value +binlog_format STATEMENT +SET GLOBAL wsrep_auto_increment_control='OFF'; +SET SESSION auto_increment_increment = 3; +SET SESSION auto_increment_offset = 1; +CREATE TABLE t1 ( +i int(11) NOT NULL AUTO_INCREMENT, +c char(32) DEFAULT 'dummy_text', +PRIMARY KEY (i) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +insert into t1(i) values(null); +select * from t1; +i c +4 dummy_text +insert into t1(i) values(null), (null), (null); +select * from t1; +i c +4 dummy_text +7 dummy_text +10 dummy_text +13 dummy_text +select * from t1; +i c +4 dummy_text +7 dummy_text +10 dummy_text +13 dummy_text +SET GLOBAL wsrep_auto_increment_control='ON'; +SET SESSION binlog_format='ROW'; +show variables like 'binlog_format'; +Variable_name Value +binlog_format ROW +show variables like '%auto_increment%'; +Variable_name Value +auto_increment_increment 2 +auto_increment_offset 1 +wsrep_auto_increment_control ON +SET GLOBAL wsrep_auto_increment_control='OFF'; +show variables like '%auto_increment%'; +Variable_name Value +auto_increment_increment 2 +auto_increment_offset 1 +wsrep_auto_increment_control OFF +SET GLOBAL wsrep_auto_increment_control='ON'; +drop table t1; +SET GLOBAL wsrep_forced_binlog_format='ROW'; +SET GLOBAL wsrep_forced_binlog_format='ROW'; +CREATE TABLE t1 ( +i int(11) NOT NULL AUTO_INCREMENT, +c char(32) DEFAULT 'dummy_text', +PRIMARY KEY (i) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +insert into t1(i) values(null); +select * from t1; +i c +3 dummy_text +insert into t1(i) values(null), (null), (null); +select * from t1; +i c +3 dummy_text +5 dummy_text +7 dummy_text +9 dummy_text +select * from t1; +i c +3 dummy_text +5 dummy_text +7 dummy_text +9 dummy_text +SET GLOBAL wsrep_forced_binlog_format='none'; +SET GLOBAL wsrep_forced_binlog_format='none'; +drop table t1; +SET SESSION binlog_format='ROW'; +show variables like 'binlog_format'; +Variable_name Value +binlog_format ROW +SET GLOBAL wsrep_auto_increment_control='OFF'; +SET SESSION auto_increment_increment = 3; +SET SESSION auto_increment_offset = 1; +CREATE TABLE t1 ( +i int(11) NOT NULL AUTO_INCREMENT, +c char(32) DEFAULT 'dummy_text', +PRIMARY KEY (i) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +insert into t1(i) values(null); +select * from t1; +i c +4 dummy_text +insert into t1(i) values(null), (null), (null); +select * from t1; +i c +4 dummy_text +7 dummy_text +10 dummy_text +13 dummy_text +select * from t1; +i c +4 dummy_text +7 dummy_text +10 dummy_text +13 dummy_text +SET GLOBAL wsrep_auto_increment_control='ON'; +show variables like 'binlog_format'; +Variable_name Value +binlog_format ROW +show variables like '%auto_increment%'; +Variable_name Value +auto_increment_increment 2 +auto_increment_offset 1 +wsrep_auto_increment_control ON +SET GLOBAL wsrep_auto_increment_control='OFF'; +show variables like '%auto_increment%'; +Variable_name Value +auto_increment_increment 2 +auto_increment_offset 1 +wsrep_auto_increment_control OFF +SET GLOBAL wsrep_auto_increment_control='ON'; +drop table t1; diff --git a/mysql-test/suite/galera/r/galera_defaults.result b/mysql-test/suite/galera/r/galera_defaults.result index 5c5fdabf432..8083784bb01 100644 --- a/mysql-test/suite/galera/r/galera_defaults.result +++ b/mysql-test/suite/galera/r/galera_defaults.result @@ -53,71 +53,3 @@ WSREP_SST_DONOR_REJECTS_QUERIES OFF WSREP_SST_METHOD rsync WSREP_SYNC_WAIT 15 <BASE_DIR>; <BASE_HOST>; <BASE_PORT>; cert.log_conflicts = no; debug = no; evs.auto_evict = 0; evs.causal_keepalive_period = PT1S; evs.debug_log_mask = 0x1; evs.delay_margin = PT1S; evs.delayed_keep_period = PT30S; evs.inactive_check_period = PT0.5S; evs.inactive_timeout = PT30S; evs.info_log_mask = 0; evs.install_timeout = PT15S; evs.join_retrans_period = PT1S; evs.keepalive_period = PT1S; evs.max_install_timeouts = 3; evs.send_window = 4; evs.stats_report_period = PT1M; evs.suspect_timeout = PT10S; evs.use_aggregate = true; evs.user_send_window = 2; evs.version = 0; evs.view_forget_timeout = P1D; <GCACHE_DIR>; gcache.keep_pages_size = 0; gcache.mem_size = 0; <GCACHE_NAME>; gcache.page_size = 128M; gcache.recover = no; gcache.size = 10M; gcomm.thread_prio = ; gcs.fc_debug = 0; gcs.fc_factor = 1.0; gcs.fc_limit = 16; gcs.fc_master_slave = no; gcs.max_packet_size = 64500; gcs.max_throttle = 0.25; <GCS_RECV_Q_HARD_LIMIT>; gcs.recv_q_soft_limit = 0.25; gcs.sync_donor = no; <GMCAST_LISTEN_ADDR>; gmcast.mcast_addr = ; gmcast.mcast_ttl = 1; gmcast.peer_timeout = PT3S; gmcast.segment = 0; gmcast.time_wait = PT5S; gmcast.version = 0; <IST_RECV_ADDR>; pc.announce_timeout = PT3S; pc.checksum = false; pc.ignore_quorum = false; pc.ignore_sb = false; pc.linger = PT20S; pc.npvo = false; pc.recovery = true; pc.version = 0; pc.wait_prim = true; pc.wait_prim_timeout = PT30S; pc.weight = 1; protonet.backend = asio; protonet.version = 0; repl.causal_read_timeout = PT90S; repl.commit_order = 3; repl.key_format = FLAT8; repl.max_ws_size = 2147483647; <REPL_PROTO_MAX>;socket.checksum = 2; socket.recv_buf_size = 212992; -SELECT COUNT(*) FROM INFORMATION_SCHEMA.GLOBAL_STATUS -WHERE VARIABLE_NAME LIKE 'wsrep_%' -AND VARIABLE_NAME != 'wsrep_debug_sync_waiters'; -COUNT(*) -58 -SELECT VARIABLE_NAME FROM INFORMATION_SCHEMA.GLOBAL_STATUS -WHERE VARIABLE_NAME LIKE 'wsrep_%' -AND VARIABLE_NAME != 'wsrep_debug_sync_waiters' -ORDER BY VARIABLE_NAME; -VARIABLE_NAME -WSREP_APPLY_OOOE -WSREP_APPLY_OOOL -WSREP_APPLY_WINDOW -WSREP_CAUSAL_READS -WSREP_CERT_DEPS_DISTANCE -WSREP_CERT_INDEX_SIZE -WSREP_CERT_INTERVAL -WSREP_CLUSTER_CONF_ID -WSREP_CLUSTER_SIZE -WSREP_CLUSTER_STATE_UUID -WSREP_CLUSTER_STATUS -WSREP_COMMIT_OOOE -WSREP_COMMIT_OOOL -WSREP_COMMIT_WINDOW -WSREP_CONNECTED -WSREP_DESYNC_COUNT -WSREP_EVS_DELAYED -WSREP_EVS_EVICT_LIST -WSREP_EVS_REPL_LATENCY -WSREP_EVS_STATE -WSREP_FLOW_CONTROL_PAUSED -WSREP_FLOW_CONTROL_PAUSED_NS -WSREP_FLOW_CONTROL_RECV -WSREP_FLOW_CONTROL_SENT -WSREP_GCOMM_UUID -WSREP_INCOMING_ADDRESSES -WSREP_LAST_COMMITTED -WSREP_LOCAL_BF_ABORTS -WSREP_LOCAL_CACHED_DOWNTO -WSREP_LOCAL_CERT_FAILURES -WSREP_LOCAL_COMMITS -WSREP_LOCAL_INDEX -WSREP_LOCAL_RECV_QUEUE -WSREP_LOCAL_RECV_QUEUE_AVG -WSREP_LOCAL_RECV_QUEUE_MAX -WSREP_LOCAL_RECV_QUEUE_MIN -WSREP_LOCAL_REPLAYS -WSREP_LOCAL_SEND_QUEUE -WSREP_LOCAL_SEND_QUEUE_AVG -WSREP_LOCAL_SEND_QUEUE_MAX -WSREP_LOCAL_SEND_QUEUE_MIN -WSREP_LOCAL_STATE -WSREP_LOCAL_STATE_COMMENT -WSREP_LOCAL_STATE_UUID -WSREP_PROTOCOL_VERSION -WSREP_PROVIDER_NAME -WSREP_PROVIDER_VENDOR -WSREP_PROVIDER_VERSION -WSREP_READY -WSREP_RECEIVED -WSREP_RECEIVED_BYTES -WSREP_REPLICATED -WSREP_REPLICATED_BYTES -WSREP_REPL_DATA_BYTES -WSREP_REPL_KEYS -WSREP_REPL_KEYS_BYTES -WSREP_REPL_OTHER_BYTES -WSREP_THREAD_COUNT diff --git a/mysql-test/suite/galera/r/galera_ist_innodb_flush_logs,debug.rdiff b/mysql-test/suite/galera/r/galera_ist_innodb_flush_logs,debug.rdiff new file mode 100644 index 00000000000..fa1b67e7ef3 --- /dev/null +++ b/mysql-test/suite/galera/r/galera_ist_innodb_flush_logs,debug.rdiff @@ -0,0 +1,103 @@ +--- r/galera_ist_innodb_flush_logs.result 2018-09-05 10:34:36.192439933 +0300 ++++ r/galera_ist_innodb_flush_logs.reject 2018-09-17 10:20:06.039150838 +0300 +@@ -86,3 +86,100 @@ + DROP TABLE t1; + COMMIT; + SET AUTOCOMMIT=ON; ++Performing State Transfer on a server that has been killed and restarted ++while a DDL was in progress on it ++CREATE TABLE t1 (f1 CHAR(255)) ENGINE=InnoDB; ++SET AUTOCOMMIT=OFF; ++START TRANSACTION; ++INSERT INTO t1 VALUES ('node1_committed_before'); ++INSERT INTO t1 VALUES ('node1_committed_before'); ++INSERT INTO t1 VALUES ('node1_committed_before'); ++INSERT INTO t1 VALUES ('node1_committed_before'); ++INSERT INTO t1 VALUES ('node1_committed_before'); ++START TRANSACTION; ++INSERT INTO t1 VALUES ('node2_committed_before'); ++INSERT INTO t1 VALUES ('node2_committed_before'); ++INSERT INTO t1 VALUES ('node2_committed_before'); ++INSERT INTO t1 VALUES ('node2_committed_before'); ++INSERT INTO t1 VALUES ('node2_committed_before'); ++COMMIT; ++SET GLOBAL debug_dbug = 'd,sync.alter_opened_table'; ++ALTER TABLE t1 ADD COLUMN f2 INTEGER; ++SET wsrep_sync_wait = 0; ++Killing server ... ++SET AUTOCOMMIT=OFF; ++START TRANSACTION; ++INSERT INTO t1 (f1) VALUES ('node1_committed_during'); ++INSERT INTO t1 (f1) VALUES ('node1_committed_during'); ++INSERT INTO t1 (f1) VALUES ('node1_committed_during'); ++INSERT INTO t1 (f1) VALUES ('node1_committed_during'); ++INSERT INTO t1 (f1) VALUES ('node1_committed_during'); ++COMMIT; ++START TRANSACTION; ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++SET AUTOCOMMIT=OFF; ++START TRANSACTION; ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++Performing --wsrep-recover ... ++Starting server ... ++Using --wsrep-start-position when starting mysqld ... ++SET AUTOCOMMIT=OFF; ++START TRANSACTION; ++INSERT INTO t1 (f1) VALUES ('node2_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node2_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node2_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node2_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node2_committed_after'); ++COMMIT; ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++COMMIT; ++SET AUTOCOMMIT=OFF; ++START TRANSACTION; ++INSERT INTO t1 (f1) VALUES ('node1_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_committed_after'); ++COMMIT; ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++ROLLBACK; ++SELECT COUNT(*) = 2 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't1'; ++COUNT(*) = 2 ++1 ++SELECT COUNT(*) = 35 FROM t1; ++COUNT(*) = 35 ++1 ++SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; ++COUNT(*) = 0 ++1 ++COMMIT; ++SET AUTOCOMMIT=ON; ++SELECT COUNT(*) = 2 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't1'; ++COUNT(*) = 2 ++1 ++SELECT COUNT(*) = 35 FROM t1; ++COUNT(*) = 35 ++1 ++SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; ++COUNT(*) = 0 ++1 ++DROP TABLE t1; ++COMMIT; ++SET AUTOCOMMIT=ON; ++SET GLOBAL debug_dbug = $debug_orig; diff --git a/mysql-test/suite/galera/r/galera_ist_innodb_flush_logs.result b/mysql-test/suite/galera/r/galera_ist_innodb_flush_logs.result index 5421b234ee1..e3bf407ddaa 100644 --- a/mysql-test/suite/galera/r/galera_ist_innodb_flush_logs.result +++ b/mysql-test/suite/galera/r/galera_ist_innodb_flush_logs.result @@ -86,99 +86,3 @@ COUNT(*) = 0 DROP TABLE t1; COMMIT; SET AUTOCOMMIT=ON; -Performing State Transfer on a server that has been killed and restarted -while a DDL was in progress on it -CREATE TABLE t1 (f1 CHAR(255)) ENGINE=InnoDB; -SET AUTOCOMMIT=OFF; -START TRANSACTION; -INSERT INTO t1 VALUES ('node1_committed_before'); -INSERT INTO t1 VALUES ('node1_committed_before'); -INSERT INTO t1 VALUES ('node1_committed_before'); -INSERT INTO t1 VALUES ('node1_committed_before'); -INSERT INTO t1 VALUES ('node1_committed_before'); -START TRANSACTION; -INSERT INTO t1 VALUES ('node2_committed_before'); -INSERT INTO t1 VALUES ('node2_committed_before'); -INSERT INTO t1 VALUES ('node2_committed_before'); -INSERT INTO t1 VALUES ('node2_committed_before'); -INSERT INTO t1 VALUES ('node2_committed_before'); -COMMIT; -SET GLOBAL debug = 'd,sync.alter_opened_table'; -ALTER TABLE t1 ADD COLUMN f2 INTEGER; -SET wsrep_sync_wait = 0; -Killing server ... -SET AUTOCOMMIT=OFF; -START TRANSACTION; -INSERT INTO t1 (f1) VALUES ('node1_committed_during'); -INSERT INTO t1 (f1) VALUES ('node1_committed_during'); -INSERT INTO t1 (f1) VALUES ('node1_committed_during'); -INSERT INTO t1 (f1) VALUES ('node1_committed_during'); -INSERT INTO t1 (f1) VALUES ('node1_committed_during'); -COMMIT; -START TRANSACTION; -INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); -SET AUTOCOMMIT=OFF; -START TRANSACTION; -INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); -Performing --wsrep-recover ... -Starting server ... -Using --wsrep-start-position when starting mysqld ... -SET AUTOCOMMIT=OFF; -START TRANSACTION; -INSERT INTO t1 (f1) VALUES ('node2_committed_after'); -INSERT INTO t1 (f1) VALUES ('node2_committed_after'); -INSERT INTO t1 (f1) VALUES ('node2_committed_after'); -INSERT INTO t1 (f1) VALUES ('node2_committed_after'); -INSERT INTO t1 (f1) VALUES ('node2_committed_after'); -COMMIT; -INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); -COMMIT; -SET AUTOCOMMIT=OFF; -START TRANSACTION; -INSERT INTO t1 (f1) VALUES ('node1_committed_after'); -INSERT INTO t1 (f1) VALUES ('node1_committed_after'); -INSERT INTO t1 (f1) VALUES ('node1_committed_after'); -INSERT INTO t1 (f1) VALUES ('node1_committed_after'); -INSERT INTO t1 (f1) VALUES ('node1_committed_after'); -COMMIT; -INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); -ROLLBACK; -SELECT COUNT(*) = 2 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't1'; -COUNT(*) = 2 -1 -SELECT COUNT(*) = 35 FROM t1; -COUNT(*) = 35 -1 -SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; -COUNT(*) = 0 -1 -COMMIT; -SET AUTOCOMMIT=ON; -SELECT COUNT(*) = 2 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't1'; -COUNT(*) = 2 -1 -SELECT COUNT(*) = 35 FROM t1; -COUNT(*) = 35 -1 -SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; -COUNT(*) = 0 -1 -DROP TABLE t1; -COMMIT; -SET AUTOCOMMIT=ON; diff --git a/mysql-test/suite/galera/r/galera_ist_mysqldump,debug.rdiff b/mysql-test/suite/galera/r/galera_ist_mysqldump,debug.rdiff new file mode 100644 index 00000000000..74e6abd713f --- /dev/null +++ b/mysql-test/suite/galera/r/galera_ist_mysqldump,debug.rdiff @@ -0,0 +1,106 @@ +--- r/galera_ist_mysqldump.result 2018-09-11 12:38:42.027479411 +0300 ++++ r/galera_ist_mysqldump.reject 2018-09-17 10:28:44.483441364 +0300 +@@ -180,6 +180,103 @@ + DROP TABLE t1; + COMMIT; + SET AUTOCOMMIT=ON; ++Performing State Transfer on a server that has been killed and restarted ++while a DDL was in progress on it ++CREATE TABLE t1 (f1 CHAR(255)) ENGINE=InnoDB; ++SET AUTOCOMMIT=OFF; ++START TRANSACTION; ++INSERT INTO t1 VALUES ('node1_committed_before'); ++INSERT INTO t1 VALUES ('node1_committed_before'); ++INSERT INTO t1 VALUES ('node1_committed_before'); ++INSERT INTO t1 VALUES ('node1_committed_before'); ++INSERT INTO t1 VALUES ('node1_committed_before'); ++START TRANSACTION; ++INSERT INTO t1 VALUES ('node2_committed_before'); ++INSERT INTO t1 VALUES ('node2_committed_before'); ++INSERT INTO t1 VALUES ('node2_committed_before'); ++INSERT INTO t1 VALUES ('node2_committed_before'); ++INSERT INTO t1 VALUES ('node2_committed_before'); ++COMMIT; ++SET GLOBAL debug_dbug = 'd,sync.alter_opened_table'; ++ALTER TABLE t1 ADD COLUMN f2 INTEGER; ++SET wsrep_sync_wait = 0; ++Killing server ... ++SET AUTOCOMMIT=OFF; ++START TRANSACTION; ++INSERT INTO t1 (f1) VALUES ('node1_committed_during'); ++INSERT INTO t1 (f1) VALUES ('node1_committed_during'); ++INSERT INTO t1 (f1) VALUES ('node1_committed_during'); ++INSERT INTO t1 (f1) VALUES ('node1_committed_during'); ++INSERT INTO t1 (f1) VALUES ('node1_committed_during'); ++COMMIT; ++START TRANSACTION; ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++SET AUTOCOMMIT=OFF; ++START TRANSACTION; ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++Performing --wsrep-recover ... ++Starting server ... ++Using --wsrep-start-position when starting mysqld ... ++SET AUTOCOMMIT=OFF; ++START TRANSACTION; ++INSERT INTO t1 (f1) VALUES ('node2_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node2_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node2_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node2_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node2_committed_after'); ++COMMIT; ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++COMMIT; ++SET AUTOCOMMIT=OFF; ++START TRANSACTION; ++INSERT INTO t1 (f1) VALUES ('node1_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_committed_after'); ++COMMIT; ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++ROLLBACK; ++SELECT COUNT(*) = 2 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't1'; ++COUNT(*) = 2 ++1 ++SELECT COUNT(*) = 35 FROM t1; ++COUNT(*) = 35 ++1 ++SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; ++COUNT(*) = 0 ++1 ++COMMIT; ++SET AUTOCOMMIT=ON; ++SELECT COUNT(*) = 2 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't1'; ++COUNT(*) = 2 ++1 ++SELECT COUNT(*) = 35 FROM t1; ++COUNT(*) = 35 ++1 ++SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; ++COUNT(*) = 0 ++1 ++DROP TABLE t1; ++COMMIT; ++SET AUTOCOMMIT=ON; ++SET GLOBAL debug_dbug = $debug_orig; + CALL mtr.add_suppression("Slave SQL: Error 'The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement' on query"); + DROP USER sst; + CALL mtr.add_suppression("Slave SQL: Error 'The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement' on query"); diff --git a/mysql-test/suite/galera/r/galera_ist_mysqldump.result b/mysql-test/suite/galera/r/galera_ist_mysqldump.result index 58a3ca297f8..296ecc2adc7 100644 --- a/mysql-test/suite/galera/r/galera_ist_mysqldump.result +++ b/mysql-test/suite/galera/r/galera_ist_mysqldump.result @@ -7,6 +7,7 @@ GRANT ALL PRIVILEGES ON *.* TO 'sst'; SET GLOBAL wsrep_sst_auth = 'sst:'; connection node_2; SET GLOBAL wsrep_sst_method = 'mysqldump'; +call mtr.add_suppression("WSREP: wsrep_sst_method is set to 'mysqldump' yet mysqld bind_address is set to .*"); connection node_1; connection node_2; Performing State Transfer on a server that has been shut down cleanly and restarted @@ -199,114 +200,6 @@ COUNT(*) = 0 DROP TABLE t1; COMMIT; SET AUTOCOMMIT=ON; -Performing State Transfer on a server that has been killed and restarted -while a DDL was in progress on it -connection node_1; -CREATE TABLE t1 (f1 CHAR(255)) ENGINE=InnoDB; -SET AUTOCOMMIT=OFF; -START TRANSACTION; -INSERT INTO t1 VALUES ('node1_committed_before'); -INSERT INTO t1 VALUES ('node1_committed_before'); -INSERT INTO t1 VALUES ('node1_committed_before'); -INSERT INTO t1 VALUES ('node1_committed_before'); -INSERT INTO t1 VALUES ('node1_committed_before'); -connection node_2; -START TRANSACTION; -INSERT INTO t1 VALUES ('node2_committed_before'); -INSERT INTO t1 VALUES ('node2_committed_before'); -INSERT INTO t1 VALUES ('node2_committed_before'); -INSERT INTO t1 VALUES ('node2_committed_before'); -INSERT INTO t1 VALUES ('node2_committed_before'); -COMMIT; -SET GLOBAL debug_dbug = 'd,sync.alter_opened_table'; -connection node_1; -ALTER TABLE t1 ADD COLUMN f2 INTEGER; -connection node_2; -SET wsrep_sync_wait = 0; -Killing server ... -connection node_1; -SET AUTOCOMMIT=OFF; -START TRANSACTION; -INSERT INTO t1 (f1) VALUES ('node1_committed_during'); -INSERT INTO t1 (f1) VALUES ('node1_committed_during'); -INSERT INTO t1 (f1) VALUES ('node1_committed_during'); -INSERT INTO t1 (f1) VALUES ('node1_committed_during'); -INSERT INTO t1 (f1) VALUES ('node1_committed_during'); -COMMIT; -START TRANSACTION; -INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); -connect node_1a_galera_st_kill_slave_ddl, 127.0.0.1, root, , test, $NODE_MYPORT_1; -SET AUTOCOMMIT=OFF; -START TRANSACTION; -INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); -connection node_2; -Performing --wsrep-recover ... -connection node_2; -Starting server ... -Using --wsrep-start-position when starting mysqld ... -SET AUTOCOMMIT=OFF; -START TRANSACTION; -INSERT INTO t1 (f1) VALUES ('node2_committed_after'); -INSERT INTO t1 (f1) VALUES ('node2_committed_after'); -INSERT INTO t1 (f1) VALUES ('node2_committed_after'); -INSERT INTO t1 (f1) VALUES ('node2_committed_after'); -INSERT INTO t1 (f1) VALUES ('node2_committed_after'); -COMMIT; -connection node_1; -INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); -COMMIT; -SET AUTOCOMMIT=OFF; -START TRANSACTION; -INSERT INTO t1 (f1) VALUES ('node1_committed_after'); -INSERT INTO t1 (f1) VALUES ('node1_committed_after'); -INSERT INTO t1 (f1) VALUES ('node1_committed_after'); -INSERT INTO t1 (f1) VALUES ('node1_committed_after'); -INSERT INTO t1 (f1) VALUES ('node1_committed_after'); -COMMIT; -connection node_1a_galera_st_kill_slave_ddl; -INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); -ROLLBACK; -SELECT COUNT(*) = 2 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't1'; -COUNT(*) = 2 -1 -SELECT COUNT(*) = 35 FROM t1; -COUNT(*) = 35 -1 -SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; -COUNT(*) = 0 -1 -COMMIT; -SET AUTOCOMMIT=ON; -connection node_1; -SELECT COUNT(*) = 2 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't1'; -COUNT(*) = 2 -1 -SELECT COUNT(*) = 35 FROM t1; -COUNT(*) = 35 -1 -SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; -COUNT(*) = 0 -1 -DROP TABLE t1; -COMMIT; -SET AUTOCOMMIT=ON; -SET GLOBAL debug_dbug = $debug_orig; connection node_1; CALL mtr.add_suppression("Slave SQL: Error 'The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement' on query"); DROP USER sst; diff --git a/mysql-test/suite/galera/r/galera_ist_rsync,debug.rdiff b/mysql-test/suite/galera/r/galera_ist_rsync,debug.rdiff new file mode 100644 index 00000000000..9070acc76bf --- /dev/null +++ b/mysql-test/suite/galera/r/galera_ist_rsync,debug.rdiff @@ -0,0 +1,103 @@ +--- r/galera_ist_rsync.result 2018-09-11 12:38:42.027479411 +0300 ++++ r/galera_ist_rsync.reject 2018-09-17 10:50:16.527307668 +0300 +@@ -259,3 +259,100 @@ + DROP TABLE t1; + COMMIT; + SET AUTOCOMMIT=ON; ++Performing State Transfer on a server that has been killed and restarted ++while a DDL was in progress on it ++CREATE TABLE t1 (f1 CHAR(255)) ENGINE=InnoDB; ++SET AUTOCOMMIT=OFF; ++START TRANSACTION; ++INSERT INTO t1 VALUES ('node1_committed_before'); ++INSERT INTO t1 VALUES ('node1_committed_before'); ++INSERT INTO t1 VALUES ('node1_committed_before'); ++INSERT INTO t1 VALUES ('node1_committed_before'); ++INSERT INTO t1 VALUES ('node1_committed_before'); ++START TRANSACTION; ++INSERT INTO t1 VALUES ('node2_committed_before'); ++INSERT INTO t1 VALUES ('node2_committed_before'); ++INSERT INTO t1 VALUES ('node2_committed_before'); ++INSERT INTO t1 VALUES ('node2_committed_before'); ++INSERT INTO t1 VALUES ('node2_committed_before'); ++COMMIT; ++SET GLOBAL debug_dbug = 'd,sync.alter_opened_table'; ++ALTER TABLE t1 ADD COLUMN f2 INTEGER; ++SET wsrep_sync_wait = 0; ++Killing server ... ++SET AUTOCOMMIT=OFF; ++START TRANSACTION; ++INSERT INTO t1 (f1) VALUES ('node1_committed_during'); ++INSERT INTO t1 (f1) VALUES ('node1_committed_during'); ++INSERT INTO t1 (f1) VALUES ('node1_committed_during'); ++INSERT INTO t1 (f1) VALUES ('node1_committed_during'); ++INSERT INTO t1 (f1) VALUES ('node1_committed_during'); ++COMMIT; ++START TRANSACTION; ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++SET AUTOCOMMIT=OFF; ++START TRANSACTION; ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++Performing --wsrep-recover ... ++Starting server ... ++Using --wsrep-start-position when starting mysqld ... ++SET AUTOCOMMIT=OFF; ++START TRANSACTION; ++INSERT INTO t1 (f1) VALUES ('node2_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node2_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node2_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node2_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node2_committed_after'); ++COMMIT; ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++COMMIT; ++SET AUTOCOMMIT=OFF; ++START TRANSACTION; ++INSERT INTO t1 (f1) VALUES ('node1_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_committed_after'); ++COMMIT; ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++ROLLBACK; ++SELECT COUNT(*) = 2 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't1'; ++COUNT(*) = 2 ++1 ++SELECT COUNT(*) = 35 FROM t1; ++COUNT(*) = 35 ++1 ++SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; ++COUNT(*) = 0 ++1 ++COMMIT; ++SET AUTOCOMMIT=ON; ++SELECT COUNT(*) = 2 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't1'; ++COUNT(*) = 2 ++1 ++SELECT COUNT(*) = 35 FROM t1; ++COUNT(*) = 35 ++1 ++SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; ++COUNT(*) = 0 ++1 ++DROP TABLE t1; ++COMMIT; ++SET AUTOCOMMIT=ON; ++SET GLOBAL debug_dbug = $debug_orig; diff --git a/mysql-test/suite/galera/r/galera_ist_rsync.result b/mysql-test/suite/galera/r/galera_ist_rsync.result index 9c0d78d96e9..8a7c02ab1b6 100644 --- a/mysql-test/suite/galera/r/galera_ist_rsync.result +++ b/mysql-test/suite/galera/r/galera_ist_rsync.result @@ -285,111 +285,3 @@ COUNT(*) = 0 DROP TABLE t1; COMMIT; SET AUTOCOMMIT=ON; -Performing State Transfer on a server that has been killed and restarted -while a DDL was in progress on it -connection node_1; -CREATE TABLE t1 (f1 CHAR(255)) ENGINE=InnoDB; -SET AUTOCOMMIT=OFF; -START TRANSACTION; -INSERT INTO t1 VALUES ('node1_committed_before'); -INSERT INTO t1 VALUES ('node1_committed_before'); -INSERT INTO t1 VALUES ('node1_committed_before'); -INSERT INTO t1 VALUES ('node1_committed_before'); -INSERT INTO t1 VALUES ('node1_committed_before'); -connection node_2; -START TRANSACTION; -INSERT INTO t1 VALUES ('node2_committed_before'); -INSERT INTO t1 VALUES ('node2_committed_before'); -INSERT INTO t1 VALUES ('node2_committed_before'); -INSERT INTO t1 VALUES ('node2_committed_before'); -INSERT INTO t1 VALUES ('node2_committed_before'); -COMMIT; -SET GLOBAL debug_dbug = 'd,sync.alter_opened_table'; -connection node_1; -ALTER TABLE t1 ADD COLUMN f2 INTEGER; -connection node_2; -SET wsrep_sync_wait = 0; -Killing server ... -connection node_1; -SET AUTOCOMMIT=OFF; -START TRANSACTION; -INSERT INTO t1 (f1) VALUES ('node1_committed_during'); -INSERT INTO t1 (f1) VALUES ('node1_committed_during'); -INSERT INTO t1 (f1) VALUES ('node1_committed_during'); -INSERT INTO t1 (f1) VALUES ('node1_committed_during'); -INSERT INTO t1 (f1) VALUES ('node1_committed_during'); -COMMIT; -START TRANSACTION; -INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); -connect node_1a_galera_st_kill_slave_ddl, 127.0.0.1, root, , test, $NODE_MYPORT_1; -SET AUTOCOMMIT=OFF; -START TRANSACTION; -INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); -connection node_2; -Performing --wsrep-recover ... -connection node_2; -Starting server ... -Using --wsrep-start-position when starting mysqld ... -SET AUTOCOMMIT=OFF; -START TRANSACTION; -INSERT INTO t1 (f1) VALUES ('node2_committed_after'); -INSERT INTO t1 (f1) VALUES ('node2_committed_after'); -INSERT INTO t1 (f1) VALUES ('node2_committed_after'); -INSERT INTO t1 (f1) VALUES ('node2_committed_after'); -INSERT INTO t1 (f1) VALUES ('node2_committed_after'); -COMMIT; -connection node_1; -INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); -COMMIT; -SET AUTOCOMMIT=OFF; -START TRANSACTION; -INSERT INTO t1 (f1) VALUES ('node1_committed_after'); -INSERT INTO t1 (f1) VALUES ('node1_committed_after'); -INSERT INTO t1 (f1) VALUES ('node1_committed_after'); -INSERT INTO t1 (f1) VALUES ('node1_committed_after'); -INSERT INTO t1 (f1) VALUES ('node1_committed_after'); -COMMIT; -connection node_1a_galera_st_kill_slave_ddl; -INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); -ROLLBACK; -SELECT COUNT(*) = 2 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't1'; -COUNT(*) = 2 -1 -SELECT COUNT(*) = 35 FROM t1; -COUNT(*) = 35 -1 -SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; -COUNT(*) = 0 -1 -COMMIT; -SET AUTOCOMMIT=ON; -connection node_1; -SELECT COUNT(*) = 2 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't1'; -COUNT(*) = 2 -1 -SELECT COUNT(*) = 35 FROM t1; -COUNT(*) = 35 -1 -SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; -COUNT(*) = 0 -1 -DROP TABLE t1; -COMMIT; -SET AUTOCOMMIT=ON; -SET GLOBAL debug_dbug = $debug_orig; diff --git a/mysql-test/suite/galera/r/galera_ist_xtrabackup-v2,debug.rdiff b/mysql-test/suite/galera/r/galera_ist_xtrabackup-v2,debug.rdiff new file mode 100644 index 00000000000..95310b3ffeb --- /dev/null +++ b/mysql-test/suite/galera/r/galera_ist_xtrabackup-v2,debug.rdiff @@ -0,0 +1,103 @@ +--- r/galera_ist_xtrabackup-v2.result 2018-09-05 10:34:36.192439933 +0300 ++++ r/galera_ist_xtrabackup-v2.reject 2018-09-17 11:13:33.395264800 +0300 +@@ -259,3 +259,100 @@ + DROP TABLE t1; + COMMIT; + SET AUTOCOMMIT=ON; ++Performing State Transfer on a server that has been killed and restarted ++while a DDL was in progress on it ++CREATE TABLE t1 (f1 CHAR(255)) ENGINE=InnoDB; ++SET AUTOCOMMIT=OFF; ++START TRANSACTION; ++INSERT INTO t1 VALUES ('node1_committed_before'); ++INSERT INTO t1 VALUES ('node1_committed_before'); ++INSERT INTO t1 VALUES ('node1_committed_before'); ++INSERT INTO t1 VALUES ('node1_committed_before'); ++INSERT INTO t1 VALUES ('node1_committed_before'); ++START TRANSACTION; ++INSERT INTO t1 VALUES ('node2_committed_before'); ++INSERT INTO t1 VALUES ('node2_committed_before'); ++INSERT INTO t1 VALUES ('node2_committed_before'); ++INSERT INTO t1 VALUES ('node2_committed_before'); ++INSERT INTO t1 VALUES ('node2_committed_before'); ++COMMIT; ++SET GLOBAL debug_dbug = 'd,sync.alter_opened_table'; ++ALTER TABLE t1 ADD COLUMN f2 INTEGER; ++SET wsrep_sync_wait = 0; ++Killing server ... ++SET AUTOCOMMIT=OFF; ++START TRANSACTION; ++INSERT INTO t1 (f1) VALUES ('node1_committed_during'); ++INSERT INTO t1 (f1) VALUES ('node1_committed_during'); ++INSERT INTO t1 (f1) VALUES ('node1_committed_during'); ++INSERT INTO t1 (f1) VALUES ('node1_committed_during'); ++INSERT INTO t1 (f1) VALUES ('node1_committed_during'); ++COMMIT; ++START TRANSACTION; ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++SET AUTOCOMMIT=OFF; ++START TRANSACTION; ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++Performing --wsrep-recover ... ++Starting server ... ++Using --wsrep-start-position when starting mysqld ... ++SET AUTOCOMMIT=OFF; ++START TRANSACTION; ++INSERT INTO t1 (f1) VALUES ('node2_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node2_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node2_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node2_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node2_committed_after'); ++COMMIT; ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++COMMIT; ++SET AUTOCOMMIT=OFF; ++START TRANSACTION; ++INSERT INTO t1 (f1) VALUES ('node1_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_committed_after'); ++COMMIT; ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++ROLLBACK; ++SELECT COUNT(*) = 2 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't1'; ++COUNT(*) = 2 ++1 ++SELECT COUNT(*) = 35 FROM t1; ++COUNT(*) = 35 ++1 ++SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; ++COUNT(*) = 0 ++1 ++COMMIT; ++SET AUTOCOMMIT=ON; ++SELECT COUNT(*) = 2 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't1'; ++COUNT(*) = 2 ++1 ++SELECT COUNT(*) = 35 FROM t1; ++COUNT(*) = 35 ++1 ++SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; ++COUNT(*) = 0 ++1 ++DROP TABLE t1; ++COMMIT; ++SET AUTOCOMMIT=ON; ++SET GLOBAL debug_dbug = $debug_orig; diff --git a/mysql-test/suite/galera/r/galera_ist_xtrabackup-v2.result b/mysql-test/suite/galera/r/galera_ist_xtrabackup-v2.result index 175e7443a4d..099d944d491 100644 --- a/mysql-test/suite/galera/r/galera_ist_xtrabackup-v2.result +++ b/mysql-test/suite/galera/r/galera_ist_xtrabackup-v2.result @@ -259,99 +259,3 @@ COUNT(*) = 0 DROP TABLE t1; COMMIT; SET AUTOCOMMIT=ON; -Performing State Transfer on a server that has been killed and restarted -while a DDL was in progress on it -CREATE TABLE t1 (f1 CHAR(255)) ENGINE=InnoDB; -SET AUTOCOMMIT=OFF; -START TRANSACTION; -INSERT INTO t1 VALUES ('node1_committed_before'); -INSERT INTO t1 VALUES ('node1_committed_before'); -INSERT INTO t1 VALUES ('node1_committed_before'); -INSERT INTO t1 VALUES ('node1_committed_before'); -INSERT INTO t1 VALUES ('node1_committed_before'); -START TRANSACTION; -INSERT INTO t1 VALUES ('node2_committed_before'); -INSERT INTO t1 VALUES ('node2_committed_before'); -INSERT INTO t1 VALUES ('node2_committed_before'); -INSERT INTO t1 VALUES ('node2_committed_before'); -INSERT INTO t1 VALUES ('node2_committed_before'); -COMMIT; -SET GLOBAL debug = 'd,sync.alter_opened_table'; -ALTER TABLE t1 ADD COLUMN f2 INTEGER; -SET wsrep_sync_wait = 0; -Killing server ... -SET AUTOCOMMIT=OFF; -START TRANSACTION; -INSERT INTO t1 (f1) VALUES ('node1_committed_during'); -INSERT INTO t1 (f1) VALUES ('node1_committed_during'); -INSERT INTO t1 (f1) VALUES ('node1_committed_during'); -INSERT INTO t1 (f1) VALUES ('node1_committed_during'); -INSERT INTO t1 (f1) VALUES ('node1_committed_during'); -COMMIT; -START TRANSACTION; -INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); -SET AUTOCOMMIT=OFF; -START TRANSACTION; -INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); -Performing --wsrep-recover ... -Starting server ... -Using --wsrep-start-position when starting mysqld ... -SET AUTOCOMMIT=OFF; -START TRANSACTION; -INSERT INTO t1 (f1) VALUES ('node2_committed_after'); -INSERT INTO t1 (f1) VALUES ('node2_committed_after'); -INSERT INTO t1 (f1) VALUES ('node2_committed_after'); -INSERT INTO t1 (f1) VALUES ('node2_committed_after'); -INSERT INTO t1 (f1) VALUES ('node2_committed_after'); -COMMIT; -INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); -COMMIT; -SET AUTOCOMMIT=OFF; -START TRANSACTION; -INSERT INTO t1 (f1) VALUES ('node1_committed_after'); -INSERT INTO t1 (f1) VALUES ('node1_committed_after'); -INSERT INTO t1 (f1) VALUES ('node1_committed_after'); -INSERT INTO t1 (f1) VALUES ('node1_committed_after'); -INSERT INTO t1 (f1) VALUES ('node1_committed_after'); -COMMIT; -INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); -INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); -ROLLBACK; -SELECT COUNT(*) = 2 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't1'; -COUNT(*) = 2 -1 -SELECT COUNT(*) = 35 FROM t1; -COUNT(*) = 35 -1 -SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; -COUNT(*) = 0 -1 -COMMIT; -SET AUTOCOMMIT=ON; -SELECT COUNT(*) = 2 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't1'; -COUNT(*) = 2 -1 -SELECT COUNT(*) = 35 FROM t1; -COUNT(*) = 35 -1 -SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; -COUNT(*) = 0 -1 -DROP TABLE t1; -COMMIT; -SET AUTOCOMMIT=ON; diff --git a/mysql-test/suite/galera/r/galera_kill_ddl.result b/mysql-test/suite/galera/r/galera_kill_ddl.result index aac316dffe3..b11353fcbcc 100644 --- a/mysql-test/suite/galera/r/galera_kill_ddl.result +++ b/mysql-test/suite/galera/r/galera_kill_ddl.result @@ -1,3 +1,4 @@ +call mtr.add_suppression("WSREP: Last Applied Action message in non-primary configuration from member .*"); connection node_1; SET GLOBAL wsrep_provider_options = 'pc.ignore_sb=true'; CREATE TABLE t1 (f1 INTEGER) ENGINE=InnoDB; diff --git a/mysql-test/suite/galera/r/galera_kill_largechanges.result b/mysql-test/suite/galera/r/galera_kill_largechanges.result index d04bd548949..f4de101fef8 100644 --- a/mysql-test/suite/galera/r/galera_kill_largechanges.result +++ b/mysql-test/suite/galera/r/galera_kill_largechanges.result @@ -1,3 +1,4 @@ +call mtr.add_suppression("WSREP: Last Applied Action message in non-primary configuration from member .*"); connection node_1; SET GLOBAL wsrep_provider_options = 'pc.ignore_sb=true'; CREATE TABLE ten (f1 INTEGER); diff --git a/mysql-test/suite/galera/r/galera_kill_smallchanges.result b/mysql-test/suite/galera/r/galera_kill_smallchanges.result index bcd7d6fd15d..2ee291004b0 100644 --- a/mysql-test/suite/galera/r/galera_kill_smallchanges.result +++ b/mysql-test/suite/galera/r/galera_kill_smallchanges.result @@ -1,3 +1,4 @@ +call mtr.add_suppression("WSREP: Last Applied Action message in non-primary configuration from member .*"); connection node_1; SET GLOBAL wsrep_provider_options = 'pc.ignore_sb=true'; CREATE TABLE t1 (f1 INTEGER) ENGINE=InnoDB; diff --git a/mysql-test/suite/galera/r/galera_sst_rsync2,debug.rdiff b/mysql-test/suite/galera/r/galera_sst_rsync2,debug.rdiff new file mode 100644 index 00000000000..525156d88da --- /dev/null +++ b/mysql-test/suite/galera/r/galera_sst_rsync2,debug.rdiff @@ -0,0 +1,114 @@ +--- suite/galera/r/galera_sst_rsync2.result 2018-09-12 13:09:35.352229478 +0200 ++++ suite/galera/r/galera_sst_rsync2,debug.reject 2018-09-12 17:00:51.601974979 +0200 +@@ -286,3 +286,111 @@ + DROP TABLE t1; + COMMIT; + SET AUTOCOMMIT=ON; ++Performing State Transfer on a server that has been killed and restarted ++while a DDL was in progress on it ++connection node_1; ++CREATE TABLE t1 (f1 CHAR(255)) ENGINE=InnoDB; ++SET AUTOCOMMIT=OFF; ++START TRANSACTION; ++INSERT INTO t1 VALUES ('node1_committed_before'); ++INSERT INTO t1 VALUES ('node1_committed_before'); ++INSERT INTO t1 VALUES ('node1_committed_before'); ++INSERT INTO t1 VALUES ('node1_committed_before'); ++INSERT INTO t1 VALUES ('node1_committed_before'); ++connection node_2; ++START TRANSACTION; ++INSERT INTO t1 VALUES ('node2_committed_before'); ++INSERT INTO t1 VALUES ('node2_committed_before'); ++INSERT INTO t1 VALUES ('node2_committed_before'); ++INSERT INTO t1 VALUES ('node2_committed_before'); ++INSERT INTO t1 VALUES ('node2_committed_before'); ++COMMIT; ++SET GLOBAL debug_dbug = 'd,sync.alter_opened_table'; ++connection node_1; ++ALTER TABLE t1 ADD COLUMN f2 INTEGER; ++connection node_2; ++SET wsrep_sync_wait = 0; ++Killing server ... ++connection node_1; ++SET AUTOCOMMIT=OFF; ++START TRANSACTION; ++INSERT INTO t1 (f1) VALUES ('node1_committed_during'); ++INSERT INTO t1 (f1) VALUES ('node1_committed_during'); ++INSERT INTO t1 (f1) VALUES ('node1_committed_during'); ++INSERT INTO t1 (f1) VALUES ('node1_committed_during'); ++INSERT INTO t1 (f1) VALUES ('node1_committed_during'); ++COMMIT; ++START TRANSACTION; ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++connect node_1a_galera_st_kill_slave_ddl, 127.0.0.1, root, , test, $NODE_MYPORT_1; ++SET AUTOCOMMIT=OFF; ++START TRANSACTION; ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++connection node_2; ++Performing --wsrep-recover ... ++connection node_2; ++Starting server ... ++Using --wsrep-start-position when starting mysqld ... ++SET AUTOCOMMIT=OFF; ++START TRANSACTION; ++INSERT INTO t1 (f1) VALUES ('node2_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node2_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node2_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node2_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node2_committed_after'); ++COMMIT; ++connection node_1; ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++COMMIT; ++SET AUTOCOMMIT=OFF; ++START TRANSACTION; ++INSERT INTO t1 (f1) VALUES ('node1_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_committed_after'); ++COMMIT; ++connection node_1a_galera_st_kill_slave_ddl; ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++ROLLBACK; ++SELECT COUNT(*) = 2 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't1'; ++COUNT(*) = 2 ++1 ++SELECT COUNT(*) = 35 FROM t1; ++COUNT(*) = 35 ++1 ++SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; ++COUNT(*) = 0 ++1 ++COMMIT; ++SET AUTOCOMMIT=ON; ++connection node_1; ++SELECT COUNT(*) = 2 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't1'; ++COUNT(*) = 2 ++1 ++SELECT COUNT(*) = 35 FROM t1; ++COUNT(*) = 35 ++1 ++SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; ++COUNT(*) = 0 ++1 ++DROP TABLE t1; ++COMMIT; ++SET AUTOCOMMIT=ON; ++SET GLOBAL debug_dbug = $debug_orig; diff --git a/mysql-test/suite/galera/r/galera_sst_rsync2.result b/mysql-test/suite/galera/r/galera_sst_rsync2.result new file mode 100644 index 00000000000..ff85a7d6c0f --- /dev/null +++ b/mysql-test/suite/galera/r/galera_sst_rsync2.result @@ -0,0 +1,288 @@ +connection node_1; +connection node_2; +Performing State Transfer on a server that has been shut down cleanly and restarted +connection node_1; +CREATE TABLE t1 (f1 CHAR(255)) ENGINE=InnoDB; +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node1_committed_before'); +INSERT INTO t1 VALUES ('node1_committed_before'); +INSERT INTO t1 VALUES ('node1_committed_before'); +INSERT INTO t1 VALUES ('node1_committed_before'); +INSERT INTO t1 VALUES ('node1_committed_before'); +COMMIT; +connection node_2; +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node2_committed_before'); +INSERT INTO t1 VALUES ('node2_committed_before'); +INSERT INTO t1 VALUES ('node2_committed_before'); +INSERT INTO t1 VALUES ('node2_committed_before'); +INSERT INTO t1 VALUES ('node2_committed_before'); +COMMIT; +Shutting down server ... +connection node_1; +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node1_committed_during'); +INSERT INTO t1 VALUES ('node1_committed_during'); +INSERT INTO t1 VALUES ('node1_committed_during'); +INSERT INTO t1 VALUES ('node1_committed_during'); +INSERT INTO t1 VALUES ('node1_committed_during'); +COMMIT; +START TRANSACTION; +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +connect node_1a_galera_st_shutdown_slave, 127.0.0.1, root, , test, $NODE_MYPORT_1; +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +connection node_2; +Starting server ... +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node2_committed_after'); +INSERT INTO t1 VALUES ('node2_committed_after'); +INSERT INTO t1 VALUES ('node2_committed_after'); +INSERT INTO t1 VALUES ('node2_committed_after'); +INSERT INTO t1 VALUES ('node2_committed_after'); +COMMIT; +connection node_1; +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +COMMIT; +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node1_committed_after'); +INSERT INTO t1 VALUES ('node1_committed_after'); +INSERT INTO t1 VALUES ('node1_committed_after'); +INSERT INTO t1 VALUES ('node1_committed_after'); +INSERT INTO t1 VALUES ('node1_committed_after'); +COMMIT; +connection node_1a_galera_st_shutdown_slave; +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +ROLLBACK; +SELECT COUNT(*) = 35 FROM t1; +COUNT(*) = 35 +1 +SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; +COUNT(*) = 0 +1 +COMMIT; +SET AUTOCOMMIT=ON; +connection node_1; +SELECT COUNT(*) = 35 FROM t1; +COUNT(*) = 35 +1 +SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; +COUNT(*) = 0 +1 +DROP TABLE t1; +COMMIT; +SET AUTOCOMMIT=ON; +Performing State Transfer on a server that starts from a clean var directory +This is accomplished by shutting down node #2 and removing its var directory before restarting it +connection node_1; +CREATE TABLE t1 (f1 CHAR(255)) ENGINE=InnoDB; +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node1_committed_before'); +INSERT INTO t1 VALUES ('node1_committed_before'); +INSERT INTO t1 VALUES ('node1_committed_before'); +INSERT INTO t1 VALUES ('node1_committed_before'); +INSERT INTO t1 VALUES ('node1_committed_before'); +COMMIT; +connection node_2; +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node2_committed_before'); +INSERT INTO t1 VALUES ('node2_committed_before'); +INSERT INTO t1 VALUES ('node2_committed_before'); +INSERT INTO t1 VALUES ('node2_committed_before'); +INSERT INTO t1 VALUES ('node2_committed_before'); +COMMIT; +Shutting down server ... +connection node_1; +Cleaning var directory ... +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node1_committed_during'); +INSERT INTO t1 VALUES ('node1_committed_during'); +INSERT INTO t1 VALUES ('node1_committed_during'); +INSERT INTO t1 VALUES ('node1_committed_during'); +INSERT INTO t1 VALUES ('node1_committed_during'); +COMMIT; +START TRANSACTION; +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +connect node_1a_galera_st_clean_slave, 127.0.0.1, root, , test, $NODE_MYPORT_1; +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +connection node_2; +Starting server ... +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node2_committed_after'); +INSERT INTO t1 VALUES ('node2_committed_after'); +INSERT INTO t1 VALUES ('node2_committed_after'); +INSERT INTO t1 VALUES ('node2_committed_after'); +INSERT INTO t1 VALUES ('node2_committed_after'); +COMMIT; +connection node_1; +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +COMMIT; +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node1_committed_after'); +INSERT INTO t1 VALUES ('node1_committed_after'); +INSERT INTO t1 VALUES ('node1_committed_after'); +INSERT INTO t1 VALUES ('node1_committed_after'); +INSERT INTO t1 VALUES ('node1_committed_after'); +COMMIT; +connection node_1a_galera_st_clean_slave; +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +ROLLBACK; +SELECT COUNT(*) = 35 FROM t1; +COUNT(*) = 35 +1 +SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; +COUNT(*) = 0 +1 +COMMIT; +SET AUTOCOMMIT=ON; +connection node_1; +SELECT COUNT(*) = 35 FROM t1; +COUNT(*) = 35 +1 +SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; +COUNT(*) = 0 +1 +DROP TABLE t1; +COMMIT; +SET AUTOCOMMIT=ON; +Performing State Transfer on a server that has been killed and restarted +connection node_1; +CREATE TABLE t1 (f1 CHAR(255)) ENGINE=InnoDB; +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node1_committed_before'); +INSERT INTO t1 VALUES ('node1_committed_before'); +INSERT INTO t1 VALUES ('node1_committed_before'); +INSERT INTO t1 VALUES ('node1_committed_before'); +INSERT INTO t1 VALUES ('node1_committed_before'); +COMMIT; +connection node_2; +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node2_committed_before'); +INSERT INTO t1 VALUES ('node2_committed_before'); +INSERT INTO t1 VALUES ('node2_committed_before'); +INSERT INTO t1 VALUES ('node2_committed_before'); +INSERT INTO t1 VALUES ('node2_committed_before'); +COMMIT; +Killing server ... +connection node_1; +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node1_committed_during'); +INSERT INTO t1 VALUES ('node1_committed_during'); +INSERT INTO t1 VALUES ('node1_committed_during'); +INSERT INTO t1 VALUES ('node1_committed_during'); +INSERT INTO t1 VALUES ('node1_committed_during'); +COMMIT; +START TRANSACTION; +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +connect node_1a_galera_st_kill_slave, 127.0.0.1, root, , test, $NODE_MYPORT_1; +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +connection node_2; +Performing --wsrep-recover ... +Starting server ... +Using --wsrep-start-position when starting mysqld ... +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node2_committed_after'); +INSERT INTO t1 VALUES ('node2_committed_after'); +INSERT INTO t1 VALUES ('node2_committed_after'); +INSERT INTO t1 VALUES ('node2_committed_after'); +INSERT INTO t1 VALUES ('node2_committed_after'); +COMMIT; +connection node_1; +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +COMMIT; +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node1_committed_after'); +INSERT INTO t1 VALUES ('node1_committed_after'); +INSERT INTO t1 VALUES ('node1_committed_after'); +INSERT INTO t1 VALUES ('node1_committed_after'); +INSERT INTO t1 VALUES ('node1_committed_after'); +COMMIT; +connection node_1a_galera_st_kill_slave; +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +ROLLBACK; +SELECT COUNT(*) = 35 FROM t1; +COUNT(*) = 35 +1 +SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; +COUNT(*) = 0 +1 +COMMIT; +SET AUTOCOMMIT=ON; +connection node_1; +SELECT COUNT(*) = 35 FROM t1; +COUNT(*) = 35 +1 +SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; +COUNT(*) = 0 +1 +DROP TABLE t1; +COMMIT; +SET AUTOCOMMIT=ON; diff --git a/mysql-test/suite/galera/r/galera_sst_rsync_data_dir,debug.rdiff b/mysql-test/suite/galera/r/galera_sst_rsync_data_dir,debug.rdiff new file mode 100644 index 00000000000..e307a2ff0f9 --- /dev/null +++ b/mysql-test/suite/galera/r/galera_sst_rsync_data_dir,debug.rdiff @@ -0,0 +1,114 @@ +--- suite/galera/r/galera_sst_rsync_data_dir.result 2018-09-13 14:52:50.848220719 +0200 ++++ suite/galera/r/galera_sst_rsync_data_dir.reject 2018-09-13 15:03:32.339135247 +0200 +@@ -286,3 +286,111 @@ + DROP TABLE t1; + COMMIT; + SET AUTOCOMMIT=ON; ++Performing State Transfer on a server that has been killed and restarted ++while a DDL was in progress on it ++connection node_1; ++CREATE TABLE t1 (f1 CHAR(255)) ENGINE=InnoDB; ++SET AUTOCOMMIT=OFF; ++START TRANSACTION; ++INSERT INTO t1 VALUES ('node1_committed_before'); ++INSERT INTO t1 VALUES ('node1_committed_before'); ++INSERT INTO t1 VALUES ('node1_committed_before'); ++INSERT INTO t1 VALUES ('node1_committed_before'); ++INSERT INTO t1 VALUES ('node1_committed_before'); ++connection node_2; ++START TRANSACTION; ++INSERT INTO t1 VALUES ('node2_committed_before'); ++INSERT INTO t1 VALUES ('node2_committed_before'); ++INSERT INTO t1 VALUES ('node2_committed_before'); ++INSERT INTO t1 VALUES ('node2_committed_before'); ++INSERT INTO t1 VALUES ('node2_committed_before'); ++COMMIT; ++SET GLOBAL debug_dbug = 'd,sync.alter_opened_table'; ++connection node_1; ++ALTER TABLE t1 ADD COLUMN f2 INTEGER; ++connection node_2; ++SET wsrep_sync_wait = 0; ++Killing server ... ++connection node_1; ++SET AUTOCOMMIT=OFF; ++START TRANSACTION; ++INSERT INTO t1 (f1) VALUES ('node1_committed_during'); ++INSERT INTO t1 (f1) VALUES ('node1_committed_during'); ++INSERT INTO t1 (f1) VALUES ('node1_committed_during'); ++INSERT INTO t1 (f1) VALUES ('node1_committed_during'); ++INSERT INTO t1 (f1) VALUES ('node1_committed_during'); ++COMMIT; ++START TRANSACTION; ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++connect node_1a_galera_st_kill_slave_ddl, 127.0.0.1, root, , test, $NODE_MYPORT_1; ++SET AUTOCOMMIT=OFF; ++START TRANSACTION; ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++connection node_2; ++Performing --wsrep-recover ... ++connection node_2; ++Starting server ... ++Using --wsrep-start-position when starting mysqld ... ++SET AUTOCOMMIT=OFF; ++START TRANSACTION; ++INSERT INTO t1 (f1) VALUES ('node2_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node2_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node2_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node2_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node2_committed_after'); ++COMMIT; ++connection node_1; ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_committed_after'); ++COMMIT; ++SET AUTOCOMMIT=OFF; ++START TRANSACTION; ++INSERT INTO t1 (f1) VALUES ('node1_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_committed_after'); ++INSERT INTO t1 (f1) VALUES ('node1_committed_after'); ++COMMIT; ++connection node_1a_galera_st_kill_slave_ddl; ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ++ROLLBACK; ++SELECT COUNT(*) = 2 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't1'; ++COUNT(*) = 2 ++1 ++SELECT COUNT(*) = 35 FROM t1; ++COUNT(*) = 35 ++1 ++SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; ++COUNT(*) = 0 ++1 ++COMMIT; ++SET AUTOCOMMIT=ON; ++connection node_1; ++SELECT COUNT(*) = 2 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't1'; ++COUNT(*) = 2 ++1 ++SELECT COUNT(*) = 35 FROM t1; ++COUNT(*) = 35 ++1 ++SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; ++COUNT(*) = 0 ++1 ++DROP TABLE t1; ++COMMIT; ++SET AUTOCOMMIT=ON; ++SET GLOBAL debug_dbug = $debug_orig; diff --git a/mysql-test/suite/galera/r/galera_sst_xtrabackup-v2_data_dir.result b/mysql-test/suite/galera/r/galera_sst_xtrabackup-v2_data_dir.result new file mode 100644 index 00000000000..cec0f21ee22 --- /dev/null +++ b/mysql-test/suite/galera/r/galera_sst_xtrabackup-v2_data_dir.result @@ -0,0 +1,262 @@ +Performing State Transfer on a server that has been shut down cleanly and restarted +CREATE TABLE t1 (f1 CHAR(255)) ENGINE=InnoDB; +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node1_committed_before'); +INSERT INTO t1 VALUES ('node1_committed_before'); +INSERT INTO t1 VALUES ('node1_committed_before'); +INSERT INTO t1 VALUES ('node1_committed_before'); +INSERT INTO t1 VALUES ('node1_committed_before'); +COMMIT; +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node2_committed_before'); +INSERT INTO t1 VALUES ('node2_committed_before'); +INSERT INTO t1 VALUES ('node2_committed_before'); +INSERT INTO t1 VALUES ('node2_committed_before'); +INSERT INTO t1 VALUES ('node2_committed_before'); +COMMIT; +Shutting down server ... +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node1_committed_during'); +INSERT INTO t1 VALUES ('node1_committed_during'); +INSERT INTO t1 VALUES ('node1_committed_during'); +INSERT INTO t1 VALUES ('node1_committed_during'); +INSERT INTO t1 VALUES ('node1_committed_during'); +COMMIT; +START TRANSACTION; +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +Starting server ... +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node2_committed_after'); +INSERT INTO t1 VALUES ('node2_committed_after'); +INSERT INTO t1 VALUES ('node2_committed_after'); +INSERT INTO t1 VALUES ('node2_committed_after'); +INSERT INTO t1 VALUES ('node2_committed_after'); +COMMIT; +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +COMMIT; +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node1_committed_after'); +INSERT INTO t1 VALUES ('node1_committed_after'); +INSERT INTO t1 VALUES ('node1_committed_after'); +INSERT INTO t1 VALUES ('node1_committed_after'); +INSERT INTO t1 VALUES ('node1_committed_after'); +COMMIT; +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +ROLLBACK; +SELECT COUNT(*) = 35 FROM t1; +COUNT(*) = 35 +1 +SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; +COUNT(*) = 0 +1 +COMMIT; +SET AUTOCOMMIT=ON; +SELECT COUNT(*) = 35 FROM t1; +COUNT(*) = 35 +1 +SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; +COUNT(*) = 0 +1 +DROP TABLE t1; +COMMIT; +SET AUTOCOMMIT=ON; +Performing State Transfer on a server that starts from a clean var directory +This is accomplished by shutting down node #2 and removing its var directory before restarting it +CREATE TABLE t1 (f1 CHAR(255)) ENGINE=InnoDB; +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node1_committed_before'); +INSERT INTO t1 VALUES ('node1_committed_before'); +INSERT INTO t1 VALUES ('node1_committed_before'); +INSERT INTO t1 VALUES ('node1_committed_before'); +INSERT INTO t1 VALUES ('node1_committed_before'); +COMMIT; +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node2_committed_before'); +INSERT INTO t1 VALUES ('node2_committed_before'); +INSERT INTO t1 VALUES ('node2_committed_before'); +INSERT INTO t1 VALUES ('node2_committed_before'); +INSERT INTO t1 VALUES ('node2_committed_before'); +COMMIT; +Shutting down server ... +Cleaning var directory ... +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node1_committed_during'); +INSERT INTO t1 VALUES ('node1_committed_during'); +INSERT INTO t1 VALUES ('node1_committed_during'); +INSERT INTO t1 VALUES ('node1_committed_during'); +INSERT INTO t1 VALUES ('node1_committed_during'); +COMMIT; +START TRANSACTION; +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +Starting server ... +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node2_committed_after'); +INSERT INTO t1 VALUES ('node2_committed_after'); +INSERT INTO t1 VALUES ('node2_committed_after'); +INSERT INTO t1 VALUES ('node2_committed_after'); +INSERT INTO t1 VALUES ('node2_committed_after'); +COMMIT; +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +COMMIT; +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node1_committed_after'); +INSERT INTO t1 VALUES ('node1_committed_after'); +INSERT INTO t1 VALUES ('node1_committed_after'); +INSERT INTO t1 VALUES ('node1_committed_after'); +INSERT INTO t1 VALUES ('node1_committed_after'); +COMMIT; +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +ROLLBACK; +SELECT COUNT(*) = 35 FROM t1; +COUNT(*) = 35 +1 +SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; +COUNT(*) = 0 +1 +COMMIT; +SET AUTOCOMMIT=ON; +SELECT COUNT(*) = 35 FROM t1; +COUNT(*) = 35 +1 +SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; +COUNT(*) = 0 +1 +DROP TABLE t1; +COMMIT; +SET AUTOCOMMIT=ON; +Performing State Transfer on a server that has been killed and restarted +CREATE TABLE t1 (f1 CHAR(255)) ENGINE=InnoDB; +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node1_committed_before'); +INSERT INTO t1 VALUES ('node1_committed_before'); +INSERT INTO t1 VALUES ('node1_committed_before'); +INSERT INTO t1 VALUES ('node1_committed_before'); +INSERT INTO t1 VALUES ('node1_committed_before'); +COMMIT; +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node2_committed_before'); +INSERT INTO t1 VALUES ('node2_committed_before'); +INSERT INTO t1 VALUES ('node2_committed_before'); +INSERT INTO t1 VALUES ('node2_committed_before'); +INSERT INTO t1 VALUES ('node2_committed_before'); +COMMIT; +Killing server ... +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node1_committed_during'); +INSERT INTO t1 VALUES ('node1_committed_during'); +INSERT INTO t1 VALUES ('node1_committed_during'); +INSERT INTO t1 VALUES ('node1_committed_during'); +INSERT INTO t1 VALUES ('node1_committed_during'); +COMMIT; +START TRANSACTION; +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +Performing --wsrep-recover ... +Starting server ... +Using --wsrep-start-position when starting mysqld ... +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node2_committed_after'); +INSERT INTO t1 VALUES ('node2_committed_after'); +INSERT INTO t1 VALUES ('node2_committed_after'); +INSERT INTO t1 VALUES ('node2_committed_after'); +INSERT INTO t1 VALUES ('node2_committed_after'); +COMMIT; +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +INSERT INTO t1 VALUES ('node1_to_be_committed_after'); +COMMIT; +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES ('node1_committed_after'); +INSERT INTO t1 VALUES ('node1_committed_after'); +INSERT INTO t1 VALUES ('node1_committed_after'); +INSERT INTO t1 VALUES ('node1_committed_after'); +INSERT INTO t1 VALUES ('node1_committed_after'); +COMMIT; +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +ROLLBACK; +SELECT COUNT(*) = 35 FROM t1; +COUNT(*) = 35 +1 +SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; +COUNT(*) = 0 +1 +COMMIT; +SET AUTOCOMMIT=ON; +SELECT COUNT(*) = 35 FROM t1; +COUNT(*) = 35 +1 +SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; +COUNT(*) = 0 +1 +DROP TABLE t1; +COMMIT; +SET AUTOCOMMIT=ON; diff --git a/mysql-test/suite/galera/r/galera_suspend_slave.result b/mysql-test/suite/galera/r/galera_suspend_slave.result index 3b950e82e33..07433399081 100644 --- a/mysql-test/suite/galera/r/galera_suspend_slave.result +++ b/mysql-test/suite/galera/r/galera_suspend_slave.result @@ -7,9 +7,11 @@ disconnect node_2; connection node_1; Suspending node_2 ... SET SESSION wsrep_sync_wait = 0; +SET SESSION wsrep_sync_wait = 15; INSERT INTO t1 VALUES (1); Got one of the listed errors Resuming node_2 ... +SET SESSION wsrep_sync_wait = 0; INSERT INTO t1 VALUES (1); connection node_2a; SET SESSION wsrep_sync_wait = 0; diff --git a/mysql-test/suite/galera/r/galera_toi_truncate.result b/mysql-test/suite/galera/r/galera_toi_truncate.result index 933379cade5..73285d723c1 100644 --- a/mysql-test/suite/galera/r/galera_toi_truncate.result +++ b/mysql-test/suite/galera/r/galera_toi_truncate.result @@ -4,6 +4,8 @@ INSERT INTO ten VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); CREATE TABLE t1 (f1 INTEGER) ENGINE=InnoDB; connection node_2; SET SESSION wsrep_retry_autocommit = 0; +INSERT INTO t1(f1) SELECT 1 FROM ten as a1, ten AS a2; +set debug_sync='ha_commit_trans_after_prepare WAIT_FOR go'; INSERT INTO t1 (f1) SELECT 1 FROM ten AS a1, ten AS a2, ten AS a3, ten AS a4, ten AS a5, ten AS a6;; connection node_1; TRUNCATE TABLE t1;; diff --git a/mysql-test/suite/galera/r/galera_unicode_identifiers.result b/mysql-test/suite/galera/r/galera_unicode_identifiers.result index 5eb13a83df9..77848bc751f 100644 --- a/mysql-test/suite/galera/r/galera_unicode_identifiers.result +++ b/mysql-test/suite/galera/r/galera_unicode_identifiers.result @@ -8,7 +8,7 @@ CREATE TABLE `table with space` ( `column with space` INTEGER AUTO_INCREMENT PRIMARY KEY, `second column with space` INTEGER, UNIQUE `index name with space` (`second column with space`) -); +) engine=innodb; INSERT INTO `table with space` VALUES (1, 1); CREATE DATABASE `база`; USE `база`; @@ -16,7 +16,7 @@ CREATE TABLE `таблица` ( `първа_колона` INTEGER PRIMARY KEY, `втора_колона` INTEGER, UNIQUE `индекÑ` (`втора_колона`) -); +) engine=innodb; INSERT INTO `таблица` VALUES (1, 1); CREATE DATABASE `втора база`; USE `втора база`; @@ -24,7 +24,7 @@ CREATE TABLE `втора таблица` ( `първа колона` INTEGER, `втора колона` INTEGER, KEY `първи индекÑ` (`първа колона`) -); +) engine=innodb; INSERT INTO `втора таблица` VALUES (1, 1); connection node_2; USE `database with space`; diff --git a/mysql-test/suite/galera/r/galera_var_node_address.result b/mysql-test/suite/galera/r/galera_var_node_address.result index b8076958532..7696d1e3f4f 100644 --- a/mysql-test/suite/galera/r/galera_var_node_address.result +++ b/mysql-test/suite/galera/r/galera_var_node_address.result @@ -5,7 +5,7 @@ SELECT VARIABLE_VALUE = 4 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_N VARIABLE_VALUE = 4 1 connection node_1; -CREATE TABLE t1 (f1 INTEGER); +CREATE TABLE t1 (f1 INTEGER) ENGINE=INNODB; connection node_2; INSERT INTO t1 VALUES (1); connect node_3, 127.0.0.1, root, , test, $NODE_MYPORT_3; diff --git a/mysql-test/suite/galera/r/galera_var_slave_threads.result b/mysql-test/suite/galera/r/galera_var_slave_threads.result index 3f0a63ab9d7..c28cc091ae9 100644 --- a/mysql-test/suite/galera/r/galera_var_slave_threads.result +++ b/mysql-test/suite/galera/r/galera_var_slave_threads.result @@ -20,72 +20,11 @@ SET GLOBAL wsrep_slave_threads = 64; connection node_1; INSERT INTO t1 VALUES (1); connection node_2; +SELECT COUNT(*) = 1 FROM t1; +COUNT(*) = 1 +1 SET GLOBAL wsrep_slave_threads = 1; connection node_1; -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); -INSERT INTO t2 VALUES (DEFAULT); connection node_2; SELECT COUNT(*) FROM t2; COUNT(*) diff --git a/mysql-test/suite/galera/r/galera_wan.result b/mysql-test/suite/galera/r/galera_wan.result index 73100636a0d..41b915fa5bf 100644 --- a/mysql-test/suite/galera/r/galera_wan.result +++ b/mysql-test/suite/galera/r/galera_wan.result @@ -1,3 +1,7 @@ +CALL mtr.add_suppression("WSREP: Stray state UUID msg:.*"); +CALL mtr.add_suppression("WSREP: Sending JOIN failed:.*"); +CALL mtr.add_suppression("There are no nodes in the same segment that will ever be able to become donors, yet there is a suitable donor outside"); +call mtr.add_suppression("WSREP: Sending JOIN failed:.*"); SELECT VARIABLE_VALUE = 4 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_size'; VARIABLE_VALUE = 4 1 @@ -6,7 +10,6 @@ CREATE TABLE t1 (f1 INTEGER); connect node_3, 127.0.0.1, root, , test, $NODE_MYPORT_3; connection node_3; INSERT INTO t1 VALUES (1); -CALL mtr.add_suppression("There are no nodes in the same segment that will ever be able to become donors, yet there is a suitable donor outside"); connect node_4, 127.0.0.1, root, , test, $NODE_MYPORT_4; connection node_4; SELECT VARIABLE_VALUE LIKE '%gmcast.segment = 3%' FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'wsrep_provider_options'; @@ -16,4 +19,3 @@ SELECT COUNT(*) = 1 FROM t1; COUNT(*) = 1 1 DROP TABLE t1; -CALL mtr.add_suppression("There are no nodes in the same segment that will ever be able to become donors, yet there is a suitable donor outside"); diff --git a/mysql-test/suite/galera/r/mysql-wsrep#332.result b/mysql-test/suite/galera/r/mysql-wsrep#332.result new file mode 100644 index 00000000000..8667f5e9c41 --- /dev/null +++ b/mysql-test/suite/galera/r/mysql-wsrep#332.result @@ -0,0 +1,111 @@ +CREATE TABLE p (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB; +CREATE TABLE c (f1 INTEGER PRIMARY KEY, p_id INTEGER) ENGINE=INNODB; +INSERT INTO p VALUES (1, 0); +INSERT INTO p VALUES (2, 0); +INSERT INTO c VALUES (1, 1); +INSERT INTO c VALUES (2, 2); +SET AUTOCOMMIT=ON; +START TRANSACTION; +UPDATE p SET f1 = f1 + 100; +SET SESSION wsrep_sync_wait = 0; +SET GLOBAL wsrep_provider_options = 'dbug=d,apply_monitor_slave_enter_sync'; +ALTER TABLE c ADD FOREIGN KEY (p_id) REFERENCES p(f1); +SET SESSION wsrep_on = 0; +SET SESSION wsrep_on = 1; +SET GLOBAL wsrep_provider_options = 'dbug='; +SET GLOBAL wsrep_provider_options = 'dbug=d,local_monitor_enter_sync'; +COMMIT; +SET SESSION wsrep_on = 0; +SET SESSION wsrep_on = 1; +SET GLOBAL wsrep_provider_options = 'signal=apply_monitor_slave_enter_sync'; +SET GLOBAL wsrep_provider_options = 'signal=local_monitor_enter_sync'; +SET GLOBAL wsrep_provider_options = 'dbug='; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +SELECT * FROM p; +f1 f2 +1 0 +2 0 +SELECT * FROM c; +f1 p_id +1 1 +2 2 +DROP TABLE c; +DROP TABLE p; +CREATE TABLE p1 (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB; +CREATE TABLE p2 (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB; +CREATE TABLE c (f1 INTEGER PRIMARY KEY, p_id1 INTEGER, p_id2 INTEGER) ENGINE=INNODB; +INSERT INTO p1 VALUES (1, 0), (2, 0); +INSERT INTO p2 VALUES (1, 0), (2, 0); +INSERT INTO c VALUES (1, 1, 1); +INSERT INTO c VALUES (2, 2, 2); +SET AUTOCOMMIT=ON; +START TRANSACTION; +UPDATE p1 SET f1 = f1 + 100; +SET SESSION wsrep_sync_wait = 0; +SET GLOBAL wsrep_provider_options = 'dbug=d,apply_monitor_slave_enter_sync'; +ALTER TABLE c ADD FOREIGN KEY (p_id1) REFERENCES p1(f1), ADD FOREIGN KEY (p_id2) REFERENCES p2(f1); +SET SESSION wsrep_on = 0; +SET SESSION wsrep_on = 1; +SET GLOBAL wsrep_provider_options = 'dbug='; +SET GLOBAL wsrep_provider_options = 'dbug=d,local_monitor_enter_sync'; +COMMIT; +SET SESSION wsrep_on = 0; +SET SESSION wsrep_on = 1; +SET GLOBAL wsrep_provider_options = 'signal=apply_monitor_slave_enter_sync'; +SET GLOBAL wsrep_provider_options = 'signal=local_monitor_enter_sync'; +SET GLOBAL wsrep_provider_options = 'dbug='; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +SELECT * FROM p1; +f1 f2 +1 0 +2 0 +SELECT * FROM p2; +f1 f2 +1 0 +2 0 +SELECT * FROM c; +f1 p_id1 p_id2 +1 1 1 +2 2 2 +DROP TABLE c; +DROP TABLE p1; +DROP TABLE p2; +CREATE TABLE p1 (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB; +CREATE TABLE p2 (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB; +CREATE TABLE c (f1 INTEGER PRIMARY KEY, p_id1 INTEGER, p_id2 INTEGER) ENGINE=INNODB; +INSERT INTO p1 VALUES (1, 0), (2, 0); +INSERT INTO p2 VALUES (1, 0), (2, 0); +INSERT INTO c VALUES (1, 1, 1); +INSERT INTO c VALUES (2, 2, 2); +SET AUTOCOMMIT=ON; +START TRANSACTION; +UPDATE p2 SET f1 = f1 + 100; +SET SESSION wsrep_sync_wait = 0; +SET GLOBAL wsrep_provider_options = 'dbug=d,apply_monitor_slave_enter_sync'; +ALTER TABLE c ADD FOREIGN KEY (p_id1) REFERENCES p1(f1), ADD FOREIGN KEY (p_id2) REFERENCES p2(f1); +SET SESSION wsrep_on = 0; +SET SESSION wsrep_on = 1; +SET GLOBAL wsrep_provider_options = 'dbug='; +SET GLOBAL wsrep_provider_options = 'dbug=d,local_monitor_enter_sync'; +COMMIT; +SET SESSION wsrep_on = 0; +SET SESSION wsrep_on = 1; +SET GLOBAL wsrep_provider_options = 'signal=apply_monitor_slave_enter_sync'; +SET GLOBAL wsrep_provider_options = 'signal=local_monitor_enter_sync'; +SET GLOBAL wsrep_provider_options = 'dbug='; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +SELECT * FROM p1; +f1 f2 +1 0 +2 0 +SELECT * FROM p2; +f1 f2 +1 0 +2 0 +SELECT * FROM c; +f1 p_id1 p_id2 +1 1 1 +2 2 2 +DROP TABLE c; +DROP TABLE p1; +DROP TABLE p2; diff --git a/mysql-test/suite/galera/r/query_cache.result b/mysql-test/suite/galera/r/query_cache.result index d497fc87544..a1d5d96d627 100644 --- a/mysql-test/suite/galera/r/query_cache.result +++ b/mysql-test/suite/galera/r/query_cache.result @@ -421,24 +421,6 @@ Qcache_queries_in_cache 1 set query_cache_type=on; # On node-1 connection node_1; -reset query cache; -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 0 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 4 -# On node-2 -connection node_2; -reset query cache; -show status like "Qcache_queries_in_cache"; -Variable_name Value -Qcache_queries_in_cache 0 -show status like "Qcache_hits"; -Variable_name Value -Qcache_hits 3 -# On node-1 -connection node_1; select sql_no_cache * from t1; a 1 @@ -1318,9 +1300,6 @@ Qcache_queries_in_cache 0 show status like "Qcache_hits"; Variable_name Value Qcache_hits 12 -select count(*) from t1; -count(*) -70 # On node-2 connection node_2; select count(*) from t1; @@ -1328,7 +1307,7 @@ count(*) 70 show status like "Qcache_queries_in_cache"; Variable_name Value -Qcache_queries_in_cache 1 +Qcache_queries_in_cache 2 show status like "Qcache_hits"; Variable_name Value Qcache_hits 11 @@ -1515,7 +1494,7 @@ Variable_name Value Qcache_hits 11 show status like "Qcache_inserts"; Variable_name Value -Qcache_inserts 34 +Qcache_inserts 35 unlock table; drop view v1; set query_cache_wlock_invalidate=default; @@ -1626,7 +1605,7 @@ Variable_name Value Qcache_hits 11 show status like "Qcache_inserts"; Variable_name Value -Qcache_inserts 36 +Qcache_inserts 37 set character_set_client=cp1251; SELECT a,'Â','â'='Â' FROM t1; a ?? '??'='?‚' @@ -1640,7 +1619,7 @@ Variable_name Value Qcache_hits 11 show status like "Qcache_inserts"; Variable_name Value -Qcache_inserts 37 +Qcache_inserts 38 set character_set_results=cp1251; SELECT a,'Â','â'='Â' FROM t1; a Ã? 'â'='Â' @@ -1654,7 +1633,7 @@ Variable_name Value Qcache_hits 11 show status like "Qcache_inserts"; Variable_name Value -Qcache_inserts 38 +Qcache_inserts 39 drop table t1; # # Comments before command @@ -1665,9 +1644,6 @@ create table t1 (a int) engine=innodb; show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 -show status like "Qcache_inserts"; -Variable_name Value -Qcache_inserts 46 show status like "Qcache_hits"; Variable_name Value Qcache_hits 12 @@ -1678,9 +1654,6 @@ a show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 1 -show status like "Qcache_inserts"; -Variable_name Value -Qcache_inserts 47 show status like "Qcache_hits"; Variable_name Value Qcache_hits 13 @@ -1689,9 +1662,6 @@ connection node_2; show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 -show status like "Qcache_inserts"; -Variable_name Value -Qcache_inserts 38 show status like "Qcache_hits"; Variable_name Value Qcache_hits 11 @@ -1702,9 +1672,6 @@ a show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 1 -show status like "Qcache_inserts"; -Variable_name Value -Qcache_inserts 39 show status like "Qcache_hits"; Variable_name Value Qcache_hits 12 diff --git a/mysql-test/suite/galera/suite.pm b/mysql-test/suite/galera/suite.pm index 4e6927b3213..233aa020a94 100644 --- a/mysql-test/suite/galera/suite.pm +++ b/mysql-test/suite/galera/suite.pm @@ -81,6 +81,8 @@ push @::global_suppressions, qr|WSREP: .*core_handle_uuid_msg.*|, qr(WSREP: --wsrep-causal-reads=ON takes precedence over --wsrep-sync-wait=0. WSREP_SYNC_WAIT_BEFORE_READ is on), qr|WSREP: JOIN message from member .* in non-primary configuration. Ignored.|, + qr(WSREP: Failed to remove page file .*), + qr(WSREP: wsrep_sst_method is set to 'mysqldump' yet mysqld bind_address is set to .*), ); $ENV{PATH}="$epath:$ENV{PATH}"; diff --git a/mysql-test/suite/galera/t/MW-286.test b/mysql-test/suite/galera/t/MW-286.test index 1b2e322f078..426b4493bb7 100644 --- a/mysql-test/suite/galera/t/MW-286.test +++ b/mysql-test/suite/galera/t/MW-286.test @@ -7,7 +7,7 @@ --source include/big_test.inc --connection node_1 -CREATE TABLE ten (f1 INTEGER); +CREATE TABLE ten (f1 INTEGER) Engine=InnoDB; INSERT INTO ten VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); CREATE TABLE t1 (f1 INTEGER) Engine=InnoDB; @@ -22,11 +22,13 @@ INSERT INTO t1 (f1) SELECT 000000 + (10000 * a1.f1) + (1000 * a2.f1) + (100 * a3 SET GLOBAL wsrep_desync = TRUE; SET wsrep_on = FALSE; ---error ER_QUERY_INTERRUPTED +--error 0,ER_QUERY_INTERRUPTED ALTER TABLE t1 ADD PRIMARY KEY (f1); SET wsrep_on = TRUE; SET GLOBAL wsrep_desync = FALSE; +--connection node_1 +reap; DROP TABLE t1; DROP TABLE ten; diff --git a/mysql-test/suite/galera/t/MW-328A.test b/mysql-test/suite/galera/t/MW-328A.test index 4d6e1ea3625..09aad1bcf60 100644 --- a/mysql-test/suite/galera/t/MW-328A.test +++ b/mysql-test/suite/galera/t/MW-328A.test @@ -7,6 +7,7 @@ # a success was reported. # +--source include/big_test.inc --source include/galera_cluster.inc --source include/have_innodb.inc --source suite/galera/t/MW-328-header.inc diff --git a/mysql-test/suite/galera/t/MW-328B.test b/mysql-test/suite/galera/t/MW-328B.test index a7b4053ab0c..000b0d8a9ab 100644 --- a/mysql-test/suite/galera/t/MW-328B.test +++ b/mysql-test/suite/galera/t/MW-328B.test @@ -7,6 +7,7 @@ # gets the deadlock error # +--source include/big_test.inc --source include/galera_cluster.inc --source include/have_innodb.inc --source suite/galera/t/MW-328-header.inc diff --git a/mysql-test/suite/galera/t/MW-328C.test b/mysql-test/suite/galera/t/MW-328C.test index b681e743ab3..72a8480923c 100644 --- a/mysql-test/suite/galera/t/MW-328C.test +++ b/mysql-test/suite/galera/t/MW-328C.test @@ -7,6 +7,7 @@ # masks all deadlock errors # +--source include/big_test.inc --source include/galera_cluster.inc --source include/have_innodb.inc --source suite/galera/t/MW-328-header.inc diff --git a/mysql-test/suite/galera/t/MW-44-master.opt b/mysql-test/suite/galera/t/MW-44-master.opt index a15aa0a99d9..9b086195e8a 100644 --- a/mysql-test/suite/galera/t/MW-44-master.opt +++ b/mysql-test/suite/galera/t/MW-44-master.opt @@ -1 +1,2 @@ --log-output=TABLE +--general-log=OFF diff --git a/mysql-test/suite/galera/t/MW-44.test b/mysql-test/suite/galera/t/MW-44.test index e8caa28c80e..8730631edc6 100644 --- a/mysql-test/suite/galera/t/MW-44.test +++ b/mysql-test/suite/galera/t/MW-44.test @@ -6,33 +6,37 @@ --source include/have_innodb.inc --connection node_1 -SET GLOBAL general_log='OFF'; TRUNCATE TABLE mysql.general_log; ---let $wait_condition = SELECT COUNT(*) = 0 FROM mysql.general_log; ---source include/wait_condition.inc --sleep 1 --connection node_2 -SET GLOBAL general_log='OFF'; -TRUNCATE TABLE mysql.general_log; --let $wait_condition = SELECT COUNT(*) = 0 FROM mysql.general_log; --source include/wait_condition.inc +TRUNCATE TABLE mysql.general_log; --sleep 1 --connection node_1 -SET GLOBAL general_log='ON'; -SELECT argument from mysql.general_log WHERE argument NOT LIKE 'SELECT%'; +--let $wait_condition = SELECT COUNT(*) = 0 FROM mysql.general_log; +--source include/wait_condition.inc +SELECT Argument FROM mysql.general_log; +SET GLOBAL general_log='ON'; SET SESSION wsrep_osu_method=TOI; CREATE TABLE t1 (f1 INTEGER) ENGINE=InnoDB; SET SESSION wsrep_osu_method=RSU; ALTER TABLE t1 ADD COLUMN f2 INTEGER; SET SESSION wsrep_osu_method=TOI; -SELECT COUNT(*) = 2 FROM mysql.general_log WHERE argument LIKE 'CREATE%' OR argument LIKE 'ALTER%'; +--let $wait_condition = SELECT COUNT(argument) = 2 FROM mysql.general_log WHERE argument LIKE 'CREATE%' OR argument LIKE 'ALTER%'; +--source include/wait_condition.inc + +SELECT argument FROM mysql.general_log WHERE argument LIKE 'CREATE%' OR argument LIKE 'ALTER%'; --connection node_2 -SET GLOBAL general_log='ON'; -SELECT COUNT(*) = 0 FROM mysql.general_log WHERE argument NOT LIKE 'SELECT%'; +SELECT Argument FROM mysql.general_log; DROP TABLE t1; +SET GLOBAL general_log='OFF'; + +--connection node_1 +SET GLOBAL general_log='OFF'; diff --git a/mysql-test/suite/galera/t/galera#505.test b/mysql-test/suite/galera/t/galera#505.test new file mode 100644 index 00000000000..785b1411596 --- /dev/null +++ b/mysql-test/suite/galera/t/galera#505.test @@ -0,0 +1,26 @@ +# galera#505 - Change of pc.weight wsrep param will be correctly stored in wsrep_provider_options variable + +--source include/galera_cluster.inc + +--connection node_1 + +SET SESSION wsrep_sync_wait=0; +--disable_result_log +--disable_query_log +--let $galera_version=25.3.24 +source ../../wsrep/include/check_galera_version.inc; +--enable_result_log +--enable_query_log +SET SESSION wsrep_sync_wait=DEFAULT; + +# Convert "... pc.weight = N; ..." to "N; ..." +--let $s1 = `SELECT SUBSTR(@@wsrep_provider_options, LOCATE('pc.weight =', @@wsrep_provider_options) + LENGTH('pc.weight = '))` +# Convert "N; ..." to "N" +--let $pc_weight_value = `SELECT SUBSTR('$s1', 1, LOCATE(';', '$s1') - 1)` + +SET GLOBAL wsrep_provider_options = 'pc.weight=3'; + +-- replace_regex /.*(pc\.weight = [0-9]+);.*/\1/ +SHOW GLOBAL VARIABLES LIKE 'wsrep_provider_options'; + +--eval SET GLOBAL wsrep_provider_options = 'pc.weight=$pc_weight_value' diff --git a/mysql-test/suite/galera/t/galera_binlog_stmt_autoinc.test b/mysql-test/suite/galera/t/galera_binlog_stmt_autoinc.test new file mode 100644 index 00000000000..d3889a89016 --- /dev/null +++ b/mysql-test/suite/galera/t/galera_binlog_stmt_autoinc.test @@ -0,0 +1,230 @@ +## +## Tests the auto-increment with binlog in STATEMENT mode. +## + +--source include/galera_cluster.inc +--source include/have_innodb.inc + +--let $node_1=node_1 +--let $node_2=node_2 +--source include/auto_increment_offset_save.inc + +## +## Verify the correct operation of the auto-increment when the binlog +## format artificially set to the 'STATEMENT' (although this mode is +## not recommended in the current version): +## + +--connection node_2 +SET GLOBAL wsrep_forced_binlog_format='STATEMENT'; + +--connection node_1 +SET GLOBAL wsrep_forced_binlog_format='STATEMENT'; + +CREATE TABLE t1 ( + i int(11) NOT NULL AUTO_INCREMENT, + c char(32) DEFAULT 'dummy_text', + PRIMARY KEY (i) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +insert into t1(i) values(null); + +select * from t1; + +insert into t1(i) values(null), (null), (null); + +select * from t1; + +--connection node_2 +--let $wait_condition = SELECT COUNT(*) = 4 FROM t1; +--source include/wait_condition.inc +select * from t1; + +SET GLOBAL wsrep_forced_binlog_format='none'; + +--connection node_1 + +SET GLOBAL wsrep_forced_binlog_format='none'; + +drop table t1; + +## +## Check the operation when the automatic control over the auto-increment +## settings is switched off, that is, when we use the increment step and +## the offset specified by the user. In the current session, the binlog +## format is set to 'STATEMENT'. It is important that the values of the +## auto-increment options does not changed on other node - it allows us +## to check the correct transmission of the auto-increment options to +## other nodes: +## + +--disable_warnings +SET SESSION binlog_format='STATEMENT'; +--enable_warnings + +show variables like 'binlog_format'; + +SET GLOBAL wsrep_auto_increment_control='OFF'; + +SET SESSION auto_increment_increment = 3; +SET SESSION auto_increment_offset = 1; + +CREATE TABLE t1 ( + i int(11) NOT NULL AUTO_INCREMENT, + c char(32) DEFAULT 'dummy_text', + PRIMARY KEY (i) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +insert into t1(i) values(null); + +select * from t1; + +insert into t1(i) values(null), (null), (null); + +select * from t1; + +--connection node_2 +--let $wait_condition = SELECT COUNT(*) = 4 FROM t1; +--source include/wait_condition.inc + +select * from t1; + +--connection node_1 + +## +## Verify the return to automatic calculation of the step +## and offset of the auto-increment: +## + +SET GLOBAL wsrep_auto_increment_control='ON'; + +SET SESSION binlog_format='ROW'; + +show variables like 'binlog_format'; +show variables like '%auto_increment%'; + +## +## Verify the recovery of original user-defined values after +## stopping the automatic control over auto-increment: +## + +SET GLOBAL wsrep_auto_increment_control='OFF'; + +show variables like '%auto_increment%'; + +## +## Restore original options and drop test table: +## + +SET GLOBAL wsrep_auto_increment_control='ON'; + +drop table t1; + +## +## Verify the correct operation of the auto-increment when the binlog +## format set to the 'ROW': +## + +--connection node_2 +SET GLOBAL wsrep_forced_binlog_format='ROW'; + +--connection node_1 +SET GLOBAL wsrep_forced_binlog_format='ROW'; + +CREATE TABLE t1 ( + i int(11) NOT NULL AUTO_INCREMENT, + c char(32) DEFAULT 'dummy_text', + PRIMARY KEY (i) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +insert into t1(i) values(null); + +select * from t1; + +insert into t1(i) values(null), (null), (null); + +select * from t1; + +--connection node_2 +--let $wait_condition = SELECT COUNT(*) = 4 FROM t1; +--source include/wait_condition.inc + +select * from t1; + +SET GLOBAL wsrep_forced_binlog_format='none'; + +--connection node_1 + +SET GLOBAL wsrep_forced_binlog_format='none'; + +drop table t1; + +## +## Check the operation when the automatic control over the auto-increment +## settings is switched off, that is, when we use the increment step and +## the offset specified by the user. In the current session, the binlog +## format is set to 'ROW'. It is important that the values of the +## auto-increment options does not changed on other node - it allows us +## to check the correct transmission of the auto-increment options to +## other nodes: +## + +SET SESSION binlog_format='ROW'; + +show variables like 'binlog_format'; + +SET GLOBAL wsrep_auto_increment_control='OFF'; + +SET SESSION auto_increment_increment = 3; +SET SESSION auto_increment_offset = 1; + +CREATE TABLE t1 ( + i int(11) NOT NULL AUTO_INCREMENT, + c char(32) DEFAULT 'dummy_text', + PRIMARY KEY (i) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +insert into t1(i) values(null); + +select * from t1; + +insert into t1(i) values(null), (null), (null); + +select * from t1; + +--connection node_2 +--let $wait_condition = SELECT COUNT(*) = 4 FROM t1; +--source include/wait_condition.inc + +select * from t1; + +--connection node_1 + +## +## Verify the return to automatic calculation of the step +## and offset of the auto-increment: +## + +SET GLOBAL wsrep_auto_increment_control='ON'; + +show variables like 'binlog_format'; +show variables like '%auto_increment%'; + +## +## Verify the recovery of original user-defined values after +## stopping the automatic control over auto-increment: +## + +SET GLOBAL wsrep_auto_increment_control='OFF'; + +show variables like '%auto_increment%'; + +## +## Restore original options and drop test table: +## + +SET GLOBAL wsrep_auto_increment_control='ON'; + +drop table t1; + +--source include/auto_increment_offset_restore.inc diff --git a/mysql-test/suite/galera/t/galera_defaults.test b/mysql-test/suite/galera/t/galera_defaults.test index facc5180f2f..0ad97916302 100644 --- a/mysql-test/suite/galera/t/galera_defaults.test +++ b/mysql-test/suite/galera/t/galera_defaults.test @@ -13,7 +13,7 @@ # Make sure that the test is operating on the right version of galera library. --disable_query_log ---let $galera_version=3.20 +--let $galera_version=25.3.20 source ../wsrep/include/check_galera_version.inc; --enable_query_log @@ -59,14 +59,3 @@ ORDER BY VARIABLE_NAME; $wsrep_provider_options =~ s/repl.proto_max = .*?;\s*/<REPL_PROTO_MAX>;/sgio; print $wsrep_provider_options."\n"; EOF - -# Global Status - -SELECT COUNT(*) FROM INFORMATION_SCHEMA.GLOBAL_STATUS -WHERE VARIABLE_NAME LIKE 'wsrep_%' -AND VARIABLE_NAME != 'wsrep_debug_sync_waiters'; - -SELECT VARIABLE_NAME FROM INFORMATION_SCHEMA.GLOBAL_STATUS -WHERE VARIABLE_NAME LIKE 'wsrep_%' -AND VARIABLE_NAME != 'wsrep_debug_sync_waiters' -ORDER BY VARIABLE_NAME; diff --git a/mysql-test/suite/galera/t/galera_ist_mysqldump.test b/mysql-test/suite/galera/t/galera_ist_mysqldump.test index f60d5549eda..7bfca0334fa 100644 --- a/mysql-test/suite/galera/t/galera_ist_mysqldump.test +++ b/mysql-test/suite/galera/t/galera_ist_mysqldump.test @@ -4,6 +4,8 @@ --source suite/galera/include/galera_sst_set_mysqldump.inc +call mtr.add_suppression("WSREP: wsrep_sst_method is set to 'mysqldump' yet mysqld bind_address is set to .*"); + --let $node_1=node_1 --let $node_2=node_2 --source include/auto_increment_offset_save.inc diff --git a/mysql-test/suite/galera/t/galera_ist_xtrabackup-v2.test b/mysql-test/suite/galera/t/galera_ist_xtrabackup-v2.test index c44b0642342..34961765008 100644 --- a/mysql-test/suite/galera/t/galera_ist_xtrabackup-v2.test +++ b/mysql-test/suite/galera/t/galera_ist_xtrabackup-v2.test @@ -3,8 +3,13 @@ --source include/have_innodb.inc --source include/have_xtrabackup.inc +--let $node_1=node_1 +--let $node_2=node_2 +--source include/auto_increment_offset_save.inc + --source suite/galera/include/galera_st_disconnect_slave.inc --source suite/galera/include/galera_st_shutdown_slave.inc --source suite/galera/include/galera_st_kill_slave.inc --source suite/galera/include/galera_st_kill_slave_ddl.inc +--source include/auto_increment_offset_restore.inc diff --git a/mysql-test/suite/galera/t/galera_kill_ddl.test b/mysql-test/suite/galera/t/galera_kill_ddl.test index 90f3f30cc76..ca59264ced4 100644 --- a/mysql-test/suite/galera/t/galera_kill_ddl.test +++ b/mysql-test/suite/galera/t/galera_kill_ddl.test @@ -5,6 +5,8 @@ --source include/galera_cluster.inc --source include/have_innodb.inc +call mtr.add_suppression("WSREP: Last Applied Action message in non-primary configuration from member .*"); + --connection node_1 # Enable the master to continue running during the split-brain situation that diff --git a/mysql-test/suite/galera/t/galera_kill_largechanges.test b/mysql-test/suite/galera/t/galera_kill_largechanges.test index e9a32ce813b..2803a43d85a 100644 --- a/mysql-test/suite/galera/t/galera_kill_largechanges.test +++ b/mysql-test/suite/galera/t/galera_kill_largechanges.test @@ -6,6 +6,8 @@ --source include/galera_cluster.inc --source include/have_innodb.inc +call mtr.add_suppression("WSREP: Last Applied Action message in non-primary configuration from member .*"); + --connection node_1 # Enable the master to continue running during the split-brain situation that # occurs when the slave is killed diff --git a/mysql-test/suite/galera/t/galera_kill_smallchanges.test b/mysql-test/suite/galera/t/galera_kill_smallchanges.test index d998032cbc3..148c3dbc132 100644 --- a/mysql-test/suite/galera/t/galera_kill_smallchanges.test +++ b/mysql-test/suite/galera/t/galera_kill_smallchanges.test @@ -5,6 +5,8 @@ --source include/galera_cluster.inc --source include/have_innodb.inc +call mtr.add_suppression("WSREP: Last Applied Action message in non-primary configuration from member .*"); + --connection node_1 # Enable the master to continue running during the split-brain situation that diff --git a/mysql-test/suite/galera/t/galera_sst_rsync2.cnf b/mysql-test/suite/galera/t/galera_sst_rsync2.cnf new file mode 100644 index 00000000000..34e67c66403 --- /dev/null +++ b/mysql-test/suite/galera/t/galera_sst_rsync2.cnf @@ -0,0 +1,15 @@ +!include ../galera_2nodes.cnf + +[mysqld] +wsrep_sst_method=rsync + +[mysqld.1] +wsrep_provider_options='base_port=@mysqld.1.#galera_port;gcache.size=1;pc.ignore_sb=true' +log_bin=@ENV.MYSQLTEST_VARDIR/server1_binlog +log_bin_index=@ENV.MYSQLTEST_VARDIR/tmp/server1_binlog_index.index + +[mysqld.2] +wsrep_provider_options='base_port=@mysqld.2.#galera_port;gcache.size=1;pc.ignore_sb=true' +log_bin=@ENV.MYSQLTEST_VARDIR/server2_binlog +log_bin_index=@ENV.MYSQLTEST_VARDIR/tmp/server2_binlog_index.index + diff --git a/mysql-test/suite/galera/t/galera_sst_rsync2.test b/mysql-test/suite/galera/t/galera_sst_rsync2.test new file mode 100644 index 00000000000..f796356cac7 --- /dev/null +++ b/mysql-test/suite/galera/t/galera_sst_rsync2.test @@ -0,0 +1,12 @@ +--source include/galera_cluster.inc + +--let $node_1=node_1 +--let $node_2=node_2 +--source include/auto_increment_offset_save.inc + +--source suite/galera/include/galera_st_shutdown_slave.inc +--source suite/galera/include/galera_st_clean_slave.inc + +--source suite/galera/include/galera_st_kill_slave.inc +--source suite/galera/include/galera_st_kill_slave_ddl.inc +--source include/auto_increment_offset_restore.inc diff --git a/mysql-test/suite/galera/t/galera_sst_xtrabackup-v2_data_dir.cnf b/mysql-test/suite/galera/t/galera_sst_xtrabackup-v2_data_dir.cnf new file mode 100644 index 00000000000..89f23d24d87 --- /dev/null +++ b/mysql-test/suite/galera/t/galera_sst_xtrabackup-v2_data_dir.cnf @@ -0,0 +1,16 @@ +!include ../galera_2nodes.cnf + +[mysqld] +wsrep_sst_method=xtrabackup-v2 +wsrep_sst_auth="root:" +wsrep_debug=ON + +[mysqld.1] +wsrep_provider_options='base_port=@mysqld.1.#galera_port;gcache.size=1;pc.ignore_sb=true' + +[mysqld.2] +innodb_data_home_dir=@ENV.MYSQL_TMP_DIR/rsync_test_2 +wsrep_provider_options='base_port=@mysqld.2.#galera_port;gcache.size=1;pc.ignore_sb=true' + +[sst] +transferfmt=@ENV.MTR_GALERA_TFMT diff --git a/mysql-test/suite/galera/t/galera_sst_xtrabackup-v2_data_dir.test b/mysql-test/suite/galera/t/galera_sst_xtrabackup-v2_data_dir.test new file mode 100644 index 00000000000..135bc2a39f7 --- /dev/null +++ b/mysql-test/suite/galera/t/galera_sst_xtrabackup-v2_data_dir.test @@ -0,0 +1,23 @@ +--source include/big_test.inc +--source include/galera_cluster.inc +--source include/have_innodb.inc +--source include/have_xtrabackup.inc + +# Save original auto_increment_offset values. +--let $node_1=node_1 +--let $node_2=node_2 +--source include/auto_increment_offset_save.inc + +--source suite/galera/include/galera_st_shutdown_slave.inc +--source suite/galera/include/galera_st_clean_slave.inc + +--source suite/galera/include/galera_st_kill_slave.inc +--source suite/galera/include/galera_st_kill_slave_ddl.inc + +# Restore original auto_increment_offset values. +--source include/auto_increment_offset_restore.inc + +--source include/galera_end.inc + +# cleanup temporary database files: +--remove_files_wildcard $MYSQL_TMP_DIR/rsync_test_2 * diff --git a/mysql-test/suite/galera/t/galera_suspend_slave.test b/mysql-test/suite/galera/t/galera_suspend_slave.test index 6f0f8014397..0f95bcd4531 100644 --- a/mysql-test/suite/galera/t/galera_suspend_slave.test +++ b/mysql-test/suite/galera/t/galera_suspend_slave.test @@ -34,6 +34,8 @@ SET SESSION wsrep_sync_wait = 0; --let $wait_condition = SELECT VARIABLE_VALUE = 1 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_size'; --source include/wait_condition.inc +SET SESSION wsrep_sync_wait = 15; + --error ER_UNKNOWN_COM_ERROR,ER_LOCK_WAIT_TIMEOUT,ER_LOCK_DEADLOCK,ER_ERROR_DURING_COMMIT INSERT INTO t1 VALUES (1); @@ -46,6 +48,7 @@ INSERT INTO t1 VALUES (1); exit(0); EOF +SET SESSION wsrep_sync_wait = 0; --source include/wait_until_ready.inc INSERT INTO t1 VALUES (1); diff --git a/mysql-test/suite/galera/t/galera_toi_truncate.test b/mysql-test/suite/galera/t/galera_toi_truncate.test index 59ef5c2028f..0c1d0e45e41 100644 --- a/mysql-test/suite/galera/t/galera_toi_truncate.test +++ b/mysql-test/suite/galera/t/galera_toi_truncate.test @@ -5,6 +5,8 @@ --source include/galera_cluster.inc --source include/have_innodb.inc +--source include/have_debug_sync.inc +--source include/not_embedded.inc # # INSERT and TRUNCATE on different nodes @@ -16,14 +18,23 @@ INSERT INTO ten VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); CREATE TABLE t1 (f1 INTEGER) ENGINE=InnoDB; -# Insert 100K rows +# Insert 1m rows --connection node_2 +--let $wait_condition = SELECT COUNT(*) = 10 FROM ten; +--source include/wait_condition.inc + # Prevent autocommit retring from masking the deadlock error we expect to get SET SESSION wsrep_retry_autocommit = 0; +INSERT INTO t1(f1) SELECT 1 FROM ten as a1, ten AS a2; + +set debug_sync='ha_commit_trans_after_prepare WAIT_FOR go'; --send INSERT INTO t1 (f1) SELECT 1 FROM ten AS a1, ten AS a2, ten AS a3, ten AS a4, ten AS a5, ten AS a6; --connection node_1 ---sleep 1 +# Wait for a above insert to start +--let $wait_condition = SELECT COUNT(*) >= 100 from t1; +--source include/wait_condition.inc + --send TRUNCATE TABLE t1; --connection node_1 @@ -38,6 +49,5 @@ SELECT COUNT(*) = 0 FROM t1; --connection node_1 SELECT COUNT(*) = 0 FROM t1; - DROP TABLE t1; DROP TABLE ten; diff --git a/mysql-test/suite/galera/t/galera_unicode_identifiers.test b/mysql-test/suite/galera/t/galera_unicode_identifiers.test index f3df60a6415..c0c95768650 100644 --- a/mysql-test/suite/galera/t/galera_unicode_identifiers.test +++ b/mysql-test/suite/galera/t/galera_unicode_identifiers.test @@ -13,6 +13,8 @@ SET GLOBAL wsrep_sync_wait = 15; --connection node_1 +--let $innodb_num_tables_orig = `SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES` + # Spaces in identifiers CREATE DATABASE `database with space`; @@ -21,7 +23,7 @@ CREATE TABLE `table with space` ( `column with space` INTEGER AUTO_INCREMENT PRIMARY KEY, `second column with space` INTEGER, UNIQUE `index name with space` (`second column with space`) -); +) engine=innodb; INSERT INTO `table with space` VALUES (1, 1); # Unicode identifiers @@ -32,7 +34,7 @@ CREATE TABLE `таблица` ( `първа_колона` INTEGER PRIMARY KEY, `втора_колона` INTEGER, UNIQUE `индекÑ` (`втора_колона`) -); +) engine=innodb; INSERT INTO `таблица` VALUES (1, 1); @@ -44,11 +46,15 @@ CREATE TABLE `втора таблица` ( `първа колона` INTEGER, `втора колона` INTEGER, KEY `първи индекÑ` (`първа колона`) -); +) engine=innodb; INSERT INTO `втора таблица` VALUES (1, 1); --connection node_2 +# Wait until 3 above tables with databases are created also to this node +--let $wait_condition = SELECT COUNT(*) = $innodb_num_tables_orig + 3 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES; +--source include/wait_condition.inc + USE `database with space`; SELECT `second column with space` FROM `table with space`; diff --git a/mysql-test/suite/galera/t/galera_var_node_address.test b/mysql-test/suite/galera/t/galera_var_node_address.test index 22e98e3aa82..b50265be5ae 100644 --- a/mysql-test/suite/galera/t/galera_var_node_address.test +++ b/mysql-test/suite/galera/t/galera_var_node_address.test @@ -13,13 +13,19 @@ call mtr.add_suppression("WSREP: Sending JOIN failed: -[0-9]+ (Transport endpoi SELECT VARIABLE_VALUE = 4 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_size'; --connection node_1 -CREATE TABLE t1 (f1 INTEGER); +CREATE TABLE t1 (f1 INTEGER) ENGINE=INNODB; --connection node_2 +let $wait_condition= SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE 'test/t1'; +--source include/wait_condition.inc INSERT INTO t1 VALUES (1); --connect node_3, 127.0.0.1, root, , test, $NODE_MYPORT_3 --connection node_3 +let $wait_condition= SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE 'test/t1'; +--source include/wait_condition.inc +let $wait_condition= SELECT COUNT(*) = 1 FROM t1; +--source include/wait_condition.inc SELECT COUNT(*) = 1 FROM t1; --connection node_1 diff --git a/mysql-test/suite/galera/t/galera_var_slave_threads.test b/mysql-test/suite/galera/t/galera_var_slave_threads.test index e5986f7ee12..80edcb2aff9 100644 --- a/mysql-test/suite/galera/t/galera_var_slave_threads.test +++ b/mysql-test/suite/galera/t/galera_var_slave_threads.test @@ -33,6 +33,12 @@ SET GLOBAL wsrep_slave_threads = 64; INSERT INTO t1 VALUES (1); --connection node_2 +--let $wait_timeout=600 +--let $wait_condition = SELECT COUNT(*) = 1 FROM t1; +--source include/wait_condition.inc + +SELECT COUNT(*) = 1 FROM t1; + --let $wait_condition = SELECT COUNT(*) = @@wsrep_slave_threads + 1 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'system user' AND (STATE IS NULL OR STATE NOT LIKE 'InnoDB%'); --source include/wait_condition.inc @@ -44,6 +50,8 @@ SET GLOBAL wsrep_slave_threads = 1; --connection node_1 +--disable_result_log +--disable_query_log # Generate 64 replication events --let $count = 64 while ($count) @@ -51,6 +59,8 @@ while ($count) INSERT INTO t2 VALUES (DEFAULT); --dec $count } +--enable_query_log +--enable_result_log --connection node_2 SELECT COUNT(*) FROM t2; diff --git a/mysql-test/suite/galera/t/galera_wan.test b/mysql-test/suite/galera/t/galera_wan.test index a8fd351b168..ca86a3000a6 100644 --- a/mysql-test/suite/galera/t/galera_wan.test +++ b/mysql-test/suite/galera/t/galera_wan.test @@ -9,6 +9,12 @@ --source include/galera_cluster.inc --source include/have_innodb.inc +CALL mtr.add_suppression("WSREP: Stray state UUID msg:.*"); +CALL mtr.add_suppression("WSREP: Sending JOIN failed:.*"); +CALL mtr.add_suppression("There are no nodes in the same segment that will ever be able to become donors, yet there is a suitable donor outside"); +call mtr.add_suppression("WSREP: Sending JOIN failed:.*"); + + SELECT VARIABLE_VALUE = 4 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_size'; --connection node_1 @@ -16,15 +22,19 @@ CREATE TABLE t1 (f1 INTEGER); --connect node_3, 127.0.0.1, root, , test, $NODE_MYPORT_3 --connection node_3 +--let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE 'test/t1'; +--source include/wait_condition.inc INSERT INTO t1 VALUES (1); -CALL mtr.add_suppression("There are no nodes in the same segment that will ever be able to become donors, yet there is a suitable donor outside"); --connect node_4, 127.0.0.1, root, , test, $NODE_MYPORT_4 --connection node_4 +--let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE 'test/t1'; +--source include/wait_condition.inc +--let $wait_condition = SELECT COUNT(*) = 1 FROM t1; +--source include/wait_condition.inc SELECT VARIABLE_VALUE LIKE '%gmcast.segment = 3%' FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'wsrep_provider_options'; SELECT COUNT(*) = 1 FROM t1; DROP TABLE t1; -CALL mtr.add_suppression("There are no nodes in the same segment that will ever be able to become donors, yet there is a suitable donor outside"); diff --git a/mysql-test/suite/galera/t/mysql-wsrep#332.test b/mysql-test/suite/galera/t/mysql-wsrep#332.test new file mode 100644 index 00000000000..2da01ba900e --- /dev/null +++ b/mysql-test/suite/galera/t/mysql-wsrep#332.test @@ -0,0 +1,113 @@ +--source include/galera_cluster.inc +--source include/have_innodb.inc +--source include/have_debug_sync.inc +--source suite/galera/include/galera_have_debug_sync.inc + +# Open connection node_1a here, MW-369.inc will use it later +--connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1 + +# +# Test the scenario where a foreign key is added to an existing child table, and +# concurrently UPDATE the parent table so that it violates the constraint. +# +# We expect that ALTER TABLE ADD FOREIGN KEY adds a table level key on both +# parent and child table. And therefore we also expect the UPDATE to fail +# certification. +# +--connection node_1 +CREATE TABLE p (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB; +CREATE TABLE c (f1 INTEGER PRIMARY KEY, p_id INTEGER) ENGINE=INNODB; + +INSERT INTO p VALUES (1, 0); +INSERT INTO p VALUES (2, 0); + +INSERT INTO c VALUES (1, 1); +INSERT INTO c VALUES (2, 2); + +--let $mw_369_parent_query = UPDATE p SET f1 = f1 + 100 +--let $mw_369_child_query = ALTER TABLE c ADD FOREIGN KEY (p_id) REFERENCES p(f1) + +--source MW-369.inc + +# Expect certification failure +--connection node_1 +--error ER_LOCK_DEADLOCK +--reap + +--connection node_2 +SELECT * FROM p; +SELECT * FROM c; + +DROP TABLE c; +DROP TABLE p; + + +# +# Same as above, except that two foreign keys pointing to different parent +# tables are added, p1 and p2. Concurrently UPDATE p1. +# +# Expect certification error on UPDATE. +# +--connection node_1 +CREATE TABLE p1 (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB; +CREATE TABLE p2 (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB; +CREATE TABLE c (f1 INTEGER PRIMARY KEY, p_id1 INTEGER, p_id2 INTEGER) ENGINE=INNODB; + +INSERT INTO p1 VALUES (1, 0), (2, 0); +INSERT INTO p2 VALUES (1, 0), (2, 0); + +INSERT INTO c VALUES (1, 1, 1); +INSERT INTO c VALUES (2, 2, 2); + +--let $mw_369_parent_query = UPDATE p1 SET f1 = f1 + 100 +--let $mw_369_child_query = ALTER TABLE c ADD FOREIGN KEY (p_id1) REFERENCES p1(f1), ADD FOREIGN KEY (p_id2) REFERENCES p2(f1) + +--source MW-369.inc + +# Expect certification failure +--connection node_1 +--error ER_LOCK_DEADLOCK +--reap + +--connection node_2 +SELECT * FROM p1; +SELECT * FROM p2; +SELECT * FROM c; + +DROP TABLE c; +DROP TABLE p1; +DROP TABLE p2; + + +# +# Same as above, except that UPDATE is on p2. +# +--connection node_1 +CREATE TABLE p1 (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB; +CREATE TABLE p2 (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB; +CREATE TABLE c (f1 INTEGER PRIMARY KEY, p_id1 INTEGER, p_id2 INTEGER) ENGINE=INNODB; + +INSERT INTO p1 VALUES (1, 0), (2, 0); +INSERT INTO p2 VALUES (1, 0), (2, 0); + +INSERT INTO c VALUES (1, 1, 1); +INSERT INTO c VALUES (2, 2, 2); + +--let $mw_369_parent_query = UPDATE p2 SET f1 = f1 + 100 +--let $mw_369_child_query = ALTER TABLE c ADD FOREIGN KEY (p_id1) REFERENCES p1(f1), ADD FOREIGN KEY (p_id2) REFERENCES p2(f1) + +--source MW-369.inc + +# Expect certification failure +--connection node_1 +--error ER_LOCK_DEADLOCK +--reap + +--connection node_2 +SELECT * FROM p1; +SELECT * FROM p2; +SELECT * FROM c; + +DROP TABLE c; +DROP TABLE p1; +DROP TABLE p2; diff --git a/mysql-test/suite/galera/t/query_cache.test b/mysql-test/suite/galera/t/query_cache.test index 13b21eca6e7..aeec67fb576 100644 --- a/mysql-test/suite/galera/t/query_cache.test +++ b/mysql-test/suite/galera/t/query_cache.test @@ -114,6 +114,11 @@ show status like "Qcache_queries_in_cache"; show status like "Qcache_hits"; # +# RESET QUERY CACHE +# +--source include/reset_query_cache.inc + +# # MERGE TABLES with INSERT/UPDATE and DELETE # --echo @@ -181,6 +186,11 @@ show status like "Qcache_hits"; drop table t1, t2, t3; # +# RESET QUERY CACHE +# +--source include/reset_query_cache.inc + +# # SELECT SQL_CACHE ... # --echo # On node-1 @@ -214,16 +224,7 @@ set query_cache_type=on; # # RESET QUERY CACHE # ---echo # On node-1 ---connection node_1 -reset query cache; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; ---echo # On node-2 ---connection node_2 -reset query cache; -show status like "Qcache_queries_in_cache"; -show status like "Qcache_hits"; +--source include/reset_query_cache.inc # # SELECT SQL_NO_CACHE @@ -241,6 +242,11 @@ show status like "Qcache_hits"; drop table t1; # +# RESET QUERY CACHE +# +--source include/reset_query_cache.inc + +# # Check that queries that uses NOW(), LAST_INSERT_ID()... are not cached. # --echo # On node-1 @@ -283,6 +289,11 @@ show status like "Qcache_hits"; drop table t1; # +# RESET QUERY CACHE +# +--source include/reset_query_cache.inc + +# # Non-cachable ODBC work around (and prepare cache for drop database) # --echo # On node-1 @@ -317,6 +328,11 @@ show status like "Qcache_hits"; drop table t1; # +# RESET QUERY CACHE +# +--source include/reset_query_cache.inc + +# # Charset conversion (cp1251_koi8 always present) # Note: Queries using different default character sets are cached separately. # @@ -344,6 +360,11 @@ show status like "Qcache_hits"; drop table t1; # +# RESET QUERY CACHE +# +--source include/reset_query_cache.inc + +# # Same tables in different dbs # --echo # On node-1 @@ -377,6 +398,11 @@ drop database mysqltest; drop table t1; # +# RESET QUERY CACHE +# +--source include/reset_query_cache.inc + +# # FOUND_ROWS() # --echo # On node-1 @@ -429,6 +455,11 @@ show status like "Qcache_queries_in_cache"; drop table t1; # +# RESET QUERY CACHE +# +--source include/reset_query_cache.inc + +# # Test insert delayed (MYISAM) # @@ -532,6 +563,11 @@ set GLOBAL query_cache_min_res_unit=default; show global variables like "query_cache_min_res_unit"; # +# RESET QUERY CACHE +# +--source include/reset_query_cache.inc + +# # Case sensitive test # --echo # On node-1 @@ -554,6 +590,11 @@ show status like "Qcache_inserts"; drop table t1; # +# RESET QUERY CACHE +# +--source include/reset_query_cache.inc + +# # Test of query cache resizing # --echo # On node-1 @@ -620,6 +661,11 @@ select * from t1; drop table t1; # +# RESET QUERY CACHE +# +--source include/reset_query_cache.inc + +# # Temporary tables (ignored by Galera) # --echo # On node-1 @@ -653,6 +699,11 @@ show status like "Qcache_hits"; drop table t1, t2; # +# RESET QUERY CACHE +# +--source include/reset_query_cache.inc + +# # System databse test (no need to perform it on node_2) # Note: Queries on system tables are not cached. # @@ -705,6 +756,11 @@ show status like "Qcache_hits"; drop table t2; # +# RESET QUERY CACHE +# +--source include/reset_query_cache.inc + +# # Load data invalidation test # --echo # On node-1 @@ -717,13 +773,13 @@ show status like "Qcache_hits"; eval load data infile '$MYSQLTEST_VARDIR/std_data/words.dat' into table t1; show status like "Qcache_queries_in_cache"; show status like "Qcache_hits"; -select count(*) from t1; - -# Wait for "load data" to replicate. ---sleep 5 +--let $wait_condition = SELECT COUNT(*) = 70 FROM t1; +--source include/wait_condition.inc --echo # On node-2 --connection node_2 +--let $wait_condition = SELECT COUNT(*) = 70 FROM t1; +--source include/wait_condition.inc select count(*) from t1; show status like "Qcache_queries_in_cache"; show status like "Qcache_hits"; @@ -735,6 +791,11 @@ select count(*) from t1; drop table t1; +# +# RESET QUERY CACHE +# +--source include/reset_query_cache.inc + --echo # --echo # INTO OUTFILE/DUMPFILE test --echo # @@ -756,6 +817,11 @@ let $datadir=`select @@datadir`; --remove_file $datadir/test/query_cache.dump.file --remove_file $datadir/test/query_cache.out.file +# +# RESET QUERY CACHE +# +--source include/reset_query_cache.inc + --echo # --echo # Test of SQL_SELECT_LIMIT --echo # @@ -786,6 +852,11 @@ SET SQL_SELECT_LIMIT=DEFAULT; drop table t1; +# +# RESET QUERY CACHE +# +--source include/reset_query_cache.inc + --echo # --echo # WRITE LOCK & QC --echo # @@ -857,6 +928,11 @@ drop table t1; drop table t1; # +# RESET QUERY CACHE +# +--source include/reset_query_cache.inc + +# # Test character set related variables: # character_set_result # character_set_client @@ -944,6 +1020,11 @@ show status like "Qcache_inserts"; drop table t1; +# +# RESET QUERY CACHE +# +--source include/reset_query_cache.inc + --echo # --echo # Comments before command --echo # @@ -952,27 +1033,28 @@ drop table t1; --connection node_1 create table t1 (a int) engine=innodb; show status like "Qcache_queries_in_cache"; -show status like "Qcache_inserts"; show status like "Qcache_hits"; /**/ select * from t1; /**/ select * from t1; show status like "Qcache_queries_in_cache"; -show status like "Qcache_inserts"; show status like "Qcache_hits"; --echo # On node-2 --connection node_2 show status like "Qcache_queries_in_cache"; -show status like "Qcache_inserts"; show status like "Qcache_hits"; /**/ select * from t1; /**/ select * from t1; show status like "Qcache_queries_in_cache"; -show status like "Qcache_inserts"; show status like "Qcache_hits"; drop table t1; +# +# RESET QUERY CACHE +# +--source include/reset_query_cache.inc + --echo # --echo # Information schema & query cache test --echo # @@ -1011,6 +1093,11 @@ show status like 'qcache_queries_in_cache'; show status like "Qcache_hits"; drop table t1; +# +# RESET QUERY CACHE +# +--source include/reset_query_cache.inc + --echo # Restore original settings. --echo # On node-1 --connection node_1 diff --git a/mysql-test/suite/galera_3nodes/r/galera_pc_weight.result b/mysql-test/suite/galera_3nodes/r/galera_pc_weight.result index 6fb931638ef..9f845ffe776 100644 --- a/mysql-test/suite/galera_3nodes/r/galera_pc_weight.result +++ b/mysql-test/suite/galera_3nodes/r/galera_pc_weight.result @@ -1,11 +1,17 @@ +SELECT VARIABLE_VALUE = 3 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_weight'; +VARIABLE_VALUE = 3 SET GLOBAL wsrep_provider_options = 'pc.weight=3'; -Suspending node ... +SELECT VARIABLE_VALUE = 5 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_weight'; +VARIABLE_VALUE = 5 +SET GLOBAL wsrep_provider_options = 'gmcast.isolate=1'; SET SESSION wsrep_sync_wait=0; SET SESSION wsrep_on=OFF; SET SESSION wsrep_on=ON; SHOW STATUS LIKE 'wsrep_cluster_size'; Variable_name Value wsrep_cluster_size 2 +SHOW STATUS LIKE 'wsrep_cluster_weight'; +Variable_name Value SHOW STATUS LIKE 'wsrep_cluster_status'; Variable_name Value wsrep_cluster_status non-Primary @@ -22,9 +28,13 @@ SHOW STATUS LIKE 'wsrep_local_state_comment'; Variable_name Value wsrep_local_state_comment Initialized SET SESSION wsrep_sync_wait=0; +SET SESSION wsrep_on=OFF; +SET SESSION wsrep_on=ON; SHOW STATUS LIKE 'wsrep_cluster_size'; Variable_name Value wsrep_cluster_size 2 +SHOW STATUS LIKE 'wsrep_cluster_weight'; +Variable_name Value SHOW STATUS LIKE 'wsrep_cluster_status'; Variable_name Value wsrep_cluster_status non-Primary @@ -40,7 +50,8 @@ wsrep_local_state 0 SHOW STATUS LIKE 'wsrep_local_state_comment'; Variable_name Value wsrep_local_state_comment Initialized -Resuming node ... +SELECT VARIABLE_VALUE = 3 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_weight'; +VARIABLE_VALUE = 3 SELECT VARIABLE_VALUE = 'Primary' FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_status'; VARIABLE_VALUE = 'Primary' 1 @@ -57,11 +68,14 @@ SELECT VARIABLE_VALUE = 'Synced' FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VAR VARIABLE_VALUE = 'Synced' 1 SET GLOBAL wsrep_provider_options = 'pc.weight=1'; -SET SESSION wsrep_sync_wait=0; -SET SESSION wsrep_sync_wait=0; +SELECT VARIABLE_VALUE = 1 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_weight'; +VARIABLE_VALUE = 1 +SET GLOBAL wsrep_provider_options = 'gmcast.isolate=0'; SELECT VARIABLE_VALUE = 3 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_size'; VARIABLE_VALUE = 3 1 +SELECT VARIABLE_VALUE = 3 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_weight'; +VARIABLE_VALUE = 3 SELECT VARIABLE_VALUE = 'Primary' FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_status'; VARIABLE_VALUE = 'Primary' 1 @@ -80,6 +94,8 @@ VARIABLE_VALUE = 'Synced' SELECT VARIABLE_VALUE = 3 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_size'; VARIABLE_VALUE = 3 1 +SELECT VARIABLE_VALUE = 3 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_weight'; +VARIABLE_VALUE = 3 SELECT VARIABLE_VALUE = 'Primary' FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_status'; VARIABLE_VALUE = 'Primary' 1 @@ -98,6 +114,8 @@ VARIABLE_VALUE = 'Synced' SELECT VARIABLE_VALUE = 3 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_size'; VARIABLE_VALUE = 3 1 +SELECT VARIABLE_VALUE = 3 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_weight'; +VARIABLE_VALUE = 3 SELECT VARIABLE_VALUE = 'Primary' FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_status'; VARIABLE_VALUE = 'Primary' 1 @@ -116,6 +134,9 @@ VARIABLE_VALUE = 'Synced' SET GLOBAL wsrep_provider_options = 'pc.weight=1'; CALL mtr.add_suppression('WSREP: gcs_caused\\(\\) returned -1'); CALL mtr.add_suppression('overriding reported weight for'); +CALL mtr.add_suppression('SYNC message from member'); +CALL mtr.add_suppression('user message in state LEAVING'); +CALL mtr.add_suppression('sending install message failed: (Transport endpoint is not connected|Socket is not connected)'); CALL mtr.add_suppression('WSREP: user message in state LEAVING'); -CALL mtr.add_suppression('sending install message failed: Transport endpoint is not connected'); +CALL mtr.add_suppression('sending install message failed: (Transport endpoint is not connected|Socket is not connected)'); CALL mtr.add_suppression('overriding reported weight for'); diff --git a/mysql-test/suite/galera_3nodes/t/galera_pc_weight.test b/mysql-test/suite/galera_3nodes/t/galera_pc_weight.test index d69881aa5eb..0a94e7cd85d 100644 --- a/mysql-test/suite/galera_3nodes/t/galera_pc_weight.test +++ b/mysql-test/suite/galera_3nodes/t/galera_pc_weight.test @@ -8,9 +8,12 @@ --source include/have_innodb.inc --connection node_1 +SELECT VARIABLE_VALUE = 3 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_weight'; SET GLOBAL wsrep_provider_options = 'pc.weight=3'; ---source include/galera_suspend.inc ---sleep 10 +SELECT VARIABLE_VALUE = 5 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_weight'; + +# Isolate node_1 from the cluster. +SET GLOBAL wsrep_provider_options = 'gmcast.isolate=1'; --connection node_2 # Do not wait for causality as we are no longer in the primary component @@ -22,8 +25,9 @@ SET SESSION wsrep_on=ON; # We can not use SELECT queries here, as only SHOW is allowed to run. # For nodes #2 and #3, we expect a non-primary component of size 2 - +# and cluster weight 0 SHOW STATUS LIKE 'wsrep_cluster_size'; +SHOW STATUS LIKE 'wsrep_cluster_weight'; SHOW STATUS LIKE 'wsrep_cluster_status'; SHOW STATUS LIKE 'wsrep_connected'; SHOW STATUS LIKE 'wsrep_ready'; @@ -35,8 +39,13 @@ SHOW STATUS LIKE 'wsrep_local_state_comment'; --source include/galera_connect.inc --connection node_3 SET SESSION wsrep_sync_wait=0; +SET SESSION wsrep_on=OFF; +--let $wait_condition = SELECT VARIABLE_VALUE = 'non-Primary' FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_status' +--source include/wait_condition.inc +SET SESSION wsrep_on=ON; SHOW STATUS LIKE 'wsrep_cluster_size'; +SHOW STATUS LIKE 'wsrep_cluster_weight'; SHOW STATUS LIKE 'wsrep_cluster_status'; SHOW STATUS LIKE 'wsrep_connected'; SHOW STATUS LIKE 'wsrep_ready'; @@ -44,15 +53,13 @@ SHOW STATUS LIKE 'wsrep_local_state'; SHOW STATUS LIKE 'wsrep_local_state_comment'; --connection node_1 ---source include/galera_resume.inc ---sleep 10 ---source include/wait_until_connected_again.inc # For Node #1, we expect a primary component of size 1 --let $wait_condition = SELECT VARIABLE_VALUE = 1 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_size' --source include/wait_condition.inc +SELECT VARIABLE_VALUE = 3 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_weight'; SELECT VARIABLE_VALUE = 'Primary' FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_status'; SELECT VARIABLE_VALUE = 'ON' FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_connected'; SELECT VARIABLE_VALUE = 'ON' FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_ready'; @@ -60,30 +67,27 @@ SELECT VARIABLE_VALUE = 4 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_N SELECT VARIABLE_VALUE = 'Synced' FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_local_state_comment'; SET GLOBAL wsrep_provider_options = 'pc.weight=1'; +SELECT VARIABLE_VALUE = 1 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_weight'; -# Restore the cluster by resetting wsrep_cluster_address on nodes #1 and #2 +# Resume cluster connectivity on node_1 +--connection node_1 +SET GLOBAL wsrep_provider_options = 'gmcast.isolate=0'; ---connection node_2 ---disable_query_log ---eval SET GLOBAL wsrep_cluster_address = @@wsrep_cluster_address; ---enable_query_log +--let $wait_condition = SELECT VARIABLE_VALUE = 3 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_size' +--source include/wait_condition.inc -SET SESSION wsrep_sync_wait=0; ---source include/wait_until_connected_again.inc +--connection node_2 +--source include/wait_condition.inc --connection node_3 ---disable_query_log ---eval SET GLOBAL wsrep_cluster_address = @@wsrep_cluster_address; ---enable_query_log - -SET SESSION wsrep_sync_wait=0; ---source include/wait_until_connected_again.inc - -# On all nodes, we now expect a Primary component of size 3, Synced and ready +--source include/wait_condition.inc --connection node_1 ---source include/wait_until_connected_again.inc +--source include/wait_condition.inc + +# On all nodes, we now expect a Primary component of size 3, weight 3, Synced and ready SELECT VARIABLE_VALUE = 3 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_size'; +SELECT VARIABLE_VALUE = 3 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_weight'; SELECT VARIABLE_VALUE = 'Primary' FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_status'; SELECT VARIABLE_VALUE = 'ON' FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_connected'; SELECT VARIABLE_VALUE = 'ON' FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_ready'; @@ -92,6 +96,7 @@ SELECT VARIABLE_VALUE = 'Synced' FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VAR --connection node_2 SELECT VARIABLE_VALUE = 3 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_size'; +SELECT VARIABLE_VALUE = 3 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_weight'; SELECT VARIABLE_VALUE = 'Primary' FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_status'; SELECT VARIABLE_VALUE = 'ON' FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_connected'; SELECT VARIABLE_VALUE = 'ON' FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_ready'; @@ -100,6 +105,7 @@ SELECT VARIABLE_VALUE = 'Synced' FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VAR --connection node_3 SELECT VARIABLE_VALUE = 3 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_size'; +SELECT VARIABLE_VALUE = 3 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_weight'; SELECT VARIABLE_VALUE = 'Primary' FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_status'; SELECT VARIABLE_VALUE = 'ON' FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_connected'; SELECT VARIABLE_VALUE = 'ON' FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_ready'; @@ -116,8 +122,11 @@ CALL mtr.add_suppression('WSREP: gcs_caused\\(\\) returned -1'); --connection node_2 CALL mtr.add_suppression('overriding reported weight for'); +CALL mtr.add_suppression('SYNC message from member'); +CALL mtr.add_suppression('user message in state LEAVING'); +CALL mtr.add_suppression('sending install message failed: (Transport endpoint is not connected|Socket is not connected)'); --connection node_3 CALL mtr.add_suppression('WSREP: user message in state LEAVING'); -CALL mtr.add_suppression('sending install message failed: Transport endpoint is not connected'); +CALL mtr.add_suppression('sending install message failed: (Transport endpoint is not connected|Socket is not connected)'); CALL mtr.add_suppression('overriding reported weight for'); diff --git a/mysql-test/suite/innodb/r/foreign-keys.result b/mysql-test/suite/innodb/r/foreign-keys.result index 0cb53f52a6c..4402c5ca2fe 100644 --- a/mysql-test/suite/innodb/r/foreign-keys.result +++ b/mysql-test/suite/innodb/r/foreign-keys.result @@ -51,3 +51,76 @@ c d 6 30 drop table t2, t1; drop user foo; +create table t1 (f1 int primary key) engine=innodb; +create table t2 (f2 int primary key) engine=innodb; +create table t3 (f3 int primary key, foreign key (f3) references t2(f2)) engine=innodb; +insert into t1 values (1),(2),(3),(4),(5); +insert into t2 values (1),(2),(3),(4),(5); +insert into t3 values (1),(2),(3),(4),(5); +connect con1,localhost,root; +set debug_sync='alter_table_before_rename_result_table signal g1 wait_for g2'; +alter table t2 add constraint foreign key (f2) references t1(f1) on delete cascade on update cascade; +connection default; +set debug_sync='before_execute_sql_command wait_for g1'; +update t1 set f1 = f1 + 100000 limit 2; +connect con2,localhost,root; +kill query UPDATE; +disconnect con2; +connection default; +ERROR 70100: Query execution was interrupted +set debug_sync='now signal g2'; +connection con1; +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `f2` int(11) NOT NULL, + PRIMARY KEY (`f2`), + CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f2`) REFERENCES `t1` (`f1`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +disconnect con1; +connection default; +select * from t2 where f2 not in (select f1 from t1); +f2 +select * from t3 where f3 not in (select f2 from t2); +f3 +drop table t3; +drop table t2; +drop table t1; +set debug_sync='reset'; +create table t1 (a int primary key, b int) engine=innodb; +create table t2 (c int primary key, d int, +foreign key (d) references t1 (a) on update cascade) engine=innodb; +insert t1 values (1,1),(2,2),(3,3); +insert t2 values (4,1),(5,2),(6,3); +flush table t2 with read lock; +connect con1,localhost,root; +delete from t1 where a=2; +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`d`) REFERENCES `t1` (`a`) ON UPDATE CASCADE) +update t1 set a=10 where a=1; +connection default; +unlock tables; +connection con1; +connection default; +lock table t2 write; +connection con1; +delete from t1 where a=2; +connection default; +unlock tables; +connection con1; +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`d`) REFERENCES `t1` (`a`) ON UPDATE CASCADE) +connection default; +unlock tables; +disconnect con1; +create user foo; +grant select,update on test.t1 to foo; +connect foo,localhost,foo; +update t1 set a=30 where a=3; +disconnect foo; +connection default; +select * from t2; +c d +5 2 +4 10 +6 30 +drop table t2, t1; +drop user foo; diff --git a/mysql-test/suite/innodb/r/foreign_key.result b/mysql-test/suite/innodb/r/foreign_key.result index 5838c3a1fd5..a151651b594 100644 --- a/mysql-test/suite/innodb/r/foreign_key.result +++ b/mysql-test/suite/innodb/r/foreign_key.result @@ -317,3 +317,22 @@ engine=innodb; insert into t1 values(1, 1); insert into t2(f1) values(1); drop table t2, t1; +SET FOREIGN_KEY_CHECKS=0; +CREATE TABLE staff ( +staff_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, +store_id TINYINT UNSIGNED NOT NULL, +PRIMARY KEY (staff_id), +KEY idx_fk_store_id (store_id), +CONSTRAINT fk_staff_store FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE RESTRICT ON UPDATE CASCADE +) ENGINE=InnoDB; +CREATE TABLE store ( +store_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, +manager_staff_id TINYINT UNSIGNED NOT NULL, +PRIMARY KEY (store_id), +UNIQUE KEY idx_unique_manager (manager_staff_id), +CONSTRAINT fk_store_staff FOREIGN KEY (manager_staff_id) REFERENCES staff (staff_id) ON DELETE RESTRICT ON UPDATE CASCADE +) ENGINE=InnoDB; +SET FOREIGN_KEY_CHECKS=DEFAULT; +LOCK TABLE staff WRITE; +UNLOCK TABLES; +DROP TABLES staff, store; diff --git a/mysql-test/suite/innodb/r/innodb-lock.result b/mysql-test/suite/innodb/r/innodb-lock.result index e63a7cd1505..1fe0d263fef 100644 --- a/mysql-test/suite/innodb/r/innodb-lock.result +++ b/mysql-test/suite/innodb/r/innodb-lock.result @@ -2,18 +2,15 @@ set global innodb_table_locks=1; select @@innodb_table_locks; @@innodb_table_locks 1 -connect con1,localhost,root,,; -connect con2,localhost,root,,; -drop table if exists t1; set @@innodb_table_locks=1; -connection con1; +connect con1,localhost,root,,; create table t1 (id integer, x integer) engine=INNODB; insert into t1 values(0, 0); set autocommit=0; SELECT * from t1 where id = 0 FOR UPDATE; id x 0 0 -connection con2; +connect con2,localhost,root,,; set autocommit=0; lock table t1 write; connection con1; @@ -116,13 +113,49 @@ Warnings: Warning 1062 Duplicate entry '3' for key 'PRIMARY' SELECT * FROM t1 FOR UPDATE; connection con2; +disconnect con2; connection default; COMMIT; connection con1; a b 3 1 COMMIT; -disconnect con1; -disconnect con2; connection default; DROP TABLE t1; +# +# MDEV-11080 InnoDB: Failing assertion: +# table->n_waiting_or_granted_auto_inc_locks > 0 +# +CREATE TABLE t1 (pk INTEGER AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES (NULL),(NULL); +CREATE TABLE t2 LIKE t1; +BEGIN; +connection con1; +BEGIN; +DELETE FROM t2; +connection default; +LOCK TABLE t2 READ;; +connection con1; +SET innodb_lock_wait_timeout= 1, lock_wait_timeout= 2; +INSERT INTO t2 SELECT * FROM t1; +COMMIT; +connection default; +UNLOCK TABLES; +DROP TABLE t1, t2; +# +# MDEV-16709 InnoDB: Error: trx already had an AUTO-INC lock +# +CREATE TABLE t1 (pk INT AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB +PARTITION BY key (pk) PARTITIONS 2; +CREATE TABLE t2 (a INT) ENGINE=InnoDB; +INSERT INTO t2 VALUES (1),(2),(3),(4),(5),(6); +CREATE TABLE t3 (b INT) ENGINE=InnoDB; +INSERT INTO t3 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9); +connection con1; +INSERT t1 SELECT NULL FROM t2; +connection default; +INSERT t1 SELECT NULL FROM t3; +connection con1; +disconnect con1; +connection default; +DROP TABLE t1, t2, t3; diff --git a/mysql-test/suite/innodb/t/foreign-keys.test b/mysql-test/suite/innodb/t/foreign-keys.test index e77e1e761c7..35da5a5d075 100644 --- a/mysql-test/suite/innodb/t/foreign-keys.test +++ b/mysql-test/suite/innodb/t/foreign-keys.test @@ -1,5 +1,8 @@ --source include/have_innodb.inc --source include/have_debug.inc +--source include/have_debug_sync.inc + +--enable_connect_log --echo # --echo # Bug #19471516 SERVER CRASHES WHEN EXECUTING ALTER TABLE @@ -70,3 +73,87 @@ connection default; select * from t2; drop table t2, t1; drop user foo; + +# +# MDEV-16465 Invalid (old?) table or database name or hang in ha_innobase::delete_table and log semaphore wait upon concurrent DDL with foreign keys +# +create table t1 (f1 int primary key) engine=innodb; +create table t2 (f2 int primary key) engine=innodb; +create table t3 (f3 int primary key, foreign key (f3) references t2(f2)) engine=innodb; +insert into t1 values (1),(2),(3),(4),(5); +insert into t2 values (1),(2),(3),(4),(5); +insert into t3 values (1),(2),(3),(4),(5); +connect con1,localhost,root; +set debug_sync='alter_table_before_rename_result_table signal g1 wait_for g2'; +send alter table t2 add constraint foreign key (f2) references t1(f1) on delete cascade on update cascade; +connection default; +let $conn=`select connection_id()`; +set debug_sync='before_execute_sql_command wait_for g1'; +send update t1 set f1 = f1 + 100000 limit 2; +connect con2,localhost,root; +let $wait_condition= select 1 from information_schema.processlist where state='Waiting for table metadata lock' and info like 'update t1 %'; +source include/wait_condition.inc; +--replace_result $conn UPDATE +eval kill query $conn; +disconnect con2; +connection default; +error ER_QUERY_INTERRUPTED; +reap; +set debug_sync='now signal g2'; +connection con1; +reap; +show create table t2; +disconnect con1; +connection default; +select * from t2 where f2 not in (select f1 from t1); +select * from t3 where f3 not in (select f2 from t2); +drop table t3; +drop table t2; +drop table t1; +set debug_sync='reset'; + +# +# FK and prelocking: +# child table accesses (reads and writes) wait for locks. +# +create table t1 (a int primary key, b int) engine=innodb; +create table t2 (c int primary key, d int, + foreign key (d) references t1 (a) on update cascade) engine=innodb; +insert t1 values (1,1),(2,2),(3,3); +insert t2 values (4,1),(5,2),(6,3); +flush table t2 with read lock; # this takes MDL_SHARED_NO_WRITE +connect (con1,localhost,root); +--error ER_ROW_IS_REFERENCED_2 +delete from t1 where a=2; +send update t1 set a=10 where a=1; +connection default; +let $wait_condition= select 1 from information_schema.processlist where state='Waiting for table metadata lock'; +source include/wait_condition.inc; +unlock tables; +connection con1; +reap; +connection default; +lock table t2 write; # this takes MDL_SHARED_NO_READ_WRITE +connection con1; +send delete from t1 where a=2; +connection default; +let $wait_condition= select 1 from information_schema.processlist where state='Waiting for table metadata lock'; +source include/wait_condition.inc; +unlock tables; +connection con1; +--error ER_ROW_IS_REFERENCED_2 +reap; +connection default; +unlock tables; +disconnect con1; + +# but privileges should not be checked +create user foo; +grant select,update on test.t1 to foo; +connect(foo,localhost,foo); +update t1 set a=30 where a=3; +disconnect foo; +connection default; +select * from t2; +drop table t2, t1; +drop user foo; diff --git a/mysql-test/suite/innodb/t/foreign_key.test b/mysql-test/suite/innodb/t/foreign_key.test index b586f3e9406..7a8a9295ee7 100644 --- a/mysql-test/suite/innodb/t/foreign_key.test +++ b/mysql-test/suite/innodb/t/foreign_key.test @@ -289,3 +289,27 @@ insert into t2(f1) values(1); drop table t2, t1; --source include/wait_until_count_sessions.inc + +# +# MDEV-12669 Circular foreign keys cause a loop and OOM upon LOCK TABLE +# +SET FOREIGN_KEY_CHECKS=0; +CREATE TABLE staff ( + staff_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, + store_id TINYINT UNSIGNED NOT NULL, + PRIMARY KEY (staff_id), + KEY idx_fk_store_id (store_id), + CONSTRAINT fk_staff_store FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE RESTRICT ON UPDATE CASCADE +) ENGINE=InnoDB; +CREATE TABLE store ( + store_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, + manager_staff_id TINYINT UNSIGNED NOT NULL, + PRIMARY KEY (store_id), + UNIQUE KEY idx_unique_manager (manager_staff_id), + CONSTRAINT fk_store_staff FOREIGN KEY (manager_staff_id) REFERENCES staff (staff_id) ON DELETE RESTRICT ON UPDATE CASCADE +) ENGINE=InnoDB; +SET FOREIGN_KEY_CHECKS=DEFAULT; + +LOCK TABLE staff WRITE; +UNLOCK TABLES; +DROP TABLES staff, store; diff --git a/mysql-test/suite/innodb/t/innodb-lock.test b/mysql-test/suite/innodb/t/innodb-lock.test index 3e9b3dd7fe4..9e5505270be 100644 --- a/mysql-test/suite/innodb/t/innodb-lock.test +++ b/mysql-test/suite/innodb/t/innodb-lock.test @@ -1,4 +1,5 @@ --source include/have_innodb.inc +--source include/have_partition.inc # # Check and select innodb lock type @@ -12,26 +13,15 @@ select @@innodb_table_locks; # Testing of explicit table locks with enforced table locks # -connect (con1,localhost,root,,); -connect (con2,localhost,root,,); - ---disable_warnings -drop table if exists t1; ---enable_warnings - -# -# Testing of explicit table locks with enforced table locks -# - set @@innodb_table_locks=1; -connection con1; +connect (con1,localhost,root,,); create table t1 (id integer, x integer) engine=INNODB; insert into t1 values(0, 0); set autocommit=0; SELECT * from t1 where id = 0 FOR UPDATE; -connection con2; +connect (con2,localhost,root,,); set autocommit=0; # The following statement should hang because con1 is locking the page @@ -166,14 +156,66 @@ let $wait_condition= where state = 'Sending data' and info = 'SELECT * FROM t1 FOR UPDATE'; --source include/wait_condition.inc +disconnect con2; connection default; COMMIT; connection con1; reap; COMMIT; -disconnect con1; -disconnect con2; - connection default; DROP TABLE t1; + +--echo # +--echo # MDEV-11080 InnoDB: Failing assertion: +--echo # table->n_waiting_or_granted_auto_inc_locks > 0 +--echo # + +CREATE TABLE t1 (pk INTEGER AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES (NULL),(NULL); +CREATE TABLE t2 LIKE t1; + +BEGIN; + +connection con1; +BEGIN; +DELETE FROM t2; +connection default; +--send LOCK TABLE t2 READ; +connection con1; +SET innodb_lock_wait_timeout= 1, lock_wait_timeout= 2; +--error 0,ER_LOCK_WAIT_TIMEOUT +INSERT INTO t2 SELECT * FROM t1; +COMMIT; + +connection default; +reap; +UNLOCK TABLES; +DROP TABLE t1, t2; + +--echo # +--echo # MDEV-16709 InnoDB: Error: trx already had an AUTO-INC lock +--echo # + +CREATE TABLE t1 (pk INT AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB +PARTITION BY key (pk) PARTITIONS 2; + +CREATE TABLE t2 (a INT) ENGINE=InnoDB; +INSERT INTO t2 VALUES (1),(2),(3),(4),(5),(6); + +CREATE TABLE t3 (b INT) ENGINE=InnoDB; +INSERT INTO t3 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9); + +connection con1; +send INSERT t1 SELECT NULL FROM t2; + +connection default; +--error 0,ER_DUP_ENTRY,ER_LOCK_DEADLOCK +INSERT t1 SELECT NULL FROM t3; + +connection con1; +--error 0,ER_DUP_ENTRY,ER_LOCK_DEADLOCK +reap; +disconnect con1; +connection default; +DROP TABLE t1, t2, t3; diff --git a/mysql-test/suite/maria/create.result b/mysql-test/suite/maria/create.result new file mode 100644 index 00000000000..82c6b8c9871 --- /dev/null +++ b/mysql-test/suite/maria/create.result @@ -0,0 +1,33 @@ +CREATE OR REPLACE TABLE t1 ( +f1 DECIMAL(43,0) NOT NULL, +f2 TIME(4) NULL, +f3 BINARY(101) NULL, +f4 TIMESTAMP(4) NULL, +f5 DATETIME(1) NULL, +f6 SET('a','b','c') NOT NULL DEFAULT 'a', +f7 VARBINARY(2332) NOT NULL DEFAULT '', +f8 DATE NULL, +f9 BLOB NULL, +f10 MEDIUMINT(45) NOT NULL DEFAULT 0, +f11 YEAR NULL, +f12 BIT(58) NULL, +v2 TIME(1) AS (f2) VIRTUAL, +v3 BINARY(115) AS (f3) VIRTUAL, +v4 TIMESTAMP(3) AS (f4) VIRTUAL, +v7 VARBINARY(658) AS (f7) PERSISTENT, +v8 DATE AS (f8) PERSISTENT, +v9 TINYTEXT AS (f9) PERSISTENT, +v11 YEAR AS (f11) VIRTUAL +) ENGINE=Aria; +INSERT IGNORE INTO t1 (f1,f2,f3,f4,f5,f6,f7,f8,f9,f10,f11,f12) VALUES +(0.8,'16:01:46',NULL,'2006-03-01 12:44:34','2029-10-10 21:27:53','a','foo','1989-12-24','bar',9,1975,b'1'); +Warnings: +Note 1265 Data truncated for column 'f1' at row 1 +DROP TABLE t1; +CREATE OR REPLACE TABLE t1 (a INT(45)); +INSERT IGNORE INTO t1 VALUES (1),(2); +CREATE OR REPLACE TABLE t2 ENGINE=Aria AS SELECT SUM(a) AS f1, IFNULL( 'qux', ExtractValue( 'foo', 'bar' ) ) AS f2 FROM t1; +select * from t2; +f1 f2 +3 qux +DROP TABLE t1, t2; diff --git a/mysql-test/suite/maria/create.test b/mysql-test/suite/maria/create.test new file mode 100644 index 00000000000..8f2ffd7492f --- /dev/null +++ b/mysql-test/suite/maria/create.test @@ -0,0 +1,42 @@ +--source include/have_maria.inc + +# MDEV-17021 +# Server crash or assertion `length <= column->length' failure in +# write_block_record +# + +CREATE OR REPLACE TABLE t1 ( + f1 DECIMAL(43,0) NOT NULL, + f2 TIME(4) NULL, + f3 BINARY(101) NULL, + f4 TIMESTAMP(4) NULL, + f5 DATETIME(1) NULL, + f6 SET('a','b','c') NOT NULL DEFAULT 'a', + f7 VARBINARY(2332) NOT NULL DEFAULT '', + f8 DATE NULL, + f9 BLOB NULL, + f10 MEDIUMINT(45) NOT NULL DEFAULT 0, + f11 YEAR NULL, + f12 BIT(58) NULL, + v2 TIME(1) AS (f2) VIRTUAL, + v3 BINARY(115) AS (f3) VIRTUAL, + v4 TIMESTAMP(3) AS (f4) VIRTUAL, + v7 VARBINARY(658) AS (f7) PERSISTENT, + v8 DATE AS (f8) PERSISTENT, + v9 TINYTEXT AS (f9) PERSISTENT, + v11 YEAR AS (f11) VIRTUAL +) ENGINE=Aria; +INSERT IGNORE INTO t1 (f1,f2,f3,f4,f5,f6,f7,f8,f9,f10,f11,f12) VALUES + (0.8,'16:01:46',NULL,'2006-03-01 12:44:34','2029-10-10 21:27:53','a','foo','1989-12-24','bar',9,1975,b'1'); +DROP TABLE t1; + +# +# MDEV-17067 Server crash in write_block_record +# + +CREATE OR REPLACE TABLE t1 (a INT(45)); +INSERT IGNORE INTO t1 VALUES (1),(2); + +CREATE OR REPLACE TABLE t2 ENGINE=Aria AS SELECT SUM(a) AS f1, IFNULL( 'qux', ExtractValue( 'foo', 'bar' ) ) AS f2 FROM t1; +select * from t2; +DROP TABLE t1, t2; diff --git a/mysql-test/suite/maria/maria.result b/mysql-test/suite/maria/maria.result index f49b62638d1..fcfecd31eb9 100644 --- a/mysql-test/suite/maria/maria.result +++ b/mysql-test/suite/maria/maria.result @@ -2791,6 +2791,10 @@ id name -1 dog 2 cat DROP TABLE t1; +CREATE TABLE t1 (pk int, i2 int) ENGINE=Aria; +INSERT INTO t1 VALUES (1,2), (2,3),(3,4); +DELETE FROM tt.*, t1.* USING t1 AS tt LEFT JOIN t1 ON (tt.i2 = t1.pk); +DROP TABLE t1; # # End of 5.5 tests # diff --git a/mysql-test/suite/maria/maria.test b/mysql-test/suite/maria/maria.test index d7710047f48..19aab4aa944 100644 --- a/mysql-test/suite/maria/maria.test +++ b/mysql-test/suite/maria/maria.test @@ -2008,6 +2008,16 @@ INSERT INTO t1 (name) VALUES ('cat'); SELECT * FROM t1; DROP TABLE t1; +# +# MDEV-16682 +# Assertion `(buff[7] & 7) == HEAD_PAGE' failed. +# + +CREATE TABLE t1 (pk int, i2 int) ENGINE=Aria; +INSERT INTO t1 VALUES (1,2), (2,3),(3,4); +DELETE FROM tt.*, t1.* USING t1 AS tt LEFT JOIN t1 ON (tt.i2 = t1.pk); +DROP TABLE t1; + --echo # --echo # End of 5.5 tests --echo # diff --git a/mysql-test/suite/mariabackup/absolute_ibdata_paths.opt b/mysql-test/suite/mariabackup/absolute_ibdata_paths.opt index 52b6b743ac8..71996e09b18 100644 --- a/mysql-test/suite/mariabackup/absolute_ibdata_paths.opt +++ b/mysql-test/suite/mariabackup/absolute_ibdata_paths.opt @@ -1 +1 @@ ---innodb --innodb-data-home-dir= --innodb-data-file-path=$MYSQLTEST_VARDIR/tmp/absolute_path_ibdata1:3M;ibdata_second:1M:autoextend
\ No newline at end of file +--innodb --innodb-data-home-dir= --innodb-data-file-path=$MYSQLTEST_VARDIR/tmp/absolute_path_ibdata1:3M;ibdata_second:1M:autoextend --innodb-undo-tablespaces=2
\ No newline at end of file diff --git a/mysql-test/suite/mariabackup/nolock_ddl_during_backup_end.result b/mysql-test/suite/mariabackup/nolock_ddl_during_backup_end.result new file mode 100644 index 00000000000..1bd3052b708 --- /dev/null +++ b/mysql-test/suite/mariabackup/nolock_ddl_during_backup_end.result @@ -0,0 +1,2 @@ +CREATE TABLE t1(i int) ENGINE=INNODB; +# xtrabackup backup diff --git a/mysql-test/suite/mariabackup/nolock_ddl_during_backup_end.test b/mysql-test/suite/mariabackup/nolock_ddl_during_backup_end.test new file mode 100644 index 00000000000..f6bc51bd9a6 --- /dev/null +++ b/mysql-test/suite/mariabackup/nolock_ddl_during_backup_end.test @@ -0,0 +1,14 @@ +--source include/have_debug.inc +let $targetdir=$MYSQLTEST_VARDIR/tmp/backup; +mkdir $targetdir; + +CREATE TABLE t1(i int) ENGINE=INNODB; + +# this will table and populate it, after backup has list of tables to be copied +--let backup_fix_ddl=BEGIN NOT ATOMIC DROP TABLE test.t1;DO SLEEP(10000); END +echo # xtrabackup backup; +--disable_result_log +error 1; +exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --backup --target-dir=$targetdir --no-lock --dbug=+d,mariabackup_events; +--enable_result_log +rmdir $targetdir; diff --git a/mysql-test/suite/mariabackup/undo_space_id.opt b/mysql-test/suite/mariabackup/undo_space_id.opt index 16135129d5b..006e17a2535 100644 --- a/mysql-test/suite/mariabackup/undo_space_id.opt +++ b/mysql-test/suite/mariabackup/undo_space_id.opt @@ -1,2 +1,3 @@ --debug=d,innodb_undo_upgrade,force_rebootstrap +--innodb_data_file_path=ib_mysql:10M --innodb_undo_tablespaces=2 diff --git a/mysql-test/suite/parts/r/show_create.result b/mysql-test/suite/parts/r/show_create.result index 79ac61d180d..c7b0ecdb6d9 100644 --- a/mysql-test/suite/parts/r/show_create.result +++ b/mysql-test/suite/parts/r/show_create.result @@ -103,3 +103,11 @@ t_partition CREATE TABLE `t_partition` ( PARTITION BY HASH (`f1`) PARTITIONS 2 drop table t_partition; +create table t1 (a int) partition by range(a) (partition p0 values less than (5)); +set sql_mode='ansi_quotes'; +alter table t1 add partition (partition p1 values less than (10)); +set sql_mode= default; +show table status; +Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary +t1 MyISAM 10 Fixed 0 0 0 0 2048 0 NULL X X NULL latin1_swedish_ci NULL partitioned 0 N +drop table t1; diff --git a/mysql-test/suite/parts/r/update_and_cache.result b/mysql-test/suite/parts/r/update_and_cache.result new file mode 100644 index 00000000000..52f13e66702 --- /dev/null +++ b/mysql-test/suite/parts/r/update_and_cache.result @@ -0,0 +1,7 @@ +CREATE TABLE t1 (pk INT PRIMARY KEY, a INT); +INSERT INTO t1 VALUES (1,10),(2,20); +CREATE TABLE t2 (b INT) PARTITION BY KEY (b) PARTITIONS 2; +INSERT INTO t2 VALUES (1),(2); +DELETE t2 FROM t2 WHERE b BETWEEN 5 AND 9; +UPDATE t2 JOIN t1 SET b = 5; +DROP TABLE t1, t2; diff --git a/mysql-test/suite/parts/t/show_create.test b/mysql-test/suite/parts/t/show_create.test index 9c43b163790..3f62ee11757 100644 --- a/mysql-test/suite/parts/t/show_create.test +++ b/mysql-test/suite/parts/t/show_create.test @@ -39,3 +39,14 @@ create table t_partition (f1 int) partition by hash(f1) partitions 2; select * from t_partition as tbl; show create table t_partition; drop table t_partition; + +# +# MDEV-16792 Assertion `m_status == DA_ERROR' failed in Diagnostics_area::sql_errno or wrong result upon SHOW TABLE STATUS after adding partition under ANSI_QUOTES +# +create table t1 (a int) partition by range(a) (partition p0 values less than (5)); +set sql_mode='ansi_quotes'; +alter table t1 add partition (partition p1 values less than (10)); +set sql_mode= default; +--replace_column 12 X 13 X +show table status; +drop table t1; diff --git a/mysql-test/suite/parts/t/update_and_cache.test b/mysql-test/suite/parts/t/update_and_cache.test new file mode 100644 index 00000000000..08ade807422 --- /dev/null +++ b/mysql-test/suite/parts/t/update_and_cache.test @@ -0,0 +1,12 @@ +--source include/have_partition.inc + +CREATE TABLE t1 (pk INT PRIMARY KEY, a INT); +INSERT INTO t1 VALUES (1,10),(2,20); + +CREATE TABLE t2 (b INT) PARTITION BY KEY (b) PARTITIONS 2; +INSERT INTO t2 VALUES (1),(2); + +DELETE t2 FROM t2 WHERE b BETWEEN 5 AND 9; +UPDATE t2 JOIN t1 SET b = 5; + +DROP TABLE t1, t2; diff --git a/mysql-test/suite/rpl/include/rpl_foreign_key.test b/mysql-test/suite/rpl/include/rpl_foreign_key.test deleted file mode 100644 index d10deece1b1..00000000000 --- a/mysql-test/suite/rpl/include/rpl_foreign_key.test +++ /dev/null @@ -1,60 +0,0 @@ -# Check the replication of the FOREIGN_KEY_CHECKS variable. - --- source include/master-slave.inc - -eval CREATE TABLE t1 (a INT AUTO_INCREMENT KEY) ENGINE=$engine_type; -eval CREATE TABLE t2 (b INT AUTO_INCREMENT KEY, c INT, FOREIGN KEY(b) REFERENCES t1(a)) ENGINE=$engine_type; - -SET FOREIGN_KEY_CHECKS=0; -INSERT INTO t1 VALUES (10); -INSERT INTO t1 VALUES (NULL),(NULL),(NULL); -INSERT INTO t2 VALUES (5,0); -INSERT INTO t2 VALUES (NULL,LAST_INSERT_ID()); -SET FOREIGN_KEY_CHECKS=1; -SELECT * FROM t1 ORDER BY a; -SELECT * FROM t2 ORDER BY b; -sync_slave_with_master; -SELECT * FROM t1 ORDER BY a; -SELECT * FROM t2 ORDER BY b; - -connection master; -SET TIMESTAMP=1000000000; -CREATE TABLE t3 ( a INT UNIQUE ); -SET FOREIGN_KEY_CHECKS=0; ---error ER_DUP_ENTRY -INSERT INTO t3 VALUES (1),(1); -sync_slave_with_master; - -connection master; -SET FOREIGN_KEY_CHECKS=0; -DROP TABLE IF EXISTS t1,t2,t3; -SET FOREIGN_KEY_CHECKS=1; -sync_slave_with_master; - -# -# Bug #32468 delete rows event on a table with foreign key constraint fails -# - -connection master; - -eval create table t1 (b int primary key) engine = $engine_type; -eval create table t2 (a int primary key, b int, foreign key (b) references t1(b)) - engine = $engine_type; - -insert into t1 set b=1; -insert into t2 set a=1, b=1; - -set foreign_key_checks=0; -delete from t1; - ---echo must sync w/o a problem (could not with the buggy code) -sync_slave_with_master; -select count(*) from t1 /* must be zero */; - - -# cleanup for bug#32468 - -connection master; -drop table t2,t1; - ---source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_foreign_key_innodb.result b/mysql-test/suite/rpl/r/rpl_foreign_key_innodb.result index 120ae018d2f..7f2a82f1efb 100644 --- a/mysql-test/suite/rpl/r/rpl_foreign_key_innodb.result +++ b/mysql-test/suite/rpl/r/rpl_foreign_key_innodb.result @@ -43,8 +43,7 @@ SET FOREIGN_KEY_CHECKS=1; connection slave; connection master; create table t1 (b int primary key) engine = INNODB; -create table t2 (a int primary key, b int, foreign key (b) references t1(b)) -engine = INNODB; +create table t2 (a int primary key, b int, foreign key (b) references t1(b)) engine = INNODB; insert into t1 set b=1; insert into t2 set a=1, b=1; set foreign_key_checks=0; diff --git a/mysql-test/suite/rpl/t/rpl_foreign_key_innodb.test b/mysql-test/suite/rpl/t/rpl_foreign_key_innodb.test index 489e90f9eb5..53db1723325 100644 --- a/mysql-test/suite/rpl/t/rpl_foreign_key_innodb.test +++ b/mysql-test/suite/rpl/t/rpl_foreign_key_innodb.test @@ -1,3 +1,61 @@ -- source include/have_innodb.inc -let $engine_type=INNODB; --- source include/rpl_foreign_key.test + +# Check the replication of the FOREIGN_KEY_CHECKS variable. + +-- source include/master-slave.inc + +CREATE TABLE t1 (a INT AUTO_INCREMENT KEY) ENGINE=INNODB; +CREATE TABLE t2 (b INT AUTO_INCREMENT KEY, c INT, FOREIGN KEY(b) REFERENCES t1(a)) ENGINE=INNODB; + +SET FOREIGN_KEY_CHECKS=0; +INSERT INTO t1 VALUES (10); +INSERT INTO t1 VALUES (NULL),(NULL),(NULL); +INSERT INTO t2 VALUES (5,0); +INSERT INTO t2 VALUES (NULL,LAST_INSERT_ID()); +SET FOREIGN_KEY_CHECKS=1; +SELECT * FROM t1 ORDER BY a; +SELECT * FROM t2 ORDER BY b; +sync_slave_with_master; +SELECT * FROM t1 ORDER BY a; +SELECT * FROM t2 ORDER BY b; + +connection master; +SET TIMESTAMP=1000000000; +CREATE TABLE t3 ( a INT UNIQUE ); +SET FOREIGN_KEY_CHECKS=0; +--error ER_DUP_ENTRY +INSERT INTO t3 VALUES (1),(1); +sync_slave_with_master; + +connection master; +SET FOREIGN_KEY_CHECKS=0; +DROP TABLE IF EXISTS t1,t2,t3; +SET FOREIGN_KEY_CHECKS=1; +sync_slave_with_master; + +# +# Bug #32468 delete rows event on a table with foreign key constraint fails +# + +connection master; + +create table t1 (b int primary key) engine = INNODB; +create table t2 (a int primary key, b int, foreign key (b) references t1(b)) engine = INNODB; + +insert into t1 set b=1; +insert into t2 set a=1, b=1; + +set foreign_key_checks=0; +delete from t1; + +--echo must sync w/o a problem (could not with the buggy code) +sync_slave_with_master; +select count(*) from t1 /* must be zero */; + + +# cleanup for bug#32468 + +connection master; +drop table t2,t1; + +--source include/rpl_end.inc diff --git a/mysql-test/suite/sys_vars/r/wsrep_start_position_basic.result b/mysql-test/suite/sys_vars/r/wsrep_start_position_basic.result index fd6dc1b5a4d..a49e6135d47 100644 --- a/mysql-test/suite/sys_vars/r/wsrep_start_position_basic.result +++ b/mysql-test/suite/sys_vars/r/wsrep_start_position_basic.result @@ -17,6 +17,10 @@ SELECT @@global.wsrep_start_position; 00000000-0000-0000-0000-000000000000:-1 # valid values +SET @@global.wsrep_start_position='00000000-0000-0000-0000-000000000000:-2'; +SELECT @@global.wsrep_start_position; +@@global.wsrep_start_position +00000000-0000-0000-0000-000000000000:-2 SET @@global.wsrep_start_position='12345678-1234-1234-1234-123456789012:100'; SELECT @@global.wsrep_start_position; @@global.wsrep_start_position @@ -27,12 +31,6 @@ SELECT @@global.wsrep_start_position; 00000000-0000-0000-0000-000000000000:-1 # invalid values -call mtr.add_suppression("WSREP: SST position can't be set in past. Requested: -2, Current: -1."); -SET @@global.wsrep_start_position='00000000-0000-0000-0000-000000000000:-2'; -ERROR 42000: Variable 'wsrep_start_position' can't be set to the value of '00000000-0000-0000-0000-000000000000:-2' -SELECT @@global.wsrep_start_position; -@@global.wsrep_start_position -00000000-0000-0000-0000-000000000000:-1 SET @@global.wsrep_start_position='000000000000000-0000-0000-0000-000000000000:-1'; ERROR 42000: Variable 'wsrep_start_position' can't be set to the value of '000000000000000-0000-0000-0000-000000000000:-1' SET @@global.wsrep_start_position='12345678-1234-1234-12345-123456789012:100'; diff --git a/mysql-test/suite/sys_vars/t/wsrep_start_position_basic.test b/mysql-test/suite/sys_vars/t/wsrep_start_position_basic.test index 7f8eb138727..3e57cfa6da2 100644 --- a/mysql-test/suite/sys_vars/t/wsrep_start_position_basic.test +++ b/mysql-test/suite/sys_vars/t/wsrep_start_position_basic.test @@ -19,6 +19,8 @@ SELECT @@global.wsrep_start_position; --echo --echo # valid values +SET @@global.wsrep_start_position='00000000-0000-0000-0000-000000000000:-2'; +SELECT @@global.wsrep_start_position; SET @@global.wsrep_start_position='12345678-1234-1234-1234-123456789012:100'; SELECT @@global.wsrep_start_position; SET @@global.wsrep_start_position=default; @@ -26,10 +28,6 @@ SELECT @@global.wsrep_start_position; --echo --echo # invalid values -call mtr.add_suppression("WSREP: SST position can't be set in past. Requested: -2, Current: -1."); ---error ER_WRONG_VALUE_FOR_VAR -SET @@global.wsrep_start_position='00000000-0000-0000-0000-000000000000:-2'; -SELECT @@global.wsrep_start_position; --error ER_WRONG_VALUE_FOR_VAR SET @@global.wsrep_start_position='000000000000000-0000-0000-0000-000000000000:-1'; --error ER_WRONG_VALUE_FOR_VAR diff --git a/mysql-test/suite/wsrep/include/check_galera_version.inc b/mysql-test/suite/wsrep/include/check_galera_version.inc index e495da8f1ee..32d01197f94 100644 --- a/mysql-test/suite/wsrep/include/check_galera_version.inc +++ b/mysql-test/suite/wsrep/include/check_galera_version.inc @@ -17,23 +17,27 @@ eval SET @GALERA_VERSION='$galera_version'; SELECT CAST(REGEXP_REPLACE(@GALERA_VERSION,'^(\\d+)\\.(\\d+).*','\\1') AS UNSIGNED) INTO @GALERA_MAJOR_VERSION; SELECT CAST(REGEXP_REPLACE(@GALERA_VERSION,'^(\\d+)\\.(\\d+).*','\\2') AS UNSIGNED) INTO @GALERA_MINOR_VERSION; +SELECT CAST(REGEXP_REPLACE(@GALERA_VERSION,'^(\\d+)\\.(\\d+)\\.(\\d+).*','\\3') AS UNSIGNED) INTO @GALERA_RELEASE_VERSION; # Actual SELECT VARIABLE_VALUE INTO @ACTUAL_GALERA_VERSION FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE 'wsrep_provider_version'; - -SELECT CAST(REGEXP_REPLACE(@ACTUAL_GALERA_VERSION,'^(\\d+)\\.(\\d+).*','\\1') AS UNSIGNED) INTO @ACTUAL_GALERA_MAJOR_VERSION; -SELECT CAST(REGEXP_REPLACE(@ACTUAL_GALERA_VERSION,'^(\\d+)\\.(\\d+).*','\\2') AS UNSIGNED) INTO @ACTUAL_GALERA_MINOR_VERSION; +SELECT CAST(REGEXP_REPLACE(@ACTUAL_GALERA_VERSION,'^[\\d\\.]*(\\d+)\\.\\d+.*','\\1') AS UNSIGNED) INTO @ACTUAL_GALERA_MINOR_VERSION; +SELECT CAST(REGEXP_REPLACE(@ACTUAL_GALERA_VERSION,'^[\\d\\.]*\\.(\\d+).*','\\1') AS UNSIGNED) INTO @ACTUAL_GALERA_RELEASE_VERSION; # For testing -#SELECT @GALERA_MAJOR_VERSION, @GALERA_MINOR_VERSION; +#SELECT @GALERA_MAJOR_VERSION; +#SELECT @GALERA_MINOR_VERSION; +#SELECT @GALERA_RELEASE_VERSION; #SELECT @ACTUAL_GALERA_VERSION; -#SELECT @ACTUAL_GALERA_MAJOR_VERSION, @ACTUAL_GALERA_MINOR_VERSION; +#SELECT @ACTUAL_GALERA_MINOR_VERSION; +#SELECT @ACTUAL_GALERA_RELEASE_VERSION; -if (!`SELECT (@ACTUAL_GALERA_MAJOR_VERSION > @GALERA_MAJOR_VERSION) OR - (@ACTUAL_GALERA_MAJOR_VERSION = @GALERA_MAJOR_VERSION AND @ACTUAL_GALERA_MINOR_VERSION >= @GALERA_MINOR_VERSION) +if (!`SELECT (@ACTUAL_GALERA_MINOR_VERSION > @GALERA_MINOR_VERSION) OR + (@ACTUAL_GALERA_MINOR_VERSION = @GALERA_MINOR_VERSION AND + @ACTUAL_GALERA_RELEASE_VERSION >= @GALERA_RELEASE_VERSION) `) { - skip Test requires Galera library version $galera_version; + skip Test requires Galera library version >= $galera_version; } --enable_query_log diff --git a/mysql-test/suite/wsrep/r/variables.result b/mysql-test/suite/wsrep/r/variables.result index b6f22828532..a5f95971367 100644 --- a/mysql-test/suite/wsrep/r/variables.result +++ b/mysql-test/suite/wsrep/r/variables.result @@ -19,6 +19,7 @@ SET GLOBAL wsrep_provider=none; # variables when using "_" # CALL mtr.add_suppression("WSREP: Could not open saved state file for reading.*"); +# wsrep SHOW GLOBAL STATUS LIKE 'wsrep%'; Variable_name Value wsrep_apply_oooe # @@ -59,61 +60,8 @@ wsrep_local_send_queue_min # wsrep_local_state # wsrep_local_state_comment # wsrep_local_state_uuid # -wsrep_protocol_version # -wsrep_provider_name # -wsrep_provider_vendor # -wsrep_provider_version # -wsrep_ready # -wsrep_received # -wsrep_received_bytes # -wsrep_repl_data_bytes # -wsrep_repl_keys # -wsrep_repl_keys_bytes # -wsrep_repl_other_bytes # -wsrep_replicated # -wsrep_replicated_bytes # -wsrep_thread_count # - -SHOW GLOBAL STATUS LIKE 'wsrep_%'; -Variable_name Value -wsrep_apply_oooe # -wsrep_apply_oool # -wsrep_apply_window # -wsrep_causal_reads # -wsrep_cert_deps_distance # -wsrep_cert_index_size # -wsrep_cert_interval # -wsrep_cluster_conf_id # -wsrep_cluster_size # -wsrep_cluster_state_uuid # -wsrep_cluster_status # -wsrep_commit_oooe # -wsrep_commit_oool # -wsrep_commit_window # -wsrep_connected # -wsrep_flow_control_paused # -wsrep_flow_control_paused_ns # -wsrep_flow_control_recv # -wsrep_flow_control_sent # -wsrep_incoming_addresses # -wsrep_last_committed # -wsrep_local_bf_aborts # -wsrep_local_cached_downto # -wsrep_local_cert_failures # -wsrep_local_commits # -wsrep_local_index # -wsrep_local_recv_queue # -wsrep_local_recv_queue_avg # -wsrep_local_recv_queue_max # -wsrep_local_recv_queue_min # -wsrep_local_replays # -wsrep_local_send_queue # -wsrep_local_send_queue_avg # -wsrep_local_send_queue_max # -wsrep_local_send_queue_min # -wsrep_local_state # -wsrep_local_state_comment # -wsrep_local_state_uuid # +wsrep_open_connections # +wsrep_open_transactions # wsrep_protocol_version # wsrep_provider_name # wsrep_provider_vendor # |