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

sql_log_bin=0 and log_slave_updates can cause inconsistencies in binary logs

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.6.4
    • None
    • Galera, Replication, wsrep
    • None

    Description

      Hello,

      • Galera Cluster
      • log_slave_updates enabled on each node.

      Node1)

      MariaDB [test]> CREATE TABLE test (id int primary key auto_increment);
      Query OK, 0 rows affected (0.016 sec)
       
      MariaDB [test]> set sql_log_bin=0;
      Query OK, 0 rows affected (0.000 sec)
       
      MariaDB [test]> INSERT INTO test SET id = 42;
      Query OK, 1 row affected (0.001 sec)
      

      Binary logs from Node1:

       
      #210816 13:46:22 server id 1  end_log_pos 256 CRC32 0xfefd192a 	Start: binlog v 4, server v 10.6.4-MariaDB-1:10.6.4+maria~buster-log created 210816 13:46:22
      ...
      use `test`/*!*/;
      SET TIMESTAMP=1629114390/*!*/;
      SET @@session.pseudo_thread_id=39/*!*/;
      SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1, @@session.sql_if_exists=0/*!*/;
      SET @@session.sql_mode=1411383296/*!*/;
      SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
      /*!\C utf8mb3 *//*!*/;
      SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=45/*!*/;
      SET @@session.lc_time_names=0/*!*/;
      SET @@session.collation_database=DEFAULT/*!*/;
      CREATE TABLE test (id int primary key auto_increment)
      /*!*/;
      DELIMITER ;
      # End of log file
      ROLLBACK /* added by mysqlbinlog */;
      /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
      /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
      

      Binary logs from node2 (insert is written into the binary log):

      #210816 13:38:52 server id 1  end_log_pos 256 CRC32 0xbe7c519f 	Start: binlog v 4, server v 10.6.4-MariaDB-1:10.6.4+maria~buster-log created 210816 13:38:52 at startup
      ...
      #210816 13:46:38 server id 1  end_log_pos 1353 CRC32 0xea06756c 	Write_rows: table id 137 flags: STMT_END_F
       
      BINLOG '
      HlAaYRMBAAAALwAAACMFAAAAAIkAAAAAAAEABHRlc3QABHRlc3QAAQMAACtCtWg=
      HlAaYRcBAAAAJgAAAEkFAAAAAIkAAAAAAAEAAQH+KgAAAGx1Buo=
      '/*!*/;
      ### INSERT INTO `test`.`test`
      ### SET
      ###   @1=42 /* INT meta=0 nullable=0 is_null=0 */
      # Number of rows: 1
      # at 1353
      #210816 13:46:38 server id 1  end_log_pos 1384 CRC32 0x90af975e 	Xid = 14
      COMMIT/*!*/;
      DELIMITER ;
      # End of log file
      ROLLBACK /* added by mysqlbinlog */;
      

      All statements that are replicated through Galera and were executed as sql_log_bin=0 should be also skipped in binary logs on all members of the cluster.

      Attachments

        Activity

          People

            Unassigned Unassigned
            IMP IMP
            Votes:
            0 Vote for this issue
            Watchers:
            4 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.