Oracle Cloud Infrastructure Documentation

Limitations

Inbound replication to MySQL HeatWave Service does not support some of the configurations that are possible for MySQL replication.

  • Only row-based replication is supported. This binary log format is the default in MySQL version 5.7 and higher. Statement-based replication and mixed replication are not supported.
    Note

    MySQL 5.7 has a known bug where the DROP TEMPORARY TABLE statement is wrongly logged in row-based binary log and causes row-based replication to fail. MySQL 5.7 is in Sustaining Support since Oct 2023 and no further bug fixes will be delivered per Oracle Lifetime Support Policy.
  • Only asynchronous replication is supported. Semi-synchronous replication is not supported.
  • Only replication from a single source is supported. Multi-source replication is not supported.
  • A channel can support a maximum of 30 channel filters.
  • Changes to the mysql schema are not replicated and cause replication to stop.
  • When a high availability DB system is upgraded, the inbound replication channel is suspended. The channel is resumed when the upgrade process is complete.
  • Only statements which the applier username has privilege to execute can be replicated. Replication fails if the applier username has insufficient privilege to execute any statement read from the binary logs of the source server. The list of privileges is restricted to the privileges granted to the administrator of the DB system. See Default MySQL Privileges.
  • Prior to MySQL 8.2, the username in the DEFINER clause of CREATE VIEW, CREATE PROCEDURE, and CREATE FUNCTION statements must be the same as the applier username in order to replicate these statements successfully. In MySQL 8.2 or higher, the applier user must have the SET_ANY_DEFINER privilege to replicate CREATE VIEW, CREATE PROCEDURE, and CREATE FUNCTION statements with DEFINER clause containing other username.
  • DB systems (MySQL 8.3.0 or higher) created prior to May 2024 that do not have the TRANSACTION_GTID_TAG privilege need to be upgraded in order to replicate transactions with GTID tags.
  • In asynchronous replication, both source and target DB systems can be Read-Write DB systems and the associated HeatWave cluster can be managed independently. However, since there is a replication channel in between, actions performed at the source can impact the functionality of the target DB system.
  • When the ALTER TABLE <table_name> SECONDARY_LOAD and ALTER TABLE <table_name> SECONDARY_UNLOAD statements are replicated to a target DB system, they do not load or unload data in the HeatWave cluster attached to the target DB system, if any.

    This helps set up replication between DB systems wherein the source has a HeatWave cluster but the target does not have a HeatWave cluster, and also in high availability DB systems where only the primary DB system has a HeatWave cluster. However, when the ALTER TABLE <table_name> SECONDARY_LOAD statement is replicated, the secondary load metadata is updated in the MySQL data dictionary. Since the secondary load metadata is consulted at the time of recovery, if the target DB system restarts, the new tables whose secondary load DDL statements are replicated, are also considered for reloading in the HeatWave cluster on the target DB system.

  • You can independently load or unload tables in the HeatWave cluster on both source and target DB systems. But, if you load a table in the HeatWave cluster on the target DB system first and then attempt to load the same table in the HeatWave cluster on the source DB system, it may cause the replication channel to break, and an error similar to the following is generated in such cases: 
    Replica SQL for channel 'replication_channel': Worker 1 failed executing transaction 
'<transaction_id>' at source log binary-log.<number>, end_log_pos <number>;
Error 'Secondary engine operation failed. Table already has a secondary engine defined.' on query.
Default database: ''. Query: 'ALTER TABLE <table_name> SECONDARY_ENGINE=RAPID', Error_code: MY-003889
    This happens because the ALTER TABLE <table_name> SECONDARY_ENGINE = RAPID DDL statement that is generated by the load is replicated but fails on the target DB system because the corresponding table is already loaded to the HeatWave cluster and has SECONDARY_ENGINE = RAPID already set.
  • When the HeatWave cluster is deleted or recreated on the source DB system, all previously-loaded tables are unloaded from the HeatWave cluster on the source DB system, and the same tables are unloaded from the HeatWave cluster on the target DB system as well. This is because when the HeatWave cluster on the source DB system is deleted or recreated, the SECONDARY_ENGINE setting for all previously-loaded tables in the source DB system is reset to NULL. When the SECONDARY_ENGINE setting for a table is reset on the source DB system, then the SECONDARY_ENGINE setting for the same table is reset to NULL on the target DB system as well, which results in the table being unloaded from the HeatWave cluster on the target DB system.
  • If you want to replicate Lakehouse external tables, it is recommended not to set the channel to GENERATE_IMPLICIT_PRIMARY_KEY for tables without primary key. Replication breaks when altering an InnoDB table with a generated primary key to a Lakehouse table.
  • In versions before 8.4.0-u2, if the target DB system has a stopped (inactive) HeatWave cluster, the replication channel cannot apply any DDL statement that creates or alters a Lakehouse external table. The replication channel will break.
  • In versions before 8.4.0-u2, even though the data load is skipped, the replication channel can break if MySQL HeatWave Lakehouse is enabled in the target DB system and it does not have read and list access to the files specified in the engine attribute of the Lakehouse external table.