FLUSH TABLE WITH READ LOCK, FLUSH FOR EXPORT cause TOI to break
Affects | Status | Importance | Assigned to | Milestone | ||
---|---|---|---|---|---|---|
MySQL patches by Codership | Status tracked in 5.6 | |||||
5.6 |
Confirmed
|
Undecided
|
Unassigned | |||
Percona XtraDB Cluster moved to https://jira.percona.com/projects/PXC | Status tracked in 5.6 | |||||
5.5 |
New
|
Undecided
|
Raghavendra D Prabhu | |||
5.6 |
Fix Released
|
Undecided
|
Raghavendra D Prabhu |
Bug Description
When running a TOI DDL, where one of the nodes has the table locked, the DDL fails on that node with
2014-10-02 17:01:00 34030 [Warning] Slave SQL: Error 'Lock wait timeout exceeded; try restarting transaction' on query. Default database: 'test'. Query: 'ALTER TABLE t1 ADD COLUMN f2 INTEGER', Error_code: 1205
Instead, the DDL should block until the lock has been unlocked and proceed.
This is especially important in light of the FLUSH FOR EXPORT, which is supposed to be used to take backups of InnoDB tables. If a DDL arrives on a node while such a backup is being taken, that DDL will not be applied on that node.
Test case:
--source include/
--source include/
#
# Ensure that FLUSH TABLES ... FOR EXPORT is observed. No table changes are permitted
#
CREATE TABLE t1 (id INT PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1);
--connection node_2
FLUSH TABLES t1 WITH READ LOCK;
--connection node_1
ALTER TABLE t1 ADD COLUMN f2 INTEGER;
--connection node_2
UNLOCK TABLES;
# New column is not here
SHOW CREATE TABLE t1;
tags: | added: i53088 |
FLUSH TABLES FOR EXPORT is new in 5.6.6. FLUSH TABLE WITH READ LOCK has been there since forever.