Description:
I'm using mysql-6.1-fk-stage.
I start the server with mysqld --foreign-key-all-engines=1.
I create a parent table and a child table.
I create a procedure which randomly alters and upates.
I call the procedure.
Eventually it says a table doesn't exist. But it exists.
How to repeat:
Create tables and procedure thus:
DELIMITER //
DROP PROCEDURE IF EXISTS p//
DROP TABLE IF EXISTS t1,t2//
SET @@storage_engine=innodb//
CREATE TABLE t1 (s1 INT PRIMARY KEY)//
CREATE TABLE t2 (s1 INT REFERENCES t1(s1) ON UPDATE CASCADE ON DELETE CASCADE)//
CREATE PROCEDURE p ()
BEGIN
DECLARE v INT DEFAULT 0;
DECLARE comma CHAR(1);
WHILE v < 1000000 DO
SET comma = ' ';
SET @s = 'ALTER ';
IF RAND() > 0.5 THEN
SET @s = CONCAT(@s,' IGNORE ');
END IF;
IF RAND() > 0.5 THEN
SET @s = CONCAT(@s,' TABLE t2 ');
ELSE
SET @s = CONCAT(@s,' TABLE t1 ');
END IF;
IF RAND() > 0.8 THEN
SET @s = CONCAT(@s,comma,' ADD COLUMN s2 INT ');
IF RAND() > 0.5 THEN
SET @s = CONCAT(@s,' FIRST ');
END IF;
SET comma = ',';
END IF;
IF RAND() > 0.8 THEN
SET @s = CONCAT(@s,comma,' ADD INDEX i (s1) ');
SET comma = ',';
END IF;
IF RAND() > 0.8 THEN
SET @s = CONCAT(@s,comma,' ADD CONSTRAINT c FOREIGN KEY (s2) REFERENCES t1(s1) ');
SET comma = ',';
END IF;
IF RAND() > 0.8 THEN
SET @s = CONCAT(@s,comma,' ALTER COLUMN s1 DROP DEFAULT ');
SET comma = ',';
END IF;
IF RAND() > 0.8 THEN
SET @s = CONCAT(@s,comma,' DROP COLUMN s2 ');
SET comma = ',';
END IF;
IF RAND() > 0.8 THEN
SET @s = CONCAT(@s,comma,' DROP COLUMN s2 ');
SET comma = ',';
END IF;
IF RAND() > 0.8 THEN
SET @s = CONCAT(@s,comma,' DROP PRIMARY KEY ');
SET comma = ',';
END IF;
IF RAND() > 0.8 THEN
SET @s = CONCAT(@s,comma,' DROP INDEX i ');
SET comma = ',';
END IF;
IF RAND() > 0.8 THEN
SET @s = CONCAT(@s,comma,' DISABLE KEYS ');
SET comma = ',';
END IF;
IF RAND() > 0.8 THEN
SET @s = CONCAT(@s,comma,' ENABLE KEYS ');
SET comma = ',';
END IF;
IF RAND() > 0.8 THEN
SET @s = CONCAT(@s,comma,' ORDER BY s1 ');
SET comma = ',';
END IF;
IF RAND() > 0.8 THEN
SET @s = CONCAT(@s,comma,' CONVERT TO CHARACTER SET latin1 ');
SET comma = ',';
END IF;
IF RAND() > 0.8 THEN
SET @s = CONCAT(@s,comma,' CHARACTER SET latin1 ');
SET comma = ',';
END IF;
IF RAND() > 0.8 THEN
SET @s = CONCAT(@s,comma,' DISCARD TABLESPACE ');
SET comma = ',';
END IF;
IF RAND() > 0.8 THEN
SET @s = CONCAT(@s,comma,' MODIFY COLUMN s1 INT ');
SET comma = ',';
END IF;
IF RAND() > 0.8 THEN
SET @s = CONCAT(@s,comma,' CHANGE COLUMN s1 s1 INT ');
SET comma = ',';
END IF;
SELECT v, @s;
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
PREPARE stmt1 FROM @s;
EXECUTE stmt1;
END;
IF RAND() > 0.9 THEN
BEGIN
DECLARE CONTINUE HANDLER FOR 1213 SELECT 'deadlock';
DECLARE CONTINUE HANDLER FOR 1062 SELECT 'duplicate';
SET @w = RAND()*100000;
INSERT INTO t1 (s1) VALUES (@w);
END;
END IF;
IF RAND() > 0.9 THEN
BEGIN
DECLARE CONTINUE HANDLER FOR 1213 SELECT 'deadlock';
DELETE FROM t2;
END;
END IF;
IF RAND() > 0.9 THEN
BEGIN
DECLARE CONTINUE HANDLER FOR 1213 SELECT 'deadlock';
INSERT INTO t2 (s1) SELECT s1 FROM t1;
END;
END IF;
IF RAND() > 0.9 THEN
BEGIN
DECLARE CONTINUE HANDLER FOR 1213 SELECT 'deadlock';
UPDATE t1 SET s1 = s1 - 1;
END;
END IF;
SET v = v + 1;
END WHILE;
END//
DELIMITER ;
CALL p();
After a while, usually less than 300 iterations, you'll see an error
ERROR 1146 (42S02): Table 'c.t1' doesn't exist
or
ERROR 1146 (42S02): Table 'c.t2' doesn't exist
(The error is for UPDATE or DELETE or INSERT, not for ALTER.)