MySQL Bugs: #18416: Renaming a table looses all triggers

Bug #18416	Renaming a table looses all triggers
Submitted:	22 Mar 2006 10:48	Modified:	22 Mar 2006 12:18
Reporter:	Paul Furnival	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.18	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
When renaming a table using "RENAME TABLE `t1` TO `t2`, the resulting table `t2` looses any triggers.

MySQL Version information as follows:
mysql  Ver 14.12 Distrib 5.0.18, for pc-linux-gnu (i686) using  EditLine wrapper

Connection id:          41345
Current database:       paulf
Current user:           root@localhost
SSL:                    Not in use
Current pager:          less -inSFX
Using outfile:          ''
Using delimiter:        ;
Server version:         5.0.18-log
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /tmp/mysql.sock
Uptime:                 8 days 19 hours 17 min 48 sec

Threads: 1  Questions: 1481030  Slow queries: 30  Opens: 1134  Flush tables: 1  Open tables: 63  Queries per second avg: 1.947

How to repeat:

CREATE TABLE t1 (
  t1_key INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  field1 VARCHAR(24),
  field2 VARCHAR(24)
);

DELIMITER //
CREATE TRIGGER t1_bi BEFORE INSERT ON t1
FOR EACH ROW BEGIN
    SET NEW.field2 = NEW.field1;
END;
//
DELIMITER ;

INSERT INTO t1 (`field1`) VALUES ('first record');
INSERT INTO t1 (`field1`) VALUES ('second record');

/*
This gives the following, expected results
    select * from t1;
   
    +--------+---------------+---------------+
    | t1_key | field1        | field2        |
    +--------+---------------+---------------+
    |      1 | first record  | first record  |
    |      2 | second record | second record |
    +--------+---------------+---------------+
    2 rows in set (0.00 sec)

 show triggers;
+-----------------+--------+--------------+----------------------------------------------------------------------------------
| Trigger         | Event  | Table        | Statement
+-----------------+--------+--------------+----------------------------------------------------------------------------------
| t1_bi           | INSERT | t1           |  BEGIN
    SET NEW.field2 = NEW.field1;
END
+-----------------+--------+--------------+----------------------------------------------------------------------------------

*/

RENAME TABLE t1 TO t2;
INSERT INTO t2 (`field1`) VALUES ('third record');
INSERT INTO t2 (`field1`) VALUES ('fourth record');

/*

The trigger does not set the value for `field2`, I would expect `field2` to be the same as `field1`

select * from t2;
    +--------+---------------+---------------+
    | t1_key | field1        | field2        |
    +--------+---------------+---------------+
    |      1 | first record  | first record  |
    |      2 | second record | second record |
    |      3 | third record  | NULL          |
    |      4 | fourth record | NULL          |
    +--------+---------------+---------------+
    4 rows in set (0.00 sec)

And there are no triggers!!!!
show triggers;
    Empty set (0.01 sec)

*/

Suggested fix:
Renaming a table should maintain the triggers

Please, use a newer version, 5.0.19, already awaylable. Look:

mysql> CREATE TABLE t1 (
    ->   t1_key INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    ->   field1 VARCHAR(24),
    ->   field2 VARCHAR(24)
    -> );
Query OK, 0 rows affected (0.11 sec)

mysql> delimiter //
mysql> CREATE TRIGGER t1_bi BEFORE INSERT ON t1
    -> FOR EACH ROW BEGIN
    ->     SET NEW.field2 = NEW.field1;
    -> END;
    -> //
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter ;
mysql> INSERT INTO t1 (`field1`) VALUES ('first record');
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO t1 (`field1`) VALUES ('second record');
Query OK, 1 row affected (0.06 sec)

mysql> select * from t1;
+--------+---------------+---------------+
| t1_key | field1        | field2        |
+--------+---------------+---------------+
| 1      | first record  | first record  |
| 2      | second record | second record |
+--------+---------------+---------------+
2 rows in set (0.00 sec)

mysql> show triggers\G
*************************** 1. row ***************************
  Trigger: t1_bi
    Event: INSERT
    Table: t1
Statement: BEGIN
    SET NEW.field2 = NEW.field1;
END
   Timing: BEFORE
  Created: NULL
 sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
  Definer: root@localhost
1 row in set (0.44 sec)

mysql> RENAME TABLE t1 TO t2;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO t2 (`field1`) VALUES ('third record');
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO t2 (`field1`) VALUES ('fourth record');
Query OK, 1 row affected (0.05 sec)

mysql> select * from t2;
+--------+---------------+---------------+
| t1_key | field1        | field2        |
+--------+---------------+---------------+
| 1      | first record  | first record  |
| 2      | second record | second record |
| 3      | third record  | third record  |
| 4      | fourth record | fourth record |
+--------+---------------+---------------+
4 rows in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.19-nt |
+-----------+
1 row in set (0.00 sec)

Whooops !

Updating to 5.0.19 has fixed this.
Thanks.