|
Necessary condition: Binlog_format=ROW
SQL:
reset master;
|
use test;
|
create table school(
|
schoolID int not null default 0 primary key,
|
schoolName blob not null
|
)ENGINE=InnoDB;
|
create table class(
|
classID int not null default 0 primary key,
|
className blob not null,
|
schoolID int not null default 0
|
)ENGINE=InnoDB;
|
create table student(
|
stdID int not null default 0 primary key,
|
stdName blob not null,
|
classID int not null default 0,
|
schoolID int not null default 0
|
)ENGINE=InnoDB;
|
INSERT INTO school (schoolID,schoolName) VALUES (1,"S-a"),(2,"S-b");
|
INSERT INTO class(classID,className,schoolID) VALUES (1,"C-a",1),(2,"C-b",1),(3,"C-c",1),(4,"C-d",1);
|
INSERT INTO student(stdID,stdName,classID,schoolID) VALUES (1,"S-a",1,1),(2,"S-b",1,1),(3,"S-c",1,1),(4,"S-d",4,1),(5,"S-e",2,1),(6,"S-f",3,1),(7,"S-g",4,1);
|
|
BEGIN;
|
UPDATE school,student,class
|
SET school.schoolName='school_deleted',student.schoolID=2,class.schoolID=2
|
WHERE school.schoolID= class.schoolID AND class.schoolID = student.schoolID AND school.schoolID=1;
|
DELETE school,student,class
|
FROM school INNER JOIN student INNER JOIN class
|
WHERE school.schoolID IN (2);
|
COMMIT;
|
|
flush logs;
|
|
drop database test;
|
create database test;
|
bug01
mysqlbinlog $MYSQLD_DATADIR/master-bin.00001 | mysql -uroot work fine.
mysqlbinlog -vv $MYSQLD_DATADIR/master-bin.00001 | mysql -uroot have a error.
ERROR 1064 (42000) at line 240: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '3af+XhjlghcIHgEAAJ8LAAAAABcAAAAAAAAABP//8AEAAAADAFMtYQEAAAABAAAA8AEAAAADAFMt
|
...' at line 1
|
The cause of the bug: alternates the result of base64_encode and the commented UPDATE statement
bug02
If only student tables data need to be flashbacked, but not school,class table data, flashback result not corret.
mysqlbinlog -vv --flashback --table=student $MYSQLD_DATADIR/master-bin.00001
The cause of the bug: The rows_event of the school & class tables should be skipped, but has also been added to events_in_stmt(mysqlbinlog.cc).
bug03
If only school table data need to be flashbacked, but not student,class table data, an error will occur.
mysqlbinlog --flashback $MYSQLD_DATADIR/master-bin.00001 | mysql -uroot work fine.
mysqlbinlog --flashback --table=school $MYSQLD_DATADIR/master-bin.00001 | mysql -uroot have a error.
ERROR 1064 (42000) at line 25: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '3af+XhPlghcIMwAAANcJAAAAABIAAAAAAAEABHRlc3QABnNjaG9vbAACA/wBAgAMOrI+
|
'/*!*/;
|
...' at line 1
|
The cause of the bug: If the current ev is stmt_end and needs to be skipped, we still need to correctly handle the events in events_in_stmt that are not skipped.
|
bug02-image.png
