MySQL Bugs: #28006: Falcon: isolation failed after alter

Bug #28006	Falcon: isolation failed after alter
Submitted:	22 Apr 2007 1:09	Modified:	20 May 18:05
Reporter:	Peter Gulutzan
Status:	QA testing
Category:	Server: Falcon	Severity:	S3 (Non-critical)
Version:	6.0.0-alpha-debug	OS:	Linux (SUSE 10 64-bit)
Assigned to:	John H. Embretsen	Target Version:	6.0-beta
Tags:	F_ISOLATION
Triage:	Triaged: D2 (Serious)

Description:
I create a Falcon table.
I insert a row.
I start a REPEATABLE READ transaction.
I select from the row.
On another connection, I alter the table.
On the original connection, I select from the table.
The result differs. Isolation failed.

There is similarity to Bug#22183, which was
closed after the comment "Falcon now gives
an error when attempting an "alter table"
(or variation) with outstanding transactions."

I could get comparable effects with DROP or RENAME.

How to repeat:
There are two mysql clients. Call them T1 and T2.

On T1, say:
set @@autocommit=0;
create table t1 (s1 int) engine=falcon;
insert into t1 values (1);
commit;
set transaction isolation level repeatable read;
start transaction;
select * from t1; /* result is 's1','1' */

On T2, say:
alter table t1 add column s2 int;
alter table t1 drop column s1;

On T1, say:
select * from t1; /* result is not 's1','1' */

Thank you for the bug report. Verified as described.

Setting to Won't fix as this is a general problem. See also Bug#15491.

Added test case falcon_bug_28006.test and pushed to 5.1-falcon tree.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/32867

ChangeSet@1.2685, 2007-08-22 12:16:02+02:00, hakank@lu0011.wdf.sap.corp +3 -0
  Added test for Bug #28006.

Attached Falcon test case for this issue. Will fail (result length mismatch) until this issue is fixed.

Attachment: falcon_bug_28006.test (application/octet-stream, text), 1.73 KiB.

Attached test result file corresponding to falcon_bug_28006.test. Test to be removed from source trees until issue is fixed.

Attachment: falcon_bug_28006.result (application/octet-stream, text), 661 bytes.

Similar to Bug#40460.

Assign to Kevin Lewis.  This bug should be fixed by WL#4284.  We will keep it open until
that can be tested.  See also Bug#40460

The alter table t1 is now blocked, but not consistently in all cases.
For details please see
    Bug#44836 DDL locking is not consistent

This needs to be tested again after Bug#44836 is fixed by the Server team.  Hakan did not
want to close the bug because of that inconsistency.

Here are the results of Hakan's testing;

               Falcon       InnoDB

DROP TABLE     blocked      blocked
TRUNCATE       ERROR 1015   works without block
DROP COLUMN    ERROR 1015   blocked
DROP INDEX     ERROR 1015   blocked
ADD COLUMN     ERROR 1015   blocked
ADD INDEX      ERROR 1015   blocked

Legend:  ERROR 1015 (HY000): Can't lock file (errno: 212)

DROP TABLE:  DROP TABLE t1;
TRUNCATE:    TRUNCATE t1;
DROP COLUMN: ALTER TABLE t1 DROP COLUMN b;
DROP INDEX:  ALTER TABLE t1 DROP PRIMARY KEY;
ADD COLUMN:  ALTER TABLE t1 ADD COLUMN c varchar(5);
ADD INDEX:   ALTER TABLE t1 ADD INDEX (b);

Details:
DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a int PRIMARY KEY, b varchar(5)) Engine Falcon;
-- In another session with Falcon
CREATE TABLE t1 (a int PRIMARY KEY, b varchar(5)) Engine InnoDB;

INSERT INTO t1 VALUES (1, 'aaaaa'), (2, 'bbbbb'), (3, 'ccccc');

-- Connection 1
SET @@autocommit = 0;
SELECT * FROM t1;

-- Connection 2
SET @@autocommit = 0;
-- Theses DML operations were tested in different sessions
DROP TABLE t1;
TRUNCATE t1;
ALTER TABLE t1 DROP COLUMN b;
ALTER TABLE t1 DROP PRIMARY KEY;
ALTER TABLE t1 ADD COLUMN c varchar(5);
ALTER TABLE t1 ADD INDEX (b);

-- CONNECTION 1
SELECT * FROM t1;
SELECT * FROM t1;
COMMIT;
SELECT * FROM t1;

> For Falcon strange things are happening. In my last email I got
> > ERROR 1015 (HY000): Can't lock file (errno: 212)
> > 
> > This time I get:
> > [22:33] root@test>SELECT * FROM t1;
> > ERROR 1213 (40001): Deadlock found when trying to get lock; try
> > restarting transaction
> > 
> > This means, that I have to do some further testing ...

The strange behavior is now documented as
http://bugs.mysql.com/bug.php?id=44836 DDL locking is not consistent