Wednesday, September 20, 2006

MySQL - LOAD DATA INFILE and cPanel

I wanna convert this blog as a NoteKeeper, a Dairy , a Friend , a NewsPaper and everything else I feels at that very moment. As my father always say I start a job with great enthu, but will burn out soon. I bet him for this blog!!!

So at this time it is about the LOAD DATA INFILE thing for which I had to spend around 10 mins to figure what is wrong with the command. The server do allow LOAD DATA INFILE thing, but the client was getting Access Denied (well actually better thing compared to Not allowed on this server error). I confirmed that we do allow it, by taking mysql prompt and entering show variables and it showed

local_infile ON

Happy now..but why the issue of access denied. Ctrl + C and Ctrl + Ved a small code
<?php

if ($db = mysql_connect('localhost','db_user','password')) {
print
"connected!";
} else {
print
"could not connect" . mysql_error;
}

if (
mysql_select_db('db_name', $db)) {
print
"
database accessed!"
;
} else {
print
"could not access database!" . mysql_error();
}

$query = "LOAD DATA INFILE '/home/username/csv_data_to_import.txt'
INTO TABLE table_name
FIELDS TERMINATED BY ','
ENCLOSED BY '\"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES"
;
if (
mysql_query($query, $db)) {
echo
"
wohoo! loaded!"
;
} else {
print
"
failed!"
. mysql_error();
}
?>

Added a LOCAL keyword and it started working. Now the question was why :-P I was puzzled till I carefully read the documentation at http://dev.mysql.com/doc/refman/4.1/en/load-data.html which read


Note that, in the non-LOCAL case, these rules mean that a file named as ./myfile.txt is read from the server's data directory, whereas the file named as myfile.txt is read from the database directory of the default database. For example, if db1 is the default database, the following LOAD DATA statement reads the file data.txt from the database directory for db1, even though the statement explicitly loads the file into a table in the db2 database:

which basically meant, if I have to use LOAD DATA INFILE (without LOCAL keyword, I have to copy the csv to /var/lib/mysql/db_name or to the temp database directory (?) and use the csv location appopriately. Anyways the story ended fine. The prince married the princess and happily lived thereafter.

No comments: