LOAD DATA [LOCAL] INFILE problems. Help!

Anon

Ok, I'm trying to load a text file into my mySQL database with the following command in phpMyAdmin:

LOAD DATA LOCAL INFILE "/usr/local/psa/home/vhosts/QuoteWorld.org/httpdocs/archive.txt" INTO TABLE quotations FIELDS TERMINATED BY '~';

When I use the LOCAL command, it does not find the file. Perhaps there is a path relative to the "LOCAL" file that I'm not aware of?

If I take the LOCAL out, and run it like this:

LOAD DATA INFILE "/usr/local/psa/home/vhosts/QuoteWorld.org/httpdocs/archive.txt" INTO TABLE quotations FIELDS TERMINATED BY '~';

It finds the file without trouble, but then tells me that access is denied like this:

Access denied for user: 'quoteworld@localhost' (Using password: YES)

So, my amateur conclusion is that my user does not have the proper priveleges to LOAD a non-LOCAL file, but I do not know the relative path to tell the DB where the file is in terms of what it considers to be "LOCAL". My IPP isn't much help on this matter, and I do not have telnet access to change my user's priveleges.

If anyone has any suggestions on what the relative path might be, how I can change my user's priveleges from within phpMyAdmin, or any other way I can load this file, I would be eternally greatful.

Thanks!
Brian Baker

Anon

You have to have the file_priv set to on for the user that's making the command. It's set to off by default.

do you have root access to the database? if so, here's one way to change it..

(log into mysql as root)

use mysql;
update user set File_priv='Y' where User = 'yourusername';
mysqladmin reload;

Let me know if that works for you.

Anon

Woops. forget about the mysqladmin roload command. Exit to the terminal right before that line and run this command instead...

cd /usr/local/mysql/bin
./mysqladmin -uroot -p reload

Anon

Mark, I appreciate the help, and I imagine that would work, but I do not have access to the root. :/

I could ask my provider to do this for me, but I'm sure they will be concerned with security issues, etc. What can I tell them to put their minds at ease, and is there a site to verify any of it?

Thanks again!
Brian Baker

Anon

I'd just tell the administrator that you need file_priv turned on in order to run some necessary commands. It's not an uncommon request. The risk on his end is that you will be able to write a file anywhere in the file system with the privileges of the mysqld daemon.

Anon

Gotcha. I'll see what they say. Thanks!

In case they don't go for it, anyone got any other suggestions?

Brian Baker

Anon

You have phpMyAdmin running and that already has a built in upload script to push data into a db. The upload data option only shows if you have the correct permissions in later versions (at least the 2.2x series). So if you don't see that option then you know your permission is wrong (on earlier versions it was there but would fail less then gracefully if your permissions were wrong).

You can also set up your own script and try.

Your query is missing the final part - LINES TERMINATED BY 'whatever_you_use_\n_say' which might be part of the problem (although this may be optional I can't remember off-hand).

Create a form, put in fields for file, fields separated by, lines terminated by, and point the form to the php script, method post.

Use LOCAL in the query as the form will send the file to the tmp directory of the servcer for loading.

Good luck

Anon

I have tried the upload feature in phpMyAdmin, and, as you put it, it failed less than gracefully. :/

In trying to creat my own script, I seem to be doing something wrong. I'm not an experienced PHP programmer by any means - I just look at other script's code to try and figure out what it does and apply it to my situation - so I'm probably doing something obviously stupid here. The important part of the script (only thing missing is the FORM) is below. I took out the variables to make sure that was not the problem...it wasn't.

<?php

$connection = mysql_connect("localhost", "quoteworld", "plato4820") or die ("Couldn't connect to database");
$db = mysql_select_db("quoteworld_org", $connection) or die ("Couldn't select DB");

$sql = "LOAD DATA INFILE \"/usr/local/psa/home/vhosts/QuoteWorld.org/httpdocs/archive.txt\" INTO TABLE \"quotations\" FIELDS TERMINATED BY \"~\";";

$sql_result = mysql_query($sql, $connection) or die ("Couldn't execute query");

?>

I get the "Couldn't execute query error".

Any suggestions?

Thanks!
Brian Baker