Edit File: delayed.result
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 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 'f1' at row 1 INSERT DELAYED INTO t2 VALUES (0,'2007-00-00'); ERROR 22007: Incorrect date value: '2007-00-00' for column '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; connection: update insert delayed into t1 values (2,2);; connection: select select * from t1; a b 1 1 connection: default select * from t1; a b 1 1 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 <already existing> # 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 # Connection con1 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 COMMENT 'test'; # 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); # Connection con2 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 # Connection con1 # Connection default DROP TABLE t1, t2, t3; # # Test for bug #56251 "Deadlock with INSERT DELAYED and MERGE tables". # 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: # Connection 'default'. drop tables tm, t1, t2;
Back to File Manager