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

update multi tables, mysqlbinlog errors occur

    XMLWordPrintable

    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

            People

            Assignee:
            Elkin Andrei Elkin
            Reporter:
            lukexw lukexwang
            Votes:
            0 Vote for this issue
            Watchers:
            5 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.