Bug #44836 DDL locking is not consistent

• Submitted: 12 May 23:38
• Modified: 13 May 9:25
• Reporter: Hakan Kuecuekyilmaz
• Status: Verified
• Category: Server: DDL
• Severity: S3 (Non-critical)
• Version: mysql-6.0-falcon
• OS: Any
• Assigned to: Dmitri Lenev
• Triage: Triaged: D2 (Serious)

[12 May 23:38] Hakan Kuecuekyilmaz

Description:
DDL locking is not consistent when tested on a fresh install compared to later
operations.

How to repeat:
-- Connection 1 with a complete fresh installation
DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a int PRIMARY KEY, b varchar(5)) Engine Falcon;
-- In another session try with InnoDB
-- CREATE TABLE t1 (a int PRIMARY KEY, b varchar(5)) Engine InnoDB;
INSERT INTO t1 VALUES (1, 'aaaaa'), (2, 'bbbbb'), (3, 'ccccc');

SET @@autocommit = 0;
SELECT * FROM t1;

-- Connection 2
SET @@autocommit = 0;
-- this blocks
ALTER TABLE t1 DROP COLUMN b;

-- Connection 1
-- this happens with Falcon and InnoDB
-- ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting
transaction
SELECT * FROM t1;

--
-- COMMIT on both connections and start the test again
-- Connection 1
DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a int PRIMARY KEY, b varchar(5)) Engine Falcon;
INSERT INTO t1 VALUES (1, 'aaaaa'), (2, 'bbbbb'), (3, 'ccccc');

SET @@autocommit = 0;
SELECT * FROM t1;

-- Connection 2
SET @@autocommit = 0;
-- Falcon get's ERROR 1015 (HY000): Can't lock file (errno: 212)
-- InnoDB it blocks here
ALTER TABLE t1 DROP COLUMN b;

-- Connection 1
-- works as expected
SELECT * FROM t1;
COMMIT;

-- Connection 2
SET @@autocommit = 0;
-- works
ALTER TABLE t1 DROP COLUMN b;

-- Connection 1
-- works as expected
SELECT * FROM t1;

[12 May 23:56] Hakan Kuecuekyilmaz

Happens in mysql-6.0-falcon tree. Most likely also in mysql-6.0 tree.

[13 May 9:25] Sveta Smirnova

Thank you for the report.

Verified as described using mysql-6.0-falcon tree.

[13 May 9:30] Sveta Smirnova

With mysql-6.0 results almost as described, although

----<Q>----
-- Connection 2
SET @@autocommit = 0;
-- Falcon get's ERROR 1015 (HY000): Can't lock file (errno: 212)
-- InnoDB it blocks here
ALTER TABLE t1 DROP COLUMN b;
----</Q>----

doesn't block in case of InnoDB