Details
-
Bug
-
Status: Done
-
High
-
Resolution: Fixed
-
5.7.28-31, 5.7.29-32
-
None
Description
Hello,
We use row based replication, and replication kept breaking when we tried to upgrade to 5.7.29. DCLs are randomly getting NULLs written into the binlog, and subsequently the relay log that goes to the replica. We have automation around user grants, so we do run them more frequently than the average database user.
In order to reproduce this on database clusters that were mostly without data updates (other than pt-heartbeat updating data, and monitoring doing selects), all I need to do is run this:
while [ 0 ] ; do mysql < grants.sql ; sleep 0.5; done
and eventually replication breaks - it might take 4 hours, it might take 48 hours, but eventually it does break. I have noticed it is more effective if there is some variety in the grants - I did try just one USAGE grant and was unable to break replication. I needed at least the grants I am using.
grants.sql:
GRANT USAGE on *.* TO 'pt'@'%'; GRANT ALL PRIVILEGES ON *.* TO 'user1.01'@'%' REQUIRE ISSUER '/CN=Cats User Certificate Authority - G1/O=Cats Inc./ST=California/L=San Francisco/C=US' AND SUBJECT '/OU=cathotel/CN=cathotel-sn3-user1.01' WITH GRANT OPTION; GRANT ALL PRIVILEGES ON *.* TO 'user2.01'@'%' REQUIRE ISSUER '/CN=Cats User Certificate Authority - G1/O=Cats Inc./ST=California/L=San Francisco/C=US' AND SUBJECT '/OU=cathotel/CN=cathotel-sn3-user2.01' WITH GRANT OPTION; GRANT ALL PRIVILEGES ON *.* TO 'user3.02'@'%' REQUIRE ISSUER '/CN=Cats User Certificate Authority - G1/O=Cats Inc./ST=California/L=San Francisco/C=US' AND SUBJECT '/OU=cathotel/CN=cathotel-sn3-user3.02' WITH GRANT OPTION; GRANT ALL PRIVILEGES ON *.* TO 'user4.01'@'%' REQUIRE ISSUER '/CN=Cats User Certificate Authority - G1/O=Cats Inc./ST=California/L=San Francisco/C=US' AND SUBJECT '/OU=cathotel/CN=cathotel-sn3-user4.01' WITH GRANT OPTION; GRANT ALL PRIVILEGES ON *.* TO 'user5.01'@'%' REQUIRE ISSUER '/CN=Cats User Certificate Authority - G1/O=Cats Inc./ST=California/L=San Francisco/C=US' AND SUBJECT '/OU=cathotel/CN=cathotel-sn3-user5.01' WITH GRANT OPTION; GRANT ALL PRIVILEGES ON *.* TO 'user6.01'@'%' REQUIRE ISSUER '/CN=Cats User Certificate Authority - G1/O=Cats Inc./ST=California/L=San Francisco/C=US' AND SUBJECT '/OU=cathotel/CN=cathotel-sn3-user6.01' WITH GRANT OPTION; GRANT ALL PRIVILEGES ON *.* TO 'user7.02'@'%' REQUIRE ISSUER '/CN=Cats User Certificate Authority - G1/O=Cats Inc./ST=California/L=San Francisco/C=US' AND SUBJECT '/OU=cathotel/CN=cathotel-sn3-user7.02' WITH GRANT OPTION; GRANT ALL PRIVILEGES ON *.* TO 'user8.01'@'%' REQUIRE ISSUER '/CN=Cats User Certificate Authority - G1/O=Cats Inc./ST=California/L=San Francisco/C=US' AND SUBJECT '/OU=cathotel/CN=cathotel-sn3-user8.01' WITH GRANT OPTION; GRANT ALL PRIVILEGES ON *.* TO 'user9.01'@'%' REQUIRE ISSUER '/CN=Cats User Certificate Authority - G1/O=Cats Inc./ST=California/L=San Francisco/C=US' AND SUBJECT '/OU=cathotel/CN=cathotel-sn3-user9.01' WITH GRANT OPTION; GRANT ALL PRIVILEGES ON *.* TO 'user10.01'@'%' REQUIRE ISSUER '/CN=Cats User Certificate Authority - G1/O=Cats Inc./ST=California/L=San Francisco/C=US' AND SUBJECT '/OU=cathotel/CN=cathotel-sn3-user10.01' WITH GRANT OPTION; GRANT PROCESS, SHOW DATABASES, REPLICATION CLIENT ON *.* TO 'user1.01'@'%' REQUIRE ISSUER '/CN=Cats User Certificate Authority - G1/O=Cats Inc./ST=California/L=San Francisco/C=US' AND SUBJECT '/OU=cathotel/CN=cathotel-sn3-user1.01'; GRANT PROCESS, SHOW DATABASES, REPLICATION CLIENT ON *.* TO 'user2.01'@'%' REQUIRE ISSUER '/CN=Cats User Certificate Authority - G1/O=Cats Inc./ST=California/L=San Francisco/C=US' AND SUBJECT '/OU=cathotel/CN=cathotel-sn3-user2.01'; GRANT PROCESS, SHOW DATABASES, REPLICATION CLIENT ON *.* TO 'user3.02'@'%' REQUIRE ISSUER '/CN=Cats User Certificate Authority - G1/O=Cats Inc./ST=California/L=San Francisco/C=US' AND SUBJECT '/OU=cathotel/CN=cathotel-sn3-user3.02'; GRANT PROCESS, SHOW DATABASES, REPLICATION CLIENT ON *.* TO 'user4.01'@'%' REQUIRE ISSUER '/CN=Cats User Certificate Authority - G1/O=Cats Inc./ST=California/L=San Francisco/C=US' AND SUBJECT '/OU=cathotel/CN=cathotel-sn3-user4.01'; GRANT PROCESS, SHOW DATABASES, REPLICATION CLIENT ON *.* TO 'user5.01'@'%' REQUIRE ISSUER '/CN=Cats User Certificate Authority - G1/O=Cats Inc./ST=California/L=San Francisco/C=US' AND SUBJECT '/OU=cathotel/CN=cathotel-sn3-user5.01'; GRANT PROCESS, SHOW DATABASES, REPLICATION CLIENT ON *.* TO 'user6.01'@'%' REQUIRE ISSUER '/CN=Cats User Certificate Authority - G1/O=Cats Inc./ST=California/L=San Francisco/C=US' AND SUBJECT '/OU=cathotel/CN=cathotel-sn3-user6.01'; GRANT PROCESS, SHOW DATABASES, REPLICATION CLIENT ON *.* TO 'user7.02'@'%' REQUIRE ISSUER '/CN=Cats User Certificate Authority - G1/O=Cats Inc./ST=California/L=San Francisco/C=US' AND SUBJECT '/OU=cathotel/CN=cathotel-sn3-user7.02'; GRANT PROCESS, SHOW DATABASES, REPLICATION CLIENT ON *.* TO 'user8.01'@'%' REQUIRE ISSUER '/CN=Cats User Certificate Authority - G1/O=Cats Inc./ST=California/L=San Francisco/C=US' AND SUBJECT '/OU=cathotel/CN=cathotel-sn3-user8.01'; GRANT PROCESS, SHOW DATABASES, REPLICATION CLIENT ON *.* TO 'user9.01'@'%' REQUIRE ISSUER '/CN=Cats User Certificate Authority - G1/O=Cats Inc./ST=California/L=San Francisco/C=US' AND SUBJECT '/OU=cathotel/CN=cathotel-sn3-user9.01'; GRANT PROCESS, SHOW DATABASES, REPLICATION CLIENT ON *.* TO 'user10.01'@'%' REQUIRE ISSUER '/CN=Cats User Certificate Authority - G1/O=Cats Inc./ST=California/L=San Francisco/C=US' AND SUBJECT '/OU=cathotel/CN=cathotel-sn3-user10.01'; GRANT SELECT ON `performance_schema`.* TO 'user1.01'@'%'; GRANT SELECT ON `performance_schema`.* TO 'user2.01'@'%'; GRANT SELECT ON `performance_schema`.* TO 'user3.02'@'%'; GRANT SELECT ON `performance_schema`.* TO 'user4.01'@'%'; GRANT SELECT ON `performance_schema`.* TO 'user5.01'@'%'; GRANT SELECT ON `performance_schema`.* TO 'user6.01'@'%'; GRANT SELECT ON `performance_schema`.* TO 'user7.02'@'%'; GRANT SELECT ON `performance_schema`.* TO 'user8.01'@'%'; GRANT SELECT ON `performance_schema`.* TO 'user9.01'@'%'; GRANT SELECT ON `performance_schema`.* TO 'user10.01'@'%'; GRANT EXECUTE ON PROCEDURE `dbadmin`.`largest_tables` TO 'user1.01'@'%'; GRANT EXECUTE ON PROCEDURE `dbadmin`.`largest_tables` TO 'user2.01'@'%'; GRANT EXECUTE ON PROCEDURE `dbadmin`.`largest_tables` TO 'user3.02'@'%'; GRANT EXECUTE ON PROCEDURE `dbadmin`.`largest_tables` TO 'user4.01'@'%'; GRANT EXECUTE ON PROCEDURE `dbadmin`.`largest_tables` TO 'user5.01'@'%'; GRANT EXECUTE ON PROCEDURE `dbadmin`.`largest_tables` TO 'user6.01'@'%'; GRANT EXECUTE ON PROCEDURE `dbadmin`.`largest_tables` TO 'user7.02'@'%'; GRANT EXECUTE ON PROCEDURE `dbadmin`.`largest_tables` TO 'user8.01'@'%'; GRANT EXECUTE ON PROCEDURE `dbadmin`.`largest_tables` TO 'user9.01'@'%'; GRANT EXECUTE ON PROCEDURE `dbadmin`.`largest_tables` TO 'user10.01'@'%';
Here is an example of part of a binlog passed through xxd of an offending section with a null:
0001720: 3230 3837 382f 2a21 2a2f 3b0a 4752 414e 20878/*!*/;.GRAN
0001730: 5420 5345 4c45 4354 204f 4e20 0070 6572 T SELECT ON .per
0001740: 666f 726d 616e 6365 5f73 6368 656d 6160 formance_schema`
Where, reading the binlog directly in less or mysqlbinlog, it looks like this:
#200208 0:14:38 server id 167839575 end_log_pos 193747544 CRC32 0x154ef675 Query thread_id=23536 exec_time=0 error_code=0 SET TIMESTAMP=1581120878/*!*/; GRANT SELECT ON ^@performance_schema`.* TO 'user1.01'@'%' /*!*/; # at 193747544
There are no extra characters in the general log, which I enabled to test this bug. I was not able to cause this in 5.7.27, only the subsequent 2 versions.