MySQL Bugs: #17866: Problem with renaming table with triggers with fully qualified subject table

Bug #17866	Problem with renaming table with triggers with fully qualified subject table
Submitted:	2 Mar 2006 16:12	Modified:	5 Mar 2006 9:32
Reporter:	[ name withheld ]	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.19-BK, 5.0.18	OS:	Linux (Linux)
Assigned to:	Dmitry Lenev	CPU Architecture:	Any

Description:
A DROP TRIGGER fails if the table doesn't exists any more

How to repeat:
Create a trigger table1.trigger1
Drop table table1
Try to drop trigger1

You must recreate the table to remove the trigger

Suggested fix:
- Make it impossible to drop table with triggers
- Or make it possible to drop triggers when table doesn't exists

Thank you for a problem report. Please, specify the exact MySQL server version used (5.0.x, it's important). A simple test case will be useful also.

This is with mysql 5.0.18

When droping a table, trigger is removed, but when renaming the table, trigger is not removed ar changed.

CREATE TABLE test.users (user_id INT(11),user_lastvisit INT(11));
CREATE TABLE test.sessions (session_user_id INT(11),session_time INT(11));
DELIMITER |

CREATE TRIGGER test.update_lastvisit BEFORE DELETE ON test.sessions
 FOR EACH ROW BEGIN
 UPDATE test.users SET user_lastvisit=OLD.session_time WHERE user_id=OLD.session_user_id;
END;
|

RENAME TABLE test.sessions TO test.sessions_renamed;
DROP TRIGGER test.update_lastvisit;

Verified just as described in the last message on 5.0.19-BK (ChangeSet@1.1616.2144.129, 2006-03-01 18:38:19-06:00):

mysql> CREATE TABLE test.users (user_id INT(11),user_lastvisit INT(11));
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE test.sessions (session_user_id INT(11),session_time INT(11));
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter //
mysql> CREATE TRIGGER test.update_lastvisit1 BEFORE DELETE ON test.sessions
    ->  FOR EACH ROW BEGIN
    ->  UPDATE test.users SET user_lastvisit=OLD.session_time WHERE
    -> user_id=OLD.session_user_id;
    -> END;
    -> //
Query OK, 0 rows affected (0.01 sec)

mysql> RENAME TABLE test.sessions TO test.sessions_renamed//
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TRIGGER test.update_lastvisit//
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'sessi
ons
 FOR EACH ROW BEGIN
 UPDATE test.users SET user_lastvisit=OLD.session_t' at line 1
mysql> select version()//
+-----------+
| version() |
+-----------+
| 5.0.19    |
+-----------+
1 row in set (0.00 sec)

It is a bug.

http://lists.mysql.com/commits/3407

Hi, Arnaud, Valeriy!

What you observe are the effects of bug #13525 "Rename table does not keep info of triggers" which should be fixed in version 5.0.19. So your original problem is duplicate of bug #13525.

The problem reported by Valeriy caused by deficiency in fix for bug #13525, which caused
problems when one was renaming table with triggers which had in their defintions fully qualified subject table. I have updated synopsis to reflect this. Fix for this problem should appear in 5.0.19 as well.

Since this second problem does not present in released versions, ChangeLog note for it is not needed.

Thank you for your interest in MySQL!