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:
and here:
Both of these solutions suggest setting local-infile=1 on the server, but my server already has that setting.
解决方案
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
After upgrading to the new connector and updating odbc.ini, LOAD DATA LOCAL INFILE is successful when connecting via ODBC.