Bug #84564 Inconsistent GRANT behavior on master and slave breaks replication

• Submitted: 19 Jan 2017 13:56
• Modified: 4 Dec 2017 6:29
• Reporter: Przemyslaw Malkowski
• Status: Duplicate
• Category: MySQL Server: Replication
• Severity: S2 (Serious)
• Version: 5.7, 5.7.17
• OS: Any
• CPU Architecture: Any

[19 Jan 2017 13:56] Przemyslaw Malkowski

Description:
GRANT PROXY command for non existent user breaks replication.
Cannot reproduce with earlier major versions.

master [localhost] {root} ((none)) > select @@version;
+------------+
| @@version  |
+------------+
| 5.7.17-log |
+------------+
1 row in set (0.00 sec)

How to repeat:
master [localhost] {root} ((none)) > flush logs;
Query OK, 0 rows affected (0.03 sec)

master [localhost] {root} ((none)) > CREATE USER 'testuser'@'%';
Query OK, 0 rows affected (0.01 sec)

master [localhost] {root} ((none)) > grant proxy on test2 to testuser;
Query OK, 0 rows affected (0.01 sec)

master [localhost] {root} ((none)) > show binlog events in 'mysql-bin.000002'\G
*************************** 1. row ***************************
   Log_name: mysql-bin.000002
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 123
       Info: Server ver: 5.7.17-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: mysql-bin.000002
        Pos: 123
 Event_type: Previous_gtids
  Server_id: 1
End_log_pos: 154
       Info: 
*************************** 3. row ***************************
   Log_name: mysql-bin.000002
        Pos: 154
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 219
       Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 4. row ***************************
   Log_name: mysql-bin.000002
        Pos: 219
 Event_type: Query
  Server_id: 1
End_log_pos: 356
       Info: CREATE USER 'testuser'@'%' IDENTIFIED WITH 'mysql_native_password'
*************************** 5. row ***************************
   Log_name: mysql-bin.000002
        Pos: 356
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 421
       Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 6. row ***************************
   Log_name: mysql-bin.000002
        Pos: 421
 Event_type: Query
  Server_id: 1
End_log_pos: 556
       Info: GRANT USAGE ON *.* TO 'test2'@'%','testuser'@'%'
6 rows in set (0.00 sec)

master [localhost] {root} ((none)) > select user,host from mysql.user where user like 'test%';
+----------+------+
| user     | host |
+----------+------+
| testuser | %    |
+----------+------+
1 row in set (0.00 sec)

master [localhost] {root} ((none)) > show grants for 'testuser'@'%';
+----------------------------------------------+
| Grants for testuser@%                        |
+----------------------------------------------+
| GRANT USAGE ON *.* TO 'testuser'@'%'         |
| GRANT PROXY ON 'test2'@'%' TO 'testuser'@'%' |
+----------------------------------------------+
2 rows in set (0.00 sec)

-- SLAVE

slave1 [localhost] {msandbox} ((none)) > show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: rsandbox
                  Master_Port: 20192
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 556
               Relay_Log_File: mysql-relay.000005
                Relay_Log_Pos: 569
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
...
                   Last_Errno: 1133
                   Last_Error: Error 'Can't find any matching row in the user table' on query. Default database: ''. Query: 'GRANT USAGE ON *.* TO 'test2'@'%','testuser'@'%''

slave1 [localhost] {msandbox} ((none)) > show grants for 'testuser'@'%';
+--------------------------------------+
| Grants for testuser@%                |
+--------------------------------------+
| GRANT USAGE ON *.* TO 'testuser'@'%' |
+--------------------------------------+
1 row in set (0.00 sec)

-- workaround for broken replication:

slave1 [localhost] {msandbox} ((none)) > CREATE USER 'test2'@'%';
Query OK, 0 rows affected (0.01 sec)

slave1 [localhost] {msandbox} ((none)) > start slave;
Query OK, 0 rows affected (0.01 sec)

Suggested fix:
Query results should be consistent on master and slave.

[20 Jan 2017 5:13] MySQL Verification Team

Hello Przemyslaw,

Thank you for the report.
Verified as described.

Thanks,
Umesh

[20 Jan 2017 5:13] MySQL Verification Team

test results

Attachment: 84564_5.7.17.results (application/octet-stream, text), 11.54 KiB.

[23 Jun 2017 7:23] MySQL Verification Team

Bug #86806 marked as duplicate of this

[12 Jul 2017 8:49] 镇熙 林

With brand new MySQL Server, there is only one root user ('root'@'localhost'), with grants below:
    GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
    GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION;
Now I want to create a 'root'@'%' user with same privileges. So I run the scripts below:
    CREATE USER 'root'@'%' IDENTIFIED BY 'root';
    GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
    GRANT PROXY ON ''@'' TO 'root'@'%' WITH GRANT OPTION;
But this will trigger the bug 84564!
How can I create a 'root'@'%' user with same privileges?

[4 Dec 2017 7:19] Venkatesh Venugopal

This bug is a duplicate of Bug#81424.