MySQL Bugs: #12597: mysqldump dumps triggers wrongly

Bug #12597	mysqldump dumps triggers wrongly
Submitted:	16 Aug 2005 11:23	Modified:	20 Oct 2005 1:19
Reporter:	Max Mether	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S3 (Non-critical)
Version:	5.0.11	OS:	Windows (Windows)
Assigned to:		CPU Architecture:	Any

Description:
mysqldump does two things wrongly when dumping triggers. 

1) The end of the comment '*/' is attached to the CREATE TRIGGER statement and the parser doesn't accept this (at least not on windows)

2) The CREATE TRIGGER statements are stored before the INSERTS => all INSERT triggers are fired again when restoring a table
 

How to repeat:
Create a dump of a table with a trigger and execute the dump

Suggested fix:
1) Add a space

2) Either ad a ALTER TABLE DISABLE TRIGGERS (or similar) or put the CREATE TRIGGER statements after the INSERTS

Could you please attach a file with a test case which provoke the
issue reported. Looks to me is the way how was created the trigger
and not the */ position.

C:\mysql\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11 to server version: 5.0.12-beta-nt

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

mysql> create database db;
Query OK, 1 row affected (0.00 sec)

mysql> use db;
Database changed
mysql> CREATE TABLE test1(a1 INT);
Query OK, 0 rows affected (0.11 sec)

mysql> CREATE TABLE test2(a2 INT);
Query OK, 0 rows affected (0.09 sec)

mysql>
mysql> DELIMITER //
mysql>
mysql> CREATE TRIGGER testref BEFORE INSERT ON test1
    ->   FOR EACH ROW BEGIN
    ->     INSERT INTO test2 SET a2 = NEW.a1;
    ->   END
    -> //
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> insert into test1 values (1);
Query OK, 1 row affected (0.03 sec)

mysql> select * from test2;
+------+
| a2   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql>

C:\mysql\bin>mysqldump -uroot db > db.sql

/*!50003 SET @OLD_SQL_MODE=@@SQL_MODE*/;
DELIMITER //;
/*!50003 SET SESSION SQL_MODE=""*/ //
/*!50003 CREATE TRIGGER `testref` BEFORE INSERT ON `test1` FOR EACH ROW BEGIN
    INSERT INTO test2 SET a2 = NEW.a1;
  END*/ //

C:\mysql\bin>mysqladmin -uroot drop db
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 'db' database [y/N] y
Database "db" dropped

C:\mysql\bin>mysqladmin -uroot create db

C:\mysql\bin>mysql -uroot db
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15 to server version: 5.0.12-beta-nt

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

mysql> source c:\mysql\bin\db.sql
Query OK, 0 rows affected (0.00 sec)

<cut>

ERROR 1146 (42S02): Table 'db.test2' doesn't exist
ERROR 1146 (42S02): Table 'db.test2' doesn't exist
Query OK, 0 rows affected (0.00 sec)

<cut>

Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+--------------+
| Tables_in_db |
+--------------+
| test1        |
| test2        |
+--------------+
2 rows in set (0.00 sec)

mysql>

mysql> show triggers from db\G
*************************** 1. row ***************************
  Trigger: testref
    Event: INSERT
    Table: test1
Statement:  BEGIN
    INSERT INTO test2 SET a2 = NEW.a1;
  END
   Timing: BEFORE
  Created: NULL
 sql_mode:
1 row in set (0.00 sec)

mysql> select * from test2;
+------+
| a2   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

Do the following:

create database db;
use db;
create table rowtest (a int(11) NOT NULL auto_increment, b char(10), primary key (a));
create table rowcopy select * from rowtest;
create trigger rowtest_ai after insert on rowtest for each row insert into rowcopy values (new.a, new.b);

insert into rowtest (b) values ('test'), ('insert'), ('enough');

After that I took a mysqldump and got:
<cut>
--
-- Table structure for table `rowcopy`
--

DROP TABLE IF EXISTS `rowcopy`;
CREATE TABLE `rowcopy` (
  `a` int(11) NOT NULL default '0',
  `b` char(10) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `rowcopy`
--

/*!40000 ALTER TABLE `rowcopy` DISABLE KEYS */;
LOCK TABLES `rowcopy` WRITE;
INSERT INTO `rowcopy` VALUES (1,'test'),(2,'insert'),(3,'enough');
UNLOCK TABLES;
/*!40000 ALTER TABLE `rowcopy` ENABLE KEYS */;

--
-- Table structure for table `rowtest`
--

DROP TABLE IF EXISTS `rowtest`;
CREATE TABLE `rowtest` (
  `a` int(11) NOT NULL auto_increment,
  `b` char(10) default NULL,
  PRIMARY KEY  (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

/*!50003 SET @OLD_SQL_MODE=@@SQL_MODE*/;
DELIMITER //;
/*!50003 SET SESSION SQL_MODE=""*/ //
/*!50003 CREATE TRIGGER `rowtest_ai` AFTER INSERT ON `rowtest` FOR EACH ROW insert into rowcopy values (new.a, new.b)*/
//

DELIMITER ;//
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;
--
-- Dumping data for table `rowtest`
--

/*!40000 ALTER TABLE `rowtest` DISABLE KEYS */;
LOCK TABLES `rowtest` WRITE;
INSERT INTO `rowtest` VALUES (1,'test'),(2,'insert'),(3,'enough');
UNLOCK TABLES;
/*!40000 ALTER TABLE `rowtest` ENABLE KEYS */;

<cut>

This dump doesn't run, and as we can see the trigger would be fired again, if the dump worked

Thanks for bug report.I was able to repeat the bug.

/*!50003 CREATE TRIGGER `rowtest_ai` AFTER INSERT ON `rowtest` FOR EACH ROW insert into
rowcopy values (new.a, new.b)*/ //

ERROR 1064 (42000) at line 51: You have an error in your SQL syntax; check the manual that corresponds to your MySQL ser
ver version for the right syntax to use near 'insert into
rowcopy values (new.a, new.b)*/' at line 1

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/internals/29214

Fixed - see dump file below (using Jorge's example)

-- MySQL dump 10.10
--
-- Host: localhost    Database: bug12597
-- ------------------------------------------------------
-- Server version       5.0.13-beta-debug-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `test1`
--

DROP TABLE IF EXISTS `test1`;
CREATE TABLE `test1` (
  `a1` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `test1`
--

/*!40000 ALTER TABLE `test1` DISABLE KEYS */;
LOCK TABLES `test1` WRITE;
INSERT INTO `test1` VALUES (1);
UNLOCK TABLES;
/*!40000 ALTER TABLE `test1` ENABLE KEYS */;

--
-- Table structure for table `test2`
--

DROP TABLE IF EXISTS `test2`;
CREATE TABLE `test2` (
  `a2` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `test2`
--

/*!40000 ALTER TABLE `test2` DISABLE KEYS */;
LOCK TABLES `test2` WRITE;
INSERT INTO `test2` VALUES (1);
UNLOCK TABLES;
/*!40000 ALTER TABLE `test2` ENABLE KEYS */;

/*!50003 SET @OLD_SQL_MODE=@@SQL_MODE*/;
DELIMITER //;
/*!50003 SET SESSION SQL_MODE="" */ //
/*!50003 CREATE TRIGGER `testref` BEFORE INSERT ON `test1` FOR EACH ROW begin
insert into test2 SET a2 = NEW.a1;
end */ //

DELIMITER ;//
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

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/internals/29287

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/internals/29533

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/internals/29589

pushed patch

fixed in 5.0.13

Noted in 5.0.13 changelog.

Bug #41958 was marked as duplicate of this one.