TOI ALTER TABLE blocks all DML's for duration of ALTER TABLE in separate databases
Affects | Status | Importance | Assigned to | Milestone | ||
---|---|---|---|---|---|---|
Galera |
Confirmed
|
Wishlist
|
Alex Yurchenko | |||
MySQL patches by Codership |
Confirmed
|
Wishlist
|
Unassigned | |||
Percona XtraDB Cluster moved to https://jira.percona.com/projects/PXC | Status tracked in 5.6 | |||||
5.5 |
Confirmed
|
Wishlist
|
Unassigned | |||
5.6 |
Confirmed
|
Wishlist
|
Unassigned |
Bug Description
Hi,
Using Percona XtraDB Cluster with Galera - 5.5.17-22.1.log and am seeing long-running ALTER TABLE statements in Node1-Database A block all writes on Node2-Database B using wsrep_OSU_
--Node 1 - Database "n126db1" - table "t1"
CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`hostname` varchar(64) NOT NULL,
`port` int(11) NOT NULL,
`instime` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=
--Node 2 - Database "n127db2" - table "t2"
CREATE TABLE `t2` (
`c1` int(11) NOT NULL,
`c2` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Node 1 (n126db1) - ALTER TABLE t1 add column c5 int;
Node 2 (n127db2) - insert into t2 values (1,2);
mysql> show processlist;
+------
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined | Rows_read |
+------
| 1 | event_scheduler | localhost | NULL | Daemon | 64607 | Waiting on empty queue | NULL | 0 | 0 | 1 |
| 2 | system user | | NULL | Sleep | 64606 | wsrep aborter idle | NULL | 0 | 0 | 1 |
| 3 | system user | | n126db1 | Sleep | 44 | copy to tmp table | alter table t1 add column c5 int | 0 | 0 | 4066970 |
| 4 | system user | | NULL | Sleep | 566 | committed 3569220 | NULL | 0 | 0 | 1 |
| 5 | system user | | NULL | Sleep | 566 | committed 3569199 | NULL | 0 | 0 | 1 |
| 6 | system user | | NULL | Sleep | 566 | committed 3569222 | NULL | 0 | 0 | 1 |
| 7 | system user | | NULL | Sleep | 566 | committed 3569215 | NULL | 0 | 0 | 1 |
| 8 | system user | | NULL | Sleep | 566 | committed 3569219 | NULL | 0 | 0 | 1 |
| 9 | system user | | NULL | Sleep | 566 | committed 3569211 | NULL | 0 | 0 | 1 |
| 10 | system user | | NULL | Sleep | 566 | committed 3569223 | NULL | 0 | 0 | 1 |
| 4329 | root | localhost | n127db2 | Query | 42 | query end | insert into t2 values (1,2) | 0 | 0 | 2 |
| 4339 | root | localhost | NULL | Query | 0 | sleeping | show processlist | 0 | 0 | 1 |
+------
12 rows in set (0.00 sec)
The documentation at http://
Contrast this behavior when the test is run on a single-RSU node. The ALTER TABLE takes just as long - but inserts into the other database are not blocked.
Thanks
Patrick
no longer affects: | codership-mysql |
Changed in galera: | |
milestone: | none → 4.0 |
status: | Won't Fix → Confirmed |
Default setting of replicator. commit_ order (IN-ORDER) prevents out-of-order committing of changes to the database, hence connection hanging in "query end" state: DML was applied, but commit postponed till the end of DDL.
Possible actions:
1) Leave as it is and fix documentation
2) Disable commit order control for TOI actions
The latter is probably easier.