diff options
Diffstat (limited to 'mysql-test')
50 files changed, 1820 insertions, 118 deletions
diff --git a/mysql-test/main/contributors.result b/mysql-test/main/contributors.result index 0c7ca03a2c5..8d72373696c 100644 --- a/mysql-test/main/contributors.result +++ b/mysql-test/main/contributors.result @@ -5,6 +5,8 @@ Tencent Cloud https://cloud.tencent.com Platinum Sponsor of the MariaDB Foundati Microsoft https://microsoft.com/ Platinum Sponsor of the MariaDB Foundation MariaDB Corporation https://mariadb.com Founding member, Platinum Sponsor of the MariaDB Foundation ServiceNow https://servicenow.com Platinum Sponsor of the MariaDB Foundation +Intel https://www.intel.com Platinum Sponsor of the MariaDB Foundation +SIT https://sit.org Platinum Sponsor of the MariaDB Foundation 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 diff --git a/mysql-test/main/create_or_replace.result b/mysql-test/main/create_or_replace.result index 294b0623fc1..178b7182666 100644 --- a/mysql-test/main/create_or_replace.result +++ b/mysql-test/main/create_or_replace.result @@ -1,3 +1,5 @@ +SET @save_persistent=@@GLOBAL.innodb_stats_persistent; +SET GLOBAL innodb_stats_persistent=OFF; CREATE TABLE t2 (a int); INSERT INTO t2 VALUES(1),(2),(3); # @@ -258,8 +260,7 @@ Note 1051 Unknown table 'test.t1,mysqltest2.t2' create table test.t1 (i int) engine=myisam; create table mysqltest2.t2 like test.t1; lock table test.t1 write, mysqltest2.t2 write; -select * from information_schema.metadata_lock_info -where table_name not like 'innodb_%_stats'; +select * from information_schema.metadata_lock_info; THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME # MDL_BACKUP_DDL NULL Backup lock # MDL_BACKUP_DML NULL Backup lock @@ -272,8 +273,7 @@ ERROR 42000: A table must have at least 1 column show tables; Tables_in_test t2 -select * from information_schema.metadata_lock_info -where table_name not like 'innodb_%_stats'; +select * from information_schema.metadata_lock_info; THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME # MDL_BACKUP_DDL NULL Backup lock # MDL_BACKUP_DML NULL Backup lock @@ -282,16 +282,14 @@ THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME # MDL_SHARED_NO_READ_WRITE NULL Table metadata lock mysqltest2 t2 create or replace table mysqltest2.t2; ERROR 42000: A table must have at least 1 column -select * from information_schema.metadata_lock_info -where table_name not like 'innodb_%_stats'; +select * from information_schema.metadata_lock_info; THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME create table t1 (i int); drop table t1; create table test.t1 (i int); create table mysqltest2.t2 like test.t1; lock table test.t1 write, mysqltest2.t2 write; -select * from information_schema.metadata_lock_info -where table_name not like 'innodb_%_stats'; +select * from information_schema.metadata_lock_info; THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME # MDL_BACKUP_DDL NULL Backup lock # MDL_BACKUP_DML NULL Backup lock @@ -304,8 +302,7 @@ ERROR 42S21: Duplicate column name 'a' show tables; Tables_in_test t2 -select * from information_schema.metadata_lock_info -where table_name not like 'innodb_%_stats'; +select * from information_schema.metadata_lock_info; THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME # MDL_BACKUP_DDL NULL Backup lock # MDL_BACKUP_DML NULL Backup lock @@ -314,16 +311,14 @@ THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME # MDL_SHARED_NO_READ_WRITE NULL Table metadata lock mysqltest2 t2 create or replace table mysqltest2.t2 (a int) select 1 as 'a', 2 as 'a'; ERROR 42S21: Duplicate column name 'a' -select * from information_schema.metadata_lock_info -where table_name not like 'innodb_%_stats'; +select * from information_schema.metadata_lock_info; THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME create table t1 (i int); drop table t1; create table test.t1 (i int) engine=innodb; create table mysqltest2.t2 like test.t1; lock table test.t1 write, mysqltest2.t2 write; -select * from information_schema.metadata_lock_info -where table_name not like 'innodb_%_stats'; +select * from information_schema.metadata_lock_info; THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME # MDL_BACKUP_DDL NULL Backup lock # MDL_INTENTION_EXCLUSIVE NULL Schema metadata lock mysqltest2 @@ -335,8 +330,7 @@ drop table test.t1,mysqltest2.t2; create table test.t1 (i int) engine=aria transactional=1 checksum=1; create table mysqltest2.t2 like test.t1; lock table test.t1 write, mysqltest2.t2 write; -select * from information_schema.metadata_lock_info -where table_name not like 'innodb_%_stats'; +select * from information_schema.metadata_lock_info; THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME # MDL_BACKUP_DDL NULL Backup lock # MDL_INTENTION_EXCLUSIVE NULL Schema metadata lock mysqltest2 @@ -353,8 +347,7 @@ drop table test.t1; # create table t1 (i int); lock table t1 write; -select * from information_schema.metadata_lock_info -where table_schema!='mysql' or table_name not like 'innodb_%_stats'; +select * from information_schema.metadata_lock_info; THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME # MDL_BACKUP_DDL NULL Backup lock # MDL_BACKUP_DML NULL Backup lock @@ -365,8 +358,7 @@ ERROR 22001: Data too long for column 'a' at row 1 show tables; Tables_in_test t2 -select * from information_schema.metadata_lock_info -where table_schema!='mysql' or table_name not like 'innodb_%_stats'; +select * from information_schema.metadata_lock_info; THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME create table t1 (i int); drop table t1; @@ -454,8 +446,7 @@ drop view t1; # create table t1 (a int); lock table t1 write, t2 read; -select * from information_schema.metadata_lock_info -where table_name not like 'innodb_%_stats'; +select * from information_schema.metadata_lock_info; THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME # MDL_BACKUP_DDL NULL Backup lock # MDL_BACKUP_DML NULL Backup lock @@ -463,8 +454,7 @@ THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME # MDL_SHARED_NO_READ_WRITE NULL Table metadata lock test t1 # MDL_SHARED_READ NULL Table metadata lock test t2 create or replace table t1 (i int); -select * from information_schema.metadata_lock_info -where table_name not like 'innodb_%_stats'; +select * from information_schema.metadata_lock_info; THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME # MDL_BACKUP_DDL NULL Backup lock # MDL_BACKUP_DML NULL Backup lock @@ -472,8 +462,7 @@ THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME # MDL_SHARED_NO_READ_WRITE NULL Table metadata lock test t1 # MDL_SHARED_READ NULL Table metadata lock test t2 create or replace table t1 like t2; -select * from information_schema.metadata_lock_info -where table_name not like 'innodb_%_stats'; +select * from information_schema.metadata_lock_info; THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME # MDL_BACKUP_DDL NULL Backup lock # MDL_BACKUP_DML NULL Backup lock @@ -481,8 +470,7 @@ THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME # MDL_SHARED_NO_READ_WRITE NULL Table metadata lock test t1 # MDL_SHARED_READ NULL Table metadata lock test t2 create or replace table t1 select 1 as f1; -select * from information_schema.metadata_lock_info -where table_name not like 'innodb_%_stats'; +select * from information_schema.metadata_lock_info; THREAD_ID LOCK_MODE LOCK_DURATION LOCK_TYPE TABLE_SCHEMA TABLE_NAME # MDL_BACKUP_DDL NULL Backup lock # MDL_BACKUP_DML NULL Backup lock @@ -580,3 +568,4 @@ ERROR HY000: Table 't3' was not locked with LOCK TABLES UNLOCK TABLES; DROP TABLE t3; # End of 10.4 tests +SET GLOBAL innodb_stats_persistent=@save_persistent; diff --git a/mysql-test/main/create_or_replace.test b/mysql-test/main/create_or_replace.test index 7fa08d13847..573e0e177c2 100644 --- a/mysql-test/main/create_or_replace.test +++ b/mysql-test/main/create_or_replace.test @@ -5,6 +5,9 @@ --source include/have_innodb.inc --source include/have_metadata_lock_info.inc +SET @save_persistent=@@GLOBAL.innodb_stats_persistent; +SET GLOBAL innodb_stats_persistent=OFF; + # # Create help table # @@ -212,21 +215,18 @@ create table mysqltest2.t2 like test.t1; lock table test.t1 write, mysqltest2.t2 write; --replace_column 1 # --sorted_result -select * from information_schema.metadata_lock_info -where table_name not like 'innodb_%_stats'; +select * from information_schema.metadata_lock_info; --error ER_TABLE_MUST_HAVE_COLUMNS create or replace table test.t1; show tables; --replace_column 1 # --sorted_result -select * from information_schema.metadata_lock_info -where table_name not like 'innodb_%_stats'; +select * from information_schema.metadata_lock_info; --error ER_TABLE_MUST_HAVE_COLUMNS create or replace table mysqltest2.t2; --replace_column 1 # --sorted_result -select * from information_schema.metadata_lock_info -where table_name not like 'innodb_%_stats'; +select * from information_schema.metadata_lock_info; create table t1 (i int); drop table t1; @@ -235,21 +235,18 @@ create table mysqltest2.t2 like test.t1; lock table test.t1 write, mysqltest2.t2 write; --replace_column 1 # --sorted_result -select * from information_schema.metadata_lock_info -where table_name not like 'innodb_%_stats'; +select * from information_schema.metadata_lock_info; --error ER_DUP_FIELDNAME create or replace table test.t1 (a int) select 1 as 'a', 2 as 'a'; show tables; --replace_column 1 # --sorted_result -select * from information_schema.metadata_lock_info -where table_name not like 'innodb_%_stats'; +select * from information_schema.metadata_lock_info; --error ER_DUP_FIELDNAME create or replace table mysqltest2.t2 (a int) select 1 as 'a', 2 as 'a'; --replace_column 1 # --sorted_result -select * from information_schema.metadata_lock_info -where table_name not like 'innodb_%_stats'; +select * from information_schema.metadata_lock_info; create table t1 (i int); drop table t1; @@ -258,8 +255,7 @@ create table mysqltest2.t2 like test.t1; lock table test.t1 write, mysqltest2.t2 write; --replace_column 1 # --sorted_result -select * from information_schema.metadata_lock_info -where table_name not like 'innodb_%_stats'; +select * from information_schema.metadata_lock_info; unlock tables; drop table test.t1,mysqltest2.t2; @@ -268,8 +264,7 @@ create table mysqltest2.t2 like test.t1; lock table test.t1 write, mysqltest2.t2 write; --replace_column 1 # --sorted_result -select * from information_schema.metadata_lock_info -where table_name not like 'innodb_%_stats'; +select * from information_schema.metadata_lock_info; unlock tables; drop table t1; @@ -285,15 +280,13 @@ create table t1 (i int); lock table t1 write; --replace_column 1 # --sorted_result -select * from information_schema.metadata_lock_info -where table_schema!='mysql' or table_name not like 'innodb_%_stats'; +select * from information_schema.metadata_lock_info; --error ER_DATA_TOO_LONG create or replace table t1 (a char(1)) engine=Innodb select 'foo' as a; show tables; --replace_column 1 # --sorted_result -select * from information_schema.metadata_lock_info -where table_schema!='mysql' or table_name not like 'innodb_%_stats'; +select * from information_schema.metadata_lock_info; create table t1 (i int); drop table t1; @@ -371,24 +364,20 @@ create table t1 (a int); lock table t1 write, t2 read; --replace_column 1 # --sorted_result -select * from information_schema.metadata_lock_info -where table_name not like 'innodb_%_stats'; +select * from information_schema.metadata_lock_info; create or replace table t1 (i int); --replace_column 1 # --sorted_result -select * from information_schema.metadata_lock_info -where table_name not like 'innodb_%_stats'; +select * from information_schema.metadata_lock_info; create or replace table t1 like t2; --replace_column 1 # --sorted_result -select * from information_schema.metadata_lock_info -where table_name not like 'innodb_%_stats'; +select * from information_schema.metadata_lock_info; create or replace table t1 select 1 as f1; --replace_column 1 # --sorted_result -select * from information_schema.metadata_lock_info -where table_name not like 'innodb_%_stats'; +select * from information_schema.metadata_lock_info; drop table t1; unlock tables; @@ -520,3 +509,5 @@ UNLOCK TABLES; DROP TABLE t3; --echo # End of 10.4 tests + +SET GLOBAL innodb_stats_persistent=@save_persistent; diff --git a/mysql-test/main/ctype_utf32.result b/mysql-test/main/ctype_utf32.result index cf9db875290..069e4174c9d 100644 --- a/mysql-test/main/ctype_utf32.result +++ b/mysql-test/main/ctype_utf32.result @@ -2913,5 +2913,30 @@ t1 CREATE TABLE `t1` ( DROP TABLE t1; SET NAMES utf8; # +# MDEV-28078 Garbage on multiple equal ENUMs with tricky character sets +# +CREATE TABLE t1 ( +c1 ENUM ('a','b') CHARACTER SET utf32 DEFAULT 'a', +c2 ENUM ('a','b') CHARACTER SET utf32 DEFAULT 'a' +); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` enum('a','b') CHARACTER SET utf32 DEFAULT 'a', + `c2` enum('a','b') CHARACTER SET utf32 DEFAULT 'a' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 ( +c1 ENUM ('00000061','00000062') DEFAULT '00000061' COLLATE latin1_bin, +c2 ENUM ('a','b') DEFAULT 'a' COLLATE utf32_general_ci +); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` enum('00000061','00000062') CHARACTER SET latin1 COLLATE latin1_bin DEFAULT '00000061', + `c2` enum('a','b') CHARACTER SET utf32 DEFAULT 'a' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +# # End of 10.2 tests # diff --git a/mysql-test/main/ctype_utf32.test b/mysql-test/main/ctype_utf32.test index 6944fdb30be..0e50405f871 100644 --- a/mysql-test/main/ctype_utf32.test +++ b/mysql-test/main/ctype_utf32.test @@ -1068,5 +1068,24 @@ SET NAMES utf8; --echo # +--echo # MDEV-28078 Garbage on multiple equal ENUMs with tricky character sets +--echo # + +CREATE TABLE t1 ( + c1 ENUM ('a','b') CHARACTER SET utf32 DEFAULT 'a', + c2 ENUM ('a','b') CHARACTER SET utf32 DEFAULT 'a' +); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 ( + c1 ENUM ('00000061','00000062') DEFAULT '00000061' COLLATE latin1_bin, + c2 ENUM ('a','b') DEFAULT 'a' COLLATE utf32_general_ci +); +SHOW CREATE TABLE t1; +DROP TABLE t1; + + +--echo # --echo # End of 10.2 tests --echo # diff --git a/mysql-test/main/multi_update_innodb.result b/mysql-test/main/multi_update_innodb.result index 2ec7eb3065e..52bbece4fa0 100644 --- a/mysql-test/main/multi_update_innodb.result +++ b/mysql-test/main/multi_update_innodb.result @@ -207,4 +207,19 @@ ERROR 23000: Duplicate entry '0000-00-00 00:00:00' for key 'f2k' DROP VIEW v1; DROP TABLE t3,t4; SET @@sql_mode=@save_sql_mode; +# # End of 10.2 tests +# +# +# MDEV-28095 crash in multi-update and implicit grouping +# +CREATE TABLE t1 (a int) engine=innodb; +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b int); +INSERT INTO t2 VALUES (1),(2); +UPDATE t1 NATURAL JOIN t2 SET a = 1 ORDER BY AVG (a) ; +ERROR HY000: Invalid use of group function +DROP TABLE t1, t2; +# +# End of 10.3 tests +# diff --git a/mysql-test/main/multi_update_innodb.test b/mysql-test/main/multi_update_innodb.test index 04736482011..02f6a7a3316 100644 --- a/mysql-test/main/multi_update_innodb.test +++ b/mysql-test/main/multi_update_innodb.test @@ -243,4 +243,23 @@ DROP VIEW v1; DROP TABLE t3,t4; SET @@sql_mode=@save_sql_mode; +--echo # --echo # End of 10.2 tests +--echo # + +--echo # +--echo # MDEV-28095 crash in multi-update and implicit grouping +--echo # +CREATE TABLE t1 (a int) engine=innodb; +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b int); +INSERT INTO t2 VALUES (1),(2); +--error ER_INVALID_GROUP_FUNC_USE +UPDATE t1 NATURAL JOIN t2 SET a = 1 ORDER BY AVG (a) ; +DROP TABLE t1, t2; + + +--echo # +--echo # End of 10.3 tests +--echo # + diff --git a/mysql-test/main/opt_tvc.result b/mysql-test/main/opt_tvc.result index 9752aa71bfb..9b6d97492cd 100644 --- a/mysql-test/main/opt_tvc.result +++ b/mysql-test/main/opt_tvc.result @@ -732,3 +732,30 @@ a b 4 4 drop table t1; SET @@in_predicate_conversion_threshold= default; +# +# MDEV-27937: Prepared statement with ? in the list if IN predicate +# +set in_predicate_conversion_threshold=2; +create table t1 (id int, a int, b int); +insert into t1 values (1,3,30), (2,7,70), (3,1,10); +prepare stmt from " +select * from t1 where a in (7, ?, 5, 1); +"; +execute stmt using 3; +id a b +1 3 30 +2 7 70 +3 1 10 +deallocate prepare stmt; +prepare stmt from " +select * from t1 where (a,b) in ((7,70), (3,?), (5,50), (1,10)); +"; +execute stmt using 30; +id a b +1 3 30 +2 7 70 +3 1 10 +deallocate prepare stmt; +drop table t1; +set in_predicate_conversion_threshold=default; +# End of 10.3 tests diff --git a/mysql-test/main/opt_tvc.test b/mysql-test/main/opt_tvc.test index e4e8c6d7919..f8469f22aa1 100644 --- a/mysql-test/main/opt_tvc.test +++ b/mysql-test/main/opt_tvc.test @@ -428,3 +428,29 @@ eval $query; drop table t1; SET @@in_predicate_conversion_threshold= default; +--echo # +--echo # MDEV-27937: Prepared statement with ? in the list if IN predicate +--echo # + +set in_predicate_conversion_threshold=2; + +create table t1 (id int, a int, b int); +insert into t1 values (1,3,30), (2,7,70), (3,1,10); + +prepare stmt from " +select * from t1 where a in (7, ?, 5, 1); +"; +execute stmt using 3; +deallocate prepare stmt; + +prepare stmt from " +select * from t1 where (a,b) in ((7,70), (3,?), (5,50), (1,10)); +"; +execute stmt using 30; +deallocate prepare stmt; + +drop table t1; + +set in_predicate_conversion_threshold=default; + +--echo # End of 10.3 tests diff --git a/mysql-test/main/processlist.result b/mysql-test/main/processlist.result index 2d3228a6d91..d99160f5c74 100644 --- a/mysql-test/main/processlist.result +++ b/mysql-test/main/processlist.result @@ -40,3 +40,23 @@ utf8mb4_string xxx😎yyy # # End of 10.1 tests # +# +# Start of 10.3 tests +# +# +# MDEV-28131 Unexpected warning while selecting from information_schema.processlist +# +connect conn1, localhost, root,,; +connection conn1; +SELECT SLEEP(1000); +connection default; +SELECT progress FROM information_schema.processlist WHERE info='SELECT SLEEP(1000)'; +progress +0.000 +connection conn1; +Got one of the listed errors +connection default; +disconnect conn1; +# +# End of 10.3 tests +# diff --git a/mysql-test/main/processlist.test b/mysql-test/main/processlist.test index 8e98701459a..f419f57ea2f 100644 --- a/mysql-test/main/processlist.test +++ b/mysql-test/main/processlist.test @@ -70,3 +70,38 @@ SELECT INFO, INFO_BINARY, 'xxx😎yyy' AS utf8mb4_string FROM INFORMATION_SCHEMA --echo # --echo # End of 10.1 tests --echo # + +--echo # +--echo # Start of 10.3 tests +--echo # + +--echo # +--echo # MDEV-28131 Unexpected warning while selecting from information_schema.processlist +--echo # + +connect (conn1, localhost, root,,); +connection conn1; +let $ID= `select connection_id()`; +send SELECT SLEEP(1000); +connection default; +let $wait_timeout= 10; +let $wait_condition=select count(*)=1 from information_schema.processlist +where state='User sleep' and info='SELECT SLEEP(1000)'; +--source include/wait_condition.inc +SELECT progress FROM information_schema.processlist WHERE info='SELECT SLEEP(1000)'; +disable_query_log; +eval kill $ID; +enable_query_log; +let $wait_timeout= 10; +let $wait_condition=select count(*)=0 from information_schema.processlist +where state='User sleep' and info='SELECT SLEEP(1000)'; +--source include/wait_condition.inc +connection conn1; +--error 2013,ER_CONNECTION_KILLED +reap; +connection default; +disconnect conn1; + +--echo # +--echo # End of 10.3 tests +--echo # diff --git a/mysql-test/main/sp-cursor.result b/mysql-test/main/sp-cursor.result index dc38ad64069..2218f8c36fe 100644 --- a/mysql-test/main/sp-cursor.result +++ b/mysql-test/main/sp-cursor.result @@ -738,6 +738,69 @@ c DROP PROCEDURE p1; DROP TABLE t1; # +# MDEV-26009: Server crash when calling twice procedure using FOR-loop +# +CREATE TABLE t1 ( id int, name varchar(24)); +INSERT INTO t1 values (1, 'x'), (2, 'y'), (3, 'z'); +create function get_name(_id int) returns varchar(24) +return (select name from t1 where id = _id); +select get_name(id) from t1; +get_name(id) +x +y +z +create procedure test_proc() +begin +declare _cur cursor for select get_name(id) from t1; +for row in _cur do select 1; end for; +end; +^^ +call test_proc(); +1 +1 +1 +1 +1 +1 +call test_proc(); +1 +1 +1 +1 +1 +1 +drop procedure test_proc; +drop function get_name; +drop table t1; +CREATE TABLE t1 (id int, name varchar(24)); +INSERT INTO t1 (id, name) VALUES (1, 'x'),(2, 'y'),(3, 'z'); +create function get_name(_id int) returns varchar(24) +return (select name from t1 where id = _id); +create view v1 as select get_name(id) from t1; +create procedure test_proc() +begin +declare _cur cursor for select 1 from v1; +for row in _cur do select 1; end for; +end$$ +call test_proc(); +1 +1 +1 +1 +1 +1 +call test_proc(); +1 +1 +1 +1 +1 +1 +drop procedure test_proc; +drop view v1; +drop function get_name; +drop table t1; +# # Start of 10.8 tests # # diff --git a/mysql-test/main/sp-cursor.test b/mysql-test/main/sp-cursor.test index f86721f41e8..44fe6ba51e8 100644 --- a/mysql-test/main/sp-cursor.test +++ b/mysql-test/main/sp-cursor.test @@ -747,6 +747,62 @@ DROP TABLE t1; --echo # +--echo # MDEV-26009: Server crash when calling twice procedure using FOR-loop +--echo # + + +CREATE TABLE t1 ( id int, name varchar(24)); +INSERT INTO t1 values (1, 'x'), (2, 'y'), (3, 'z'); + +create function get_name(_id int) returns varchar(24) + return (select name from t1 where id = _id); + +select get_name(id) from t1; + +delimiter ^^; + +create procedure test_proc() +begin + declare _cur cursor for select get_name(id) from t1; + for row in _cur do select 1; end for; +end; +^^ +delimiter ;^^ + +call test_proc(); +call test_proc(); + +drop procedure test_proc; +drop function get_name; +drop table t1; + + +CREATE TABLE t1 (id int, name varchar(24)); +INSERT INTO t1 (id, name) VALUES (1, 'x'),(2, 'y'),(3, 'z'); + +create function get_name(_id int) returns varchar(24) + return (select name from t1 where id = _id); + +create view v1 as select get_name(id) from t1; + +delimiter $$; +create procedure test_proc() +begin + declare _cur cursor for select 1 from v1; + for row in _cur do select 1; end for; +end$$ +delimiter ;$$ + +call test_proc(); +call test_proc(); + +drop procedure test_proc; +drop view v1; +drop function get_name; +drop table t1; + + +--echo # --echo # Start of 10.8 tests --echo # diff --git a/mysql-test/main/view.result b/mysql-test/main/view.result index 075e50e1b0a..35471454cb4 100644 --- a/mysql-test/main/view.result +++ b/mysql-test/main/view.result @@ -6846,6 +6846,34 @@ id bar Drop View v1; Drop table t1; # +# MDEV-24281: Execution of PREPARE from CREATE VIEW statement +# +create table t1 (s1 int); +insert into t1 values (3), (7), (1); +prepare stmt from " +create view v1 as select 's1', s1, 1 as My_exp_s1 from t1; +"; +execute stmt; +deallocate prepare stmt; +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 's1' AS `My_exp_1_s1`,`t1`.`s1` AS `s1`,1 AS `My_exp_s1` from `t1` latin1 latin1_swedish_ci +select * from v1; +My_exp_1_s1 s1 My_exp_s1 +s1 3 1 +s1 7 1 +s1 1 1 +drop view v1; +prepare stmt from " +create view v1 as select 's1', s1, 1 as My_exp_s1 from t1; +"; +execute stmt; +execute stmt; +ERROR 42S01: Table 'v1' already exists +deallocate prepare stmt; +drop view v1; +drop table t1; +# # End of 10.3 tests # # diff --git a/mysql-test/main/view.test b/mysql-test/main/view.test index 825274756a8..49b339c9f4c 100644 --- a/mysql-test/main/view.test +++ b/mysql-test/main/view.test @@ -6577,6 +6577,32 @@ Drop View v1; Drop table t1; --echo # +--echo # MDEV-24281: Execution of PREPARE from CREATE VIEW statement +--echo # + +create table t1 (s1 int); +insert into t1 values (3), (7), (1); + +prepare stmt from " +create view v1 as select 's1', s1, 1 as My_exp_s1 from t1; +"; +execute stmt; +deallocate prepare stmt; +show create view v1; +select * from v1; +drop view v1; + +prepare stmt from " +create view v1 as select 's1', s1, 1 as My_exp_s1 from t1; +"; +execute stmt; +--error ER_TABLE_EXISTS_ERROR +execute stmt; +deallocate prepare stmt; +drop view v1; +drop table t1; + +--echo # --echo # End of 10.3 tests --echo # diff --git a/mysql-test/suite/binlog/r/binlog_autocommit_off_no_hang.result b/mysql-test/suite/binlog/r/binlog_autocommit_off_no_hang.result new file mode 100644 index 00000000000..71eecd881ca --- /dev/null +++ b/mysql-test/suite/binlog/r/binlog_autocommit_off_no_hang.result @@ -0,0 +1,6 @@ +ALTER TABLE mysql.gtid_slave_pos ENGINE=innodb; +# Restart the server so mysqld reads the gtid_slave_pos using innodb +# Set gtid_slave_pos should not hang +SET GLOBAL gtid_slave_pos=@@gtid_binlog_pos; +COMMIT; +RESET MASTER; diff --git a/mysql-test/suite/binlog/r/binlog_mysqlbinlog_raw_flush.result b/mysql-test/suite/binlog/r/binlog_mysqlbinlog_raw_flush.result new file mode 100644 index 00000000000..9148f0e8c2b --- /dev/null +++ b/mysql-test/suite/binlog/r/binlog_mysqlbinlog_raw_flush.result @@ -0,0 +1,7 @@ +CREATE TABLE t1 (a int); +FLUSH LOGS; +INSERT INTO t1 VALUES (1); +# timeout TIMEOUT MYSQL_BINLOG --raw --read-from-remote-server --user=root --host=127.0.0.1 --port=MASTER_MYPORT --stop-never --result-file=MYSQLTEST_VARDIR/tmp/ master-bin.000001 +# MYSQL_BINLOG MYSQLTEST_VARDIR/tmp/master-bin.000002 > MYSQLTEST_VARDIR/tmp/local-bin.000002.out +FOUND 1 /GTID 0-1-2/ in local-bin.000002.out +DROP TABLE t1; diff --git a/mysql-test/suite/binlog/t/binlog_autocommit_off_no_hang-master.opt b/mysql-test/suite/binlog/t/binlog_autocommit_off_no_hang-master.opt new file mode 100644 index 00000000000..e0fa81e6eeb --- /dev/null +++ b/mysql-test/suite/binlog/t/binlog_autocommit_off_no_hang-master.opt @@ -0,0 +1 @@ +--autocommit=0 diff --git a/mysql-test/suite/binlog/t/binlog_autocommit_off_no_hang.test b/mysql-test/suite/binlog/t/binlog_autocommit_off_no_hang.test new file mode 100644 index 00000000000..8f1dbb2a2dd --- /dev/null +++ b/mysql-test/suite/binlog/t/binlog_autocommit_off_no_hang.test @@ -0,0 +1,45 @@ +# +# Purpose: +# When the mysql.gtid_slave_pos table uses the InnoDB engine, and mysqld +# starts, it reads the table and begins a transaction. After mysqld reads the +# value, it should end the transaction and release all associated locks. +# The bug reported in DBAAS-7828 shows that when autocommit is off, the locks +# are not released, resulting in indefinite hangs on future attempts to change +# gtid_slave_pos. This test ensures its fix such that the locks are properly +# released. +# +# References: +# DBAAS-7828: Primary/replica: configuration change of "autocommit=0" can +# not be applied +# + +--source include/have_innodb.inc +--source include/have_log_bin.inc + +# Reading gtid_slave_pos table is format independent so just use one for +# reduced test time +--source include/have_binlog_format_row.inc + +--let old_slave_pos_engine= query_get_value(SHOW TABLE STATUS FROM mysql LIKE 'gtid_slave_pos', Engine, 1) + +# Use a transactional engine +ALTER TABLE mysql.gtid_slave_pos ENGINE=innodb; + +--echo # Restart the server so mysqld reads the gtid_slave_pos using innodb +--exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +--shutdown_server +--source include/wait_until_disconnected.inc +--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +--enable_reconnect +--source include/wait_until_connected_again.inc + +--echo # Set gtid_slave_pos should not hang +SET GLOBAL gtid_slave_pos=@@gtid_binlog_pos; +COMMIT; + +# Revert table type +--disable_query_log +--eval ALTER TABLE mysql.gtid_slave_pos ENGINE=$old_slave_pos_engine +--enable_query_log + +RESET MASTER; diff --git a/mysql-test/suite/binlog/t/binlog_mysqlbinlog_raw_flush.test b/mysql-test/suite/binlog/t/binlog_mysqlbinlog_raw_flush.test new file mode 100644 index 00000000000..f95fc0137a2 --- /dev/null +++ b/mysql-test/suite/binlog/t/binlog_mysqlbinlog_raw_flush.test @@ -0,0 +1,45 @@ +# +# Purpose: +# When using mariadb-binlog with options for --raw and --stop-never, events +# from the master's currently active log file should be written to their +# respective log file specified by --result-file, and shown on-disk. This test +# ensures that the log files on disk, created by mariadb-binlog, have the most +# up-to-date events from the master. +# +# Methodology: +# On the master, rotate to a newly active binlog file and write an event to +# it. Read the master's binlog using mariadb-binlog with --raw and --stop-never +# and write the data to an intermediary binlog file (a timeout is used on this +# command to ensure it exits). Read the local intermediary binlog file to ensure +# that the master's most recent event exists in the local file. +# +# References: +# MDEV-14608: mysqlbinlog lastest backupfile size is 0 +# + +--source include/linux.inc +--source include/have_log_bin.inc + +# Create newly active log +CREATE TABLE t1 (a int); +FLUSH LOGS; +INSERT INTO t1 VALUES (1); + +# Read binlog data from master to intermediary result file +--let TIMEOUT=1 +--echo # timeout TIMEOUT MYSQL_BINLOG --raw --read-from-remote-server --user=root --host=127.0.0.1 --port=MASTER_MYPORT --stop-never --result-file=MYSQLTEST_VARDIR/tmp/ master-bin.000001 +--error 124 # Error 124 means timeout was reached +--exec timeout $TIMEOUT $MYSQL_BINLOG --raw --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT --stop-never --result-file=$MYSQLTEST_VARDIR/tmp/ master-bin.000001 + +# Ensure the binlog output has the most recent events from the master +--echo # MYSQL_BINLOG MYSQLTEST_VARDIR/tmp/master-bin.000002 > MYSQLTEST_VARDIR/tmp/local-bin.000002.out +--exec $MYSQL_BINLOG $MYSQLTEST_VARDIR/tmp/master-bin.000002 > $MYSQLTEST_VARDIR/tmp/local-bin.000002.out +--let SEARCH_PATTERN= GTID 0-1-2 +--let SEARCH_FILE= $MYSQLTEST_VARDIR/tmp/local-bin.000002.out +--source include/search_pattern_in_file.inc + +# Cleanup +DROP TABLE t1; +--remove_file $MYSQLTEST_VARDIR/tmp/master-bin.000001 +--remove_file $MYSQLTEST_VARDIR/tmp/master-bin.000002 +--remove_file $MYSQLTEST_VARDIR/tmp/local-bin.000002.out diff --git a/mysql-test/suite/compat/oracle/r/sp-package.result b/mysql-test/suite/compat/oracle/r/sp-package.result index 273c2f3ea7d..daa244a3c5a 100644 --- a/mysql-test/suite/compat/oracle/r/sp-package.result +++ b/mysql-test/suite/compat/oracle/r/sp-package.result @@ -2960,3 +2960,311 @@ END $$ CALL xyz.xyz123(17,18,@R); DROP PACKAGE xyz; DROP TABLE t1; +# +# MDEV-28166 sql_mode=ORACLE: fully qualified package function calls do not work: db.pkg.func() +# +SELECT `db `.pkg.func(); +ERROR 42000: Incorrect database name 'db ' +SELECT db.`pkg `.func(); +ERROR 42000: Incorrect routine name 'pkg ' +SELECT db.pkg.`func `(); +ERROR 42000: Incorrect routine name 'func ' +CREATE DATABASE db1; +USE db1; +CREATE PACKAGE pkg1 AS +FUNCTION f1 RETURN TEXT; +FUNCTION f2_db1_pkg1_f1 RETURN TEXT; +FUNCTION f2_pkg1_f1 RETURN TEXT; +FUNCTION f2_f1 RETURN TEXT; +END; +$$ +CREATE PACKAGE BODY pkg1 +AS +FUNCTION f1 RETURN TEXT IS +BEGIN +RETURN 'This is db1.pkg1.f1'; +END; +FUNCTION f2_db1_pkg1_f1 RETURN TEXT IS +BEGIN +RETURN db1.pkg1.f1(); +END; +FUNCTION f2_pkg1_f1 RETURN TEXT IS +BEGIN +RETURN pkg1.f1(); +END; +FUNCTION f2_f1 RETURN TEXT IS +BEGIN +RETURN f1(); +END; +END; +$$ +USE db1; +SELECT pkg1.f2_db1_pkg1_f1(); +pkg1.f2_db1_pkg1_f1() +This is db1.pkg1.f1 +SELECT pkg1.f2_pkg1_f1(); +pkg1.f2_pkg1_f1() +This is db1.pkg1.f1 +SELECT pkg1.f2_f1(); +pkg1.f2_f1() +This is db1.pkg1.f1 +SELECT db1.pkg1.f2_db1_pkg1_f1(); +db1.pkg1.f2_db1_pkg1_f1() +This is db1.pkg1.f1 +SELECT db1.pkg1.f2_pkg1_f1(); +db1.pkg1.f2_pkg1_f1() +This is db1.pkg1.f1 +SELECT db1.pkg1.f2_f1(); +db1.pkg1.f2_f1() +This is db1.pkg1.f1 +USE test; +SELECT db1.pkg1.f2_db1_pkg1_f1(); +db1.pkg1.f2_db1_pkg1_f1() +This is db1.pkg1.f1 +SELECT db1.pkg1.f2_pkg1_f1(); +db1.pkg1.f2_pkg1_f1() +This is db1.pkg1.f1 +SELECT db1.pkg1.f2_f1(); +db1.pkg1.f2_f1() +This is db1.pkg1.f1 +DROP DATABASE db1; +CREATE DATABASE db1; +CREATE DATABASE db2; +CREATE PACKAGE db1.pkg1 AS +FUNCTION f1 RETURN TEXT; +END; +$$ +CREATE PACKAGE BODY db1.pkg1 AS +FUNCTION f1 RETURN TEXT AS +BEGIN +RETURN 'This is db1.pkg1.f1'; +END; +END; +$$ +CREATE PACKAGE db2.pkg1 AS +FUNCTION f1 RETURN TEXT; +FUNCTION var1 RETURN TEXT; +FUNCTION var2 RETURN TEXT; +END; +$$ +CREATE PACKAGE BODY db2.pkg1 AS +m_var1 TEXT; +m_var2 TEXT; +FUNCTION f1 RETURN TEXT AS +BEGIN +RETURN 'This is db2.pkg1.f1'; +END; +FUNCTION var1 RETURN TEXT AS +BEGIN +RETURN m_var1; +END; +FUNCTION var2 RETURN TEXT AS +BEGIN +RETURN m_var2; +END; +BEGIN +m_var1:= db1.pkg1.f1(); +m_var2:= db2.pkg1.f1(); +END; +$$ +SELECT db2.pkg1.var1(), db2.pkg1.var2(); +db2.pkg1.var1() db2.pkg1.var2() +This is db1.pkg1.f1 This is db2.pkg1.f1 +DROP DATABASE db1; +DROP DATABASE db2; +CREATE PACKAGE pkg1 AS +FUNCTION f1(a TEXT) RETURN TEXT; +END; +$$ +CREATE PACKAGE BODY pkg1 AS +FUNCTION f1(a TEXT) RETURN TEXT AS +BEGIN +RETURN a; +END; +END; +$$ +SELECT test.pkg1.f1('xxx'); +test.pkg1.f1('xxx') +xxx +SELECT test.pkg1.f1('xxx' AS a); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AS a)' at line 1 +DROP PACKAGE pkg1; +# +# MDEV-19328 sql_mode=ORACLE: Package function in VIEW +# +SET sql_mode=ORACLE; +CREATE PACKAGE test1 AS +FUNCTION f_test RETURN number; +END test1; +$$ +CREATE PACKAGE BODY test1 +AS +FUNCTION f_test RETURN NUMBER IS +BEGIN +RETURN 1; +END; +END test1; +$$ +SET sql_mode=ORACLE; +CREATE VIEW v_test AS SELECT 1 AS c1 FROM DUAL WHERE 1=test1.f_test(); +SELECT * FROM v_test; +c1 +1 +SHOW CREATE VIEW v_test; +View v_test +Create View CREATE VIEW "v_test" AS select 1 AS "c1" from DUAL where 1 = "test"."test1"."f_test"() +character_set_client latin1 +collation_connection latin1_swedish_ci +SET sql_mode=DEFAULT; +SELECT * FROM v_test; +c1 +1 +SHOW CREATE VIEW v_test; +View v_test +Create View CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_test` AS select 1 AS `c1` from DUAL where 1 = `test`.`test1`.`f_test`() +character_set_client latin1 +collation_connection latin1_swedish_ci +DROP VIEW v_test; +SET sql_mode=DEFAULT; +CREATE VIEW v_test AS SELECT 1 AS c1 FROM DUAL WHERE 1=test1.f_test(); +ERROR 42000: FUNCTION test1.f_test does not exist +SET sql_mode=ORACLE; +CREATE VIEW v_test AS SELECT 1 AS c1 FROM DUAL WHERE 1=test.test1.f_test(); +SELECT * FROM v_test; +c1 +1 +SHOW CREATE VIEW v_test; +View v_test +Create View CREATE VIEW "v_test" AS select 1 AS "c1" from DUAL where 1 = "test"."test1"."f_test"() +character_set_client latin1 +collation_connection latin1_swedish_ci +SET sql_mode=DEFAULT; +SELECT * FROM v_test; +c1 +1 +SHOW CREATE VIEW v_test; +View v_test +Create View CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_test` AS select 1 AS `c1` from DUAL where 1 = `test`.`test1`.`f_test`() +character_set_client latin1 +collation_connection latin1_swedish_ci +DROP VIEW v_test; +SET sql_mode=DEFAULT; +CREATE VIEW v_test AS SELECT 1 AS c1 FROM DUAL WHERE 1=test.test1.f_test(); +SELECT * FROM v_test; +c1 +1 +SHOW CREATE VIEW v_test; +View v_test +Create View CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_test` AS select 1 AS `c1` from DUAL where 1 = `test`.`test1`.`f_test`() +character_set_client latin1 +collation_connection latin1_swedish_ci +SET sql_mode=ORACLE; +SELECT * FROM v_test; +c1 +1 +SHOW CREATE VIEW v_test; +View v_test +Create View CREATE VIEW "v_test" AS select 1 AS "c1" from DUAL where 1 = "test"."test1"."f_test"() +character_set_client latin1 +collation_connection latin1_swedish_ci +DROP VIEW v_test; +SET sql_mode=ORACLE; +DROP PACKAGE test1; +# +# MDEV-19804 sql_mode=ORACLE: call procedure in packages +# +CALL `db1 `.pkg.p; +ERROR 42000: Incorrect database name 'db1 ' +CALL db1.`pkg `.p; +ERROR 42000: Incorrect routine name 'pkg ' +CALL db1.pkg.`p `; +ERROR 42000: Incorrect routine name 'p ' +SET sql_mode=ORACLE; +CREATE PACKAGE pkg1 as +PROCEDURE p1(); +END; +$$ +CREATE PACKAGE BODY pkg1 as +PROCEDURE p1() as +BEGIN +SELECT 'test-function' AS c1; +END; +END; +$$ +CALL pkg1.p1; +c1 +test-function +CALL test.pkg1.p1; +c1 +test-function +SET sql_mode=DEFAULT; +CALL test.pkg1.p1; +c1 +test-function +SET sql_mode=ORACLE; +BEGIN +CALL pkg1.p1; +CALL test.pkg1.p1; +END +$$ +c1 +test-function +c1 +test-function +BEGIN +pkg1.p1; +test.pkg1.p1; +END +$$ +c1 +test-function +c1 +test-function +DROP PACKAGE pkg1; +CREATE DATABASE db1; +CREATE PACKAGE db1.pkg1 AS +PROCEDURE p1(a OUT TEXT); +END; +$$ +CREATE PACKAGE BODY db1.pkg1 AS +PROCEDURE p1(a OUT TEXT) AS +BEGIN +a:= 'This is db1.pkg1.p1'; +END; +END; +$$ +CREATE DATABASE db2; +CREATE PACKAGE db2.pkg1 AS +FUNCTION var1 RETURN TEXT; +PROCEDURE p1(a OUT TEXT); +PROCEDURE p2_db1_pkg1_p1; +END; +$$ +CREATE PACKAGE BODY db2.pkg1 AS +m_var1 TEXT; +FUNCTION var1 RETURN TEXT AS +BEGIN +RETURN m_var1; +END; +PROCEDURE p1(a OUT TEXT) AS +BEGIN +a:= 'This is db2.pkg1.p1'; +END; +PROCEDURE p2_db1_pkg1_p1 AS +a TEXT; +BEGIN +db1.pkg1.p1(a); +SELECT a; +END; +BEGIN +db1.pkg1.p1(m_var1); +END; +$$ +SELECT db2.pkg1.var1(); +db2.pkg1.var1() +This is db1.pkg1.p1 +CALL db2.pkg1.p2_db1_pkg1_p1; +a +This is db1.pkg1.p1 +DROP DATABASE db1; +DROP DATABASE db2; diff --git a/mysql-test/suite/compat/oracle/t/sp-package.test b/mysql-test/suite/compat/oracle/t/sp-package.test index edad90e547f..615ce51e195 100644 --- a/mysql-test/suite/compat/oracle/t/sp-package.test +++ b/mysql-test/suite/compat/oracle/t/sp-package.test @@ -2689,3 +2689,330 @@ CALL xyz.xyz123(17,18,@R); DROP PACKAGE xyz; DROP TABLE t1; --disable_prepare_warnings + + +--echo # +--echo # MDEV-28166 sql_mode=ORACLE: fully qualified package function calls do not work: db.pkg.func() +--echo # + +--error ER_WRONG_DB_NAME +SELECT `db `.pkg.func(); +--error ER_SP_WRONG_NAME +SELECT db.`pkg `.func(); +--error ER_SP_WRONG_NAME +SELECT db.pkg.`func `(); + + +CREATE DATABASE db1; +USE db1; + +DELIMITER $$; +CREATE PACKAGE pkg1 AS + FUNCTION f1 RETURN TEXT; + FUNCTION f2_db1_pkg1_f1 RETURN TEXT; + FUNCTION f2_pkg1_f1 RETURN TEXT; + FUNCTION f2_f1 RETURN TEXT; +END; +$$ +CREATE PACKAGE BODY pkg1 +AS + FUNCTION f1 RETURN TEXT IS + BEGIN + RETURN 'This is db1.pkg1.f1'; + END; + FUNCTION f2_db1_pkg1_f1 RETURN TEXT IS + BEGIN + RETURN db1.pkg1.f1(); + END; + FUNCTION f2_pkg1_f1 RETURN TEXT IS + BEGIN + RETURN pkg1.f1(); + END; + FUNCTION f2_f1 RETURN TEXT IS + BEGIN + RETURN f1(); + END; +END; +$$ +DELIMITER ;$$ + +USE db1; +SELECT pkg1.f2_db1_pkg1_f1(); +SELECT pkg1.f2_pkg1_f1(); +SELECT pkg1.f2_f1(); + +SELECT db1.pkg1.f2_db1_pkg1_f1(); +SELECT db1.pkg1.f2_pkg1_f1(); +SELECT db1.pkg1.f2_f1(); + +USE test; +SELECT db1.pkg1.f2_db1_pkg1_f1(); +SELECT db1.pkg1.f2_pkg1_f1(); +SELECT db1.pkg1.f2_f1(); + +DROP DATABASE db1; + + +# +# Testing db.pkg.func() in the package initialization section +# + +CREATE DATABASE db1; +CREATE DATABASE db2; + +DELIMITER $$; +CREATE PACKAGE db1.pkg1 AS + FUNCTION f1 RETURN TEXT; +END; +$$ +CREATE PACKAGE BODY db1.pkg1 AS + FUNCTION f1 RETURN TEXT AS + BEGIN + RETURN 'This is db1.pkg1.f1'; + END; +END; +$$ +DELIMITER ;$$ + + +DELIMITER $$; +CREATE PACKAGE db2.pkg1 AS + FUNCTION f1 RETURN TEXT; + FUNCTION var1 RETURN TEXT; + FUNCTION var2 RETURN TEXT; +END; +$$ +CREATE PACKAGE BODY db2.pkg1 AS + m_var1 TEXT; + m_var2 TEXT; + FUNCTION f1 RETURN TEXT AS + BEGIN + RETURN 'This is db2.pkg1.f1'; + END; + FUNCTION var1 RETURN TEXT AS + BEGIN + RETURN m_var1; + END; + FUNCTION var2 RETURN TEXT AS + BEGIN + RETURN m_var2; + END; +BEGIN + m_var1:= db1.pkg1.f1(); + m_var2:= db2.pkg1.f1(); +END; +$$ +DELIMITER ;$$ + +SELECT db2.pkg1.var1(), db2.pkg1.var2(); + +DROP DATABASE db1; +DROP DATABASE db2; + +# +# Make sure fully qualified package function call does not support AS syntax: +# SELECT db.pkg.func(10 AS a); +# + +DELIMITER $$; +CREATE PACKAGE pkg1 AS + FUNCTION f1(a TEXT) RETURN TEXT; +END; +$$ +CREATE PACKAGE BODY pkg1 AS + FUNCTION f1(a TEXT) RETURN TEXT AS + BEGIN + RETURN a; + END; +END; +$$ +DELIMITER ;$$ +SELECT test.pkg1.f1('xxx'); +--error ER_PARSE_ERROR +SELECT test.pkg1.f1('xxx' AS a); +DROP PACKAGE pkg1; + + +--echo # +--echo # MDEV-19328 sql_mode=ORACLE: Package function in VIEW +--echo # + +SET sql_mode=ORACLE; +DELIMITER $$; +CREATE PACKAGE test1 AS + FUNCTION f_test RETURN number; +END test1; +$$ +CREATE PACKAGE BODY test1 +AS + FUNCTION f_test RETURN NUMBER IS + BEGIN + RETURN 1; + END; +END test1; +$$ +DELIMITER ;$$ + + +SET sql_mode=ORACLE; +CREATE VIEW v_test AS SELECT 1 AS c1 FROM DUAL WHERE 1=test1.f_test(); +SELECT * FROM v_test; +--vertical_results +SHOW CREATE VIEW v_test; +--horizontal_results +SET sql_mode=DEFAULT; +SELECT * FROM v_test; +--vertical_results +SHOW CREATE VIEW v_test; +--horizontal_results +DROP VIEW v_test; + + +SET sql_mode=DEFAULT; +--error ER_SP_DOES_NOT_EXIST +CREATE VIEW v_test AS SELECT 1 AS c1 FROM DUAL WHERE 1=test1.f_test(); + + +SET sql_mode=ORACLE; +CREATE VIEW v_test AS SELECT 1 AS c1 FROM DUAL WHERE 1=test.test1.f_test(); +SELECT * FROM v_test; +--vertical_results +SHOW CREATE VIEW v_test; +--horizontal_results +SET sql_mode=DEFAULT; +SELECT * FROM v_test; +--vertical_results +SHOW CREATE VIEW v_test; +--horizontal_results +DROP VIEW v_test; + + +SET sql_mode=DEFAULT; +CREATE VIEW v_test AS SELECT 1 AS c1 FROM DUAL WHERE 1=test.test1.f_test(); +SELECT * FROM v_test; +--vertical_results +SHOW CREATE VIEW v_test; +--horizontal_results +SET sql_mode=ORACLE; +SELECT * FROM v_test; +--vertical_results +SHOW CREATE VIEW v_test; +--horizontal_results +DROP VIEW v_test; + +SET sql_mode=ORACLE; +DROP PACKAGE test1; + + +--echo # +--echo # MDEV-19804 sql_mode=ORACLE: call procedure in packages +--echo # + +--error ER_WRONG_DB_NAME +CALL `db1 `.pkg.p; +--error ER_SP_WRONG_NAME +CALL db1.`pkg `.p; +--error ER_SP_WRONG_NAME +CALL db1.pkg.`p `; + + +SET sql_mode=ORACLE; +DELIMITER $$; +CREATE PACKAGE pkg1 as + PROCEDURE p1(); +END; +$$ +CREATE PACKAGE BODY pkg1 as + PROCEDURE p1() as + BEGIN + SELECT 'test-function' AS c1; + END; +END; +$$ +DELIMITER ;$$ + +CALL pkg1.p1; +CALL test.pkg1.p1; + +# In sql_mode=DEFAULT we support fully qualified package function names +# (this is needed for VIEWs). Let's make sure we also support fully +# qualified package procedure names, for symmetry + +SET sql_mode=DEFAULT; +CALL test.pkg1.p1; +SET sql_mode=ORACLE; + +DELIMITER $$; +BEGIN + CALL pkg1.p1; + CALL test.pkg1.p1; +END +$$ +DELIMITER ;$$ + +DELIMITER $$; +BEGIN + pkg1.p1; + test.pkg1.p1; +END +$$ +DELIMITER ;$$ + +DROP PACKAGE pkg1; + + +# +# Testing packages in different databases calling each other +# in routines and in the initialization section. +# + +CREATE DATABASE db1; +DELIMITER $$; +CREATE PACKAGE db1.pkg1 AS + PROCEDURE p1(a OUT TEXT); +END; +$$ +CREATE PACKAGE BODY db1.pkg1 AS + PROCEDURE p1(a OUT TEXT) AS + BEGIN + a:= 'This is db1.pkg1.p1'; + END; +END; +$$ +DELIMITER ;$$ + +CREATE DATABASE db2; +DELIMITER $$; +CREATE PACKAGE db2.pkg1 AS + FUNCTION var1 RETURN TEXT; + PROCEDURE p1(a OUT TEXT); + PROCEDURE p2_db1_pkg1_p1; +END; +$$ +CREATE PACKAGE BODY db2.pkg1 AS + m_var1 TEXT; + FUNCTION var1 RETURN TEXT AS + BEGIN + RETURN m_var1; + END; + PROCEDURE p1(a OUT TEXT) AS + BEGIN + a:= 'This is db2.pkg1.p1'; + END; + PROCEDURE p2_db1_pkg1_p1 AS + a TEXT; + BEGIN + db1.pkg1.p1(a); + SELECT a; + END; +BEGIN + db1.pkg1.p1(m_var1); +END; +$$ +DELIMITER ;$$ + +SELECT db2.pkg1.var1(); +CALL db2.pkg1.p2_db1_pkg1_p1; + +DROP DATABASE db1; +DROP DATABASE db2; diff --git a/mysql-test/suite/galera/disabled.def b/mysql-test/suite/galera/disabled.def index eb1aba72162..76992e9d59b 100644 --- a/mysql-test/suite/galera/disabled.def +++ b/mysql-test/suite/galera/disabled.def @@ -44,3 +44,4 @@ pxc-421: wsrep_provider is read-only for security reasons query_cache: MDEV-15805 Test failure on galera.query_cache versioning_trx_id: MDEV-18590: galera.versioning_trx_id: Test failure: mysqltest: Result content mismatch galera_bf_abort_at_after_statement : Unstable +galera_bf_abort_ps_bind : MDEV-28193 Galera test failure on galera_bf_abort_ps_bind diff --git a/mysql-test/suite/galera/include/kill_galera.inc b/mysql-test/suite/galera/include/kill_galera.inc index 98ebf4ff35d..56118df84f9 100644 --- a/mysql-test/suite/galera/include/kill_galera.inc +++ b/mysql-test/suite/galera/include/kill_galera.inc @@ -2,7 +2,7 @@ if (!$kill_signal) { ---let $kill_signal = 9 +--let $kill_signal = KILL } # Write file to make mysql-test-run.pl expect the crash, but don't start it diff --git a/mysql-test/suite/galera/r/MDEV-24143.result b/mysql-test/suite/galera/r/MDEV-24143.result new file mode 100644 index 00000000000..860d8a35834 --- /dev/null +++ b/mysql-test/suite/galera/r/MDEV-24143.result @@ -0,0 +1,23 @@ +connection node_2; +connection node_1; +CREATE TABLE t1 (c1 BIGINT NOT NULL PRIMARY KEY, c2 BINARY (10), c3 DATETIME); +SELECT get_lock ('test2', 0); +get_lock ('test2', 0) +1 +DROP TABLE t1; +CREATE TABLE t1 (c1 SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY); +INSERT INTO t1 VALUES (1); +SET SESSION wsrep_trx_fragment_size=10; +SET SESSION autocommit=0; +SELECT * FROM t1 WHERE c1 <=0 ORDER BY c1 DESC; +c1 +INSERT INTO t1 VALUES (4),(3),(1),(2); +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +CREATE TABLE t1 (pk INT PRIMARY KEY, b INT) ENGINE=SEQUENCE; +ERROR 42S01: Table 't1' already exists +ALTER TABLE t1 DROP COLUMN c2; +ERROR 42000: Can't DROP COLUMN `c2`; check that it exists +SELECT get_lock ('test', 1.5); +get_lock ('test', 1.5) +1 +DROP TABLE t1; diff --git a/mysql-test/suite/galera/r/MDEV-27713.result b/mysql-test/suite/galera/r/MDEV-27713.result new file mode 100644 index 00000000000..14575cb484d --- /dev/null +++ b/mysql-test/suite/galera/r/MDEV-27713.result @@ -0,0 +1,46 @@ +connection node_2; +connection node_1; +CREATE TABLE t1 ( +f1 INT, +f2 VARCHAR(255) PRIMARY KEY +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +INSERT INTO t1 VALUES(1, 'abc'); +connection node_1; +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES (2,'def'); +connection node_2; +SET GLOBAL event_scheduler=ON; +CREATE PROCEDURE update_table() +BEGIN +SET AUTOCOMMIT=OFF; +DO GET_LOCK('local_lock', 0); +SET DEBUG_SYNC = 'innodb_row_update_for_mysql_begin SIGNAL blocked WAIT_FOR continue'; +UPDATE t1 SET f2 = 'jkl' WHERE f1 != 2; +DO RELEASE_LOCK('local_lock'); +END| +CREATE DEFINER=current_user +EVENT event +ON SCHEDULE AT CURRENT_TIMESTAMP +ON COMPLETION PRESERVE +ENABLE +DO CALL update_table(); +connect node_2a, 127.0.0.1, root, , test, $NODE_MYPORT_2; +SET DEBUG_SYNC = 'now WAIT_FOR blocked'; +connect node_2b, 127.0.0.1, root, , test, $NODE_MYPORT_2; +SET GLOBAL debug_dbug = "+d,sync.wsrep_apply_cb"; +connection node_1; +COMMIT; +connection node_2b; +SET DEBUG_SYNC = "now WAIT_FOR sync.wsrep_apply_cb_reached"; +SET DEBUG_SYNC = "now SIGNAL signal.wsrep_apply_cb"; +connection node_2a; +SET DEBUG_SYNC = 'now SIGNAL continue'; +connection node_2; +SET GLOBAL event_scheduler=default; +DROP PROCEDURE update_table; +DROP EVENT event; +SET DEBUG_SYNC='reset'; +SET GLOBAL debug_dbug = DEFAULT; +connection node_1; +DROP TABLE t1; diff --git a/mysql-test/suite/galera/r/galera_bf_abort_ps_bind.result b/mysql-test/suite/galera/r/galera_bf_abort_ps_bind.result new file mode 100644 index 00000000000..adc7da58eae --- /dev/null +++ b/mysql-test/suite/galera/r/galera_bf_abort_ps_bind.result @@ -0,0 +1,37 @@ +connection node_2; +connection node_1; +CREATE TABLE t (i int primary key auto_increment, j varchar(20) character set utf8); +connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1; +connection node_1a; +SET SESSION wsrep_sync_wait = 0; +connection node_1; +insert into t values (1, 'first'); +PS_prepare INSERT INTO t(j) VALUES (?);; +PS_bind node1; +PS_execute; +PS_execute; +select * from t; +i j +1 first +3 node1 +5 node1 +PS_close; +PS_prepare INSERT INTO t(j) VALUES (?);; +PS_bind node1; +begin; +update t set j='node1' where i=1; +connection node_2; +update t set j='node2' where i=1; +connection node_1a; +connection node_1; +PS_execute; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +PS_execute; +commit; +select * from t; +i j +1 node2 +3 node1 +5 node1 +7 node1 +drop table t; diff --git a/mysql-test/suite/galera/r/galera_ist_restart_joiner.result b/mysql-test/suite/galera/r/galera_ist_restart_joiner.result index 7cb6d90840e..2f78c475762 100644 --- a/mysql-test/suite/galera/r/galera_ist_restart_joiner.result +++ b/mysql-test/suite/galera/r/galera_ist_restart_joiner.result @@ -18,6 +18,7 @@ SET SESSION wsrep_on=ON; connection node_1; UPDATE t1 SET f2 = 'd' WHERE f1 > 3; connection node_2; +Killing server ... connection node_1; UPDATE t1 SET f2 = 'e' WHERE f1 > 4; connection node_2; diff --git a/mysql-test/suite/galera/t/MDEV-24143.test b/mysql-test/suite/galera/t/MDEV-24143.test new file mode 100644 index 00000000000..e58f147cb7c --- /dev/null +++ b/mysql-test/suite/galera/t/MDEV-24143.test @@ -0,0 +1,20 @@ +--source include/galera_cluster.inc +--source include/have_sequence.inc + +CREATE TABLE t1 (c1 BIGINT NOT NULL PRIMARY KEY, c2 BINARY (10), c3 DATETIME); +SELECT get_lock ('test2', 0); +DROP TABLE t1; +CREATE TABLE t1 (c1 SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY); +INSERT INTO t1 VALUES (1); +SET SESSION wsrep_trx_fragment_size=10; +SET SESSION autocommit=0; +SELECT * FROM t1 WHERE c1 <=0 ORDER BY c1 DESC; +--error ER_LOCK_DEADLOCK +INSERT INTO t1 VALUES (4),(3),(1),(2); +--error ER_TABLE_EXISTS_ERROR +CREATE TABLE t1 (pk INT PRIMARY KEY, b INT) ENGINE=SEQUENCE; +--error ER_CANT_DROP_FIELD_OR_KEY +ALTER TABLE t1 DROP COLUMN c2; +SELECT get_lock ('test', 1.5); +DROP TABLE t1; + diff --git a/mysql-test/suite/galera/t/MDEV-27713.test b/mysql-test/suite/galera/t/MDEV-27713.test new file mode 100644 index 00000000000..4bfcd7e3d50 --- /dev/null +++ b/mysql-test/suite/galera/t/MDEV-27713.test @@ -0,0 +1,67 @@ +--source include/galera_cluster.inc +--source include/have_innodb.inc +--source include/have_debug.inc +--source include/have_debug_sync.inc +--source include/big_test.inc + +CREATE TABLE t1 ( + f1 INT, + f2 VARCHAR(255) PRIMARY KEY +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +INSERT INTO t1 VALUES(1, 'abc'); + +--connection node_1 +SET AUTOCOMMIT=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES (2,'def'); + +--connection node_2 + +SET GLOBAL event_scheduler=ON; + +DELIMITER |; +CREATE PROCEDURE update_table() +BEGIN + SET AUTOCOMMIT=OFF; + DO GET_LOCK('local_lock', 0); + SET DEBUG_SYNC = 'innodb_row_update_for_mysql_begin SIGNAL blocked WAIT_FOR continue'; + UPDATE t1 SET f2 = 'jkl' WHERE f1 != 2; + DO RELEASE_LOCK('local_lock'); +END| +DELIMITER ;| + +CREATE DEFINER=current_user + EVENT event + ON SCHEDULE AT CURRENT_TIMESTAMP + ON COMPLETION PRESERVE + ENABLE + DO CALL update_table(); + +--connect node_2a, 127.0.0.1, root, , test, $NODE_MYPORT_2 +SET DEBUG_SYNC = 'now WAIT_FOR blocked'; + +# Applier control thread +--connect node_2b, 127.0.0.1, root, , test, $NODE_MYPORT_2 +SET GLOBAL debug_dbug = "+d,sync.wsrep_apply_cb"; + +--connection node_1 +COMMIT; + +# Applier control thread +--connection node_2b +SET DEBUG_SYNC = "now WAIT_FOR sync.wsrep_apply_cb_reached"; +SET DEBUG_SYNC = "now SIGNAL signal.wsrep_apply_cb"; + +--connection node_2a +SET DEBUG_SYNC = 'now SIGNAL continue'; + +--connection node_2 +SET GLOBAL event_scheduler=default; +DROP PROCEDURE update_table; +DROP EVENT event; +SET DEBUG_SYNC='reset'; +SET GLOBAL debug_dbug = DEFAULT; + +--connection node_1 +DROP TABLE t1; diff --git a/mysql-test/suite/galera/t/MW-44.test b/mysql-test/suite/galera/t/MW-44.test index a2acfc57f6c..7b479d45844 100644 --- a/mysql-test/suite/galera/t/MW-44.test +++ b/mysql-test/suite/galera/t/MW-44.test @@ -19,7 +19,11 @@ SET SESSION wsrep_osu_method=RSU; ALTER TABLE t1 ADD COLUMN f2 INTEGER; SET SESSION wsrep_osu_method=TOI; ---let $wait_condition = SELECT COUNT(*) = 2 FROM mysql.general_log WHERE argument LIKE "CREATE%" OR argument LIKE "ALTER%" +--let $wait_condition = SELECT COUNT(*) = 1 FROM mysql.general_log WHERE argument LIKE "CREATE%" AND command_type != 'Prepare' +--let $wait_condition_on_error_output = SELECT * FROM mysql.general_log +--source include/wait_condition_with_debug.inc + +--let $wait_condition = SELECT COUNT(*) = 1 FROM mysql.general_log WHERE argument LIKE "ALTER%" AND command_type != 'Prepare' --let $wait_condition_on_error_output = SELECT * FROM mysql.general_log --source include/wait_condition_with_debug.inc diff --git a/mysql-test/suite/galera/t/galera_bf_abort_ps_bind.cnf b/mysql-test/suite/galera/t/galera_bf_abort_ps_bind.cnf new file mode 100644 index 00000000000..62cf1854032 --- /dev/null +++ b/mysql-test/suite/galera/t/galera_bf_abort_ps_bind.cnf @@ -0,0 +1,7 @@ +!include ../galera_2nodes.cnf + +[mysqld.1] +wsrep-debug=1 + +[mysqld.2] +wsrep-debug=1 diff --git a/mysql-test/suite/galera/t/galera_bf_abort_ps_bind.test b/mysql-test/suite/galera/t/galera_bf_abort_ps_bind.test new file mode 100644 index 00000000000..a840f612a82 --- /dev/null +++ b/mysql-test/suite/galera/t/galera_bf_abort_ps_bind.test @@ -0,0 +1,58 @@ +--source include/galera_cluster.inc +--source include/have_innodb.inc + +CREATE TABLE t (i int primary key auto_increment, j varchar(20) character set utf8); + +--connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1 +--connection node_1a +SET SESSION wsrep_sync_wait = 0; + +--connection node_1 +insert into t values (1, 'first'); + +# prepare a statement for inserting rows into table t +--PS_prepare INSERT INTO t(j) VALUES (?); + +# bind parameter, to insert with column j having value 'node1' +--PS_bind node1 + +# insert two rows with the PS +# this is for showing that two execute commands can follow a bind command +--PS_execute +--PS_execute +select * from t; + +# close the prepared statement, and prepare a new PS, +# this happens to be same as the first PS +# also bind parameter for the PS +--PS_close +--PS_prepare INSERT INTO t(j) VALUES (?); +--PS_bind node1 + +# start a transaction and make one update +# leaving the transaction open +begin; +update t set j='node1' where i=1; + +# replicate a transaction from node2, which BF aborts the open +# transaction in node1 +--connection node_2 +update t set j='node2' where i=1; + +# wait until the BF has completed, and update from node_2 has committed +--connection node_1a +--let $wait_condition = SELECT COUNT(*) = 1 FROM t WHERE j='node2' +--source include/wait_condition.inc + +# continue the open transaction, trying to insert third row, deadlock is now observed +--connection node_1 +--error ER_LOCK_DEADLOCK +--PS_execute + +# try to insert one more row +--PS_execute +commit; + +select * from t; + +drop table t; diff --git a/mysql-test/suite/galera/t/galera_ist_restart_joiner.test b/mysql-test/suite/galera/t/galera_ist_restart_joiner.test index f56d0e657bd..c535ac455b9 100644 --- a/mysql-test/suite/galera/t/galera_ist_restart_joiner.test +++ b/mysql-test/suite/galera/t/galera_ist_restart_joiner.test @@ -61,19 +61,7 @@ UPDATE t1 SET f2 = 'd' WHERE f1 > 3; # Kill node #2 while IST is in progress --connection node_2 - -# Kill the connected server ---disable_reconnect - ---perl - my $pid_filename = $ENV{'KILL_NODE_PIDFILE'}; - my $mysqld_pid = `cat $pid_filename`; - chomp($mysqld_pid); - system("kill -9 $mysqld_pid"); - exit(0); -EOF - ---source include/wait_until_disconnected.inc +--source include/kill_galera.inc --connection node_1 --source include/wait_until_connected_again.inc diff --git a/mysql-test/suite/galera_3nodes/r/galera_garbd_backup.result b/mysql-test/suite/galera_3nodes/r/galera_garbd_backup.result new file mode 100644 index 00000000000..f176ef1dd7f --- /dev/null +++ b/mysql-test/suite/galera_3nodes/r/galera_garbd_backup.result @@ -0,0 +1,41 @@ +connection node_1; +connection node_1; +connection node_2; +connection node_3; +connection node_1; +SET GLOBAL innodb_max_dirty_pages_pct=99; +SET GLOBAL innodb_max_dirty_pages_pct_lwm=99; +connection node_1; +CREATE TABLE t1 (f1 INTEGER, f2 varchar(1024)) Engine=InnoDB; +CREATE TABLE ten (f1 INTEGER) ENGINE=InnoDB; +INSERT INTO ten VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); +INSERT INTO t1 (f2) SELECT REPEAT('x', 1024) FROM ten AS a1, ten AS a2, ten AS a3, ten AS a4; +connection node_2; +Killing node #3 to free ports for garbd ... +connection node_3; +connection node_1; +SET GLOBAL debug_dbug = "+d,sync.wsrep_donor_state"; +Starting garbd ... +SET SESSION debug_sync = "now WAIT_FOR sync.wsrep_donor_state_reached"; +SET GLOBAL innodb_max_dirty_pages_pct_lwm=0; +SET GLOBAL innodb_max_dirty_pages_pct=0; +SET SESSION debug_sync = "now SIGNAL signal.wsrep_donor_state"; +SET GLOBAL debug_dbug = ""; +SET debug_sync='RESET'; +connection node_2; +Killing garbd ... +connection node_1; +connection node_2; +DROP TABLE t1; +DROP TABLE ten; +Restarting node #3 to satisfy MTR's end-of-test checks +connection node_3; +connection node_1; +SET GLOBAL innodb_max_dirty_pages_pct = 75.000000; +SET GLOBAL innodb_max_dirty_pages_pct_lwm = 0.000000; +connection node_1; +CALL mtr.add_suppression("WSREP: Protocol violation\. JOIN message sender 1\.0 \(.*\) is not in state transfer \(SYNCED\)"); +connection node_2; +CALL mtr.add_suppression("WSREP: Protocol violation\. JOIN message sender 1\.0 \(.*\) is not in state transfer \(SYNCED\)"); +connection node_3; +CALL mtr.add_suppression("WSREP: Protocol violation\. JOIN message sender 1\.0 \(.*\) is not in state transfer \(SYNCED\)"); diff --git a/mysql-test/suite/galera_3nodes/t/galera_garbd_backup.cnf b/mysql-test/suite/galera_3nodes/t/galera_garbd_backup.cnf new file mode 100644 index 00000000000..8b7cb948a87 --- /dev/null +++ b/mysql-test/suite/galera_3nodes/t/galera_garbd_backup.cnf @@ -0,0 +1,13 @@ +!include ../galera_3nodes.cnf + +[mysqld] +wsrep_sst_method=rsync + +[mysqld.1] +wsrep_node_name=node1 + +[mysqld.2] +wsrep_node_name=node2 + +[mysqld.3] +wsrep_node_name=node3 diff --git a/mysql-test/suite/galera_3nodes/t/galera_garbd_backup.test b/mysql-test/suite/galera_3nodes/t/galera_garbd_backup.test new file mode 100644 index 00000000000..302bf430dde --- /dev/null +++ b/mysql-test/suite/galera_3nodes/t/galera_garbd_backup.test @@ -0,0 +1,134 @@ +# +# A very basic test for the galera arbitrator. We shut down node #3 and use its port allocation to start garbd. +# As MTR does not allow multiple servers to be down at the same time, we are limited as to what we can test. +# + +--source include/galera_cluster.inc +--source include/have_innodb.inc +--source include/have_garbd.inc +--source include/big_test.inc +--source include/have_debug.inc +--source include/have_debug_sync.inc + +--connection node_1 +# Save original auto_increment_offset values. +--let $node_1=node_1 +--let $node_2=node_2 +--let $node_3=node_3 + +--let $galera_connection_name = node_3 +--let $galera_server_number = 3 +--source include/galera_connect.inc +--source suite/galera/include/galera_base_port.inc +--let $NODE_GALERAPORT_3 = $_NODE_GALERAPORT + +--source ../galera/include/auto_increment_offset_save.inc + +# Save galera ports +--connection node_1 +--source suite/galera/include/galera_base_port.inc +--let $NODE_GALERAPORT_1 = $_NODE_GALERAPORT +--let $datadir= `SELECT @@datadir` + +--let $innodb_max_dirty_pages_pct = `SELECT @@innodb_max_dirty_pages_pct` +--let $innodb_max_dirty_pages_pct_lwm = `SELECT @@innodb_max_dirty_pages_pct_lwm` + +SET GLOBAL innodb_max_dirty_pages_pct=99; +SET GLOBAL innodb_max_dirty_pages_pct_lwm=99; + +--connection node_1 +CREATE TABLE t1 (f1 INTEGER, f2 varchar(1024)) Engine=InnoDB; +CREATE TABLE ten (f1 INTEGER) ENGINE=InnoDB; +INSERT INTO ten VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); +INSERT INTO t1 (f2) SELECT REPEAT('x', 1024) FROM ten AS a1, ten AS a2, ten AS a3, ten AS a4; + +--connection node_2 +--source suite/galera/include/galera_base_port.inc +--let $NODE_GALERAPORT_2 = $_NODE_GALERAPORT + +--echo Killing node #3 to free ports for garbd ... +--connection node_3 +--source include/shutdown_mysqld.inc + +--connection node_1 +--let $wait_condition = SELECT VARIABLE_VALUE = 2 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_size' +--source include/wait_condition.inc + +# stop SST donor thread when node is in donor state +SET GLOBAL debug_dbug = "+d,sync.wsrep_donor_state"; + +--echo Starting garbd ... +--exec $MTR_GARBD_EXE --address "gcomm://127.0.0.1:$NODE_GALERAPORT_1" --group my_wsrep_cluster --donor node1 --sst backup --options 'base_port=$NODE_GALERAPORT_3' > $MYSQL_TMP_DIR/garbd.log 2>&1 & + +SET SESSION debug_sync = "now WAIT_FOR sync.wsrep_donor_state_reached"; + +# +# get hash of data directory contents before BP dirty page flushing +# +--exec find $datadir -type f ! -name tables_flushed ! -name backup_sst_complete -exec md5sum {} \; | md5sum >$MYSQLTEST_VARDIR/tmp/innodb_before + +# this should force buffer pool flushing, if not already done by donor state change transfer +SET GLOBAL innodb_max_dirty_pages_pct_lwm=0; +SET GLOBAL innodb_max_dirty_pages_pct=0; + +--disable_query_log +--disable_result_log +select f1 from t1; +select * from ten; +--enable_result_log +--enable_query_log + +# +# +# record the hash of data directory contents after BP dirty page flushing +# +--exec find $datadir -type f ! -name tables_flushed ! -name backup_sst_complete -exec md5sum {} \; | md5sum >$MYSQLTEST_VARDIR/tmp/innodb_after + +# there should be no disk writes +--diff_files $MYSQLTEST_VARDIR/tmp/innodb_before $MYSQLTEST_VARDIR/tmp/innodb_after + +SET SESSION debug_sync = "now SIGNAL signal.wsrep_donor_state"; +SET GLOBAL debug_dbug = ""; +SET debug_sync='RESET'; + +--connection node_2 + +# +# garbd will die automatically, because of the backup SST script +# but just to be sure, sending explicit kill here, as well +# +--echo Killing garbd ... +# FreeBSD's /bin/pkill only supports short versions of the options: +# -o Select only the oldest (least recently started) +# -f Match against full argument lists +--error 0,1 +--exec pkill -o -f garbd.*$NODE_GALERAPORT_3 + +--connection node_1 +--let $wait_condition = SELECT VARIABLE_VALUE = 2 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_size' +--source include/wait_condition.inc + +--connection node_2 + +DROP TABLE t1; +DROP TABLE ten; + +--echo Restarting node #3 to satisfy MTR's end-of-test checks +--connection node_3 +let $restart_noprint=2; +--source include/start_mysqld.inc + +--connection node_1 +--eval SET GLOBAL innodb_max_dirty_pages_pct = $innodb_max_dirty_pages_pct +--eval SET GLOBAL innodb_max_dirty_pages_pct_lwm = $innodb_max_dirty_pages_pct_lwm + +--source ../galera/include/auto_increment_offset_restore.inc + +--connection node_1 +CALL mtr.add_suppression("WSREP: Protocol violation\. JOIN message sender 1\.0 \(.*\) is not in state transfer \(SYNCED\)"); + +--connection node_2 +CALL mtr.add_suppression("WSREP: Protocol violation\. JOIN message sender 1\.0 \(.*\) is not in state transfer \(SYNCED\)"); + +--connection node_3 +CALL mtr.add_suppression("WSREP: Protocol violation\. JOIN message sender 1\.0 \(.*\) is not in state transfer \(SYNCED\)"); diff --git a/mysql-test/suite/galera_sr/disabled.def b/mysql-test/suite/galera_sr/disabled.def index 9f6ae2a51ef..e45118f6f0a 100644 --- a/mysql-test/suite/galera_sr/disabled.def +++ b/mysql-test/suite/galera_sr/disabled.def @@ -14,4 +14,5 @@ GCF-1018B : MDEV-18534 wsrep::transaction::adopt(): Assertion `transaction.is_st GCF-1060 : MDEV-20848 galera_sr.GCF_1060 GCF-585 : MDEV-24698 galera_sr.GCF-585 MTR failed with SIGABRT: no such a transition REPLICATING -> APPLYING galera-features#56 : MDEV-24896 +GCF-1060 : MDEV-26528 wrong usage of mutex LOCK_thd_kill and LOCK_thd_kill galera_sr_shutdown_master : MDEV-23612: galera_sr.galera_sr_shutdown_master MTR failed: WSREP_SST: [ERROR] Possible timeout in receving first data from donor in gtid stage diff --git a/mysql-test/suite/galera_sr/r/MDEV-27553.result b/mysql-test/suite/galera_sr/r/MDEV-27553.result index f6f81bd13f1..5a6a5bd4956 100644 --- a/mysql-test/suite/galera_sr/r/MDEV-27553.result +++ b/mysql-test/suite/galera_sr/r/MDEV-27553.result @@ -1,23 +1,36 @@ connection node_2; connection node_1; -CREATE TABLE t1 (f1 INTEGER PRIMARY KEY); connection node_1; +connection node_2; +connect node_2a, 127.0.0.1, root, , test, $NODE_MYPORT_2; +CREATE TABLE t1 (f1 INTEGER PRIMARY KEY); +connection node_2; SET SESSION wsrep_trx_fragment_size=1; START TRANSACTION; INSERT INTO t1 VALUES (1); +SELECT COUNT(*) `Expect 1` FROM mysql.wsrep_streaming_log; +Expect 1 +1 SET @@global.debug_dbug="+d,ha_index_init_fail"; ROLLBACK; -connection node_2; +connection node_1; +SET SESSION wsrep_sync_wait = 0; SELECT COUNT(*) `Expect 0` FROM mysql.wsrep_streaming_log; Expect 0 0 -connection node_1; +connection node_2; SET @@global.debug_dbug=""; +SET SESSION wsrep_sync_wait = 0; SELECT COUNT(*) `Expect 1` FROM mysql.wsrep_streaming_log; Expect 1 1 -SET SESSION wsrep_on=OFF; -DELETE FROM mysql.wsrep_streaming_log; -SET SESSION wsrep_on=ON; +connection node_2; +SET GLOBAL wsrep_on=OFF; +# restart +SELECT COUNT(*) `Expect 0` FROM mysql.wsrep_streaming_log; +Expect 0 +0 DROP TABLE t1; CALL mtr.add_suppression("WSREP: Failed to init table for index scan"); +CALL mtr.add_suppression("WSREP: Failed to apply write set"); +CALL mtr.add_suppression("Failed to report last committed"); diff --git a/mysql-test/suite/galera_sr/t/MDEV-27553.test b/mysql-test/suite/galera_sr/t/MDEV-27553.test index d17af175512..5c557db9201 100644 --- a/mysql-test/suite/galera_sr/t/MDEV-27553.test +++ b/mysql-test/suite/galera_sr/t/MDEV-27553.test @@ -5,29 +5,76 @@ --source include/galera_cluster.inc --source include/have_debug.inc +--let $node_1=node_1 +--let $node_2=node_2 +--source suite/galera/include/auto_increment_offset_save.inc + +--connect node_2a, 127.0.0.1, root, , test, $NODE_MYPORT_2 + CREATE TABLE t1 (f1 INTEGER PRIMARY KEY); ---connection node_1 ---let $wsrep_cluster_address_orig = `SELECT @@wsrep_cluster_address` +--connection node_2 SET SESSION wsrep_trx_fragment_size=1; START TRANSACTION; INSERT INTO t1 VALUES (1); -# This will result in failure to remove fragments -# from streaming log, in the following ROLLBACK. +SELECT COUNT(*) `Expect 1` FROM mysql.wsrep_streaming_log; + +# +# Issue ROLLBACK and make sure it fails to clean up +# the streaming log. Failure to remove fragments +# results in apply failure of the rollback fragment. +# The node should disconnect from the cluster. +# SET @@global.debug_dbug="+d,ha_index_init_fail"; ROLLBACK; ---connection node_2 + +# +# Expect the cluster to shrink +# +--connection node_1 +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 + +# +# ROLLBACK should clean up the streaming log just fine in node 1 +# SELECT COUNT(*) `Expect 0` FROM mysql.wsrep_streaming_log; ---connection node_1 +# +# Expect the failure on ROLLBACK to leave a entry in streaming log +# +--connection node_2 SET @@global.debug_dbug=""; +SET SESSION wsrep_sync_wait = 0; +# Expect node to be disconnected +--let wait_condition = SELECT VARIABLE_VALUE = 'Disconnected' FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_status'; +--source include/wait_condition.inc + SELECT COUNT(*) `Expect 1` FROM mysql.wsrep_streaming_log; -SET SESSION wsrep_on=OFF; -DELETE FROM mysql.wsrep_streaming_log; -SET SESSION wsrep_on=ON; + +# +# Restart node 2, so that it joins the cluster back +# +--connection node_2 +SET GLOBAL wsrep_on=OFF; +--source include/restart_mysqld.inc + +# +# After restart, the streaming log is empty in node 2 +# +SELECT COUNT(*) `Expect 0` FROM mysql.wsrep_streaming_log; + +# +# Cleanup +# DROP TABLE t1; CALL mtr.add_suppression("WSREP: Failed to init table for index scan"); +CALL mtr.add_suppression("WSREP: Failed to apply write set"); +CALL mtr.add_suppression("Failed to report last committed"); + +--source suite/galera/include/auto_increment_offset_restore.inc diff --git a/mysql-test/suite/innodb/r/row_format_redundant.result b/mysql-test/suite/innodb/r/row_format_redundant.result index 8a629d06dd8..b798832e96f 100644 --- a/mysql-test/suite/innodb/r/row_format_redundant.result +++ b/mysql-test/suite/innodb/r/row_format_redundant.result @@ -1,3 +1,4 @@ +SET GLOBAL innodb_fast_shutdown=0; # restart: --innodb-data-home-dir=MYSQLTEST_VARDIR/tmp/row_format_redundant --innodb-log-group-home-dir=MYSQLTEST_VARDIR/tmp/row_format_redundant --innodb-data-file-path=ibdata1:1M:autoextend --innodb-undo-tablespaces=0 --innodb-stats-persistent=0 SET GLOBAL innodb_file_per_table=1; # @@ -8,25 +9,17 @@ SET GLOBAL innodb_file_per_table=ON; create table t1 (a int not null, d varchar(15) not null, b varchar(198) not null, c char(156)) engine=InnoDB row_format=redundant; -insert into t1 values(123, 'abcdef', 'jghikl', 'mnop'); -insert into t1 values(456, 'abcdef', 'jghikl', 'mnop'); -insert into t1 values(789, 'abcdef', 'jghikl', 'mnop'); -insert into t1 values(134, 'kasdfsdsadf', 'adfjlasdkfjasd', 'adfsadflkasdasdfljasdf'); -insert into t1 select * from t1; -insert into t1 select * from t1; -insert into t1 select * from t1; -insert into t1 select * from t1; -insert into t1 select * from t1; -insert into t1 select * from t1; -insert into t1 select * from t1; -insert into t1 select * from t1; -insert into t1 select * from t1; -insert into t1 select * from t1; +create temporary table t like t1; +insert into t values(123, 'abcdef', 'jghikl', 'mnop'); +insert into t values(456, 'abcdef', 'jghikl', 'mnop'); +insert into t values(789, 'abcdef', 'jghikl', 'mnop'); +insert into t values(134, 'kasdfsdsadf', 'adfjlasdkfjasd', 'adfsadflkasdasdfljasdf'); +insert into t1 select a,d,b,c from t, seq_1_to_1024; SET GLOBAL innodb_file_per_table=OFF; create table t2 (a int not null, d varchar(15) not null, b varchar(198) not null, c char(156), fulltext ftsic(c)) engine=InnoDB row_format=redundant; -insert into t2 select * from t1; +insert into t2 select a,d,b,c from t, seq_1_to_1024; create table t3 (a int not null, d varchar(15) not null, b varchar(198), c varchar(150), index k1(c(99), b(56)), index k2(b(5), c(10))) engine=InnoDB row_format=redundant; diff --git a/mysql-test/suite/innodb/t/alter_crash_rebuild.test b/mysql-test/suite/innodb/t/alter_crash_rebuild.test new file mode 100644 index 00000000000..500cd28e5c5 --- /dev/null +++ b/mysql-test/suite/innodb/t/alter_crash_rebuild.test @@ -0,0 +1,26 @@ +--source include/have_innodb.inc +--source include/have_debug.inc +--source include/have_debug_sync.inc + +CREATE TABLE t1 (a INT NOT NULL) ENGINE=InnoDB STATS_PERSISTENT=0; + +connect ddl,localhost,root; +SET DEBUG_SYNC='after_trx_committed_in_memory SIGNAL stuck WAIT_FOR ever EXECUTE 2'; +send ALTER TABLE t1 ADD PRIMARY KEY(a); + +connection default; +SET DEBUG_SYNC='now WAIT_FOR stuck'; +SET DEBUG_SYNC='now SIGNAL ever'; +SET DEBUG_SYNC='now WAIT_FOR stuck'; + +SET GLOBAL innodb_log_checkpoint_now=ON; + +--let $shutdown_timeout=0 +--source include/restart_mysqld.inc + +disconnect ddl; + +SHOW CREATE TABLE t1; +SELECT * FROM t1; +DROP TABLE t1; +--source include/wait_all_purged.inc diff --git a/mysql-test/suite/innodb/t/row_format_redundant.test b/mysql-test/suite/innodb/t/row_format_redundant.test index aadbfd2b773..6de7597e983 100644 --- a/mysql-test/suite/innodb/t/row_format_redundant.test +++ b/mysql-test/suite/innodb/t/row_format_redundant.test @@ -1,6 +1,7 @@ --source include/have_innodb.inc # Embedded mode doesn't allow restarting --source include/not_embedded.inc +--source include/have_sequence.inc --disable_query_log call mtr.add_suppression("InnoDB: Table `mysql`\\.`innodb_table_stats` not found"); @@ -21,6 +22,8 @@ let bugdir= $MYSQLTEST_VARDIR/tmp/row_format_redundant; --let $d=$d --innodb-data-file-path=ibdata1:1M:autoextend --let $d=$d --innodb-undo-tablespaces=0 --innodb-stats-persistent=0 --let $restart_parameters= $d +# Ensure that any DDL records from previous tests have been purged. +SET GLOBAL innodb_fast_shutdown=0; --source include/restart_mysqld.inc SET GLOBAL innodb_file_per_table=1; @@ -35,27 +38,21 @@ create table t1 (a int not null, d varchar(15) not null, b varchar(198) not null, c char(156)) engine=InnoDB row_format=redundant; -insert into t1 values(123, 'abcdef', 'jghikl', 'mnop'); -insert into t1 values(456, 'abcdef', 'jghikl', 'mnop'); -insert into t1 values(789, 'abcdef', 'jghikl', 'mnop'); -insert into t1 values(134, 'kasdfsdsadf', 'adfjlasdkfjasd', 'adfsadflkasdasdfljasdf'); -insert into t1 select * from t1; -insert into t1 select * from t1; -insert into t1 select * from t1; -insert into t1 select * from t1; -insert into t1 select * from t1; -insert into t1 select * from t1; -insert into t1 select * from t1; -insert into t1 select * from t1; -insert into t1 select * from t1; -insert into t1 select * from t1; +create temporary table t like t1; + +insert into t values(123, 'abcdef', 'jghikl', 'mnop'); +insert into t values(456, 'abcdef', 'jghikl', 'mnop'); +insert into t values(789, 'abcdef', 'jghikl', 'mnop'); +insert into t values(134, 'kasdfsdsadf', 'adfjlasdkfjasd', 'adfsadflkasdasdfljasdf'); + +insert into t1 select a,d,b,c from t, seq_1_to_1024; SET GLOBAL innodb_file_per_table=OFF; create table t2 (a int not null, d varchar(15) not null, b varchar(198) not null, c char(156), fulltext ftsic(c)) engine=InnoDB row_format=redundant; -insert into t2 select * from t1; +insert into t2 select a,d,b,c from t, seq_1_to_1024; create table t3 (a int not null, d varchar(15) not null, b varchar(198), c varchar(150), index k1(c(99), b(56)), index k2(b(5), c(10))) engine=InnoDB diff --git a/mysql-test/suite/innodb_zip/r/innochecksum.result b/mysql-test/suite/innodb_zip/r/innochecksum.result index e6525af4b52..bb94de7a369 100644 --- a/mysql-test/suite/innodb_zip/r/innochecksum.result +++ b/mysql-test/suite/innodb_zip/r/innochecksum.result @@ -14,7 +14,8 @@ FOUND 1 /Error: --no-check must be associated with --write option./ in my_restar FOUND 1 /unknown variable 'strict-check=innodb'/ in my_restart.err [7]: check the innochecksum with short form strict-check & no-check , an error is expected FOUND 1 /unknown option '-C'/ in my_restart.err -FOUND 1 /unknown variable 'write=crc32'/ in my_restart.err +FOUND 1 /ignoring option '--write' due to invalid value 'crc32'/ in my_restart.err +FOUND 1 /Error: --no-check must be associated with --write option/ in my_restart.err # restart SELECT * FROM tab1; c1 c2 diff --git a/mysql-test/suite/innodb_zip/r/innochecksum_2.result b/mysql-test/suite/innodb_zip/r/innochecksum_2.result index 7be6f6f9af3..681d8e1f4c7 100644 --- a/mysql-test/suite/innodb_zip/r/innochecksum_2.result +++ b/mysql-test/suite/innodb_zip/r/innochecksum_2.result @@ -27,6 +27,7 @@ end-page 0 page 0 no-check FALSE allow-mismatches 0 +write FALSE page-type-summary FALSE page-type-dump MYSQLTEST_VARDIR/tmp/dump.txt per-page-details FALSE @@ -54,6 +55,7 @@ See https://mariadb.com/kb/en/library/innochecksum/ for usage hints. -n, --no-check Ignore the checksum verification. -a, --allow-mismatches=# Maximum checksum mismatch allowed. + -w, --write Rewrite the checksum. -S, --page-type-summary Display a count of each page type in a tablespace. -D, --page-type-dump=name @@ -75,6 +77,7 @@ end-page 0 page 0 no-check FALSE allow-mismatches 0 +write FALSE page-type-summary FALSE page-type-dump (No default value) per-page-details FALSE diff --git a/mysql-test/suite/innodb_zip/r/innochecksum_3.result b/mysql-test/suite/innodb_zip/r/innochecksum_3.result index 280528f4200..03a31194c63 100644 --- a/mysql-test/suite/innodb_zip/r/innochecksum_3.result +++ b/mysql-test/suite/innodb_zip/r/innochecksum_3.result @@ -133,6 +133,7 @@ end-page 0 page 0 no-check FALSE allow-mismatches 0 +write FALSE page-type-summary FALSE page-type-dump MYSQLTEST_VARDIR/tmp/dump.txt per-page-details FALSE diff --git a/mysql-test/suite/innodb_zip/t/innochecksum.test b/mysql-test/suite/innodb_zip/t/innochecksum.test index 60860adeac8..b78cd4329de 100644 --- a/mysql-test/suite/innodb_zip/t/innochecksum.test +++ b/mysql-test/suite/innodb_zip/t/innochecksum.test @@ -57,9 +57,15 @@ let SEARCH_PATTERN= unknown option '-C'; --error 1 --exec $INNOCHECKSUM --no-check --write=crc32 $MYSQLD_DATADIR/test/tab1.ibd 2> $SEARCH_FILE -let SEARCH_PATTERN= unknown variable 'write=crc32'; +--let SEARCH_PATTERN= ignoring option '--write' due to invalid value 'crc32' --source include/search_pattern_in_file.inc +--error 1 +--exec $INNOCHECKSUM --no-check $MYSQLD_DATADIR/test/tab1.ibd 2> $SEARCH_FILE +--let SEARCH_PATTERN= Error: --no-check must be associated with --write option +--source include/search_pattern_in_file.inc + +--exec $INNOCHECKSUM --no-check --write $MYSQLD_DATADIR/test/tab1.ibd --source include/start_mysqld.inc SELECT * FROM tab1; diff --git a/mysql-test/suite/rpl/r/mdev_24667.result b/mysql-test/suite/rpl/r/mdev_24667.result new file mode 100644 index 00000000000..7c7342d63d6 --- /dev/null +++ b/mysql-test/suite/rpl/r/mdev_24667.result @@ -0,0 +1,30 @@ +include/rpl_init.inc [topology=1->2->3] +call mtr.add_suppression('Unsafe statement written to the binary log using '); +connection server_1; +set binlog_format=statement; +#first bug +create table t1 (a int); +create temporary table tmp like t1; +load data local infile 'MYSQLTEST_VARDIR/load_data' INTO TABLE tmp; +insert into t1 select * from tmp; +#second bug +create table t2 (a int); +create temporary table tmp2 like t2; +insert into tmp2 values(10); +update tmp2 set a = 20 limit 1; +Warnings: +Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted +insert into t2 select * from tmp2; +connection server_2; +connection server_3; +#t1 should have 2 rows +select count(*) = 2 from t1; +count(*) = 2 +1 +#t2 should have 1 rows with a = 20 +select * from t2; +a +20 +connection server_1; +drop table t1, t2, tmp, tmp2; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/mdev_24667.cnf b/mysql-test/suite/rpl/t/mdev_24667.cnf new file mode 100644 index 00000000000..58b605ad928 --- /dev/null +++ b/mysql-test/suite/rpl/t/mdev_24667.cnf @@ -0,0 +1,8 @@ +!include ../my.cnf + +[mysqld.3] +log-slave-updates + +[ENV] +SERVER_MYPORT_3= @mysqld.3.port +SERVER_MYSOCK_3= @mysqld.3.socket diff --git a/mysql-test/suite/rpl/t/mdev_24667.test b/mysql-test/suite/rpl/t/mdev_24667.test new file mode 100644 index 00000000000..d8490b335db --- /dev/null +++ b/mysql-test/suite/rpl/t/mdev_24667.test @@ -0,0 +1,56 @@ +# +# MDEV-24667 LOAD DATA INFILE/inserted rows not written to binlog +# +# In this test we will have a replication configuration like 1->2->3 +# 1 will have statement format +# 2 and 3 will have mixed format +# We will make some updates on temporary table which are unsafe , So 2 must +# Log these queries in row format, Since it is on tmp table , It wont be logged +# So the next query which copies the data from tmp table to normal must be logged +# into the row format. Instead of checking for the binlog We will compare the +# results on the 3, If no binlog is lost(ie it is logged into row format), There +# should not be any data loss. +--let $rpl_topology=1->2->3 +--source include/rpl_init.inc +--source include/have_binlog_format_mixed.inc +call mtr.add_suppression('Unsafe statement written to the binary log using '); +--connection server_1 + +set binlog_format=statement; +--echo #first bug +create table t1 (a int); +create temporary table tmp like t1; +--write_file $MYSQLTEST_VARDIR/load_data +1 +2 +EOF +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +eval load data local infile '$MYSQLTEST_VARDIR/load_data' INTO TABLE tmp; +insert into t1 select * from tmp; + +--echo #second bug +create table t2 (a int); +#insert into t2 values(10); +create temporary table tmp2 like t2; +insert into tmp2 values(10); +update tmp2 set a = 20 limit 1; +insert into t2 select * from tmp2; +--save_master_pos + +--connection server_2 +--sync_with_master +--save_master_pos + +--connection server_3 +--sync_with_master +--echo #t1 should have 2 rows +select count(*) = 2 from t1; +--echo #t2 should have 1 rows with a = 20 +select * from t2; + + +# cleanup +--connection server_1 +drop table t1, t2, tmp, tmp2; +--remove_file $MYSQLTEST_VARDIR/load_data +--source include/rpl_end.inc |