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

[14 Jun 2013 5:26] andy yang
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)
[14 Jun 2013 6:57] MySQL Verification Team
Hello Andy,

Thank you for the report.
Verified as described.

Thanks,
Umesh
[14 Jun 2013 7:03] MySQL Verification Team
// 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)
[14 Jun 2013 7:22] andy yang
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
[14 Jun 2013 7:22] andy yang
done
[14 Jun 2013 7:27] MySQL Verification Team
Duplicate of http://bugs.mysql.com/bug.php?id=40344

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.051 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.