Bug #48972 mysqldump --insert-ignore leaves set unique_checks=0.
Submitted: 22 Nov 2009 20:03 Modified: 1 Apr 2014 17:48
Reporter: Eric Bergen (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S3 (Non-critical)
Version:5.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: Contribution, insert-ignore, mysqldump, unique_checks
Triage: Triaged: D5 (Feature request)

[22 Nov 2009 20:03] Eric Bergen
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,
[23 Nov 2009 4:40] Valeriy Kravchuk
Thank you for the feature request and code contributed.
[7 Apr 2011 16:57] Eric Bergen
This bug is 500 days old. Is there any chance of it being fixed?
[1 Apr 2014 17:48] Eric Bergen
This has been verified for just over 1000 days..