drop table if exists t1; 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); FLUSH TABLE t1; insert into t1 set a = 8,tmsp=19711006010203; select * from t1 where tmsp=0; a tmsp select * from t1 where tmsp=19711006010203; a tmsp 5 1971-10-06 01:02:03 6 1971-10-06 01:02:03 8 1971-10-06 01:02:03 drop table t1; 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"); insert delayed into t1 values (3,"this will give an","error"); ERROR 21S01: Column count doesn't match value count at row 1 FLUSH TABLE t1; show status like 'not_flushed_delayed_rows'; Variable_name Value Not_flushed_delayed_rows 0 select * from t1; a b 1 b 2 c 3 d 4 e drop table t1; create table t1 (a int not null primary key); insert into t1 values (1); insert delayed into t1 values (1); select * from t1; a 1 drop table t1; CREATE TABLE t1 ( a int(10) NOT NULL auto_increment, PRIMARY KEY (a)); insert delayed into t1 values(null); insert into t1 values(null); insert into t1 values(null); insert delayed into t1 values(null); insert delayed into t1 values(null); insert delayed into t1 values(null); insert into t1 values(null); insert into t1 values(null); insert into t1 values(null); delete from t1 where a=6; insert delayed into t1 values(null); insert delayed into t1 values(null); insert delayed into t1 values(null); insert delayed into t1 values(null); FLUSH TABLE t1; select * from t1 order by a; a 1 2 3 4 5 7 8 9 10 11 12 13 DROP TABLE t1; 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; CREATE TABLE t1 ( c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1) ); INSERT INTO t1 VALUES (NULL),(NULL),(NULL); SELECT * FROM t1; c1 4 9 14 DROP TABLE t1; CREATE TABLE t1 ( c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1) ); INSERT DELAYED INTO t1 VALUES (NULL),(NULL),(NULL); FLUSH TABLE t1; SELECT * FROM t1; c1 4 9 14 DROP TABLE t1; 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; 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; 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); INSERT INTO t1 VALUES( 69, 31), (NULL, 32), (NULL, 33); INSERT INTO t1 VALUES(NULL, 41), (NULL, 42), (NULL, 43); SET insert_id= 114; INSERT INTO t1 VALUES(NULL, 51), (NULL, 52), (NULL, 53); INSERT INTO t1 VALUES(NULL, 61), (NULL, 62), (NULL, 63); INSERT INTO t1 VALUES( 49, 71), (NULL, 72), (NULL, 73); INSERT INTO t1 VALUES(NULL, 81), (NULL, 82), (NULL, 83); SET insert_id= 114; INSERT INTO t1 VALUES(NULL, 91); ERROR 23000: Duplicate entry '114' for key 'PRIMARY' INSERT INTO t1 VALUES (NULL, 92), (NULL, 93); SELECT * FROM t1; c1 c2 14 11 19 12 24 13 29 21 34 22 39 23 69 31 74 32 79 33 84 41 89 42 94 43 114 51 119 52 124 53 129 61 134 62 139 63 49 71 144 72 149 73 154 81 159 82 164 83 169 92 174 93 SELECT COUNT(*) FROM t1; COUNT(*) 26 SELECT SUM(c1) FROM t1; SUM(c1) 2569 DROP TABLE t1; 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); INSERT DELAYED INTO t1 VALUES( 69, 31), (NULL, 32), (NULL, 33); INSERT DELAYED INTO t1 VALUES(NULL, 41), (NULL, 42), (NULL, 43); 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); INSERT DELAYED INTO t1 VALUES( 49, 71), (NULL, 72), (NULL, 73); INSERT DELAYED INTO t1 VALUES(NULL, 81), (NULL, 82), (NULL, 83); SET insert_id= 114; INSERT DELAYED INTO t1 VALUES(NULL, 91); INSERT DELAYED INTO t1 VALUES (NULL, 92), (NULL, 93); FLUSH TABLE t1; SELECT * FROM t1; c1 c2 14 11 19 12 24 13 29 21 34 22 39 23 69 31 74 32 79 33 84 41 89 42 94 43 114 51 119 52 124 53 129 61 134 62 139 63 49 71 144 72 149 73 154 81 159 82 164 83 169 92 174 93 SELECT COUNT(*) FROM t1; COUNT(*) 26 SELECT SUM(c1) FROM t1; SUM(c1) 2569 DROP TABLE t1; 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; CREATE TABLE t1(a BIT); INSERT DELAYED INTO t1 VALUES(1); FLUSH TABLE t1; SELECT HEX(a) FROM t1; HEX(a) 1 DROP TABLE t1; CREATE TABLE t1 (a INT); INSERT DELAYED INTO t1 SET a= b(); ERROR 42000: FUNCTION test.b does not exist INSERT DELAYED INTO t1 SET b= 1; ERROR 42S22: Unknown column 'b' in 'field list' INSERT DELAYED INTO t1 SET b= b(); ERROR 42000: FUNCTION test.b does not exist DROP TABLE t1; End of 5.0 tests DROP TABLE IF EXISTS t1,t2; 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"); SELECT * FROM t1; id f1 0 test1 SET SQL_MODE='PIPES_AS_CONCAT'; INSERT DELAYED INTO t1 VALUES(0,'a' || 'b'); SELECT * FROM t1; id f1 0 test1 1 ab SET SQL_MODE='ERROR_FOR_DIVISION_BY_ZERO,STRICT_ALL_TABLES'; INSERT DELAYED INTO t1 VALUES(mod(1,0),"test3"); ERROR 22012: Division by 0 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'; INSERT DELAYED INTO t2 VALUES (0,'0000-00-00'); ERROR 22007: Incorrect date value: '0000-00-00' for column `test`.`t2`.`f1` at row 1 INSERT DELAYED INTO t2 VALUES (0,'2007-00-00'); ERROR 22007: Incorrect date value: '2007-00-00' for column `test`.`t2`.`f1` at row 1 DROP TABLE t1,t2; set @old_delayed_updates = @@global.low_priority_updates; set global low_priority_updates = 1; select @@global.low_priority_updates; @@global.low_priority_updates 1 drop table if exists t1; create table t1 (a int, b int); insert into t1 values (1,1); lock table t1 read; connect update,localhost,root,,; connection update; insert delayed into t1 values (2,2);; connection default; connect select,localhost,root,,; select * from t1; a b 1 1 connection default; select * from t1; a b 1 1 connection default; disconnect update; disconnect select; unlock tables; select * from t1; a b 1 1 2 2 drop table t1; set global low_priority_updates = @old_delayed_updates; # # Bug #47682 strange behaviour of INSERT DELAYED # DROP TABLE IF EXISTS t1, t2; CREATE TABLE t1 (f1 integer); CREATE TABLE t2 (f1 integer); FLUSH TABLES WITH READ LOCK; LOCK TABLES t1 READ; INSERT DELAYED INTO t2 VALUES (1); Got one of the listed errors UNLOCK TABLES; DROP TABLE t1, t2; End of 5.1 tests # # Bug #47274 assert in open_table on CREATE TABLE # DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; CREATE TABLE t1 ( f1 INTEGER AUTO_INCREMENT, PRIMARY KEY (f1)); # The following CREATE TABLEs before gave an assert. INSERT DELAYED t1 VALUES (4); CREATE TABLE t1 AS SELECT 1 AS f1; ERROR 42S01: Table 't1' already exists REPLACE DELAYED t1 VALUES (5); CREATE TABLE t1 AS SELECT 1 AS f1; ERROR 42S01: Table 't1' already exists INSERT DELAYED t1 VALUES (6); CREATE TABLE t1 (f1 INTEGER); ERROR 42S01: Table 't1' already exists CREATE TABLE t2 (f1 INTEGER); INSERT DELAYED t1 VALUES (7); CREATE TABLE t1 LIKE t2; ERROR 42S01: Table 't1' already exists DROP TABLE t2; DROP TABLE t1; # # Bug#54332 Deadlock with two connections doing LOCK TABLE+INSERT DELAYED # # This test is not supposed to work under --ps-protocol since # INSERT DELAYED doesn't work under LOCK TABLES with this protocol. DROP TABLE IF EXISTS t1, t2; CREATE TABLE t1 (a INT); CREATE TABLE t2 (a INT); CREATE TABLE t3 (a INT); # Test 1: Using LOCK TABLE connect con1, localhost, root; LOCK TABLE t1 WRITE; connection default; LOCK TABLE t2 WRITE; # Sending: INSERT DELAYED INTO t1 VALUES (1); connection con1; # Wait until INSERT DELAYED is blocked on table 't1'. INSERT DELAYED INTO t2 VALUES (1); ERROR 40001: Deadlock found when trying to get lock; try restarting transaction UNLOCK TABLES; connection default; # Reaping: INSERT DELAYED INTO t1 VALUES (1) UNLOCK TABLES; # Test 2: Using ALTER TABLE START TRANSACTION; SELECT * FROM t1 WHERE a=0; a connection con1; # Sending: ALTER TABLE t1 MODIFY a INT UNSIGNED;; connection default; # Wait until ALTER TABLE is blocked on table 't1'. INSERT DELAYED INTO t1 VALUES (3); ERROR 40001: Deadlock found when trying to get lock; try restarting transaction COMMIT; connection con1; # Reaping: ALTER TABLE t1 COMMENT 'test' # Test 3: Using RENAME TABLE connection default; START TRANSACTION; INSERT INTO t2 VALUES (1); connection con1; # Sending: RENAME TABLE t1 to t5, t2 to t4; connection default; # Wait until RENAME TABLE is blocked on table 't1'. INSERT DELAYED INTO t1 VALUES (4); ERROR 40001: Deadlock found when trying to get lock; try restarting transaction COMMIT; connection con1; # Reaping: RENAME TABLE t1 to t5, t2 to t4 connection default; # Reverting the renames RENAME TABLE t5 to t1, t4 to t2; # Test 4: Two INSERT DELAYED on the same table START TRANSACTION; INSERT INTO t2 VALUES (1); connect con2, localhost, root; LOCK TABLE t1 WRITE, t2 WRITE; connection con1; # Wait until LOCK TABLE is blocked on table 't2'. INSERT DELAYED INTO t1 VALUES (5); connection default; # Wait until INSERT DELAYED is blocked on table 't1'. INSERT DELAYED INTO t1 VALUES (6); ERROR 40001: Deadlock found when trying to get lock; try restarting transaction COMMIT; connection con2; # Reaping: LOCK TABLE t1 WRITE, t2 WRITE UNLOCK TABLES; connection con1; # Reaping: INSERT DELAYED INTO t1 VALUES (5) connection default; # Test 5: LOCK TABLES + INSERT DELAYED in one connection. # This test has triggered some asserts in metadata locking # 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; connection con1; disconnect con1; connection default; DROP TABLE t1, t2, t3; # # Test for bug #56251 "Deadlock with INSERT DELAYED and MERGE tables". # connect con1,localhost,root,,; connection default; drop table if exists t1, t2, tm; create table t1(a int); create table t2(a int); create table tm(a int) engine=merge union=(t1, t2); begin; select * from t1; a connection con1; # Sending: alter table t1 comment 'test'; connection default; # Wait until ALTER TABLE blocks and starts waiting # for connection 'default'. It should wait with a # pending SNW lock on 't1'. # Attempt to perform delayed insert into 'tm' should not lead # to a deadlock. Instead error ER_DELAYED_NOT_SUPPORTED should # be emitted. insert delayed into tm values (1); ERROR HY000: DELAYED option not supported for table 'tm' # Unblock ALTER TABLE. commit; connection con1; # Reaping ALTER TABLE: disconnect con1; connection default; drop tables tm, t1, t2; # # MDEV-9621 INSERT DELAYED fails on insert for tables with many columns # 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; # # INSERT DELAYED hangs if table was crashed # create table t1 (a int, b int) engine=myisam; insert into t1 values (1,1); SET debug_dbug="d,crash_shutdown"; shutdown; ERROR HY000: Lost connection to MySQL server during query call mtr.add_suppression(" marked as crashed and should be repaired"); call mtr.add_suppression("Checking table"); insert delayed into t1 values (2,2); Warnings: Error 145 Table './test/t1' is marked as crashed and should be repaired Error 1194 Table 't1' is marked as crashed and should be repaired Error 1034 1 client is using or hasn't closed the table properly insert delayed into t1 values (3,3); flush tables t1; select * from t1; a b 1 1 2 2 3 3 drop table t1;