2

We have recently updated our client pcs from Windows 7 to Windows 10, and along with that, updated our MySQL Drivers from "MySQL ODBC 5.3 Unicode Driver" to "MySQL ODBC 8.0 Unicode Driver".

I use the LOAD DATA LOCAL INFILE command via the RODBC R package to import data from a local CSV file to a MySQL server. No changes have been made on the server and the SQL statement is still the same as before, but since the driver update I get the following error message:

"The used command is not allowed with this MySQL version"

The command still works when I run it on one of the old Win7 machines, which means that it is not a server-based problem (such as local-infile=0 in the config-file).

Has anyone else experienced this?

Thanks and kind regards,

Johannes

TallTed
  • 9,069
  • 2
  • 22
  • 37
Johannes
  • 21
  • 4
  • This is also something that is required to enabled on the client connection time to the database. The RODBC package should have an option for this. – danblack Mar 08 '19 at 07:53
  • thanks for the quick replies! I can't seem to find the option in the RODBC package for enabling client-side local-infile. Maybe the package is too old to support an explicit option for this. In any case, this is my workaround: I upload the csv-file via curl to the server and read the contents without the LOCAL part. Not the best option, I know, but I had to find a working solution quick. – Johannes Mar 11 '19 at 07:35
  • Possible duplicate of [MySQL LOAD DATA LOCAL INFILE not allowed over ODBC](https://stackoverflow.com/questions/53733880/mysql-load-data-local-infile-not-allowed-over-odbc) – Stephen Ostermiller Nov 01 '19 at 22:44

3 Answers3

2

The latest versions of MySQL ODBC Connector (> 8.0.14) allow you to set

ENABLE_LOCAL_INFILE=1

in the odbc.ini connection details.

In your R code, try:

odbcDriverConnect("DSN=dsnName;ENABLE_LOCAL_INFILE=1")

Alternatively, try editing the Windows registry entry for your ODBC connection:

  1. Open the Windows Registry Editor - click the windows icon and type regedit
  2. Go to HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI. This is where my ODBC entries are located.
  3. Select the ODBC connection you want to change and choose Edit>New>String Value from the menu.
  4. Give the new entry the name ENABLE_LOCAL_INFILE and set its value to 1.

https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-configuration-connection-parameters.html

Upgrading my connector and updating the odbc.ini resulted in success for me.

Chris
  • 116
  • 10
1

It has been disabled because of a vulnerability in the way the MySQL client driver handles the local-infile on the client side, see also: Percona Responds to MySQL LOCAL INFILE Security Issues.

Enabling the capability on the client makes your client driver vulnerable for an (outside) injection. So consider that if the machine using the odbc driver can be (ab)used by outside connections, it might become vulnerable for the local-infile directive.

TallTed
  • 9,069
  • 2
  • 22
  • 37
Art van Scheppingen
  • 301
  • 1
  • 5
0

There are two settings that affect LOAD DATA LOCAL INFILE:

  • local-infile, on the server
  • local-infile, on the client

See the doc for reference:

This also affects connectors, LOAD DATA LOCAL INFILE is now disabled by default in the ODBC connector in 8.0.14.

TallTed
  • 9,069
  • 2
  • 22
  • 37
Marc Alff
  • 8,227
  • 33
  • 59