Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-6856

Correct binlogs corruptions in PS 5.7.28 and 5.7.29

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Done
    • Priority: High
    • Resolution: Fixed
    • Affects Version/s: 5.7.28-31, 5.7.29-32
    • Fix Version/s: 5.7.30-33
    • Component/s: None
    • Labels:

      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.

        Smart Checklist

          Attachments

            Activity

              People

              Assignee:
              Unassigned
              Reporter:
              eslocombe Emily Slocombe
              Votes:
              0 Vote for this issue
              Watchers:
              8 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: