I'm trying to load data into a MySQL database using the LOAD DATA LOCAL INFILE statement. On normal files, this works fine.
If I create a temporary file with File::Temp, store CSV data in it, close the file and then directly LOAD it into the database using
$dbh->do("LOAD DATA LOCAL INFILE '$tempfile' INTO TABLE $temptable" FIELDS TERMINATED BY ',');
the last two records are reproducibly omitted. However, if I do anything with the tempfile between creation and LOADing, for example with
`touch $tempfile`;
everything works as expected.
Is this an issue with the MySQL driver having trouble with freshly created tempfiles? Is it a filesystem (ext4) issue, maybe a cache flush not happening in time? Am I missing something here?
EDIT: Actually, all records are omitted if the temporary CSV file is not created by a format-converter subroutine, but by hand as shown below. I also included the code for the database interaction. Note the commented touch $tmpfh, which, when uncommented, would make the example work.
Adding UNLINK => 0 to File::Temp->new() does not make a difference.
my $tmpfh = File::Temp->new();
print $tmpfh <<EOT;
record1,textfield1
record2,textfield2
record3,textfield3
record4,textfield4
record5,textfield5
EOT
# `touch $tmpfh`; # uncomment this line to make it work
# get db handle
my $dbh = DBI->connect("DBI:mysql:$dbname:$dbserver", $username, $pwd);
# drop and recreate temp table
$dbh->do("DROP TABLE IF EXISTS $temptable") or die;
$dbh->do("CREATE TABLE $temptable (
`id` INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
`header` VARCHAR(255) NOT NULL,
`sequence` MEDIUMBLOB)")
or die;
# load data into temp table
my $nrecords = $dbh->do("LOAD DATA LOCAL INFILE '$tmpfh'
INTO TABLE $temptable
FIELDS TERMINATED BY ','
(header, sequence)")
or die;
$dbh->disconnect();
printf "Loaded %d records from %s into %s on %s.\n", $nrecords, $tmpfh, $dbname, $dbserver;
Close the file handle to flush the buffer. Keep the "UNLINK => 0" if you want the file to remain when the object goes out of scope.