From a7abddeffa6a760ce948c2dfb007cdf3f1a369d5 Mon Sep 17 00:00:00 2001
From: Michael Widenius <monty@mariadb.org>
Date: Fri, 9 Mar 2018 14:05:35 +0200
Subject: Create 'main' test directory and move 't' and 'r' there

---
 mysql-test/main/delayed.test | 644 +++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 644 insertions(+)
 create mode 100644 mysql-test/main/delayed.test

(limited to 'mysql-test/main/delayed.test')

diff --git a/mysql-test/main/delayed.test b/mysql-test/main/delayed.test
new file mode 100644
index 00000000000..573985fec1f
--- /dev/null
+++ b/mysql-test/main/delayed.test
@@ -0,0 +1,644 @@
+# delayed works differently in embedded server
+--source include/not_embedded.inc
+# Don't test this under valgrind, memory leaks will occur
+--source include/not_valgrind.inc
+# Avoid CrashReporter popup on Mac
+--source include/not_crashrep.inc
+# Binary must be compiled with debug for crash to occur
+--source include/have_debug_sync.inc
+
+#
+# test of DELAYED insert and timestamps
+# (Can't be tested with purify :( )
+#
+
+# limit the test to engines which support INSERT DELAYED
+disable_query_log;
+--require include/true.require
+select @@global.storage_engine in
+("memory","myisam","archive","blackhole") and
+@@session.storage_engine in
+("memory","myisam","archive","blackhole") as `TRUE`;
+enable_query_log;
+
+--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_SP_DOES_NOT_EXIST
+INSERT DELAYED INTO t1 SET a= b();
+--error ER_BAD_FIELD_ERROR
+INSERT DELAYED INTO t1 SET b= 1;
+--error ER_SP_DOES_NOT_EXIST
+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;
+--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 = "Waiting for table level lock";
+--source include/wait_condition.inc
+connect (select,localhost,root,,);
+select * from t1;
+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;
+
+# ER_CANT_UPDATE_WITH_READLOCK with normal execution
+# ER_TABLE_NOT_LOCKED when executed as prepared statement
+--error ER_CANT_UPDATE_WITH_READLOCK, ER_TABLE_NOT_LOCKED
+INSERT DELAYED INTO t2 VALUES (1);
+
+UNLOCK TABLES;
+DROP TABLE t1, t2;
+
+
+--echo End of 5.1 tests
+
+
+
+--echo #
+--echo # Bug #47274 assert in open_table on CREATE TABLE <already existing>
+--echo #
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+DROP TABLE IF EXISTS t2;
+--enable_warnings
+
+CREATE TABLE t1 ( f1 INTEGER AUTO_INCREMENT, PRIMARY KEY (f1));
+
+--echo # The following CREATE TABLEs before gave an assert.
+
+INSERT DELAYED t1 VALUES (4);
+--error ER_TABLE_EXISTS_ERROR
+CREATE TABLE t1 AS SELECT 1 AS f1;
+
+REPLACE DELAYED t1 VALUES (5);
+--error ER_TABLE_EXISTS_ERROR
+CREATE TABLE t1 AS SELECT 1 AS f1;
+
+INSERT DELAYED t1 VALUES (6);
+--error ER_TABLE_EXISTS_ERROR
+CREATE TABLE t1 (f1 INTEGER);
+
+CREATE TABLE t2 (f1 INTEGER);
+INSERT DELAYED t1 VALUES (7);
+--error ER_TABLE_EXISTS_ERROR
+CREATE TABLE t1 LIKE t2;
+
+DROP TABLE t2;
+DROP TABLE t1;
+
+
+--echo #
+--echo # Bug#54332 Deadlock with two connections doing LOCK TABLE+INSERT DELAYED
+--echo #
+
+--echo # This test is not supposed to work under --ps-protocol since
+--echo # INSERT DELAYED doesn't work under LOCK TABLES with this protocol.
+--disable_ps_protocol
+
+--disable_warnings
+DROP TABLE IF EXISTS t1, t2;
+--enable_warnings
+
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT);
+CREATE TABLE t3 (a INT);
+
+--echo # Test 1: Using LOCK TABLE
+
+connect (con1, localhost, root);
+LOCK TABLE t1 WRITE;
+
+connection default;
+LOCK TABLE t2 WRITE;
+--echo # Sending:
+--send INSERT DELAYED INTO t1 VALUES (1)
+
+connection con1;
+--echo # Wait until INSERT DELAYED is blocked on table 't1'.
+let $wait_condition=
+  SELECT COUNT(*) = 1 FROM information_schema.processlist
+  WHERE state = "Waiting for table metadata lock"
+  AND info = "INSERT DELAYED INTO t1 VALUES (1)";
+--source include/wait_condition.inc
+--error ER_LOCK_DEADLOCK
+INSERT DELAYED INTO t2 VALUES (1);
+UNLOCK TABLES;
+
+connection default;
+--echo # Reaping: INSERT DELAYED INTO t1 VALUES (1)
+--reap
+UNLOCK TABLES;
+
+--echo # Test 2: Using ALTER TABLE
+
+START TRANSACTION;
+SELECT * FROM t1 WHERE a=0;
+
+connection con1;
+--echo # Sending:
+--send ALTER TABLE t1 MODIFY a INT UNSIGNED;
+
+connection default;
+--echo # Wait until ALTER TABLE is blocked on table 't1'.
+let $wait_condition=
+  SELECT COUNT(*) = 1 FROM information_schema.processlist
+  WHERE state = "Waiting for table metadata lock"
+  AND info LIKE "ALTER TABLE t1%";
+--source include/wait_condition.inc
+--error ER_LOCK_DEADLOCK
+INSERT DELAYED INTO t1 VALUES (3);
+COMMIT;
+
+connection con1;
+--echo # Reaping: ALTER TABLE t1 COMMENT 'test'
+--reap
+
+--echo # Test 3: Using RENAME TABLE
+
+connection default;
+START TRANSACTION;
+INSERT INTO t2 VALUES (1);
+
+connection con1;
+--echo # Sending:
+--send RENAME TABLE t1 to t5, t2 to t4
+
+connection default;
+--echo # Wait until RENAME TABLE is blocked on table 't1'.
+let $wait_condition=
+  SELECT COUNT(*) = 1 FROM information_schema.processlist
+  WHERE state = "Waiting for table metadata lock"
+  AND info = "RENAME TABLE t1 to t5, t2 to t4";
+--source include/wait_condition.inc
+--error ER_LOCK_DEADLOCK
+INSERT DELAYED INTO t1 VALUES (4);
+COMMIT;
+
+connection con1;
+--echo # Reaping: RENAME TABLE t1 to t5, t2 to t4
+--reap
+
+connection default;
+--echo # Reverting the renames
+RENAME TABLE t5 to t1, t4 to t2;
+
+--echo # Test 4: Two INSERT DELAYED on the same table
+
+START TRANSACTION;
+INSERT INTO t2 VALUES (1);
+
+connect (con2, localhost, root);
+--send LOCK TABLE t1 WRITE, t2 WRITE
+
+connection con1;
+--echo # Wait until LOCK TABLE is blocked on table 't2'.
+let $wait_condition=
+  SELECT COUNT(*) = 1 FROM information_schema.processlist
+  WHERE state = "Waiting for table metadata lock"
+  AND info = "LOCK TABLE t1 WRITE, t2 WRITE";
+--source include/wait_condition.inc
+--send INSERT DELAYED INTO t1 VALUES (5)
+
+connection default;
+--echo # Wait until INSERT DELAYED is blocked on table 't1'.
+let $wait_condition=
+  SELECT COUNT(*) = 1 FROM information_schema.processlist
+  WHERE state = "Waiting for table metadata lock"
+  AND info = "INSERT DELAYED INTO t1 VALUES (5)";
+--source include/wait_condition.inc
+--error ER_LOCK_DEADLOCK
+INSERT DELAYED INTO t1 VALUES (6);
+COMMIT;
+
+connection con2;
+--echo # Reaping: LOCK TABLE t1 WRITE, t2 WRITE
+--reap
+UNLOCK TABLES;
+
+connection con1;
+--echo # Reaping: INSERT DELAYED INTO t1 VALUES (5)
+--reap
+
+connection default;
+
+--echo # Test 5: LOCK TABLES + INSERT DELAYED in one connection.
+--echo # This test has triggered some asserts in metadata locking
+--echo # 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;
+--source include/wait_until_disconnected.inc
+connection con1;
+disconnect con1;
+--source include/wait_until_disconnected.inc
+
+connection default;
+DROP TABLE t1, t2, t3;
+--enable_ps_protocol
+
+
+--echo #
+--echo # Test for bug #56251 "Deadlock with INSERT DELAYED and MERGE tables".
+--echo #
+connect (con1,localhost,root,,);
+connection default;
+--disable_warnings
+drop table if exists t1, t2, tm;
+--enable_warnings
+create table t1(a int);
+create table t2(a int);
+create table tm(a int) engine=merge union=(t1, t2);
+begin;
+select * from t1;
+
+connection con1;
+--echo # Sending:
+--send alter table t1 comment 'test'
+
+connection default;
+--echo # Wait until ALTER TABLE blocks and starts waiting
+--echo # for connection 'default'. It should wait with a
+--echo # pending SNW lock on 't1'.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for table metadata lock" and
+        info = "alter table t1 comment 'test'";
+--source include/wait_condition.inc
+--echo # Attempt to perform delayed insert into 'tm' should not lead
+--echo # to a deadlock. Instead error ER_DELAYED_NOT_SUPPORTED should
+--echo # be emitted.
+--error ER_DELAYED_NOT_SUPPORTED
+insert delayed into tm values (1);
+--echo # Unblock ALTER TABLE.
+commit;
+
+connection con1;
+--echo # Reaping ALTER TABLE:
+--reap
+
+disconnect con1;
+--source include/wait_until_disconnected.inc
+connection default;
+drop tables tm, t1, t2;
+
+--echo #
+--echo # MDEV-9621 INSERT DELAYED fails on insert for tables with many columns
+--echo #
+
+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;
+
+--echo #
+--echo # INSERT DELAYED hangs if table was crashed
+--echo #
+
+create table t1 (a int, b int) engine=myisam;
+insert into t1 values (1,1);
+
+# Will come back with t1 crashed.
+--source include/crash_mysqld.inc
+
+call mtr.add_suppression(" marked as crashed and should be repaired");
+call mtr.add_suppression("Checking table");
+
+--replace_result '\\' '/'
+insert delayed into t1 values (2,2);
+insert delayed into t1 values (3,3);
+flush tables t1;
+select * from t1;
+drop table t1;
-- 
cgit v1.2.1