Bug #88720 Inconsistent and unsafe FLUSH behavior in terms of replication
Submitted: 30 Nov 2017 14:52 Modified: 1 Dec 2017 7:11
Reporter: Przemyslaw Malkowski Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.6, 5.7, 8.0.3, 5.6.38, 5.7.20 OS:Any
Assigned to: CPU Architecture:Any
Tags: flush

[30 Nov 2017 14:52] Przemyslaw Malkowski
Description:
Many of the FLUSH commands are bin-logged (but not all), and if GTID mode enabled, adding GTID sequence with local UUID.

These commands also do not respect super_read_only=1.

An example ones affected:
FLUSH SLOW LOGS
FLUSH HOSTS
FLUSH STATUS
FLUSH PRIVILEGES
FLUSH USER_RESOURCES
etc.

Not affected:
FLUSH LOGS
FLUSH ENGINE LOGS
FLUSH BINARY LOGS

This may cause replication problems later as the cluster becomes inconsistent.
Also tested on 8.0.3-rc

Somewhat related bug: https://bugs.mysql.com/bug.php?id=83232

How to repeat:
slave1 [localhost] {msandbox} ((none)) > select @@super_read_only\G
*************************** 1. row ***************************
@@super_read_only: 1
1 row in set (0.00 sec)

slave1 [localhost] {msandbox} ((none)) > show global variables like 'gtid_executed'\G
*************************** 1. row ***************************
Variable_name: gtid_executed
        Value: 00012411-1111-1111-1111-111111111111:1-3
1 row in set (0.00 sec)

slave1 [localhost] {msandbox} ((none)) > flush logs;
Query OK, 0 rows affected (0.08 sec)

slave1 [localhost] {msandbox} ((none)) > show global variables like 'gtid_executed'\G
*************************** 1. row ***************************
Variable_name: gtid_executed
        Value: 00012411-1111-1111-1111-111111111111:1-3
1 row in set (0.00 sec)

slave1 [localhost] {msandbox} ((none)) > flush slow logs;
Query OK, 0 rows affected (0.00 sec)

etc

slave1 [localhost] {msandbox} ((none)) > show global variables like 'gtid_executed'\G                                                                                                                                                        *************************** 1. row ***************************
Variable_name: gtid_executed
        Value: 00012411-1111-1111-1111-111111111111:1-3,
00012412-2222-2222-2222-222222222222:1-6
1 row in set (0.00 sec)

slave1 [localhost] {msandbox} ((none)) > show binlog events in 'mysql-bin.000002';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                              |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000002 |   4 | Format_desc    |       101 |         124 | Server ver: 8.0.3-rc-log, Binlog ver: 4                           |
| mysql-bin.000002 | 124 | Previous_gtids |       101 |         195 | 00012411-1111-1111-1111-111111111111:1-3                          |
| mysql-bin.000002 | 195 | Gtid           |       101 |         268 | SET @@SESSION.GTID_NEXT= '00012412-2222-2222-2222-222222222222:1' |
| mysql-bin.000002 | 268 | Query          |       101 |         346 | flush slow logs                                                   |
| mysql-bin.000002 | 346 | Gtid           |       101 |         419 | SET @@SESSION.GTID_NEXT= '00012412-2222-2222-2222-222222222222:2' |
| mysql-bin.000002 | 419 | Query          |       101 |         502 | flush user_resources                                              |
| mysql-bin.000002 | 502 | Gtid           |       101 |         575 | SET @@SESSION.GTID_NEXT= '00012412-2222-2222-2222-222222222222:3' |
| mysql-bin.000002 | 575 | Query          |       101 |         649 | flush hosts                                                       |
| mysql-bin.000002 | 649 | Gtid           |       101 |         722 | SET @@SESSION.GTID_NEXT= '00012412-2222-2222-2222-222222222222:4' |
| mysql-bin.000002 | 722 | Query          |       101 |         797 | flush status                                                      |
| mysql-bin.000002 | 797 | Gtid           |       101 |         870 | SET @@SESSION.GTID_NEXT= '00012412-2222-2222-2222-222222222222:5' |
| mysql-bin.000002 | 870 | Query          |       101 |         957 | flush privileges                                                  |
| mysql-bin.000002 | 957 | Rotate         |       101 |        1004 | mysql-bin.000003;pos=4                                            |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
13 rows in set (0.00 sec)

Suggested fix:
I don't see much sense in actually replicating these FLUSH commands at all, especially that most of them are related to local resources, which can be different on the slave side.
 
If though any of these has to be replicated/bin-logged, it should respect the suer_read_only setting.
[30 Nov 2017 20:20] Sveta Smirnova
test case for MTR

Attachment: rpl_bug88720.test (application/octet-stream, text), 441 bytes.

[30 Nov 2017 20:20] Sveta Smirnova
Option file for master, copy same file for slave

Attachment: rpl_bug88720-master.opt (application/octet-stream, text), 72 bytes.

[1 Dec 2017 7:11] Umesh Shastry
Hello Przemyslaw,Sveta,

Thank you for the report and test case.

Thanks,
Umesh