Skip navigation links

Additional languages
MySQL 5.1 Reference Manual :: 16 Replication :: 16.3 Replication Notes and Tips :: 16.3.1 Replication Features and Issues :: 16.3.1.3 Replication of CREATE TABLE ... SELECT Statements
16.3.1.4 Replication with Differing Tables on Master and Slave »
Section Navigation      [Toggle]

16.3.1.3. Replication of CREATE TABLE ... SELECT Statements

This section discusses the rules that are applied when a CREATE TABLE ... SELECT statement is replicated.

Note

CREATE TABLE ... SELECT always performs an implicit commit (Section 12.4.3, “Statements That Cause an Implicit Commit”).

Statement succeeds.  If the CREATE TABLE ... SELECT statement succeeds on the master, then it is replicated as follows:

  • STATEMENT or MIXED format.  The CREATE TABLE ... SELECT statement is itself replicated.

  • ROW format.  The statement is replicated as a CREATE TABLE statement followed by a series of binwrite events (that is, binary inserts).

Statement fails.  The failure of a CREATE TABLE ... SELECT is handled according to the following criteria:

  • No IF NOT EXISTS option.  If the CREATE TABLE ... SELECT does not contain an IF NOT EXISTS option, then the statement has no effect. However, the implicit commit caused by the statement is logged. This is true regardless of the replication format, storage engine used, and the reason for which the statement failed.

  • Statement uses IF NOT EXISTS.  If the CREATE TABLE ... SELECT statement includes the IF NOT EXISTS option and fails, the failure is handled according to the replication format. If the row-based format is in use, the action taken depends additionally on whether or not the table to be created uses a transactional or non-transactional storage engine, and on the reason for the failure:

    • STATEMENT or MIXED format.  When using statement-based or mixed-format replication, the CREATE TABLE IF NOT EXISTS ... SELECT is logged with an error.

    • ROW format.  When row-based replication is used, failure of a CREATE TABLE ... SELECT that includes IF NOT EXISTS is handled differently depending on the reason for the failure, as shown in the following diagram:

      CREATE TABLE IF NOT EXISTS ... SELECT Failure Handling (RBR)