MySQL Bugs: #19964: Rename a table that contains trigger, impossible afterwards to delete this one

Bug #19964	Rename a table that contains trigger, impossible afterwards to delete this one
Submitted:	20 May 2006 8:40	Modified:	20 May 2006 16:37
Reporter:	Jrme Despatis (Candidate Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.20	OS:	Linux (Linux 2.6.15 Debian)
Assigned to:		CPU Architecture:	Any

Description:
Hi

I have a blocking problem

I create an Inno DB table, i add a trigger on it

I rename this table.

And after, i can't delete the previous trigger, nor even add a new one

See below

How to repeat:
CREATE TABLE `tabl` (
  `id` tinyint(3) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TRIGGER AI_tabl_utime AFTER INSERT ON tabl FOR EACH ROW BEGIN REPLACE INTO `update_times` VALUES ('tabl', NOW()); END;

RENAME TABLE tabl TO table2;

If i try to delete a trigger (so mysql says it doesn't exist)
DROP TRIGGER AI_table2_utime;
ERROR 1360 (HY000): Trigger does not exist

but if i create this trigger, mysql says now it exists !
mysql> delimiter //
mysql> CREATE TRIGGER AI_table2_utime AFTER INSERT ON table2 FOR EACH ROW BEGIN REPLACE INTO `update_times` VALUES ('table2', NOW()); END; //
ERROR 1359 (HY000): Trigger already exists

Hi

And to have a working table again, i have to delete it and create it again

Hope this helps

On Windows/MySQL 5.0.21 this works fine:
(note that is SQLyog - not cmd-ln-clinet DELIMITER SYNTAX)

CREATE TABLE `tabl` (
  `id` tinyint(3) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DELIMITER !!;

CREATE TRIGGER AI_tabl_utime AFTER INSERT ON tabl FOR EACH ROW 
BEGIN 
REPLACE
INTO `update_times` VALUES ('tabl', NOW()); 
END!!
DELIMITER ;!!

RENAME TABLE tabl TO table2;

DROP TRIGGER AI_tabl_utime;

The trigger name does not change because you change the name of the table!

Sorry .. overlooked your last point, but also not reproducable here:

CREATE TABLE `tabl` (
  `id` tinyint(3) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DELIMITER !!;
CREATE TRIGGER AI_tabl_utime AFTER INSERT ON tabl FOR EACH ROW 
BEGIN 
REPLACE
INTO `update_times` VALUES ('tabl', NOW()); 
END!!
DELIMITER ;!!

RENAME TABLE tabl TO table2;
DROP TRIGGER AI_tabl_utime;

delimiter !!;
CREATE TRIGGER AI_table2_utime AFTER INSERT ON table2 FOR EACH ROW 
BEGIN
REPLACE INTO `update_times` VALUES ('table2', NOW()); 
END; !!
delimiter ;!!

/* returns
(0 row(s) affected)
(0 ms taken)

(0 row(s) affected)
(0 ms taken)

(0 row(s) affected)
(0 ms taken)

(0 row(s) affected)
(0 ms taken)

(0 row(s) affected)
(0 ms taken)

(0 row(s) affected)
(0 ms taken)

.. and trigger 'AI_table2_utime' exists and not other triggers!
*/

@Jerome

I also think you should be a little more careful with the use of the S1/critical category when reporting bugs!

and I say that as a non-MySQL person!

I've changed the state in serious

But for me, when i do all the instructions i've done, i can't create a trigger on the table i have renamed...

Peter, thanks your your reporting

In your last try, i can see:
DROP TRIGGER AI_tabl_utime;

if you don't do this command, maybe you'll have the problem i have

my problem is here in fact:

(i only do this command to confirm that AI_table2_utime doesn't exist)
DROP TRIGGER AI_table2_utime;
ERROR 1360 (HY000): Trigger does not exist (this error is correct, the trigger doesn't exist)

and if i create this trigger, mysql says now it exists !
mysql> delimiter //
mysql> CREATE TRIGGER AI_table2_utime AFTER INSERT ON table2 FOR EACH ROW BEGIN
REPLACE INTO `update_times` VALUES ('table2', NOW()); END; //
ERROR 1359 (HY000): Trigger already exists

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

- the trigger name doesn't change when the table is renamed, why should it?

- there can be only one trigger per table right now, this is why you can't
  add a 2nd trigger under the new name