Bug #95994 CREATE TABLESPACE wont respect SPCO and slave may go to ERROR state

• Submitted: 26 Jun 2019 9:07
• Modified: 18 Nov 2019 19:13
• Reporter: Deepthi Eranti Sreenivas
• Status: Closed
• Category: MySQL Server: Replication
• Severity: S3 (Non-critical)
• Version: 8.0
• OS: Any
• CPU Architecture: Any

[26 Jun 2019 9:07] Deepthi Eranti Sreenivas

Description:
Slave preserve commit order: For multithreaded slaves, the setting 1 for this variable ensures that transactions are externalized on the slave in the same order as they appear in the slave's relay log, and prevents gaps in the sequence of transactions that have been executed from the relay log. 

CREATE TABLESPACE is not committed in order on MTS slave even if SPCO = ON.

How to repeat:
./mtr rpl_mts_spco_create_tablespace --mysqld=--slave-parallel-workers=4 --mysqld=--slave-parallel-type=1 --mysqld=--slave-preserve-commit-order=1 --mysqld=--binlog-format=row

--source include/master-slave.inc

CREATE TABLE t1(a INT PRIMARY KEY);
CREATE TABLE t2(a INT PRIMARY KEY);

--source include/sync_slave_sql_with_master.inc

--source include/rpl_connection_slave.inc
BEGIN;
INSERT INTO t1 VALUES(1);

--source include/rpl_connection_master.inc
SET @@GLOBAL.DEBUG='+d,set_commit_parent_100';
BEGIN; INSERT INTO t1 VALUES (1); COMMIT; BEGIN; INSERT INTO t1 VALUES (2); COMMIT; CREATE TABLESPACE ts1;

--source include/rpl_connection_slave.inc
--sleep 8
SHOW PROCESSLIST;
--query_vertical SELECT NAME FROM information_schema.INNODB_TABLESPACES WHERE NAME LIKE 'ts1'

COMMIT;

--sleep 5
--echo # Wait for slave to stop
SHOW PROCESSLIST;

--echo # table t1 wont have value 2
SELECT * FROM t1;

--echo # Check if ts1 exists
--query_vertical SELECT NAME FROM information_schema.INNODB_TABLESPACES WHERE NAME LIKE 'ts1'

--echo # We cant create tablespace ts1
CREATE TABLESPACE ts1;

[18 Nov 2019 19:13] Daniel Price

Posted by developer:
 
Fixed as of the upcoming 8.0.19 release, and here's the changelog entry:

CREATE TABLESPACE failed with an error indicating that the tablespace
already exists. The error was due to the failure of a preceding CREATE
TABLESPACE operation where the DDL failed but related changes were not
rolled back due to rollback being disabled prior to transaction commit.
Rollback is now disabled after the transaction commits successfully.