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

flashback does not correcly revert update/replace statements

    Details

    • Sprint:
      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

            • Assignee:
              sachin.setiya.007 Sachin Setiya
              Reporter:
              oli Oli Sennhauser
            • Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated: