Bug #6357 SET UNIQUE_CHECKS=0; doesn't work

• Submitted: 1 Nov 2004 6:41
• Modified: 1 Nov 2004 7:08
• Reporter: Yoshinori Matsunobu
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.0.21 , 4.1.7
• OS: Windows (Windows XP, RedHat AS 3.0)
• CPU Architecture: Any

[1 Nov 2004 6:41] Yoshinori Matsunobu

Description:
The SQL statement "SET UNIQUE_CHECKS=0;" doesn't seem to work.

In the online manual (http://dev.mysql.com/doc/mysql/en/SET_OPTION.html), 
the folowing statements are written.
----
UNIQUE_CHECKS = {0 | 1} 
If set to 1 (the default), uniqueness checks for secondary indexes in InnoDB tables are performed. If set to 0, no uniqueness checks are done. This variable was added in MySQL 3.23.52. 
----

But ,after executing "SET UNIQUE_CHECKS=0;" , MySQL still perform uniqueness checks.

How to repeat:
mysql> CREATE TABLE test1 (col1 INTEGER PRIMARY KEY,col2 INTEGER,CONSTRAINT UNIQUE u(col2)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.07 sec)

mysql> show create table test1;
| test1 | CREATE TABLE `test1` (
  `col1` int(11) NOT NULL default '0',
  `col2` int(11) default NULL,
  PRIMARY KEY  (`col1`),
  UNIQUE KEY `u` (`col2`)
) ENGINE=InnoDB DEFAULT CHARSET=sjis |

mysql> INSERT INTO test1 VALUES(100,100),(101,101),(102,102);
Query OK, 3 rows affected (0.14 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test1;
+------+------+
| col1 | col2 |
+------+------+
|  100 |  100 |
|  101 |  101 |
|  102 |  102 |
+------+------+
3 rows in set (0.02 sec)

mysql> SET UNIQUE_CHECKS=0;
Query OK, 0 rows affected (0.01 sec)

mysql> select @@unique_checks;
+-----------------+
| @@unique_checks |
+-----------------+
|               0 |
+-----------------+
1 row in set (0.02 sec)

mysql> INSERT INTO test1 VALUES(103,101);
ERROR 1062 (23000): Duplicate entry '101' for key 2

mysql> INSERT INTO test1 VALUES(100,103);
ERROR 1062 (23000): Duplicate entry '100' for key 1

mysql> select * from test1;
+------+------+
| col1 | col2 |
+------+------+
|  100 |  100 |
|  101 |  101 |
|  102 |  102 |
+------+------+
3 rows in set (0.02 sec)

[1 Nov 2004 6:47] Yoshinori Matsunobu

Sorry, I experienced this problem in 4.0.21 and 4.1.7 , not 4.0.7.

[1 Nov 2004 7:08] Heikki Tuuri

Yoshinori,

thank you for the bug report. The documentation was incorrect.

Best regards,

Heikki

ChangeSet
  1.2280 04/11/01 08:39:12 [e-mail] +1 -0
  manual.texi:
    UNIQUE_CHECKS=0 was incorrectly documented: it only removes uniqueness checks when
secondary index entries are inserted to InnoDB's insert buffer

  Docs/manual.texi
    1.2167 04/11/01 08:39:06 [e-mail] +5 -3
    UNIQUE_CHECKS=0 was incorrectly documented: it only removes uniqueness checks when
secondary index entries are inserted to InnoDB's insert buffer

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	heikki
# Host:	hundin.mysql.fi
# Root:	/home/heikki/mysqldoc

--- 1.2166/Docs/manual.texi	Sat Oct 30 14:51:20 2004
+++ 1.2167/Docs/manual.texi	Mon Nov  1 08:39:06 2004
@@ -59325,9 +59325,11 @@
 
 @item UNIQUE_CHECKS = @{0 | 1@}
 If set to @code{1} (the default), uniqueness checks for secondary indexes in
-@code{InnoDB} tables are performed.  If set to @code{0}, no uniqueness
-checks are done.  This variable was added in MySQL 3.23.52.
-@xref{InnoDB foreign key constraints,  , @code{InnoDB} foreign key constraints}.
+@code{InnoDB} tables are performed. If set to @code{0}, uniqueness
+checks are not done for index entries inserted into InnoDB's insert
+buffer. If you know for certain that your data does not contain
+uniqueness violations, you can set this to 0 to speed up large table
+imports to InnoDB. This variable was added in MySQL 3.23.52.
 
 @end table