MySQL Bugs: #83232: replication breaks after bug #74145 happens in master

Bug #83232	replication breaks after bug #74145 happens in master
Submitted:	1 Oct 2016 6:32	Modified:	2 Aug 2018 11:49
Reporter:	Jericho Rivera	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.6, 5.6.33	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
On a master-slave replication environment, following the steps to reproduce https://bugs.mysql.com/bug.php?id=74145 using FLUSH SLOW LOGS and not FLUSH LOGS. Slave replication breaks with:

Last_SQL_Errno: 0
Last_SQL_Error: Query caused different errors on master and slave.     Error on master: message (format)='Invalid error code' error code=29 ; Error on slave: actual message='no error', error code=0. Default database: ''. Query: 'flush slow logs'

How to repeat:
Setup master-slave replication servers.

Do how-to-repeat steps in bug #74145 but use FLUSH SLOW LOGS instead of FLUSH LOGS.
[root@c7a mysql-5.6.32-linux-glibc2.5-x86_64]# chmod 000 data/c7a-slow.log
mysql> flush slow logs;
ERROR 29 (HY000): File './c7a-slow.log' not found (Errcode: 13 - Permission denied)

Check replication slave status.
Last_SQL_Error: Query caused different errors on master and slave.     Error on master: message (format)='Invalid error code' error code=29 ; Error on slave: actual message='no error', error code=0. Default database: ''. Query: 'flush slow logs'

Suggested fix:
Same as FLUSH LOGS, avoid writing FLUSH SLOW LOGS to the binary log.

Hello Jericho,

Thank you for the report.
Verified as described with 5.6.33 build.

Thanks,
Umesh

--
rm -rf master
scripts/mysql_install_db --basedir=/export/umesh/server/binaries/GABuilds/mysql-advanced-5.6.33 --datadir=/export/umesh/server/binaries/GABuilds/mysql-advanced-5.6.33/master -v
bin/mysqld --no-defaults --basedir=/export/umesh/server/binaries/GABuilds/mysql-advanced-5.6.33 --datadir=/export/umesh/server/binaries/GABuilds/mysql-advanced-5.6.33/master --core-file --socket=/tmp/mysql_master.sock   --port=3306 --log-error=/export/umesh/server/binaries/GABuilds/mysql-advanced-5.6.33/master/log.err    --server_id=1 --log_bin  2>&1 &

rm -rf 83232
scripts/mysql_install_db --basedir=/export/umesh/server/binaries/GABuilds/mysql-advanced-5.6.33 --datadir=/export/umesh/server/binaries/GABuilds/mysql-advanced-5.6.33/slave -v
bin/mysqld --no-defaults --basedir=/export/umesh/server/binaries/GABuilds/mysql-advanced-5.6.33 --datadir=/export/umesh/server/binaries/GABuilds/mysql-advanced-5.6.33/slave --core-file --socket=/tmp/mysql_slave.sock   --port=3307 --log-error=/export/umesh/server/binaries/GABuilds/mysql-advanced-5.6.33/slave/log.err  --server_id=2 --log_bin 2>&1 &

-- setup master/slave replication

-- master

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-advanced-5.6.33: ls -l master/hod03-slow.log
-rw-rw---- 1 umshastr common 232 Oct  3 08:11 master/hod03-slow.log
[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-advanced-5.6.33: chmod 000 master/hod03-slow.log
[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-advanced-5.6.33: ls -l master/hod03-slow.log
---------- 1 umshastr common 232 Oct  3 08:11 master/hod03-slow.log
[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-advanced-5.6.33: bin/mysql -uroot -S /tmp/mysql_master.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.33-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2016, 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> FLUSH SLOW LOGS;
ERROR 29 (HY000): File '/export/umesh/server/binaries/GABuilds/mysql-advanced-5.6.33/master/hod03-slow.log' not found (Errcode: 13 - Permission denied)
mysql>

-- slave

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: localhost
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: hod03-bin.000001
          Read_Master_Log_Pos: 891
               Relay_Log_File: hod03-relay-bin.000002
                Relay_Log_Pos: 283
        Relay_Master_Log_File: hod03-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error: Query caused different errors on master and slave.     Error on master: message (format)='Invalid error code' error code=29 ; Error on slave: actual message='no error', error code=0. Default database: ''. Query: 'FLUSH SLOW LOGS'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 813
              Relay_Log_Space: 534
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error: Query caused different errors on master and slave.     Error on master: message (format)='Invalid error code' error code=29 ; Error on slave: actual message='no error', error code=0. Default database: ''. Query: 'FLUSH SLOW LOGS'
  Replicate_Ignore_Server_Ids:

test case for MTR

Attachment: bug83232.test (application/octet-stream, text), 287 bytes.

Repeatable with all versions: 5.5, 5.6, 5.7, 8.0

Posted by developer:
 
Changelog entry added for MySQL 5.6.42, 5.7.24, and 8.0.13:

When FLUSH statements for specific log types (such as FLUSH SLOW LOGS) resulted in an error, the statements were still written to the binary log. This stopped replication because the error had occurred on the master, but did not occur on the slave. MySQL Server now checks on the outcome of these FLUSH statements, and if an error occurred, the statement is not written to the binary log.