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

DROP part of failed CREATE OR REPLACE is not written into binary log

    XMLWordPrintable

    Details

      Description

      In some cases when CREATE OR REPLACE TABLE fails half-way, having dropped the previous table but unable to create a new one, DROP TABLE is still written in the binary log to preserve consistency. However, it doesn't always happen:

      Test case 1

      --source include/have_log_bin.inc
       
      CREATE TABLE t (old_table_field INT);
      --error ER_DUP_FIELDNAME
      CREATE OR REPLACE TABLE t AS SELECT 1 AS b, 2 AS b;
      CREATE TABLE t (new_table_field INT);
       
      SHOW BINLOG EVENTS;
       
      # Cleanup
      DROP TABLE IF EXISTS t;
      

      Test case 2

      --source include/master-slave.inc
       
      CREATE TABLE t (a INT);
      --error ER_DUP_FIELDNAME
      CREATE OR REPLACE TABLE t AS SELECT 1 AS b, 2 AS b;
      CREATE TABLE t2 (c INT);
      RENAME TABLE t2 TO t;
       
      SHOW TABLES;
      SHOW BINLOG EVENTS;
       
      --sync_slave_with_master
       
      SHOW TABLES;
       
      # Cleanup
      --connection master
      DROP TABLE IF EXISTS t, t2;
      --source include/rpl_end.inc
      

      Test case 1 on 10.5 65e73b56

      CREATE TABLE t (old_table_field INT);
      CREATE OR REPLACE TABLE t AS SELECT 1 AS b, 2 AS b;
      ERROR 42S21: Duplicate column name 'b'
      CREATE TABLE t (new_table_field INT);
      SHOW BINLOG EVENTS;
      Log_name	Pos	Event_type	Server_id	End_log_pos	Info
      master-bin.000001	4	Format_desc	1	256	Server ver: 10.5.10-MariaDB-debug-log, Binlog ver: 4
      master-bin.000001	256	Gtid_list	1	285	[]
      master-bin.000001	285	Binlog_checkpoint	1	329	master-bin.000001
      master-bin.000001	329	Gtid	1	371	GTID 0-1-1
      master-bin.000001	371	Query	1	474	use `test`; CREATE TABLE t (old_table_field INT)
      master-bin.000001	474	Gtid	1	516	GTID 0-1-2
      master-bin.000001	516	Query	1	619	use `test`; CREATE TABLE t (new_table_field INT)
      

      In a replication setup test case 1 still works (doesn't fail) due to special logic performed by the slave, added by this commit in 10.0:

      commit 5426facdcbfba2d78dab3c709cbf278073383b7c
      Author: Michael Widenius
      Date:   Wed Feb 5 19:01:59 2014 +0200
       
          Replication changes for CREATE OR REPLACE TABLE
          - CREATE TABLE is by default executed on the slave as CREATE OR REPLACE
          - DROP TABLE is by default executed on the slave as DROP TABLE IF NOT EXISTS
      

      However, since it's only a hack for fixing the next CREATE, test case 2 fails with replication, too.

      Last_SQL_Errno	1050
      Last_SQL_Error	Error 'Table 't' already exists' on query. Default database: 'test'. Query: 'RENAME TABLE t2 TO t'
      

      If MDEV-25292 makes it to 10.6, the source of the problem should be eliminated. But previous releases still have a long life to live.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              midenok Aleksey Midenkov
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated: