[RESOLVED] LOAD DATA INFILE problems

mrbaseball34

I was wondering...

If I am in phpMyAdmin, I can upload a file and import it using LOAD DATA INFILE.

But, if I try to run it in a query, using the SAME user, I get an access denied. I'm supposing that it is because the user doesn't have the FILE privilege.

I really need my user to be able to run LOAD DATA INFILE via a query. I need to
load a CSV into a table for further processing .vs iterating each row in the CSV file.

I figure that a stored procedure to do my complex import will be exponentially faster than iterating the rows of the CSV file and importing the data into the various table using multiple queries.

I have already improved the process because the original developer did this:

require('config.php');
$connection = mysql_connect($dbhost, $dbuser, $dbpass);
mysql_select_db($dbname,$connection);
$sql = "select * from sometable";
$result = mysql_query($sql) or die('error, query failed');
mysql_close($connection);

in every function call .vs using one persistant connection.

I cut the processing down to less than half with just by changing to use one connection. Now I think that, if I'm able to load the table directly from the CSV, I should be able to write a stored proc to do the import much faster.

Can anyone help out with ideas?

mrbaseball34

USE
LOAD DATA LOCAL INFILE

and it works without the FILE privilege.

Funny how the docs don't say that, it seems to be a very common problem.

bradgrafelman

mrbaseball34;10953284 wrote:
Funny how the docs don't say that, it seems to be a very common problem.

Oh?

MySQL Manual wrote:
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.

🙂