| Bug #65731 | ALTER IGNORE behaves differently than documented | ||
|---|---|---|---|
| Submitted: | 25 Jun 2012 22:32 | Modified: | 26 Jun 2012 16:16 |
| Reporter: | Ryan Lowe | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.5.25 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[26 Jun 2012 7:41]
Valeriy Kravchuk
I do not see any problem. You added primary (and thus unique) key like this: primary key (c1,c2) and all your rows are unique by these 2 columns, (1,1), (1,2), (2,1) and (2,2). Other indexes are non-unique by default. So, why do you expect any other output?
[26 Jun 2012 15:51]
Ryan Lowe
haha yes you're right, I copied incorrectly. What it *should* have said was: mysql> create table ignore_test ( c1 int not null, c2 int not null) engine=innodb; Query OK, 0 rows affected (0.00 sec) mysql> insert into ignore_test values (1,1), (1,2), (2,1), (2,2); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> insert into ignore_test values (1,1), (1,2), (2,1), (2,2); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> alter ignore table ignore_test add primary key (c1,c2), add index (c1), add index (c2); ERROR 1062 (23000): Duplicate entry '1-1' for key 'PRIMARY' mysql> If I understand the manual correctly, this should have succeeded and the duplicate data been deleted?
[26 Jun 2012 16:16]
Sveta Smirnova
Thank you for the feedback. This is duplicate of bug #40344.
Description: The documentation states: "If IGNORE is specified, only the first row is used of rows with duplicates on a unique key. The other conflicting rows are deleted. Incorrect values are truncated to the closest matching acceptable value." However, that is clearly not the actualy behavior: mysql> create table ignore_test ( -> c1 int not null, -> c2 int not null) -> engine=innodb; Query OK, 0 rows affected (0.01 sec) mysql> insert into ignore_test values (1,1), (1,2), (2,1), (2,2); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> alter ignore table ignore_test add primary key (c1,c2), add index (c1), add index (c2); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from ignore_test; +----+----+ | c1 | c2 | +----+----+ | 1 | 1 | | 1 | 2 | | 2 | 1 | | 2 | 2 | +----+----+ 4 rows in set (0.00 sec) mysql> show create table ignore_test\G *************************** 1. row *************************** Table: ignore_test Create Table: CREATE TABLE `ignore_test` ( `c1` int(11) NOT NULL, `c2` int(11) NOT NULL, PRIMARY KEY (`c1`,`c2`), KEY `c1` (`c1`), KEY `c2` (`c2`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) How to repeat: See main description.