Description:
By default mysqldump always appends set_unique_checks=0 to it's output. When using --insert-ignore --no-create-info to try to merge two sets of data the unique checks on secondary indexes are disabled causing duplicate rows.
How to repeat:
mysqldump -u root --database test -S /var/lib/mysql/mysql.sock --insert-ignore --no-create-info
-- MySQL dump 10.10
--
-- Host: localhost Database: test
-- ------------------------------------------------------
-- Server version 5.0.27-standard
/*!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 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!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 */;
--
Suggested fix:
Disable unique_checks=0 when --insert-ignore is used.
http://ebergen.net/patches/mysqldump_unique_checks.patch
--- orig/client/mysqldump.c 2009-01-13 05:02:01.000000000 -0800
+++ patched/client/mysqldump.c 2009-11-22 11:52:49.000000000 -0800
@@ -594,8 +594,13 @@
if (!path)
{
+ if (!opt_ignore)
+ {
+ fprintf(md_result_file,"\
+/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;\n");
+ }
+
fprintf(md_result_file,"\
-/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;\n\
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;\n\
");
}
@@ -625,8 +630,13 @@
if (!path)
{
fprintf(md_result_file,"\
-/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;\n\
+/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;\n");
+
+ if (!opt_ignore)
+ {
+ fprintf(md_result_file,"\
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;\n");
+ }
}
if (opt_set_charset)
fprintf(sql_file,
