Bug #35779 'Trigger already exists' error + renamed table

• Submitted: 2 Apr 2008 22:55
• Modified: 4 Apr 2008 7:43
• Reporter: Evgeniy L
• Status: Not a Bug
• Category: MySQL Server: Stored Routines
• Severity: S3 (Non-critical)
• Version: 5.0.51a-community-nt
• OS: Windows (WinXP SP2)
• CPU Architecture: Any
• Tags: rename table, trigger

[2 Apr 2008 22:55] Evgeniy L

Description:
'Trigger already exists' error while creating trigger on table with same name which has had just renamed table.

How to repeat:
use test
drop table if exists t;
drop table if exists t1;
create table t (i int);
create trigger t_bi before insert on t for each row set new.i = 1;
rename table t to t1;
create table t (i int);
create trigger t_bi before insert on t for each row set new.i = 1;

Suggested fix:
related bugs
http://bugs.mysql.com/bug.php?id=18333
http://bugs.mysql.com/bug.php?id=18153

[3 Apr 2008 0:00] MySQL Verification Team

Thank you for the bug report. Looking our Manual at:

http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html
.....
"This statement creates a new trigger. A trigger is a named database object that is associated with a table, and that activates when a particular event occurs for the table. The trigger becomes associated with the table named tbl_name, which must refer to a permanent table."

Notice above it is a 'database object', so when you rename the table it is still
a database object hence the error message you got. You can also notice the
drop trigger syntax: http://dev.mysql.com/doc/refman/5.0/en/drop-trigger.html

"DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name"

Doesn't includes the table_name. So when you see the mentioned error message
you need to drop the trigger to be able to create again:

c:\dbs>5.0\bin\mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.60-nt Source distribution

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

mysql> use test
Database changed
mysql> drop table if exists t;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> drop table if exists t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table t (i int);
Query OK, 0 rows affected (0.06 sec)

mysql> create trigger t_bi before insert on t for each row set new.i = 1;
Query OK, 0 rows affected (0.03 sec)

mysql> rename table t to t1;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t (i int);
Query OK, 0 rows affected (0.05 sec)

mysql> create trigger t_bi before insert on t for each row set new.i = 1;
ERROR 1359 (HY000): Trigger already exists
mysql> drop trigger t_bi;
Query OK, 0 rows affected (0.00 sec)

mysql> create trigger t_bi before insert on t for each row set new.i = 1;
Query OK, 0 rows affected (0.02 sec)

mysql>