Bug #83007 GRANT statement breaks 5.7→8.0 replication w/ GTID
Submitted: 15 Sep 2016 8:33 Modified: 15 Sep 2016 13:28
Reporter: Daniël van Eeden (OCA) Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:8.0.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: GTID

[15 Sep 2016 8:33] Daniël van Eeden
Description:
With replication from 5.7.13 to 8.0.0 with GTID enabled and then running a grant statement results in this error:

                   Last_Errno: 1785
                   Last_Error: Error 'Statement violates GTID consistency: Updates to non-transactional tables can only be done in either autocommitted statements or single-statement transactions, and never in the same statement as updates to transactional tables.' on query. Default database: ''. Query: 'GRANT ALL PRIVILEGES ON *.* TO 'test_global'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx''

How to repeat:
master (5.7):

    grant all on *.* to 'repl-test'@'10.%' identified by 'asjfljksahflashflasjfhlsj';

result on slave:

    Last_SQL_Errno: 1785
    Last_SQL_Error: Error 'Statement violates GTID consistency: Updates to non-transactional tables can only be done in either autocommitted statements or single-statement transactions, and never in the same statement as updates to transactional tables.' on query. Default database: ''. Query: 'GRANT ALL PRIVILEGES ON *.* TO 'repl-test'@'10.%' IDENTIFIED WITH 'mysql_native_password' AS '*7F27E4CB2384774E8FF31F74B5726F9522BD9502''
[15 Sep 2016 8:38] Daniël van Eeden
Note that this does generate a warning on 5.7..

mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                            |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
[15 Sep 2016 12:47] MySQL Verification Team
Hello Daniël,

Thank you for the report.
I tried to reproduce the issue using 5.7.15(5.7.13)->8.0 but not seeing the reported issue(observed just warning for the GRANT.. on master which you reported too). Could you please share exact steps, configuration file used to reproduce this issue at our end?

Thanks,
Umesh
[15 Sep 2016 13:23] Daniël van Eeden
I tried to reproduce this with mysql sandbox and that failed (replication didn't break)

## Master

make_sandbox 5.7.15 -- --master --gtid

CREATE USER 'repl'@'%' IDENTIFIED BY '879qfh32cdq87t';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
GRANT ALL ON *.* TO 'repl-test'@'10.%' IDENTIFIED BY 'asjfljksahflashflasjfhlsj';

## Slave

make_sandbox 8.0.0 --gtid

CHANGE MASTER TO
  MASTER_HOST='127.0.0.1',
  MASTER_PORT=5715,MASTER_USER='repl',
  MASTER_PASSWORD='879qfh32cdq87t',
  MASTER_AUTO_POSITION=1;
START SLAVE;

Inspecting the server which had the original issues I found out that mysql.user and mysql.db were still MyISAM. Running mysql_upgrade fixed that.

While upgrading replication should not be used (e.g. start mysql with --skip-slave-start and run mysql_upgrade). So this should not really be an issue.

Please close as not a bug.
[15 Sep 2016 13:25] MySQL Verification Team
Thank you  Daniël, closing the bug for now.

Regards,
Umesh