4.1.7 Replication of CREATE TABLE ... SELECT Statements

MySQL applies these rules when CREATE TABLE ... SELECT statements are replicated:

• CREATE TABLE ... SELECT always performs an implicit commit (Statements That Cause an Implicit Commit).

• If the destination table does not exist, logging occurs as follows. It does not matter whether IF NOT EXISTS is present.

  • STATEMENT or MIXED format: The statement is logged as written.

  • ROW format: The statement is logged as a CREATE TABLE statement followed by a series of insert-row events.

• If the CREATE TABLE ... SELECT statement fails, nothing is logged. This includes the case that the destination table exists and IF NOT EXISTS is not given.

• If the destination table exists and IF NOT EXISTS is given, MySQL 8.0 ignores the statement completely; nothing is inserted or logged.

When statement-based replication is in use, MySQL 8.0 does not allow a CREATE TABLE ... SELECT statement to make any changes in tables other than the table that is created by the statement. This is not an issue when using row-based replication, because the statement is logged as a CREATE TABLE statement with any changes to table data logged as row-insert events, rather than as the entire CREATE TABLE ... SELECT.