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:
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
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."


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:


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 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:

INFILE '$PATH_TO/baseballdatabank-2017.1/core/Fielding.csv'
INTO TABLE db1.baseball_fielding

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] Umesh Shastry
Hello Geoff,

Thank you for the report and feedback!

[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". 


Thank you for the bug report.