Mysql 8 Command on Ddl Error Continue

Community Blog Solving the DTS-077100 Error During Data Synchronization

Solving the DTS-077100 Error During Data Synchronization

This article explains a series of possible error scenarios while working with DTS.

Errors occur when DTS synchronizes DML/DDL data to the destination database table. They are usually caused by some problems with the destination database.

First of all, DTS does not support the scenario where triggers are built on the destination table. In this case, any error may occur (including data quality problems such as data inconsistency.)

1. Errors List

                com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '3205385' for key 'PRIMARY'.              

Primary Key or Unique Key Conflict Error

Reason: This error is generally caused by the trigger on the destination table 'xxx'.'xxx'. (DTS operation on the 'xxx'.'xxx' table does not cause primary key or unique key conflicts but is caused by the internal sql of the trigger on this table.)

Solution: Delete the trigger on destination table 'xxx'.'xxx' and click restart to resume

                com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column '***' in 'field list'.              

Errors Saying the Column Does Not Exist

Reason: This error is generally caused by the user deleting the column of ' *' on the destination table 'xxx'.'xxx'. (If the user really does not need the column, the column can be deleted from the source table, and DTS will synchronize the DDL of the deleted column to the destination table automatically. If the user wants to keep the column on the source table, the user should choose to filter the column when creating the task.)

Solution: The user manually creates the column ' 'on the destination table 'xxx'.'xxx' and clicks restart* to resume.

                com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'xxx.xxx' doesn't exist.              

Errors Saying the Table Does Not Exist

Reason: This error is generally caused by the user deleting the table on the destination table 'xxx'.'xxx'. (If the user really does not need the column, the column can be deleted from the source table, and DTS will synchronize the DDL of the deleted column to the destination table automatically. If the user wants to keep the column on the source table, the user should choose to filter the column when creating the task.)

Solution: The user manually creates the table 'xxx'.'xxx' on the destination database and clicks restart to resume.

                com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column '***' cannot be null.              

Errors Saying the Column Is Not Allowed to Be Null

Reason: This error is generally caused when the user modifying the column on the destination table 'xxx'.'xxx' is not allowed to be null.

Solution: The user changes the column manually to allow null on the destination database table and clicks restart to resume.

                com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (...).              

Foreign Key Constraint Violation Error

Reason: This error is generally caused by the user's destination database not supporting session-level prohibition of foreign key checking (MySQL branch version developed by the user.)

Solution: The user deletes the foreign key constraint of the response from the destination database and table manually and clicks restart to resume.

                java.sql.SQLException: The MySQL server is running with the --read-only option so it cannot execute this statement.              

The Destination Database Only Allows Read Operations (--read-only)

Reason: The destination database is set to read-only, which may be due to user operation or RDS instance expiration (full disk/internal primary and secondary switching.)

Solution: The user can set the destination database to be writable or renew the RDS instance (upgrade RDS instance disk space) and then click restart to restore it.

                java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction.              

The Destination Database Receives a Lock Timeout When Performing a Write Operation (Lock Wait Timeout Exceeded)

Reason: The destination database may have long queries or DDL statements related to the table.

Solution: After the related long query or DDL execution of the destination database is completed, click restart to resume.

                com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: INSERT(DELETE/UPDATE) command denied to user 'xxx'@'***' for table '*'.              

The Error of No Permission When Performing Write Operations on the Destination Database (INSERT(DELETE/UPDATE Command Denied to User))

Reason: The target RDS disk is full or locked out.

Solution: After upgrading RDS disk space or renewing, click restart to resume.

                com.mysql.jdbc.PacketTooBigException: Packet for query is too large (12014078 > 4194304). You can change this value on the server by setting the max_allowed_packet' variable.              

Writing SQL in the Destination Database Exceeds the max_allowed_packet Limit (Packet for query is too large...)

Reason: The max_allowed_packet setting of the destination database is too small.

Solution: Resize the max_allowed_packet with high permissions on the destination instance (SET global max_allowed_packet= ) and click restart* to resume

                redis.clients.jedis.exceptions.JedisConnectionException: Could not get a resource from the pool.              

Failed to Obtain a Connection While Performing a Write Operation on the Destination Database

Reason: The destination Redis library cannot be connected.

Solution: After confirming the destination Redis library is accessible, click restart to resume.

This article was originally published on developer.aliyun.com/.

ApsaraDB

204 posts | 17 followers

Follow

You may also like

Comments

ApsaraDB

204 posts | 17 followers

Follow

Related Products

• ApsaraDB RDS for SQL Server

  An on-demand database hosting service for SQL Server with automated monitoring, backup and disaster recovery capabilities

  Learn More

• Database Overview

  Fully managed and less trouble database services

  Learn More

• Data Transmission Service

  Supports data migration and data synchronization between data engines, such as relational database, NoSQL and OLAP

  Learn More

• Time Series Database (TSDB)

  TSDB is a stable, reliable, and cost-effective online high-performance time series database service.

  Learn More

coxwastual.blogspot.com

Source: https://www.alibabacloud.com/blog/solving-the-dts-077100-error-during-data-synchronization_598444

Share this post