Diagnose issues for MySQL

Troubleshoot an error

The migration job process might incur errors during runtime.

  • Some errors, such as a bad password on the source database, are recoverable, meaning they can be fixed and the migration job resumes automatically.
  • Some are unrecoverable, such as a lost binlog position, meaning the migration job needs to be restarted from the beginning.

When an error occurs, the migration job status changes to Failed, and the substatus reflects the last status before failure.

To troubleshoot an error, navigate to the failed migration job to view the error and follow the steps outlined in the error message.

To view more details about the error, navigate to Cloud Monitoring using the link on the migration job. The logs are filtered to the specific migration job.

In the following table, you can find some examples of issues and how they can be solved:

For this problem... The issue might be... Try this...
Error message: Specified key was too long; max key length is 767 bytes. The source database instance may have the variable innodb_large_prefix set. Set the innodb_large_prefix flag to ON when creating the destination instance, or update the existing destination instance with the flag.
Error message: Table definition has changed. There were data definition language (DDL) changes during the dump process. Avoid DDL changes during the dump process.
Error message: Access denied; you need (at least one of) the SUPER privilege(s) for this operation. There could be an event, a view, a function, or a procedure in the source database using super user@localhost (such as root@localhost). This is not supported by Cloud SQL. See more information about DEFINER usage in Cloud SQL.
Error message: Definer user 'x' does not exist. Please create the user on the replica. The user doesn't exist on the replica. See more information about DEFINER usage in Cloud SQL.
Error message: ERROR 1045 (28000) at line {line_number}: Access denied for user 'cloudsqlimport'@'localhost. There is a DEFINER in the source database that doesn't exist in the replica. See more information about DEFINER usage in Cloud SQL.
Error message: Lost connection to MySQL server during query when dumping table. The source database may have become unreachable, or the dump contained packets too large. Verify the source database is up and reachable, or use manual dump with the max_allowed_packet option.
Error message: Got packet bigger than 'max_allowed_packet' bytes when dumping table. The packet was larger than allowed by settings. Use manual dump with the max_allowed_packet option.
Initial data migration was successful, but no data is being replicated. There may be conflicting replication flags. Check these flag settings.
Initial data migration was successful, but data replication stopped working after a while. There could be many causes. Try these suggestions.
Error message: mysqld check failed: data disk is full. The data disk of the destination instance is full. Increase the disk size of the destination instance. You can either manually increase the disk size or enable auto storage increase.
Failure connecting to the source database instance. There was a connectivity issue between the source database instance and the destination instance. Follow the steps in the debugging connectivity article.
Migration from a managed database (Amazon RDS/Aurora) doesn't start. Migrating from a managed source database without SUPERUSER privileges requires a brief downtime in the beginning of the migration. Follow the steps in this article.
Binlog is configured incorrectly on the source database. The Binlog definitions are incorrect. For continuous MySQL migration jobs, make sure you follow the required binlog definitions.
Failure to find the dump file. DMS is unable to find the dump file provided. This can happen if the migration job can't find the dump file at the given location.
  1. Check the dump path to ensure a proper file is there, or change the path.
  2. If you change the path, then use a PATCH request to ensure the job uses it.
  3. Restart the migration job.
Unable to resume replication as binlog position was lost. The binlog position was lost and the migration job couldn't be resumed. This error can occur when the replication process is paused for a long time, which causes the binlog position to be lost. Migration jobs shouldn't be paused for periods of time that approach the binlog retention period. Restart the migration job.
Failure running migration job due to incompatible source and destination database versions. The source database version provided is incompatible with the destination database version. Make sure that the destination database version is the same or one major version above the source destination version, then create a new migration job.
Error Message: Unable to connect to source database server. Database Migration Service can't establish a connection to the source database server. Make sure that the source and destination database instances can communicate with each other, and that you've completed all of the required prerequsities that appeared when you defined your settings for the migration job.
Error Message: Timeout waiting for no write traffic on source. Migrating from a managed source database without SUPERUSER privileges results in a brief downtime during the beginning of the migration. Follow the steps in Migrating from RDS MySQL without SUPERUSER privileges.
Error Message: ERROR 1146 (42S02) at line {line_number}: Table '{table_name}' doesn't exist. There might be an inconsistency between the value of the lower_case_table_names flag for the source database and the value of the flag for the destination Cloud SQL instance. Set the value of the flag for the Cloud SQL instance to match the value of the flag for the source database.
Error Message: ERROR 1109 (42S02) at line {line_number}: Unknown table '{table}' in {database}. Some objects on the source database, such as views, functions, stored procedures, or triggers, reference a table that no longer exists on the database. Check to see if these objects exist. If they do, then either drop the objects from the source database or add the table that the objects are referencing to the source database.

Specified key was too long; max key length is 767 bytes

You see the error Specified key was too long; max key length is 767 bytes.

The issue might be

The source database may have the variable innodb_large_prefix set. This allows index key prefixes longer than 767 bytes. The default value is OFF for MySQL 5.6.

Things to try

Set the innodb_large_prefix flag to ON when creating the destination database, or update the existing destination database with the flag.


Table definition has changed

You see the error Table definition has changed.

The issue might be

There were DDL changes during the dump process.

Things to try

Don't modify tables or perform any other DDL changes during the dump process.You can use a script to verify that DDL operations are stopped.


Access denied; you need (at least one of) the SUPER privilege(s) for this operation

You see the error Access denied; you need (at least one of) the SUPER privilege(s) for this operation.

The issue might be

There could be an event, a view, a function, or a procedure in the source database using super user@localhost (such as root@localhost). This is not supported by Cloud SQL.

Things to try

Refer to this document on migrating a database with DEFINER clauses.


Error message: Definer user 'x' does not exist. Please create the user on the replica.

You see the error Definer user 'x' does not exist. Please create the user on the replica.

The issue might be

The root cause is that a user in the source database with the DEFINER clause doesn't exist on the replica database.

Things to try

Refer to this document on migrating a database with DEFINER clauses. You may need to create the user in the replica database.


Error message: ERROR 1045 (28000) at line {line_number}: Access denied for user 'cloudsqlimport'@'localhost

You see the error ERROR 1045 (28000) at line {line_number}: Access denied for user 'cloudsqlimport'@'localhost.

The issue might be

The root cause is that a user in the source database with the DEFINER clause doesn't exist on the replica database and that user is cross-referenced in the object definitions in the source database.

Things to try

Refer to this document on migrating a database with DEFINER clauses. You may need to create one or more users in the replica database.


Lost connection to MySQL server during query when dumping table

You see the error Lost connection to MySQL server during query when dumping table.

The issue might be

  • The source database instance may have become unreachable from the destination instance.
  • The source database may have tables with large blobs or long strings which require setting the max_allowed_packet to a larger number on the source database.

Things to try

  • Verify the source database instance is up and reachable.
  • Use manual dump with the max_allowed_packet option to dump the data and migrate with the dump file.
  • Increasing max_allowed_packet, will most likely require tweaking the net_read_timeout and net_write_timeout settings on the source database (generally it should be increased till the connection error stops).

Got packet bigger than 'max_allowed_packet' bytes when dumping table

You see the error Got packet bigger than 'max_allowed_packet' bytes when dumping table.

The issue might be

The packet was larger than allowed by settings.

Things to try

Create a migration job using a manual dump with the max_allowed_packet option to dump the data and migrate with the dump file.


No data is being replicated

Initial data migration was successful, but no data is being replicated.

The issue might be

A possible root cause could be your source database has replication flags which result in some or all database changes not being replicated over.

Things to try

Make sure the replication flags such as binlog-do-db, binlog-ignore-db, replicate-do-db or replicate-ignore-db are not set in a conflicting way.
Run the command show master status on the source database to see the current settings.


Initial data migration was successful but data replication stopped working after a while

The initial data migration was successful but data replication stopped working after a while.

The issue might be

There can be many root causes for this issue.

Things to try

  • Check the replication metrics for your destination instance in the Cloud Monitoring UI.
  • The errors from the MySQL IO thread or SQL thread can be found in Cloud logging in the mysql.err log files.
  • The error can also be found when connecting to the destination instance. Run the command SHOW SLAVE STATUS, and check for these fields in the output:
    • Slave_IO_Running
    • Slave_SQL_Running
    • Last_IO_Error
    • Last_SQL_Error
    • If you got the error fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file' in Last_IO_Error, this could be due to insufficient binlog retention setting on source instance.

      If you got the error error connecting to master 'USER_NAME@SOURCE_HOST:SOURCE_PORT' - retry-time: RETRY_TIME retries: RETRIES, this could be due to the destination instance failing to reconnect to the source because of a connectivity or authentication issue.


mysqld check failed: data disk is full

You see the error mysqld check failed: data disk is full.

The issue might be

The data disk of the destination instance is probably full.

Things to try

Increase the disk size of the destination instance.


Failure connecting to source database

Failure connecting to source database.

The issue might be

There was a connectivity issue between the source database instance and the destination instance.

Things to try

Follow the steps in the debugging connectivity article.


Migration from a managed database (Amazon RDS/Aurora) doesn't start

The migration job fails to start.

The issue might be

Migrating from a managed source database without SUPERUSER privileges requires a brief downtime in the beginning of the migration.

Things to try

Follow the steps in this article.


Binlog is configured incorrectly on the source database

You see an error indicating a problem with the binary logs.

The issue might be

This can happen for continuous MySQL migration jobs if the binlog configuration is incorrect on the source database.

Things to try

Make sure you follow the definitions here.


Failure reading the provided dump file

You see an error indicating a problem with the dump file.

The issue might be

DMS is unable to find the dump file provided.

Things to try

  1. Check the dump path to ensure a proper file is there, or change the path
  2. If you change the path, then use a PATCH request to ensure the job uses it.
  3. Restart the migration job.

Unable to resume replication as binlog position was lost

The binlog position is lost.

The issue might be

This error can occur when the replication process is paused for a long time, which causes the binlog position to be lost. Migration jobs shouldn't be paused for periods of time that approach the binlog retention period.

Things to try

Restart the migration job.


Failure running migration job due to incompatible source and destination database versions

The source and destination database versions are not a supported combination.

The issue might be

The source database version provided is incompatible with the destination database version.

Things to try

Make sure that the destination database version is the same or one major version above the source destination version, then create a new migration job.


Can't connect to the source database server

You see the error Unable to connect to source database server.

The issue might be

Database Migration Service can't establish a connection to the source database server.

Things to try

Verify that the source and destination database instances can communicate with each other. Then, make sure that you've completed all of the required prerequsities that appeared when you defined the settings for your migration job.