Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.2.4
-
Linux n.a.
-
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.