MySQL Bugs: #40344: ALTER IGNORE TABLE t ADD INDEX does not ignore in fast index creation

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	CPU Architecture:	Any

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.

Thank you for the bug report. Verified as described.

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?

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?

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.

Will revisit this bug when it is reproducible in main branches

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'

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'

workaround is to first run "set session old_alter_table=1;"

I have just encountered this bug still present on MySQL 5.5 using InnoDB plugin.
The workaround suggested in the previous comment worked.

same for me with MySQL 5.5.8 and InnoDB v 1.1.4

bug #60886 is a duplicate of this

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.

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)

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.

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.

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.

Bug #65731 was marked as duplicate of this one.

Bug #69465 was marked as duplicate of this one.

Bug#73183 was marked as a duplicate of this bug.