MySQL LOAD DATA LOCAL INFILE不允许通过ODBC [英] MySQL LOAD DATA LOCAL INFILE not allowed over ODBC

问题描述

我正在尝试将文本文件加载到MySQL表中. 我收到的错误是:

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

HY000 1148 [MySQL] [ODBC 8.0(w)驱动程序] [mysqld-5.6.39]使用的命令 不允许使用此MySQL版本

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

我的操作系统是Ubuntu 16.04.4 LTS，客户端MySQL版本是5.7.24. MySQL数据库服务器位于运行MySQL 5.6.39的AWS RDS实例上

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

我尝试使用ODBC运行此命令:

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';

我可以使用命令行和ODBC连接成功连接到DB，并且可以运行SELECT，UPDATE，INSERT等.命令没问题.

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.

我还可以使用ODBC连接从Windows机器上正常运行LOAD DATA LOCAL INFILE命令，因此我知道问题不在服务器端.

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.

我尝试对my.cnf文件进行了几次修改都没有成功，包括此处的所有建议:

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

错误1148:此MySQL版本不允许使用该命令

在这里:

LOAD DATA LOCAL INFILE给出错误该MySQL版本不允许使用的命令

这两种解决方案都建议在服务器上设置local-infile = 1，但是我的服务器已经具有该设置.

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

推荐答案

该问题原来是ODBC连接器问题.

This issue turned out to be an ODBC connector issue.

我正在使用的MySQL ODBC连接器的版本是8.0.11，它将覆盖local-infile参数并将其设置为零. 在此版本的连接器中，(据我所知)该参数不可编辑.

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).

从8.0.14版本开始，用户可以将以下行添加到odbc.ini文件中:

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/zh-CN/connector-odbc-configuration-connection-parameters.html

升级到新的连接器并更新odbc.ini后，通过ODBC连接时LOAD DATA LOCAL INFILE成功.

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

这篇关于MySQL LOAD DATA LOCAL INFILE不允许通过ODBC的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！