MySQL Bugs: #84350: Error 1290 executing flush logs in read-only slave

Bug #84350	Error 1290 executing flush logs in read-only slave
Submitted:	27 Dec 2016 23:53	Modified:	23 Jan 2018 11:04
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:	read-only flush-logs GTID 1290

Description:
Flush logs command shows the following error when is executed in a slave instance in read-only for a user without super privilege:

ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

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
read_only	= ON

Requirements:

A) Master/slave configuration using GTIDs.
B) User with reload privilege and without super privilege.

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.02 sec)

2) In Slave instance execute flush logs:

mysql> show grants ;
+------------------------------------------------+
| Grants for test_user@localhost |
+------------------------------------------------+
| GRANT RELOAD ON *.* TO 'test_user'@'localhost' |
+------------------------------------------------+

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

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

-- Terminal 1

mysql> show master status ;
+------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------+
| mysql-bin.000006 | 726 | | | 3bc8c55f-cc7b-11e6-8186-020846607241:1-10,
42f9d661-cc7b-11e6-8175-020846607241:1-2 |
+------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

-- Terminal 2

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

-- Terminal 1

mysql> show master status ;
+------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------+
| mysql-bin.000007 | 234 | | | 3bc8c55f-cc7b-11e6-8186-020846607241:1-10,
42f9d661-cc7b-11e6-8175-020846607241:1-2 |
+------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Suggested fix:
Avoid error message if all logs are flushed.

Hello Juan Arruti,

Thank you for the report.
Verified as described.

Thanks,
Umesh

-- Even with standalone environment this can be observed
-- gtid based environment

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_master.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 &

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.17: bin/mysql -uroot -S /tmp/mysql_master.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.17-log MySQL Community Server (GPL)

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.

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

root@localhost [(none)]> set global read_only=1;
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)]> show variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |
+---------------+-------+
1 row in set (0.00 sec)

root@localhost [(none)]> flush logs;
Query OK, 0 rows affected (0.01 sec)

root@localhost [(none)]> show master logs;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| master-bin.000001 |       202 |
| master-bin.000002 |       154 |
+-------------------+-----------+
2 rows in set (0.00 sec)

root@localhost [(none)]> create user 'bug'@'localhost';
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)]> grant usage, reload on *.* to 'bug'@'localhost';
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)]> \q
Bye
[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.17: bin/mysql -ubug -S /tmp/mysql_master.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.17-log MySQL Community Server (GPL)

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.

bug@localhost [(none)]> show grants;
+------------------------------------------+
| Grants for bug@localhost                 |
+------------------------------------------+
| GRANT RELOAD ON *.* TO 'bug'@'localhost' |
+------------------------------------------+
1 row in set (0.00 sec)

bug@localhost [(none)]> show variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |
+---------------+-------+
1 row in set (0.01 sec)

bug@localhost [(none)]> show master logs;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation
bug@localhost [(none)]>
bug@localhost [(none)]> flush logs;
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
bug@localhost [(none)]> \q
Bye

-- Confirmed that new log is generted post "flush logs"

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.17: bin/mysql -uroot -S /tmp/mysql_master.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.17-log MySQL Community Server (GPL)

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.

root@localhost [(none)]> show master logs;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| master-bin.000001 |       202 |
| master-bin.000002 |       599 |
| master-bin.000003 |       194 |
+-------------------+-----------+
3 rows in set (0.00 sec)

root@localhost [(none)]>

Fixes contributed on bug 84437 for 5.7 and 8.0 fix this bug too

The testcase passes in 8.0.1.

Posted by developer:
 
This bug is fixed in 8.0.1, as part of Bug#22857926: ASSERTION `! IS_SET()' AT SQL_ERROR.CC:38 IN
READ_ONLY MODE FOR MANY RPL CMDS fix.
Commit id: b24fedd4a043088f3d9117bea3011788d4f6531e

It looks like it has been fixed in 5.7.18 under the same 22857926 too and should be documented and closed as such

Posted by developer:
 
The Replication team is currently sorting through outstanding bug reports on our files. Our records indicate that this was fixed in the patch for Bug #22857926 but this report has not yet been closed off. I have added this bug number to the changelog entry for Bug #22857926 and now closing.