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:Microsoft Windows (WinXP, Win2003-Server/Suse Linux)
Assigned to: Dmitry Lenev

[11 Mar 2006 13:23] Roland Volkmann
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.
[12 Mar 2006 0:40] Roland Volkmann
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.
[13 Mar 2006 1:32] Miguel Solorzano
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)
[20 Mar 2006 9:49] Dmitry Lenev
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;
[22 Mar 2006 17:13] Dmitry Lenev
http://lists.mysql.com/commits/4033
[22 Mar 2006 22:47] Dmitry Lenev
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.
[23 Mar 2006 7:57] Magnus BlÄudd
Reviewed the patch and applied it on Windows. Works fine. Ok to push.
[24 Mar 2006 10:53] Konstantin Osipov
Although some changes are requested in the review, it's ok to push after fixing them.
[24 Mar 2006 10:54] Konstantin Osipov
(Reviewed by email and irc)
[24 Mar 2006 19:03] 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/4139
[24 Mar 2006 19:43] 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/4144
[27 Mar 2006 14:44] Dmitry Lenev
Fix pushed to trees marked as 5.0.20 and 5.1.8
[31 Mar 2006 7:28] Jon Stephens
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.
[6 Apr 2006 20:21] Konstantin Osipov
Bug#18816 has been marked as a duplicate of this bug.
[26 Apr 2007 19:51] Valerii Kravchuk
Bug #28117 was marked as a duplicate of this one.