MySQL Bugs: #22842: Falcon: unique-index creation quietly removes a row

Bug #22842	Falcon: unique-index creation quietly removes a row
Submitted:	29 Sep 2006 20:55	Modified:	3 Dec 2007 14:25
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S2 (Serious)
Version:	5.1.12-beta-debug	OS:	Linux (SUSE 10.0 / 64-bit)
Assigned to:	Kevin Lewis	CPU Architecture:	Any

Description:
I have two concurrent transactions on different connections. Call them T1 and T2.
On T1: I insert a value.
On T1: I insert a value which would be duplicate if there were a unique index.
On T1: I say CREATE UNIQUE INDEX
On T1: I commit
On T2: I commit
Now there is only one row in the table.
But I inserted two rows.
And there was never an error message.

How to repeat:
On T1, say:
set @@autocommit=0;
create table t8 (s1 int, s2 int) engine=falcon;
start transaction;
insert into t8 values (1,1);

On T2, say:
set @@autocommit=0;
start transaction;
insert into t8 values (1,2);

On T1, say:
create unique index i on t8 (s1);
commit;

On T2, say:
commit;

On T1, say:
select * from t8;

The final SELECT looks like this:

mysql> select * from t8;
+------+------+
| s1   | s2   |
+------+------+
|    1 |    1 |
+------+------+
1 row in set (0.00 sec)

Verified on Linux 32-bit, change set 1.2364, 2006-10-03

[08:08] root@test>commit;
Query OK, 0 rows affected (0.00 sec)

[08:08] root@test>select * from t8;
+------+------+
| s1   | s2   |
+------+------+
|    1 |    1 |
+------+------+
1 row in set (0.01 sec)

Regards, Hakan

This will be fixed with on-line add index

This test now get's an error code when the create index is attempted since there are pending transactions at the time.

Test case missing.

Testcase added.

Fixed in 6.0.4

A note has been added to the 6.0.4 changelog: 

Using CREATE UNIQUE INDEX on a Falcon table where rows contain duplicate values could result in pending transactions to the table being deleted