tencent cloud

Feedback

Common Errors

Last updated: 2022-06-22 16:19:20

    This document describes some common errors and how to fix them.

    Common MySQL Errors

    Common errors reported in the MySQL system during database migration, sync, or subscription and their solutions are as detailed below:

    Error Code Description Scenario Error Message Analysis and Solution
    1227 There is a permission problem. Data migration, sync, or subscription Error 1227: Access denied.
  • Error analysis
    The account running the task doesn't have the permission to connect to the source data.
  • Solution
    Authorize the account running the task.
  • 1040 There are too many database connections. Data migration or sync Error 1040: Too many connections.
  • Error analysis
    There are too many connections to the database.
  • Solution
    Increase the maximum number of connections to the source database (max_connections) or run the task again after the business traffic drops.
  • 1045 There is a permission problem. Data migration, sync, or subscription Error 1045 (28000): Access denied for user '{{xx}}'@'{{xx}}' (using password: YES)
  • Error analysis
    The account running the task doesn't have the permission to connect to the source or target database.
  • Solution
    Authorize the account running the task.
  • 1062 There is a primary key conflict. Data sync Error 1062: Duplicate entry '{{xx}}' for key 'PRIMARY', related tables: '{{xx}}'.
  • Error analysis
    If you select Report for Primary Key Conflict Resolution in data sync, DTS will report an error when encountering a primary key conflict between the source and target databases.
  • Solution
    Modify or delete the primary key in the particular table in the target database and run the task again.
  • 1071 The index field is too long. Data migration or sync Error 1071 (42000): Specified key was too long; max key length is 767 bytes.
  • Error analysis
    By default, a single-field index in the InnoDB engine can contain up to 767 bytes, i.e., 384 two-byte fields or 256 three-byte fields. GBK, UTF-8, and utf8mb4_unicode_ci are two-, three-, and four-byte character sets respectively.
    On MySQL 5.6 or later, all MyISAM tables will be automatically converted to InnoDB tables. Therefore, if your self-built database has a composite index column containing more than 767 bytes, the same table creation statements that can normally run in your self-built database cannot properly run on MySQL 5.6 or later.
  • Solution
    Shorten the length of the composite index column of the abnormal row in the file.
    Example:
    create table test(test varcahr(255) primary key)charset=utf8;
    -- Succeeded
    create table test(test varcahr(256) primary key)charset=utf8;
    -- Failed
  • 1146 A DDL statement changing the table structure is executed in the source database during data export. Data migration Error 1146: Table '{{xx}}' doesn't exist on query. Default database: '{{xx}}'.
  • Error analysis
    1. The source or target instance is isolated or deactivated.
    2. A DDL statement changing the table structure is executed in the source database.
  • Solution
    Check whether the corresponding table exists in the source or target database; if so, submit a ticket for assistance; if not, check whether the error is caused by above factors and create the task again.
  • 1213 A deadlock is caused by double write to the source and target databases. Data sync Error 1213: Deadlock found when trying to get lock; try restarting transaction, related tables: '{{xx}}'.
  • Error analysis
    The write operation performed by DTS in the target database conflicts with the write operation performed by you, causing a deadlock.
  • Solution
    1. Kill the deadlock process and create the task again.
    2. We recommend you control the lock logic for UPDATE operations in the instance, add an index to tables, and use row lock as much as possible to reduce lock overheads.
  • 1236 There is a source database binlog problem. Data migration, sync, or subscription Error 1236 (HY000): Cannot replicate because the master purged required binary logs. Replicate the missing transactions from elsewhere, or provision a new slave from backup……
  • Error analysis
    The binlog retention period in the source database is short, so the binlog has already been cleared or at an incorrect position when DTS pulls data.
  • Solution
    Check whether the binlog retention period (expire_logs_days) set in the source database meets the business requirements. We recommend you set it to above one day and create the task again.
  • 1414 A DDL statement changing the table structure is executed in the source database during data export. Data migration Error 1414: Table definition has changed, please retry transanction.
  • Error analysis
    DDL statements changing the table structure cannot be executed during data export from the source database; otherwise, an error will be reported.
  • Solution
  • Create the migration task again.

    Common DTS Errors

    Common errors reported in the DTS system during database migration, sync, or subscription and their solutions are as detailed below:

    Error Description Scenario Error Message Analysis and Solution
    The source database has a slow SQL statement, so the table fails to be locked. Data migration or sync Find Resumable Error, src db has long query sql, fix it and try it later.
  • Error analysis
    If the source database has a slow SQL statement, to prevent the source database business from being affected, DTS needs to wait for the execution of the slow SQL statement to complete before locking the table and exporting data. The default lock time is 60 seconds, after which table locking will fail, and the task will report an error.
  • Solution
    Process the slow SQL statement in the source database or create the task again after its execution is completed.
  • The database cannot be connected to. Data migration, sync, or subscription dial tcp {{*}}: connect: connection refused.
  • Error analysis
    The source or target database is isolated or deactivated during task execution, so DTS can't connect to it.
  • Solution
    Check the source or target database status and create the task again.
  • The binlog parameter doesn't meet the format requirements. Data migration, sync, or subscription
    • Statement binlog format unsupported:{{xx}}.
    • binlog must ROW format, but MIXED now.
    • binlog row before/after image not full, missing column {{xx}}, binlog posistion:{{xx}}, gtid:{{*}}.
  • Error analysis
    The binlog_format and binlog_row_image parameters in the source database need to be set to ROW and FULL respectively. Modified parameters can take effect only after the thread is restarted.
  • Solution
    Modify the parameter according to the error message as instructed in Binlog Parameter Check and create the task again.
  • Contact Us

    Contact our sales team or business advisors to help your business.

    Technical Support

    Open a ticket if you're looking for further assistance. Our Ticket is 7x24 avaliable.

    7x24 Phone Support