Description:
The MySQL documentation says the following about LOAD DATA LOCAL INFILE:
"The LOCAL keyword affects where the file is expected to be found:
If LOCAL is specified, the file is read by the client program on the client host and sent to the server. The file can be given as a full path name to specify its exact location. If given as a relative path name, the name is interpreted relative to the directory in which the client program was started.
When using LOCAL with LOAD DATA, a copy of the file is created in the server's temporary directory. This is not the directory determined by the value of tmpdir or slave_load_tmpdir, but rather the operating system's temporary directory, and is not configurable in the MySQL Server. (Typically the system temporary directory is /tmp on Linux systems and C:\WINDOWS\TEMP on Windows.) Lack of sufficient space for the copy in this directory can cause the LOAD DATA LOCAL statement to fail."
https://dev.mysql.com/doc/refman/5.6/en/load-data.html
However, in my tests, the server does seem to put the files in tmpdir.
How to repeat:
First, let's create a new tmpdir, outside of /tmp:
sudo mkdir /var/lib/mysql/tmpdir
sudo chown mysql:mysql /var/lib/mysql/tmpdir
And then let's configure the following in /etc/my.cnf:
tmpdir=/var/lib/mysql/tmpdir
And then restart mysqld.
Let's also download some test data:
wget http://seanlahman.com/files/database/baseballdatabank-2017.1.zip
unzip baseballdatabank-2017.1.zip
And let's create the following table:
CREATE DATABASE db1;
CREATE TABLE db1.baseball_fielding (
fielding_id int auto_increment primary key,
playerID varchar(25),
yearID int,
stint int,
teamID varchar(3),
lgID varchar(2),
POS varchar(2),
G int,
GS int,
InnOuts int,
PO int,
A int,
E int,
DP int,
PB int,
WP int,
SB int,
CS int,
ZR int
);
Now let's open two shells.
In one shell, run the following in a loop:
for (( ; ; )); do sudo lsof -p `pidof mysqld` +L >> mysqld-lsof.out; done
In the other shell, open a MySQL connection, and then run the following:
LOAD DATA LOCAL
INFILE '$PATH_TO/baseballdatabank-2017.1/core/Fielding.csv'
INTO TABLE db1.baseball_fielding
IGNORE 1 LINES
(playerID,yearID,stint,teamID,lgID,POS,G,GS,InnOuts,PO,A,E,DP,PB,WP,SB,CS,ZR);
When the LOAD DATA statement finishes, interrupt the infinite loop in the other shell and check mysqld-lsof.out. You should see that all temporary files were created in tmpdir, and not in /tmp.
Suggested fix:
If the documentation is inaccurate, please correct it.
