Bug #87589 Documentation incorrectly states that LOAD DATA LOCAL INFILE does not use tmpdir
Submitted: 29 Aug 2017 18:05 Modified: 3 Aug 2018 22:04
Reporter: Geoff Montee Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.6.35, 5.7.19 OS:Any
Assigned to: CPU Architecture:Any
Tags: DML, documentation, load, tmpdir

[29 Aug 2017 18:05] Geoff Montee
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.
[4 Sep 2017 10:53] MySQL Verification Team
Hello Geoff,

Thank you for the report and feedback!

Thanks,
Umesh
[3 Aug 2018 22:04] Daniel Price
Posted by developer:
 
The referenced content as been revised as follows:

"When using LOCAL with LOAD DATA, a copy of the file is created in the
directory where the MySQL server stores temporary files. See "Where MySQL Stores Temporary Files". 

https://dev.mysql.com/doc/refman/8.0/en/load-data.html
https://dev.mysql.com/doc/refman/5.7/en/temporary-files.html

Thank you for the bug report.