# delayed works differently in embedded server --source include/not_embedded.inc # # test of DELAYED insert and timestamps # (Can't be tested with purify :( ) # --disable_warnings drop table if exists t1; --enable_warnings 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_BAD_FIELD_ERROR 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 # --disable_warnings DROP TABLE IF EXISTS t1,t2; --enable_warnings 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 # set @old_delayed_updates = @@global.low_priority_updates; set global low_priority_updates = 1; select @@global.low_priority_updates; --disable_warnings drop table if exists t1; --enable_warnings create table t1 (a int, b int); insert into t1 values (1,1); lock table t1 read; connect (update,localhost,root,,); connection update; --echo 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 = "upgrading lock"; --source include/wait_condition.inc connect (select,localhost,root,,); --echo connection: select select * from t1; connection default; --echo 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; --echo # --echo # Bug #47682 strange behaviour of INSERT DELAYED --echo # --disable_warnings DROP TABLE IF EXISTS t1, t2; --enable_warnings CREATE TABLE t1 (f1 integer); CREATE TABLE t2 (f1 integer); FLUSH TABLES WITH READ LOCK; LOCK TABLES t1 READ; --error ER_CANT_UPDATE_WITH_READLOCK INSERT DELAYED INTO t2 VALUES (1); UNLOCK TABLES; DROP TABLE t1, t2; --echo End of 5.1 tests