mysql LOAD INFILE / OUTFILE

I'm trying to create a simple import/export feature for a web app using mySQL SELECT INTO OUTFILE and LOAD DATA INFILE functions.

It seems easiest to just leave the path for the outfile/infiles as the default mysql path (which I think normally goes into the mysql.ini-defined tmp directory).

To ensure no collisions with existing exports, I generate an MD5 of a timestamp as a prefix that I append to the OUTFILE:

$this->prefix = md5(time());

SELECT * INTO OUTFILE ' . $this->prefix . '-' .开发者_如何学Python $table . '.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
FROM " . $table . " WHERE 1

So far so good, my log files show the created csv file in /var/lib/mysql/master/

When the script tries to do the LOAD DATA INFILE however, the file is not found:

2011-06-17 14:31:35 - INFO  --> Created outfile: eae77f210684ba0616c773677f707513-table.csv 
2011-06-17 14:31:35 - ERROR --> mySQL Error: 2 - File 'eae77f210684ba0616c773677f707513-table.csv' not found (Errcode: 2)

Is it possible that the LOAD INFILE and SELECT INTO OUTFILE are using different default locations?

EDIT TO ADD: I went ahead and specified the mysql tmp dir in the LOAD INFILE command-- it doesn't find the file, for some reason, even though it's clearly there and I'd assume the same "user" is reading it and writing to it:

$ sudo find / -name '8e357dcba6557a31f9a36230c4233d1b-table.csv'
/var/lib/mysql/master/8e357dcba6557a31f9a36230c4233d1b-table.csv

2011-06-17 15:04:25 - INFO  --> Created outfile: 8e357dcba6557a31f9a36230c4233d1b-table.csv
2011-06-17 15:04:25 - ERROR --> mySQL Error: 13 - File '/var/lib/mysql/master/8e357dcba6557a31f9a36230c4233d1b-table.csv' not found (Errcode: 13) 

EDIT: ADDING INFILE statement

$this->prefix = md5(time());
$this->mysql_dir = "/var/lib/mysql/master/";
LOAD DATA LOCAL INFILE ' . $this->mysql_dir . $this->prefix . '-' . $table . '.csv
INTO TABLE $table
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

Thanks

---

Have you tried LOAD DATA LOCAL INFILE?

---

I think julio, the problem is with the logic and not in SELECT INTO OUTFILE or LOAD DATA INFILE.

In both your examples, where you are adding $this->prefix to the OUTFILE / INFILE statements, you are regenerating the md5() each time. Therefore, the prefix that you use in OUTFILE does not match the prefix for INFILE and hence the error.

Possible solutions: - store the prefixes in a table that you can read from later when doing LOAD DATA INFILE - if you are generating reports at regular times, say every hour, instead of md5(), use date-time as the prefix, e.g. 20110618_1000-table.csv.