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

LOAD DATA INFILE/inserted rows not written to binlog

    XMLWordPrintable

    Details

      Description

      Hi,

      I found a bug affecting recent MariaDB versions.
      Versions tested:

      • OS repo: MariaDB 5.5.68 (on CentOS 7) - Not affected
      • OS repo: MariaDB 10.3.27 (On CentOS 8) - Affected
      • MariaDB repo: MariaDB 10.5.8 (On CentOS 8) - Affected

      (All with stock settings, e.g. stock binlog_format)

      We have a master, with multiple slaves.
      The slaves have log_slave_updates=1, to replicate to more slaves.

      1. Master: MariaDB 5.5.68 on CentOS 7 from OS repository.
      2. Slave #1: MariaDB 10.5.8 On CentOS 8 from MariaDB repository. Also tested 10.3.27 from OS repository.
      3. Slave #2: MariaDB 5.5.68 on CentOS 7 from OS repository.

      We run an query on the master to update some table. The query is successfully replicated to slaves #1 and #2. However, slave #1 doesn't write it properly to the binary log, causing the further slaves actually lose rows.

      The query we run on the master:

      create temporary table tpool like cid_stock;
      load data local infile '/root/cidcsv.sql' into table tpool fields terminated by ',' lines terminated by '\n' (cid) set countrycode='IL';
      delete from cid_stock where countrycode='IL';
      insert into cid_stock select * from tpool where countrycode='IL';
      

      The issue seems related to temporary tables, because it doesn't happen with a static table. With this query i can successfully see the rows in the binary log:

      create table pool2 like cid_stock;
      load data local infile '/root/cidcsv.sql' into table pool2 fields terminated by ',' lines terminated by '\n' (cid) set countrycode='IL';
      delete from cid_stock where countrycode='IL';
      insert into cid_stock select * from pool2 where countrycode='IL';
      

      What happens is that slave #1 correctly executes this, however it doesn't write it properly to the binary log. The load data infile statement is missing. I guess its ok for it to be missing because of binlog_format=mixed, however the rows aren't replicated either.
      So what happens is that delete works, but the insert inserts 0 rows because the table is empty.
      On slave #2 its fine, it correctly writes the load data infile to the binary log.

      Binary log emitted by slave #1:

      # at 14546
      #210124 13:09:38 server id 8  end_log_pos 14588 CRC32 0xdb2fc10d 	GTID 0-8-96 ddl
      /*!100001 SET @@session.gtid_seq_no=96*//*!*/;
      # at 14588
      #210124 13:09:38 server id 8  end_log_pos 14709 CRC32 0x47091aaf 	Query	thread_id=44	exec_time=0	error_code=0
      SET TIMESTAMP=1611493778/*!*/;
      SET @@session.pseudo_thread_id=44/*!*/;
      create temporary table tpool like cid_stock
      /*!*/;
      # at 14709
      #210124 13:09:38 server id 8  end_log_pos 14751 CRC32 0x2e83513f 	GTID 0-8-97 trans
      /*!100001 SET @@session.gtid_seq_no=97*//*!*/;
      START TRANSACTION
      /*!*/;
      # at 14751
      #210124 13:09:38 server id 8  end_log_pos 14873 CRC32 0xfa6ce3aa 	Query	thread_id=44	exec_time=0	error_code=0
      SET TIMESTAMP=1611493778/*!*/;
      delete from cid_stock where countrycode='IL'
      /*!*/;
      # at 14873
      #210124 13:09:38 server id 8  end_log_pos 14904 CRC32 0x634c53ca 	Xid = 227
      COMMIT/*!*/;
      # at 14904
      #210124 13:09:38 server id 8  end_log_pos 14946 CRC32 0xad87d017 	GTID 0-8-98
      /*!100001 SET @@session.gtid_seq_no=98*//*!*/;
      START TRANSACTION
      /*!*/;
      # at 14946
      #210124 13:09:38 server id 8  end_log_pos 15088 CRC32 0xd5b71ed0 	Query	thread_id=44	exec_time=0	error_code=0
      SET TIMESTAMP=1611493778/*!*/;
      insert into cid_stock select * from tpool where countrycode='IL'
      /*!*/;
      # at 15088
      #210124 13:09:38 server id 8  end_log_pos 15119 CRC32 0xb6ae6dbc 	Xid = 229
      COMMIT/*!*/;
      DELIMITER ;
      # End of log file
      

      Binary log emitted by slave #2:

      # at 21828
      #210124 13:09:38 server id 8  end_log_pos 21945 	Query	thread_id=44	exec_time=0	error_code=0
      SET TIMESTAMP=1611493778/*!*/;
      SET @@session.pseudo_thread_id=44/*!*/;
      create temporary table tpool like cid_stock
      /*!*/;
      # at 21945
      #210124 13:09:38 server id 8  end_log_pos 22024 	Query	thread_id=44	exec_time=0	error_code=0
      SET TIMESTAMP=1611493778/*!*/;
      BEGIN
      /*!*/;
      # at 22024
      #210124 13:09:38 server id 8  end_log_pos 22223 
      #Begin_load_query: file_id: 17  block_len: 176
      # at 22223
      #210124 13:09:38 server id 8  end_log_pos 22498 	Execute_load_query	thread_id=44	exec_time=0	error_code=0
      SET TIMESTAMP=1611493778/*!*/;
      LOAD DATA LOCAL INFILE '/tmp/SQL_LOAD_MB-11-1' INTO TABLE `tpool` FIELDS TERMINATED BY ',' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (`cid`) SET `countrycode`='IL'
      /*!*/;
      # file_id: 17 
      # at 22498
      #210124 13:09:38 server id 8  end_log_pos 22578 	Query	thread_id=44	exec_time=0	error_code=0
      SET TIMESTAMP=1611493778/*!*/;
      COMMIT
      /*!*/;
      # at 22578
      #210124 13:09:38 server id 8  end_log_pos 22642 	Query	thread_id=44	exec_time=0	error_code=0
      SET TIMESTAMP=1611493778/*!*/;
      BEGIN
      /*!*/;
      # at 22642
      #210124 13:09:38 server id 8  end_log_pos 22760 	Query	thread_id=44	exec_time=0	error_code=0
      SET TIMESTAMP=1611493778/*!*/;
      delete from cid_stock where countrycode='IL'
      /*!*/;
      # at 22760
      #210124 13:09:38 server id 8  end_log_pos 22787 	Xid = 191
      COMMIT/*!*/;
      # at 22787
      #210124 13:09:38 server id 8  end_log_pos 22851 	Query	thread_id=44	exec_time=0	error_code=0
      SET TIMESTAMP=1611493778/*!*/;
      BEGIN
      /*!*/;
      # at 22851
      #210124 13:09:38 server id 8  end_log_pos 22989 	Query	thread_id=44	exec_time=0	error_code=0
      SET TIMESTAMP=1611493778/*!*/;
      insert into cid_stock select * from tpool where countrycode='IL'
      /*!*/;
      # at 22989
      #210124 13:09:38 server id 8  end_log_pos 23016 	Xid = 193
      COMMIT/*!*/;
      DELIMITER ;
      # End of log file
      

      Table structure:

      CREATE TABLE `cid_stock` (
        `countrycode` varchar(8) NOT NULL DEFAULT '',
        `cid` varchar(32) NOT NULL DEFAULT '',
        PRIMARY KEY (`countrycode`,`cid`),
        KEY `countrycode` (`countrycode`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1
      

      Example CSV file for the load data infile:

      4912399313
      4912399314
      4912399315
      4912399316
      4912399317
      4912399318
      

        Attachments

          Activity

            People

            Assignee:
            Elkin Andrei Elkin
            Reporter:
            mastertheknife Kfir Itzhak
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration

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