MySQL Bugs: #69465: using ignore to alter table ,but failed to add unique key

Bug #69465	using ignore to alter table ,but failed to add unique key
Submitted:	14 Jun 2013 5:26	Modified:	14 Jun 2013 7:22
Reporter:	andy yang	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.5	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	unique key

Description:
I have an Innodb table , need to add unique key , but there some duplicate key,

when i use alter ingore syntax, it still can't work . 

How to repeat:
root@test 01:21:52>show variables like '%sql_mode%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
+---------------+-------+
1 row in set (0.00 sec)

root@test 01:21:57>create table test_uk(id bigint auto_increment primary key , id2 int);
Query OK, 0 rows affected (0.08 sec)

root@test 01:22:16>insert into test_uk (id2) value (1,1);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
root@test 01:22:39>insert into test_uk (id2) values (1),(1);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

root@test 01:22:51>alter ignore table test_uk add unique key udx_key(id2);
ERROR 1062 (23000): Duplicate entry '1' for key 'udx_key'
root@test 01:23:09>show variables like '%version%';
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| innodb_version          | 1.1.8-20.1          |
| protocol_version        | 10                  |
| slave_type_conversions  |                     |
| version                 | 5.5.16-log          |
| version_comment         | Source distribution |
| version_compile_machine | x86_64              |
| version_compile_os      | Linux               |
+-------------------------+---------------------+
7 rows in set (0.00 sec)

Hello Andy,

Thank you for the report.
Verified as described.

Thanks,
Umesh

// 5.5.32 affected ( innodb seems to be affected, myisam works)

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

mysql> show variables like '%sql_mode%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
+---------------+-------+
1 row in set (0.01 sec)

mysql> create table test_uk(id bigint auto_increment primary key , id2 int);
Query OK, 0 rows affected (0.04 sec)

mysql> show create table test_uk\G
*************************** 1. row ***************************
       Table: test_uk
Create Table: CREATE TABLE `test_uk` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `id2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> insert into test_uk (id2) values (1),(1);
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> alter ignore table test_uk add unique key udx_key(id2);
ERROR 1062 (23000): Duplicate entry '1' for key 'udx_key'
mysql> 
mysql> select * from test_uk;
+----+------+
| id | id2  |
+----+------+
|  1 |    1 |
|  2 |    1 |
+----+------+
2 rows in set (0.00 sec)

mysql> alter table test_uk engine=myisam;
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> show create table test_uk\G
*************************** 1. row ***************************
       Table: test_uk
Create Table: CREATE TABLE `test_uk` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `id2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> 
mysql> alter ignore table test_uk add unique key udx_key(id2);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 1  Warnings: 0

mysql> select * from test_uk;
+----+------+
| id | id2  |
+----+------+
|  1 |    1 |
+----+------+
1 row in set (0.00 sec)

mysql> 

http://dev.mysql.com/doc/refman/5.5/en/alter-table.html

// 5.6.12 not affected

mysql> use test
Database changed
mysql> create table test_uk(id bigint auto_increment primary key , id2 int);
Query OK, 0 rows affected (0.10 sec)

mysql> insert into test_uk (id2) values (1),(1);
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> alter ignore table test_uk add unique key udx_key(id2);
Query OK, 2 rows affected (0.13 sec)
Records: 2  Duplicates: 1  Warnings: 0

mysql> select * from test_uk;
+----+------+
| id | id2  |
+----+------+
|  1 |    1 |
+----+------+
1 row in set (0.00 sec)

mysql> show create table test_uk\G
*************************** 1. row ***************************
       Table: test_uk
Create Table: CREATE TABLE `test_uk` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `id2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `udx_key` (`id2`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show global variables like '%sql_mode%';
+---------------+--------------------------------------------+
| Variable_name | Value                                      |
+---------------+--------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+
1 row in set (0.00 sec)

mysql> show variables like '%sql_mode%';
+---------------+--------------------------------------------+
| Variable_name | Value                                      |
+---------------+--------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+
1 row in set (0.00 sec)

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

Thanks for your comment。 My teammate tell me that it is a bug . Please visit http://dev.mysql.com/doc/refman/5.5/en/innodb-create-index-limitations.html  Bug #40344.

root@test 03:20:30>set sql_log_bin =0;
Query OK, 0 rows affected (0.00 sec)

root@test 03:20:31>set session old_alter_table =on;
Query OK, 0 rows affected (0.00 sec)

root@test 03:20:48>alter ignore table  test_uk add unique key udx_key(id2);
Query OK, 2 rows affected (0.25 sec)
Records: 2  Duplicates: 1  Warnings: 0

done

Duplicate of http://bugs.mysql.com/bug.php?id=40344