| 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: | |
| 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:38]
MySQL Verification Team
Hello Jervin, Thank you for the report and test case. Verified as described. Thanks, Umesh
[3 Jul 2014 3:39]
MySQL Verification Team
// 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]
MySQL Verification Team
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.
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.