Bug #97101 Allow CREATE TABLE ... SELECT ... to work with group replication

• Submitted: 4 Oct 2019 4:53
• Modified: 6 Oct 2020 11:05
• Reporter: Yoseph Phillips
• Status: Closed
• Category: MySQL Server: Group Replication
• Severity: S4 (Feature request)
• OS: Any
• CPU Architecture: Any

[4 Oct 2019 4:53] Yoseph Phillips

Description:
Allow CREATE TABLE ... SELECT ... to work with group replication.

We use enforce-gtid-consistency and row based replication on all instances.

Currently this works as documented, so it is not a bug.

However it seems possible that this could be treated as syntactic sugar for doing this as two statements (one for CREATE and one for INSERT).

It seems like this limitation was added to handle situations where some instances use row based replication and other instances use statement or mixed mode replication. 

Commonly row based replication is used on all instances and so it seems that this is making people rewrite all of the CREATE TABLE ... SELECT ... statements without a good reason for it.

Even adding another variable so that DBAs could state that they are using the same replication mode on all instances would be better than having to rewrite all of these statements.

How to repeat:
CREATE TABLE testTable (testColumn INT NOT NULL, PRIMARY KEY (testColumn))
SELECT 1 AS testColumn;

Currently we always receive the error message: 'Statement violates GTID consistency: CREATE TABLE ... SELECT;'

[6 Oct 2020 11:05] MySQL Verification Team

Hi,

This was not supported 'cause this creates two transactions "create" and "insert". We already fixed this in 8.0.21

mysql [localhost:8021] {msandbox} (test) > CREATE TABLE testTable (testColumn INT NOT NULL, PRIMARY KEY (testColumn))
    -> SELECT 1 AS testColumn;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql [localhost:8021] {msandbox} (test) > select * from testTable;
+------------+
| testColumn |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

mysql [localhost:8021] {msandbox} (test) >