diff options
| author | Marko Mäkelä <marko.makela@mariadb.com> | 2023-04-26 18:31:03 +0300 |
|---|---|---|
| committer | Marko Mäkelä <marko.makela@mariadb.com> | 2023-04-26 18:31:03 +0300 |
| commit | ce6616aa284eda7615678e1ccd558172da8de15b (patch) | |
| tree | 637dab3e3ccc818a330f18fb0b45280af6132090 /mysql-test | |
| parent | c21745dbe4b8e1489b1f2f27de094df04143830a (diff) | |
| parent | 09fdd3aca6f0d6cfaf8218def9d1a99f5a325160 (diff) | |
| download | mariadb-git-ce6616aa284eda7615678e1ccd558172da8de15b.tar.gz | |
Merge 10.9 into 10.10
Diffstat (limited to 'mysql-test')
58 files changed, 1933 insertions, 149 deletions
diff --git a/mysql-test/include/sql_mode_pad_char_to_full_length.inc b/mysql-test/include/sql_mode_pad_char_to_full_length.inc new file mode 100644 index 00000000000..df03c4dbc28 --- /dev/null +++ b/mysql-test/include/sql_mode_pad_char_to_full_length.inc @@ -0,0 +1,31 @@ +--echo # +--echo # MDEV-28190 sql_mode makes MDEV-371 virtual column expressions nondeterministic +--echo # + +CREATE TABLE t1 (a INT,b CHAR(20)); +SHOW CREATE TABLE t1; +CREATE UNIQUE INDEX bi USING HASH ON t1 (b); +INSERT INTO t1 VALUES (0,0); +SET sql_mode='pad_char_to_full_length'; +DELETE FROM t1; +DROP TABLE t1; + + +SET sql_mode=''; +CREATE TABLE t1 (a INT,b CHAR(20)); +SHOW CREATE TABLE t1; +CREATE UNIQUE INDEX bi USING HASH ON t1 (b); +SET sql_mode='pad_char_to_full_length'; +INSERT INTO t1 VALUES (0,0); +DELETE FROM t1; +DROP TABLE t1; + + +SET sql_mode=''; +CREATE OR REPLACE TABLE t1 (a CHAR(20),b CHAR(20)); +SHOW CREATE TABLE t1; +CREATE UNIQUE INDEX bi USING HASH ON t1 (b); +INSERT INTO t1 VALUES (0,0); +SET sql_mode='pad_char_to_full_length'; +DELETE FROM t1; +DROP TABLE t1; diff --git a/mysql-test/lib/My/CoreDump.pm b/mysql-test/lib/My/CoreDump.pm index 05b6edf1385..be6d21146d1 100644 --- a/mysql-test/lib/My/CoreDump.pm +++ b/mysql-test/lib/My/CoreDump.pm @@ -310,16 +310,8 @@ sub cdb_check { `cdb -? 2>&1`; if ($? >> 8) { - print "Cannot find cdb. Please Install Debugging tools for Windows\n"; - print "from http://www.microsoft.com/whdc/devtools/debugging/"; - if($ENV{'ProgramW6432'}) - { - print "install64bit.mspx (native x64 version)\n"; - } - else - { - print "installx86.mspx\n"; - } + print "Cannot find the cdb debugger. Please install Debugging tools for Windows\n"; + print "and set PATH environment variable to include location of cdb.exe"; } } @@ -328,25 +320,6 @@ sub _cdb { my ($core_name, $format)= @_; print "\nTrying 'cdb' to get a backtrace\n"; return unless -f $core_name; - - # Try to set environment for debugging tools for Windows - if ($ENV{'PATH'} !~ /Debugging Tools/) - { - if ($ENV{'ProgramW6432'}) - { - # On x64 computer - $ENV{'PATH'}.= ";".$ENV{'ProgramW6432'}."\\Debugging Tools For Windows (x64)"; - } - else - { - # On x86 computer. Newest versions of Debugging tools are installed in the - # directory with (x86) suffix, older versions did not have this suffix. - $ENV{'PATH'}.= ";".$ENV{'ProgramFiles'}."\\Debugging Tools For Windows (x86)"; - $ENV{'PATH'}.= ";".$ENV{'ProgramFiles'}."\\Debugging Tools For Windows"; - } - } - - # Read module list, find out the name of executable and # build symbol path (required by cdb if executable was built on # different machine) @@ -384,7 +357,7 @@ sub _cdb { if (!$ENV{'_NT_SYMBOL_PATH'}) { my $windir= $ENV{'windir'}; - my $symbol_cache= substr($windir ,0, index($windir,'\\'))."\\cdb_symbols"; + my $symbol_cache= substr($windir ,0, index($windir,'\\'))."\\symbols"; print "OS debug symbols will be downloaded and stored in $symbol_cache.\n"; print "You can control the location of symbol cache with _NT_SYMBOL_PATH\n"; diff --git a/mysql-test/main/bootstrap.result b/mysql-test/main/bootstrap.result index fd9b4726cb6..cfff13136d6 100644 --- a/mysql-test/main/bootstrap.result +++ b/mysql-test/main/bootstrap.result @@ -1,15 +1,28 @@ -drop table if exists t1; +# +# test mysqld in bootstrap mode +# +# +# Check that --bootstrap reads from stdin +# # Kill the server # restart drop table t1; +# +# Check that --bootstrap of file with SQL error returns error +# # Kill the server # restart drop table t1; ERROR 42S02: Unknown table 'test.t1' +# +# Bootstrap with a large thd->net.max_packet +# # Kill the server # restart drop table t1; -End of 5.1 tests +# +# End of 5.1 tests +# # # Bug #11766306: 59393: HAVE_INNODB=YES WHEN MYSQLD # STARTED WITH --SKIP-INNODB @@ -18,9 +31,23 @@ SELECT 'bug' as '' FROM INFORMATION_SCHEMA.ENGINES WHERE engine='innodb' and SUPPORT='YES'; # Kill the server +# +# MDEV-13063 Server crashes in intern_plugin_lock or assertion `plugin_ptr->ref_count == 1' fails in plugin_init +# +# +# MDEV-19349 mysql_install_db: segfault at tmp_file_prefix check +# # restart -End of 5.5 tests +# +# End of 5.5 tests +# +# +# Check that --bootstrap can install and uninstall plugins +# # Kill the server +# +# Check that installed plugins are *not* automatically loaded in --bootstrap +# # restart flush tables; show create table t1; @@ -35,6 +62,18 @@ EXAMPLE ha_example.so truncate table mysql.plugin; # Kill the server # +# MDEV-9969 mysql_install_db error processing ignore_db_dirs. +# +# +# MDEV-13397 MariaDB upgrade fail when using default_time_zone +# +# +# MDEV-30818 invalid ssl prevents bootstrap +# +# +# End of 10.3 tests +# +# # MDEV-28782 mariadb-tzinfo-to-sql to work in bootstrap mode # # diff --git a/mysql-test/main/bootstrap.test b/mysql-test/main/bootstrap.test index f7bddf23408..d7cc8e36d56 100644 --- a/mysql-test/main/bootstrap.test +++ b/mysql-test/main/bootstrap.test @@ -1,17 +1,20 @@ +--echo # +--echo # test mysqld in bootstrap mode +--echo # --source include/not_embedded.inc -# -# test mysqld in bootstrap mode -# ---disable_warnings -drop table if exists t1; ---enable_warnings +--source include/have_example_plugin.inc + +--let test_bootstrap=$MYSQLTEST_VARDIR/tmp/test_bootstrap.sql +--write_file $test_bootstrap +use test; +EOF # Add the datadir to the bootstrap command let $MYSQLD_DATADIR= `select @@datadir`; let $MYSQLD_BOOTSTRAP_CMD= $MYSQLD_BOOTSTRAP_CMD --datadir=$MYSQLD_DATADIR --tmpdir=$MYSQL_TMP_DIR --default-storage-engine=MyISAM --loose-skip-innodb --plugin-maturity=unknown; -# -# Check that --bootstrap reads from stdin -# +--echo # +--echo # Check that --bootstrap reads from stdin +--echo # --write_file $MYSQLTEST_VARDIR/tmp/bootstrap_test.sql use test; CREATE TABLE t1(a int); @@ -21,9 +24,9 @@ EOF --source include/start_mysqld.inc drop table t1; remove_file $MYSQLTEST_VARDIR/tmp/bootstrap_test.sql; -# -# Check that --bootstrap of file with SQL error returns error -# +--echo # +--echo # Check that --bootstrap of file with SQL error returns error +--echo # --write_file $MYSQLTEST_VARDIR/tmp/bootstrap_error.sql use test; CREATE TABLE t1; @@ -37,9 +40,9 @@ EOF drop table t1; remove_file $MYSQLTEST_VARDIR/tmp/bootstrap_error.sql; -# -# Bootstrap with a large thd->net.max_packet -# +--echo # +--echo # Bootstrap with a large thd->net.max_packet +--echo # --disable_query_log create table t1 select 2 as a, concat(repeat('MySQL', @@max_allowed_packet/10), ';') as b; eval select * into outfile '$MYSQLTEST_VARDIR/tmp/long_query.sql' from t1; @@ -51,7 +54,9 @@ remove_file $MYSQLTEST_VARDIR/tmp/long_query.sql; --source include/start_mysqld.inc drop table t1; ---echo End of 5.1 tests +--echo # +--echo # End of 5.1 tests +--echo # --echo # --echo # Bug #11766306: 59393: HAVE_INNODB=YES WHEN MYSQLD @@ -63,29 +68,25 @@ SELECT 'bug' as '' FROM INFORMATION_SCHEMA.ENGINES WHERE engine='innodb' and SUPPORT='YES'; --source include/kill_mysqld.inc -# -# MDEV-13063 Server crashes in intern_plugin_lock or assertion `plugin_ptr->ref_count == 1' fails in plugin_init -# +--echo # +--echo # MDEV-13063 Server crashes in intern_plugin_lock or assertion `plugin_ptr->ref_count == 1' fails in plugin_init +--echo # --error 1 --exec $MYSQLD_BOOTSTRAP_CMD --myisam_recover_options=NONE -# -# MDEV-19349 mysql_install_db: segfault at tmp_file_prefix check -# ---write_file $MYSQLTEST_VARDIR/tmp/1 -use test; -EOF ---exec $MYSQLD_BOOTSTRAP_CMD < $MYSQLTEST_VARDIR/tmp/1 >> $MYSQLTEST_VARDIR/tmp/bootstrap.log 2>&1 ---remove_file $MYSQLTEST_VARDIR/tmp/1 +--echo # +--echo # MDEV-19349 mysql_install_db: segfault at tmp_file_prefix check +--echo # +--exec $MYSQLD_BOOTSTRAP_CMD < $test_bootstrap >> $MYSQLTEST_VARDIR/tmp/bootstrap.log 2>&1 --source include/start_mysqld.inc ---echo End of 5.5 tests +--echo # +--echo # End of 5.5 tests +--echo # ---source include/not_windows_embedded.inc ---source include/have_example_plugin.inc -# -# Check that --bootstrap can install and uninstall plugins -# +--echo # +--echo # Check that --bootstrap can install and uninstall plugins +--echo # let $PLUGIN_DIR=`select @@plugin_dir`; --source include/kill_mysqld.inc --write_file $MYSQLTEST_VARDIR/tmp/install_plugin.sql @@ -95,9 +96,9 @@ EOF --exec $MYSQLD_BOOTSTRAP_CMD --plugin-dir=$PLUGIN_DIR < $MYSQLTEST_VARDIR/tmp/install_plugin.sql >> $MYSQLTEST_VARDIR/tmp/bootstrap.log 2>&1 --remove_file $MYSQLTEST_VARDIR/tmp/install_plugin.sql -# -# Check that installed plugins are *not* automatically loaded in --bootstrap -# +--echo # +--echo # Check that installed plugins are *not* automatically loaded in --bootstrap +--echo # --write_file $MYSQLTEST_VARDIR/tmp/bootstrap_plugins.sql SET SQL_MODE=""; use test; @@ -113,25 +114,25 @@ drop table t1; select * from mysql.plugin; truncate table mysql.plugin; - -# -# MDEV-9969 mysql_install_db error processing ignore_db_dirs. -# ---write_file $MYSQLTEST_VARDIR/tmp/bootstrap_9969.sql -use test; -EOF --source include/kill_mysqld.inc ---exec $MYSQLD_BOOTSTRAP_CMD --ignore-db-dirs='some_dir' --ignore-db-dirs='some_dir' < $MYSQLTEST_VARDIR/tmp/bootstrap_9969.sql >> $MYSQLTEST_VARDIR/tmp/bootstrap.log 2>&1 ---remove_file $MYSQLTEST_VARDIR/tmp/bootstrap_9969.sql +--echo # +--echo # MDEV-9969 mysql_install_db error processing ignore_db_dirs. +--echo # +--exec $MYSQLD_BOOTSTRAP_CMD --ignore-db-dirs='some_dir' --ignore-db-dirs='some_dir' < $test_bootstrap >> $MYSQLTEST_VARDIR/tmp/bootstrap.log 2>&1 -# -# MDEV-13397 MariaDB upgrade fail when using default_time_zone -# ---write_file $MYSQLTEST_VARDIR/tmp/bootstrap_9969.sql -use test; -EOF ---exec $MYSQLD_BOOTSTRAP_CMD --default-time-zone=Europe/Moscow < $MYSQLTEST_VARDIR/tmp/bootstrap_9969.sql >> $MYSQLTEST_VARDIR/tmp/bootstrap.log 2>&1 ---remove_file $MYSQLTEST_VARDIR/tmp/bootstrap_9969.sql +--echo # +--echo # MDEV-13397 MariaDB upgrade fail when using default_time_zone +--echo # +--exec $MYSQLD_BOOTSTRAP_CMD --default-time-zone=Europe/Moscow < $test_bootstrap >> $MYSQLTEST_VARDIR/tmp/bootstrap.log 2>&1 + +--echo # +--echo # MDEV-30818 invalid ssl prevents bootstrap +--echo # +--exec $MYSQLD_BOOTSTRAP_CMD --ssl-ca=/dev/nonexistent < $test_bootstrap >> $MYSQLTEST_VARDIR/tmp/bootstrap.log 2>&1 + +--echo # +--echo # End of 10.3 tests +--echo # --echo # --echo # MDEV-28782 mariadb-tzinfo-to-sql to work in bootstrap mode @@ -160,3 +161,4 @@ EOF # restore --source include/start_mysqld.inc +--remove_file $test_bootstrap diff --git a/mysql-test/main/ctype_uca_partitions.result b/mysql-test/main/ctype_uca_partitions.result index 677b68e2387..486e848e191 100644 --- a/mysql-test/main/ctype_uca_partitions.result +++ b/mysql-test/main/ctype_uca_partitions.result @@ -84,3 +84,43 @@ O P Y DROP TABLE t1; +# +# Start of 10.4 tests +# +# +# MDEV-30072 Wrong ORDER BY for a partitioned prefix key + NOPAD +# +SET NAMES utf8mb4; +CREATE TABLE t1 +( +id INT, +data VARCHAR(20), +KEY data_id (data,id) +) COLLATE utf8mb3_unicode_nopad_ci ENGINE=MyISAM +PARTITION BY RANGE COLUMNS (id) +( +PARTITION p10 VALUES LESS THAN (20), +PARTITION p20 VALUES LESS THAN MAXVALUE +); +INSERT INTO t1 VALUES (30, 'ss '), (10, 'ß '); +SELECT id FROM t1 WHERE data='ss ' ORDER BY id; +id +10 +30 +SELECT id FROM t1 WHERE data='ss ' ORDER BY id DESC; +id +30 +10 +ALTER TABLE t1 DROP KEY data_id, ADD KEY data_id2(data(10),id); +SELECT id FROM t1 WHERE data='ss ' ORDER BY id; +id +10 +30 +SELECT id FROM t1 WHERE data='ss ' ORDER BY id DESC; +id +30 +10 +DROP TABLE t1; +# +# End of 10.4 tests +# diff --git a/mysql-test/main/ctype_uca_partitions.test b/mysql-test/main/ctype_uca_partitions.test index 5734bb52008..81f1a091574 100644 --- a/mysql-test/main/ctype_uca_partitions.test +++ b/mysql-test/main/ctype_uca_partitions.test @@ -36,3 +36,35 @@ SELECT * FROM t1 PARTITION (p0) ORDER BY c1; SELECT * FROM t1 PARTITION (p1) ORDER BY c1; SELECT * FROM t1 PARTITION (p2) ORDER BY c1; DROP TABLE t1; + +--echo # +--echo # Start of 10.4 tests +--echo # + +--echo # +--echo # MDEV-30072 Wrong ORDER BY for a partitioned prefix key + NOPAD +--echo # + +SET NAMES utf8mb4; +CREATE TABLE t1 +( + id INT, + data VARCHAR(20), + KEY data_id (data,id) +) COLLATE utf8mb3_unicode_nopad_ci ENGINE=MyISAM +PARTITION BY RANGE COLUMNS (id) +( + PARTITION p10 VALUES LESS THAN (20), + PARTITION p20 VALUES LESS THAN MAXVALUE +); +INSERT INTO t1 VALUES (30, 'ss '), (10, 'ß '); +SELECT id FROM t1 WHERE data='ss ' ORDER BY id; +SELECT id FROM t1 WHERE data='ss ' ORDER BY id DESC; +ALTER TABLE t1 DROP KEY data_id, ADD KEY data_id2(data(10),id); +SELECT id FROM t1 WHERE data='ss ' ORDER BY id; +SELECT id FROM t1 WHERE data='ss ' ORDER BY id DESC; +DROP TABLE t1; + +--echo # +--echo # End of 10.4 tests +--echo # diff --git a/mysql-test/main/derived.result b/mysql-test/main/derived.result index 3576c1fa356..cda21e275ae 100644 --- a/mysql-test/main/derived.result +++ b/mysql-test/main/derived.result @@ -1330,5 +1330,28 @@ a b DROP VIEW v1; DROP TABLE t1; # +# MDEV-28616: derived table over union with order by clause that +# contains subquery with unresolvable column reference +# +SELECT 1 FROM ( +SELECT 1 UNION SELECT 2 ORDER BY (SELECT 1 FROM DUAL WHERE xxx = 0) +) dt; +ERROR 42S22: Unknown column 'xxx' in 'where clause' +create table t1 (a int, b int); +insert into t1 values (3,8), (7,2), (1,4), (5,9); +create table t2 (a int, b int); +insert into t2 values (9,1), (7,3), (2,6); +create table t3 (c int, d int); +insert into t3 values (7,8), (1,2), (3,8); +select * from +( +select a,b from t1 where t1.a > 3 +union +select a,b from t2 where t2.b < 6 +order by (a - b / (select a + max(c) from t3 where d = x)) +) dt; +ERROR 42S22: Unknown column 'x' in 'where clause' +drop table t1,t2,t3; +# # End of 10.3 tests # diff --git a/mysql-test/main/derived.test b/mysql-test/main/derived.test index e25c1c89ad3..f78266b95b0 100644 --- a/mysql-test/main/derived.test +++ b/mysql-test/main/derived.test @@ -1144,5 +1144,35 @@ DROP VIEW v1; DROP TABLE t1; --echo # +--echo # MDEV-28616: derived table over union with order by clause that +--echo # contains subquery with unresolvable column reference +--echo # + +--error ER_BAD_FIELD_ERROR +SELECT 1 FROM ( + SELECT 1 UNION SELECT 2 ORDER BY (SELECT 1 FROM DUAL WHERE xxx = 0) +) dt; + +create table t1 (a int, b int); +insert into t1 values (3,8), (7,2), (1,4), (5,9); + +create table t2 (a int, b int); +insert into t2 values (9,1), (7,3), (2,6); + +create table t3 (c int, d int); +insert into t3 values (7,8), (1,2), (3,8); + +--error ER_BAD_FIELD_ERROR +select * from +( + select a,b from t1 where t1.a > 3 + union + select a,b from t2 where t2.b < 6 + order by (a - b / (select a + max(c) from t3 where d = x)) +) dt; + +drop table t1,t2,t3; + +--echo # --echo # End of 10.3 tests --echo # diff --git a/mysql-test/main/group_min_max.result b/mysql-test/main/group_min_max.result index 2b8b10b29b9..706a4132614 100644 --- a/mysql-test/main/group_min_max.result +++ b/mysql-test/main/group_min_max.result @@ -4083,6 +4083,18 @@ MIN(pk) 1 DROP TABLE t1, t2; # +# MDEV-30605 Wrong result while using index for group-by +# +CREATE TABLE t1 (pk INT primary key, a int, key(a)) engine=innodb; +INSERT INTO t1 VALUES (1,-1),(2,8),(3,5),(4,-1),(5,10), (6,-1); +SELECT MIN(pk), a FROM t1 WHERE pk <> 1 GROUP BY a; +MIN(pk) a +4 -1 +3 5 +2 8 +5 10 +DROP TABLE t1; +# # End of 10.5 tests # # diff --git a/mysql-test/main/group_min_max.test b/mysql-test/main/group_min_max.test index 0fa91e4d72b..1bc334dd3da 100644 --- a/mysql-test/main/group_min_max.test +++ b/mysql-test/main/group_min_max.test @@ -1739,6 +1739,17 @@ SELECT MIN(pk) FROM t1, t2; DROP TABLE t1, t2; --echo # +--echo # MDEV-30605 Wrong result while using index for group-by +--echo # + +CREATE TABLE t1 (pk INT primary key, a int, key(a)) engine=innodb; +INSERT INTO t1 VALUES (1,-1),(2,8),(3,5),(4,-1),(5,10), (6,-1); + +SELECT MIN(pk), a FROM t1 WHERE pk <> 1 GROUP BY a; + +DROP TABLE t1; + +--echo # --echo # End of 10.5 tests --echo # diff --git a/mysql-test/main/mysql_tzinfo_to_sql_symlink.result b/mysql-test/main/mysql_tzinfo_to_sql_symlink.result index b6b35e44988..97548768a2d 100644 --- a/mysql-test/main/mysql_tzinfo_to_sql_symlink.result +++ b/mysql-test/main/mysql_tzinfo_to_sql_symlink.result @@ -10,13 +10,13 @@ CREATE TABLE time_zone_leap_second LIKE mysql.time_zone_leap_second; set @wsrep_is_on=(select coalesce(sum(SESSION_VALUE='ON'), 0) from information_schema.SYSTEM_VARIABLES WHERE VARIABLE_NAME='wsrep_on'); SET STATEMENT SQL_MODE='' FOR SELECT concat('%', GROUP_CONCAT(OPTION), '%') INTO @replicate_opt FROM (SELECT DISTINCT concat('REPLICATE_', UPPER(ENGINE)) AS OPTION FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME IN ('time_zone', 'time_zone_name', 'time_zone_transition', 'time_zone_transition_type', 'time_zone_leap_second') AND ENGINE in ('MyISAM', 'Aria')) AS o ORDER BY OPTION DESC; set @wsrep_cannot_replicate_tz=@wsrep_is_on AND (select coalesce(sum(GLOBAL_VALUE NOT LIKE @replicate_opt), 0) from information_schema.SYSTEM_VARIABLES WHERE VARIABLE_NAME='wsrep_mode'); -execute immediate if(@wsrep_cannot_replicate_tz, "select ENGINE into @time_zone_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME='time_zone'", 'do 0'); +execute immediate if(@wsrep_cannot_replicate_tz, 'select ENGINE into @time_zone_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME=''time_zone''', 'do 0'); execute immediate if(@wsrep_cannot_replicate_tz, 'ALTER TABLE time_zone ENGINE=InnoDB', 'do 0'); -execute immediate if(@wsrep_cannot_replicate_tz, "select ENGINE into @time_zone_name_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME='time_zone_name'", 'do 0'); +execute immediate if(@wsrep_cannot_replicate_tz, 'select ENGINE into @time_zone_name_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME=''time_zone_name''', 'do 0'); execute immediate if(@wsrep_cannot_replicate_tz, 'ALTER TABLE time_zone_name ENGINE=InnoDB', 'do 0'); -execute immediate if(@wsrep_cannot_replicate_tz, "select ENGINE into @time_zone_transition_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME='time_zone_transition'", 'do 0'); +execute immediate if(@wsrep_cannot_replicate_tz, 'select ENGINE into @time_zone_transition_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME=''time_zone_transition''', 'do 0'); execute immediate if(@wsrep_cannot_replicate_tz, 'ALTER TABLE time_zone_transition ENGINE=InnoDB', 'do 0'); -execute immediate if(@wsrep_cannot_replicate_tz, "select ENGINE into @time_zone_transition_type_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME='time_zone_transition_type'", 'do 0'); +execute immediate if(@wsrep_cannot_replicate_tz, 'select ENGINE into @time_zone_transition_type_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME=''time_zone_transition_type''', 'do 0'); execute immediate if(@wsrep_cannot_replicate_tz, 'ALTER TABLE time_zone_transition_type ENGINE=InnoDB', 'do 0'); TRUNCATE TABLE time_zone; TRUNCATE TABLE time_zone_name; @@ -59,13 +59,13 @@ execute immediate if(@wsrep_cannot_replicate_tz, concat('ALTER TABLE time_zone_t set @wsrep_is_on=(select coalesce(sum(SESSION_VALUE='ON'), 0) from information_schema.SYSTEM_VARIABLES WHERE VARIABLE_NAME='wsrep_on'); SET STATEMENT SQL_MODE='' FOR SELECT concat('%', GROUP_CONCAT(OPTION), '%') INTO @replicate_opt FROM (SELECT DISTINCT concat('REPLICATE_', UPPER(ENGINE)) AS OPTION FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME IN ('time_zone', 'time_zone_name', 'time_zone_transition', 'time_zone_transition_type', 'time_zone_leap_second') AND ENGINE in ('MyISAM', 'Aria')) AS o ORDER BY OPTION DESC; set @wsrep_cannot_replicate_tz=@wsrep_is_on AND (select coalesce(sum(GLOBAL_VALUE NOT LIKE @replicate_opt), 0) from information_schema.SYSTEM_VARIABLES WHERE VARIABLE_NAME='wsrep_mode'); -execute immediate if(@wsrep_cannot_replicate_tz, "select ENGINE into @time_zone_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME='time_zone'", 'do 0'); +execute immediate if(@wsrep_cannot_replicate_tz, 'select ENGINE into @time_zone_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME=''time_zone''', 'do 0'); execute immediate if(@wsrep_cannot_replicate_tz, 'ALTER TABLE time_zone ENGINE=InnoDB', 'do 0'); -execute immediate if(@wsrep_cannot_replicate_tz, "select ENGINE into @time_zone_name_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME='time_zone_name'", 'do 0'); +execute immediate if(@wsrep_cannot_replicate_tz, 'select ENGINE into @time_zone_name_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME=''time_zone_name''', 'do 0'); execute immediate if(@wsrep_cannot_replicate_tz, 'ALTER TABLE time_zone_name ENGINE=InnoDB', 'do 0'); -execute immediate if(@wsrep_cannot_replicate_tz, "select ENGINE into @time_zone_transition_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME='time_zone_transition'", 'do 0'); +execute immediate if(@wsrep_cannot_replicate_tz, 'select ENGINE into @time_zone_transition_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME=''time_zone_transition''', 'do 0'); execute immediate if(@wsrep_cannot_replicate_tz, 'ALTER TABLE time_zone_transition ENGINE=InnoDB', 'do 0'); -execute immediate if(@wsrep_cannot_replicate_tz, "select ENGINE into @time_zone_transition_type_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME='time_zone_transition_type'", 'do 0'); +execute immediate if(@wsrep_cannot_replicate_tz, 'select ENGINE into @time_zone_transition_type_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME=''time_zone_transition_type''', 'do 0'); execute immediate if(@wsrep_cannot_replicate_tz, 'ALTER TABLE time_zone_transition_type ENGINE=InnoDB', 'do 0'); TRUNCATE TABLE time_zone; TRUNCATE TABLE time_zone_name; @@ -191,13 +191,13 @@ TRUNCATE TABLE time_zone_leap_second; set @wsrep_is_on=(select coalesce(sum(SESSION_VALUE='ON'), 0) from information_schema.SYSTEM_VARIABLES WHERE VARIABLE_NAME='wsrep_on'); SET STATEMENT SQL_MODE='' FOR SELECT concat('%', GROUP_CONCAT(OPTION), '%') INTO @replicate_opt FROM (SELECT DISTINCT concat('REPLICATE_', UPPER(ENGINE)) AS OPTION FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME IN ('time_zone', 'time_zone_name', 'time_zone_transition', 'time_zone_transition_type', 'time_zone_leap_second') AND ENGINE in ('MyISAM', 'Aria')) AS o ORDER BY OPTION DESC; set @wsrep_cannot_replicate_tz=@wsrep_is_on AND (select coalesce(sum(GLOBAL_VALUE NOT LIKE @replicate_opt), 0) from information_schema.SYSTEM_VARIABLES WHERE VARIABLE_NAME='wsrep_mode'); -execute immediate if(@wsrep_cannot_replicate_tz, "select ENGINE into @time_zone_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME='time_zone'", 'do 0'); +execute immediate if(@wsrep_cannot_replicate_tz, 'select ENGINE into @time_zone_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME=''time_zone''', 'do 0'); execute immediate if(@wsrep_cannot_replicate_tz, 'ALTER TABLE time_zone ENGINE=InnoDB', 'do 0'); -execute immediate if(@wsrep_cannot_replicate_tz, "select ENGINE into @time_zone_name_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME='time_zone_name'", 'do 0'); +execute immediate if(@wsrep_cannot_replicate_tz, 'select ENGINE into @time_zone_name_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME=''time_zone_name''', 'do 0'); execute immediate if(@wsrep_cannot_replicate_tz, 'ALTER TABLE time_zone_name ENGINE=InnoDB', 'do 0'); -execute immediate if(@wsrep_cannot_replicate_tz, "select ENGINE into @time_zone_transition_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME='time_zone_transition'", 'do 0'); +execute immediate if(@wsrep_cannot_replicate_tz, 'select ENGINE into @time_zone_transition_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME=''time_zone_transition''', 'do 0'); execute immediate if(@wsrep_cannot_replicate_tz, 'ALTER TABLE time_zone_transition ENGINE=InnoDB', 'do 0'); -execute immediate if(@wsrep_cannot_replicate_tz, "select ENGINE into @time_zone_transition_type_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME='time_zone_transition_type'", 'do 0'); +execute immediate if(@wsrep_cannot_replicate_tz, 'select ENGINE into @time_zone_transition_type_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME=''time_zone_transition_type''', 'do 0'); execute immediate if(@wsrep_cannot_replicate_tz, 'ALTER TABLE time_zone_transition_type ENGINE=InnoDB', 'do 0'); /*M!100602 execute immediate if(@wsrep_cannot_replicate_tz, 'start transaction', 'LOCK TABLES time_zone WRITE, time_zone_leap_second WRITE, @@ -313,20 +313,20 @@ TRUNCATE TABLE time_zone_leap_second; set @wsrep_is_on=(select coalesce(sum(SESSION_VALUE='ON'), 0) from information_schema.SYSTEM_VARIABLES WHERE VARIABLE_NAME='wsrep_on'); SET STATEMENT SQL_MODE='' FOR SELECT concat('%', GROUP_CONCAT(OPTION), '%') INTO @replicate_opt FROM (SELECT DISTINCT concat('REPLICATE_', UPPER(ENGINE)) AS OPTION FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME IN ('time_zone', 'time_zone_name', 'time_zone_transition', 'time_zone_transition_type', 'time_zone_leap_second') AND ENGINE in ('MyISAM', 'Aria')) AS o ORDER BY OPTION DESC; set @wsrep_cannot_replicate_tz=@wsrep_is_on AND (select coalesce(sum(GLOBAL_VALUE NOT LIKE @replicate_opt), 0) from information_schema.SYSTEM_VARIABLES WHERE VARIABLE_NAME='wsrep_mode'); -execute immediate if(@wsrep_cannot_replicate_tz, "select ENGINE into @time_zone_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME='time_zone'", 'do 0'); +execute immediate if(@wsrep_cannot_replicate_tz, 'select ENGINE into @time_zone_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME=''time_zone''', 'do 0'); execute immediate if(@wsrep_cannot_replicate_tz, 'ALTER TABLE time_zone ENGINE=InnoDB', 'do 0'); -execute immediate if(@wsrep_cannot_replicate_tz, "select ENGINE into @time_zone_name_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME='time_zone_name'", 'do 0'); +execute immediate if(@wsrep_cannot_replicate_tz, 'select ENGINE into @time_zone_name_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME=''time_zone_name''', 'do 0'); execute immediate if(@wsrep_cannot_replicate_tz, 'ALTER TABLE time_zone_name ENGINE=InnoDB', 'do 0'); -execute immediate if(@wsrep_cannot_replicate_tz, "select ENGINE into @time_zone_transition_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME='time_zone_transition'", 'do 0'); +execute immediate if(@wsrep_cannot_replicate_tz, 'select ENGINE into @time_zone_transition_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME=''time_zone_transition''', 'do 0'); execute immediate if(@wsrep_cannot_replicate_tz, 'ALTER TABLE time_zone_transition ENGINE=InnoDB', 'do 0'); -execute immediate if(@wsrep_cannot_replicate_tz, "select ENGINE into @time_zone_transition_type_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME='time_zone_transition_type'", 'do 0'); +execute immediate if(@wsrep_cannot_replicate_tz, 'select ENGINE into @time_zone_transition_type_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME=''time_zone_transition_type''', 'do 0'); execute immediate if(@wsrep_cannot_replicate_tz, 'ALTER TABLE time_zone_transition_type ENGINE=InnoDB', 'do 0'); /*M!100602 execute immediate if(@wsrep_cannot_replicate_tz, 'start transaction', 'LOCK TABLES time_zone WRITE, time_zone_leap_second WRITE, time_zone_name WRITE, time_zone_transition WRITE, time_zone_transition_type WRITE')*/; -execute immediate if(@wsrep_cannot_replicate_tz, "select ENGINE into @time_zone_leap_second_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME='time_zone_leap_second'", 'do 0'); +execute immediate if(@wsrep_cannot_replicate_tz, 'select ENGINE into @time_zone_leap_second_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME=''time_zone_leap_second''', 'do 0'); execute immediate if(@wsrep_cannot_replicate_tz, 'ALTER TABLE time_zone_leap_second ENGINE=InnoDB', 'do 0'); TRUNCATE TABLE time_zone_leap_second; execute immediate if(@wsrep_cannot_replicate_tz, concat('ALTER TABLE time_zone_leap_second ENGINE=', @time_zone_leap_second_engine), 'do 0'); @@ -497,13 +497,13 @@ set sql_mode=default; set @wsrep_is_on=(select coalesce(sum(SESSION_VALUE='ON'), 0) from information_schema.SYSTEM_VARIABLES WHERE VARIABLE_NAME='wsrep_on'); SET STATEMENT SQL_MODE='' FOR SELECT concat('%', GROUP_CONCAT(OPTION), '%') INTO @replicate_opt FROM (SELECT DISTINCT concat('REPLICATE_', UPPER(ENGINE)) AS OPTION FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME IN ('time_zone', 'time_zone_name', 'time_zone_transition', 'time_zone_transition_type', 'time_zone_leap_second') AND ENGINE in ('MyISAM', 'Aria')) AS o ORDER BY OPTION DESC; set @wsrep_cannot_replicate_tz=@wsrep_is_on AND (select coalesce(sum(GLOBAL_VALUE NOT LIKE @replicate_opt), 0) from information_schema.SYSTEM_VARIABLES WHERE VARIABLE_NAME='wsrep_mode'); -execute immediate if(@wsrep_cannot_replicate_tz, "select ENGINE into @time_zone_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME='time_zone'", 'do 0'); +execute immediate if(@wsrep_cannot_replicate_tz, 'select ENGINE into @time_zone_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME=''time_zone''', 'do 0'); execute immediate if(@wsrep_cannot_replicate_tz, 'ALTER TABLE time_zone ENGINE=InnoDB', 'do 0'); -execute immediate if(@wsrep_cannot_replicate_tz, "select ENGINE into @time_zone_name_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME='time_zone_name'", 'do 0'); +execute immediate if(@wsrep_cannot_replicate_tz, 'select ENGINE into @time_zone_name_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME=''time_zone_name''', 'do 0'); execute immediate if(@wsrep_cannot_replicate_tz, 'ALTER TABLE time_zone_name ENGINE=InnoDB', 'do 0'); -execute immediate if(@wsrep_cannot_replicate_tz, "select ENGINE into @time_zone_transition_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME='time_zone_transition'", 'do 0'); +execute immediate if(@wsrep_cannot_replicate_tz, 'select ENGINE into @time_zone_transition_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME=''time_zone_transition''', 'do 0'); execute immediate if(@wsrep_cannot_replicate_tz, 'ALTER TABLE time_zone_transition ENGINE=InnoDB', 'do 0'); -execute immediate if(@wsrep_cannot_replicate_tz, "select ENGINE into @time_zone_transition_type_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME='time_zone_transition_type'", 'do 0'); +execute immediate if(@wsrep_cannot_replicate_tz, 'select ENGINE into @time_zone_transition_type_engine from information_schema.TABLES where TABLE_SCHEMA=DATABASE() and TABLE_NAME=''time_zone_transition_type''', 'do 0'); execute immediate if(@wsrep_cannot_replicate_tz, 'ALTER TABLE time_zone_transition_type ENGINE=InnoDB', 'do 0'); TRUNCATE TABLE time_zone; TRUNCATE TABLE time_zone_name; diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index 83bc03fe0f9..70038a5f605 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -9948,6 +9948,401 @@ SELECT a FROM t1 WHERE (a,b) in (SELECT @c,@d); a DROP TABLE t1; # +# MDEV-31085: multi-update using view with optimizer trace enabled +# +SET SESSION optimizer_trace = 'enabled=on'; +CREATE TABLE t (a int, b int); +CREATE VIEW v AS SELECT 1 AS c UNION SELECT 2 AS c; +INSERT INTO t VALUES (0,4),(5,6); +UPDATE t, v SET t.b = t.a, t.a = v.c WHERE v.c < t.a; +SELECT * FROM information_schema.optimizer_trace; +QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES +UPDATE t, v SET t.b = t.a, t.a = v.c WHERE v.c < t.a { + "steps": [ + { + "view": { + "table": "v", + "select_id": 2, + "algorithm": "materialized" + } + }, + { + "join_preparation": { + "select_id": 2, + "steps": [ + { + "expanded_query": "/* select#2 */ select 1 AS c" + } + ] + } + }, + { + "join_preparation": { + "select_id": 3, + "steps": [ + { + "expanded_query": "/* select#3 */ select 2 AS c" + } + ] + } + }, + { + "join_preparation": { + "select_id": 1, + "steps": [ + { + "expanded_query": "/* select#1 */ update t join v set t.b = t.a,t.a = v.c where v.c < t.a" + } + ] + } + }, + { + "join_optimization": { + "select_id": 1, + "steps": [ + { + "condition_processing": { + "condition": "WHERE", + "original_condition": "v.c < t.a", + "steps": [ + { + "transformation": "equality_propagation", + "resulting_condition": "v.c < t.a" + }, + { + "transformation": "constant_propagation", + "resulting_condition": "v.c < t.a" + }, + { + "transformation": "trivial_condition_removal", + "resulting_condition": "v.c < t.a" + } + ] + } + }, + { + "join_optimization": { + "select_id": 2, + "steps": [] + } + }, + { + "join_optimization": { + "select_id": 3, + "steps": [] + } + }, + { + "table_dependencies": [ + { + "table": "t", + "row_may_be_null": false, + "map_bit": 0, + "depends_on_map_bits": [] + }, + { + "table": "<derived2>", + "row_may_be_null": false, + "map_bit": 1, + "depends_on_map_bits": [] + } + ] + }, + { + "ref_optimizer_key_uses": [] + }, + { + "rows_estimation": [ + { + "table": "t", + "table_scan": { + "rows": 2, + "cost": 2.004394531 + } + }, + { + "table": "<derived2>", + "table_scan": { + "rows": 2, + "cost": 2 + } + } + ] + }, + { + "considered_execution_plans": [ + { + "plan_prefix": [], + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 2, + "cost": 2.004394531, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 2, + "cost": 2.004394531, + "uses_join_buffering": false + } + } + }, + { + "best_access_path": { + "table": "<derived2>", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 2, + "cost": 2, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 2, + "cost": 2, + "uses_join_buffering": false + } + } + } + ] + }, + { + "plan_prefix": [], + "table": "t", + "rows_for_plan": 2, + "cost_for_plan": 2.404394531, + "rest_of_plan": [ + { + "plan_prefix": ["t"], + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "<derived2>", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 2, + "cost": 2, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 2, + "cost": 2, + "uses_join_buffering": true + } + } + } + ] + }, + { + "plan_prefix": ["t"], + "table": "<derived2>", + "rows_for_plan": 4, + "cost_for_plan": 5.204394531 + } + ] + }, + { + "plan_prefix": [], + "table": "<derived2>", + "rows_for_plan": 2, + "cost_for_plan": 2.4, + "rest_of_plan": [ + { + "plan_prefix": ["<derived2>"], + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 2, + "cost": 2.004394531, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 2, + "cost": 2.004394531, + "uses_join_buffering": true + } + } + } + ] + }, + { + "plan_prefix": ["<derived2>"], + "table": "t", + "rows_for_plan": 4, + "cost_for_plan": 5.204394531, + "pruned_by_cost": true, + "current_cost": 5.204394531, + "best_cost": 5.204394531 + } + ] + } + ] + }, + { + "best_join_order": ["t", "<derived2>"] + }, + { + "substitute_best_equal": { + "condition": "WHERE", + "resulting_condition": "v.c < t.a" + } + }, + { + "attaching_conditions_to_tables": { + "attached_conditions_computation": [], + "attached_conditions_summary": [ + { + "table": "t", + "attached": null + }, + { + "table": "<derived2>", + "attached": "v.c < t.a" + } + ] + } + } + ] + } + }, + { + "join_execution": { + "select_id": 1, + "steps": [ + { + "join_execution": { + "select_id": 2, + "steps": [] + } + }, + { + "join_execution": { + "select_id": 3, + "steps": [] + } + }, + { + "join_preparation": { + "select_id": "fake", + "steps": [ + { + "expanded_query": "select c AS c from dual" + } + ] + } + }, + { + "join_optimization": { + "select_id": "fake", + "steps": [ + { + "table_dependencies": [ + { + "table": "union", + "row_may_be_null": false, + "map_bit": 0, + "depends_on_map_bits": [] + } + ] + }, + { + "rows_estimation": [ + { + "table": "union", + "table_scan": { + "rows": 2, + "cost": 10.1 + } + } + ] + }, + { + "considered_execution_plans": [ + { + "plan_prefix": [], + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "union", + "considered_access_paths": [ + { + "access_type": "scan", + "resulting_rows": 2, + "cost": 10.1, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records": 2, + "cost": 10.1, + "uses_join_buffering": false + } + } + } + ] + }, + { + "plan_prefix": [], + "table": "union", + "rows_for_plan": 2, + "cost_for_plan": 10.5 + } + ] + }, + { + "best_join_order": ["union"] + }, + { + "attaching_conditions_to_tables": { + "attached_conditions_computation": [], + "attached_conditions_summary": [ + { + "table": "union", + "attached": null + } + ] + } + } + ] + } + }, + { + "join_execution": { + "select_id": "fake", + "steps": [] + } + } + ] + } + } + ] +} 0 0 +SELECT * FROM t; +a b +0 4 +1 5 +SET optimizer_trace=DEFAULT; +DROP VIEW v; +DROP TABLE t; +# # End of 10.4 tests # set optimizer_trace='enabled=on'; diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test index d07afb2dfce..0cacc4a60ea 100644 --- a/mysql-test/main/opt_trace.test +++ b/mysql-test/main/opt_trace.test @@ -683,6 +683,25 @@ SELECT a FROM t1 WHERE (a,b) in (SELECT @c,@d); DROP TABLE t1; --echo # +--echo # MDEV-31085: multi-update using view with optimizer trace enabled +--echo # + +SET SESSION optimizer_trace = 'enabled=on'; + +CREATE TABLE t (a int, b int); +CREATE VIEW v AS SELECT 1 AS c UNION SELECT 2 AS c; +INSERT INTO t VALUES (0,4),(5,6); +UPDATE t, v SET t.b = t.a, t.a = v.c WHERE v.c < t.a; +SELECT * FROM information_schema.optimizer_trace; + +SELECT * FROM t; + +SET optimizer_trace=DEFAULT; + +DROP VIEW v; +DROP TABLE t; + +--echo # --echo # End of 10.4 tests --echo # diff --git a/mysql-test/main/parser.result b/mysql-test/main/parser.result index 50be9dc49c8..89732e20b40 100644 --- a/mysql-test/main/parser.result +++ b/mysql-test/main/parser.result @@ -1910,12 +1910,32 @@ SET @@sql_mode=@save_sql_mode; # # MDEV-30151 parse error 1=2 not between/in # -select 1=2 not in (3,4); -1=2 not in (3,4) +SELECT 1=2 NOT IN (3,4); +1=2 NOT IN (3,4) 1 -select 1=2 not between 3 and 4; -1=2 not between 3 and 4 +SELECT 1=2 NOT BETWEEN 3 AND 4; +1=2 NOT BETWEEN 3 AND 4 1 +CREATE TABLE t1 ( f INT AS ( 1 IN ( 2 NOT BETWEEN 3 AND 4 ) ) ); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f` int(11) GENERATED ALWAYS AS (1 = 2 not between 3 and 4) VIRTUAL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE TABLE t1 ( f INT, CHECK ( 1 IN ( 2 NOT BETWEEN 3 AND 4 ) ) ); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f` int(11) DEFAULT NULL, + CONSTRAINT `CONSTRAINT_1` CHECK (1 = 2 not between 3 and 4) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +DROP TABLE t1; +CREATE VIEW v1 AS SELECT 1 IN ( 2 NOT BETWEEN 3 AND 4 ); +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 1 = 2 not between 3 and 4 AS `1 IN ( 2 NOT BETWEEN 3 AND 4 )` latin1 latin1_swedish_ci +DROP VIEW v1; # # End of 10.3 tests # diff --git a/mysql-test/main/parser.test b/mysql-test/main/parser.test index 48cda68b416..dcad30b36b8 100644 --- a/mysql-test/main/parser.test +++ b/mysql-test/main/parser.test @@ -1687,8 +1687,21 @@ SET @@sql_mode=@save_sql_mode; --echo # --echo # MDEV-30151 parse error 1=2 not between/in --echo # -select 1=2 not in (3,4); -select 1=2 not between 3 and 4; + +SELECT 1=2 NOT IN (3,4); +SELECT 1=2 NOT BETWEEN 3 AND 4; + +CREATE TABLE t1 ( f INT AS ( 1 IN ( 2 NOT BETWEEN 3 AND 4 ) ) ); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 ( f INT, CHECK ( 1 IN ( 2 NOT BETWEEN 3 AND 4 ) ) ); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE VIEW v1 AS SELECT 1 IN ( 2 NOT BETWEEN 3 AND 4 ); +SHOW CREATE VIEW v1; +DROP VIEW v1; --echo # --echo # End of 10.3 tests diff --git a/mysql-test/main/sql_mode_pad_char_to_full_length.result b/mysql-test/main/sql_mode_pad_char_to_full_length.result new file mode 100644 index 00000000000..6f68aade613 --- /dev/null +++ b/mysql-test/main/sql_mode_pad_char_to_full_length.result @@ -0,0 +1,94 @@ +# +# Start of 10.4 tests +# +# +# MDEV-28190 sql_mode makes MDEV-371 virtual column expressions nondeterministic +# +SET default_storage_engine=MyISAM; +# +# MDEV-28190 sql_mode makes MDEV-371 virtual column expressions nondeterministic +# +CREATE TABLE t1 (a INT,b CHAR(20)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` char(20) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +CREATE UNIQUE INDEX bi USING HASH ON t1 (b); +INSERT INTO t1 VALUES (0,0); +SET sql_mode='pad_char_to_full_length'; +DELETE FROM t1; +DROP TABLE t1; +SET sql_mode=''; +CREATE TABLE t1 (a INT,b CHAR(20)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` char(20) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +CREATE UNIQUE INDEX bi USING HASH ON t1 (b); +SET sql_mode='pad_char_to_full_length'; +INSERT INTO t1 VALUES (0,0); +DELETE FROM t1; +DROP TABLE t1; +SET sql_mode=''; +CREATE OR REPLACE TABLE t1 (a CHAR(20),b CHAR(20)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` char(20) DEFAULT NULL, + `b` char(20) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +CREATE UNIQUE INDEX bi USING HASH ON t1 (b); +INSERT INTO t1 VALUES (0,0); +SET sql_mode='pad_char_to_full_length'; +DELETE FROM t1; +DROP TABLE t1; +SET default_storage_engine=MEMORY; +# +# MDEV-28190 sql_mode makes MDEV-371 virtual column expressions nondeterministic +# +CREATE TABLE t1 (a INT,b CHAR(20)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` char(20) DEFAULT NULL +) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +CREATE UNIQUE INDEX bi USING HASH ON t1 (b); +INSERT INTO t1 VALUES (0,0); +SET sql_mode='pad_char_to_full_length'; +DELETE FROM t1; +DROP TABLE t1; +SET sql_mode=''; +CREATE TABLE t1 (a INT,b CHAR(20)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` char(20) DEFAULT NULL +) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +CREATE UNIQUE INDEX bi USING HASH ON t1 (b); +SET sql_mode='pad_char_to_full_length'; +INSERT INTO t1 VALUES (0,0); +DELETE FROM t1; +DROP TABLE t1; +SET sql_mode=''; +CREATE OR REPLACE TABLE t1 (a CHAR(20),b CHAR(20)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` char(20) DEFAULT NULL, + `b` char(20) DEFAULT NULL +) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +CREATE UNIQUE INDEX bi USING HASH ON t1 (b); +INSERT INTO t1 VALUES (0,0); +SET sql_mode='pad_char_to_full_length'; +DELETE FROM t1; +DROP TABLE t1; +SET default_storage_engine=DEFAULT; +# +# End of 10.4 tests +# diff --git a/mysql-test/main/sql_mode_pad_char_to_full_length.test b/mysql-test/main/sql_mode_pad_char_to_full_length.test new file mode 100644 index 00000000000..4d492bc1b70 --- /dev/null +++ b/mysql-test/main/sql_mode_pad_char_to_full_length.test @@ -0,0 +1,19 @@ +--echo # +--echo # Start of 10.4 tests +--echo # + +--echo # +--echo # MDEV-28190 sql_mode makes MDEV-371 virtual column expressions nondeterministic +--echo # + +SET default_storage_engine=MyISAM; +--source include/sql_mode_pad_char_to_full_length.inc + +SET default_storage_engine=MEMORY; +--source include/sql_mode_pad_char_to_full_length.inc + +SET default_storage_engine=DEFAULT; + +--echo # +--echo # End of 10.4 tests +--echo # diff --git a/mysql-test/main/update.result b/mysql-test/main/update.result index f5edf1c6be3..b9b80fb48de 100644 --- a/mysql-test/main/update.result +++ b/mysql-test/main/update.result @@ -734,3 +734,32 @@ UPDATE t1,t2 SET t1.i1 = -39 WHERE t2.d1 <> t1.i1 AND t2.d1 = t1.d2; ERROR 22007: Incorrect datetime value: '19' for column `test`.`t1`.`i1` at row 1 DROP TABLE t1,t2; # End of MariaDB 10.2 tests +# +# MDEV-20773: UPDATE with LIKE predicate over non-indexed column +# of VARCHAR type +# +create table t1 (a1 varchar(30), a2 varchar(30) collate utf8_bin); +insert into t1 values +('aa','zzz'), ('b','xxaa'), ('ccc','yyy'), ('ddd','xxb'); +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 +explain extended +update t1 set a1 = 'u' + where a2 like 'xx%' and exists(select 1 from t1 where t1.a1 < 'c'); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 50.00 Using where +2 SUBQUERY t1 ALL NULL NULL NULL NULL 4 50.00 Using where +Warnings: +Note 1003 /* select#1 */ update `test`.`t1` set `test`.`t1`.`a1` = 'u' where `test`.`t1`.`a2` like 'xx%' +update t1 set a1 = 'u' + where a2 like 'xx%' and exists(select 1 from t1 where t1.a1 < 'c'); +select * from t1; +a1 a2 +aa zzz +u xxaa +ccc yyy +u xxb +drop table t1; +# End of MariaDB 10.4 tests diff --git a/mysql-test/main/update.test b/mysql-test/main/update.test index 8a6949447ee..147d69d50c9 100644 --- a/mysql-test/main/update.test +++ b/mysql-test/main/update.test @@ -676,3 +676,26 @@ UPDATE t1,t2 SET t1.i1 = -39 WHERE t2.d1 <> t1.i1 AND t2.d1 = t1.d2; DROP TABLE t1,t2; --echo # End of MariaDB 10.2 tests + +--echo # +--echo # MDEV-20773: UPDATE with LIKE predicate over non-indexed column +--echo # of VARCHAR type +--echo # + +create table t1 (a1 varchar(30), a2 varchar(30) collate utf8_bin); +insert into t1 values + ('aa','zzz'), ('b','xxaa'), ('ccc','yyy'), ('ddd','xxb'); +analyze table t1 persistent for all; + +explain extended +update t1 set a1 = 'u' + where a2 like 'xx%' and exists(select 1 from t1 where t1.a1 < 'c'); + +update t1 set a1 = 'u' + where a2 like 'xx%' and exists(select 1 from t1 where t1.a1 < 'c'); + +select * from t1; + +drop table t1; + +--echo # End of MariaDB 10.4 tests diff --git a/mysql-test/mariadb-test-run.pl b/mysql-test/mariadb-test-run.pl index 670f63a990f..4fc4d9f050e 100755 --- a/mysql-test/mariadb-test-run.pl +++ b/mysql-test/mariadb-test-run.pl @@ -145,7 +145,6 @@ my $opt_start_exit; my $start_only; my $file_wsrep_provider; my $num_saved_cores= 0; # Number of core files saved in vardir/log/ so far. -my $test_name_for_report; our @global_suppressions; @@ -402,6 +401,11 @@ sub main { mtr_report("Collecting tests..."); my $tests= collect_test_cases($opt_reorder, $opt_suites, \@opt_cases, \@opt_skip_test_list); + if (@$tests == 0) { + mtr_report("No tests to run..."); + exit 0; + } + mark_time_used('collect'); mysql_install_db(default_mysqld(), "$opt_vardir/install.db") unless using_extern(); @@ -516,13 +520,13 @@ sub main { } if ( not @$completed ) { - if ($test_name_for_report) - { - my $tinfo = My::Test->new(name => $test_name_for_report); - $tinfo->{result}= 'MTR_RES_FAILED'; - $tinfo->{comment}=' '; - mtr_report_test($tinfo); - } + my $test_name= mtr_grab_file($path_testlog); + $test_name =~ s/^CURRENT_TEST:\s//; + chomp($test_name); + my $tinfo = My::Test->new(name => $test_name); + $tinfo->{result}= 'MTR_RES_FAILED'; + $tinfo->{comment}=' '; + mtr_report_test($tinfo); mtr_error("Test suite aborted"); } @@ -3741,8 +3745,8 @@ sub resfile_report_test ($) { sub run_testcase ($$) { my ($tinfo, $server_socket)= @_; my $print_freq=20; - $test_name_for_report= $tinfo->{name}; - mtr_verbose("Running test:", $test_name_for_report); + + mtr_verbose("Running test:", $tinfo->{name}); $ENV{'MTR_TEST_NAME'} = $tinfo->{name}; resfile_report_test($tinfo) if $opt_resfile; @@ -5131,10 +5135,12 @@ sub mysqld_start ($$) { if (!$rc) { # Report failure about the last test case before exit - my $tinfo = My::Test->new(name => $test_name_for_report); + my $test_name= mtr_grab_file($path_current_testlog); + $test_name =~ s/^CURRENT_TEST:\s//; + my $tinfo = My::Test->new(name => $test_name); $tinfo->{result}= 'MTR_RES_FAILED'; $tinfo->{failures}= 1; - $tinfo->{logfile}=get_log_from_proc($mysqld->{'proc'}, $test_name_for_report); + $tinfo->{logfile}=get_log_from_proc($mysqld->{'proc'}, $tinfo->{name}); report_option('verbose', 1); mtr_report_test($tinfo); } diff --git a/mysql-test/suite/binlog/t/binlog_truncate_multi_engine.inc b/mysql-test/suite/binlog/t/binlog_truncate_multi_engine.inc index 52ce4741eaa..f3801070851 100644 --- a/mysql-test/suite/binlog/t/binlog_truncate_multi_engine.inc +++ b/mysql-test/suite/binlog/t/binlog_truncate_multi_engine.inc @@ -20,7 +20,9 @@ connect(con1,localhost,root,,); --source include/show_binary_logs.inc INSERT INTO t1 VALUES (1, REPEAT("x", 1)); INSERT INTO t2 VALUES (1, REPEAT("x", 1)); -if (`SELECT $case = "B"`) +--let $is_case_B=`SELECT $case = "B"` + +if ($is_case_B) { --write_file $MYSQLTEST_VARDIR/tmp/mysqld.1.expect wait-binlog_truncate_multi_engine.test @@ -39,12 +41,12 @@ if (`SELECT $debug_sync_action != ""`) send COMMIT; --connection default -if (`SELECT $case = "B"`) +if ($is_case_B) { --source include/wait_until_disconnected.inc --source include/start_mysqld.inc } -if (`SELECT $case != "B"`) +if (!$is_case_B) { SET DEBUG_SYNC= "now WAIT_FOR con1_ready"; --echo List of binary logs after rotation diff --git a/mysql-test/suite/galera/r/MDEV-30804.result b/mysql-test/suite/galera/r/MDEV-30804.result new file mode 100644 index 00000000000..2bf323d19f8 --- /dev/null +++ b/mysql-test/suite/galera/r/MDEV-30804.result @@ -0,0 +1,11 @@ +connection node_2; +connection node_1; +CREATE TABLE t (a INT) ENGINE=Aria; +CREATE TABLE t1 (a INT) ENGINE=InnoDB; +START TRANSACTION; +INSERT INTO t VALUES ('1'); +INSERT INTO t1 VALUES ('1'); +COMMIT; +ERROR HY000: Transactional commit not supported by involved engine(s) +DROP TABLE t; +DROP TABLE t1; diff --git a/mysql-test/suite/galera/r/MDEV-30955.result b/mysql-test/suite/galera/r/MDEV-30955.result new file mode 100644 index 00000000000..2a090cb58bc --- /dev/null +++ b/mysql-test/suite/galera/r/MDEV-30955.result @@ -0,0 +1,26 @@ +connection node_2; +connection node_1; +CREATE TABLE t (a CHAR(1) KEY); +START TRANSACTION; +HANDLER t OPEN; +disconnect node_1; +connect node_1, 127.0.0.1, root, , test, $NODE_MYPORT_1; +DROP TABLE t; +BACKUP STAGE START; +START TRANSACTION; +disconnect node_1; +connect node_1, 127.0.0.1, root, , test, $NODE_MYPORT_1; +connection node_1; +CREATE TABLE t1 (f1 INTEGER PRIMARY KEY); +CREATE TABLE t2 (f1 INTEGER PRIMARY KEY); +START TRANSACTION; +INSERT INTO t1 VALUES(1); +HANDLER t2 OPEN; +connection node_2; +INSERT INTO t1 VALUES(1); +connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1; +connection node_1a; +connection node_1; +COMMIT; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +DROP TABLE t1,t2; diff --git a/mysql-test/suite/galera/r/galera_var_replicate_aria_on.result b/mysql-test/suite/galera/r/galera_var_replicate_aria_on.result index 39fd748314c..91c07ba6681 100644 --- a/mysql-test/suite/galera/r/galera_var_replicate_aria_on.result +++ b/mysql-test/suite/galera/r/galera_var_replicate_aria_on.result @@ -89,11 +89,8 @@ connection node_1; SET GLOBAL wsrep_sync_wait=15; CREATE TABLE t1 (f1 INTEGER NOT NULL PRIMARY KEY) ENGINE=Aria; CREATE TABLE t2 (f1 INTEGER NOT NULL PRIMARY KEY) ENGINE=InnoDB; -SET AUTOCOMMIT=OFF; -START TRANSACTION; INSERT INTO t1 VALUES (1); INSERT INTO t2 VALUES (1); -COMMIT; connection node_2; SET GLOBAL wsrep_sync_wait=15; SELECT COUNT(*) AS EXPECT_1 FROM t1; @@ -103,6 +100,7 @@ SELECT COUNT(*) AS EXPECT_1 FROM t2; EXPECT_1 1 connection node_1; +SET AUTOCOMMIT=OFF; START TRANSACTION; INSERT INTO t1 VALUES (2); INSERT INTO t2 VALUES (2); @@ -129,6 +127,7 @@ INSERT INTO t1 VALUES (1); ERROR 23000: Duplicate entry '1' for key 'PRIMARY' connection node_1; COMMIT; +ERROR HY000: Transactional commit not supported by involved engine(s) DROP TABLE t1,t2; connection node_1; CREATE TABLE t1 (i INT NOT NULL PRIMARY KEY) ENGINE=INNODB; diff --git a/mysql-test/suite/galera/r/mdev-26175.result b/mysql-test/suite/galera/r/mdev-26175.result new file mode 100644 index 00000000000..f84244fe916 --- /dev/null +++ b/mysql-test/suite/galera/r/mdev-26175.result @@ -0,0 +1,24 @@ +connection node_2; +connection node_1; +connection node_1; +SET sql_mode="no_zero_date"; +SET GLOBAL wsrep_max_ws_rows=1; +CREATE TABLE t2 (a INT); +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY) ENGINE=InnoDB; +CREATE TRIGGER tgr BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES (0); +INSERT INTO t1 VALUES (0),(1); +ERROR HY000: wsrep_max_ws_rows exceeded +SELECT * FROM t1; +a +SELECT * FROM t2; +a +connection node_2; +SELECT * FROM t1; +a +SELECT * FROM t2; +a +connection node_1; +SET sql_mode=DEFAULT; +SET GLOBAL wsrep_max_ws_rows=DEFAULT; +DROP TRIGGER tgr; +DROP TABLE t1, t2; diff --git a/mysql-test/suite/galera/t/MDEV-30804.cnf b/mysql-test/suite/galera/t/MDEV-30804.cnf new file mode 100644 index 00000000000..9dbd81f758d --- /dev/null +++ b/mysql-test/suite/galera/t/MDEV-30804.cnf @@ -0,0 +1,7 @@ +!include ../galera_2nodes.cnf + +[mysqld.1] +log-bin + +[mysqld.2] +log-bin diff --git a/mysql-test/suite/galera/t/MDEV-30804.test b/mysql-test/suite/galera/t/MDEV-30804.test new file mode 100644 index 00000000000..561953a0578 --- /dev/null +++ b/mysql-test/suite/galera/t/MDEV-30804.test @@ -0,0 +1,21 @@ +# +# Test that transaction requiring two-phase commit and involving +# storage engines not supporting it rolls back with a message. +# + +--source include/galera_cluster.inc +--source include/have_innodb.inc +--source include/have_aria.inc + +CREATE TABLE t (a INT) ENGINE=Aria; +CREATE TABLE t1 (a INT) ENGINE=InnoDB; + +START TRANSACTION; +INSERT INTO t VALUES ('1'); +INSERT INTO t1 VALUES ('1'); + +--error ER_ERROR_DURING_COMMIT +COMMIT; + +DROP TABLE t; +DROP TABLE t1; diff --git a/mysql-test/suite/galera/t/MDEV-30955.test b/mysql-test/suite/galera/t/MDEV-30955.test new file mode 100644 index 00000000000..18577120e83 --- /dev/null +++ b/mysql-test/suite/galera/t/MDEV-30955.test @@ -0,0 +1,70 @@ +# +# MDEV-30955 +# Assertion `thd->mdl_context.is_lock_owner(MDL_key::TABLE, +# table->s->db.str, table->s->table_name.str, MDL_SHARED)' +# failed in close_thread_table() +# + +--source include/galera_cluster.inc + +# +# Test 1: Assertion thd->mdl_context.is_lock_owner() +# failed in close_thread_table() +# +CREATE TABLE t (a CHAR(1) KEY); +START TRANSACTION; +HANDLER t OPEN; + +# +# If bug is present the transaction will be aborted +# through Wsrep_client_service::bf_rollback() and +# release explicit locks too early. Later, during +# THD::cleanup(), table t will be closed and the +# THD is expected to be owner of the MDL lock that +# was just released. +# +--disconnect node_1 + +--connect node_1, 127.0.0.1, root, , test, $NODE_MYPORT_1 +DROP TABLE t; + + +# +# Test 2: Similar issue reproduces also with BACKUP STAGE locks. +# See comments in MDEV-25037 +# + +BACKUP STAGE START; +START TRANSACTION; +--disconnect node_1 +--connect node_1, 127.0.0.1, root, , test, $NODE_MYPORT_1 + + +# +# Test 3: Assertion `!thd->mdl_context.has_locks()' failed +# in do_command() +# + +--connection node_1 +CREATE TABLE t1 (f1 INTEGER PRIMARY KEY); +CREATE TABLE t2 (f1 INTEGER PRIMARY KEY); + +--let $bf_count = `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.global_status WHERE VARIABLE_NAME = 'wsrep_local_bf_aborts'` + +START TRANSACTION; +INSERT INTO t1 VALUES(1); +HANDLER t2 OPEN; + +--connection node_2 +INSERT INTO t1 VALUES(1); + +--connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1 +--connection node_1a +--let $wait_condition = SELECT VARIABLE_VALUE = $bf_count + 1 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_local_bf_aborts' +--source include/wait_condition.inc + +--connection node_1 +--error ER_LOCK_DEADLOCK +COMMIT; + +DROP TABLE t1,t2; diff --git a/mysql-test/suite/galera/t/galera_sequences.test b/mysql-test/suite/galera/t/galera_sequences.test index faa3b46d2a7..613823d83e9 100644 --- a/mysql-test/suite/galera/t/galera_sequences.test +++ b/mysql-test/suite/galera/t/galera_sequences.test @@ -1,4 +1,5 @@ --source include/galera_cluster.inc +--source include/have_innodb.inc # # MDEV-19353 : Alter Sequence do not replicate to another nodes with in Galera Cluster diff --git a/mysql-test/suite/galera/t/galera_var_replicate_aria_on.test b/mysql-test/suite/galera/t/galera_var_replicate_aria_on.test index c3bc53ee17f..ac9a79e6196 100644 --- a/mysql-test/suite/galera/t/galera_var_replicate_aria_on.test +++ b/mysql-test/suite/galera/t/galera_var_replicate_aria_on.test @@ -85,18 +85,15 @@ SELECT * FROM t1; DROP TABLE t1; # -# Transaction +# Preparation for next tests # --connection node_1 SET GLOBAL wsrep_sync_wait=15; CREATE TABLE t1 (f1 INTEGER NOT NULL PRIMARY KEY) ENGINE=Aria; CREATE TABLE t2 (f1 INTEGER NOT NULL PRIMARY KEY) ENGINE=InnoDB; -SET AUTOCOMMIT=OFF; -START TRANSACTION; INSERT INTO t1 VALUES (1); INSERT INTO t2 VALUES (1); -COMMIT; --connection node_2 SET GLOBAL wsrep_sync_wait=15; @@ -108,6 +105,7 @@ SELECT COUNT(*) AS EXPECT_1 FROM t2; # --connection node_1 +SET AUTOCOMMIT=OFF; START TRANSACTION; INSERT INTO t1 VALUES (2); INSERT INTO t2 VALUES (2); @@ -138,6 +136,8 @@ INSERT INTO t2 VALUES (1); INSERT INTO t1 VALUES (1); --connection node_1 + +--error ER_ERROR_DURING_COMMIT COMMIT; DROP TABLE t1,t2; diff --git a/mysql-test/suite/galera/t/mdev-26175.test b/mysql-test/suite/galera/t/mdev-26175.test new file mode 100644 index 00000000000..1a3f1153e03 --- /dev/null +++ b/mysql-test/suite/galera/t/mdev-26175.test @@ -0,0 +1,27 @@ +--source include/galera_cluster.inc +--source include/have_innodb.inc + +# +# MDEV-26175 : Assertion `! thd->in_sub_stmt' failed in bool trans_rollback_stmt(THD*) +# +--connection node_1 +SET sql_mode="no_zero_date"; +SET GLOBAL wsrep_max_ws_rows=1; +CREATE TABLE t2 (a INT); +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY) ENGINE=InnoDB; +CREATE TRIGGER tgr BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES (0); + +--error ER_ERROR_DURING_COMMIT +INSERT INTO t1 VALUES (0),(1); +SELECT * FROM t1; +SELECT * FROM t2; + +--connection node_2 +SELECT * FROM t1; +SELECT * FROM t2; + +--connection node_1 +SET sql_mode=DEFAULT; +SET GLOBAL wsrep_max_ws_rows=DEFAULT; +DROP TRIGGER tgr; +DROP TABLE t1, t2; diff --git a/mysql-test/suite/galera_sr/r/MDEV-30862.result b/mysql-test/suite/galera_sr/r/MDEV-30862.result new file mode 100644 index 00000000000..43da77f24df --- /dev/null +++ b/mysql-test/suite/galera_sr/r/MDEV-30862.result @@ -0,0 +1,11 @@ +connection node_2; +connection node_1; +SET autocommit=0; +SET SESSION wsrep_trx_fragment_size=1; +CREATE TABLE t2 SELECT seq FROM seq_1_to_50; +ERROR 42000: CREATE TABLE AS SELECT is not supported with streaming replication +CREATE TABLE t1 (f1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY); +INSERT INTO t1 VALUES(DEFAULT); +CREATE TABLE t2 SELECT * FROM t1; +ERROR 42000: CREATE TABLE AS SELECT is not supported with streaming replication +DROP TABLE t1; diff --git a/mysql-test/suite/galera_sr/t/MDEV-30862.test b/mysql-test/suite/galera_sr/t/MDEV-30862.test new file mode 100644 index 00000000000..6be77b4d71b --- /dev/null +++ b/mysql-test/suite/galera_sr/t/MDEV-30862.test @@ -0,0 +1,24 @@ +# +# MDEV-30862 Assertion `mode_ == m_high_priority' failed in +# void wsrep::client_state::after_applying() +# + +--source include/galera_cluster.inc +--source include/have_sequence.inc + +SET autocommit=0; +SET SESSION wsrep_trx_fragment_size=1; +--error ER_NOT_ALLOWED_COMMAND +CREATE TABLE t2 SELECT seq FROM seq_1_to_50; + + +# +# Same test without using seq +# +CREATE TABLE t1 (f1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY); +INSERT INTO t1 VALUES(DEFAULT); +--error ER_NOT_ALLOWED_COMMAND +CREATE TABLE t2 SELECT * FROM t1; + + +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/r/default_row_format_alter.result b/mysql-test/suite/innodb/r/default_row_format_alter.result index 42cbab8a5f2..33936b59003 100644 --- a/mysql-test/suite/innodb/r/default_row_format_alter.result +++ b/mysql-test/suite/innodb/r/default_row_format_alter.result @@ -129,5 +129,25 @@ SELECT ROW_FORMAT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1'; ROW_FORMAT Dynamic DROP TABLE t1; +# +# MDEV-31025 Redundant table alter fails when fixed column +# stored externally +# +set @old_sql_mode = @@sql_mode; +SET @@sql_mode=''; +CREATE TABLE t1(pk INT,c CHAR(255),c2 CHAR(255),c3 CHAR(255), +c4 char(255), c5 char(255), c6 char(255), +c7 char(255), c8 char(255), primary key(pk) +)Engine=InnoDB character set utf32 ROW_FORMAT=REDUNDANT; +INSERT INTO t1(pk, c) VALUES (1, repeat('a', 255)); +ALTER TABLE t1 FORCE; +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +SELECT LENGTH(c) FROM t1; +LENGTH(c) +1020 +DROP TABLE t1; +set @@sql_mode = @old_sql_mode; # End of 10.4 tests SET GLOBAL innodb_default_row_format = @row_format; diff --git a/mysql-test/suite/innodb/r/innodb_skip_innodb_is_tables.result b/mysql-test/suite/innodb/r/innodb_skip_innodb_is_tables.result index e919f0dee0b..d948472e965 100644 --- a/mysql-test/suite/innodb/r/innodb_skip_innodb_is_tables.result +++ b/mysql-test/suite/innodb/r/innodb_skip_innodb_is_tables.result @@ -159,8 +159,8 @@ trx_commits_insert_update transaction 0 NULL NULL NULL 0 NULL NULL NULL NULL NUL trx_rollbacks transaction 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Number of transactions rolled back trx_rollbacks_savepoint transaction 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Number of transactions rolled back to savepoint trx_rseg_history_len transaction 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 value Length of the TRX_RSEG_HISTORY list -trx_undo_slots_used transaction 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Number of undo slots used -trx_undo_slots_cached transaction 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Number of undo slots cached +trx_undo_slots_used transaction 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 value Number of undo slots used +trx_undo_slots_cached transaction 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 value Number of undo slots cached trx_rseg_current_size transaction 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 value Current rollback segment size in pages purge_del_mark_records purge 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Number of delete-marked rows purged purge_upd_exist_or_extern_records purge 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Number of purges on updates of existing records and updates on delete marked record with externally stored field diff --git a/mysql-test/suite/innodb/r/sql_mode_pad_char_to_full_length.result b/mysql-test/suite/innodb/r/sql_mode_pad_char_to_full_length.result new file mode 100644 index 00000000000..09c1cf57497 --- /dev/null +++ b/mysql-test/suite/innodb/r/sql_mode_pad_char_to_full_length.result @@ -0,0 +1,51 @@ +SET default_storage_engine=InnoDB; +# +# Start of 10.4 tests +# +# +# MDEV-28190 sql_mode makes MDEV-371 virtual column expressions nondeterministic +# +# +# MDEV-28190 sql_mode makes MDEV-371 virtual column expressions nondeterministic +# +CREATE TABLE t1 (a INT,b CHAR(20)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` char(20) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +CREATE UNIQUE INDEX bi USING HASH ON t1 (b); +INSERT INTO t1 VALUES (0,0); +SET sql_mode='pad_char_to_full_length'; +DELETE FROM t1; +DROP TABLE t1; +SET sql_mode=''; +CREATE TABLE t1 (a INT,b CHAR(20)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL, + `b` char(20) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +CREATE UNIQUE INDEX bi USING HASH ON t1 (b); +SET sql_mode='pad_char_to_full_length'; +INSERT INTO t1 VALUES (0,0); +DELETE FROM t1; +DROP TABLE t1; +SET sql_mode=''; +CREATE OR REPLACE TABLE t1 (a CHAR(20),b CHAR(20)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` char(20) DEFAULT NULL, + `b` char(20) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +CREATE UNIQUE INDEX bi USING HASH ON t1 (b); +INSERT INTO t1 VALUES (0,0); +SET sql_mode='pad_char_to_full_length'; +DELETE FROM t1; +DROP TABLE t1; +# +# End of 10.4 tests +# diff --git a/mysql-test/suite/innodb/t/default_row_format_alter.test b/mysql-test/suite/innodb/t/default_row_format_alter.test index f5dd246efb5..5f2170454f3 100644 --- a/mysql-test/suite/innodb/t/default_row_format_alter.test +++ b/mysql-test/suite/innodb/t/default_row_format_alter.test @@ -150,6 +150,23 @@ ALTER TABLE t1 DROP b; SELECT ROW_FORMAT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1'; DROP TABLE t1; +--echo # +--echo # MDEV-31025 Redundant table alter fails when fixed column +--echo # stored externally +--echo # +set @old_sql_mode = @@sql_mode; +SET @@sql_mode=''; +CREATE TABLE t1(pk INT,c CHAR(255),c2 CHAR(255),c3 CHAR(255), + c4 char(255), c5 char(255), c6 char(255), + c7 char(255), c8 char(255), primary key(pk) + )Engine=InnoDB character set utf32 ROW_FORMAT=REDUNDANT; +INSERT INTO t1(pk, c) VALUES (1, repeat('a', 255)); +ALTER TABLE t1 FORCE; +CHECK TABLE t1; +SELECT LENGTH(c) FROM t1; +DROP TABLE t1; +set @@sql_mode = @old_sql_mode; + --echo # End of 10.4 tests SET GLOBAL innodb_default_row_format = @row_format; diff --git a/mysql-test/suite/innodb/t/sql_mode_pad_char_to_full_length.test b/mysql-test/suite/innodb/t/sql_mode_pad_char_to_full_length.test new file mode 100644 index 00000000000..ba286c744d9 --- /dev/null +++ b/mysql-test/suite/innodb/t/sql_mode_pad_char_to_full_length.test @@ -0,0 +1,18 @@ +--source include/have_innodb.inc + +SET default_storage_engine=InnoDB; + +--echo # +--echo # Start of 10.4 tests +--echo # + +--echo # +--echo # MDEV-28190 sql_mode makes MDEV-371 virtual column expressions nondeterministic +--echo # + +--source include/sql_mode_pad_char_to_full_length.inc + + +--echo # +--echo # End of 10.4 tests +--echo # diff --git a/mysql-test/suite/innodb_fts/r/concurrent_insert.result b/mysql-test/suite/innodb_fts/r/concurrent_insert.result index 2335982816b..bc47511b046 100644 --- a/mysql-test/suite/innodb_fts/r/concurrent_insert.result +++ b/mysql-test/suite/innodb_fts/r/concurrent_insert.result @@ -19,7 +19,7 @@ INSERT INTO t2 VALUES('mariadb'); connection default; SET @saved_dbug = @@GLOBAL.debug_dbug; SET GLOBAL debug_dbug ='+d,fts_instrument_sync_request,ib_optimize_wq_hang'; -SET DEBUG_SYNC= 'fts_sync_end +SET DEBUG_SYNC= 'fts_instrument_sync_request SIGNAL drop_index_start WAIT_FOR sync_op'; INSERT INTO t1 VALUES('Keyword'); connect con1,localhost,root,,,; diff --git a/mysql-test/suite/innodb_fts/r/sync.result b/mysql-test/suite/innodb_fts/r/sync.result index 74a5d2f13fb..928efffdb21 100644 --- a/mysql-test/suite/innodb_fts/r/sync.result +++ b/mysql-test/suite/innodb_fts/r/sync.result @@ -11,19 +11,19 @@ INSERT INTO t1(title) VALUES('database'); connection con1; SET @old_dbug = @@SESSION.debug_dbug; SET debug_dbug = '+d,fts_instrument_sync_debug'; -SET DEBUG_SYNC= 'fts_sync_end SIGNAL written WAIT_FOR selected'; +SET DEBUG_SYNC= 'fts_write_node SIGNAL written WAIT_FOR selected'; INSERT INTO t1(title) VALUES('mysql database'); connection default; SET DEBUG_SYNC= 'now WAIT_FOR written'; SET GLOBAL innodb_ft_aux_table="test/t1"; SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE; WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION -SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE; -WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION database 2 3 2 2 0 database 2 3 2 3 6 mysql 1 3 2 1 0 mysql 1 3 2 3 0 +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE; +WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION SET GLOBAL innodb_ft_aux_table=default; SELECT * FROM t1 WHERE MATCH(title) AGAINST('mysql database'); FTS_DOC_ID title @@ -59,7 +59,7 @@ INSERT INTO t1(title) VALUES('mysql'); INSERT INTO t1(title) VALUES('database'); connection con1; SET debug_dbug = '+d,fts_instrument_sync_debug'; -SET DEBUG_SYNC= 'fts_sync_end SIGNAL written WAIT_FOR inserted'; +SET DEBUG_SYNC= 'fts_write_node SIGNAL written WAIT_FOR inserted'; INSERT INTO t1(title) VALUES('mysql database'); connection default; SET DEBUG_SYNC= 'now WAIT_FOR written'; @@ -70,14 +70,14 @@ SET debug_dbug = @old_dbug; SET GLOBAL innodb_ft_aux_table="test/t1"; SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE; WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION -database 4 4 1 4 6 -mysql 4 4 1 4 0 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE; WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION database 2 3 2 2 0 database 2 3 2 3 6 -mysql 1 3 2 1 0 -mysql 1 3 2 3 0 +database 4 4 1 4 6 +mysql 1 4 3 1 0 +mysql 1 4 3 3 0 +mysql 1 4 3 4 0 SET GLOBAL innodb_ft_aux_table=default; SELECT * FROM t1 WHERE MATCH(title) AGAINST('mysql database'); FTS_DOC_ID title diff --git a/mysql-test/suite/innodb_fts/r/sync_block.result b/mysql-test/suite/innodb_fts/r/sync_block.result new file mode 100644 index 00000000000..65bee127e80 --- /dev/null +++ b/mysql-test/suite/innodb_fts/r/sync_block.result @@ -0,0 +1,83 @@ +SET @old_log_output = @@global.log_output; +SET @old_slow_query_log = @@global.slow_query_log; +SET @old_general_log = @@global.general_log; +SET @old_long_query_time = @@global.long_query_time; +SET @old_debug = @@global.debug_dbug; +SET GLOBAL log_output = 'TABLE'; +SET GLOBAL general_log = 1; +SET GLOBAL slow_query_log = 1; +SET GLOBAL long_query_time = 1; +connect con1,localhost,root,,; +connect con2,localhost,root,,; +connection default; +# Case 1: Sync blocks DML(insert) on the same table. +CREATE TABLE t1 ( +FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, +title VARCHAR(200), +FULLTEXT(title) +) ENGINE = InnoDB; +connection con1; +SET GLOBAL debug_dbug='+d,fts_instrument_sync_debug,fts_instrument_sync_sleep'; +SET DEBUG_SYNC= 'fts_sync_begin SIGNAL begin WAIT_FOR continue'; +INSERT INTO t1(title) VALUES('mysql database'); +connection con2; +SET DEBUG_SYNC= 'now WAIT_FOR begin'; +SELECT * FROM t1 WHERE MATCH(title) AGAINST('mysql database'); +connection default; +SET DEBUG_SYNC= 'now SIGNAL continue'; +connection con1; +/* connection con1 */ INSERT INTO t1(title) VALUES('mysql database'); +connection con2; +/* conneciton con2 */ SELECT * FROM t1 WHERE MATCH(title) AGAINST('mysql database'); +FTS_DOC_ID title +connection default; +# make con1 & con2 show up in mysql.slow_log +SELECT SLEEP(2); +SLEEP(2) +0 +# slow log results should only contain INSERT INTO t1. +SELECT sql_text FROM mysql.slow_log WHERE query_time >= '00:00:02'; +sql_text +INSERT INTO t1(title) VALUES('mysql database') +SET GLOBAL debug_dbug = @old_debug; +TRUNCATE TABLE mysql.slow_log; +DROP TABLE t1; +# Case 2: Sync blocks DML(insert) on other tables. +CREATE TABLE t1 ( +FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, +title VARCHAR(200), +FULLTEXT(title) +) ENGINE = InnoDB; +CREATE TABLE t2(id INT); +connection con1; +SET GLOBAL debug_dbug='+d,fts_instrument_sync_request,fts_instrument_sync_sleep'; +SET DEBUG_SYNC= 'fts_instrument_sync_request SIGNAL begin WAIT_FOR continue'; +INSERT INTO t1(title) VALUES('mysql database'); +connection con2; +SET DEBUG_SYNC= 'now WAIT_FOR begin'; +INSERT INTO t2 VALUES(1); +connection default; +SET DEBUG_SYNC= 'now SIGNAL continue'; +connection con1; +/* connection con1 */ INSERT INTO t1(title) VALUES('mysql database'); +connection con2; +/* conneciton con2 */ INSERT INTO t2 VALUES(1); +connection default; +SET DEBUG_SYNC = 'RESET'; +# make con1 & con2 show up in mysql.slow_log +SELECT SLEEP(2); +SLEEP(2) +0 +# slow log results should be empty here. +SELECT sql_text FROM mysql.slow_log WHERE query_time >= '00:00:02'; +sql_text +SET GLOBAL debug_dbug = @old_debug; +TRUNCATE TABLE mysql.slow_log; +DROP TABLE t1,t2; +disconnect con1; +disconnect con2; +# Restore slow log settings. +SET GLOBAL log_output = @old_log_output; +SET GLOBAL general_log = @old_general_log; +SET GLOBAL slow_query_log = @old_slow_query_log; +SET GLOBAL long_query_time = @old_long_query_time; diff --git a/mysql-test/suite/innodb_fts/t/concurrent_insert.test b/mysql-test/suite/innodb_fts/t/concurrent_insert.test index b6991f6e503..9b4d9517b1a 100644 --- a/mysql-test/suite/innodb_fts/t/concurrent_insert.test +++ b/mysql-test/suite/innodb_fts/t/concurrent_insert.test @@ -31,7 +31,7 @@ INSERT INTO t2 VALUES('mariadb'); connection default; SET @saved_dbug = @@GLOBAL.debug_dbug; SET GLOBAL debug_dbug ='+d,fts_instrument_sync_request,ib_optimize_wq_hang'; -SET DEBUG_SYNC= 'fts_sync_end +SET DEBUG_SYNC= 'fts_instrument_sync_request SIGNAL drop_index_start WAIT_FOR sync_op'; send INSERT INTO t1 VALUES('Keyword'); diff --git a/mysql-test/suite/innodb_fts/t/sync.test b/mysql-test/suite/innodb_fts/t/sync.test index 7c5c835f2ee..168309a5c92 100644 --- a/mysql-test/suite/innodb_fts/t/sync.test +++ b/mysql-test/suite/innodb_fts/t/sync.test @@ -27,7 +27,7 @@ connection con1; SET @old_dbug = @@SESSION.debug_dbug; SET debug_dbug = '+d,fts_instrument_sync_debug'; -SET DEBUG_SYNC= 'fts_sync_end SIGNAL written WAIT_FOR selected'; +SET DEBUG_SYNC= 'fts_write_node SIGNAL written WAIT_FOR selected'; send INSERT INTO t1(title) VALUES('mysql database'); @@ -74,7 +74,7 @@ connection con1; SET debug_dbug = '+d,fts_instrument_sync_debug'; -SET DEBUG_SYNC= 'fts_sync_end SIGNAL written WAIT_FOR inserted'; +SET DEBUG_SYNC= 'fts_write_node SIGNAL written WAIT_FOR inserted'; send INSERT INTO t1(title) VALUES('mysql database'); diff --git a/mysql-test/suite/innodb_fts/t/sync_block.test b/mysql-test/suite/innodb_fts/t/sync_block.test new file mode 100644 index 00000000000..895d2ba8a59 --- /dev/null +++ b/mysql-test/suite/innodb_fts/t/sync_block.test @@ -0,0 +1,124 @@ +# +# BUG#22516559 MYSQL INSTANCE STALLS WHEN SYNCING FTS INDEX +# + +--source include/have_innodb.inc +--source include/have_debug.inc +--source include/have_debug_sync.inc +--source include/have_log_bin.inc +--source include/count_sessions.inc + +SET @old_log_output = @@global.log_output; +SET @old_slow_query_log = @@global.slow_query_log; +SET @old_general_log = @@global.general_log; +SET @old_long_query_time = @@global.long_query_time; +SET @old_debug = @@global.debug_dbug; + +SET GLOBAL log_output = 'TABLE'; +SET GLOBAL general_log = 1; +SET GLOBAL slow_query_log = 1; +SET GLOBAL long_query_time = 1; + +connect (con1,localhost,root,,); +connect (con2,localhost,root,,); +connection default; + +--echo # Case 1: Sync blocks DML(insert) on the same table. +CREATE TABLE t1 ( + FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + title VARCHAR(200), + FULLTEXT(title) +) ENGINE = InnoDB; + +connection con1; + +SET GLOBAL debug_dbug='+d,fts_instrument_sync_debug,fts_instrument_sync_sleep'; + +SET DEBUG_SYNC= 'fts_sync_begin SIGNAL begin WAIT_FOR continue'; + +send INSERT INTO t1(title) VALUES('mysql database'); + +connection con2; + +SET DEBUG_SYNC= 'now WAIT_FOR begin'; + +send SELECT * FROM t1 WHERE MATCH(title) AGAINST('mysql database'); + +connection default; +SET DEBUG_SYNC= 'now SIGNAL continue'; + +connection con1; +--echo /* connection con1 */ INSERT INTO t1(title) VALUES('mysql database'); +--reap + +connection con2; +--echo /* conneciton con2 */ SELECT * FROM t1 WHERE MATCH(title) AGAINST('mysql database'); +--reap + +connection default; +-- echo # make con1 & con2 show up in mysql.slow_log +SELECT SLEEP(2); +-- echo # slow log results should only contain INSERT INTO t1. +SELECT sql_text FROM mysql.slow_log WHERE query_time >= '00:00:02'; + +SET GLOBAL debug_dbug = @old_debug; +TRUNCATE TABLE mysql.slow_log; + +DROP TABLE t1; + +--echo # Case 2: Sync blocks DML(insert) on other tables. +CREATE TABLE t1 ( + FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + title VARCHAR(200), + FULLTEXT(title) +) ENGINE = InnoDB; + +CREATE TABLE t2(id INT); + +connection con1; + +SET GLOBAL debug_dbug='+d,fts_instrument_sync_request,fts_instrument_sync_sleep'; + +SET DEBUG_SYNC= 'fts_instrument_sync_request SIGNAL begin WAIT_FOR continue'; + +send INSERT INTO t1(title) VALUES('mysql database'); + +connection con2; + +SET DEBUG_SYNC= 'now WAIT_FOR begin'; + +send INSERT INTO t2 VALUES(1); + +connection default; +SET DEBUG_SYNC= 'now SIGNAL continue'; + +connection con1; +--echo /* connection con1 */ INSERT INTO t1(title) VALUES('mysql database'); +--reap + +connection con2; +--echo /* conneciton con2 */ INSERT INTO t2 VALUES(1); +--reap + +connection default; +SET DEBUG_SYNC = 'RESET'; +-- echo # make con1 & con2 show up in mysql.slow_log +SELECT SLEEP(2); +-- echo # slow log results should be empty here. +SELECT sql_text FROM mysql.slow_log WHERE query_time >= '00:00:02'; + +SET GLOBAL debug_dbug = @old_debug; +TRUNCATE TABLE mysql.slow_log; + +DROP TABLE t1,t2; + +disconnect con1; +disconnect con2; + +--source include/wait_until_count_sessions.inc + +-- echo # Restore slow log settings. +SET GLOBAL log_output = @old_log_output; +SET GLOBAL general_log = @old_general_log; +SET GLOBAL slow_query_log = @old_slow_query_log; +SET GLOBAL long_query_time = @old_long_query_time; diff --git a/mysql-test/suite/mariabackup/aria_log_dir_path.result b/mysql-test/suite/mariabackup/aria_log_dir_path.result new file mode 100644 index 00000000000..1a877321bbe --- /dev/null +++ b/mysql-test/suite/mariabackup/aria_log_dir_path.result @@ -0,0 +1,41 @@ +# +# MDEV-30968 mariadb-backup does not copy Aria logs if aria_log_dir_path is used +# +# Restart mariadbd with the test specific parameters +# restart: --aria-log-file-size=8388608 --aria-log-purge-type=external --loose-aria-log-dir-path=MYSQLTEST_VARDIR/tmp/backup_aria_log_dir_path +# Create and populate an Aria table (and Aria logs) +CREATE TABLE t1 (id INT, txt LONGTEXT) ENGINE=Aria; +BEGIN NOT ATOMIC +FOR id IN 0..9 DO +INSERT INTO test.t1 (id, txt) VALUES (id, REPEAT(id,1024*1024)); +END FOR; +END; +$$ +# Testing aria log files before --backup +SET @@global.aria_checkpoint_interval=DEFAULT /*Force checkpoint*/; +SHOW ENGINE aria logs; +Type Name Status +Aria aria_log.00000001 free +Aria aria_log.00000002 in use +# mariadb-backup --backup +# mariadb-backup --prepare +# shutdown server +# remove datadir +# remove aria-log-dir-path +# mariadb-backup --copy-back +# with parameters: --defaults-file=MYSQLTEST_VARDIR/my.cnf --copy-back --datadir=MYSQLTEST_VARDIR/mysqld.1/data/ --target-dir=MYSQLTEST_VARDIR/tmp/backup --parallel=2 --throttle=1 --aria-log-dir-path=MYSQLTEST_VARDIR/tmp/backup_aria_log_dir_path +# starting server +# restart: --aria-log-file-size=8388608 --aria-log-purge-type=external --loose-aria-log-dir-path=MYSQLTEST_VARDIR/tmp/backup_aria_log_dir_path +# Check that the table is there after --copy-back +SELECT COUNT(*) from t1; +COUNT(*) +10 +DROP TABLE t1; +# Testing aria log files after --copy-back +SET @@global.aria_checkpoint_interval=DEFAULT /*Force checkpoint*/; +SHOW ENGINE aria logs; +Type Name Status +Aria aria_log.00000001 free +Aria aria_log.00000002 in use +# Restarting mariadbd with default parameters +# restart diff --git a/mysql-test/suite/mariabackup/aria_log_dir_path.test b/mysql-test/suite/mariabackup/aria_log_dir_path.test new file mode 100644 index 00000000000..0178cd4eae5 --- /dev/null +++ b/mysql-test/suite/mariabackup/aria_log_dir_path.test @@ -0,0 +1,105 @@ +--source include/have_maria.inc + +--echo # +--echo # MDEV-30968 mariadb-backup does not copy Aria logs if aria_log_dir_path is used +--echo # + +--let $datadir=`SELECT @@datadir` +--let $targetdir=$MYSQLTEST_VARDIR/tmp/backup + +if ($ARIA_LOGDIR_MARIADB == '') +{ + --let $ARIA_LOGDIR_MARIADB=$MYSQLTEST_VARDIR/tmp/backup_aria_log_dir_path +} + +if ($ARIA_LOGDIR_FS == '') +{ + --let $ARIA_LOGDIR_FS=$MYSQLTEST_VARDIR/tmp/backup_aria_log_dir_path +} + +--let $server_parameters=--aria-log-file-size=8388608 --aria-log-purge-type=external --loose-aria-log-dir-path=$ARIA_LOGDIR_MARIADB + + +--echo # Restart mariadbd with the test specific parameters +--mkdir $ARIA_LOGDIR_FS +--let $restart_parameters=$server_parameters +--source include/restart_mysqld.inc + + +--echo # Create and populate an Aria table (and Aria logs) +CREATE TABLE t1 (id INT, txt LONGTEXT) ENGINE=Aria; +DELIMITER $$; +BEGIN NOT ATOMIC + FOR id IN 0..9 DO + INSERT INTO test.t1 (id, txt) VALUES (id, REPEAT(id,1024*1024)); + END FOR; +END; +$$ +DELIMITER ;$$ + + +--echo # Testing aria log files before --backup +SET @@global.aria_checkpoint_interval=DEFAULT /*Force checkpoint*/; +--file_exists $ARIA_LOGDIR_FS/aria_log_control +--file_exists $ARIA_LOGDIR_FS/aria_log.00000001 +--file_exists $ARIA_LOGDIR_FS/aria_log.00000002 +--error 1 +--file_exists $ARIA_LOGDIR_FS/aria_log.00000003 +--replace_regex /Size +[0-9]+ ; .+aria_log/aria_log/ +SHOW ENGINE aria logs; + + +--echo # mariadb-backup --backup +--disable_result_log +--mkdir $targetdir +--exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --backup --target-dir=$targetdir +--enable_result_log + + +--echo # mariadb-backup --prepare +--disable_result_log +--exec $XTRABACKUP --prepare --target-dir=$targetdir +--enable_result_log + + +--echo # shutdown server +--disable_result_log +--source include/shutdown_mysqld.inc +--echo # remove datadir +--rmdir $datadir +--echo # remove aria-log-dir-path +--rmdir $ARIA_LOGDIR_FS + +--echo # mariadb-backup --copy-back +--let $mariadb_backup_parameters=--defaults-file=$MYSQLTEST_VARDIR/my.cnf --copy-back --datadir=$datadir --target-dir=$targetdir --parallel=2 --throttle=1 --aria-log-dir-path=$ARIA_LOGDIR_MARIADB +--replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--exec echo "# with parameters: $mariadb_backup_parameters" +--exec $XTRABACKUP $mariadb_backup_parameters + +--echo # starting server +--let $restart_parameters=$server_parameters +--source include/start_mysqld.inc +--enable_result_log +--rmdir $targetdir + + +--echo # Check that the table is there after --copy-back +SELECT COUNT(*) from t1; +DROP TABLE t1; + + +--echo # Testing aria log files after --copy-back +SET @@global.aria_checkpoint_interval=DEFAULT /*Force checkpoint*/; +--file_exists $ARIA_LOGDIR_FS/aria_log_control +--file_exists $ARIA_LOGDIR_FS/aria_log.00000001 +--file_exists $ARIA_LOGDIR_FS/aria_log.00000002 +--error 1 +--file_exists $ARIA_LOGDIR_FS/aria_log.00000003 +--replace_regex /Size +[0-9]+ ; .+aria_log/aria_log/ +SHOW ENGINE aria logs; + + +--echo # Restarting mariadbd with default parameters +--let $restart_parameters= +--source include/restart_mysqld.inc +--rmdir $ARIA_LOGDIR_FS diff --git a/mysql-test/suite/mariabackup/aria_log_dir_path_rel.result b/mysql-test/suite/mariabackup/aria_log_dir_path_rel.result new file mode 100644 index 00000000000..7fef26096e0 --- /dev/null +++ b/mysql-test/suite/mariabackup/aria_log_dir_path_rel.result @@ -0,0 +1,41 @@ +# +# MDEV-30968 mariadb-backup does not copy Aria logs if aria_log_dir_path is used +# +# Restart mariadbd with the test specific parameters +# restart: --aria-log-file-size=8388608 --aria-log-purge-type=external --loose-aria-log-dir-path=../../tmp/backup_aria_log_dir_path_rel +# Create and populate an Aria table (and Aria logs) +CREATE TABLE t1 (id INT, txt LONGTEXT) ENGINE=Aria; +BEGIN NOT ATOMIC +FOR id IN 0..9 DO +INSERT INTO test.t1 (id, txt) VALUES (id, REPEAT(id,1024*1024)); +END FOR; +END; +$$ +# Testing aria log files before --backup +SET @@global.aria_checkpoint_interval=DEFAULT /*Force checkpoint*/; +SHOW ENGINE aria logs; +Type Name Status +Aria aria_log.00000001 free +Aria aria_log.00000002 in use +# mariadb-backup --backup +# mariadb-backup --prepare +# shutdown server +# remove datadir +# remove aria-log-dir-path +# mariadb-backup --copy-back +# with parameters: --defaults-file=MYSQLTEST_VARDIR/my.cnf --copy-back --datadir=MYSQLTEST_VARDIR/mysqld.1/data/ --target-dir=MYSQLTEST_VARDIR/tmp/backup --parallel=2 --throttle=1 --aria-log-dir-path=../../tmp/backup_aria_log_dir_path_rel +# starting server +# restart: --aria-log-file-size=8388608 --aria-log-purge-type=external --loose-aria-log-dir-path=../../tmp/backup_aria_log_dir_path_rel +# Check that the table is there after --copy-back +SELECT COUNT(*) from t1; +COUNT(*) +10 +DROP TABLE t1; +# Testing aria log files after --copy-back +SET @@global.aria_checkpoint_interval=DEFAULT /*Force checkpoint*/; +SHOW ENGINE aria logs; +Type Name Status +Aria aria_log.00000001 free +Aria aria_log.00000002 in use +# Restarting mariadbd with default parameters +# restart diff --git a/mysql-test/suite/mariabackup/aria_log_dir_path_rel.test b/mysql-test/suite/mariabackup/aria_log_dir_path_rel.test new file mode 100644 index 00000000000..c8169959929 --- /dev/null +++ b/mysql-test/suite/mariabackup/aria_log_dir_path_rel.test @@ -0,0 +1,4 @@ +--let $ARIA_LOGDIR_MARIADB=../../tmp/backup_aria_log_dir_path_rel +--let $ARIA_LOGDIR_FS=$MYSQLTEST_VARDIR/tmp/backup_aria_log_dir_path_rel + +--source aria_log_dir_path.test diff --git a/mysql-test/suite/rpl/r/rpl_xa_prepare_gtid_fail.result b/mysql-test/suite/rpl/r/rpl_xa_prepare_gtid_fail.result new file mode 100644 index 00000000000..f3fecbda349 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_xa_prepare_gtid_fail.result @@ -0,0 +1,51 @@ +include/master-slave.inc +[connection master] +connection slave; +include/stop_slave.inc +change master to master_use_gtid=slave_pos; +set @@global.slave_parallel_threads= 4; +set @@global.slave_parallel_mode= optimistic; +set @@global.gtid_strict_mode=ON; +set sql_log_bin= 0; +alter table mysql.gtid_slave_pos engine=innodb; +call mtr.add_suppression("Deadlock found.*"); +set sql_log_bin= 1; +include/start_slave.inc +connection master; +create table t1 (a int primary key, b int) engine=innodb; +insert t1 values (1,1); +include/save_master_gtid.inc +connection slave; +include/sync_with_master_gtid.inc +include/stop_slave.inc +set @@global.innodb_lock_wait_timeout= 1; +connection master; +set @@session.gtid_seq_no=100; +xa start '1'; +update t1 set b=b+10 where a=1; +xa end '1'; +xa prepare '1'; +xa commit '1'; +include/save_master_gtid.inc +connection slave; +connection slave1; +BEGIN; +SELECT * FROM mysql.gtid_slave_pos WHERE seq_no=100 FOR UPDATE; +domain_id sub_id server_id seq_no +connection slave; +include/start_slave.inc +include/wait_for_slave_sql_error.inc [errno=1942,1213] +connection slave1; +ROLLBACK; +# Cleanup +connection master; +drop table t1; +connection slave; +include/stop_slave.inc +set @@global.gtid_slave_pos= "0-1-100"; +set @@global.slave_parallel_threads= 0; +set @@global.gtid_strict_mode= 0; +set @@global.innodb_lock_wait_timeout= 50; +include/start_slave.inc +include/rpl_end.inc +# End of rpl_xa_prepare_gtid_fail.test diff --git a/mysql-test/suite/rpl/t/rpl_xa_prepare_gtid_fail.test b/mysql-test/suite/rpl/t/rpl_xa_prepare_gtid_fail.test new file mode 100644 index 00000000000..8042b355754 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_xa_prepare_gtid_fail.test @@ -0,0 +1,106 @@ +# +# When handling the replication of an XA PREPARE, the commit phase is +# bifurcated. First, the prepare is handled by the relevant storage engines. +# Then second,the GTID slave state is updated as a separate autocommit +# transaction. If the second stage fails, i.e. we are unable to update the +# GTID slave state, then the slave should immediately quit in error, without +# retry. +# +# This tests validates the above behavior by simulating a deadlock on the +# GTID slave state table during the second part of XA PREPARE's commit, to +# ensure that the appropriate error is reported and the transaction was never +# retried. +# +# +# References +# MDEV-31038: Parallel Replication Breaks if XA PREPARE Fails Updating Slave +# GTID State +# +source include/master-slave.inc; +source include/have_binlog_format_row.inc; +source include/have_innodb.inc; + +--connection slave +--source include/stop_slave.inc + +--let $save_par_thds= `SELECT @@global.slave_parallel_threads` +--let $save_strict_mode= `SELECT @@global.gtid_strict_mode` +--let $save_innodb_lock_wait_timeout= `SELECT @@global.innodb_lock_wait_timeout` + +change master to master_use_gtid=slave_pos; +set @@global.slave_parallel_threads= 4; +set @@global.slave_parallel_mode= optimistic; +set @@global.gtid_strict_mode=ON; + +set sql_log_bin= 0; +alter table mysql.gtid_slave_pos engine=innodb; +call mtr.add_suppression("Deadlock found.*"); +set sql_log_bin= 1; +--source include/start_slave.inc + +--connection master +let $datadir= `select @@datadir`; +create table t1 (a int primary key, b int) engine=innodb; +insert t1 values (1,1); +--source include/save_master_gtid.inc + +--connection slave +--source include/sync_with_master_gtid.inc +--source include/stop_slave.inc +set @@global.innodb_lock_wait_timeout= 1; + +--let $retried_tx_initial= query_get_value(SHOW ALL SLAVES STATUS, Retried_transactions, 1) + +--connection master +--let $gtid_domain_id=`SELECT @@GLOBAL.gtid_domain_id` +--let $gtid_server_id=`SELECT @@GLOBAL.server_id` +--let $xap_seq_no=100 +--eval set @@session.gtid_seq_no=$xap_seq_no +xa start '1'; +update t1 set b=b+10 where a=1; +xa end '1'; +xa prepare '1'; +--let $new_gtid= `SELECT @@global.gtid_binlog_pos` +xa commit '1'; +--source include/save_master_gtid.inc + + +--connection slave + +#--eval set statement sql_log_bin=0 for insert into mysql.gtid_slave_pos values ($gtid_domain_id, 5, $gtid_server_id, $xap_seq_no) + +--connection slave1 +BEGIN; +--eval SELECT * FROM mysql.gtid_slave_pos WHERE seq_no=$xap_seq_no FOR UPDATE + +--connection slave +--source include/start_slave.inc + +--let $slave_sql_errno= 1942,1213 +--source include/wait_for_slave_sql_error.inc + +--let $retried_tx_test= query_get_value(SHOW ALL SLAVES STATUS, Retried_transactions, 1) +if ($retried_tx_initial != $retried_tx_test) +{ + --echo Transaction was retried when a failed XA PREPARE slave GTID update should lead to immediate slave stop without retry + --die Transaction was retried when a failed XA PREPARE slave GTID update should lead to immediate slave stop without retry +} + +--connection slave1 +ROLLBACK; + +--echo # Cleanup + +--connection master +drop table t1; + +--connection slave +--source include/stop_slave.inc +--eval set @@global.gtid_slave_pos= "$new_gtid" +--eval set @@global.slave_parallel_threads= $save_par_thds +--eval set @@global.gtid_strict_mode= $save_strict_mode +--eval set @@global.innodb_lock_wait_timeout= $save_innodb_lock_wait_timeout +--source include/start_slave.inc + +--source include/rpl_end.inc +--echo # End of rpl_xa_prepare_gtid_fail.test diff --git a/mysql-test/suite/sys_vars/r/character_set_client_basic.result b/mysql-test/suite/sys_vars/r/character_set_client_basic.result index d62d88027c7..14c8bcfd058 100644 --- a/mysql-test/suite/sys_vars/r/character_set_client_basic.result +++ b/mysql-test/suite/sys_vars/r/character_set_client_basic.result @@ -500,5 +500,16 @@ res # SET GLOBAL character_set_client=2; ERROR 42000: Unknown character set: '2' +# +# MDEV-31018 Replica of 10.3, 10.4, <10.5.19 and <10.6.12 to 10.11 will not work when using non-default charset +# +SET @@pseudo_slave_mode=1; +SET character_set_client=2/*latin2_czech_cs*/; +SHOW VARIABLES LIKE 'character_set_client'; +Variable_name Value +character_set_client latin2 +SET @@pseudo_slave_mode=0; +Warnings: +Warning 1231 Slave applier execution mode not active, statement ineffective. SET @@global.character_set_client = @global_start_value; SET @@session.character_set_client = @session_start_value; diff --git a/mysql-test/suite/sys_vars/r/character_set_connection_basic.result b/mysql-test/suite/sys_vars/r/character_set_connection_basic.result index e356d62ed2b..bbb4f4e5f75 100644 --- a/mysql-test/suite/sys_vars/r/character_set_connection_basic.result +++ b/mysql-test/suite/sys_vars/r/character_set_connection_basic.result @@ -494,5 +494,21 @@ SELECT @@session.character_set_connection = WHERE VARIABLE_NAME='character_set_connection') AS res; res 1 +# +# MDEV-31018 Replica of 10.3, 10.4, <10.5.19 and <10.6.12 to 10.11 will not work when using non-default charset +# +SET character_set_connection=2/*latin2_czech_cs*/; +ERROR 42000: Unknown character set: '2' +SET @@pseudo_slave_mode=1; +SET character_set_connection=2/*latin2_czech_cs*/; +SHOW VARIABLES LIKE 'character_set_connection'; +Variable_name Value +character_set_connection latin2 +SHOW VARIABLES LIKE 'collation_connection'; +Variable_name Value +collation_connection latin2_general_ci +SET @@pseudo_slave_mode=0; +Warnings: +Warning 1231 Slave applier execution mode not active, statement ineffective. SET @@global.character_set_connection = @global_start_value; SET @@global.character_set_client = @save_character_set_client; diff --git a/mysql-test/suite/sys_vars/r/character_set_results_basic.result b/mysql-test/suite/sys_vars/r/character_set_results_basic.result Binary files differindex d1c6a52ba17..1346c8b42ca 100644 --- a/mysql-test/suite/sys_vars/r/character_set_results_basic.result +++ b/mysql-test/suite/sys_vars/r/character_set_results_basic.result diff --git a/mysql-test/suite/sys_vars/r/character_set_server_basic.result b/mysql-test/suite/sys_vars/r/character_set_server_basic.result index e0a13c729fe..7445be6aef1 100644 --- a/mysql-test/suite/sys_vars/r/character_set_server_basic.result +++ b/mysql-test/suite/sys_vars/r/character_set_server_basic.result @@ -486,5 +486,21 @@ SELECT @@session.character_set_server = WHERE VARIABLE_NAME='character_set_server') AS res; res 1 +# +# MDEV-31018 Replica of 10.3, 10.4, <10.5.19 and <10.6.12 to 10.11 will not work when using non-default charset +# +SET character_set_server=2/*latin2_czech_cs*/; +ERROR 42000: Unknown character set: '2' +SET @@pseudo_slave_mode=1; +SET character_set_server=2/*latin2_czech_cs*/; +SHOW VARIABLES LIKE 'character_set_server'; +Variable_name Value +character_set_server latin2 +SHOW VARIABLES LIKE 'collation_server'; +Variable_name Value +collation_server latin2_general_ci +SET @@pseudo_slave_mode=0; +Warnings: +Warning 1231 Slave applier execution mode not active, statement ineffective. SET @@global.character_set_server = @global_start_value; SET @@session.character_set_server = @session_start_value; diff --git a/mysql-test/suite/sys_vars/t/character_set_client_basic.test b/mysql-test/suite/sys_vars/t/character_set_client_basic.test index 09f758a0316..44c0d240a9d 100644 --- a/mysql-test/suite/sys_vars/t/character_set_client_basic.test +++ b/mysql-test/suite/sys_vars/t/character_set_client_basic.test @@ -350,6 +350,15 @@ SELECT @@session.character_set_client = --error ER_UNKNOWN_CHARACTER_SET SET GLOBAL character_set_client=2; +--echo # +--echo # MDEV-31018 Replica of 10.3, 10.4, <10.5.19 and <10.6.12 to 10.11 will not work when using non-default charset +--echo # + +SET @@pseudo_slave_mode=1; +SET character_set_client=2/*latin2_czech_cs*/; +SHOW VARIABLES LIKE 'character_set_client'; +SET @@pseudo_slave_mode=0; + #################################### # Restore initial value # #################################### diff --git a/mysql-test/suite/sys_vars/t/character_set_connection_basic.test b/mysql-test/suite/sys_vars/t/character_set_connection_basic.test index 3d9094fca04..46bdfcb52e2 100644 --- a/mysql-test/suite/sys_vars/t/character_set_connection_basic.test +++ b/mysql-test/suite/sys_vars/t/character_set_connection_basic.test @@ -275,6 +275,19 @@ SELECT @@session.character_set_connection = (SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME='character_set_connection') AS res; +--echo # +--echo # MDEV-31018 Replica of 10.3, 10.4, <10.5.19 and <10.6.12 to 10.11 will not work when using non-default charset +--echo # + +--error ER_UNKNOWN_CHARACTER_SET +SET character_set_connection=2/*latin2_czech_cs*/; +SET @@pseudo_slave_mode=1; +SET character_set_connection=2/*latin2_czech_cs*/; +SHOW VARIABLES LIKE 'character_set_connection'; +SHOW VARIABLES LIKE 'collation_connection'; +SET @@pseudo_slave_mode=0; + + #################################### # Restore initial value # #################################### diff --git a/mysql-test/suite/sys_vars/t/character_set_results_basic.test b/mysql-test/suite/sys_vars/t/character_set_results_basic.test index 617332b9780..41090be6657 100644 --- a/mysql-test/suite/sys_vars/t/character_set_results_basic.test +++ b/mysql-test/suite/sys_vars/t/character_set_results_basic.test @@ -272,6 +272,18 @@ SELECT @@session.character_set_results = (SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME='character_set_results') AS res; +--echo # +--echo # MDEV-31018 Replica of 10.3, 10.4, <10.5.19 and <10.6.12 to 10.11 will not work when using non-default charset +--echo # + +--error ER_UNKNOWN_CHARACTER_SET +SET character_set_results=2/*latin2_czech_cs*/; +SET @@pseudo_slave_mode=1; +SET character_set_results=2; +SHOW VARIABLES LIKE 'character_set_results'; +SET @@pseudo_slave_mode=0; + + #################################### # Restore initial value # #################################### diff --git a/mysql-test/suite/sys_vars/t/character_set_server_basic.test b/mysql-test/suite/sys_vars/t/character_set_server_basic.test index b2f4788fdae..929dbfaf143 100644 --- a/mysql-test/suite/sys_vars/t/character_set_server_basic.test +++ b/mysql-test/suite/sys_vars/t/character_set_server_basic.test @@ -266,6 +266,18 @@ SELECT @@session.character_set_server = (SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME='character_set_server') AS res; +--echo # +--echo # MDEV-31018 Replica of 10.3, 10.4, <10.5.19 and <10.6.12 to 10.11 will not work when using non-default charset +--echo # + +--error ER_UNKNOWN_CHARACTER_SET +SET character_set_server=2/*latin2_czech_cs*/; +SET @@pseudo_slave_mode=1; +SET character_set_server=2/*latin2_czech_cs*/; +SHOW VARIABLES LIKE 'character_set_server'; +SHOW VARIABLES LIKE 'collation_server'; +SET @@pseudo_slave_mode=0; + #################################### # Restore initial value # #################################### |