Bug #45955 Foreign keys: error message saying table doesn't exist

• Submitted: 5 Jul 2009 23:58
• Modified: 20 Dec 2013 6:51
• Reporter: Trudy Pelzer
• Status: Won't fix
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 6.1.0-alpha-debug
• OS: Linux (SUSE 10.2)
• CPU Architecture: Any

[5 Jul 2009 23:58] Trudy Pelzer

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.)

[9 Jul 2009 0:54] MySQL Verification Team

Thank you for the bug report.

-----------------------------------------------------------------------------------+
|  260 | ALTER  TABLE t1  ADD CONSTRAINT c FOREIGN KEY (s2) REFERENCES t1(s1) , ALTER COLUMN s1 DROP DEFAULT , DROP PRIMARY KEY , DISABLE KEYS , CONVERT TO CHARACTER SET latin1 , DISCARD TABLESPACE  | 
+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (10.89 sec)

ERROR 1146 (42S02): Table 'test.t1' doesn't exist
mysql>

[20 Dec 2013 6:51] Erlend Dahl

6.x project was abandoned years ago.