SHOW GLOBAL STATUS hangs while a large transaction is certifying and/or applying

[Jay Janssen]

In this test of large transactions, myq_status will hang just after a large transaction is received:

https://gist.github.com/jayjanssen/4945584

This appears to be the SHOW GLOBAL STATUS that myq_status runs hanging on some lock. I don't see any reason why certification should block SHOW GLOBAL STATUS.

[Przemek (pmalkowski)]

The same problem is repeatable when global lock is set on a node with "flush tables with read lock" while wsrep_causal_reads is set.
Steps to reproduce:

node2>select @@wsrep_causal_reads;
+----------------------+
| @@wsrep_causal_reads |
+----------------------+
| 1 |
+----------------------+
1 row in set (0.01 sec)

node2>flush tables with read lock;
Query OK, 0 rows affected (0.01 sec)

-----------------
node3>insert into test.t1 values (null);
Query OK, 1 row affected (0.02 sec)
-----------------

node2>show status like 'Threads%';
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

node2>select @@version;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

node2>show variables like 'version';
+---------------+---------------+
| Variable_name | Value |
+---------------+---------------+
| version | 5.5.34-55-log |
+---------------+---------------+
1 row in set (0.00 sec)

node2>set wsrep_causal_reads=0;
Query OK, 0 rows affected (0.00 sec)

node2>select @@version;
+---------------+
| @@version |
+---------------+
| 5.5.34-55-log |
+---------------+
1 row in set (0.00 sec)

node2>show status like 'Threads%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 0 |
| Threads_connected | 34 |
| Threads_created | 42 |
| Threads_running | 1 |
+-------------------+-------+
4 rows in set (0.01 sec)

This is important as it breaks backups made with Percona Xtrabackup, as it does some SHOW STATUS queries after it sets FTWRL.
The example error from innobackupex script looks like this:
"DBD::mysql::db selectall_hashref failed: Lock wait timeout exceeded; try restarting transaction at /usr/bin/innobackupex line 1652.
innobackupex: Error:
Error executing 'SHOW STATUS': DBD::mysql::db selectall_hashref failed: Lock wait timeout exceeded; try restarting transaction at /usr/bin/innobackupex line 1652."

[Raghavendra D Prabhu (raghavendra-prabhu)]

@Przemek,

I am able to replicate this partially in that, it only blocks
select but not 'show status...'. This is with 5.6. With 5.5, it
still blocks 'show status...'.

However, it is not related to original issue here, so can you
please report it as a separate issue?

[Raghavendra D Prabhu (raghavendra-prabhu)]

Created separate issue https://bugs.launchpad.net/percona-xtradb-cluster/+bug/1271177 for #1 and #2.

[Raghavendra D Prabhu (raghavendra-prabhu)]

@Jay,

a) Is the hanging related in any way to wsrep_causal_reads or FTWRL? (I
don't see them mentioned in your gist).

b) Do you see the issue with 5.6 PXC as well? (This is because of
a larger transactions behaving differently with Galera 3).

[Alex Yurchenko (ayurchen)]

This seems to be a Galera issue and was resolved in 3.x

[Shahriyar Rzayev (rzayev-sehriyar)]

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