path: root/mysql-test/r/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 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 '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;
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 <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
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;