diff options
author | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 18:00:34 +0000 |
---|---|---|
committer | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 18:00:34 +0000 |
commit | 3f619478f796eddbba6e39502fe941b285dd97b1 (patch) | |
tree | e2c7b5777f728320e5b5542b6213fd3591ba51e2 /mysql-test/main/delayed.test | |
parent | Initial commit. (diff) | |
download | mariadb-3f619478f796eddbba6e39502fe941b285dd97b1.tar.xz mariadb-3f619478f796eddbba6e39502fe941b285dd97b1.zip |
Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'mysql-test/main/delayed.test')
-rw-r--r-- | mysql-test/main/delayed.test | 639 |
1 files changed, 639 insertions, 0 deletions
diff --git a/mysql-test/main/delayed.test b/mysql-test/main/delayed.test new file mode 100644 index 00000000..38c3de9a --- /dev/null +++ b/mysql-test/main/delayed.test @@ -0,0 +1,639 @@ +# delayed works differently in embedded server +--source include/not_embedded.inc +# Don't test this under valgrind, memory leaks will occur +--source include/not_valgrind.inc +# Avoid CrashReporter popup on Mac +--source include/not_crashrep.inc +# Binary must be compiled with debug for crash to occur +--source include/have_debug_sync.inc + +# +# test of DELAYED insert and timestamps +# (Can't be tested with purify :( ) +# + +# limit the test to engines which support INSERT DELAYED +disable_query_log; +--require include/true.require +select @@global.default_storage_engine in +("memory","myisam","archive","blackhole") and +@@session.default_storage_engine in +("memory","myisam","archive","blackhole") as `TRUE`; +enable_query_log; + +create table t1 (a char(10), tmsp timestamp); +insert into t1 set a = 1; +insert delayed into t1 set a = 2; +insert into t1 set a = 3, tmsp=NULL; +insert delayed into t1 set a = 4; +insert delayed into t1 set a = 5, tmsp = 19711006010203; +insert delayed into t1 (a, tmsp) values (6, 19711006010203); +insert delayed into t1 (a, tmsp) values (7, NULL); +# Wait until the rows are flushed to the table files. +FLUSH TABLE t1; +insert into t1 set a = 8,tmsp=19711006010203; +select * from t1 where tmsp=0; +select * from t1 where tmsp=19711006010203; +drop table t1; + +# +# Test bug when inserting NULL into an auto_increment field with +# INSERT DELAYED +# + +create table t1 (a int not null auto_increment primary key, b char(10)); +insert delayed into t1 values (1,"b"); +insert delayed into t1 values (null,"c"); +insert delayed into t1 values (3,"d"),(null,"e"); +--error 1136 +insert delayed into t1 values (3,"this will give an","error"); +# Wait until the rows are flushed to the table files. +FLUSH TABLE t1; +show status like 'not_flushed_delayed_rows'; +select * from t1; +drop table t1; + +# End of 4.1 tests + +# +# Bug #12226: Crash when a delayed insert fails due to a duplicate key +# +create table t1 (a int not null primary key); +insert into t1 values (1); +insert delayed into t1 values (1); +select * from t1; +drop table t1; + +# +# Bug #20195: INSERT DELAYED with auto_increment is assigned wrong values +# +CREATE TABLE t1 ( a int(10) NOT NULL auto_increment, PRIMARY KEY (a)); + +# Make one delayed insert to start the separate thread +insert delayed into t1 values(null); + +# Do some normal inserts +insert into t1 values(null); +insert into t1 values(null); + +# Discarded, since the delayed-counter is 2, which is already used +insert delayed into t1 values(null); + +# Discarded, since the delayed-counter is 3, which is already used +insert delayed into t1 values(null); + +# Works, since the delayed-counter is 4, which is unused +insert delayed into t1 values(null); + +# Do some more inserts +insert into t1 values(null); +insert into t1 values(null); +insert into t1 values(null); + +# Delete one of the above to make a hole +delete from t1 where a=6; + +# Discarded, since the delayed-counter is 5, which is already used +insert delayed into t1 values(null); + +# Works, since the delayed-counter is 6, which is unused (the row we deleted) +insert delayed into t1 values(null); + +# Discarded, since the delayed-counter is 7, which is already used +insert delayed into t1 values(null); + +# Works, since the delayed-counter is 8, which is unused +insert delayed into t1 values(null); + +# Wait until the rows are flushed to the table files. +FLUSH TABLE t1; +# Check what we have now +select * from t1 order by a; + +DROP TABLE t1; + +# +# Bug#20627 - INSERT DELAYED does not honour auto_increment_* variables +# +SET @bug20627_old_auto_increment_offset= + @@auto_increment_offset; +SET @bug20627_old_auto_increment_increment= + @@auto_increment_increment; +SET @bug20627_old_session_auto_increment_offset= + @@session.auto_increment_offset; +SET @bug20627_old_session_auto_increment_increment= + @@session.auto_increment_increment; +SET @@auto_increment_offset= 2; +SET @@auto_increment_increment= 3; +SET @@session.auto_increment_offset= 4; +SET @@session.auto_increment_increment= 5; +# +# Normal insert as reference. +CREATE TABLE t1 ( + c1 INT NOT NULL AUTO_INCREMENT, + PRIMARY KEY (c1) + ); +INSERT INTO t1 VALUES (NULL),(NULL),(NULL); +# Check what we have now +SELECT * FROM t1; +DROP TABLE t1; +# +# Delayed insert. +CREATE TABLE t1 ( + c1 INT NOT NULL AUTO_INCREMENT, + PRIMARY KEY (c1) + ); +INSERT DELAYED INTO t1 VALUES (NULL),(NULL),(NULL); +# Wait until the rows are flushed to the table files. +FLUSH TABLE t1; +# Check what we have now +SELECT * FROM t1; +DROP TABLE t1; +# +# Cleanup +SET @@auto_increment_offset= + @bug20627_old_auto_increment_offset; +SET @@auto_increment_increment= + @bug20627_old_auto_increment_increment; +SET @@session.auto_increment_offset= + @bug20627_old_session_auto_increment_offset; +SET @@session.auto_increment_increment= + @bug20627_old_session_auto_increment_increment; + +# +# Bug#20830 - INSERT DELAYED does not honour SET INSERT_ID +# +SET @bug20830_old_auto_increment_offset= + @@auto_increment_offset; +SET @bug20830_old_auto_increment_increment= + @@auto_increment_increment; +SET @bug20830_old_session_auto_increment_offset= + @@session.auto_increment_offset; +SET @bug20830_old_session_auto_increment_increment= + @@session.auto_increment_increment; +SET @@auto_increment_offset= 2; +SET @@auto_increment_increment= 3; +SET @@session.auto_increment_offset= 4; +SET @@session.auto_increment_increment= 5; +# +# Normal insert as reference. +CREATE TABLE t1 ( + c1 INT(11) NOT NULL AUTO_INCREMENT, + c2 INT(11) DEFAULT NULL, + PRIMARY KEY (c1) + ); +SET insert_id= 14; +INSERT INTO t1 VALUES(NULL, 11), (NULL, 12), (NULL, 13); +INSERT INTO t1 VALUES(NULL, 21), (NULL, 22), (NULL, 23); +# Restart sequence at a different value. +INSERT INTO t1 VALUES( 69, 31), (NULL, 32), (NULL, 33); +INSERT INTO t1 VALUES(NULL, 41), (NULL, 42), (NULL, 43); +# Restart sequence at a different value. +SET insert_id= 114; +INSERT INTO t1 VALUES(NULL, 51), (NULL, 52), (NULL, 53); +INSERT INTO t1 VALUES(NULL, 61), (NULL, 62), (NULL, 63); +# Set one value below the maximum value. +INSERT INTO t1 VALUES( 49, 71), (NULL, 72), (NULL, 73); +INSERT INTO t1 VALUES(NULL, 81), (NULL, 82), (NULL, 83); +# Create a duplicate value. +SET insert_id= 114; +--error ER_DUP_ENTRY +INSERT INTO t1 VALUES(NULL, 91); +INSERT INTO t1 VALUES (NULL, 92), (NULL, 93); +# Check what we have now +SELECT * FROM t1; +SELECT COUNT(*) FROM t1; +SELECT SUM(c1) FROM t1; +DROP TABLE t1; +# +# Delayed insert. +CREATE TABLE t1 ( + c1 INT(11) NOT NULL AUTO_INCREMENT, + c2 INT(11) DEFAULT NULL, + PRIMARY KEY (c1) + ); +SET insert_id= 14; +INSERT DELAYED INTO t1 VALUES(NULL, 11), (NULL, 12), (NULL, 13); +INSERT DELAYED INTO t1 VALUES(NULL, 21), (NULL, 22), (NULL, 23); +# Restart sequence at a different value. +INSERT DELAYED INTO t1 VALUES( 69, 31), (NULL, 32), (NULL, 33); +INSERT DELAYED INTO t1 VALUES(NULL, 41), (NULL, 42), (NULL, 43); +# Restart sequence at a different value. +SET insert_id= 114; +INSERT DELAYED INTO t1 VALUES(NULL, 51), (NULL, 52), (NULL, 53); +INSERT DELAYED INTO t1 VALUES(NULL, 61), (NULL, 62), (NULL, 63); +# Set one value below the maximum value. +INSERT DELAYED INTO t1 VALUES( 49, 71), (NULL, 72), (NULL, 73); +INSERT DELAYED INTO t1 VALUES(NULL, 81), (NULL, 82), (NULL, 83); +# Create a duplicate value. +SET insert_id= 114; +INSERT DELAYED INTO t1 VALUES(NULL, 91); +INSERT DELAYED INTO t1 VALUES (NULL, 92), (NULL, 93); +# Wait until the rows are flushed to the table files. +FLUSH TABLE t1; +# Check what we have now +SELECT * FROM t1; +SELECT COUNT(*) FROM t1; +SELECT SUM(c1) FROM t1; +DROP TABLE t1; +# +# Cleanup +SET @@auto_increment_offset= + @bug20830_old_auto_increment_offset; +SET @@auto_increment_increment= + @bug20830_old_auto_increment_increment; +SET @@session.auto_increment_offset= + @bug20830_old_session_auto_increment_offset; +SET @@session.auto_increment_increment= + @bug20830_old_session_auto_increment_increment; + +# +# BUG#26238 - inserted delayed always inserts 0 for BIT columns +# +CREATE TABLE t1(a BIT); +INSERT DELAYED INTO t1 VALUES(1); +FLUSH TABLE t1; +SELECT HEX(a) FROM t1; +DROP TABLE t1; + +# +# Bug #32676: insert delayed crash with wrong column and function specified +# +CREATE TABLE t1 (a INT); +--error ER_SP_DOES_NOT_EXIST +INSERT DELAYED INTO t1 SET a= b(); +--error ER_BAD_FIELD_ERROR +INSERT DELAYED INTO t1 SET b= 1; +--error ER_SP_DOES_NOT_EXIST +INSERT DELAYED INTO t1 SET b= b(); +DROP TABLE t1; + +--echo End of 5.0 tests + +# +# Bug#27358 INSERT DELAYED does not honour SQL_MODE of the client +# +SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO'; +CREATE TABLE `t1` ( + `id` int(11) PRIMARY KEY auto_increment, + `f1` varchar(10) NOT NULL UNIQUE +); +INSERT DELAYED INTO t1 VALUES(0,"test1"); +sleep 1; +SELECT * FROM t1; +SET SQL_MODE='PIPES_AS_CONCAT'; +INSERT DELAYED INTO t1 VALUES(0,'a' || 'b'); +sleep 1; +SELECT * FROM t1; +SET SQL_MODE='ERROR_FOR_DIVISION_BY_ZERO,STRICT_ALL_TABLES'; +--error 1365 +INSERT DELAYED INTO t1 VALUES(mod(1,0),"test3"); +CREATE TABLE t2 ( + `id` int(11) PRIMARY KEY auto_increment, + `f1` date +); +SET SQL_MODE='NO_ZERO_DATE,STRICT_ALL_TABLES,NO_ZERO_IN_DATE'; +--error ER_TRUNCATED_WRONG_VALUE +INSERT DELAYED INTO t2 VALUES (0,'0000-00-00'); +--error ER_TRUNCATED_WRONG_VALUE +INSERT DELAYED INTO t2 VALUES (0,'2007-00-00'); +DROP TABLE t1,t2; + +# +# Bug#40536: SELECT is blocked by INSERT DELAYED waiting on upgrading lock, +# even with low_priority_updates +# +--disable_service_connection + +set @old_delayed_updates = @@global.low_priority_updates; +set global low_priority_updates = 1; +select @@global.low_priority_updates; + +create table t1 (a int, b int); +insert into t1 values (1,1); +lock table t1 read; +connect (update,localhost,root,,); +connection update; +--send insert delayed into t1 values (2,2); +connection default; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where command = "Delayed insert" and state = "Waiting for table level lock"; +--source include/wait_condition.inc +connect (select,localhost,root,,); +select * from t1; +connection default; +select * from t1; +connection default; +disconnect update; +disconnect select; +unlock tables; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where command = "Delayed insert" and state = "Waiting for INSERT"; +--source include/wait_condition.inc +select * from t1; +drop table t1; + +set global low_priority_updates = @old_delayed_updates; +--enable_service_connection + +--echo # +--echo # Bug #47682 strange behaviour of INSERT DELAYED +--echo # + +CREATE TABLE t1 (f1 integer); +CREATE TABLE t2 (f1 integer); + +FLUSH TABLES WITH READ LOCK; +LOCK TABLES t1 READ; + +# ER_CANT_UPDATE_WITH_READLOCK with normal execution +# ER_TABLE_NOT_LOCKED when executed as prepared statement +--error ER_CANT_UPDATE_WITH_READLOCK, ER_TABLE_NOT_LOCKED +INSERT DELAYED INTO t2 VALUES (1); + +UNLOCK TABLES; +DROP TABLE t1, t2; + + +--echo End of 5.1 tests + + + +--echo # +--echo # Bug #47274 assert in open_table on CREATE TABLE <already existing> +--echo # + +CREATE TABLE t1 ( f1 INTEGER AUTO_INCREMENT, PRIMARY KEY (f1)); + +--echo # The following CREATE TABLEs before gave an assert. + +INSERT DELAYED t1 VALUES (4); +--error ER_TABLE_EXISTS_ERROR +CREATE TABLE t1 AS SELECT 1 AS f1; + +REPLACE DELAYED t1 VALUES (5); +--error ER_TABLE_EXISTS_ERROR +CREATE TABLE t1 AS SELECT 1 AS f1; + +INSERT DELAYED t1 VALUES (6); +--error ER_TABLE_EXISTS_ERROR +CREATE TABLE t1 (f1 INTEGER); + +CREATE TABLE t2 (f1 INTEGER); +INSERT DELAYED t1 VALUES (7); +--error ER_TABLE_EXISTS_ERROR +CREATE TABLE t1 LIKE t2; + +DROP TABLE t2, t1; + +# The following test is disabled as it fails randomly +if (0) +{ +--echo # +--echo # Bug#54332 Deadlock with two connections doing LOCK TABLE+INSERT DELAYED +--echo # + +--echo # This test is not supposed to work under --ps-protocol since +--echo # INSERT DELAYED doesn't work under LOCK TABLES with this protocol. +--disable_ps_protocol +--disable_view_protocol + +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (a INT); +CREATE TABLE t3 (a INT); + +--echo # Test 1: Using LOCK TABLE + +connect (con1, localhost, root); +LOCK TABLE t1 WRITE; + +connection default; +LOCK TABLE t2 WRITE; +--echo # Sending: +--send INSERT DELAYED INTO t1 VALUES (1) + +connection con1; +--echo # Wait until INSERT DELAYED is blocked on table 't1'. +let $wait_condition= + SELECT COUNT(*) = 1 FROM information_schema.processlist + WHERE state = "Waiting for table metadata lock" + AND info = "INSERT DELAYED INTO t1 VALUES (1)"; +--source include/wait_condition.inc +--error ER_LOCK_DEADLOCK +INSERT DELAYED INTO t2 VALUES (1); +UNLOCK TABLES; + +connection default; +--echo # Reaping: INSERT DELAYED INTO t1 VALUES (1) +--reap +UNLOCK TABLES; + +--echo # Test 2: Using ALTER TABLE + +START TRANSACTION; +SELECT * FROM t1 WHERE a=0; + +connection con1; +--echo # Sending: +--send ALTER TABLE t1 MODIFY a INT UNSIGNED; + +connection default; +--echo # Wait until ALTER TABLE is blocked on table 't1'. +let $wait_condition= + SELECT COUNT(*) = 1 FROM information_schema.processlist + WHERE state = "Waiting for table metadata lock" + AND info LIKE "ALTER TABLE t1%"; +--source include/wait_condition.inc +--error ER_LOCK_DEADLOCK +INSERT DELAYED INTO t1 VALUES (3); +COMMIT; + +connection con1; +--echo # Reaping: ALTER TABLE t1 COMMENT 'test' +--reap + +--echo # Test 3: Using RENAME TABLE + +connection default; +START TRANSACTION; +INSERT INTO t2 VALUES (1); + +connection con1; +--echo # Sending: +--send RENAME TABLE t1 to t5, t2 to t4 + +connection default; +--echo # Wait until RENAME TABLE is blocked on table 't1'. +let $wait_condition= + SELECT COUNT(*) = 1 FROM information_schema.processlist + WHERE state = "Waiting for table metadata lock" + AND info = "RENAME TABLE t1 to t5, t2 to t4"; +--source include/wait_condition.inc +--error ER_LOCK_DEADLOCK +INSERT DELAYED INTO t1 VALUES (4); +COMMIT; + +connection con1; +--echo # Reaping: RENAME TABLE t1 to t5, t2 to t4 +--reap + +connection default; +--echo # Reverting the renames +RENAME TABLE t5 to t1, t4 to t2; + +--echo # Test 4: Two INSERT DELAYED on the same table + +START TRANSACTION; +INSERT INTO t2 VALUES (1); + +connect (con2, localhost, root); +--send LOCK TABLE t1 WRITE, t2 WRITE + +connection con1; +--echo # Wait until LOCK TABLE is blocked on table 't2'. +let $wait_condition= + SELECT COUNT(*) = 1 FROM information_schema.processlist + WHERE state = "Waiting for table metadata lock" + AND info = "LOCK TABLE t1 WRITE, t2 WRITE"; +--source include/wait_condition.inc +--send INSERT DELAYED INTO t1 VALUES (5) + +connection default; +--echo # Wait until INSERT DELAYED is blocked on table 't1'. +let $wait_condition= + SELECT COUNT(*) = 1 FROM information_schema.processlist + WHERE state = "Waiting for table metadata lock" + AND info = "INSERT DELAYED INTO t1 VALUES (5)"; +--source include/wait_condition.inc +--error ER_LOCK_DEADLOCK +INSERT DELAYED INTO t1 VALUES (6); +COMMIT; + +connection con2; +--echo # Reaping: LOCK TABLE t1 WRITE, t2 WRITE +--reap +UNLOCK TABLES; + +connection con1; +--echo # Reaping: INSERT DELAYED INTO t1 VALUES (5) +--reap + +connection default; + +--echo # Test 5: LOCK TABLES + INSERT DELAYED in one connection. +--echo # This test has triggered some asserts in metadata locking +--echo # subsystem at some point in time.. +LOCK TABLE t1 WRITE; +INSERT DELAYED INTO t2 VALUES (7); +UNLOCK TABLES; +SET AUTOCOMMIT= 0; +LOCK TABLE t1 WRITE; +INSERT DELAYED INTO t2 VALUES (8); +UNLOCK TABLES; +SET AUTOCOMMIT= 1; + +connection con2; +disconnect con2; +--source include/wait_until_disconnected.inc +connection con1; +disconnect con1; +--source include/wait_until_disconnected.inc + +connection default; +DROP TABLE t1, t2, t3; +--enable_ps_protocol +--enable_view_protocol +} + +--echo # +--echo # Test for bug #56251 "Deadlock with INSERT DELAYED and MERGE tables". +--echo # +--disable_view_protocol +connect (con1,localhost,root,,); +connection default; +create table t1(a int); +create table t2(a int); +create table tm(a int) engine=merge union=(t1, t2); +begin; +select * from t1; + +connection con1; +--echo # Sending: +--send alter table t1 comment 'test' + +connection default; +--echo # Wait until ALTER TABLE blocks and starts waiting +--echo # for connection 'default'. It should wait with a +--echo # pending SNW lock on 't1'. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for table metadata lock" and + info = "alter table t1 comment 'test'"; +--source include/wait_condition.inc +--echo # Attempt to perform delayed insert into 'tm' should not lead +--echo # to a deadlock. Instead error ER_DELAYED_NOT_SUPPORTED should +--echo # be emitted. +--error ER_DELAYED_NOT_SUPPORTED +insert delayed into tm values (1); +--echo # Unblock ALTER TABLE. +commit; + +connection con1; +--echo # Reaping ALTER TABLE: +--reap + +disconnect con1; +--source include/wait_until_disconnected.inc +connection default; +drop tables tm, t1, t2; + +--echo # +--echo # MDEV-9621 INSERT DELAYED fails on insert for tables with many columns +--echo # + +CREATE TABLE t1 ( + a int,b int,c int,d int,e int,f int,g int,h int,i int,j int,k int,l int,m int,n int,o int,p int,q int,r int,s int,t int,u int,v int,x int,y int,z int +) ENGINE=MyISAM; + +INSERT DELAYED INTO t1 (a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,x,y,z) +values (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1); +INSERT DELAYED INTO t1 (a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,x,y,z) +values (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1); +drop table t1; + +--echo # +--echo # INSERT DELAYED hangs if table was crashed +--echo # + +create table t1 (a int, b int) engine=myisam; +insert into t1 values (1,1); + +# Will come back with t1 crashed. +--source include/crash_mysqld.inc + +call mtr.add_suppression(" marked as crashed and should be repaired"); +call mtr.add_suppression("Checking table"); + +--enable_prepare_warnings +--replace_result '\\' '/' +insert delayed into t1 values (2,2); +--disable_prepare_warnings +insert delayed into t1 values (3,3); +flush tables t1; +select * from t1; +drop table t1; + +--echo # +--echo # MDEV-26546 SIGSEGV's in spider_db_connect on SHOW TABLE and +--echo # spider_db_mbase::connect (and SIGSEGV's in check_vcol_forward_refs +--echo # and inline_mysql_mutex_lock) +--echo # + +CREATE TABLE t1 (c0 INT,UNIQUE (c0) USING HASH) ENGINE=MYISAM; +INSERT DELAYED INTO t1 VALUES (0); +INSERT DELAYED INTO t1 VALUES (0); +DROP TABLE t1; + +--enable_view_protocol |