|
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.
|