Bug #19686 Alter table or create/drop index destroys triggers incompletely

• Submitted: 10 May 2006 14:20
• Modified: 10 May 2006 14:47
• Reporter: John Miller
• Status: Closed
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0.19-log
• OS: Linux (linux)
• CPU Architecture: Any

[10 May 2006 14:20] John Miller

Description:
Perfoming any ALTER TABLE, CREATE INDEX, or DROP INDEX statement on a table which has triggers makes the trigger disappear.  However any attempt to recreate that trigger causes a 1359 error - Trigger already exists because the TRN file was not removed.

I would not expect those statements to have any effect on triggers, however if it does it should cleanly remove them.

How to repeat:
mysql> create table t(i int not null primary key, j int null);
Query OK, 0 rows affected (0.55 sec)
mysql> delimiter |
mysql> create trigger t_test_ins before insert on t
    -> for each row begin
    ->   set NEW.j = NEW.i;
    -> end|
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> show triggers;
+------------+--------+-------+--------------------------------+--------+---------+----------+---------+
| Trigger    | Event  | Table | Statement                      | Timing | Created | sql_mode | Definer |
+------------+--------+-------+--------------------------------+--------+---------+----------+---------+
| t_test_ins | INSERT | t     | begin
  set NEW.j = NEW.i;
end | BEFORE | NULL    |          | john@%  |
+------------+--------+-------+--------------------------------+--------+---------+----------+---------+
1 row in set (0.16 sec)
mysql> create index IX_t_j on t(j);
Query OK, 0 rows affected (3.30 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show triggers;
Empty set (0.13 sec)
mysql> delimiter |
mysql> create trigger t_test_ins before insert on t
    -> for each row begin
    ->   set NEW.j = NEW.i;
    -> end|
ERROR 1359 (HY000): Trigger already exists

"ls" in the test directory returns:
t.frm
t_test_ins.TRN

[10 May 2006 14:47] MySQL Verification Team

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

miguel@hegel:~/dbs/5.0> bin/mysqladmin -uroot create db81
miguel@hegel:~/dbs/5.0> bin/mysql -uroot db81
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.22-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table t(i int not null primary key, j int null);
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter |
mysql> create trigger t_test_ins before insert on t
    -> for each row begin
    -> set NEW.j = NEW.i;
    -> end|
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> show triggers\G
*************************** 1. row ***************************
  Trigger: t_test_ins
    Event: INSERT
    Table: t
Statement: begin
set NEW.j = NEW.i;
end
   Timing: BEFORE
  Created: NULL
 sql_mode: 
  Definer: root@localhost
1 row in set (0.01 sec)

mysql> create index IX_t_j on t(j);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show triggers\G
*************************** 1. row ***************************
  Trigger: t_test_ins
    Event: INSERT
    Table: t
Statement: begin
set NEW.j = NEW.i;
end
   Timing: BEFORE
  Created: NULL
 sql_mode: 
  Definer: root@localhost
1 row in set (0.01 sec)

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `i` int(11) NOT NULL,
  `j` int(11) default NULL,
  PRIMARY KEY  (`i`),
  KEY `IX_t_j` (`j`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql>