Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-23077

update multi tables, mysqlbinlog errors occur

Details

    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.

      Attachments

        1. bug02-image.png
          bug02-image.png
          98 kB
        2. bug03-imgae01.png
          bug03-imgae01.png
          346 kB
        3. bug03-imgae02.png
          bug03-imgae02.png
          346 kB
        4. image-2020-07-03-12-23-24-367.png
          image-2020-07-03-12-23-24-367.png
          235 kB

        Activity

          Elkin Andrei Elkin added a comment -

          lukexw Thanks for the reporting and patches! I am reviewing them atm. Notice though the bug01 issue duplicates MDEV-16372 which 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-16372 commits.
          In this ticket let's focus on the flashback bug02.

          Elkin Andrei Elkin added a comment - lukexw Thanks for the reporting and patches! I am reviewing them atm. Notice though the bug01 issue duplicates MDEV-16372 which 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-16372 commits. In this ticket let's focus on the flashback bug02.
          svoj Sergey Vojtovich added a comment -

          PR status update: no response from author for 4 years. Elkin feels like this PR can be worthy and he intends to take care of it.

          svoj Sergey Vojtovich added a comment - PR status update: no response from author for 4 years. Elkin feels like this PR can be worthy and he intends to take care of it.

          People

            Elkin Andrei Elkin
            lukexw lukexwang
            Votes:
            0 Vote for this issue
            Watchers:
            7 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.