PHPMyAdmin saying: The used command is not allowed

问题:

I am an Java developer new to MySQL.

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 :

The used command is not allowed with this MySQL version

Version details is as follows

Mysql Server version: 5.5.34-0

OS : ubuntu0.13.04.1 (Ubuntu)

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

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

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

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

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. ERROR 1148: The used command is not allowed with this MySQL version

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

[UPDATE]

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

SHOW VARIABLES LIKE "local%"

Load script

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

回答1:

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

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

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

3. 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]

Hope this will help you.

回答2:

My friend,

kindly us the following steps:-

1. Firstly, remove any current installations of MySQL server

2. run command in terminal sudo apt-get --purge remove mysql-server

3. go ahead and obtain the source files and other packages required to compile and install the source.

4. run command sudo apt-get install mysql-source-5.5 build-essential libncurses5-dev cmake

5.Go to the directory where the source is.

The best part while installing from source is that you may configure as per your need like DENABLED_LOCAL_INFILE

Kindly follow below link:-

http://www.sudo-juice.com/install-mysql-5-5-from-source-ubuntu-linux/

回答3:

I have fixed this problem by removing LOCAL keyword. Please remove LOCAL keyword from the command, it will work for mysql server version 5.5.34-0.