2

I am attempting to load a text file into a MySQL table. The error I receive is:

HY000 1148 [MySQL][ODBC 8.0(w) Driver][mysqld-5.6.39]The used command is not allowed with this MySQL version

My OS is Ubuntu 16.04.4 LTS and client MySQL version is 5.7.24. The MySQL db server is on an AWS RDS instance running MySQL 5.6.39

I tried running this command using an ODBC:

LOAD DATA LOCAL INFILE 'abc.txt' INTO TABLE tblname FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

I can successfully connect to the DB using both the command line and the ODBC connection and can run SELECT, UPDATE, INSERT, et al. commands with no problem.

I can also run the LOAD DATA LOCAL INFILE command from a Windows machine with no problem using an ODBC connection, so I know the issue is not on the server side.

I have tried several modifications to the my.cnf file with no success, including all the recommendations here:

ERROR 1148: The used command is not allowed with this MySQL version

and here:

LOAD DATA LOCAL INFILE gives the error The used command is not allowed with this MySQL version

Both of these solutions suggest setting local-infile=1 on the server, but my server already has that setting.

Stephen Ostermiller
  • 23,933
  • 14
  • 88
  • 109
Chris
  • 116
  • 10
  • @StephenOstermiller No, that wasn't quite the same problem. It turned out to be an ODBC Connector issue as detailed in my answer. – Chris Nov 01 '19 at 20:38
  • I have to admit, I tried so many different things that I can't remember when the command line started working. In the end, I think I had two separate issues (ODBC and command line) that I assumed were the same. Both were related to the local-infile parameter, but each issue had different solutions. – Chris Nov 01 '19 at 22:31
  • 1
    Fair enough. Now that I edited this question, it is distinct. – Stephen Ostermiller Nov 01 '19 at 22:39
  • After rereading my original post, I realize that my request was unclear/inaccurate. Running the command from the command line was not an issue. It was always ODBC related. I will edit my original post. In the end, the local-infile parameter was the culprit. The fix, however, was slightly different than the other posts. – Chris Nov 01 '19 at 22:41
  • You beat me to it!! Thanks @StephenOstermiller. – Chris Nov 01 '19 at 22:42

1 Answers1

1

This issue turned out to be an ODBC connector issue.

The version of the MySQL ODBC connector I was using was 8.0.11 which overrides the local-infile parameter and sets it to zero. In this version of the connector, the parameter was not editable (to my knowledge).

As of version 8.0.14, the user can add the following line to the odbc.ini file:

ENABLE_LOCAL_INFILE=1

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

After upgrading to the new connector and updating odbc.ini, LOAD DATA LOCAL INFILE is successful when connecting via ODBC.

Chris
  • 116
  • 10