[#MDEV-25595] DROP part of failed CREATE OR REPLACE is not written into binary log

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.

[MDEV-25595] DROP part of failed CREATE OR REPLACE is not written into binary log Created: 2021-05-04 Updated: 2021-07-05 Resolved: 2021-07-05
Status:	Closed
Project:	MariaDB Server
Component/s:	Data Definition - Create Table, Replication
Affects Version/s:	10.2, 10.3, 10.4, 10.5, 10.6
Fix Version/s:	10.2.40, 10.3.31, 10.4.21, 10.5.12, 10.6.4

[MDEV-25595] DROP part of failed CREATE OR REPLACE is not written into binary log Created: 2021-05-04 Updated: 2021-07-05 Resolved: 2021-07-05