1
# delayed works differently in embedded server
2
--source include/not_embedded.inc
4
# test of DELAYED insert and timestamps
5
# (Can't be tested with purify :( )
9
drop table if exists t1;
11
create table t1 (a char(10), tmsp timestamp);
12
insert into t1 set a = 1;
13
insert delayed into t1 set a = 2;
14
insert into t1 set a = 3, tmsp=NULL;
15
insert delayed into t1 set a = 4;
16
insert delayed into t1 set a = 5, tmsp = 19711006010203;
17
insert delayed into t1 (a, tmsp) values (6, 19711006010203);
18
insert delayed into t1 (a, tmsp) values (7, NULL);
19
# Wait until the rows are flushed to the table files.
21
insert into t1 set a = 8,tmsp=19711006010203;
22
select * from t1 where tmsp=0;
23
select * from t1 where tmsp=19711006010203;
27
# Test bug when inserting NULL into an auto_increment field with
31
create table t1 (a int not null auto_increment primary key, b char(10));
32
insert delayed into t1 values (1,"b");
33
insert delayed into t1 values (null,"c");
34
insert delayed into t1 values (3,"d"),(null,"e");
36
insert delayed into t1 values (3,"this will give an","error");
37
# Wait until the rows are flushed to the table files.
39
show status like 'not_flushed_delayed_rows';
46
# Bug #12226: Crash when a delayed insert fails due to a duplicate key
48
create table t1 (a int not null primary key);
49
insert into t1 values (1);
50
insert delayed into t1 values (1);
55
# Bug #20195: INSERT DELAYED with auto_increment is assigned wrong values
57
CREATE TABLE t1 ( a int(10) NOT NULL auto_increment, PRIMARY KEY (a));
59
# Make one delayed insert to start the separate thread
60
insert delayed into t1 values(null);
62
# Do some normal inserts
63
insert into t1 values(null);
64
insert into t1 values(null);
66
# Discarded, since the delayed-counter is 2, which is already used
67
insert delayed into t1 values(null);
69
# Discarded, since the delayed-counter is 3, which is already used
70
insert delayed into t1 values(null);
72
# Works, since the delayed-counter is 4, which is unused
73
insert delayed into t1 values(null);
75
# Do some more inserts
76
insert into t1 values(null);
77
insert into t1 values(null);
78
insert into t1 values(null);
80
# Delete one of the above to make a hole
81
delete from t1 where a=6;
83
# Discarded, since the delayed-counter is 5, which is already used
84
insert delayed into t1 values(null);
86
# Works, since the delayed-counter is 6, which is unused (the row we deleted)
87
insert delayed into t1 values(null);
89
# Discarded, since the delayed-counter is 7, which is already used
90
insert delayed into t1 values(null);
92
# Works, since the delayed-counter is 8, which is unused
93
insert delayed into t1 values(null);
95
# Wait until the rows are flushed to the table files.
97
# Check what we have now
98
select * from t1 order by a;
103
# Bug#20627 - INSERT DELAYED does not honour auto_increment_* variables
105
SET @bug20627_old_auto_increment_offset=
106
@@auto_increment_offset;
107
SET @bug20627_old_auto_increment_increment=
108
@@auto_increment_increment;
109
SET @bug20627_old_session_auto_increment_offset=
110
@@session.auto_increment_offset;
111
SET @bug20627_old_session_auto_increment_increment=
112
@@session.auto_increment_increment;
113
SET @@auto_increment_offset= 2;
114
SET @@auto_increment_increment= 3;
115
SET @@session.auto_increment_offset= 4;
116
SET @@session.auto_increment_increment= 5;
118
# Normal insert as reference.
120
c1 INT NOT NULL AUTO_INCREMENT,
123
INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
124
# Check what we have now
130
c1 INT NOT NULL AUTO_INCREMENT,
133
INSERT DELAYED INTO t1 VALUES (NULL),(NULL),(NULL);
134
# Wait until the rows are flushed to the table files.
136
# Check what we have now
141
SET @@auto_increment_offset=
142
@bug20627_old_auto_increment_offset;
143
SET @@auto_increment_increment=
144
@bug20627_old_auto_increment_increment;
145
SET @@session.auto_increment_offset=
146
@bug20627_old_session_auto_increment_offset;
147
SET @@session.auto_increment_increment=
148
@bug20627_old_session_auto_increment_increment;
151
# Bug#20830 - INSERT DELAYED does not honour SET INSERT_ID
153
SET @bug20830_old_auto_increment_offset=
154
@@auto_increment_offset;
155
SET @bug20830_old_auto_increment_increment=
156
@@auto_increment_increment;
157
SET @bug20830_old_session_auto_increment_offset=
158
@@session.auto_increment_offset;
159
SET @bug20830_old_session_auto_increment_increment=
160
@@session.auto_increment_increment;
161
SET @@auto_increment_offset= 2;
162
SET @@auto_increment_increment= 3;
163
SET @@session.auto_increment_offset= 4;
164
SET @@session.auto_increment_increment= 5;
166
# Normal insert as reference.
168
c1 INT(11) NOT NULL AUTO_INCREMENT,
169
c2 INT(11) DEFAULT NULL,
173
INSERT INTO t1 VALUES(NULL, 11), (NULL, 12), (NULL, 13);
174
INSERT INTO t1 VALUES(NULL, 21), (NULL, 22), (NULL, 23);
175
# Restart sequence at a different value.
176
INSERT INTO t1 VALUES( 69, 31), (NULL, 32), (NULL, 33);
177
INSERT INTO t1 VALUES(NULL, 41), (NULL, 42), (NULL, 43);
178
# Restart sequence at a different value.
180
INSERT INTO t1 VALUES(NULL, 51), (NULL, 52), (NULL, 53);
181
INSERT INTO t1 VALUES(NULL, 61), (NULL, 62), (NULL, 63);
182
# Set one value below the maximum value.
183
INSERT INTO t1 VALUES( 49, 71), (NULL, 72), (NULL, 73);
184
INSERT INTO t1 VALUES(NULL, 81), (NULL, 82), (NULL, 83);
185
# Create a duplicate value.
188
INSERT INTO t1 VALUES(NULL, 91);
189
INSERT INTO t1 VALUES (NULL, 92), (NULL, 93);
190
# Check what we have now
192
SELECT COUNT(*) FROM t1;
193
SELECT SUM(c1) FROM t1;
198
c1 INT(11) NOT NULL AUTO_INCREMENT,
199
c2 INT(11) DEFAULT NULL,
203
INSERT DELAYED INTO t1 VALUES(NULL, 11), (NULL, 12), (NULL, 13);
204
INSERT DELAYED INTO t1 VALUES(NULL, 21), (NULL, 22), (NULL, 23);
205
# Restart sequence at a different value.
206
INSERT DELAYED INTO t1 VALUES( 69, 31), (NULL, 32), (NULL, 33);
207
INSERT DELAYED INTO t1 VALUES(NULL, 41), (NULL, 42), (NULL, 43);
208
# Restart sequence at a different value.
210
INSERT DELAYED INTO t1 VALUES(NULL, 51), (NULL, 52), (NULL, 53);
211
INSERT DELAYED INTO t1 VALUES(NULL, 61), (NULL, 62), (NULL, 63);
212
# Set one value below the maximum value.
213
INSERT DELAYED INTO t1 VALUES( 49, 71), (NULL, 72), (NULL, 73);
214
INSERT DELAYED INTO t1 VALUES(NULL, 81), (NULL, 82), (NULL, 83);
215
# Create a duplicate value.
217
INSERT DELAYED INTO t1 VALUES(NULL, 91);
218
INSERT DELAYED INTO t1 VALUES (NULL, 92), (NULL, 93);
219
# Wait until the rows are flushed to the table files.
221
# Check what we have now
223
SELECT COUNT(*) FROM t1;
224
SELECT SUM(c1) FROM t1;
228
SET @@auto_increment_offset=
229
@bug20830_old_auto_increment_offset;
230
SET @@auto_increment_increment=
231
@bug20830_old_auto_increment_increment;
232
SET @@session.auto_increment_offset=
233
@bug20830_old_session_auto_increment_offset;
234
SET @@session.auto_increment_increment=
235
@bug20830_old_session_auto_increment_increment;
238
# BUG#26238 - inserted delayed always inserts 0 for BIT columns
240
CREATE TABLE t1(a BIT);
241
INSERT DELAYED INTO t1 VALUES(1);
243
SELECT HEX(a) FROM t1;
247
# Bug #32676: insert delayed crash with wrong column and function specified
249
CREATE TABLE t1 (a INT);
251
INSERT DELAYED INTO t1 SET b= b();
254
--echo End of 5.0 tests
257
# Bug#27358 INSERT DELAYED does not honour SQL_MODE of the client
260
DROP TABLE IF EXISTS t1,t2;
262
SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO';
264
`id` int(11) PRIMARY KEY auto_increment,
265
`f1` varchar(10) NOT NULL UNIQUE
267
INSERT DELAYED INTO t1 VALUES(0,"test1");
270
SET SQL_MODE='PIPES_AS_CONCAT';
271
INSERT DELAYED INTO t1 VALUES(0,'a' || 'b');
274
SET SQL_MODE='ERROR_FOR_DIVISION_BY_ZERO,STRICT_ALL_TABLES';
276
INSERT DELAYED INTO t1 VALUES(mod(1,0),"test3");
278
`id` int(11) PRIMARY KEY auto_increment,
281
SET SQL_MODE='NO_ZERO_DATE,STRICT_ALL_TABLES,NO_ZERO_IN_DATE';
282
--error ER_TRUNCATED_WRONG_VALUE
283
INSERT DELAYED INTO t2 VALUES (0,'0000-00-00');
284
--error ER_TRUNCATED_WRONG_VALUE
285
INSERT DELAYED INTO t2 VALUES (0,'2007-00-00');
289
# Bug#40536: SELECT is blocked by INSERT DELAYED waiting on upgrading lock,
290
# even with low_priority_updates
293
set @old_delayed_updates = @@global.low_priority_updates;
294
set global low_priority_updates = 1;
295
select @@global.low_priority_updates;
298
drop table if exists t1;
300
create table t1 (a int, b int);
301
insert into t1 values (1,1);
303
connect (update,localhost,root,,);
305
--echo connection: update
306
--send insert delayed into t1 values (2,2);
309
select count(*) = 1 from information_schema.processlist
310
where command = "Delayed insert" and state = "Waiting for table level lock";
311
--source include/wait_condition.inc
312
connect (select,localhost,root,,);
313
--echo connection: select
316
--echo connection: default
323
select count(*) = 1 from information_schema.processlist
324
where command = "Delayed insert" and state = "Waiting for INSERT";
325
--source include/wait_condition.inc
329
set global low_priority_updates = @old_delayed_updates;
333
--echo # Bug #47682 strange behaviour of INSERT DELAYED
337
DROP TABLE IF EXISTS t1, t2;
340
CREATE TABLE t1 (f1 integer);
341
CREATE TABLE t2 (f1 integer);
343
FLUSH TABLES WITH READ LOCK;
346
# ER_CANT_UPDATE_WITH_READLOCK with normal execution
347
# ER_TABLE_NOT_LOCKED when executed as prepared statement
348
--error ER_CANT_UPDATE_WITH_READLOCK, ER_TABLE_NOT_LOCKED
349
INSERT DELAYED INTO t2 VALUES (1);
355
--echo End of 5.1 tests
360
--echo # Bug #47274 assert in open_table on CREATE TABLE <already existing>
364
DROP TABLE IF EXISTS t1;
365
DROP TABLE IF EXISTS t2;
368
CREATE TABLE t1 ( f1 INTEGER AUTO_INCREMENT, PRIMARY KEY (f1));
370
--echo # The following CREATE TABLEs before gave an assert.
372
INSERT DELAYED t1 VALUES (4);
373
--error ER_TABLE_EXISTS_ERROR
374
CREATE TABLE t1 AS SELECT 1 AS f1;
376
REPLACE DELAYED t1 VALUES (5);
377
--error ER_TABLE_EXISTS_ERROR
378
CREATE TABLE t1 AS SELECT 1 AS f1;
380
INSERT DELAYED t1 VALUES (6);
381
--error ER_TABLE_EXISTS_ERROR
382
CREATE TABLE t1 (f1 INTEGER);
384
CREATE TABLE t2 (f1 INTEGER);
385
INSERT DELAYED t1 VALUES (7);
386
--error ER_TABLE_EXISTS_ERROR
387
CREATE TABLE t1 LIKE t2;
394
--echo # Bug#54332 Deadlock with two connections doing LOCK TABLE+INSERT DELAYED
397
--echo # This test is not supposed to work under --ps-protocol since
398
--echo # INSERT DELAYED doesn't work under LOCK TABLES with this protocol.
399
--disable_ps_protocol
402
DROP TABLE IF EXISTS t1, t2;
405
CREATE TABLE t1 (a INT);
406
CREATE TABLE t2 (a INT);
407
CREATE TABLE t3 (a INT);
409
--echo # Test 1: Using LOCK TABLE
411
--echo # Connection con1
412
connect (con1, localhost, root);
415
--echo # Connection default
419
--send INSERT DELAYED INTO t1 VALUES (1)
421
--echo # Connection con1
423
--echo # Wait until INSERT DELAYED is blocked on table 't1'.
425
SELECT COUNT(*) = 1 FROM information_schema.processlist
426
WHERE state = "Waiting for table metadata lock"
427
AND info = "INSERT DELAYED INTO t1 VALUES (1)";
428
--source include/wait_condition.inc
429
--error ER_LOCK_DEADLOCK
430
INSERT DELAYED INTO t2 VALUES (1);
433
--echo # Connection default
435
--echo # Reaping: INSERT DELAYED INTO t1 VALUES (1)
439
--echo # Test 2: Using ALTER TABLE
442
SELECT * FROM t1 WHERE a=0;
444
--echo # Connection con1
447
--send ALTER TABLE t1 COMMENT 'test'
449
--echo # Connection default
451
--echo # Wait until ALTER TABLE is blocked on table 't1'.
453
SELECT COUNT(*) = 1 FROM information_schema.processlist
454
WHERE state = "Waiting for table metadata lock"
455
AND info = "ALTER TABLE t1 COMMENT 'test'";
456
--source include/wait_condition.inc
457
--error ER_LOCK_DEADLOCK
458
INSERT DELAYED INTO t1 VALUES (3);
461
--echo # Connection con1
463
--echo # Reaping: ALTER TABLE t1 COMMENT 'test'
466
--echo # Test 3: Using RENAME TABLE
468
--echo # Connection default
471
INSERT INTO t2 VALUES (1);
473
--echo # Connection con1
476
--send RENAME TABLE t1 to t5, t2 to t4
478
--echo # Connection default
480
--echo # Wait until RENAME TABLE is blocked on table 't1'.
482
SELECT COUNT(*) = 1 FROM information_schema.processlist
483
WHERE state = "Waiting for table metadata lock"
484
AND info = "RENAME TABLE t1 to t5, t2 to t4";
485
--source include/wait_condition.inc
486
--error ER_LOCK_DEADLOCK
487
INSERT DELAYED INTO t1 VALUES (4);
490
--echo # Connection con1
492
--echo # Reaping: RENAME TABLE t1 to t5, t2 to t4
495
--echo # Connection default
497
--echo # Reverting the renames
498
RENAME TABLE t5 to t1, t4 to t2;
500
--echo # Test 4: Two INSERT DELAYED on the same table
503
INSERT INTO t2 VALUES (1);
505
--echo # Connection con2
506
connect (con2, localhost, root);
507
--send LOCK TABLE t1 WRITE, t2 WRITE
509
--echo # Connection con1
511
--echo # Wait until LOCK TABLE is blocked on table 't2'.
513
SELECT COUNT(*) = 1 FROM information_schema.processlist
514
WHERE state = "Waiting for table metadata lock"
515
AND info = "LOCK TABLE t1 WRITE, t2 WRITE";
516
--source include/wait_condition.inc
517
--send INSERT DELAYED INTO t1 VALUES (5)
519
--echo # Connection default
521
--echo # Wait until INSERT DELAYED is blocked on table 't1'.
523
SELECT COUNT(*) = 1 FROM information_schema.processlist
524
WHERE state = "Waiting for table metadata lock"
525
AND info = "INSERT DELAYED INTO t1 VALUES (5)";
526
--source include/wait_condition.inc
527
--error ER_LOCK_DEADLOCK
528
INSERT DELAYED INTO t1 VALUES (6);
531
--echo # Connection con2
533
--echo # Reaping: LOCK TABLE t1 WRITE, t2 WRITE
537
--echo # Connection con1
539
--echo # Reaping: INSERT DELAYED INTO t1 VALUES (5)
542
--echo # Connection default
545
--echo # Test 5: LOCK TABLES + INSERT DELAYED in one connection.
546
--echo # This test has triggered some asserts in metadata locking
547
--echo # subsystem at some point in time..
549
INSERT DELAYED INTO t2 VALUES (7);
553
INSERT DELAYED INTO t2 VALUES (8);
557
--echo # Connection con2
560
--source include/wait_until_disconnected.inc
561
--echo # Connection con1
564
--source include/wait_until_disconnected.inc
566
--echo # Connection default
568
DROP TABLE t1, t2, t3;
573
--echo # Test for bug #56251 "Deadlock with INSERT DELAYED and MERGE tables".
575
connect (con1,localhost,root,,);
578
drop table if exists t1, t2, tm;
580
create table t1(a int);
581
create table t2(a int);
582
create table tm(a int) engine=merge union=(t1, t2);
586
--echo # Connection 'con1'.
589
--send alter table t1 comment 'test'
591
--echo # Connection 'default'.
593
--echo # Wait until ALTER TABLE blocks and starts waiting
594
--echo # for connection 'default'. It should wait with a
595
--echo # pending SNW lock on 't1'.
597
select count(*) = 1 from information_schema.processlist
598
where state = "Waiting for table metadata lock" and
599
info = "alter table t1 comment 'test'";
600
--source include/wait_condition.inc
601
--echo # Attempt to perform delayed insert into 'tm' should not lead
602
--echo # to a deadlock. Instead error ER_DELAYED_NOT_SUPPORTED should
604
--error ER_DELAYED_NOT_SUPPORTED
605
insert delayed into tm values (1);
606
--echo # Unblock ALTER TABLE.
609
--echo # Connection 'con1'.
611
--echo # Reaping ALTER TABLE:
615
--source include/wait_until_disconnected.inc
616
--echo # Connection 'default'.
618
drop tables tm, t1, t2;
622
--echo # Bug#13259227 ASSERTION TABLES->TABLE->POS_IN_TABLE_LIST
623
--echo # == TABLES FAILED IN SQL_BASE.CC:4668
627
DROP TABLE IF EXISTS t1;
630
CREATE TEMPORARY TABLE t1(a int);
631
--error ER_NO_SUCH_TABLE
632
INSERT DELAYED t1 VALUES (1);
634
DROP TEMPORARY TABLE t1;
637
--echo # Bug#13985071: DEPRECATE INSERT DELAYED
640
SET GLOBAL delayed_insert_limit = 100;
641
SET GLOBAL delayed_insert_timeout = 300;
642
SET GLOBAL delayed_queue_size = 1000;
643
SET GLOBAL max_delayed_threads = 20;
644
SET GLOBAL max_insert_delayed_threads = 20;