Mysql LOAD INFILE / OUTFILE?

Up vote 0 down vote favorite share g+ share fb share tw.

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 . '-' .

$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 mysql load-data-infile link|improve this question edited 11-06-177 at 22:31 asked 11-06-177 at 21:48julio1,089416 82% accept rate.

– Ken White Jun 17 '11 at 22:25 those log entries are just my own log notes-- that's not an "official" mysql log file. It gets written when the actual OUTFILE statement returns a success code. I'll post the load infile statement.

– julio Jun 17 '11 at 22:27 After your edit, the problem seems pretty clear. You're creating the OUTFILE using $this->prefix = md5(time()); as part of the filename. Your LOAD INFILE is also using $this->prefix = md5(time()); as part of the filename; if the time has changed at all, the MD5 values won't match, and therefore neither will the OUTFILE and INFILE names.

– Ken White Jun 17 '11 at 22:43 @Ken-- thanks, but that's no the case either, since the timestamp is the same-- the class sets that timestamp/md5, then uses that to do the export/import at the same time. It doesn't run against a different version of the outfile. – julio Jun 17 '11 at 20:48.

Yes-- both with LOCAL and without. – julio Jun 17 '11 at 22:19 This actually was the problem-- removing the LOCAL and specifying the filesystem path did the trick. The LOCAL infile changed the path where mySQL was looking for the data.

– julio Jun 21 '11 at 20:48.

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.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.