MySQL Bugs: #13525: Rename table does not keep info of triggers

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:	Windows (Windows/Linux)
Assigned to:	Dmitry Lenev	CPU Architecture:	Any

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)

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>

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

Approved by email with a few comments.

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

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

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

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).

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>

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

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

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

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

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

Bug: http://bugs.mysql.com/bug.php?id=19048 was marked as
duplicateof this one.