Problems with 'LOAD DATA LOCAL INFILE' migrating to RDS

Question

We host a customers database on an ubuntu server running mysqld  Ver 10.1.33-MariaDB-1~xenial we are trying to get migrated to RDS. The customer has a job to update their database and the use the load local file parameter. This does not seem to be working on RDS. We have been researching and trying different options and now looking to see if the community can help. Below is the command that is throwing the error:

```
 EXEC('LOAD DATA LOCAL INFILE ''' + @DataFile1 + ''' INTO TABLE sh_phone_order FIELDS TERMINATED BY ''	'' ENCLOSED BY ''^'' LINES TERMINATED BY ''
'';') AT MYSQL_NEW_USER_MAGENTO_STAGING
 ```

This is the error they receive:

```
Executed as user: CLAWNET\SQL2agent. Could not execute statement on remote server 'MYSQL_NEW_MAGENTO_STAGING'. [SQLSTATE 42000] (Error 7215)  OLE DB provider "MSDASQL" for linked server "MYSQL_NEW_MAGENTO_STAGING" returned message "[MySQL][ODBC 5.3(a) Driver][mysqld-5.6.10]The used command is not allowed with this MySQL version". [SQLSTATE 01000] (Error 7412)  Could not execute statement on remote server 'MYSQL_NEW_MAGENTO_STAGING'. [SQLSTATE 42000] (Error 7215)  OLE DB provider "MSDASQL" for linked server "MYSQL_NEW_MAGENTO_STAGING" returned message "[MySQL][ODBC 5.3(a) Driver][mysqld-5.6.10]The used command is not allowed with this MySQL version". [SQLSTATE 01000] (Error 7412).  The step failed.
```

The RDS server has localfile set to 1. We are not sure what else to check to get this working.  
Does anyone know what we might need to change to allow this?  
  
Edited by: tonywebscale on Apr 21, 2020 12:18 PM

Answer

I had a little trouble understanding how this example ever worked, but I think I understand after some research. If I read it correctly, there is a Linked Server from a Microsoft SQL Server to a MySQL Server. You then tell Microsoft SQL Server to pass the string "Load Data Local Infile" to the MySQL Server and execute it local to the MySQL Server. But the entire point of Load Data Local Infile is to read data from the client and send it to the server, so it can't be executed on the MySQL Server directly. BUT, a little searching around suggests that the ODBC driver can indeed intercept and execute this command locally.  
  
Sooo, I think the problem is that you need to be setting ENABLE_LOCAL_INFILE = 1 _on the **client**_ side of the ODBC connection from SQL Server to the RDS MySQL database. You can probably just add this to the connect string. See https://stackoverflow.com/questions/55058843/mysql-load-data-local-infile-not-working-after-driver-update-from-5-to-8 for more discussion.  
  
Another thing to consider is that the ODBC driver used in the Microsoft SQL Server installation is a version that doesn't support ENABLE_LOCAL_INFILE. But before tackling if there is a way to update the driver, make sure the connection string is trying to enable local infile.

Problems with 'LOAD DATA LOCAL INFILE' migrating to RDS

Relevant content