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: | |
| 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
[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.