Real-time synchronization FAQ

What data sources does real-time synchronization support?

For data sources supported by real-time synchronization, see Supported data sources.

Why is the latency of my real-time synchronization task high?

When you query table data produced by a real-time synchronization task and find that the data has not been synchronized, the cause may be high latency in the real-time synchronization task. You can go to the Real-time synchronization tasks page in Operation Center to check whether the Business Latency value is too large. For more information, see Solutions for high latency in real-time synchronization tasks.

If the business latency is high, possible causes are as follows:

Why is using the public internet not recommended for real-time synchronization tasks?

Using the public internet for real-time synchronization tasks has the following risks:

• The network may be unstable, and packet loss may occur frequently, which affects synchronization performance.

• Security is low.

Column format in real-time synchronization

When Data Integration synchronizes data from MySQL, Oracle, Loghub, or PolarDB to DataHub or Kafka in real time, it adds five additional columns to the destination for metadata management, sorting, deduplication, and other operations. For more information, see Additional columns in real-time synchronization.

How does real-time synchronization handle TRUNCATE operations?

Real-time synchronization supports TRUNCATE operations, which take effect during incremental and full merge. If you choose to ignore TRUNCATE, extra data may appear during real-time data synchronization.

How can I improve the speed and performance of real-time synchronization?

If the write speed is slow, you can increase the write concurrency, and adjust the JVM parameters. JVM parameters are not related to the number of synchronized databases but are related to the change frequency. If the current resource group machine allows, the more memory you allocate, the lower the Full GC frequency, and the higher the real-time synchronization performance. Set Concurrency to 3. In the Basic Configuration panel of the task, set JVM Parameters to -Xms8192m -Xmx8192m.

Can I run real-time synchronization tasks in the console?

Real-time synchronization tasks cannot be run directly in the DataWorks console. After you configure a real-time synchronization task, you must commit and deploy the real-time synchronization node, and then run the node in the production environment. For more information, see Deploy a real-time synchronization node.

Why does real-time synchronization slow down when using a MySQL data source?

One reason is that the volume of binlogs to process increases. Because binlogs are generated at the instance level, even if the synchronization task is configured to synchronize only table A, changes to other tables such as table B or table C also generate binlogs. These additional binlogs slow down the synchronization processing speed.

Why does memory usage differ between selecting a single database and multiple databases in real-time synchronization?

When you select two or more databases, the task enters the "full-instance" real-time synchronization mode, which consumes more resources than two separate tasks each synchronizing a single database.

What DDL policies does real-time synchronization support?

The processing methods are as follows:

DDL type categories:

Considerations for source DDL and DML operations when writing to a destination data source

• After a column is added on the source and the DDL is successfully executed on the destination, the following limitations apply:

  • After a column with a DEFAULT VALUE is added, the new column in the destination table does not have a value and remains NULL. When new data is subsequently inserted into the added column on the source, the real-time synchronization task synchronizes the new data to that column.

  • After a VIRTUAL column is added, the new column in the destination table does not have a value and remains NULL. When new data is subsequently inserted into the added column on the source, the real-time synchronization task synchronizes the new data to that column.

• For real-time synchronization from MySQL or PolarDB MySQL sources, we recommend that you append columns at the end when adding columns on the source. Do not add columns in the middle of existing columns. If adding columns in the middle is unavoidable on the source, note the following constraints:

  • In a full-plus-incremental solution, do not add columns in the middle during the full synchronization phase. Otherwise, data exceptions occur during the incremental synchronization phase.

  • During the incremental synchronization phase, the synchronization checkpoint reset time must be set to a point after the DDL event that added columns in the middle. Otherwise, subsequent incremental synchronization data may be abnormal.

• If data exceptions occur, you can re-run the full data initialization (you only need to remove the tables that had columns added in the middle and re-initialize those tables, rather than re-initializing all tables in the task) to restore the correct data.

Why is the latency high after a real-time synchronization task reading from PostgreSQL fails over?

This is a characteristic of the PostgreSQL database itself. If the latency is unacceptable, you can stop the task and restart it to perform a full-plus-incremental data synchronization.

How do I perform a full synchronization for an existing synchronization task?

In Data Integration, find the target synchronization task in the task list, and then click More > Rerun in the Actions column.

Does a DataWorks real-time synchronization task support configuring a dirty data threshold (errorLimit) or ignoring dirty data?

Real-time synchronization tasks do not support configuring a dirty data threshold (errorLimit) or ignoring dirty data. If dirty data is encountered during synchronization, the task is blocked.

Alternative solutions:

• Replace the real-time synchronization task with an hourly or minute-level scheduled batch synchronization task. Batch synchronization supports configuring a dirty data threshold and ignoring dirty data.

• Add a data processing node in the real-time synchronization pipeline to cleanse and filter data, preventing dirty data from reaching the destination.

• Locate and fix the source data based on the task error message to eliminate dirty data at the source.

When synchronizing data from a MySQL data source in real time, data is read initially but stops being read after a while. How do I resolve this?

1. Run the following command in the database to check the binlog file that the current database instance is writing to.

   show master status 

2. Compare the binlog file read in the logs. Search the logs for journalName=MySQL-bin.000001,position=50 to verify whether data is being written to the database.

3. If data is being written but the binlog is not advancing, contact your DBA for assistance.

Real-time synchronization tasks for Oracle, PolarDB, and MySQL repeatedly report errors

• Symptom: The real-time synchronization task repeatedly reports errors.

  When the source of a real-time synchronization task is an Oracle, PolarDB, or MySQL data source, DDL messages from the source are not processed by default. When DDL changes other than creating tables are performed, the real-time task reports an error and exits. In a checkpoint-based recovery scenario, the synchronization task may still report errors even if no DDL message is generated from the source.

  Note

  To avoid data loss or disorder during a certain period, do not use the Rename command to swap the names of two columns. For example, swapping the column names of column A and column B is not allowed.

• Cause: Real-time synchronization supports checkpoint-based recovery. To prevent data loss, when the synchronization is restarted, the offset may be rolled back to replay some data. This process may cause previously encountered DDL messages to be read again and trigger another alert.

• Solution:

  1. When DDL changes are made on the source, manually perform the corresponding DDL changes on the destination database.

  2. Start the real-time synchronization task and change the DDL message processing policy from Error to Ignore.

     Note

     Because checkpoint-based resumption still subscribes to this DDL event, the event is temporarily set to be ignored to prevent the task from failing again.

  3. Stop the real-time synchronization task, change the DDL message processing policy back from Ignore to Error, and restart the real-time synchronization task.

Kafka real-time synchronization error: Startup mode for the consumer set to timestampOffset, but no begin timestamp was specified.

Reset the start offset. In the Start dialog box of the task, find the Reset Offset option and select the Reset Offset check box to enable this feature.

The Reset Offset feature of Data Integration is used to reset the start position of data synchronization. You can use this feature when you need to restart synchronization from a specific point in time or data position. For example, if an error occurs during data synchronization or you need to re-synchronize part of the data, resetting the offset allows you to restart synchronization from a specified position to ensure data consistency and completeness.

MySQL real-time synchronization error: Cannot replicate because the master purged required binary logs.

MySQL real-time synchronization error: Cannot replicate because the master purged required binary logs. Replicate the missing transactions from elsewhere, or provision a new slave from backup.. This error may occur because the binlog record for the consumption offset cannot be found in MySQL. Check the binlog retention period of your MySQL instance and make sure the offset is configured within this time range when you start the synchronization task.

MySQL real-time synchronization error: MySQLBinlogReaderException

MySQL real-time synchronization error: MySQLBinlogReaderException: The database you are currently syncing is the standby database, but the current value of log_slave_updates is OFF, you need to enable the binlog log update of the standby database first. . This error may occur because binlog is not enabled for the standby database. If you want to synchronize data from the standby database, you need to enable cascading binlog on the standby database. Contact your DBA for assistance.

For more information about how to enable binlog, see Step 3: Enable MySQL binlog.

MySQL real-time synchronization error: show master status' has an error!

MySQL real-time synchronization error: show master status' has an error!. The error details are Caused by: java.io.IOException: message=Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation, with command: show master status. This error may occur because the data source does not have the required database permissions.

The account configured for the data source must have the SELECT, REPLICATION SLAVE, and REPLICATION CLIENT privileges on the database. For more information about how to grant the required privileges to the data source, see Step 2: Create an account and configure permissions.

MySQL real-time synchronization error: parse.exception.PositionNotFoundException: can't find start position forxxx

The synchronization cannot find the offset. Reset the offset.

MySQL real-time synchronization error: Can't get position by timestamp

Possible cause: The binlog corresponding to the specified synchronization start time has been purged by the source database. After the binlog is purged, the real-time synchronization task cannot obtain the offset based on that time point, resulting in this error.

Solution: On the Data Source Configuration page, enable the Support OSS Binlog Reading feature for the MySQL data source. After this feature is enabled, the synchronization task can read purged binlog from OSS log backups, preventing synchronization interruption caused by local binlog expiration.

MySQL real-time synchronization error: The database offset has expired. Select a new offset. The earliest available offset for the source database is xxx.

• Reset the offset: When you start the real-time synchronization task, reset the offset and select the earliest available offset for the source database.

• Adjust the binlog retention period: If the database offset has expired, consider adjusting the binlog retention period in the MySQL database, for example, to 7 days.

• Data synchronization: If data has already been lost, you can consider performing a full synchronization again or configuring a batch synchronization task to manually synchronize the lost data.

PolarDB real-time synchronization error:The mysql server does not enable the binlog write function. Please enable the mysql binlog write function first

• Possible cause: The loose_polar_log_bin parameter is not enabled for the source PolarDB data source.

• Solution: Enable the loose_polar_log_bin parameter. For more information, see Configure a PolarDB data source for real-time synchronization.

Real-time synchronization to Hologres error: permission denied for database xxx

When you synchronize data to Hologres in real time, you must grant the current operating user the admin permission on the Hologres instance (the permission to create schemas is required). For more information, see Manage Hologres permissions.

Real-time synchronization to MaxCompute error: ODPS-0410051:invalid credentials-accessKeyid not found

When you synchronize data to a MaxCompute data source in real time and use a temporary AccessKey pair, the temporary AccessKey pair automatically expires after 7 days, which causes the task to fail. The platform automatically restarts the task when it detects that a temporary AccessKey pair has caused a task failure. If you have configured alert rules of this type for the task, you will receive alert notifications.

Real-time synchronization from Oracle error: logminer doesn't init, send HeartbeatRecord

When a real-time synchronization task from Oracle initializes and searches for an appropriate synchronization checkpoint, it needs to load the previous archived log. If the archived log is large, the initialization phase may take 3 to 5 minutes to complete.

What do I do if a PostgreSQL real-time synchronization error indicates that the read/write splitting address or Replication mode is not supported?

Possible cause: PostgreSQL real-time synchronization (CDC) relies on the logical replication (Replication Mode) mechanism of the database and only supports connecting to the primary instance endpoint. Read/write splitting addresses, proxy addresses, and secondary instance addresses do not support this mechanism. Therefore, the task reports an error indicating that the read/write splitting address or Replication mode is not supported.

Solution: Change the PostgreSQL data source connection address in the data source configuration to the primary instance endpoint, save the configuration, test the connectivity again, and then re-run the real-time synchronization task after the test succeeds.