Bug #73183 ALTER IGNORE TABLE Does not Ignore Duplicate Unique Keys as Documented
Submitted: 3 Jul 2014 3:22 Modified: 22 Jul 2014 9:16
Reporter: Jervin R Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.5.37, 5.5.38 OS:Any
Assigned to: CPU Architecture:Any

[3 Jul 2014 3:22] Jervin R
Description:
The documentation says with the IGNORE extension, UNIQUE KEY violations on the new table will be deleted, however this is not the case.

How to repeat:
[revin@forge mysql]$ sb 5538
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql [localhost] {msandbox} ((none)) > select @@unique_checks, @@sql_mode;
+-----------------+------------+
| @@unique_checks | @@sql_mode |
+-----------------+------------+
|               1 |            |
+-----------------+------------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} ((none)) > create table t (id int unsigned not null auto_increment primary key, c char(1)) engine=innodb;
ERROR 1046 (3D000): No database selected
mysql [localhost] {msandbox} ((none)) > use test;
Database changed
mysql [localhost] {msandbox} (test) > create table t (id int unsigned not null auto_increment primary key, c char(1)) engine=innodb;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (test) > insert into t (c) values('a'), ('a');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > alter ignore table t add unique index c (c);
ERROR 1062 (23000): Duplicate entry 'a' for key 'c'

This test works as expected on 5.6.19.
[3 Jul 2014 3:38] Umesh Shastry
Hello Jervin,

Thank you for the report and test case.
Verified as described.

Thanks,
Umesh
[3 Jul 2014 3:39] Umesh Shastry
// 5.5 - Doesn't work as per doc - http://dev.mysql.com/doc/refman/5.5/en/alter-table.html

mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.5.38-debug-log |
+------------------+
1 row in set (0.00 sec)

mysql> use test
Database changed
Database changed
mysql> select @@unique_checks, @@sql_mode;
+-----------------+------------+
| @@unique_checks | @@sql_mode |
+-----------------+------------+
|               1 |            |
+-----------------+------------+
1 row in set (0.00 sec)

mysql> create table t (id int unsigned not null auto_increment primary key, c char(1)) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t (c) values('a'), ('a');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> alter ignore table t add unique index c (c);
ERROR 1062 (23000): Duplicate entry 'a' for key 'c'
mysql>

// 5.6 - Works as per documented
mysql>
mysql> select version();
+---------------------------------------+
| version()                             |
+---------------------------------------+
| 5.6.20-enterprise-commercial-advanced |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> select @@unique_checks, @@sql_mode;
+-----------------+------------------------+
| @@unique_checks | @@sql_mode             |
+-----------------+------------------------+
|               1 | NO_ENGINE_SUBSTITUTION |
+-----------------+------------------------+
1 row in set (0.00 sec)

mysql> create table t (id int unsigned not null auto_increment primary key, c char(1)) engine=innodb;
Query OK, 0 rows affected (0.04 sec)

mysql>  insert into t (c) values('a'), ('a');
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> alter ignore table t add unique index c (c);
Query OK, 2 rows affected, 1 warning (0.05 sec)
Records: 2  Duplicates: 1  Warnings: 1

mysql> select * from t;
+----+------+
| id | c    |
+----+------+
|  1 | a    |
+----+------+
1 row in set (0.00 sec)
[3 Jul 2014 5:33] Shane Bester
for what its worth, "alter ignore" is removed in 5.7, so just gives a syntax error.
[22 Jul 2014 9:16] Abhishek Ranjan
This is documented as a limitation : http://dev.mysql.com/doc/refman/5.5/en/innodb-create-index-limitations.html

Duplicate of Bug#40344.