XtraDB can't create unique index with ignore keyword when there are duplicate values

[tianma]

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 (pz-percona)]

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,...

[tianma (tianma-1473)]

Thank you very much , I think it is necessary to choose by user according to application

[Stewart Smith (stewart)]

All development of XtraDB has moved under the Percona Server project - https://launchpad.net/percona-server - If this bug can be reproduced against current Percona Server, please file this bug against percona-server (you can simply do so by using the "Also affects project" link above).

Thanks,
Stewart Smith
Director of Server Development
Percona.