Error 1290 executing flush logs in read-only slave

[Juan Pablo Arruti]

Description:

Tested in Percona Server 5.7.16 and MySQL Community 5.7.17.

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)

[Juan Pablo Arruti (juanarru)]

Persona Server 5.7.16

[Laurynas Biveinis (laurynas-biveinis)]

    https://github.com/percona/percona-server/pull/1456
    https://github.com/percona/percona-server/pull/1457

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1044