Bug #19048 Renaming a table looses TRIGGERs

• Submitted: 12 Apr 2006 12:27
• Modified: 12 Apr 2006 13:09
• Reporter: Bertin Colpron
• Status: Duplicate
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.15
• OS: Windows (WinXP)
• CPU Architecture: Any

[12 Apr 2006 12:27] Bertin Colpron

Description:
If I rename a table with one or more TRIGGERs associated, then

- The triggers does not fire for the renamed table.
- The associated triggers are no longer listed by SHOW TRIGGERS
- The triggers cannot be dropped (associated table does not exist).
- Cannot recreate trigger with the same names (trigger already exists).

Note: If I rename the table back to its previous name, the triggers show up normally.

How to repeat:
CREATE TABLE t1 (c VARCHAR(100));

CREATE TRIGGER my_trigger BEFORE INSERT ON t1
FOR EACH ROW SET @a = @a + 1;

ALTER TABLE t1 RENAME TO t2;

SHOW TRIGGERS;       // Empty set

CREATE TRIGGER my_trigger BEFORE INSERT ON t2
FOR EACH ROW SET @a = @a + 1;
         // Fails. Trigger 'my_trigger' already exists

DROP TRIGGER my_trigger;  // Fails. Table t1 doesn't exist

[12 Apr 2006 12:56] MySQL Verification Team

Thank you for the bug report. Duplicate of bug:
http://bugs.mysql.com/bug.php?id=13525

[12 Apr 2006 13:09] Bertin Colpron

Sorry about that. I just noticed that the bug search page looks only for 'Active' bugs by default...