MySQL setting --local-infile

Posted: Mon Aug 15, 2005 5:55 pm

Raven do you happen to know how and or where to set --local-infile=0 in order to allow for text uploading from local host?

I have read every manual under the sun....they all send you around in circles.....

I have found my my.cnf but I can not find the correct syntax for the command.

This is on a local testing server so I am not worried about the security issues with in....I just need the correct syntax!

Thank You for your help!

Dawg

Posted: Mon Aug 15, 2005 7:22 pm

OK...After a DAY of searching I found this.....I hope it works!!!!

Dave

LOAD DATA LOCAL INFILE error: "The used command is not allowed with this MySQL version" when trying to import data in phpMyAdmin or mysql prompt. What is causing this?

Answer: This is because the newest version of MySQL has disabled local file access due to security reasons. This LOAD DATA statement has to be used from the unix mysql prompt after making the changes below (it will not work from phpMyAdmin). The link to this article from MySQL explaining this is: http://www.mysql.com/doc/en/LOAD_DATA_LOCAL.html

Fix: To fix the problem and load data, you must edit your /etc/my.cnf file from unix and put the following in there for the last line OR you can pass the --local-infile=1 in the parameters when connecting to mysql prompt.

local-infile=1

When you're finished editing the file, it should look like the one below:

[client]
port=3306
host=127.0.0.1
local-infile=1

Once this file has been changed, you can import data using the mysql prompt only (not from phpMyAdmin).

1) Add the local-infile=1 statement to the /etc/my.cnf file using the vi editor. (if you don't know how to do this, e-mail support@showcasehosting.com and we'll change it for you).

2) FTP the files to your web site into the same directory where you're going to run the mysql command.

3) Connect from unix command line to the mysql command prompt via the following syntax:

mysql -u username -p -h youripaddress yourdatabasename

OR if you don't want to modify the /etc/my.cnf file, just use the following command instead:

mysql -u username -p -h youripaddress --local-infile=1 yourdatabasename

4) Once connected to mysql prompt, enter the LOAD DATA INFILE command to match your database and text file. The syntax for the command is at http://www.mysql.com/doc/en/LOAD_DATA.html

For example, the file below contains vehicle makes and is named "makes.txt".

"20","Ferrari"
"30","Lamborghini"

To load this file into a table called Makes that has 2 fields (MakeID, MakeName)

LOAD DATA LOCAL INFILE './make.txt' INTO TABLE
`Make` FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'

(Keep in mind, after the ENCLOSED BY is a single quote, followed by a double quote, then another single quote.)

http://www.showcasehosting.com/FAQ.html#MySQL:LoadDataLocal

Posted: Sat Aug 20, 2005 11:38 am

I am on a shared host.....and it did not work.

The way I got around it was to install phpdev. Excellent program for working locally. It is for testing only and is "lose" security wise. So I uploaded my data to a database there...then did an SQL dumo and uploaded that sql dump to my main server....worked like a champ!

Thanks...

Dawg

Site Admin/Owner Joined: Aug 27, 2002 Posts: 17088

I never saw your original post! I am sorry. I am glad that you have resolved it though.