Comment 1 for bug 604439

Tianma,

This difference goes to Innodb plugin and what is called fast index
creation.

When you create index in standard Innodb the whole table is rebuilt hence
the duplicate rows can be ignored. The Innodb Plugin and XtraDB only build
the index
separately so it can't remove the row.

We'll need to add an option to be able to switch to old behavior of table
rebiuild - it is helpful
in cases like this and also in case there are some bugs with fast index
creation

On Sun, Jul 11, 2010 at 8:34 PM, tianma <email address hidden> wrote:

> Public bug reported:
>
> MySQL Version : MySQL -5.1.47+XtraDB-1.0.8
>
> Description: XtraDB can't create unique index with ignore keyword when
> there are duplicate values
>
>
> Storage Engine: XtraDB
>
> mysql> create table t1 (a int ,b varchar(10));
> Query OK, 0 rows affected (0.06 sec)
>
> mysql> insert into t1(a,b) values (1,'aaaa'),(2,'bbbb'),(1,'cccc');
> Query OK, 3 rows affected (0.00 sec)
> Records: 3 Duplicates: 0 Warnings: 0
>
> mysql> alter ignore table t1 add unique index idx_a(a);
> ERROR 1062 (23000): Duplicate entry '1' for key 'idx_a'
>
> Storage Engine: Innodb :
>
> mysql> create table t1 (a int ,b varchar(10));
> Query OK, 0 rows affected (0.06 sec)
>
> mysql> insert into t1(a,b) values (1,'aaaa'),(2,'bbbb'),(1,'cccc');
> Query OK, 3 rows affected (0.00 sec)
> Records: 3 Duplicates: 0 Warnings: 0
>
> mysql> alter ignore table t1 add unique index idx_a(a);
> Query OK, 3 rows affected (0.01 sec)
> Records: 3 Duplicates: 1 Warnings: 0
>
> mysql> select * from t1;
> +------+------+
> | a | b |
> +------+------+
> | 1 | aaaa |
> | 2 | bbbb |
> +------+------+
> 2 rows in set (0.00 sec)
>
> mysql> show create table t1;
>
> +-------+-------------------------------------------------------------------------------------------------------------------------------------------------+
> | Table | Create Table
>
> |
>
> +-------+-------------------------------------------------------------------------------------------------------------------------------------------------+
> | t1 | CREATE TABLE `t1` (
> `a` int(11) DEFAULT NULL,
> `b` varchar(10) DEFAULT NULL,
> UNIQUE KEY `idx_a` (`a`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
>
> +-------+-------------------------------------------------------------------------------------------------------------------------------------------------+
> 1 row in set (0.00 sec)
>
> ** Affects: percona-xtradb
> Importance: Undecided
> Status: New
>
> --
> XtraDB can't create unique index with ignore keyword when there are
> duplicate values
> https://bugs.launchpad.net/bugs/604439
> You received this bug notification because you are a member of Percona
> developers, which is the registrant for Percona-XtraDB.
>
> Status in Percona XtraDB Storage Engine for MySQL: New
>
> Bug description:
> MySQL Version : MySQL -5.1.47+XtraDB-1.0.8
>
> Description: XtraDB can't create unique index with ignore keyword when
> there are duplicate values
>
>
> Storage Engine: XtraDB
>
> mysql> create table t1 (a int ,b varchar(10));
> Query OK, 0 rows affected (0.06 sec)
>
> mysql> insert into t1(a,b) values (1,'aaaa'),(2,'bbbb'),(1,'cccc');
> Query OK, 3 rows affected (0.00 sec)
> Records: 3 Duplicates: 0 Warnings: 0
>
> mysql> alter ignore table t1 add unique index idx_a(a);
> ERROR 1062 (23000): Duplicate entry '1' for key 'idx_a'
>
> Storage Engine: Innodb :
>
> mysql> create table t1 (a int ,b varchar(10));
> Query OK, 0 rows affected (0.06 sec)
>
> mysql> insert into t1(a,b) values (1,'aaaa'),(2,'bbbb'),(1,'cccc');
> Query OK, 3 rows affected (0.00 sec)
> Records: 3 Duplicates: 0 Warnings: 0
>
> mysql> alter ignore table t1 add unique index idx_a(a);
> Query OK, 3 rows affected (0.01 sec)
> Records: 3 Duplicates: 1 Warnings: 0
>
> mysql> select * from t1;
> +------+------+
> | a | b |
> +------+------+
> | 1 | aaaa |
> | 2 | bbbb |
> +------+------+
> 2 rows in set (0.00 sec)
>
> mysql> show create table t1;
>
> +-------+-------------------------------------------------------------------------------------------------------------------------------------------------+
> | Table | Create Table
>
> |
>
> +-------+-------------------------------------------------------------------------------------------------------------------------------------------------+
> | t1 | CREATE TABLE `t1` (
> `a` int(11) DEFAULT NULL,
> `b` varchar(10) DEFAULT NULL,
> UNIQUE KEY `idx_a` (`a`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
>
> +-------+-------------------------------------------------------------------------------------------------------------------------------------------------+
> 1 row in set (0.00 sec)
>
>
>

--
Peter Zaitsev, CEO, Percona Inc.
Tel: +1 888 401 3401 ext 501 Skype: peter_zaitsev
24/7 Emergency Line +1 888 401 3401 ext 911

Percona Training Workshops
http://www.percona.com/training/