Bug #41958 mysqldump shouldn't dump triggers before data
Submitted: 8 Jan 2009 12:29 Modified: 14 Jan 2009 8:14
Reporter: Baron Schwartz (Basic Quality Contributor) Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S2 (Serious)
Version: OS:Any
Assigned to: CPU Architecture:Any
Tags: qc

[8 Jan 2009 12:29] Baron Schwartz
Description:
The mysqldump tool dumps triggers before data, which means that any INSERT triggers that modify data will cause the restored data to be different from the dumped data.

How to repeat:
The Sakila sample database is a good example.  Import it.  Use mk-table-checksum to checksum everything.  Dump and re-import, re-checksum, and the checksums won't match.

Suggested fix:
Move the triggers after the INSERT statements.
[9 Jan 2009 13:22] Mark Leith
Hey Baron!

I can't repeat this on 5.1.30 or 5.0.74. There was a similar bug way back in version before 5.0.13 (http://bugs.mysql.com/bug.php?id=12597). 

5.0.74:

--
-- Table structure for table `t1`
--

DROP TABLE IF EXISTS `t1`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `t1` (
  `i` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;

--
-- Dumping data for table `t1`
--

LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
INSERT INTO `t1` VALUES (2),(3),(4);
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
UNLOCK TABLES;

/*!50003 SET @SAVE_SQL_MODE=@@SQL_MODE*/;

DELIMITER ;;
/*!50003 SET SESSION SQL_MODE="" */;;
/*!50003 CREATE */ /*!50017 DEFINER=`root`@`localhost` */ /*!50003 TRIGGER `tr1` BEFORE INSERT ON `t1` FOR EACH ROW set new.i = new.i + 1 */;;

DELIMITER ;
/*!50003 SET SESSION SQL_MODE=@SAVE_SQL_MODE*/;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

5.1.30:

--
-- Table structure for table `t1`
--

DROP TABLE IF EXISTS `t1`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `t1` (
  `i` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;

--
-- Dumping data for table `t1`
--

LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
INSERT INTO `t1` VALUES (2),(3),(4);
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
UNLOCK TABLES;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = latin1 */ ;
/*!50003 SET character_set_results = latin1 */ ;
/*!50003 SET collation_connection  = latin1_swedish_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = '' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 trigger tr1 before insert on t1 for each row set new.i = new.i + 1 */;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;

Is it possible that an old mysqldump binary was being used? Which version did you see this on?
[14 Jan 2009 0:50] Baron Schwartz
Mark, you're right.  This is a duplicate of #12597 and I should have verified it before submitting.  I'm sorry to waste your time.
[14 Jan 2009 8:14] Sveta Smirnova
Thank you for the feedback.

Closed as duplicate of bug #12597