MySQL Bugs: #18153: REPAIR/OPTIMIZE/ALTER on transactional tables corrupt triggers

Bug #18153	REPAIR/OPTIMIZE/ALTER on transactional tables corrupt triggers
Submitted:	11 Mar 2006 13:23	Modified:	31 Mar 2006 7:28
Reporter:	Roland Volkmann	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.19-nt/5.0.20BK/5.1.8BK	OS:	Windows (WinXP, Win2003-Server/Suse Linux)
Assigned to:	Dmitry Lenev	CPU Architecture:	Any

Description:
On InnoDB-Database with tables having triggers, the triggers are corrupted on running "mysqlcheck.exe --optimize":
the *.TRG files are removed, only *.TRN are kept.

How to repeat:
mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed
mysql> create table ttt (info varchar(10));
Query OK, 0 rows affected (0.02 sec)

mysql> create trigger fire before insert on ttt for each row set new.info := upper(new.info);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into ttt (info) values ('hello');
Query OK, 1 row affected (0.00 sec)

mysql> select * from ttt;
+-------+
| info  |
+-------+
| HELLO |
+-------+
1 row in set (0.00 sec)

mysql>

shell>mysqlcheck.exe --optimize --databases --user=root --password=secret test

shell>

mysql> insert into ttt (info) values ('world');
Query OK, 1 row affected (0.00 sec)

mysql> select * from ttt;
+-------+
| info  |
+-------+
| HELLO |
| world |
+-------+
2 rows in set (0.00 sec)

mysql>

Remark: use of mysqlcheck.exe from version 5.0.18 in the environment of version 5.0.19 doesn't work the same way, within version 5.0.18 everything works well.

In addition to the described optimize-command, triggers get also lost/corrupted under normal work. So I had to go back now to version 5.0.18-nt on production server.

Thank you for the bug report. I was able to repeat also on Linux BK 5.0
and 5.1:

c:\mysql\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8 to server version: 5.0.19-nt

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

mysql> create database test;
Query OK, 1 row affected (0.01 sec)

mysql> use test;
Database changed
mysql> create table ttt (info varchar(10));
Query OK, 0 rows affected (0.11 sec)

mysql> create trigger fire before insert on ttt for each row set new.info := upper(new.info);
Query OK, 0 rows affected (0.06 sec)

mysql> insert into ttt (info) values ('hello');
Query OK, 1 row affected (0.03 sec)

mysql> select * from ttt;
+-------+
| info  |
+-------+
| HELLO |
+-------+
1 row in set (0.00 sec)

mysql> exit
Bye

c:\mysql\bin>mysqlcheck.exe --optimize --databases --user=root test
test.ttt                                           OK

c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10 to server version: 5.0.19-nt

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

mysql> insert into ttt (info) values ('world');
Query OK, 1 row affected (0.03 sec)

mysql> select * from ttt;
+-------+
| info  |
+-------+
| HELLO |
| world |
+-------+
2 rows in set (0.00 sec)

miguel@hegel:~/dbs/5.0> libexec/mysqld --default-storage-engine=InnoDB
060312 22:28:10  InnoDB: Started; log sequence number 0 63238
060312 22:28:10 [Note] libexec/mysqld: ready for connections.
Version: '5.0.20-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution

miguel@hegel:~/dbs/5.0> bin/mysqladmin -uroot drop test
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the 'test' database [y/N] y
Database "test" dropped
miguel@hegel:~/dbs/5.0> bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.20-debug

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

mysql> create database test;
Query OK, 1 row affected (0.04 sec)

mysql> use test;
Database changed
mysql> create table ttt (info varchar(10));
Query OK, 0 rows affected (0.16 sec)

mysql> create trigger fire before insert on ttt for each row set new.info :=
    -> upper(new.info);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into ttt (info) values ('hello');
Query OK, 1 row affected (0.01 sec)

mysql> select * from ttt;
+-------+
| info  |
+-------+
| HELLO |
+-------+
1 row in set (0.00 sec)

mysql> exit
Bye
miguel@hegel:~/dbs/5.0> bin/mysqlcheck --optimize --databases --user=root test
test.ttt                                           OK
miguel@hegel:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.0.20-debug

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

mysql> insert into ttt (info) values ('world');
Query OK, 1 row affected (0.01 sec)

mysql> select * from ttt;
+-------+
| info  |
+-------+
| HELLO |
| world |
+-------+
2 rows in set (0.01 sec)

Since bug #18333 has the same cause it was marked duplicate of this one.

It suggest much simplier test case for the same issue:

DROP TABLE IF EXISTS t1;
CREATE table t1(i INT PRIMARY KEY) ENGINE=innodb;
CREATE TRIGGER tr1 BEFORE INSERT ON t1
  FOR EACH ROW BEGIN
  END;
ALTER TABLE t1 ADD COLUMN j INT;
SHOW TRIGGERS;

http://lists.mysql.com/commits/4033

Bug #18442 "ALTER TABLE with trigger to same name will delete .TRG file" was marked as duplicate of this one.

Note that more precise synopsis of this bug sounds like:
Applying ALTER/OPTIMIZE/REPAIR TABLE statements to transactional table or to  table of any type on Windows causes disappearance of its triggers.

Reviewed the patch and applied it on Windows. Works fine. Ok to push.

Although some changes are requested in the review, it's ok to push after fixing them.

(Reviewed by email and irc)

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/4139

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/4144

Fix pushed to trees marked as 5.0.20 and 5.1.8

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:

Documented bugfix in 5.0.20 and 5.1.8 changelogs. Closed.

Bug#18816 has been marked as a duplicate of this bug.

Bug #28117 was marked as a duplicate of this one.