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:
- 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!
Microsoft
