• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2584
  • Last Modified:

MySQL LOAD DATA INFILE problem: file not found

Hi X-perts,

I am having problems with LOAD DATA INFILE command. Here is the code:

<?
set_time_limit(600);

$hostname_myconn = "localhost";
$database_myconn = "xxxx-2";
$username_myconn = "xxxxxxx";
$password_myconn = "xxxxxxx";
$myconn = mysql_connect($hostname_myconn, $username_myconn, $password_myconn, FALSE, 128) or trigger_error(mysql_error(),E_USER_ERROR);

mysql_select_db($database_myconn,$myconn);

$sql = "LOAD DATA LOCAL INFILE './extr.txt' INTO TABLE draf2
            FIELDS TERMINATED BY '\\t'
            LINES TERMINATED BY '\\n'";
mysql_query($sql,$myconn) or die(mysql_error());

?>

The php script is in the same directory as the input file extr.txt, but it returns an error "File not found". I tried a few things:

1) specified a full path as '/usr/local/psa/home/vhosts....../extr.txt'  --- the same error
2) added $client_flags = 128; to the db connection (as the above code) - the same error

What am I missing here? Please, help

thanks

A
0
andy7789
Asked:
andy7789
  • 8
  • 6
2 Solutions
 
Steve BinkCommented:
Have you made sure the data file is readable by the user running the script?  If you are running this as part of a website, that file should be readable by apache's user (usually 'apache').

As an experiment, try setting world-readable:

# chmod 644 ./extr.txt
0
 
andy7789Author Commented:
the current setting of chmod is 666, though the owner is not apache, but the default dir owner. I will try to change the owner to apache to see what happens
0
 
Steve BinkCommented:
If it is 666, the file should be readable by everyone.  I think I might have been mistaken, anyways...apache should not be reading the file.  MySQL should be reading the file.

If you log in to mysql using the ($username_myconn, $password_myconn) information in your code, can you execute the query manually?
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
andy7789Author Commented:
no, it does not work - the same error. Now the file extr.txt is chmod 666 with owner apache

the same problem
0
 
Steve BinkCommented:
What about the manual query?
0
 
andy7789Author Commented:
I am trying to execute it via SQLyog, but it would require a full path on the server - like

LOAD DATA LOCAL INFILE '/usr/local/psa/home/vhosts/mydomain.com/httpdocs/qzip/extr.txt' INTO TABLE draf2 FIELDS TERMINATED BY '\\t' LINES TERMINATED BY '\\n'

it returns a similar error:

Error Code : 2
File '\usr\local\psa\home\vhosts\mydomain.com\httpdocs\qzip\extr.txt' not found (Errcode: 2)
0
 
raja_ind82Commented:
Please confim that your text file is in correct path. if yes, can you please try this same query without 'LOCAL' word.

Thanks,
M.Raja
0
 
andy7789Author Commented:
yes, the path is correct, checked many tomes via ssh pwd. If i run it without LOCAL, it returns an error

Error Code : 1045
Access denied for user ...

It is quite normal, because the server searches for the file relative to the server's data directory. See

http://dev.mysql.com/doc/refman/5.0/en/load-data.html
0
 
andy7789Author Commented:
if i run it from the SQLyog command prompt as

LOAD DATA LOCAL INFILE 'c:/temp/extr.txt' INTO TABLE draf2 FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'

it works fine, but i cannot make it work from the server
0
 
Steve BinkCommented:
Have you tried running it without the LOCAL keyword, but with the full path?  The data file is on the same server, right?

If so, raja_ind82 has a valid point in that the LOCAL keyword does not make sense here.  PHP is simply sending the query text to the server and has no concerns other than sending just the query text.  The server is expecting the file contents to follow the query over the same connection, having been read by the client.   Since your client does not know this, it fails.  I'm guessing it works with SQLyog because that app knows to read it locally and send it with the query.  

The manual has to this say about not using the LOCAL keyword:


# If LOCAL is not specified, the file must be located on the server host and is read directly by the server. The server uses the following rules to locate the file:
    * If the filename is an absolute pathname, the server uses it as given.
    * If the filename is a relative pathname with one or more leading components, the server searches for the file relative to the server's data directory.
    * If a filename with no leading components is given, the server looks for the file in the database directory of the default database.

0
 
andy7789Author Commented:
Yes, I managed to run it without LOCAL, though I had to enable user's File privileges for MySQL.

I still have no ideas where php lools for the file in case, if LOCAL is specified. If I run it from the remote computer (ex via SQLyog command prompt) it searches on the local drive and it works fine. If I run the script on the server, it should look for a file on the server, if LOCAL is specified. But it does not.
0
 
andy7789Author Commented:
another point - how to remove "" from the fields when using LOAD DATA INFILE?

when I import the text file directly via SQLyog or phpmyadmin, it removes the all the "", but when executing the LOAD DATA INFILE string, it leaves the exact field values as is
0
 
andy7789Author Commented:
i got it - OPTIONALLY ENCLOSED BY '"'
0
 
Steve BinkCommented:
>>> I still have no ideas where php lools for the file in case, if LOCAL is specified.

I don't think it does.  For PHP to grab the file contents and send them along as well, it would have to know what kind of query you are sending.  I have not looked at the source, but I think mysql_query just sends string 'x' to the server.  It does no parsing, syntax verification, etc.  PHP is not a full-featured MySQL client.  It merely provides access to a subset of the API functions.

This IS possible in PHP, though.  Have PHP read the local file (file_get_contents()), and append it to the query string.  There might be more to the syntax than that, but I do not think it would be very complicated.  Perhaps another expert already knows the format to use...?  I'll see what I can find on that.

I'm sure SQLyog has been coded to parse SQL.  I've never used that particular app, but I use a similar one: EMS MySQL Manager.  In my SQL script window, I have a button for verifying SQL syntax, so I at least know it can parse the query.  The MySQL C API certainly has the functions available (see http://dev.mysql.com/doc/refman/5.0/en/mysql-set-local-infile-handler.html), so it would be an easy thing to include them in managers like these.  
0
 
Steve BinkCommented:
I found few references to the actual protocol of a LOAD DATA statement.  If I had to delve further, I'd get something like Ethereal or mysqlsniffer to ferret out what is actually being sent.  According to one document, it is going to be a formatted packet, so the raw query through PHP may not be possible.  However, there looks to be a better option in PHP5.  In the MySQLi library, that same function from the API is shown as an 'undocumented' function.  Look here:

http://www.php.net/manual/en/function.mysqli-set-local-infile-handler.php

Great help, right?  Absolutely no documentation, and man, did I look.  FINALLY I found some sample code that shows it in action.  I tried the code on my dev server (recently upgraded to PHP5) and it worked flawlessly, once I enabled the whole INFILE thing.  You can find the sample code at:

http://php5.sourcesdb.com/r.php?num=1630

I had to put a '/' in front of the filename, but it gave the expected results.  The downside is you'll have to upgrade to PHP5 to use the MySQLi module.  Not such a big hit...my recent upgrade needed only minor adjustments to 'unbreak' my app, and I like the newer library better anyways.  :)
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now