Percona XtraDB Cluster moved to https://jira.percona.com/projects/PXC

Bug #1421360
Comment #1

Comment 1 for bug 1421360

Revision history for this message

Przemek (pmalkowski) wrote on 2015-02-23: Re: Issuing FLUS PRIVILEGES on a node will create a local transaction on GTID-configured clusters

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> insert into test.t1 values (null);
Query OK, 1 row affected (0.07 sec)

percona33 mysql> select @@global.gtid_executed\G
*************************** 1. row ***************************
@@global.gtid_executed: bfa44ec0-55bd-ee1b-6916-25822fb94622:1-6
1 row in set (0.00 sec)

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

percona33 mysql> select @@global.gtid_executed\G
*************************** 1. row ***************************
@@global.gtid_executed: 18febdb4-bb42-11e4-a3ff-080027079e3d:1,
bfa44ec0-55bd-ee1b-6916-25822fb94622:1-6
1 row in set (0.00 sec)

and also other FLUSH commands:

percona33 mysql> show binlog events +-------------------- | Log_name | Pos | Event_type +-------------------- | percona33-bin.000002 | 4 | Format_desc | percona33-bin.000002 | 120 | Previous_gtids | | percona33-bin.000002 | 191 | Gtid | percona33-bin.000002 | 239 | Query | percona33-bin.000002 | 312 | Table_map | percona33-bin.000002 | 357 | Write_rows | percona33-bin.000002 | 397 | Xid | percona33-bin.000002 | 428 | Gtid | percona33-bin.000002 | 476 | Query | percona33-bin.000002 | 560 | Gtid | percona33-bin.000002 | 608 | Query | percona33-bin.000002 | 687 | Gtid | percona33-bin.000002 | 735 | Query | percona33-bin.000002 | 815 | Gtid | percona33-bin.000002 | 863 | Query | percona33-bin.000002 | 943 | Gtid | percona33-bin.000002 | 991 | Query +--------------------bin.000002';
/>--+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Server_id | End_log_pos | Info |
/>--+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| 33 | 120 | Server ver: 5.6.22-72.0-56-log, Binlog ver: 4 |
33 | 191 | bfa44ec0-55bd-ee1b-6916-25822fb94622:1-5 |
| 33 | 239 | SET @@SESSION.GTID_NEXT= 'bfa44ec0-55bd-ee1b-6916-25822fb94622:6' |
| 33 | 312 | BEGIN |
| 33 | 357 | table_id: 70 (test.t1) |
| 33 | 397 | table_id: 70 flags: STMT_END_F |
| 33 | 428 | COMMIT /* xid=3348 */ |
| 33 | 476 | SET @@SESSION.GTID_NEXT= '18febdb4-bb42-11e4-a3ff-080027079e3d:1' |
| 33 | 560 | flush privileges |
| 33 | 608 | SET @@SESSION.GTID_NEXT= '18febdb4-bb42-11e4-a3ff-080027079e3d:2' |
| 33 | 687 | flush hosts |
| 33 | 735 | SET @@SESSION.GTID_NEXT= '18febdb4-bb42-11e4-a3ff-080027079e3d:3' |
| 33 | 815 | flush status |
| 33 | 863 | SET @@SESSION.GTID_NEXT= '18febdb4-bb42-11e4-a3ff-080027079e3d:4' |
| 33 | 943 | flush tables |
| 33 | 991 | SET @@SESSION.GTID_NEXT= '18febdb4-bb42-11e4-a3ff-080027079e3d:5' |
| 33 | 1079 | flush USER_RESOURCES |
/>--+-----+----------------+-----------+-------------+-------------------------------------------------------------------+

percona33 mysql> select @@global.gtid_executed\G
*************************** 1. row ***************************
@@global.gtid_executed: 18febdb4-bb42-11e4-a3ff-080027079e3d:1-5,
bfa44ec0-55bd-ee1b-6916-25822fb94622:1-6
1 row in set (0.00 sec)

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> insert into test.t1 values (null);
Query OK, 1 row affected (0.07 sec)

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

and also other FLUSH commands:

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)                                            |
| percona33-bin.000002 | 357 | Write_rows     |        33 |         397 | table_id: 70 flags: STMT_END_F                                    |
| percona33-bin.000002 | 397 | Xid            |        33 |         428 | COMMIT /* xid=3348 */                                             |
| percona33-bin.000002 | 428 | Gtid           |        33 |         476 | SET @@SESSION.GTID_NEXT= '18febdb4-bb42-11e4-a3ff-080027079e3d:1' |
| percona33-bin.000002 | 476 | Query          |        33 |         560 | flush privileges                                                  |
| percona33-bin.000002 | 560 | Gtid           |        33 |         608 | SET @@SESSION.GTID_NEXT= '18febdb4-bb42-11e4-a3ff-080027079e3d:2' |
| percona33-bin.000002 | 608 | Query          |        33 |         687 | flush hosts                                                       |
| percona33-bin.000002 | 687 | Gtid           |        33 |         735 | SET @@SESSION.GTID_NEXT= '18febdb4-bb42-11e4-a3ff-080027079e3d:3' |
| percona33-bin.000002 | 735 | Query          |        33 |         815 | flush status                                                      |
| percona33-bin.000002 | 815 | Gtid           |        33 |         863 | SET @@SESSION.GTID_NEXT= '18febdb4-bb42-11e4-a3ff-080027079e3d:4' |
| percona33-bin.000002 | 863 | Query          |        33 |         943 | flush tables                                                      |
| percona33-bin.000002 | 943 | Gtid           |        33 |         991 | SET @@SESSION.GTID_NEXT= '18febdb4-bb42-11e4-a3ff-080027079e3d:5' |
| percona33-bin.000002 | 991 | Query          |        33 |        1079 | flush USER_RESOURCES                                              |
+----------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
17 rows in set (0.00 sec)

percona33 mysql> select @@global.gtid_executed\G
*************************** 1. row ***************************
@@global.gtid_executed: 18febdb4-bb42-11e4-a3ff-080027079e3d:1-5,
bfa44ec0-55bd-ee1b-6916-25822fb94622:1-6
1 row in set (0.00 sec)