Restoring from a mysqldump into tables with triggers
This is actually old news, but I never thought to file a bug report (until now) or say anything to anyone about it. If you use mysqldump to dump and restore a MySQL table that has INSERT triggers, you can get different data in your restored database than you had when you dumped. The problem? The tool dumps the triggers before the data, so they get added back to the table before the rows are inserted.
The fix for this is really trivial. Just move the triggers after the INSERTs. Someone should patch mysqldump, but I’ve been too lazy… and besides, you know what I say about me and C programming. Unsafe at any speed.
This isn’t a problem with mk-parallel-dump, by the way. It puts the triggers in separate files, and mk-parallel-restore adds them back after the data is restored.
I like the Postgres approach to this problem:
ALTER TABLE foo DISABLE TRIGGER ALL;
ALTER TABLE foo ENABLE TRIGGER ALL;
Andrew
8 Jan 09 at 10:36 am
mysqldump in 5.0.67 appears to put triggers after the inserts already:
# mysqldump5 –version
mysqldump Ver 10.11 Distrib 5.0.67, for apple-darwin9.5.0 (i686)
–
– Table structure for table `customer`
–
DROP TABLE IF EXISTS `customer`;
…
CREATE TABLE `customer` (
…
) ENGINE=InnoDB AUTO_INCREMENT=600 DEFAULT CHARSET=utf8;
–
– Dumping data for table `customer`
–
LOCK TABLES `customer` WRITE;
/*!40000 ALTER TABLE `customer` DISABLE KEYS */;
INSERT INTO `customer` VALUES (…);
/*!40000 ALTER TABLE `customer` ENABLE KEYS */;
UNLOCK TABLES;
DELIMITER ;;
/*!50003 SET SESSION SQL_MODE=”STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER” */;;
/*!50003 CREATE */ /*!50017 DEFINER=`root`@`localhost` */ /*!50003 TRIGGER `customer_create_date` BEFORE INSERT ON `customer` FOR EACH ROW SET NEW.create_date = NOW() */;;
DELIMITER ;
…
Although it would be nice in some cases to disable/enable triggers (especially selectively).
Ms. Anthrope
8 Jan 09 at 1:14 pm