Bug #19132 set unique_checks = 0 does not workd

• Submitted: 16 Apr 2006 18:34
• Modified: 17 Apr 2006 19:57
• Reporter: Roland Bouman
• Status: Closed
• Category: MySQL Server: Documentation
• Severity: S3 (Non-critical)
• Version: 5.1.7 beta
• OS: Windows (win xp)
• CPU Architecture: Any

[16 Apr 2006 18:34] Roland Bouman

Description:
According to http://dev.mysql.com/doc/refman/5.1/en/set-option.html, the dynamic system variable UNIQUE_CHECKS can be set to 0 to prevent checking of unique keys at insert time. 

I cannot get it to work. Setting the value to 0 still gives me 

ERROR 1062 (23000): Duplicate entry <entry> for key <keyname>

How to repeat:
create table parent_table(
    id int auto_increment primary key
,   name varchar(50) unique
)
engine = innodb
;
insert into parent_table(name)
values ('Mark'),('Luke'),('Matthew'),('John')
;
set unique_checks := 0
;
insert into parent_table(name)
values ('Mark'),('Luke'),('Matthew'),('John')
;
insert into parent_table(name)
values ('Mark'),('Luke'),('Matthew'),('John')
;
ERROR 1062 (23000): Duplicate entry 'Mark' for key 'name'

mysql> select @@unique_checks,@@sql_mode,version();
+-----------------+------------+---------------+
| @@unique_checks | @@sql_mode | version()     |
+-----------------+------------+---------------+
|               0 |            | 5.1.7-beta-nt |
+-----------------+------------+---------------+
1 row in set (0.01 sec)

Suggested fix:
disable unique key checking when the UNIQUE_CHECKS dynamic system variable is set to 0

[17 Apr 2006 4:24] Morgan Tocker

Verified in 5.0.19 and with MyISAM storage engine.

[17 Apr 2006 5:08] Heikki Tuuri

This is a bug in documentation. The setting

UNIQUE_CHECKS=0

allows the storage engine to assume that the data set to be imported does not contain unique key violations. But the engine is still allowed to give a duplicate key error if it detects one. The documentation should be corrected.

[17 Apr 2006 8:27] Roland Bouman

Ok, thanks for the help sofar. Maybe you guys have a generic way to identify the relevant parts of the documentation, but so far, I spotted references to unique_checks in:

http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html
http://dev.mysql.com/doc/refman/5.1/en/set-option.html

[17 Apr 2006 15:21] MySQL Verification Team

Thank you for the bug report.
Category changed to documentation.

[17 Apr 2006 19:57] Paul DuBois

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).