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

DROP TABLE generated after half-failed CREATE OR REPLACE can break replication

    XMLWordPrintable

    Details

      Description

      When CREATE OR REPLACE TABLE fails after dropping the table, a special DROP TABLE is automatically written in the binary log.
      It causes data inconsistency between master and slave in the following scenario:

      • table t2 is created;
      • temporary table t2 is created and written to the binary log in statement format;
      • create or replace table t2 drops the base table t2 and then fails;
      • drop table t2 is generated and written to the binary log;
        => at the end, master and slave end up with different sets of base tables: the slave has t2, but the master does not.

      Test case

      --source include/have_binlog_format_mixed.inc
      --source include/master-slave.inc
       
      --enable_connect_log
       
      --let $con0 = `select connection_id()`
      create table t1 (i int);
      insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8);
      insert into t1 select t1a.* from t1 t1a, t1 t1b, t1 t1c, t1 t1d, t1 t1e, t1 t1f, t1 t1g;
       
      create table t2 like t1;
      create temporary table t2 as select 1;
      send create or replace table t2 as select * from t1;
       
      --connection master1
       
      --let $show_statement= SHOW PROCESSLIST
      --let $field= State
      --let $condition= = 'Sending data'
      --source include/wait_show_condition.inc
       
      eval kill query $con0;
       
      --connection master
      --error ER_QUERY_INTERRUPTED
      --reap
       
      show tables;
       
      --sync_slave_with_master
       
      show tables;
      drop table if exists t1, t2;
       
       
      --connection master
      drop table if exists t1, t2;
       
      --disable_connect_log
      --source include/rpl_end.inc
      

      Actual result, 10.3 62d21ddac, 10.0 55abcfa7b, and others

      connection master1;
      show tables;
      Tables_in_test
      t1
      connection slave;
      show tables;
      Tables_in_test
      t1
      t2
      

      Note: if your machine is too fast, CREATE OR REPLACE might end too quickly and won't be caught by SHOW PROCESSLIST, in which case you'll get a timeout on wait condition and subsequently success instead of ER_QUERY_INTERRUPTED on reap. Try to increase the amount of data in t1, to make CREATE AND REPLACE take longer.

        Attachments

          Activity

            People

            Assignee:
            Elkin Andrei Elkin
            Reporter:
            elenst Elena Stepanova
            Votes:
            1 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.