MySQL Bugs: #15361: alter table + stored procedure + trigger = error 1146 (table doesn't exist)

Bug #15361	alter table + stored procedure + trigger = error 1146 (table doesn't exist)
Submitted:	30 Nov 2005 18:18	Modified:	30 Nov 2005 19:03
Reporter:	Scott Noyes (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.15-nt/5.0.16	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
Two tables are created.  A trigger is added to one which updates the other.  A stored procedure is created which updates the first.  After altering the table, the stored procedure fails with an error 1146.  The error message is not always exactly the same.

How to repeat:
CREATE TABLE `work` (`num` int NOT NULL auto_increment primary key);
ALTER TABLE work AUTO_INCREMENT = 2;
CREATE TABLE `primes` (`prime` int);

DELIMITER //

CREATE TRIGGER work_ai AFTER INSERT ON work FOR EACH ROW
BEGIN
  INSERT INTO primes VALUES (NEW.num);
  DELETE p1 FROM primes p1 JOIN primes p2 ON (SQRT(p1.prime) > p2.prime) WHERE p1.prime = NEW.num AND p1.prime % p2.prime = 0;
END//

CREATE PROCEDURE makePrime(IN count INT)
BEGIN
  DECLARE i INT DEFAULT 0;
  WHILE i < count DO
    INSERT INTO work VALUES (NULL);
    SET i = i + 1;
  END WHILE;
END//

DELIMITER ;

CALL makePrime(100);
-- note that this call works

ALTER TABLE work AUTO_INCREMENT = 2;

CALL makePrime(100);
-- Result here is one of:
-- ERROR 1146 (42S02): Table '.' doesn't exist
-- ERROR 1146 (42S02): Table 'test.work_ai.work_ai' doesn't exist

This is similar to 
http://bugs.mysql.com/bug.php?id=10664
http://bugs.mysql.com/bug.php?id=10285

with two differences:
- the table has not been truncated, just altered the auto_increment start
- the alter table occures outside the stored procedure

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

C:\mysql5016>bin\mysql --defaults-file=c:\mysql5016\s5016.ini -uroot -p --prompt="win5016>"
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.16-nt-max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

win5016>use test
Database changed
win5016>CREATE TABLE `work` (`num` int NOT NULL auto_increment primary key);
Query OK, 0 rows affected (0.08 sec)

win5016>ALTER TABLE work AUTO_INCREMENT = 2;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

win5016>CREATE TABLE `primes` (`prime` int);
Query OK, 0 rows affected (0.03 sec)

win5016>
win5016>DELIMITER //
win5016>CREATE TRIGGER work_ai AFTER INSERT ON work FOR EACH ROW
    -> BEGIN
    ->   INSERT INTO primes VALUES (NEW.num);
    ->   DELETE p1 FROM primes p1 JOIN primes p2 ON (SQRT(p1.prime) > p2.prime) WHERE
    -> p1.prime = NEW.num AND p1.prime % p2.prime = 0;
    -> END//
Query OK, 0 rows affected (0.00 sec)

win5016>CREATE PROCEDURE makePrime(IN count INT)
    -> BEGIN
    ->   DECLARE i INT DEFAULT 0;
    ->   WHILE i < count DO
    ->     INSERT INTO work VALUES (NULL);
    ->     SET i = i + 1;
    ->   END WHILE;
    -> END//
Query OK, 0 rows affected (0.00 sec)

win5016>DELIMITER ;
win5016>CALL makePrime(100);
Query OK, 1 row affected (0.05 sec)

win5016>ALTER TABLE work AUTO_INCREMENT = 2;
Query OK, 100 rows affected (0.08 sec)
Records: 100  Duplicates: 0  Warnings: 0

win5016>CALL makePrime(100);
ERROR 1146 (42S02): Table '.' doesn't exist
win5016>
C:\mysql5017>bin\mysql --defaults-file=c:\mysql5017\s5017.ini -uroot -p --prompt="win5017>"
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.17-nt-max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

win5017>use test
Database changed
win5017>CREATE TABLE `work` (`num` int NOT NULL auto_increment primary key);
Query OK, 0 rows affected (0.05 sec)

win5017>ALTER TABLE work AUTO_INCREMENT = 2;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

win5017>CREATE TABLE `primes` (`prime` int);
Query OK, 0 rows affected (0.06 sec)

win5017>DELIMITER //
win5017>CREATE TRIGGER work_ai AFTER INSERT ON work FOR EACH ROW
    -> BEGIN
    ->   INSERT INTO primes VALUES (NEW.num);
    ->   DELETE p1 FROM primes p1 JOIN primes p2 ON (SQRT(p1.prime) > p2.prime) WHERE
    -> p1.prime = NEW.num AND p1.prime % p2.prime = 0;
    -> END//
Query OK, 0 rows affected (0.00 sec)

win5017>CREATE PROCEDURE makePrime(IN count INT)
    -> BEGIN
    ->   DECLARE i INT DEFAULT 0;
    ->   WHILE i < count DO
    ->     INSERT INTO work VALUES (NULL);
    ->     SET i = i + 1;
    ->   END WHILE;
    -> END//
Query OK, 0 rows affected (0.02 sec)

win5017>
win5017>DELIMITER ;
win5017>
win5017>CALL makePrime(100);
Query OK, 1 row affected (0.06 sec)

win5017>ALTER TABLE work AUTO_INCREMENT = 2;
Query OK, 100 rows affected (0.05 sec)
Records: 100  Duplicates: 0  Warnings: 0

win5017>CALL makePrime(100);
Query OK, 1 row affected (0.05 sec)

win5017>