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

CREATE TABLE .. SELECT LASTVAL is written to binlog as single statement, causes discrepancy between master and slave

    XMLWordPrintable

Details

    Description

      --source include/have_innodb.inc
      		 
      --source include/have_binlog_format_mixed.inc
      		 
      --source include/master-slave.inc
      		 
       
      		 
      CREATE SEQUENCE s ENGINE=InnoDB;
      		 
      SELECT NEXTVAL(s);
      		 
      CREATE TABLE t ENGINE=InnoDB SELECT LASTVAL(s) AS a;
      		 
      SELECT * FROM t;
      		 
      --sync_slave_with_master
      		 
      SELECT * FROM t;
      		 
       
      		 
      # Cleanup
      		 
      --connection master
      		 
      DROP TABLE t;
      		 
      DROP SEQUENCE s;
      		 
      --source include/rpl_end.inc

      In the test case above, CREATE TABLE t ENGINE=InnoDB SELECT LASTVAL(s) AS a is written to the binary log as is, without splitting it into CREATE and a row insert. However, on the slave LASTVAL expectedly returns NULL, hence the table contents between master and slave diverge, which will naturally cause further replication problems.

      Actual result

      		 
      SELECT * FROM t;
      		 
      a
      		 
      1
      		 
      connection slave;
      		 
      SELECT * FROM t;
      		 
      a
      		 
      NULL

      Attachments

        Activity

          People

            monty Michael Widenius
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.