Details
-
Bug
-
Status: In Review (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.
lukexw Thanks for the reporting and patches! I am reviewing them atm. Notice though the bug01 issue duplicates
MDEV-16372which patch has been elaborated (to actually follow your and the upstream patch idea).If anything is worth to take from your patch I'll consider and do. Feel free to come up yourself with comments to
MDEV-16372commits.In this ticket let's focus on the flashback bug02.