Troubleshooting MySQL online migration failure in DMS

This post has been republished via RSS; it originally appeared at: Microsoft Data Migration articles.

First published on MSDN on Oct 16, 2018

Authored by nesin@microsoft.com

 

Overview

While performing an online migration of a MySQL database using the Azure Database Migration Service (DMS), a few of our customers have encountered a similar error message:

“'load data local infile \"R:\\\\repldata\\\\tasks\\\\fd954e8d-5a57-48e0-93f8-af811246c0f8\\\\data_files\\\\1\\\\LOAD00000277.csv\" into table ...;'","RetCode: SQL_ERROR SqlState: HY000 NativeError: 1213 Message: [MySQL][ODBC 5.3(w) Driver][mysqld-5.6.39.0]Deadlock found when trying to get lock; try restarting transaction","Failed to start load process for file '631'","Failed to load file '631'","Task 'fd954e8d-5a57-48e0-93f8-af811246c0f8' encountered a fatal error"]" }

 

Resolution

To troubleshoot the activity failure, perform the following steps:

    1. Run a command on target Azure Database for MySQL server using workbench or any other MySQL client tool to get the information about the last deadlock.



SHOW ENGINE INNODB STATUS;
Sample output:
------------------------

LATEST DETECTED DEADLOCK

------------------------

2018-10-05 21:11:48 87ef0

*** (1) TRANSACTION:

TRANSACTION 2053969, ACTIVE 21 sec setting auto-inc lock

mysql tables in use 1, locked 1

LOCK WAIT 3 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1

MySQL thread id 590, OS thread handle 0x87610, query id 21240806 51.144.51.59 mysql56admin executing

load data local infile "R:\\repldata\\tasks\\fd954e8d-5a57-48e0-93f8-af811246c0f8\\data_files\\1\\LOAD00000277.csv" into table `i_employee`.`employee_data` CHARACTER SET UTF8MB4 fields terminated by ','  enclosed by '"' lines terminated by '\n'( `id`,`timestamp`,`employeeId`,`first_name`,`middle_name`,`last_name`,`address`,`notes`)

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

TABLE LOCK table `i_employee`.`employee_data` trx id 2053969 lock mode AUTO-INC waiting

*** (2) TRANSACTION:

TRANSACTION 2053962, ACTIVE 27 sec inserting

mysql tables in use 1, locked 1

5 lock struct(s), heap size 1184, 1 row lock(s), undo log entries 296

MySQL thread id 622, OS thread handle 0x87ef0, query id 21240803 13.74.158.111 developer update

INSERT INTO `employee_data`(

`id`,

`timestamp`,

`employeeid`,

`first_name`,

`middle_name`,

`last_name`,

`address`,

`notes`,

VALUES (

162,

`2018-04-20 18:13:11',

483,

`James’,

`Owen',

`Philips`,

‘5000, John Street, Portland, OR 73000`

‘New orientation`

)

*** (2) HOLDS THE LOCK(S):

TABLE LOCK table "i_employee"."employee_data"  trx id 2053962 lock mode AUTO-INC

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 0 page no 2303241 n bits 88 index "PRIMARY" of table "i_employee"."employee_data" trx id 2053962 lock mode S locks rec but not gap waiting



*** WE ROLL BACK TRANSACTION (1)


From the sample output file above you can see that transaction (1) is waiting for the lock to be granted, while transaction (2) is holding the lock. There could be another job that runs the insert into the same table while migration is happening.


1. Stop the insert job that is locking the table.

 

2. Start a new online data migration activity in DMS.

 

Other recommendations

On target Azure Database for MySQL instance, it is recommended that you use the max value for system parameter innodb_lock_wait_timeout . To accomplish this: 


1. In the Azure portal, navigate to your Azure Database for MySQL instance.

 

2. Select Server parameters , and then locate the innodb_lock_wait_timeout parameter.

 

3. Change the value of the parameter to the max value, as indicated in the informational icon.





That should resolve your issue!

We also recommend these timeout settings on source and target servers during migration, and tune them back to the original values after migration.

net_read_timeout  = 600

net_write_timeout  = 600

wait_timeout = 28800

interactive_timeout = 28800


Be sure to sign in to the Azure portal and set up an instance of DMS for free. We are constantly adding new database source/target pairs to DMS. Stay up-to-date on #AzureDMS news and follow us on Twitter (@Data_Migrations ). Join the Azure Database Migration Service (DMS) Yammer group, and give us feedback via User Voice or email by contacting us at dmsfeedback@microsoft.com.  Also be sure to check out the Azure Database Migration Guide for information about how to migrate other source/target pairs.

Thank you!

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.