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:
Only registered users can see links on this board! Get registered or login to the forums!
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
Only registered users can see links on this board! Get registered or login to the forums!
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
Only registered users can see links on this board! Get registered or login to the forums!
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.)
Only registered users can see links on this board! Get registered or login to the forums!
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!
View next topic View previous topic
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
![[Valid RSS]](http://www.ravenphpscripts.com/images/valid-rss.png "Validate my RSS feed"){kind=small}
:: 
::
