Bug #40344 ALTER IGNORE TABLE t ADD INDEX does not ignore in fast index creation
Submitted: 27 Oct 2008 12:16 Modified: 10 May 2012 17:24
Reporter: Marko Mäkelä Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.1.41,5.5.1-m2, 6.0 OS:Any
Assigned to: Jon Olav Hauglid
Triage: Triaged: D3 (Medium)

[27 Oct 2008 12:16] Marko Mäkelä
Description:
Fast index creation handler::add_index() or handler::alter_table_phase1() is not passed the "bool ignore" flag. Thus, these methods will ignore the IGNORE qualifier.

How to repeat:
Install the InnoDB plugin and try the following:

create table t1(a int not null, b int, c char(10) not null, d varchar(20)) engine = innodb;
insert into t1 values (5,5,'oo','oo'),(4,4,'tr','tr'),(3,4,'ad','ad'),(2,3,'ak','ak');
alter table t1 add unique index (b); -- should fail
alter ignore table t1 add unique index (b); -- should not fail

Suggested fix:
In 6.0, pass the ignore flag somehow to the new ALTER TABLE interface. In 5.1, whose API is frozen, do a full table copy when ALTER IGNORE is specified.
[27 Oct 2008 14:06] Miguel Solorzano
Thank you for the bug report. Verified as described.
[4 Nov 2008 10:36] Satya B
Hi,

 I am not able to reproduce the bug with 5.1 , 6.0 main branches. In these branches, I even see that add_index() , alter_table_phase1() are not invoked. 

Is the code related to fast index creation in main branches? Can someone please provide steps to reproduce this?
[4 Nov 2008 10:59] Marko Mäkelä
InnoDB in the main branch does not support fast index creation. The InnoDB plugin does, but the adaptation for 6.0 is work in progress.

Based on my look at the source code, NDB seems to support fast index creation, at least in 6.0. Can you verify it there?
[5 Nov 2008 10:35] Satya B
I didn't verify the functionality of ALTER IGNORE TABE t ADD INDEX.. on NDB but the methods add_index() and alter_table_phase1() are not implemented in NDB in the main branch.

I will try compiling mysql with Innodb 1.0.1 source and see if I can hit this one.
[17 Dec 2008 6:01] Satya B
Will revisit this bug when it is reproducible in main branches
[23 Oct 2009 5:41] Satya B
Reproduced with latest Innodb plugin 1.0.5

mysql> create table t1(a int not null, b int, c char(10) not null, d varchar(20)) engine = innodb;
Query OK, 0 rows affected (0.09 sec)

mysql> insert into t1 values (5,5,'oo','oo'),(4,4,'tr','tr'),(3,4,'ad','ad'),(2,3,'ak','ak');
Query OK, 4 rows affected (0.06 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> alter table t1 add unique index (b);
ERROR 1062 (23000): Duplicate entry '4' for key 'b'
mysql> alter ignore table t1 add unique index (b); //shouldn't fail
ERROR 1062 (23000): Duplicate entry '4' for key 'b'
[20 Nov 2009 21:25] Shane Bester
innodb plugin in 5.1.41:

mysql> create table `t2` (`id` int not null) engine=innodb;
Query OK, 0 rows affected (1.34 sec)

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

mysql> alter ignore table t2 add unique key(id);
ERROR 1062 (23000): Duplicate entry '1' for key 'id'
[24 Feb 2010 9:43] Shane Bester
workaround is to first run "set session old_alter_table=1;"
[25 Jun 2010 5:49] chris claydon
I have just encountered this bug still present on MySQL 5.5 using InnoDB plugin.
The workaround suggested in the previous comment worked.
[20 Jan 2011 20:33] Dāvis Mosāns
same for me with MySQL 5.5.8 and InnoDB v 1.1.4
[16 Apr 2011 2:41] Shane Bester
bug #60886 is a duplicate of this
[15 Sep 2011 6:43] Marko Mäkelä
If the semantics of IGNORE is to delete those rows that would violate the constraint, I think that it only makes sense in a row-by-row-copying operation (set old_alter_table=0).

When creating a secondary index, InnoDB acquires a shared lock on the table, indicating that it cannot modify the table. Theoretically, if both the MySQL API and InnoDB supported IGNORE, the table should be exclusively locked during the index creation, so that the offending records can be deleted.

I would say that supporting IGNORE in the storage engine is too much trouble. To me, ALTER IGNORE seems risky. If a uniqueness constraint is violated, it will blindly delete offending records without any diagnostics. You would not know which of the duplicates was preserved and which one(s) were deleted.

Those who really need ALTER IGNORE can probably use the table-copying approach. This bug could be fixed easily by making IGNORE imply old_alter_table=1.
[21 Sep 2011 22:38] Trey Raymond
Posted this in the dupe bug, saw this is the master, reposting here:

Interesting behavior.  Note in test two, when I add a condition that innodb won't like, it somehow sends a warning and then processes the ignore, deleting rows.

mysql> show create table base;
+-------+--------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                 
           |
+-------+--------------------------------------------------------------------------------------------------------------------------+
| base  | CREATE TABLE `base` (
  `R` varchar(255) NOT NULL,
  `datetime` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from base;
+------+---------------------+
| R    | datetime            |
+------+---------------------+
| asdf | 2011-09-06 12:27:50 |
| asdf | 2011-09-13 12:28:00 |
+------+---------------------+
2 rows in set (0.00 sec)

Test 1: basic alter ignore.  Not working here due to duplicate key error.  This is likely a mysql bug, I can't think of
any other reason.

mysql> create table tmp like base;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tmp select * from base;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> alter ignore table tmp
    ->  add primary key (R);
ERROR 1062 (23000): Duplicate entry 'asdf' for key 'PRIMARY'
mysql> select * from tmp;
+------+---------------------+
| R    | datetime            |
+------+---------------------+
| asdf | 2011-09-06 12:27:50 |
| asdf | 2011-09-13 12:28:00 |
+------+---------------------+
2 rows in set (0.00 sec)

mysql> drop table tmp;
Query OK, 0 rows affected (0.64 sec)

Test 2: combine adding pk with order by statement.  This ignored the order by as it happened after the row filter for
the add primary key due to the order of statements, and therefore didn't achieve desired outcome.

mysql> create table tmp like base;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tmp select * from base;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> alter ignore table tmp
    ->  add primary key (R),
    ->  order by datetime desc;
Query OK, 2 rows affected, 1 warning (0.64 sec)
Records: 2  Duplicates: 1  Warnings: 0

mysql> show warnings;
+---------+------+--------------------------------------------------------------------------------+
| Level   | Code | Message                                                                        |
+---------+------+--------------------------------------------------------------------------------+
| Warning | 1105 | ORDER BY ignored as there is a user-defined clustered index in the table 'tmp' |
+---------+------+--------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> select * from tmp;
+------+---------------------+
| R    | datetime            |
+------+---------------------+
| asdf | 2011-09-06 12:27:50 |
+------+---------------------+
1 row in set (0.00 sec)

mysql> drop table tmp;
Query OK, 0 rows affected (0.63 sec)

Test 3: The reverse of test 2, doing the order by first before putting them into the pk, so the filter keeps things in
the desired order.  This failed with a syntax error, I have no idea why.

mysql> create table tmp like base;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tmp select * from base;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> alter ignore table tmp
    ->  order by datetime desc,
    ->  add primary key (R);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server
version for the right syntax to use near 'add primary key (R)' at line 3
mysql> select * from tmp;
+------+---------------------+
| R    | datetime            |
+------+---------------------+
| asdf | 2011-09-06 12:27:50 |
| asdf | 2011-09-13 12:28:00 |
+------+---------------------+
2 rows in set (0.00 sec)

mysql> drop table tmp;
Query OK, 0 rows affected (0.64 sec)

Test 4: This is doing the order by first and then adding the key.  This is undesirable as it is in two statements, but
if done at night on this not heavily written table we should be okay.  However, it encounters the same bug as test 1,
where for some reason it ignores my ignore and produces a duplicate key error.

mysql> create table tmp like base;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tmp select * from base;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> alter ignore table tmp
    ->  order by datetime desc;
Query OK, 2 rows affected (0.64 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> alter ignore table tmp
    ->  add primary key (R);
ERROR 1062 (23000): Duplicate entry 'asdf' for key 'PRIMARY'
mysql> select * from tmp;
+------+---------------------+
| R    | datetime            |
+------+---------------------+
| asdf | 2011-09-13 12:28:00 |
| asdf | 2011-09-06 12:27:50 |
+------+---------------------+
2 rows in set (0.00 sec)

mysql> drop table tmp;
Query OK, 0 rows affected (0.64 sec)
[22 Sep 2011 6:43] Jon Olav Hauglid
Hello,

Some comments about your tests.

Test 1: This is the bug reported here. The ALTER TABLE statement uses
"fast index creation" where IGNORE is currently not working.

Test 2: While ORDER BY ends up being ignored, this is found out after
the decision about using "fast index creation" or "table copy".
"fast index creation" can't be used with ORDER BY since ORDER BY
might change the order of the rows. ALTER TABLE therefore uses
the table-copying approach which does not have the IGNORE bug.
The end result is therefore that the ALTER TABLE statement completes
as it should. In effect you have a work-around for the bug reported here.

Test 3: The syntax error sounds like a separate bug.

Test 4: This is in effect the same as Test 1.
[27 Feb 2012 18:22] Trey Raymond
I have to wonder...how would it be using fast index creation to add a primary key?  It's an innodb table, it has to rebuild it to add a primary key.
[10 May 2012 17:24] Paul Dubois
Noted in 5.6.6 changelog.

For ALTER TABLE with the IGNORE keyword, IGNORE is now part of the
information provided to the storage engine. It is up to the storage
engine whether to use this when choosing between the in-place or copy
algorithm for altering the table. For InnoDB index operations, IGNORE 
is not used if the index is unique, so the copy algorithm is used.
[26 Jun 2012 16:17] Sveta Smirnova
Bug #65731 was marked as duplicate of this one.
[14 Jun 2013 7:29] Umesh Shastry
Bug #69465 was marked as duplicate of this one.
[22 Jul 2014 9:19] Abhishek Ranjan
Bug#73183 was marked as a duplicate of this bug.