Hey guys,
I am having some problems using a text file thats hosted in my public_html to populate a file. I think its something to do with my host and I may have to go through them, but I wanted to see if you guys had any ideas.
I am using phpMyAdmin and the Query to load the data is this:
LOAD DATA
INFILE ‘http://www.mywebsite.com/calendar.txt’
INTO TABLE calendar
LINES TERMINATED BY ‘rn’
IGNORE 1 LINES
;
and I get the error:
#1045 - Access denied for user ‘andyinsd’@'localhost’ (using password: YES)
I thought this was because I have made a database user account that has a different username and password then my cpanel login. So I wrote a PHP script which connects to the server, selects the database and tries to execute the same query:
$result = mysql_query ("
LOAD DATA
INFILE ‘http://www.mywebsite.com/calendar.txt’
INTO TABLE calendar
LINES TERMINATED BY ‘rn’
IGNORE 1 LINES
");
I have used both my cpanel username and password and a few test database users and still haven’t got anything to work. The script is connecting the database just fine as I can query SELECT statements. However populating this one table with this text file has become quite the labor intensive project.
UPDATE:
I added this to the php script:
if( !$result)
{
die("Table did not populate" . mysql_error());
}
This resulted in the same error message that I got in phpMyAdmin
Access denied for user ‘whatever_Username_itried’@'localhost’ (using password: YES)
I’m assuming this is something only gator host can fix.
Thanks,
Andy
LOAD DATA LOCAL INFILE
For security reasons, when reading text files located on the server, the files must either reside in the database directory or be readable by all. Also, to use LOAD DATA INFILE on server files, you must have the FILE privilege. See Section 5.4.1, “Privileges Provided by MySQL”. For non-LOCAL load operations, if the secure_file_priv system variable is set to a nonempty directory name, the file to be loaded must be located in that directory.
Using LOCAL is a bit slower than letting the server access the files directly, because the contents of the file must be sent over the connection by the client to the server. On the other hand, you do not need the FILE privilege to load local files.
That’s my best guess.
Yeah, I’ve run into this same issue before. The cawk lover is right. When you "grant all privileges etc" MySQL doesn’t think "file" is included in "all" for some reason…
I just "source" everything, or run it from the shell.