delayed.test

#
# test of DELAYED insert and timestamps
# (Can't be tested with purify :( )
#

# This tests not performed with embedded server
-- source include/not_embedded.inc

--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);
--sleep 2
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");
# 2 was not enough for --ps-protocol
--sleep 4
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);

# Check what we have now
# must wait so that the delayed thread finishes
# Note: this must be increased if the test fails
--sleep 1
select * from t1 order by a;

DROP TABLE t1;