diff options
Diffstat (limited to 'mysql-test')
31 files changed, 610 insertions, 35 deletions
diff --git a/mysql-test/main/plugin.result b/mysql-test/main/plugin.result index 93a150ae424..04931001901 100644 --- a/mysql-test/main/plugin.result +++ b/mysql-test/main/plugin.result @@ -341,3 +341,16 @@ RENAME TABLE t1 TO t2; ERROR 42S02: Table 'test.t1' doesn't exist DROP TABLE t1; # End of 10.1 test +# +# MDEV-21258: Can't uninstall plugin if the library file doesn't exist +# +insert into mysql.plugin values ("unexisting_plugin", "soname"); +select * from mysql.plugin WHERE name='unexisting_plugin'; +name dl +unexisting_plugin soname +UNINSTALL PLUGIN unexisting_plugin; +select * from mysql.plugin WHERE name='unexisting_plugin'; +name dl +UNINSTALL PLUGIN unexisting_plugin; +ERROR 42000: PLUGIN unexisting_plugin does not exist +# End of 10.2 tests diff --git a/mysql-test/main/plugin.test b/mysql-test/main/plugin.test index e1a17d49174..d2e828b2589 100644 --- a/mysql-test/main/plugin.test +++ b/mysql-test/main/plugin.test @@ -276,3 +276,23 @@ RENAME TABLE t1 TO t2; DROP TABLE t1; --echo # End of 10.1 test + +--echo # +--echo # MDEV-21258: Can't uninstall plugin if the library file doesn't exist +--echo # + +insert into mysql.plugin values ("unexisting_plugin", "soname"); + +# check that we have the plugin installed +select * from mysql.plugin WHERE name='unexisting_plugin'; + +# make attempt to uninstall the plugin +UNINSTALL PLUGIN unexisting_plugin; + +# check that we have the plugin uninstalled +select * from mysql.plugin WHERE name='unexisting_plugin'; + +--error ER_SP_DOES_NOT_EXIST +UNINSTALL PLUGIN unexisting_plugin; + +--echo # End of 10.2 tests diff --git a/mysql-test/main/win.result b/mysql-test/main/win.result index ca7d5c234cb..24aa29598cd 100644 --- a/mysql-test/main/win.result +++ b/mysql-test/main/win.result @@ -3843,6 +3843,13 @@ ROW_NUMBER() OVER w2 5 DROP TABLE t1; # +# MDEV-18916: crash in Window_spec::print_partition() with decimals +# +SELECT cast((rank() over w1) as decimal (53,56)); +ERROR 42000: Too big scale 56 specified for 'rank() over w1'. Maximum is 38 +SELECT cast((rank() over w1) as decimal (53,30)); +ERROR HY000: Window specification with name 'w1' is not defined +# # End of 10.2 tests # # diff --git a/mysql-test/main/win.test b/mysql-test/main/win.test index 64fda5e7793..37d2b616dda 100644 --- a/mysql-test/main/win.test +++ b/mysql-test/main/win.test @@ -2498,6 +2498,15 @@ SELECT ROW_NUMBER() OVER w2 FROM t1 WINDOW w2 AS (PARTITION BY -1,0,1,2,3,4,5,6) DROP TABLE t1; --echo # +--echo # MDEV-18916: crash in Window_spec::print_partition() with decimals +--echo # + +--error ER_TOO_BIG_SCALE +SELECT cast((rank() over w1) as decimal (53,56)); +--error ER_WRONG_WINDOW_SPEC_NAME +SELECT cast((rank() over w1) as decimal (53,30)); + +--echo # --echo # End of 10.2 tests --echo # diff --git a/mysql-test/main/win_ntile.result b/mysql-test/main/win_ntile.result index 41cb1a594bf..4d02a230e13 100644 --- a/mysql-test/main/win_ntile.result +++ b/mysql-test/main/win_ntile.result @@ -433,3 +433,81 @@ ntile((select a from t1)) over (partition by b order by pk) from t1; ERROR 21000: Subquery returns more than 1 row drop table t1; +# +# MDEV-9911 NTILE must return an error when parameter is not stable +# +create table t1 ( +pk int primary key, +c1 nvarchar(10), +c2 nvarchar(10), +c3 int +); +insert into t1 values +(1, 'Mark', 'Male', 5), +(2, 'John', 'Male', 5), +(3, 'Pam', 'Female', 6), +(4, 'Sara', 'Female', 6), +(5, 'Todd', 'Male', 5), +(6, 'Mary', 'Female', 6), +(7, 'Ben', 'Male', 5), +(8, 'Jodi', 'Female', 6), +(9, 'Tom', 'Male', 5), +(10, 'Lucky', 'Male', 5), +(11, 'Mark', 'Male', 5), +(12, 'John', 'Male', 5), +(13, 'Pam', 'Female', 6), +(14, 'Sara', 'Female', 6), +(15, 'Todd', 'Male', 5), +(16, 'Mary', 'Female', 6), +(17, 'Ben', 'Male', 5), +(18, 'Jodi', 'Female', 6), +(19, 'Tom', 'Male', 5), +(20, 'Lucky', 'Male', 5); +select c1, c2, c3, ntile(6) over (partition by c2 order by pk) from t1; +c1 c2 c3 ntile(6) over (partition by c2 order by pk) +Pam Female 6 1 +Sara Female 6 1 +Mary Female 6 2 +Jodi Female 6 2 +Pam Female 6 3 +Sara Female 6 4 +Mary Female 6 5 +Jodi Female 6 6 +Mark Male 5 1 +John Male 5 1 +Todd Male 5 2 +Ben Male 5 2 +Tom Male 5 3 +Lucky Male 5 3 +Mark Male 5 4 +John Male 5 4 +Todd Male 5 5 +Ben Male 5 5 +Tom Male 5 6 +Lucky Male 5 6 +select c1, c2, c3, ntile(c3) over (partition by c2 order by pk) from t1; +c1 c2 c3 ntile(c3) over (partition by c2 order by pk) +Pam Female 6 1 +Sara Female 6 1 +Mary Female 6 2 +Jodi Female 6 2 +Pam Female 6 3 +Sara Female 6 4 +Mary Female 6 5 +Jodi Female 6 6 +Mark Male 5 1 +John Male 5 1 +Todd Male 5 1 +Ben Male 5 2 +Tom Male 5 2 +Lucky Male 5 2 +Mark Male 5 3 +John Male 5 3 +Todd Male 5 4 +Ben Male 5 4 +Tom Male 5 5 +Lucky Male 5 5 +update t1 set c3= 1 where pk = 1; +select c1, c2, c3, ntile(c3) over (partition by c2 order by pk) from t1; +ERROR HY000: Argument of NTILE must be greater than 0 +drop table t1; diff --git a/mysql-test/main/win_ntile.test b/mysql-test/main/win_ntile.test index 6f12e1f4005..c65ba7e1521 100644 --- a/mysql-test/main/win_ntile.test +++ b/mysql-test/main/win_ntile.test @@ -169,3 +169,46 @@ from t1; drop table t1; + +--echo # +--echo # MDEV-9911 NTILE must return an error when parameter is not stable +--echo # + +create table t1 ( + pk int primary key, + c1 nvarchar(10), + c2 nvarchar(10), + c3 int +); + +insert into t1 values + (1, 'Mark', 'Male', 5), + (2, 'John', 'Male', 5), + (3, 'Pam', 'Female', 6), + (4, 'Sara', 'Female', 6), + (5, 'Todd', 'Male', 5), + (6, 'Mary', 'Female', 6), + (7, 'Ben', 'Male', 5), + (8, 'Jodi', 'Female', 6), + (9, 'Tom', 'Male', 5), + (10, 'Lucky', 'Male', 5), + (11, 'Mark', 'Male', 5), + (12, 'John', 'Male', 5), + (13, 'Pam', 'Female', 6), + (14, 'Sara', 'Female', 6), + (15, 'Todd', 'Male', 5), + (16, 'Mary', 'Female', 6), + (17, 'Ben', 'Male', 5), + (18, 'Jodi', 'Female', 6), + (19, 'Tom', 'Male', 5), + (20, 'Lucky', 'Male', 5); +# Correct usage of NTILE with a fix argument NTILE(6). +select c1, c2, c3, ntile(6) over (partition by c2 order by pk) from t1; +# Correct usage - constant NTILE (argument) in each partition. +select c1, c2, c3, ntile(c3) over (partition by c2 order by pk) from t1; + +update t1 set c3= 1 where pk = 1; +--error ER_INVALID_NTILE_ARGUMENT +select c1, c2, c3, ntile(c3) over (partition by c2 order by pk) from t1; + +drop table t1; diff --git a/mysql-test/suite/encryption/r/innodb_encrypt_temporary_tables.result b/mysql-test/suite/encryption/r/innodb_encrypt_temporary_tables.result index 9a291ae1354..541680ae862 100644 --- a/mysql-test/suite/encryption/r/innodb_encrypt_temporary_tables.result +++ b/mysql-test/suite/encryption/r/innodb_encrypt_temporary_tables.result @@ -9,6 +9,9 @@ INSERT INTO t2 (f1,f2,f3) SELECT '', '', '' FROM seq_1_to_8192; SELECT COUNT(*) FROM t1; COUNT(*) 8192 +SELECT COUNT(*) FROM t2; +COUNT(*) +8192 SELECT variable_value > @old_encrypted FROM information_schema.global_status WHERE variable_name = 'innodb_encryption_n_temp_blocks_encrypted'; variable_value > @old_encrypted diff --git a/mysql-test/suite/encryption/t/innodb_encrypt_temporary_tables.test b/mysql-test/suite/encryption/t/innodb_encrypt_temporary_tables.test index 6c16afa28f4..c1a6ad38456 100644 --- a/mysql-test/suite/encryption/t/innodb_encrypt_temporary_tables.test +++ b/mysql-test/suite/encryption/t/innodb_encrypt_temporary_tables.test @@ -15,6 +15,8 @@ CREATE TEMPORARY TABLE t2(f1 CHAR(100), f2 CHAR(200), f3 CHAR(200))ENGINE=InnoDB INSERT INTO t2 (f1,f2,f3) SELECT '', '', '' FROM seq_1_to_8192; SELECT COUNT(*) FROM t1; +SELECT COUNT(*) FROM t2; + SELECT variable_value > @old_encrypted FROM information_schema.global_status WHERE variable_name = 'innodb_encryption_n_temp_blocks_encrypted'; diff --git a/mysql-test/suite/galera/disabled.def b/mysql-test/suite/galera/disabled.def index fe0b1fbcd76..8545380dd2c 100644 --- a/mysql-test/suite/galera/disabled.def +++ b/mysql-test/suite/galera/disabled.def @@ -14,10 +14,8 @@ MW-286 : MDEV-18464 Killing thread can cause mutex deadlock if done concurrently MW-329 : MDEV-19962 Galera test failure on MW-329 galera.galera_defaults : MDEV-21494 Galera test sporadic failure on galera.galera_defaults galera_as_slave_replication_bundle : MDEV-15785 OPTION_GTID_BEGIN is set in Gtid_log_event::do_apply_event() -galera_autoinc_sst_mariabackup : Known issue, may require porting MDEV-17458 from later versions galera_binlog_stmt_autoinc : MDEV-19959 Galera test failure on galera_binlog_stmt_autoinc galera_gcache_recover_manytrx : MDEV-18834 Galera test failure -galera_ist_progress : MDEV-15236 fails when trying to read transfer status galera_load_data : MDEV-19968 galera.galera_load_data galera_parallel_simple : MDEV-20318 galera.galera_parallel_simple fails galera_shutdown_nonprim : MDEV-21493 galera.galera_shutdown_nonprim diff --git a/mysql-test/suite/galera/include/galera_st_clean_slave.inc b/mysql-test/suite/galera/include/galera_st_clean_slave.inc index 3a49f4f6ad2..44cbf67fd12 100644 --- a/mysql-test/suite/galera/include/galera_st_clean_slave.inc +++ b/mysql-test/suite/galera/include/galera_st_clean_slave.inc @@ -102,12 +102,18 @@ INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); ROLLBACK; +--let $wait_condition = SELECT COUNT(*)=35 FROM t1 +--source include/wait_condition.inc + SELECT COUNT(*) = 35 FROM t1; SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; COMMIT; SET AUTOCOMMIT=ON; --connection node_1 +--let $wait_condition = SELECT COUNT(*)=35 FROM t1 +--source include/wait_condition.inc + SELECT COUNT(*) = 35 FROM t1; SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; DROP TABLE t1; diff --git a/mysql-test/suite/galera/include/galera_st_disconnect_slave.inc b/mysql-test/suite/galera/include/galera_st_disconnect_slave.inc index c8869746bd1..4674fc7867f 100644 --- a/mysql-test/suite/galera/include/galera_st_disconnect_slave.inc +++ b/mysql-test/suite/galera/include/galera_st_disconnect_slave.inc @@ -92,12 +92,18 @@ INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); ROLLBACK; +--let $wait_condition = SELECT COUNT(*)=35 FROM t1 +--source include/wait_condition.inc + SELECT COUNT(*) = 35 FROM t1; SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; COMMIT; SET AUTOCOMMIT=ON; --connection node_1 +--let $wait_condition = SELECT COUNT(*)=35 FROM t1 +--source include/wait_condition.inc + SELECT COUNT(*) = 35 FROM t1; SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; DROP TABLE t1; diff --git a/mysql-test/suite/galera/include/galera_st_kill_slave.inc b/mysql-test/suite/galera/include/galera_st_kill_slave.inc index 0b96de55a32..a4d9e91e8be 100644 --- a/mysql-test/suite/galera/include/galera_st_kill_slave.inc +++ b/mysql-test/suite/galera/include/galera_st_kill_slave.inc @@ -96,12 +96,18 @@ INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); ROLLBACK; +--let $wait_condition = SELECT COUNT(*)=35 FROM t1 +--source include/wait_condition.inc + SELECT COUNT(*) = 35 FROM t1; SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; COMMIT; SET AUTOCOMMIT=ON; --connection node_1 +--let $wait_condition = SELECT COUNT(*)=35 FROM t1 +--source include/wait_condition.inc + SELECT COUNT(*) = 35 FROM t1; SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; DROP TABLE t1; diff --git a/mysql-test/suite/galera/include/galera_st_kill_slave_ddl.inc b/mysql-test/suite/galera/include/galera_st_kill_slave_ddl.inc index 44a1513fa6e..bb8c68bd181 100644 --- a/mysql-test/suite/galera/include/galera_st_kill_slave_ddl.inc +++ b/mysql-test/suite/galera/include/galera_st_kill_slave_ddl.inc @@ -110,6 +110,9 @@ INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); INSERT INTO t1 (f1) VALUES ('node1_to_be_rollbacked_after'); ROLLBACK; +--let $wait_condition = SELECT COUNT(*)=35 FROM t1 +--source include/wait_condition.inc + SELECT COUNT(*) = 2 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't1'; SELECT COUNT(*) = 35 FROM t1; SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; @@ -117,6 +120,8 @@ COMMIT; SET AUTOCOMMIT=ON; --connection node_1 +--let $wait_condition = SELECT COUNT(*)=35 FROM t1 +--source include/wait_condition.inc SELECT COUNT(*) = 2 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't1'; SELECT COUNT(*) = 35 FROM t1; SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; diff --git a/mysql-test/suite/galera/include/galera_st_shutdown_slave.inc b/mysql-test/suite/galera/include/galera_st_shutdown_slave.inc index 207282c8237..eeb6a15e0a3 100644 --- a/mysql-test/suite/galera/include/galera_st_shutdown_slave.inc +++ b/mysql-test/suite/galera/include/galera_st_shutdown_slave.inc @@ -97,12 +97,18 @@ INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); ROLLBACK; +--let $wait_condition = SELECT COUNT(*)=35 FROM t1 +--source include/wait_condition.inc + SELECT COUNT(*) = 35 FROM t1; SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; COMMIT; SET AUTOCOMMIT=ON; --connection node_1 +--let $wait_condition = SELECT COUNT(*)=35 FROM t1 +--source include/wait_condition.inc + SELECT COUNT(*) = 35 FROM t1; SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; DROP TABLE t1; diff --git a/mysql-test/suite/galera/r/galera_var_innodb_disallow_writes.result b/mysql-test/suite/galera/r/galera_var_innodb_disallow_writes.result index 54cebbee40c..8750651612d 100644 --- a/mysql-test/suite/galera/r/galera_var_innodb_disallow_writes.result +++ b/mysql-test/suite/galera/r/galera_var_innodb_disallow_writes.result @@ -1,25 +1,28 @@ -connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1; connection node_1a; SET SESSION wsrep_sync_wait = 0; connection node_1; -CREATE TABLE t1 (f1 INTEGER) Engine=InnoDB; +CREATE TABLE t1 (f1 INTEGER, f2 varchar(1024)) Engine=InnoDB; +CREATE TABLE ten (f1 INTEGER) ENGINE=InnoDB; +INSERT INTO ten VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); SET GLOBAL innodb_disallow_writes=ON; -INSERT INTO t1 VALUES (1);; +INSERT INTO t1 (f2) SELECT 'abcde ' FROM ten AS a1, ten AS a2, ten AS a3, ten AS a4;; +connection node_2; +INSERT INTO t1 (f2) SELECT 'fghij ' FROM ten AS a1, ten AS a2, ten AS a3, ten AS a4; +SELECT COUNT(*) AS EXPECT_10000 FROM t1; +EXPECT_10000 +10000 connection node_1a; -SELECT COUNT(*) AS EXPECT_0 FROM t1; -EXPECT_0 -0 -SELECT COUNT(*) AS EXPECT_0 FROM t1; -EXPECT_0 -0 SET GLOBAL innodb_disallow_writes=OFF; connection node_1; -SELECT COUNT(*) AS EXPECT_1 FROM t1; -EXPECT_1 -1 +SELECT COUNT(*) AS EXPECT_20000 FROM t1; +EXPECT_20000 +20000 +connection node_2; +SELECT COUNT(*) AS EXPECT_20000 FROM t1; +EXPECT_20000 +20000 +connection node_1; connection node_2; -SELECT COUNT(*) AS EXPECT_1 FROM t1; -EXPECT_1 -1 DROP TABLE t1; +DROP TABLE ten; disconnect node_1a; diff --git a/mysql-test/suite/galera/t/galera_var_innodb_disallow_writes.test b/mysql-test/suite/galera/t/galera_var_innodb_disallow_writes.test index 3754cff0123..0d2a6effd92 100644 --- a/mysql-test/suite/galera/t/galera_var_innodb_disallow_writes.test +++ b/mysql-test/suite/galera/t/galera_var_innodb_disallow_writes.test @@ -1,41 +1,71 @@ # # This test checks that innodb_disallow_writes works as expected # +# Note that we need to enable binlog for this test: If the commit +# to InnoDB is done in one phase, the transaction is committed in +# memory before it is persisted to disk. This means that the +# innodb_disallow_writes=ON may not prevent transaction to +# become visible to other readers. On the other hand, if the +# commit is two phase (as it is with binlog), the transaction +# will be blocked in prepare phase. +# --source include/galera_cluster.inc +--source include/have_innodb.inc + +--let $datadir= `SELECT @@datadir` + # Open a separate connection to be used to run SHOW PROCESSLIST ---connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1 +--let $galera_connection_name = node_1a +--let $galera_server_number = 1 +--source include/galera_connect.inc --connection node_1a SET SESSION wsrep_sync_wait = 0; --connection node_1 -CREATE TABLE t1 (f1 INTEGER) Engine=InnoDB; +CREATE TABLE t1 (f1 INTEGER, f2 varchar(1024)) Engine=InnoDB; +CREATE TABLE ten (f1 INTEGER) ENGINE=InnoDB; +INSERT INTO ten VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); + SET GLOBAL innodb_disallow_writes=ON; ---send INSERT INTO t1 VALUES (1); +--exec find $datadir -type f-exec md5sum {} \; | md5sum >$MYSQLTEST_VARDIR/tmp/innodb_before + +# +# This insert has no effect before innodb_disallow_writes is OFF +# +--send INSERT INTO t1 (f2) SELECT 'abcde ' FROM ten AS a1, ten AS a2, ten AS a3, ten AS a4; + +--connection node_2 +INSERT INTO t1 (f2) SELECT 'fghij ' FROM ten AS a1, ten AS a2, ten AS a3, ten AS a4; +SELECT COUNT(*) AS EXPECT_10000 FROM t1; --connection node_1a ---let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE 'test/t1'; ---source include/wait_condition.inc -SELECT COUNT(*) AS EXPECT_0 FROM t1; -let $wait_condition = SELECT 1 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE INFO = 'INSERT INTO t1 VALUES (1)'; ---source include/wait_condition.inc -SELECT COUNT(*) AS EXPECT_0 FROM t1; +--sleep 5 + +--exec find $datadir -type f-exec md5sum {} \; | md5sum >$MYSQLTEST_VARDIR/tmp/innodb_after SET GLOBAL innodb_disallow_writes=OFF; --connection node_1 --reap -SELECT COUNT(*) AS EXPECT_1 FROM t1; +--let $wait_condition = SELECT COUNT(*) = 20000 FROM t1; +--source include/wait_condition.inc + +SELECT COUNT(*) AS EXPECT_20000 FROM t1; --connection node_2 ---let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE 'test/t1'; ---source include/wait_condition.inc ---let $wait_condition = SELECT COUNT(*) = 1 FROM t1; +--let $wait_condition = SELECT COUNT(*) = 20000 FROM t1; --source include/wait_condition.inc -SELECT COUNT(*) AS EXPECT_1 FROM t1; +SELECT COUNT(*) AS EXPECT_20000 FROM t1; + +--connection node_1 +--diff_files $MYSQLTEST_VARDIR/tmp/innodb_before $MYSQLTEST_VARDIR/tmp/innodb_after + +--connection node_2 DROP TABLE t1; +DROP TABLE ten; --disconnect node_1a diff --git a/mysql-test/suite/gcol/r/gcol_bugfixes.result b/mysql-test/suite/gcol/r/gcol_bugfixes.result index 9aff30aabc9..8eb7a9372b5 100644 --- a/mysql-test/suite/gcol/r/gcol_bugfixes.result +++ b/mysql-test/suite/gcol/r/gcol_bugfixes.result @@ -603,3 +603,69 @@ test gcol_t1 sidea NEVER NULL test gcol_t1 sideb NEVER NULL test gcol_t1 sidec VIRTUAL GENERATED ALWAYS sqrt(`sidea` * `sidea` + `sideb` * `sideb`) DROP TABLE gcol_t1; +# +# MDEV-16039 Crash when selecting virtual columns +# generated using functions with DAYNAME() +# +CREATE TABLE t1 ( +suppliersenttoday INT NOT NULL, +suppliercaptoday CHAR(10) AS (CONCAT('',DAYNAME('2020-02-05'))) +) COLLATE utf8_bin; +INSERT INTO t1 (suppliersenttoday) VALUES (0); +INSERT INTO t1 (suppliersenttoday) VALUES (0); +SELECT * FROM t1; +suppliersenttoday suppliercaptoday +0 Wednesday +0 Wednesday +PREPARE STMT FROM 'INSERT INTO t1 (suppliersenttoday) VALUES (1)'; +CREATE OR REPLACE TABLE t1 ( +suppliersenttoday INT NOT NULL, +suppliercaptoday CHAR(10) AS (CONCAT('',DAYNAME('2020-02-05'))) +) COLLATE utf8_bin; +EXECUTE STMT; +EXECUTE STMT; +SELECT * FROM t1; +suppliersenttoday suppliercaptoday +1 Wednesday +1 Wednesday +DROP TABLE t1; +# (duplicate) MDEV-20380 Server crash during update +CREATE TABLE gafld ( +nuigafld INTEGER NOT NULL, +ucrgafld VARCHAR(30) COLLATE UTF8_BIN NOT NULL +DEFAULT SUBSTRING_INDEX(USER(),'@',1) +); +EXPLAIN UPDATE gafld SET nuigafld = 0 WHERE nuigafld = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE gafld ALL NULL NULL NULL NULL 1 Using where +EXPLAIN UPDATE gafld SET nuigafld = 0 WHERE nuigafld = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE gafld ALL NULL NULL NULL NULL 1 Using where +DROP TABLE gafld; +# (duplicate) MDEV-17653 replace into generated columns is unstable +# Some columns are snipped from the MDEV test +CREATE TABLE t ( +c0 TIMESTAMP NOT NULL DEFAULT current_timestamp() +ON UPDATE current_timestamp(), +c1 DECIMAL(27,25) GENERATED ALWAYS AS (DAYOFMONTH('2020-02-05')), +c4 TIME NOT NULL, +c8 SMALLINT(6) GENERATED ALWAYS AS +(CONCAT_WS(CONVERT(C1 USING CP932), +'900') <> (c4 = 1)), +PRIMARY KEY (c4) +) DEFAULT CHARSET=latin1; +REPLACE INTO t SET c0 = '2018-06-03 10:31:43', c4 = '02:58:55'; +REPLACE INTO t SET c0 = '2018-06-03 10:31:44', c4 = '02:58:55'; +REPLACE INTO t SET c0 = '2018-06-03 10:31:45', c4 = '02:58:55'; +DROP TABLE t; +# (duplicate) MDEV-17986 crash when I insert on a table +CREATE OR REPLACE TABLE t2 ( +number BIGINT(20) NOT NULL, +lrn BIGINT(20) NOT NULL DEFAULT 0, +source VARCHAR(15) NOT NULL +DEFAULT (REVERSE(SUBSTRING_INDEX(REVERSE(user()), '@', 1))), +PRIMARY KEY (number) +); +REPLACE t2(number) VALUES('1'); +REPLACE t2(number) VALUES('1'); +DROP TABLE t2; diff --git a/mysql-test/suite/gcol/t/gcol_bugfixes.test b/mysql-test/suite/gcol/t/gcol_bugfixes.test index 5563347a02a..033c430853d 100644 --- a/mysql-test/suite/gcol/t/gcol_bugfixes.test +++ b/mysql-test/suite/gcol/t/gcol_bugfixes.test @@ -564,3 +564,73 @@ SELECT table_schema,table_name,column_name,extra,is_generated,generation_express FROM information_schema.columns WHERE table_name='gcol_t1'; DROP TABLE gcol_t1; + +--echo # +--echo # MDEV-16039 Crash when selecting virtual columns +--echo # generated using functions with DAYNAME() +--echo # + +CREATE TABLE t1 ( + suppliersenttoday INT NOT NULL, + suppliercaptoday CHAR(10) AS (CONCAT('',DAYNAME('2020-02-05'))) +) COLLATE utf8_bin; + +INSERT INTO t1 (suppliersenttoday) VALUES (0); +INSERT INTO t1 (suppliersenttoday) VALUES (0); +SELECT * FROM t1; + +PREPARE STMT FROM 'INSERT INTO t1 (suppliersenttoday) VALUES (1)'; + +CREATE OR REPLACE TABLE t1 ( + suppliersenttoday INT NOT NULL, + suppliercaptoday CHAR(10) AS (CONCAT('',DAYNAME('2020-02-05'))) +) COLLATE utf8_bin; + +EXECUTE STMT; +EXECUTE STMT; +SELECT * FROM t1; + +DROP TABLE t1; + +--echo # (duplicate) MDEV-20380 Server crash during update +CREATE TABLE gafld ( + nuigafld INTEGER NOT NULL, + ucrgafld VARCHAR(30) COLLATE UTF8_BIN NOT NULL + DEFAULT SUBSTRING_INDEX(USER(),'@',1) +); +EXPLAIN UPDATE gafld SET nuigafld = 0 WHERE nuigafld = 10; +EXPLAIN UPDATE gafld SET nuigafld = 0 WHERE nuigafld = 10; +DROP TABLE gafld; + +--echo # (duplicate) MDEV-17653 replace into generated columns is unstable +--echo # Some columns are snipped from the MDEV test +CREATE TABLE t ( + c0 TIMESTAMP NOT NULL DEFAULT current_timestamp() + ON UPDATE current_timestamp(), + c1 DECIMAL(27,25) GENERATED ALWAYS AS (DAYOFMONTH('2020-02-05')), + c4 TIME NOT NULL, + c8 SMALLINT(6) GENERATED ALWAYS AS + (CONCAT_WS(CONVERT(C1 USING CP932), + '900') <> (c4 = 1)), + PRIMARY KEY (c4) +) DEFAULT CHARSET=latin1; + +REPLACE INTO t SET c0 = '2018-06-03 10:31:43', c4 = '02:58:55'; +REPLACE INTO t SET c0 = '2018-06-03 10:31:44', c4 = '02:58:55'; +REPLACE INTO t SET c0 = '2018-06-03 10:31:45', c4 = '02:58:55'; + +DROP TABLE t; + +--echo # (duplicate) MDEV-17986 crash when I insert on a table +CREATE OR REPLACE TABLE t2 ( + number BIGINT(20) NOT NULL, + lrn BIGINT(20) NOT NULL DEFAULT 0, + source VARCHAR(15) NOT NULL + DEFAULT (REVERSE(SUBSTRING_INDEX(REVERSE(user()), '@', 1))), + PRIMARY KEY (number) +); + +REPLACE t2(number) VALUES('1'); +REPLACE t2(number) VALUES('1'); + +DROP TABLE t2; diff --git a/mysql-test/suite/innodb/disabled.def b/mysql-test/suite/innodb/disabled.def index 4484417afce..35c941f8af7 100644 --- a/mysql-test/suite/innodb/disabled.def +++ b/mysql-test/suite/innodb/disabled.def @@ -11,4 +11,3 @@ ############################################################################## create-index-debug : MDEV-13680 InnoDB may crash when btr_page_alloc() fails -innodb_force_recovery_rollback : MDEV-22889 InnoDB occasionally breaks ACID diff --git a/mysql-test/suite/innodb/r/innodb-alter.result b/mysql-test/suite/innodb/r/innodb-alter.result index 3afc3045c50..3bbc4bd3b63 100644 --- a/mysql-test/suite/innodb/r/innodb-alter.result +++ b/mysql-test/suite/innodb/r/innodb-alter.result @@ -1066,3 +1066,20 @@ SELECT * FROM t1; a b 10 10:20:30 DROP TABLE t1; +# +# MDEV-18042 Server crashes in mysql_alter_table upon adding a non-null +# date column under NO_ZERO_DATE with ALGORITHM=INPLACE +# +SET @OLD_SQL_MODE= @@SQL_MODE; +SET @@SQL_MODE= 'NO_ZERO_DATE'; +CREATE OR REPLACE TABLE t1 (i INT) ENGINE=MyISAM; +ALTER TABLE t1 ADD COLUMN d DATE NOT NULL, ALGORITHM=INPLACE; +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY +CREATE OR REPLACE TABLE t1 (i INT) ENGINE=InnoDB; +ALTER TABLE t1 ADD d DATETIME NOT NULL CHECK (f <= 0), ALGORITHM=COPY; +ERROR 42S22: Unknown column 'f' in 'CHECK' +CREATE OR REPLACE TABLE t1 (a int) ENGINE=InnoDB; +ALTER TABLE t1 ADD COLUMN b DATETIME NOT NULL, LOCK=NONE; +# Cleanup +SET @@SQL_MODE= @OLD_SQL_MODE; +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/r/innodb_force_recovery_rollback.result b/mysql-test/suite/innodb/r/innodb_force_recovery_rollback.result index dc037fd6c97..42cf8adfc13 100644 --- a/mysql-test/suite/innodb/r/innodb_force_recovery_rollback.result +++ b/mysql-test/suite/innodb/r/innodb_force_recovery_rollback.result @@ -12,6 +12,6 @@ SET GLOBAL innodb_flush_log_at_trx_commit=1; CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; disconnect con0; connection default; -SELECT * FROM t0 LOCK IN SHARE MODE; +SELECT * FROM t0 LIMIT 0 LOCK IN SHARE MODE; a DROP TABLE t0,t1; diff --git a/mysql-test/suite/innodb/r/mvcc.result b/mysql-test/suite/innodb/r/mvcc.result index fc2d403fa63..215b2165b8c 100644 --- a/mysql-test/suite/innodb/r/mvcc.result +++ b/mysql-test/suite/innodb/r/mvcc.result @@ -29,4 +29,18 @@ SELECT * FROM t1; a 0 DROP TABLE t1; +# +# MDEV-23198 Crash in REPLACE +# +BEGIN NOT ATOMIC +DECLARE c TEXT DEFAULT(SELECT CONCAT('CREATE TABLE t1(id INT PRIMARY KEY, c', +GROUP_CONCAT(seq SEPARATOR ' INT, c'), +' INT NOT NULL UNIQUE) ENGINE=InnoDB') +FROM seq_1_to_294); +EXECUTE IMMEDIATE c; +END; +$$ +INSERT INTO t1 SET id=1,c294=1; +REPLACE t1 SET id=1,c294=1; +DROP TABLE t1; SET GLOBAL innodb_file_per_table= @save_per_table; diff --git a/mysql-test/suite/innodb/t/innodb-alter.test b/mysql-test/suite/innodb/t/innodb-alter.test index 554ae0e3041..df2cd1707dc 100644 --- a/mysql-test/suite/innodb/t/innodb-alter.test +++ b/mysql-test/suite/innodb/t/innodb-alter.test @@ -668,6 +668,28 @@ ALTER TABLE t1 ADD b TIME NOT NULL DEFAULT if(unix_timestamp()>1,TIMESTAMP'2001- SELECT * FROM t1; DROP TABLE t1; +--echo # +--echo # MDEV-18042 Server crashes in mysql_alter_table upon adding a non-null +--echo # date column under NO_ZERO_DATE with ALGORITHM=INPLACE +--echo # + +SET @OLD_SQL_MODE= @@SQL_MODE; +SET @@SQL_MODE= 'NO_ZERO_DATE'; +CREATE OR REPLACE TABLE t1 (i INT) ENGINE=MyISAM; +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 ADD COLUMN d DATE NOT NULL, ALGORITHM=INPLACE; + +CREATE OR REPLACE TABLE t1 (i INT) ENGINE=InnoDB; +--error ER_BAD_FIELD_ERROR +ALTER TABLE t1 ADD d DATETIME NOT NULL CHECK (f <= 0), ALGORITHM=COPY; + +CREATE OR REPLACE TABLE t1 (a int) ENGINE=InnoDB; +ALTER TABLE t1 ADD COLUMN b DATETIME NOT NULL, LOCK=NONE; + +--echo # Cleanup +SET @@SQL_MODE= @OLD_SQL_MODE; +DROP TABLE t1; + # # End of 10.2 tests # diff --git a/mysql-test/suite/innodb/t/innodb_force_recovery_rollback.test b/mysql-test/suite/innodb/t/innodb_force_recovery_rollback.test index ad234eba72e..f1d14c45aaf 100644 --- a/mysql-test/suite/innodb/t/innodb_force_recovery_rollback.test +++ b/mysql-test/suite/innodb/t/innodb_force_recovery_rollback.test @@ -30,5 +30,6 @@ CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; disconnect con0; connection default; # If the rollback was aborted, we would end up in a lock wait here. -SELECT * FROM t0 LOCK IN SHARE MODE; +# The LIMIT 0 works around MDEV-22889 InnoDB occasionally breaks ACID +SELECT * FROM t0 LIMIT 0 LOCK IN SHARE MODE; DROP TABLE t0,t1; diff --git a/mysql-test/suite/innodb/t/mvcc.test b/mysql-test/suite/innodb/t/mvcc.test index bf76a5de798..7c37718c28a 100644 --- a/mysql-test/suite/innodb/t/mvcc.test +++ b/mysql-test/suite/innodb/t/mvcc.test @@ -1,4 +1,5 @@ --source include/have_innodb.inc +--source include/have_sequence.inc SET @save_per_table= @@GLOBAL.innodb_file_per_table; SET GLOBAL innodb_file_per_table= 1; @@ -49,4 +50,23 @@ SELECT * FROM t1; DROP TABLE t1; +--echo # +--echo # MDEV-23198 Crash in REPLACE +--echo # + +DELIMITER $$; +BEGIN NOT ATOMIC + DECLARE c TEXT DEFAULT(SELECT CONCAT('CREATE TABLE t1(id INT PRIMARY KEY, c', + GROUP_CONCAT(seq SEPARATOR ' INT, c'), + ' INT NOT NULL UNIQUE) ENGINE=InnoDB') + FROM seq_1_to_294); + EXECUTE IMMEDIATE c; +END; +$$ +DELIMITER ;$$ + +INSERT INTO t1 SET id=1,c294=1; +REPLACE t1 SET id=1,c294=1; +DROP TABLE t1; + SET GLOBAL innodb_file_per_table= @save_per_table; diff --git a/mysql-test/suite/plugins/r/audit_null_debug.result b/mysql-test/suite/plugins/r/audit_null_debug.result index 9d5c7c4a02c..e70de36a2ec 100644 --- a/mysql-test/suite/plugins/r/audit_null_debug.result +++ b/mysql-test/suite/plugins/r/audit_null_debug.result @@ -11,5 +11,6 @@ uninstall plugin audit_null; ERROR HY000: Index for table './mysql/plugin.MYI' is corrupt; try to repair it SET debug_dbug=@old_dbug; uninstall plugin audit_null; +uninstall plugin audit_null; ERROR 42000: PLUGIN audit_null does not exist delete from mysql.plugin where name='audit_null'; diff --git a/mysql-test/suite/plugins/t/audit_null_debug.test b/mysql-test/suite/plugins/t/audit_null_debug.test index 0534108b107..f3c88d24c0c 100644 --- a/mysql-test/suite/plugins/t/audit_null_debug.test +++ b/mysql-test/suite/plugins/t/audit_null_debug.test @@ -26,7 +26,8 @@ SET debug_dbug='+d,myisam_pretend_crashed_table_on_usage'; uninstall plugin audit_null; SET debug_dbug=@old_dbug; ---error 1305 +uninstall plugin audit_null; +--error ER_SP_DOES_NOT_EXIST uninstall plugin audit_null; delete from mysql.plugin where name='audit_null'; diff --git a/mysql-test/suite/rpl/r/rpl_dump_request_retry_warning.result b/mysql-test/suite/rpl/r/rpl_dump_request_retry_warning.result new file mode 100644 index 00000000000..3a80d5b5f31 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_dump_request_retry_warning.result @@ -0,0 +1,22 @@ +include/master-slave.inc +[connection master] +CREATE TABLE t1(a int); +INSERT INTO t1 VALUES(1); +connection slave; +SET @saved_dbug = @@GLOBAL.debug_dbug; +SET @@global.debug_dbug= 'd,simulate_error_on_packet_write'; +START SLAVE; +SET DEBUG_SYNC= 'now WAIT_FOR parked'; +SET @@GLOBAL.debug_dbug = @saved_dbug; +SET DEBUG_SYNC= 'now SIGNAL continue'; +SET DEBUG_SYNC= 'RESET'; +include/wait_for_slave_io_to_start.inc +include/wait_for_slave_sql_to_start.inc +connection master; +include/sync_slave_sql_with_master.inc +SELECT * FROM t1; +a +1 +connection master; +DROP TABLE t1; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_row_virt.result b/mysql-test/suite/rpl/r/rpl_row_virt.result new file mode 100644 index 00000000000..b670c63d9f1 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_row_virt.result @@ -0,0 +1,20 @@ +include/master-slave.inc +[connection master] +connection master; +create table t1 ( +id int auto_increment, +data varchar(32), +virt tinyint as (1), +primary key (id), +key virt (virt) +) engine=innodb default charset=utf8mb4; +insert into t1 (data) values ('broken'); +update t1 set data='more broken'; +connection slave; +select * from t1; +id data virt +1 more broken 1 +connection master; +drop table t1; +connection slave; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_dump_request_retry_warning.test b/mysql-test/suite/rpl/t/rpl_dump_request_retry_warning.test new file mode 100644 index 00000000000..d750d44ae71 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_dump_request_retry_warning.test @@ -0,0 +1,60 @@ +# ==== Purpose ==== +# +# Test verifies that, due to a temporary network error, if request dump +# command specific packet write operation fails then the write error gets +# handled appropriately. Further retry will be initiated with appropriate +# slave registration on master. This will ensure that master has all the +# details of slave and no warnings are reported on the master side. +# +# ==== Implementation ==== +# +# Steps: +# 0 - Skip the slave start. +# 1 - Enable debug simulation which will simulate packet write error during +# dump request command execution. +# 2 - Start the slave. Observe that slave is able to reconnect post +# temporary network write error. +# +# ==== References ==== +# +# MDEV-14203: rpl.rpl_extra_col_master_myisam, +# rpl.rpl_slave_load_tmpdir_not_exist failed in buildbot with a +# warning +# +# MDEV-13258: rpl.rpl_skip_replication, rpl.rpl_set_statement_default_master +# failed in buildbot +# + +--source include/have_debug.inc +--source include/have_debug_sync.inc +--let $rpl_skip_start_slave=1 +--source include/master-slave.inc + +# Do an insert on master +CREATE TABLE t1(a int); +INSERT INTO t1 VALUES(1); + +# Add a debug point and start the slave so that dump request fails. +connection slave; +SET @saved_dbug = @@GLOBAL.debug_dbug; +SET @@global.debug_dbug= 'd,simulate_error_on_packet_write'; + +START SLAVE; +SET DEBUG_SYNC= 'now WAIT_FOR parked'; +SET @@GLOBAL.debug_dbug = @saved_dbug; +SET DEBUG_SYNC= 'now SIGNAL continue'; +SET DEBUG_SYNC= 'RESET'; + +--source include/wait_for_slave_io_to_start.inc +--source include/wait_for_slave_sql_to_start.inc + +# Sync the slave and verify that slave has caught up with the master. +connection master; +--source include/sync_slave_sql_with_master.inc +SELECT * FROM t1; + +# Cleanup +connection master; +DROP TABLE t1; + +--source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_row_virt.test b/mysql-test/suite/rpl/t/rpl_row_virt.test new file mode 100644 index 00000000000..e79869cd8e4 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_row_virt.test @@ -0,0 +1,27 @@ +--source include/have_binlog_format_row.inc +--source include/master-slave.inc +--source include/have_innodb.inc +connection master; + +create table t1 ( + id int auto_increment, + data varchar(32), + virt tinyint as (1), + primary key (id), + key virt (virt) +) engine=innodb default charset=utf8mb4; + +insert into t1 (data) values ('broken'); + +update t1 set data='more broken'; + +--sync_slave_with_master + +select * from t1; + +--connection master +drop table t1; + +--sync_slave_with_master + +--source include/rpl_end.inc |