Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-12067

flashback does not correcly revert update/replace statements

    XMLWordPrintable

Details

    • 10.2.7-1

    Description

      mysqlbinlog --flashback does not correctly recover database

      Checksum before destroying database:

      | world.City                | 2011482258 |
      

      Sequence of statements:

      INSERT INTO test.test VALUES (NULL, 'Good record 1', CURRENT_TIMESTAMP());
       
      INSERT INTO world.City VALUES (NULL, 'Wrong value 1', '000', 'Wrong', 0);
      INSERT INTO world.City VALUES (NULL, 'Wrong value 2', '000', 'Wrong', 0)
                                  , (NULL, 'Wrong value 3', '000', 'Wrong', 0);
       
      INSERT INTO test.test VALUES (NULL, 'Good record 2', CURRENT_TIMESTAMP());
       
      UPDATE world.City SET Population = 99999999 WHERE ID IN (4040, 4058, 4073);
       
      INSERT INTO test.test VALUES (NULL, 'Good record 3', CURRENT_TIMESTAMP());
       
      DELETE FROM world.City WHERE ID BETWEEN 10 AND 99;
       
      INSERT INTO test.test VALUES (NULL, 'Good record 5', CURRENT_TIMESTAMP());
       
      REPLACE INTO world.City VALUES (4074, 'Wrong value 4', '000', 'Wrong', 0);
      REPLACE INTO world.City VALUES (4078, 'Wrong value 5', '000', 'Wrong', 0)
                                   , (NULL, 'Wrong value 6', '000', 'Wrong', 0);
       
      INSERT INTO test.test VALUES (NULL, 'Good record 6', CURRENT_TIMESTAMP());
       
      INSERT INTO world.City
      SELECT NULL, Name, CountryCode, District, Population FROM world.City WHERE ID BETWEEN 200 AND 299;
       
      INSERT INTO test.test VALUES (NULL, 'Good record 7', CURRENT_TIMESTAMP());
       
      LOAD DATA INFILE '/tmp/test.csv' INTO TABLE world.City
        COLUMNS TERMINATED BY ','
        OPTIONALLY ENCLOSED BY '"'
      ;
       
      -- 6000,"Bad city 1","XXX","Wrong",0
      -- 6001,"Bad city 2","XXX","Wrong",0
      -- 6002,"Bad city 3","XXX","Wrong",0
      -- 6003,"Bad city 4","XXX","Wrong",0
      -- 6004,"Bad city 5","XXX","Wrong",0
       
      INSERT INTO test.test VALUES (NULL, 'Good record 8', CURRENT_TIMESTAMP());
       
      -- TRUNCATE TABLE world.City;
       
      INSERT INTO test.test VALUES (NULL, 'Good record 9', CURRENT_TIMESTAMP());
      

      Flashback:

      mysqlbinlog --database=world --table=City --flashback chef_mariadb-10.000031 -v | mysql -uroot
      

      Checksum after:

      Checksum after

      | world.City | 4138787994 |
      

      some bad records found:

      select * from world.City where Population = 99999999 or Name like 'Wrong%' or district = 'Wrong';
      +------+---------------+-------------+----------+------------+
      | ID   | Name          | CountryCode | District | Population |
      +------+---------------+-------------+----------+------------+
      | 4040 | Davenport     | USA         | Iowa     |   99999999 |
      | 4058 | Boulder       | USA         | Colorado |   99999999 |
      | 4073 | Gweru         | ZWE         | Midlands |   99999999 |
      | 4074 | Wrong value 4 | 000         | Wrong    |          0 |
      | 4078 | Wrong value 5 | 000         | Wrong    |          0 |
      +------+---------------+-------------+----------+------------+
      

      Seems to be only REPLACE/UPDATE related??? See also our bug reported a few hours earlier.

      I cannot upload binary log:
      An internal error has occurred. Please contact your administrator.

      But you should be capable to easily simulate the problem with instruction above.

      Attachments

        Activity

          People

            sachin.setiya.007 Sachin Setiya (Inactive)
            oli Oli Sennhauser
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.