DDL blocked DML operations of other databases

[marsxu]

Percona XtraDB Cluster (GPL) 5.5.31-23.7.5, Revision 438, wsrep_23.7.5.r3880

use db1

CREATE TABLE `c_uid` (
  `id` bigint(24) NOT NULL AUTO_INCREMENT,
  `keycode` varchar(100) COLLATE utf8_bin DEFAULT NULL,
  `uid` varchar(100) COLLATE utf8_bin DEFAULT NULL,
  `poid` int(11) DEFAULT '1',
  `createtime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c_uid_index_1` (`keycode`),
  KEY `c_uid_index_2` (`uid`)
) ENGINE=InnoDB

 ........1000000 rows insert into c_uid .

use db2

CREATE TABLE `uid` (
  `id` bigint(24) NOT NULL AUTO_INCREMENT,
  `keycode` varchar(100) COLLATE utf8_bin DEFAULT NULL,
  `uid` varchar(100) COLLATE utf8_bin DEFAULT NULL,
  `poid` int(11) DEFAULT '1',
  `createtime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c_uid_index_1` (`keycode`),
  KEY `c_uid_index_2` (`uid`)
) ENGINE=InnoDB

use db1
alter table c_uid add col_test int ;
Query OK, 1000000 rows affected (29.41 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
++++++++ At the same time other session on same node +++++++++
insert into uid(keycode,uid,poid,createtime) values("testCode","user111",111,now());
Query OK, 1 row affected (28.51 sec)

++++++++ In the process of blocking state +++++++++
| 49522 | root | localhost | videodb | Query | 12 | copy to tmp table | alter table c_uid add col_test int | 0 | 0 | 0 |
| 49539 | root | localhost | testdb | Query | 10 | wsrep in pre-commit stage |insert into uid(keycode,uid,poid,createtime) .... | 0 | 1 | 1 |

db1 DDL operations blocked the db2 DML oprations .

[Raghavendra D Prabhu (raghavendra-prabhu)]

A duplicate of https://bugs.launchpad.net/galera/+bug/928919 reported earlier.

But, it looks like a local TOI can block all actions, which also includes DML.

Stacktrace shows it:

Thread 4 (Thread 0x7fdfc02c1700 (LWP 43744)):
#0 0x00000000009da906 in rec_offs_get_n_alloc (offsets=0x7fdf9c015118) at /media/Tintin/Work/code/percona-xtradb-cluster/trunk-25/Percona-Server/storage/innobase/include/rem0rec.ic:909
#1 rec_offs_n_fields (offsets=0x7fdf9c015118) at /media/Tintin/Work/code/percona-xtradb-cluster/trunk-25/Percona-Server/storage/innobase/include/rem0rec.ic:944
#2 rec_offs_validate (offsets=0x7fdf9c015118, index=0x0, rec=0x0) at /media/Tintin/Work/code/percona-xtradb-cluster/trunk-25/Percona-Server/storage/innobase/include/rem0rec.ic:961
#3 rec_offs_extra_size (offsets=0x7fdf9c015118) at /media/Tintin/Work/code/percona-xtradb-cluster/trunk-25/Percona-Server/storage/innobase/include/rem0rec.ic:1486
#4 0x00000000009df0ca in page_cur_insert_rec_low (current_rec=0x7fdfb06f6b0b "", index=index@entry=0x7fdf9c01dd58, rec=rec@entry=0x7fdf9c01510d "", offsets=0x7fdf9c015118, mtr=mtr@entry=0x7fdfc02bc950)
    at /media/Tintin/Work/code/percona-xtradb-cluster/trunk-25/Percona-Server/storage/innobase/page/page0cur.c:984
#5 0x00000000008f3746 in page_cur_tuple_insert (cursor=cursor@entry=0x7fdfc02bce48, tuple=tuple@entry=0x7fdf9c01dbc8, index=index@entry=0x7fdf9c01dd58, n_ext=0, mtr=mtr@entry=0x7fdfc02bc950)
    at /media/Tintin/Work/code/percona-xtradb-cluster/trunk-25/Percona-Server/storage/innobase/include/page0cur.ic:267
#6 0x00000000008fa59e in btr_cur_optimistic_insert (flags=flags@entry=0, cursor=cursor@entry=0x7fdfc02bce40, entry=entry@entry=0x7fdf9c01dbc8, rec=rec@entry=0x7fdfc02bce38, big_rec=big_rec@entry=0x7fdfc02bce30,
    n_ext=n_ext@entry=0, thr=thr@entry=0x7fdf9c01ee58, mtr=mtr@entry=0x7fdfc02bc950) at /media/Tintin/Work/code/percona-xtradb-cluster/trunk-25/Percona-Server/storage/innobase/btr/btr0cur.c:1443
#7 0x0000000000a0f014 in row_ins_index_entry_low (mode=mode@entry=2, index=index@entry=0x7fdf9c01dd58, entry=entry@entry=0x7fdf9c01dbc8, n_ext=n_ext@entry=0, thr=thr@entry=0x7fdf9c01ee58)
    at /media/Tintin/Work/code/percona-xtradb-cluster/trunk-25/Percona-Server/storage/innobase/row/row0ins.c:2241
#8 0x0000000000a131c0 in row_ins_index_entry (index=0x7fdf9c01dd58, entry=<optimized out>, n_ext=n_ext@entry=0, foreign=foreign@entry=1, thr=thr@entry=0x7fdf9c01ee58)
    at /media/Tintin/Work/code/percona-xtradb-cluster/trunk-25/Percona-Server/storage/innobase/row/row0ins.c:2353
#9 0x0000000000a13dc0 in row_ins_index_entry_step (thr=0x7fdf9c01ee58, node=0x7fdf9c01e9b0) at /media/Tintin/Work/code/percona-xtradb-cluster/trunk-25/Percona-Server/storage/innobase/row/row0ins.c:2441
#10 row_ins (thr=0x7fdf9c01ee58, node=0x7fdf9c01e9b0) at /media/Tintin/Work/code/percona-xtradb-cluster/trunk-25/Percona-Server/storage/innobase/row/row0ins.c:2573
#11 row_ins_step (thr=thr@entry=0x7fdf9c01ee58) at /media/Tintin/Work/code/percona-xtradb-cluster/trunk-25/Percona-Server/storage/innobase/row/row0ins.c:2690
#12 0x000000000086beb6 in row_insert_for_mysql (mysql_rec=<optimized out>, prebuilt=0x7fdf9c01e488) at /media/Tintin/Work/code/perco...

[joe chao (pingzhong-zhao)]

I meet a problem that DDL blocks DML operations of the same database.

[Nilnandan Joshi (nilnandan-joshi)]

Able to reproduce the same with PXC 5.5.39

On Test DB:
mysql> 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 DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.07 sec)

On Test2 DB:

mysql> use test2
Database changed
mysql> CREATE TABLE `t2` (
    -> `c1` int(11) NOT NULL,
    -> `c2` int(11) NOT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.05 sec)

while running "ALTER TABLE t1 add column c7 int;" on Node1 (test db) and "insert into t2 values (1,2)" on node2 (test2 db),
I found that ALTER on test blocked DML on test2.

mysql> show processlist;
+----+-------------+-----------+-------+---------+-------+---------------------------+----------------------------------+-----------+---------------+-----------+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined | Rows_read |
+----+-------------+-----------+-------+---------+-------+---------------------------+----------------------------------+-----------+---------------+-----------+
| 1 | system user | | test | Sleep | -4792 | copy to tmp table | ALTER TABLE t1 add column c7 int | 0 | 0 | 0 |
| 2 | system user | | NULL | Sleep | 33751 | wsrep aborter idle | NULL | 0 | 0 | 0 |
| 22 | root | localhost | test2 | Query | 14 | wsrep in pre-commit stage | insert into t2 values (1,2) | 0 | 0 | 0 |
| 23 | root | localhost | NULL | Query | 0 | sleeping | show processlist | 0 | 0 | 0 |
+----+-------------+-----------+-------+---------+-------+---------------------------+----------------------------------+-----------+---------------+-----------+
4 rows in set (0.00 sec)

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PXC-985