I try to migrate data from Amazon RDS MySQL to Azure Database for MySQL using 'Attunity Replicate for Microsoft Migrations(Replicate MSM)'.
For this I setup the Replicate MSM tool on a Windows 10 machine locally, then I define & test the source & target database endpoints eg. RDS as source and Azure as target, install the required mysql, odbc drivers and enable binary logging, local-infile parameters on both the databases. But now when I run the migration task it only create the schema's of migrated tables on target db and failed at 'load data local infile' command.
Here's the stack trace:
00014468: 2019-06-20T11:17:41 [SOURCE_UNLOAD ]I: Unload finished for table 'TestDb'.'Employee' (Id = 1). 2000 rows sent. (streamcomponent.c:2892)
00014968: 2019-06-20T11:17:41 [TARGET_LOAD ]I: Loading table 'migrationtesting'.'Employee' with parallel threads (odbc_endpoint_imp.c:5256)
00014968: 2019-06-20T11:17:41 [TARGET_LOAD ]I: Use parallel load thread pool with '3' threads (csv_target.c:280)
00014968: 2019-06-20T11:17:42 [TARGET_LOAD ]I: Load finished for table 'TestDb'.'Employee' (Id = 1). 2000 rows received. 0 rows skipped. Volume transfered 904960 (streamcomponent.c:3116)
00014968: 2019-06-20T11:17:43 [TARGET_LOAD ]E: Failed to execute statement: 'load data local infile "C:\\Program Files\\Attunity\\ReplicateMSM\\data\\tasks\\Aws2Azure\\data_files\\1\\LOAD00000001.csv" into table `migrationtesting`.`Employee` CHARACTER SET UTF8 fields terminated by ',' enclosed by '"' lines terminated by '\n'( `id`,`name`,`gender`,`mobile`,`city` ) ;' [1022502] (ar_odbc_stmt.c:4349)
00014968: 2019-06-20T11:17:43 [TARGET_LOAD ]E: RetCode: SQL_ERROR SqlState: HY000 NativeError: 1148 Message: [MySQL][ODBC 5.3(w) Driver][mysqld-5.6.39.0]The used command is not allowed with this MySQL version [1022502] (ar_odbc_stmt.c:4355)
00007376: 2019-06-20T11:17:43 [TASK_MANAGER ]W: Table 'TestDb'.'Employee' (subtask 1 thread 1) is suspended (replicationtask.c:2050)
00014968: 2019-06-20T11:17:43 [TARGET_LOAD ]E: Failed to start load process for file '1' [1022502] (csv_target.c:1350)
00007376: 2019-06-20T11:17:43 [TASK_MANAGER ]I: All tables are loaded. Full load only task is stopped (replicationtask.c:2992)
00014968: 2019-06-20T11:17:43 [TARGET_LOAD ]E: Failed to load file '1' [1022502] (csv_target.c:1418)
00014968: 2019-06-20T11:17:43 [TARGET_LOAD ]E: Failed to load data from csv file. [1022502] (odbc_endpoint_imp.c:5331)
According to Azure docs:
LOAD DATA INFILE is supported, but the [LOCAL] parameter must be specified and directed to a UNC path (Azure storage mounted through SMB).
if this is the solution then kindly explain how to implement it.
Note: MySQL Server version on both RDS and AZURE is 5.6
As the error logs suggest that you're using v5.3 of MySQL ODBC Driver in which the LOAD DATA INFILE functionality is disabled by default, to enable this we need to explicitly set the value of ENABLE_LOCAL_INFILE to 1. In Attunity Replicate for Microsoft migrations, you have to enable this flag for your Target database endpoint, you can enable it by following these steps...
Open the settings of target Endpoint.
Goto Advanced tab > Internal Parameters.
Add search key additionalConnectionProperties and hit Enter. (it's case-sensitive,so just copy/paste the same)
You can see a new key has been created under internal parameters, type the value for this newly created key as: ENABLE_LOCAL_INFILE=1;
Save and then Reload your task.
Credits: Official Attunity Community/Support team for Microsoft Migrations