MySQL Bugs: #45422: ERROR 1602 (HY000): Corrupted TRG file for table `NAMEOFDATABASE`.`NAMEOFTABLE1`

Bug #45422	ERROR 1602 (HY000): Corrupted TRG file for table `NAMEOFDATABASE`.`NAMEOFTABLE1`
Submitted:	10 Jun 2009 5:12	Modified:	20 Jul 2009 23:38
Reporter:	Roel Van de Paar	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S2 (Serious)
Version:	5.1.31	OS:	Any
Assigned to:	Sveta Smirnova	CPU Architecture:	Any

Description:
Not sure how to go about describing this bug.

Background: the customer is exporting data from a 5.0.45 installation using mysqldump. The data contains a trigger. The dump generates invalid SQL code:

--------
/*!50003 CREATE */ /*!50017 DEFINER=`root`@`localhost` */ /*!50003 TRIGGER `NAMEOFTRIGGER` 
AFTER DELETE ON `NAMEOFTABLE1` FOR EACH ROW BEGIN
DELETE NAMEOFTABLE2.* from NAMEOFTABLE2 where FIELD1 = OLD.FIELD1;
INSERT INTO NAMEOFTABLE2 (VARIOUSFIELDS)
VALUES (OLD.VARIOUSVALUES);
END */;;
--------

When importing this into a 5.1.32 installation, the customer gets:

--------
ERROR 1109 (42S02) at line 92: Unknown table 'NAMEOFTABLE2' in MULTI DELETE. 
--------

(NAMEOFTRIGGER/NAMEOFTABLE1/NAMEOFTABLE2/VARIOUSFIELDS/VARIOUSVALUES = Used for privacy purposes.)

How to repeat:
When I did some testing on this, using a SQL dump provided by the customer, I had various results each time I imported it using the SOUREC command on 5.1.31-community-log running on Windows (Vista). Here are some examples (open lines removed to save space):

Test 1:
-----------
mysql> SOURCE d:\\_ISSUES\\CSCISSUENUMBER\\CUSTOMERDUMPFILE_mysql.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Empty set (0.00 sec)
Database changed
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.02 sec)
...
-----------

After this, I could show the trigger just fine:
-----------
mysql> SHOW CREATE TRIGGER NAMEOFTRIGGER\G
*************************** 1. row ***************************
               Trigger: NAMEOFTRIGGER
              sql_mode:
SQL Original Statement: CREATE DEFINER=`root`@`localhost` TRIGGER `NAMEOFTRIGGER ` AFTER DELETE ON `NAMEOFTABLE1` FOR EACH ROW BEGIN
DELETE NAMEOFTABLE2.* from NAMEOFTABLE2 where FIELD1 = OLD.FIELD1;
INSERT INTO NAMEOFTABLE2 (VARIOUSFIELDS)
VALUES (OLD.VARIOUSVALUES);
END
  character_set_client: utf8
  collation_connection: utf8_general_ci
    Database Collation: latin1_swedish_ci
1 row in set (0.02 sec)
-----------

Test 2:
-----------
mysql> SOURCE d:\\_ISSUES\\CSCISSUENUMBER\\CUSTOMERDUMPFILE_mysql.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
+-------+------+----------------------+
| Level | Code | Message              |
+-------+------+----------------------+
| Error | 1046 | No database selected |
+-------+------+----------------------+
1 row in set (0.00 sec)
Database changed
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
...
-----------

Test 3:
-----------
mysql> SOURCE d:\\_ISSUES\\CSCISSUENUMBER\\CUSTOMERDUMPFILE_mysql.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
+-------+------+---------------------------------------------------+
| Level | Code | Message                                           |
+-------+------+---------------------------------------------------+
| Error | 1602 | Corrupted TRG file for table `NAMEOFDATABASE`.`NAMEOFTABLE1` |
+-------+------+---------------------------------------------------+
1 row in set (0.00 sec)
Database changed
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.07 sec)
...
-----------

Notice the difference: once it works, once it gives error 1046, once it gives error 1602.

At times the trigger could be shown using the SHOW CREATE TRIGGER, at other times it would give the same 1602 error:

-----------
mysql> show create trigger NAMEOFTRIGGER;
ERROR 1602 (HY000): Corrupted TRG file for table `NAMEOFDATABASE`.`NAMEOFTABLE1`
-----------

When testing on 5.1.34, running on Linux, it imported fine, but gives another error later in the SQL script (which is not there on Windows):

-----------
mysql> source /_ISSUES/CSCISSUENUMBER/CUSTOMERDUMPFILE_mysql.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Empty set (0.00 sec)
Database changed
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.16 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 7851 rows affected (1.02 sec)
Records: 7851  Duplicates: 0  Warnings: 0
Query OK, 257 rows affected (0.03 sec)
Records: 257  Duplicates: 0  Warnings: 0
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
ERROR 1109 (42S02): Unknown table 'NAMEOFTABLE2' in MULTI DELETE
...
-----------

Remember that all the tests above (except for the last one) are on the *same* system/OS (Windows), with the same MySQL installation (5.1.31), some test ran directly after each other with possibly only a dump database and/or a server restart in between. 

I thought that the 1602 error in test 3 may have been triggered by the fact that the TRG file was still there (database not deleted), but on re-testing this (by re-importing without dumping the database) it imported the source just fine without any errors (i.e. it looked exactly like test 1), however the trigger did not show when using SHOW CREATE TRIGGER:

-----------
mysql> show create trigger NAMEOFTRIGGER;
ERROR 1602 (HY000): Corrupted TRG file for table `NAMEOFDATABASE`.`NAMEOFTABLE1`
-----------

As you can see, the results vary constantly...

Suggested fix:
o Fix 'random' behavior seen when importing the SQL file in question. 
o Fix mysqldump if not done already > 5.0.45 (more info following).
o Review trigger code & related error checking.

"multi delete" error looks same as bug #27525

Also note bug #45235

In regards the mysqldump side of things: mysqldump on 5.1.31 (Windows) dumps the incorrect SQL as-is, even when a 1602 has already been shown:

-------
mysql> show create trigger NAMEOFTRIGGER;
ERROR 1602 (HY000): Corrupted TRG file for table `NAMEOFDATABASE`.`NAMEOFTABLE1`
mysql> exit
Bye

C:\mysql5.1.31\bin>mysqldump -uroot -psystem --opt NAMEOFDATABASE > out.sql

C:\mysql5.1.31\bin>
-------

The out.sql file contains the incorrect SQL trigger code (as listed already above).

There seem to be 3 issues here:

1. At some point, server accepted some wrong code for the trigger.
2. mysqldump does not correct it (or warn about it) when dumping.
3. Importing it again gives various 'random' issues, as per the above.

Regarding to MULTI DELETE error: dump contains mixed register, so it is correct to get such error on Linux => this part is not a bug.

Other part about restore on Windows still interesting though.

Thank you for the report.

I can not repeat error which you experienced on Windows. Please provide your configuration file.

[client]
port=5131

[mysql]
default-character-set=latin1

[mysqld]
log = general.log
port=5131
basedir="C:/mysql5.1.31/"
datadir="C:/mysql5.1.31/Data/"
default-character-set=latin1
default-storage-engine=MyISAM
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=100
query_cache_size=15M
table_cache=256
tmp_table_size=18M
thread_cache_size=8
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=35M
key_buffer_size=23M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=256K
innodb_data_home_dir="C:/mysql5.1.31/data/"
innodb_data_file_path=ibdata1:700M;ibdata2:200M:autoextend
innodb_additional_mem_pool_size=2M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=42M
innodb_log_file_size=10M
innodb_thread_concurrency=8

Vista Version: 

C:\Windows\system32>cmd /k Wmic OS get BuildType,Caption,Version|find "M"
Multiprocessor Free  Microsoft® Windows VistaT Business  6.0.6001

Thank you for the feedback.

I still can not repeat described behavior. Please try on Windows with current version 5.1.35 and if problem still exists provide copy-paste from cmd.exe

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I've worked out why some of the errors were happening. 

In the mysqldump-generated .sql file, for some reason, the following statement was there:

SHOW WARNINGS;

I asked the customer if this was inserted manually, but have not received a response yet.

In either case, it explains why the output of running the .sql script looks different at different times; i.e. if an error occurred just before running the SOURCE command, that error was repeated as part of the output because of the SHOW WARNINGS command.

However, this does not explain the (only remaining and unaddressed) error:
o ERROR 1602 (HY000): Corrupted TRG file for table `NAMEOFDATABASE`.`NAMEOFTABLE1` 

I have tried to re-create the issue on 5.1.35, without success.

Also, 5.1.35 lists a proper error code for the previously mentioned 'incorrect' trigger syntax:

ERROR 1235 (42000): This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'

Also, as the 'incorrect' syntax is no longer accepted by the server in the first place, mysqldump would also not dump it as such, hence mysqldump is 'fixed' by the same token.