Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6
-
None
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
- relates to
-
MDEV-25292 Atomic CREATE OR REPLACE TABLE
- Stalled