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)
• Status: Duplicate
• Category: MySQL Server: mysqldump Command-line Client
• Severity: S2 (Serious)
• OS: Any
• 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