Bug #13525 Rename table does not keep info of triggers
Submitted: 27 Sep 2005 13:08 Modified: 27 Feb 2006 20:37
Reporter: Ritesh Nadhani Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.12/BK source 5.0 OS:Microsoft Windows (Windows/Linux)
Assigned to: Dmitry Lenev CPU Architecture:Any

[27 Sep 2005 13:08] Ritesh Nadhani
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)
[27 Sep 2005 14: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 15: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 10:39] Konstantin Osipov
Approved by email with a few comments.
[24 Feb 2006 20: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 17: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 17: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 20:34] Dmitry 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 20: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>
[2 Mar 2006 23: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 13: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 17: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 11: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 11: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 13:07] Miguel Solorzano
Bug: http://bugs.mysql.com/bug.php?id=19048 was marked as
duplicateof this one.