PHPMyAdmin 说:此 MySQL 版本不允许使用所使用的命令 [英] PHPMyAdmin saying: The used command is not allowed with this MySQL version

问题描述

我是一名刚接触 MySQL 的 Java 开发人员.

I am an Java developer new to MySQL.

我想使用 PHPMyAdmin 执行加载数据操作.加载数据脚本经过验证，因为它在我的 Windows 操作系统配置上运行良好.尝试从 CSV 加载数据时，我得到:

I want to perform Load data operation using PHPMyAdmin. Load data scripts are verified as it worked pretty well on my Windows OS configuration.While trying to load data from CSV, i am getting :

此 MySQL 版本不允许使用的命令

The used command is not allowed with this MySQL version

版本详情如下

Mysql Server 版本: 5.5.34-0

操作系统: ubuntu0.13.04.1 (Ubuntu)

OS : ubuntu0.13.04.1 (Ubuntu)

我已经完成了对 my.cnf 的编辑，如下所示:

I am done with editing the my.cnf as follows:

[mysql]
local-infile    =1
#no-auto-rehash # faster start of mysql but no tab completition
.......................

[mysqld]
#
# * Basic Settings
#
local-infile    =1
user        = mysql
.........................

[client] 
loose-local-infile=1
root
port        = 3306
.........................

我尝试使用 - 多次重新启动 apache 和 mysql须藤服务 mysql 重启须藤服务 apache2 重启

I have tried restarting apache and mysql multiple times using - sudo service mysql restart sudo service apache2 restart

没有什么能帮助我使用 phpmyadmin 执行操作.使用以下命令时

Nothing worked for me that help me to perform the operation using phpmyadmin. While using below command

mysql -u root -p --local-infile test

我能够通过 mysql 提示成功执行加载数据操作.由于我有多个 CSV 块和每个块的 SQL 加载脚本，所以我想使用 phpmyadmin 一次性运行它(加载脚本由 $ 分隔).

I am able to perform the Load data operation successfully through mysql prompt. Since i have multiple CSV in chunks and SQL load scripts for each chunk so i want to run it as a whole(load scripts seprated by $) in one go using phpmyadmin.

请帮我解决这个问题.我已经通过以下有关堆栈溢出的答案

Please help me to solve this problem. I have already gone through below answers on stack overflow

1. 错误 1148: 此 MySQL 版本不允许使用的命令

LOAD DATA LOCAL INFILE 给出错误 The used command is not allowed with this MySQL version

[更新]

变量 local_infile 显示为ON"，我已经在 phpmyadmin 上使用 SQL 查询验证了这一点

Variable local_infile is showing as 'ON', I have verified this using SQL query on phpmyadmin

SHOW VARIABLES LIKE "local%"

加载脚本

LOAD DATA LOCAL INFILE 'full_dir_path/man_final.csv'
INTO TABLE `manufact_t` 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
ESCAPED BY '\' 
LINES TERMINATED BY '
'
IGNORE 1 LINES
(man_sr,man_name, address, phone, fax, email, url,subdivision, @man_parent, man_id)
set man_parent=0;

推荐答案

我的朋友.此错误可能有几个原因.

my friend. There can be few reasons of this error.

1. php.ini 的 mysql.allow_local_infile 值错误.必须 - mysql.allow_local_infile = On

1. php.ini has wrong value for mysql.allow_local_infile. Must be - mysql.allow_local_infile = On

如果您在本地工作，您可以尝试从您的 SQL 查询中删除 LOCAL.也许 MySQL 服务器设置不允许您使用 LOCAL 关键字

If you are working locally you can try to remove the LOCAL from your SQL query. Maybe MySQL server settings just don't allow you to use the LOCAL keyword

仅当您使用 cPanel 时:问题在于 cPanel 界面中内置的 phpMyAdmin 不使用使用 apache 构建的 php.所以你可以整天重建 apache，它不会改变 php 的编译方式 [来自 cPanel 论坛]

Only if you use cPanel: The problem is the phpMyAdmin that is built into the cPanel interface doesn't use the php built with apache. So you can rebuild apache all day long and it will not change how php is compiled [from cPanel forum]

希望对你有帮助.

这篇关于PHPMyAdmin 说:此 MySQL 版本不允许使用所使用的命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！