7

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;
Community
  • 1
  • 1
RQube
  • 954
  • 3
  • 13
  • 28
  • @Daanvn thanks but i had mentioned in question "mysql -u root -p --local-infile test" is working, here test is my DBName. I just want to enable Load Data Local. – RQube Apr 24 '14 at 07:42
  • may you show the command you are using data upload. – Zafar Malik Apr 30 '14 at 12:06
  • @ZafarMalik script is completely fine i am able to work with load script from mysql prompt still i am updating question to have that load script. Plz have a look on question update at the end. thanks – RQube Apr 30 '14 at 12:11
  • https://dev.mysql.com/doc/refman/5.1/en/load-data-local.html issue detail for load data local – RQube May 05 '14 at 10:14

3 Answers3

7

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.

MustDie1Bit
  • 560
  • 4
  • 16
  • thanks for your time, but whats solution for it mysql.allow_local_infile is on, and i am working on localhost(client-server on same machine). everything i have described in mail. here is detail on this issue https://dev.mysql.com/doc/refman/5.1/en/load-data-local. – RQube May 05 '14 at 09:50
  • I tried to load from source but not able to complete the installation due to some error. mail means question in previous comment – RQube May 05 '14 at 10:04
  • 1
    I've fixed the problem by removing **LOCAL** keywords, my mysql server version is 5.5.34-0. – Engine Bai Dec 30 '14 at 08:39
  • Yep! I also removed the `LOCAL` statement on my brand new Ubuntu 15.04. But then I also had to follow [this answer to another post](http://stackoverflow.com/questions/4215231/load-data-infile-error-code-13#answer-13677611) – Dominic Jun 24 '15 at 19:35
1

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/

Amit Kumar Sharma
  • 61
  • 4
0

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.

Bapu
  • 1
  • 2