Bug #41958 mysqldump shouldn't dump triggers before data

• Submitted: 8 Jan 13:29
• Modified: 14 Jan 9:14
• Reporter: Baron Schwartz (Basic Quality Contributor)
• Status: Duplicate
• Category: Client
• Severity: S2 (Serious)
• OS: Any
• Tags: qc

[8 Jan 13: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 14: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 1: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 9:14] Sveta Smirnova

Thank you for the feedback.

Closed as duplicate of bug #12597