| Bug #13525 | Rename table does not keep info of triggers | ||
|---|---|---|---|
| Submitted: | 27 Sep 2005 15:08 | Modified: | 27 Feb 2006 21:37 |
| Reporter: | Ritesh Nadhani | ||
| Status: | Closed | ||
| Category: | Server | Severity: | S2 (Serious) |
| Version: | 5.0.12/BK source 5.0 | OS: | Microsoft Windows (Windows/Linux) |
| Assigned to: | Dmitri Lenev | Target Version: | |
[27 Sep 2005 16:11]
Miguel Solorzano
miguel@hegel:~/dbs/5.0> bin/mysql -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 5.0.14-rc-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE test1(a1 INT);
Query OK, 0 rows affected (0.05 sec)
mysql> CREATE TABLE test2(a2 INT);
Query OK, 0 rows affected (0.04 sec)
mysql> CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (0.05 sec)
mysql> CREATE TABLE test4(
-> a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> b4 INT DEFAULT 0
-> );
Query OK, 0 rows affected (0.05 sec)
mysql>
mysql> DELIMITER |
mysql>
mysql> CREATE TRIGGER testref BEFORE INSERT ON test1
-> FOR EACH ROW BEGIN
-> INSERT INTO test2 SET a2 = NEW.a1;
-> DELETE FROM test3 WHERE a3 = NEW.a1;
-> UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
-> END
-> |
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> DELIMITER ;
mysql> select * from information_schema.triggers\G
*************************** 1. row ***************************
TRIGGER_CATALOG: NULL
TRIGGER_SCHEMA: test
TRIGGER_NAME: testref
EVENT_MANIPULATION: INSERT
EVENT_OBJECT_CATALOG: NULL
EVENT_OBJECT_SCHEMA: test
EVENT_OBJECT_TABLE: test1
ACTION_ORDER: 0
ACTION_CONDITION: NULL
ACTION_STATEMENT: BEGIN
INSERT INTO test2 SET a2 = NEW.a1;
DELETE FROM test3 WHERE a3 = NEW.a1;
UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
END
ACTION_ORIENTATION: ROW
ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
ACTION_REFERENCE_OLD_ROW: OLD
ACTION_REFERENCE_NEW_ROW: NEW
CREATED: NULL
SQL_MODE:
1 row in set (0.11 sec)
mysql> rename table test1 to test1a;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from information_schema.triggers\G
Empty set (0.04 sec)
mysql> rename table test1a to test1;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from information_schema.triggers\G
*************************** 1. row ***************************
TRIGGER_CATALOG: NULL
TRIGGER_SCHEMA: test
TRIGGER_NAME: testref
EVENT_MANIPULATION: INSERT
EVENT_OBJECT_CATALOG: NULL
EVENT_OBJECT_SCHEMA: test
EVENT_OBJECT_TABLE: test1
ACTION_ORDER: 0
ACTION_CONDITION: NULL
ACTION_STATEMENT: BEGIN
INSERT INTO test2 SET a2 = NEW.a1;
DELETE FROM test3 WHERE a3 = NEW.a1;
UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
END
ACTION_ORIENTATION: ROW
ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
ACTION_REFERENCE_OLD_ROW: OLD
ACTION_REFERENCE_NEW_ROW: NEW
CREATED: NULL
SQL_MODE:
1 row in set (0.05 sec)
mysql>
[13 Jan 2006 16:09]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/1036
[11 Feb 2006 11:39]
Konstantin Osipov
Approved by email with a few comments.
[24 Feb 2006 21:51]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/3123
[26 Feb 2006 18:26]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/3156
[27 Feb 2006 18:33]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/3205
[27 Feb 2006 21:34]
Dmitri Lenev
Fixed in versions 5.0.19 and 5.1.8. Now we transfer triggers associated with table when we rename it (but only if we are not changing database to which table belongs, in the latter case we will emit error).
[27 Feb 2006 21:37]
Mike Hillyer
Documented in 5.0.19 and 5.1.8 changelogs:
<listitem>
<para>
The <literal>RENAME TABLE</literal> statement did not move
triggers to the new table. (Bug #13525)
</para>
</listitem>
[3 Mar 2006 0:24]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/3407
[4 Mar 2006 14:55]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/3466
[22 Mar 2006 18:08]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/4033
[24 Mar 2006 12:39]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/4112
[24 Mar 2006 12:55]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/4114
[12 Apr 2006 15:07]
Miguel Solorzano
Bug: http://bugs.mysql.com/bug.php?id=19048 was marked as duplicateof this one.
Description: If you do rename table, it does not keep its TRIGGER info for the table. I know I am reporting it for 5.0.12 but I dont think it has been fixed in 5.0.13 or is it? When you rename it back to original table name, the triggers are again present! How to repeat: mysql> use example; Database changed mysql> select version(); +----------------+ | version() | +----------------+ | 5.0.12-beta-nt | +----------------+ 1 row in set (0.00 sec) mysql> show triggers like 'student'; +---------+--------+---------+-------------------------------------------------- ------------------------------------+--------+---------+----------+ | Trigger | Event | Table | Statement | Timing | Created | sql_mode | +---------+--------+---------+-------------------------------------------------- ------------------------------------+--------+---------+----------+ | t1 | INSERT | student | BEGIN insert into stu set usn=new.usn and name=new.name and grade=new.grade; END | AFTER | NULL | | | t2 | UPDATE | student | BEGIN update stu set name=new.name and grade=new.grade where usn=old.usn; END | AFTER | NULL | | | t3 | DELETE | student | BEGIN delete from stu where usn=old.usn; END | AFTER | NULL | | +---------+--------+---------+-------------------------------------------------- ------------------------------------+--------+---------+----------+ 3 rows in set (0.00 sec) mysql> select trigger_name from information_schema.triggers where trigger_schema ='example' and event_object_table='student'; +--------------+ | trigger_name | +--------------+ | t1 | | t2 | | t3 | +--------------+ 3 rows in set (0.02 sec) mysql> rename table student to teacher; Query OK, 0 rows affected (0.02 sec) mysql> show triggers like 'teacher'; Empty set (0.00 sec) mysql> select trigger_name from information_schema.triggers where trigger_schema ='example' and event_object_table='teacher'; Empty set (0.00 sec) mysql> rename table teacher to student; Query OK, 0 rows affected (0.03 sec) mysql> show triggers like 'student'; +---------+--------+---------+-------------------------------------------------- ------------------------------------+--------+---------+----------+ | Trigger | Event | Table | Statement | Timing | Created | sql_mode | +---------+--------+---------+-------------------------------------------------- ------------------------------------+--------+---------+----------+ | t1 | INSERT | student | BEGIN insert into stu set usn=new.usn and name=new.name and grade=new.grade; END | AFTER | NULL | | | t2 | UPDATE | student | BEGIN update stu set name=new.name and grade=new.grade where usn=old.usn; END | AFTER | NULL | | | t3 | DELETE | student | BEGIN delete from stu where usn=old.usn; END | AFTER | NULL | | +---------+--------+---------+-------------------------------------------------- ------------------------------------+--------+---------+----------+ 3 rows in set (0.00 sec) mysql> select trigger_name from information_schema.triggers where trigger_schema ='example' and event_object_table='student'; +--------------+ | trigger_name | +--------------+ | t1 | | t2 | | t3 | +--------------+ 3 rows in set (0.01 sec)