Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6, 10.11, 11.4, 11.8, 12.3
-
None
Description
Note:
The provided test case is based on the non-atomic nature of CREATE OR REPLACE; when MDEV-25292 (Atomic CoR) is pushed to the main branch, the test case will become not applicable to it and further release lines, although it will be still valid at least till 12.3 LTS goes EOL. However, the point of this bug report is not about non-atomicity of CREATE OR REPLACE, it is well-known, and replication has some hack-ish workarounds for it, sometimes successful, sometimes not; the problem here is the discrepancy in how AUTOCOMMIT value affects the outcome of DDL replication.
The base of the test case is:
- create table t1
- run create or replace t1 which fails after dropping the table
- run DDL involving table t1 which should work if the table doesn't exist and fail if it does.
Since CREATE OR REPLACE is currently non-atomic, it might drop the table on the master but not create a new one. To mitigate this, the server usually generates DROP TABLE IF EXISTS for the binlog:
|
Normal behavior |
[connection master] |
CREATE TABLE t1 (a INT); |
CREATE OR REPLACE TABLE t1 ENGINE=PERFORMANCE_SCHEMA AS SELECT 1 AS x; |
ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by the engine") |
SHOW BINLOG EVENTS;
|
Log_name Pos Event_type Server_id End_log_pos Info
|
master-bin.000001 4 Format_desc 1 257 Server ver: 12.3.2-MariaDB-log, Binlog ver: 4
|
master-bin.000001 257 Gtid_list 1 286 []
|
master-bin.000001 286 Binlog_checkpoint 1 330 master-bin.000001
|
master-bin.000001 330 Gtid 1 372 GTID 0-1-1
|
master-bin.000001 372 Query 1 471 use `test`; CREATE TABLE t1 (a INT) |
master-bin.000001 471 Gtid 1 513 BEGIN GTID 0-1-2 |
master-bin.000001 513 Query 1 0 use `test`; DROP TABLE IF EXISTS `test`.`t1`/* Generated to handle failed CREATE OR REPLACE */ |
master-bin.000001 662 Query 1 737 ROLLBACK |
Problem:
However, it stops generating DROP TABLE if we have AUTOCOMMIT=0 in the session:
|
Unexpected AUTOCOMMIT effect (12.3 2f4743f15) |
[connection master] |
CREATE TABLE t1 (a INT); |
SET AUTOCOMMIT = OFF; |
CREATE OR REPLACE TABLE t1 ENGINE=PERFORMANCE_SCHEMA AS SELECT 1 AS x; |
ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by the engine") |
COMMIT; # Just in case, doesn't make any difference |
SHOW BINLOG EVENTS;
|
Log_name Pos Event_type Server_id End_log_pos Info
|
master-bin.000001 4 Format_desc 1 257 Server ver: 12.3.2-MariaDB-log, Binlog ver: 4
|
master-bin.000001 257 Gtid_list 1 286 []
|
master-bin.000001 286 Binlog_checkpoint 1 330 master-bin.000001
|
master-bin.000001 330 Gtid 1 372 GTID 0-1-1
|
master-bin.000001 372 Query 1 471 use `test`; CREATE TABLE t1 (a INT) |
which of course means that master and slave diverge.
Post-note:
Replication still tries to "fix" the problem even after this divergence, e.g. by
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
|
but of it doesn't help in many other cases, e.g. with RENAME:
|
Replication abort |
[connection master] |
CREATE TABLE t1 (a INT); |
CREATE TABLE t2 (b INT); |
SET AUTOCOMMIT = 0; |
CREATE OR REPLACE TABLE t1 ENGINE=PERFORMANCE_SCHEMA AS SELECT 1 AS x; |
ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by the engine") |
COMMIT;
|
RENAME TABLE t2 TO t1;
|
SHOW BINLOG EVENTS;
|
Log_name Pos Event_type Server_id End_log_pos Info
|
master-bin.000001 4 Format_desc 1 257 Server ver: 12.3.2-MariaDB-log, Binlog ver: 4
|
master-bin.000001 257 Gtid_list 1 286 []
|
master-bin.000001 286 Binlog_checkpoint 1 330 master-bin.000001
|
master-bin.000001 330 Gtid 1 372 GTID 0-1-1
|
master-bin.000001 372 Query 1 471 use `test`; CREATE TABLE t1 (a INT)
|
master-bin.000001 471 Gtid 1 513 GTID 0-1-2
|
master-bin.000001 513 Query 1 612 use `test`; CREATE TABLE t2 (b INT)
|
master-bin.000001 612 Gtid 1 654 GTID 0-1-3
|
master-bin.000001 654 Query 1 751 use `test`; RENAME TABLE t2 TO t1
|
|
|
...
|
Last_Errno 1050
|
Last_Error Error 'Table 't1' already exists' on query. Default database: 'test'. Query: 'RENAME TABLE t2 TO t1' |
|
Complete test case |
--source include/have_binlog_format_row.inc
|
--source include/master-slave.inc
|
|
|
CREATE TABLE t1 (a INT); |
CREATE TABLE t2 (b INT); |
SET AUTOCOMMIT = 0; |
--error ER_CANT_CREATE_TABLE
|
CREATE OR REPLACE TABLE t1 ENGINE=PERFORMANCE_SCHEMA AS SELECT 1 AS x; |
COMMIT; # Just in case, doesn't make any difference |
RENAME TABLE t2 TO t1; |
|
|
--sync_slave_with_master
|
|
|
# Cleanup
|
--connection master
|
DROP TABLE IF EXISTS t2, t1; |
Attachments
Issue Links
- relates to
-
MDEV-25292 Atomic CREATE OR REPLACE TABLE
-
- In Testing
-