MySQL: DDL Replication

Learn about DDL replication in MySQL.

Limitation of DDL Replication in MySQL

• Case 1: If DDL or the TRACKSCHEMACHANGES is enabled , and the table is in the capture list. Extract abends with the following error:

  DDL statement "CREATE TABLE AS SELECT" is detected for table 'table-name' at binary log 
  number 'binlog number' and offset 'binlog offset'. The DDL operation "CREATE TABLE AS
  SELECT" is not supported. Execute all 'CREATE TABLE AS SELECT' DDL operations manually to the target
  database and start Extract by adding Extract parameter TRANLOGOPTIONS WARNCREATEASSELECT. 
  Once Extract gets past the 'CREATE TABLE AS SELECT' operation, restart Extract after disabling
  the parameter TRANLOGOPTIONS WARNCREATEASSELECT.

  You need to take actions as suggested in the error message.

  When the parameter TRANLOGOPTIONS WARNCREATEASSELECT is used in the Extract parameter file, Extract continues after issuing the following warning message in the Extract report, in case of subsequent 'CREATE TABLE AS SELECT' operation is found.

  "DDL statement 'CREATE TABLE AS SELECT' is detected for table 'table-name' at binary log
  number 'binlog number'  and offset 'binlog offset'. The DDL operation 'CREATE TABLE AS SELECT'
  is not supported. The DDL and the associated DMLs are ignored."

• Case 2: When DDL replication or TRACKSCHEMACHANGES is disabled and the table is in the Extract list.

  When both DDL replication / TRACKSCHEMACHANGES are not enabled in the capture, the DDL and
  associated DML operations with CREATE TABLE AS SELECT are ignored. User needs to issue the
  same "CREATE TABLE AS SELECT" in the target as it was issued in the source.

• Case 3: When the table is not in the capture list , then irrespective of the DDL or TRACKSCHEMACHANGES enabled or not , all the table operations are ignored.

Note:

See TRANLOGOPTIONS WARNCREATEASSELECT in the Parameters and Functions Reference for Oracle GoldenGate for details.