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
[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;
解决方案
my friend. There can be few reasons of this error.
php.ini has wrong value for mysql.allow_local_infile. Must be - mysql.allow_local_infile = On
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
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.