| 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: | |
| 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: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
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''