Issuing FLUSH commands on a node will create a local transaction on GTID-configured clusters

[Fernando Laudares Camargos]

On a GTID configured cluster:

mysql> show variables like '%gtid%';
+---------------------------------+-----------+
| Variable_name | Value |
+---------------------------------+-----------+
| enforce_gtid_consistency | ON |
| gtid_executed | |
| gtid_mode | ON |
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | |
| simplified_binlog_gtid_recovery | OFF |
+---------------------------------+-----------+

Where the "cluster UUID" is 00a389be-4d16-ee1b-5d1d-4844673417ef:

mysql> select @@global.gtid_executed\G
*************************** 1. row ***************************
@@global.gtid_executed: 00a389be-4d16-ee1b-5d1d-4844673417ef:1-12

And the server_uuid is:

+--------------------------------------+
| @@server_uuid |
+--------------------------------------+
| ff90853d-b2e9-11e4-b8bd-0800272e951c |
+--------------------------------------+

The issuing of "FLUSH PRIVILEGES" will create a local transaction, one that is registered under a GTID set having the node's server_uuid as source_id, as we usually have on regular MySQL GTID replication:

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.gtid_executed\G
*************************** 1. row ***************************
@@global.gtid_executed: 00a389be-4d16-ee1b-5d1d-4844673417ef:1-12,
ff90853d-b2e9-11e4-b8bd-0800272e951c:1

Here's some additional info on configuration and versions:

mysql> show variables like 'wsrep%myisam%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| wsrep_replicate_myisam | OFF |
+------------------------+-------+

mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+-------------------------+---------------------------------------------------------------------------------------------------+
| innodb_version | 5.6.21-70.1 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.6.21-70.1-56-log |
| version_comment | Percona XtraDB Cluster (GPL), Release rel70.1, Revision 938, WSREP version 25.8, wsrep_25.8.r4150 |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+---------------------------------------------------------------------------------------------------+

[Przemek (pmalkowski)]

I can confirm many "flush" commands are not replicated within Galera cluster, but written to the binary log with GTID of local

percona33 mysql> select @@version, @@version_comment;
+--------------------+---------------------------------------------------------------------------------------------------+
| @@version | @@version_comment |
+--------------------+---------------------------------------------------------------------------------------------------+
| 5.6.22-72.0-56-log | Percona XtraDB Cluster (GPL), Release rel72.0, Revision 964, WSREP version 25.8, wsrep_25.8.r4150 |
+--------------------+---------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

percona33 mysql> select @@server_uuid;
+--------------------------------------+
| @@server_uuid |
+--------------------------------------+
| 18febdb4-bb42-11e4-a3ff-080027079e3d |
+--------------------------------------+
1 row in set (0.00 sec)

percona33 mysql> show binlog events in 'percona33-bin.000002';
+----------------------+-----+----------------+-----------+-------------+-----------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+----------------------+-----+----------------+-----------+-------------+-----------------------------------------------+
| percona33-bin.000002 | 4 | Format_desc | 33 | 120 | Server ver: 5.6.22-72.0-56-log, Binlog ver: 4 |
| percona33-bin.000002 | 120 | Previous_gtids | 33 | 191 | bfa44ec0-55bd-ee1b-6916-25822fb94622:1-5 |
+----------------------+-----+----------------+-----------+-------------+-----------------------------------------------+
2 rows in set (0.00 sec)

percona33 mysql> insert into test.t1 values (null);
Query OK, 1 row affected (0.07 sec)

percona33 mysql> show binlog events in 'percona33-bin.000002';
+----------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+----------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| percona33-bin.000002 | 4 | Format_desc | 33 | 120 | Server ver: 5.6.22-72.0-56-log, Binlog ver: 4 |
| percona33-bin.000002 | 120 | Previous_gtids | 33 | 191 | bfa44ec0-55bd-ee1b-6916-25822fb94622:1-5 |
| percona33-bin.000002 | 191 | Gtid | 33 | 239 | SET @@SESSION.GTID_NEXT= 'bfa44ec0-55bd-ee1b-6916-25822fb94622:6' |
| percona33-bin.000002 | 239 | Query | 33 | 312 | BEGIN |
| percona33-bin.000002 | 312 | Table_map | 33 | 357 | table_id: 70 (test.t1) ...

[David Bennett (dbpercona)]

Related codership fix - https://github.com/codership/mysql-wsrep/issues/67

[David Bennett (dbpercona)]

Verified this is fixed in PXC rev981 using this script:
http://bazaar.launchpad.net/~percona-core/percona-qa/trunk/view/head:/pxc-replication-check.pl

[Nilnandan Joshi (nilnandan-joshi)]

It seems, this bug doesn't fixed. Still available in PXC 5.6.24

[root@percona-pxc56-1 mysql]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.6.24-72.2-56-log Percona XtraDB Cluster (GPL), Release rel72.2, Revision 1, WSREP version 25.11, wsrep_25.11

Copyright (c) 2009-2015 Percona LLC and/or its affiliates
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql> select @@version, @@version_comment;
+--------------------+---------------------------------------------------------------------------------------------+
| @@version | @@version_comment |
+--------------------+---------------------------------------------------------------------------------------------+
| 5.6.24-72.2-56-log | Percona XtraDB Cluster (GPL), Release rel72.2, Revision 1, WSREP version 25.11, wsrep_25.11 |
+--------------------+---------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select @@server_uuid;
+--------------------------------------+
| @@server_uuid |
+--------------------------------------+
| 0cf78f9a-f55d-11e4-ae8c-0800278a9f76 |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> select @@global.gtid_executed\G
*************************** 1. row ***************************
@@global.gtid_executed: 7dce9046-083b-ee1b-4bde-d5e28877f19f:1
1 row in set (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.gtid_executed\G
*************************** 1. row ***************************
@@global.gtid_executed: 7dce9046-083b-ee1b-4bde-d5e28877f19f:1-2
1 row in set (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.gtid_executed\G
*************************** 1. row ***************************
@@global.gtid_executed: 7dce9046-083b-ee1b-4bde-d5e28877f19f:1-3
1 row in set (0.00 sec)

mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.gtid_executed\G
*************************** 1. row ***************************
@@global.gtid_executed: 0cf78f9a-f55d-11e4-ae8c-0800278a9f76:1,
7dce9046-083b-ee1b-4bde-d5e28877f19f:1-3
1 row in set (0.00 sec)

mysql> select @@server_uuid;
+--------------------------------------+
| @@server_uuid |
+--------------------------------------+
| 0cf78f9a-f55d-11e4-ae8c-0800278a9f76 |
+--------------------------------------+
1 row in set (0.00 sec)

mysql>

[Shahriyar Rzayev (rzayev-sehriyar)]

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