Bug #84437 super-read-only does not allow FLUSH LOGS on 5.7
Submitted: 6 Jan 2017 23:44 Modified: 23 Jan 11:27
Reporter: Juan Arruti Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.7.17 OS:Any
Assigned to: CPU Architecture:Any
Tags: GTID super_read_only

[6 Jan 2017 23:44] Juan Arruti
Description:
Flush logs command shows the following error when is executed in a slave instance with super-read-only by root user:
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

This also affects mysqladmin flush-logs command.

How to repeat:
Variables for Master:

gtid-mode = ON
enforce-gtid-consistency = ON

Variables for Slave:

gtid-mode = ON
enforce-gtid-consistency = ON
log_slave_updates = ON
super_read_only	= ON

Requirements:

A) Master/slave configuration using GTIDs.

Procedure:

1) In Master instance create a table and insert a row on it.

mysql> create table test (col integer) ;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into test values (1);
Query OK, 1 row affected (0.01 sec)

2) In Slave instance log as root user and flush logs.

mysql> flush logs ;
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

Regardless of the error message, the logs are flushed by the command:

mysql> show master status ;
+------------------+----------+--------------+------------------+----------------------------------------------------------------------------------------------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                                                                |
+------------------+----------+--------------+------------------+----------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000079 |      690 |              |                  | 3bc8c55f-cc7b-11e6-8186-020846607241:1-197,42f9d661-cc7b-11e6-8175-020846607241:1-5,bb73025c-d38a-11e6-8547-020846607241:2-125 |
+------------------+----------+--------------+------------------+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> flush logs ;
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

mysql> show master status ;
+------------------+----------+--------------+------------------+----------------------------------------------------------------------------------------------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                                                                |
+------------------+----------+--------------+------------------+----------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000080 |      274 |              |                  | 3bc8c55f-cc7b-11e6-8186-020846607241:1-197,42f9d661-cc7b-11e6-8175-020846607241:1-5,bb73025c-d38a-11e6-8547-020846607241:2-125 |
+------------------+----------+--------------+------------------+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Suggested fix:
Suggested fix: Avoid error message if all logs are flushed.
[9 Jan 2017 6:02] Umesh Shastry
Hello Juan Arruti,

Thank you for the report.
Verified as described.

Thanks,
Umesh
[9 Jan 2017 6:02] Umesh Shastry
--

-- Setup GTID based replication

rm -rf master
bin/mysqld --initialize-insecure --basedir=/export/umesh/server/binaries/GABuilds/mysql-5.7.17 --datadir=/export/umesh/server/binaries/GABuilds/mysql-5.7.17/master -v
bin/mysqld --no-defaults --basedir=/export/umesh/server/binaries/GABuilds/mysql-5.7.17 --datadir=/export/umesh/server/binaries/GABuilds/mysql-5.7.17/master --core-file --socket=/tmp/mysql.sock --port=3306 --log-error=/export/umesh/server/binaries/GABuilds/mysql-5.7.17/master/log.err --log-bin=master-bin --server_id=1 --gtid_mode=ON --log-slave-updates --enforce-gtid-consistency 2>&1 &

rm -rf slave
bin/mysqld --initialize-insecure --basedir=/export/umesh/server/binaries/GABuilds/mysql-5.7.17 --datadir=/export/umesh/server/binaries/GABuilds/mysql-5.7.17/slave -v
bin/mysqld --no-defaults --basedir=/export/umesh/server/binaries/GABuilds/mysql-5.7.17 --datadir=/export/umesh/server/binaries/GABuilds/mysql-5.7.17/slave --core-file --socket=/tmp/mysql_slave.sock --port=3307 --log-error=/export/umesh/server/binaries/GABuilds/mysql-5.7.17/slave/log.err --log-bin=slave-bin --server_id=2 --skip_slave_start --gtid_mode=ON --log-slave-updates --enforce-gtid-consistency --super_read_only=ON 2>&1 &

-- master

root@localhost [(none)]> create database test; use test;
Query OK, 1 row affected (0.00 sec)

Database changed
root@localhost [test]> create table test (col integer) ;
Query OK, 0 rows affected (0.00 sec)

root@localhost [test]>  insert into test values (1);
Query OK, 1 row affected (0.01 sec)

-- slave

root@localhost [(none)]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| slave-bin.000001 |      1795 |
+------------------+-----------+
1 row in set (0.00 sec)

root@localhost [(none)]> flush logs;
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
root@localhost [(none)]>
root@localhost [(none)]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| slave-bin.000001 |      1842 |
| slave-bin.000002 |       194 |
+------------------+-----------+
2 rows in set (0.00 sec)
[12 Jan 2017 12:17] Sveta Smirnova
Originally reported at https://bugs.launchpad.net/percona-server/+bug/1654682
[2 Mar 2017 13:52] Laurynas Biveinis
Bug 84437 and 84350 fix for 5.7

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: bug84437-84350-5.7.patch (application/octet-stream, text), 10.51 KiB.

[2 Mar 2017 13:52] Laurynas Biveinis
Bug 84437 and 84350 fix for 8.0.0

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: bug84437-84350-8.0.patch (application/octet-stream, text), 10.57 KiB.

[11 Apr 2017 12:48] Laurynas Biveinis
This has been fixed in 8.0.1, probably by the following commit. The parts of my contributed fix to fix missing FLUSH LOGS error propagation still apply.

commit b24fedd4a043088f3d9117bea3011788d4f6531e
Author: Sujatha Sivakumar <sujatha.sivakumar@oracle.com>
Date:   Tue Nov 15 12:01:05 2016 +0530

    Bug#22857926: ASSERTION `! IS_SET()' AT SQL_ERROR.CC:38 IN
    READ_ONLY MODE FOR MANY RPL CMDS.
    
    Problem:
    =======
    This bug is opened to fix assertion `! is_set()' at
    sql_error.cc:380 in "void Diagnostics_area::set_ok_status
    (ulonglong, ulonglong, const char*)" for many RPL commands.
    This issue has been fixed for couple of commands with
    Bug#22097534. But, still issue exists for few other
    commands.
    
    RPL commands affected:-
    =======================
    [PENDING] 1. FLUSH LOGS;
    [PENDING] 2. RESET SLAVE ALL;
    [PENDING] 3. SET GLOBAL gtid_purged='aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1';
    [FIXED]   4. CHANGE MASTER TO..
    [FIXED]   5. CHANGE REPLICATION FILTER
    [FIXED]   6. START SLAVE;
    [FIXED]   7. STOP SLAVE;
    
    Analysis:
    ========
    When the above set of commands are executed they will try
    to update replication specific system tables. For example
    mysql.gtid_executed
    mysql.salve_master_info
    mysql.relay_log_info
    
    This scenario is applicable to all replication specific
    internal system tables. During the fix for Bug22097534 it
    was concluded that "read only" mode should prevent changes
    to user data, but not to prevent normal Server operations or
    to prevent a DBA from managing the state of the MySQL Server
    itself.
    
    Hence the above commands should be able to update data in
    replication specific internal tables.
    
    Fix:
    ===
    There exists a flag named 'ignore_global_read_lock' whose
    intention is to ignore the read only option and allow
    commits to replication specific system tables. Reused
    this flag as part of fix. If this flag is set then the
    additional check for 'read only' option is not done.
    Hence 'read only' mode error will not be reported and
    command will complete successfully.
[3 May 2017 8:21] Laurynas Biveinis
It looks like it has been fixed in 5.7.18 under the same 22857926 too and should be documented and closed as such (but please consider taking the error propagation bits in the contributed patch)
[19 Jan 7:33] Neha Kumari
Posted by developer:
 
The error propagation part of the contributed patch was fixed as a separate bug (Bug#26272158:FLUSH LOGS SENDS OK EVEN AFTER FAILING IN THE COMMIT PHASE) in 5.7.20 and 8.0.3.
[23 Jan 11:27] Margaret Fisher
The Replication team is currently sorting through outstanding bug reports on
our files. Our records indicate that this was partly fixed as Bug??#26272158,
and the remainder was fixed in the patch for Bug #22857926, but this report
has not yet been closed off because the bugs were not linked together
properly. The error propagation part of the patch was used as Bug??#26272158,
so I have added a patch credit, sorry that was not done earlier. I have added
this bug number to the changelog entries for Bug??#26272158 and Bug
#22857926, and now closing.