Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.5.3, 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5
-
CentOS7
Description
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.
