Bug #27997 Falcon: unique violation after select for update
Submitted: 21 Apr 2007 1:07 Modified: 30 Apr 2007 14:07
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Falcon storage engine Severity:S2 (Serious)
Version:6.0.0-alpha-debug OS:Linux (SUSE 10 64-bit)
Assigned to: CPU Architecture:Any

[21 Apr 2007 1:07] Peter Gulutzan
Description:
I create a Falcon table with two columns.
I create a unique index on both columns.
I insert two rows without violating uniqueness.
I select for update.
I update the rows to the same values.
Uniqueness is violated.

ChangeSet@1.2589, 2007-04-15

How to repeat:
mysql> create table t (s1 int, s2 int) engine=falcon;
Query OK, 0 rows affected (0.44 sec)

mysql> create unique index i1 on t (s1,s2);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t for update;
+------+------+
| s1   | s2   |
+------+------+
|    5 | NULL |
|    5 | NULL |
+------+------+
2 rows in set (0.00 sec)

mysql> update t set s2 = 2;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0
[21 Apr 2007 2:15] Peter Gulutzan
Whoops, forgot to emphasize: autocommit = 0.

The correct sequence is:

set @@autocommit=0;
create table t (s1 int, s2 int) engine=falcon;
create unique index i1 on t (s1,s2);
insert into t values (5,null),(5,null);
commit;
select * from t for update;
update t set s2 = 2;
[21 Apr 2007 14:53] Hakan Küçükyılmaz
Verified as described. Added test case falcon_bug_27997.test which fails:

TEST                           RESULT         TIME (ms)
-------------------------------------------------------

falcon_bug_27997               [ fail ]

Errors are (from /home/hakan/work/mysql/mysql-5.1-falcon/mysql-test/var/log/mysqltest-time) :
mysqltest: At line 27: query 'UPDATE t1 SET b = 2' succeeded - should have failed with errno 1582...
(the last lines may be the most important ones)
[24 Apr 2007 21:50] Jim Starkey
Problem was confusion with lock record when comparing record versions.
[24 Apr 2007 23:28] Hakan Küçükyılmaz
Still fails because of wrong error message.

ERROR 23000: Duplicate entry 'NULL-2' for key 'i1'

it should be ERROR 23000: Duplicate entry '5-2' for key 'i1'

Details:

TEST                           RESULT         TIME (ms)
-------------------------------------------------------

falcon_bug_27997               [ fail ]

ERROR: Bug#27997 2007-04-21 hakank Currently failing
Below are the diffs between actual and expected results:
-------------------------------------------------------
*** r/falcon_bug_27997.result   2007-04-21 17:55:13.000000000 +0300
--- r/falcon_bug_27997.reject   2007-04-25 02:25:58.000000000 +0300
***************
*** 11,17 ****
  5     NULL
  5     NULL
  UPDATE t1 SET b = 2;
! ERROR 23000: Duplicate entry '5-2' for key 'i1'
  SELECT count(*) FROM t1;
  count(*)
  2
--- 11,17 ----
  5     NULL
  5     NULL
  UPDATE t1 SET b = 2;
! ERROR 23000: Duplicate entry 'NULL-2' for key 'i1'
  SELECT count(*) FROM t1;
  count(*)
  2
-------------------------------------------------------
Please follow the instructions outlined at
http://www.mysql.com/doc/en/Reporting_mysqltest_bugs.html
to find the reason to this problem and how to report this.
[30 Apr 2007 6:18] Hakan Küçükyılmaz
This bug is fixed. Problem with wrong error message is new Bug#28158.

Best regards,

Hakan
[30 Apr 2007 14:07] MC Brown
A note has been added to the 6.0.0 changelog.