错误 1148：此 MySQL 版本不允许使用的命令

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

提问by sosytee

I am trying to load data into mysql database using

我正在尝试使用将数据加载到 mysql 数据库中

LOAD DATA LOCAL
INFILE A.txt
INTO DB
LINES TERMINATED BY '|';

the topic of this question is the response I get. I understand the local data offloading is off by default and I have to enable it using a the command local-infile=1but I do not know where to place this command.

这个问题的主题是我得到的回应。我知道默认情况下本地数据卸载是关闭的，我必须使用命令启用它，local-infile=1但我不知道在哪里放置这个命令。

回答by arkascha

You can specify that as an additional option when setting up your client connection:

您可以在设置客户端连接时将其指定为附加选项：

mysql -u myuser -p --local-infile somedatabase

This is because that feature opens a security hole. So you have to enable it in an explicit manner in case you really want to use it.

这是因为该功能打开了一个安全漏洞。所以你必须以明确的方式启用它，以防你真的想使用它。

Both client and server should enable the local-file option. Otherwise it doesn't work.To enable it for files on the server side server add following to the my.cnfconfiguration file:

客户端和服务器都应该启用本地文件选项。否则它不起作用。要为服务器端服务器上的文件启用它，请将以下内容添加到my.cnf配置文件中：

loose-local-infile = 1

回答by Java Xu

I find the answer here.

我在这里找到答案。

It's because the server variable local_infileis set to FALSE|0. Refer from the document.

这是因为服务器变量local_infile设置为FALSE|0。参考文档。

You can verify by executing:

您可以通过执行来验证：

SHOW VARIABLES LIKE 'local_infile';

If you have SUPER privilege you can enable it (without restarting server with a new configuration) by executing:

如果您有 SUPER 权限，您可以通过执行以下命令启用它（无需使用新配置重新启动服务器）：

SET GLOBAL local_infile = 1;

回答by Vérace

http://dev.mysql.com/doc/refman/5.6/en/load-data-local.html

http://dev.mysql.com/doc/refman/5.6/en/load-data-local.html

Put this in my.cnf - the [client]section should already be there (if you're not too concerned about security).

将它放在 my.cnf 中 - 该[client]部分应该已经存在（如果您不太关心安全性）。

[client]
loose-local-infile=1

回答by Dominic

All of this didn't solve it for me on my brand new Ubuntu 15.04.

所有这些都没有在我全新的 Ubuntu 15.04 上为我解决。

I removed the LOCALand got this command:

我删除了LOCAL并得到了这个命令：

LOAD DATA
INFILE A.txt
INTO DB
LINES TERMINATED BY '|';

but then I got:

但后来我得到了：

MySQL said: File 'A.txt' not found (Errcode: 13 - Permission denied)

That led me to this answer from Nelson to another question on stackoverflowwhich solved the issue for me!

这让我从 Nelson那里得到了关于 stackoverflow 的另一个问题的答案，它为我解决了这个问题！

回答by Vineet Sharma

SpringBoot 2.1 with Default MySQL connector

带有默认 MySQL 连接器的 SpringBoot 2.1

To Solve this please follow the instructions

要解决此问题，请按照说明进行操作

1. SET GLOBAL local_infile = 1;

1.设置全局local_infile = 1;

to set this global variable please follow the instruction provided in MySQL documentation https://dev.mysql.com/doc/refman/8.0/en/load-data-local.html

要设置此全局变量，请按照 MySQL 文档https://dev.mysql.com/doc/refman/8.0/en/load-data-local.html 中提供的说明进行操作

2. Change the MySQL Connector String

2. 更改 MySQL 连接器字符串

allowLoadLocalInfile=true Example : jdbc:mysql://localhost:3306/xxxx?useSSL=false&useUnicode=yes&characterEncoding=UTF-8&allowLoadLocalInfile=true

allowLoadLocalInfile=true 示例：jdbc:mysql://localhost:3306/xxxx?useSSL=false&useUnicode=yes&characterEncoding=UTF-8&allowLoadLocalInfile=true

回答by Nilesh Mishra

I had the same issue while importing the CSV file in AWS MySQL 8.0 RDS.

我在 AWS MySQL 8.0 RDS 中导入 CSV 文件时遇到了同样的问题。

mysql> LOAD DATA LOCAL INFILE '/tmp/your.csv' INTO TABLE test.demo2 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES;

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

mysql> LOAD DATA LOCAL INFILE '/tmp/your.csv' INTO TABLE test.demo2 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES;

ERROR 1148 (42000): 此 MySQL 版本不允许使用的命令

1) Check for local_infileparameter

1) 检查local_infile参数

mysql> SHOW VARIABLES LIKE 'local_infile';

2) Log out from client and re-login using below parameter.

2) 从客户端注销并使用以下参数重新登录。

mysql -u root -p -h rdsendpoint --local-infile=1

3) Run the same command

3）运行相同的命令

mysql> LOAD DATA LOCAL INFILE '/tmp/your.csv' INTO TABLE test.demo2 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES;


Query OK, 300 rows affected (0.01 sec)

Records: 300  Deleted: 0  Skipped: 0  Warnings: 0

回答by VladimirS

Just little addition.

只是一点点添加。

Found another reincarnation in mysql.connector ver 8.0.16 It now requires allow_local_infile=Trueor you will see the above error. Worked in prior versions.

在 mysql.connector ver 8.0.16 中发现了另一个转世 现在需要allow_local_infile=True否则您将看到上述错误。在以前的版本中工作。

conn = mysql.connector.connect(host=host, user=user, passwd=passwd, database=database, allow_local_infile=True)

回答by K F

I got this error while loading data when using docker[1]. The solution worked after I followed these next steps. Initially, I created the database and table datavaultand fdata. When I tried to import the data[2], I got the error[3]. Then I did:

使用 docker[1] 时加载数据时出现此错误。在我遵循这些后续步骤后，该解决方案起作用了。最初，我创建了数据库和表datavault以及fdata. 当我尝试导入数据 [2] 时，出现错误 [3]。然后我做了：

• SET GLOBAL local_infile = 1;
• Confirm using SHOW VARIABLES LIKE 'local_infile';
• Then I restarted my mysql session: mysql -P 3306 -u required --local-infile=1 -p, see [4] for user creation.
• I recreatedmy table as this solved my problem:
  • use datavault;
  • drop table fdata;
  • CREATE TABLE fdata (fID INT, NAME VARCHAR(64), LASTNAME VARCHAR(64), EMAIL VARCHAR(128), GENDER VARCHAR(12), IPADDRESS VARCHAR(40));
• Finally I imported the data using [2].

• SET GLOBAL local_infile = 1;
• 确认使用 SHOW VARIABLES LIKE 'local_infile';
• 然后我重新启动了我的 mysql 会话：mysql -P 3306 -u required --local-infile=1 -p，请参阅 [4] 以了解用户创建。
• 我重新创建了我的表，因为这解决了我的问题：
  • use datavault;
  • drop table fdata;
  • CREATE TABLE fdata (fID INT, NAME VARCHAR(64), LASTNAME VARCHAR(64), EMAIL VARCHAR(128), GENDER VARCHAR(12), IPADDRESS VARCHAR(40));
• 最后我使用 [2] 导入了数据。

For completeness, I would add I was running the mysql version inside the container via docker exec -it testdb sh. The mysql version was mysql Ver 8.0.17 for Linux on x86_64 (MySQL Community Server - GPL). This was alsotested with mysql.exe Ver 14.14 Distrib 5.7.14, for Win64 (x86_64)which was another version of mysql from WAMP64. The associated commands used are listed in [5].

为了完整起见，我会补充说我是通过docker exec -it testdb sh. mysql 版本是mysql Ver 8.0.17 for Linux on x86_64 (MySQL Community Server - GPL)。这也使用mysql.exe Ver 14.14 Distrib 5.7.14进行了测试，适用于 Win64 (x86_64)，这是 WAMP64 的另一个 mysql 版本。使用的相关命令列在 [5] 中。

---

---

[1] docker run --name testdb -v //c/Users/C/Downloads/data/csv-data/:/var/data -p 3306 -e MYSQL_ROOT_PASSWORD=password -d mysql:latest

[1] docker run --name testdb -v //c/Users/C/Downloads/data/csv-data/:/var/data -p 3306 -e MYSQL_ROOT_PASSWORD=password -d mysql:latest

[2] load data local infile '/var/data/mockdata.csv' into table fdata fields terminated by ',' enclosed by '' lines terminated by '\n' IGNORE 1 ROWS;

[2] load data local infile '/var/data/mockdata.csv' into table fdata fields terminated by ',' enclosed by '' lines terminated by '\n' IGNORE 1 ROWS;

[3] ERROR 1148 (42000): The used command is not allowed with this MySQL version

[3] ERROR 1148 (42000): 此 MySQL 版本不允许使用的命令

[4] The required client was created using:

[4] 所需的客户端是使用以下方法创建的：

• CREATE USER 'required'@'%' IDENTIFIED BY 'password';
• GRANT ALL PRIVILEGES ON * . * TO 'required'@'%';
• FLUSH PRIVILEGES;
• You might need this line ALTER USER 'required'@'%' IDENTIFIED WITH mysql_native_password BY 'password';if you run into this error: Authentication plugin ‘caching_sha2_password' cannot be loaded

• CREATE USER 'required'@'%' IDENTIFIED BY 'password';
• GRANT ALL PRIVILEGES ON * . * TO 'required'@'%';
• FLUSH PRIVILEGES;
• ALTER USER 'required'@'%' IDENTIFIED WITH mysql_native_password BY 'password';如果遇到此错误，您可能需要此行：Authentication plugin ‘caching_sha2_password' cannot be loaded

[5] Commands using mysql from WAMP64:

[5] WAMP64中使用mysql的命令：

• mysql -urequired -ppassword -P 32775 -h 192.168.99.100 --local-infile=1where the port is thee mapped port into the host as described by docker ps -aand the host ip was optained using docker-machine ip(This depends on OS and possibly Dockerversion).
• Create database datavault2and table fdataas described above
• load data local infile 'c:/Users/C/Downloads/data/csv-data/mockdata.csv' into table fdata fields terminated by ',' enclosed by '' lines terminated by '\n';
• For my record, this other alternative to load the file worked after I have previously created datavault3and fdata: mysql -urequired -ppassword -P 32775 -h 192.168.99.100 --local-infile datavault3 -e "LOAD DATA LOCAL INFILE 'c:/Users/C/Downloads/data/csv-data/mockdata.csv' REPLACE INTO TABLE fdata FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 ROWS"and it successfully loaded the data easily checked after running select * from fdata limit 10;.

• mysql -urequired -ppassword -P 32775 -h 192.168.99.100 --local-infile=1其中端口是映射到主机的端口，如描述的那样，docker ps -a并且使用主机 ip 选择docker-machine ip（这取决于操作系统和可能的Docker版本）。
• 如上所述创建数据库datavault2和表fdata
• load data local infile 'c:/Users/C/Downloads/data/csv-data/mockdata.csv' into table fdata fields terminated by ',' enclosed by '' lines terminated by '\n';
• 根据我的记录，在我之前创建datavault3和fdata之后，加载文件的另一种替代方法起作用了：mysql -urequired -ppassword -P 32775 -h 192.168.99.100 --local-infile datavault3 -e "LOAD DATA LOCAL INFILE 'c:/Users/C/Downloads/data/csv-data/mockdata.csv' REPLACE INTO TABLE fdata FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 ROWS"并且它成功加载了在运行后轻松检查的数据select * from fdata limit 10;。

回答by Atul

If you are using Java8 or + version, JDBC and MySql8 and facing this issue then try this:

如果您使用的是 Java8 或 + 版本、JDBC 和 MySql8 并遇到此问题，请尝试以下操作：

Add parameter to connection string:

将参数添加到连接字符串：

jdbc:mysql://localhost:3306/tempDB?allowLoadLocalInfile=true

Also, set

另外，设置

local_infile = 1

local_infile = 1

in my.cnf file.

在 my.cnf 文件中。

The latest version of mysql-java-connector might be wont allow directly to connect to local file. So with this parameter, you can able to enable it. This works for me.

最新版本的 mysql-java-connector 可能不允许直接连接到本地文件。因此，使用此参数，您可以启用它。这对我有用。

回答by mathsyouth

Refer to MySQL 8.0 Reference Manual -- 6.1.6 Security Issues with LOAD DATA LOCAL

请参阅 MySQL 8.0 参考手册 -- 6.1.6 LOAD DATA LOCAL 的安全问题

On the server side, run

在服务器端，运行

mysql.server start --local-infile

On the client side, run

在客户端，运行

mysql --local-infile database_name -u username -p