How to allow mysql(mariadb) to read files from /tmp - Fedora 30

Question

I've written a program receives data from a socket, formats the data to CSV format, then dumps the data to a file; '/tmp/test_csv.csv'.

Next, I execute the following in mysql:

LOAD DATA INFILE '/tmp/test_csv.csv'
INTO TABLE flow_data
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

This statement outputs the following error:

Can't get stat of '/tmp/test_csv.csv' (Errcode: 2 "No such file or directory")

From what I understand, mysql doesn't have access to read from /tmp, which makes perfect sense.

The solution i'm looking for is to give mysql access to read from /tmp, whilst retaining its inability to write there (the latter is optional).

I need to dump the csv file to /tmp (or any other RAMdisk style directory) because of it's size, so dumping the file to the mysql database directory isn't a valid solution. The quantity of data i'm working with would cause my hard disk to get heavily contended (by both the file, and mysql) if it wasn't stored in-memory.

The only solution I have found/tried involves changing perms with semanage. https://stackoverflow.com/a/3971632/1449160 However, I had no luck with it.

I've also seen there is a workaround, by using the keyword LOCAL. However, i'm uncertain of the performance implications of this solution and would much rather let mysql read the file directly - or at least test to see if it matters.

OS: Fedora 30

mysql -V
mysql  Ver 15.1 Distrib 10.3.12-MariaDB, for Linux (x86_64)

*EDIT Both the file (/tmp/test_csv.csv) and the sql server are in the same machine. I know 'LOAD DATA LOCAl INFILE' would also work, but i'm trying to get mysql to read the file directly

What is the owner of the file ? File permissions must be wrong. `mysql` user must have read access to the file load it. — abeyaz, Jun 18 '19 at 17:49
```sudo chown mysql test_csv.csv``` Still no luck. Same error — vddox, Jun 18 '19 at 17:53

score 2 · Accepted Answer · answered Jun 18 '19 at 17:59

Your problem is probably caused by the fact that nowadays most daemons have a private TMPFS (including mysqld). In order to allow mysql to access /tmp, you need to change its daemon configuration like so:

export SYSTEMD_EDITOR=vim #Change the editor to the one you prefer
sudo -E systemctl edit --full mysqld.service
# Search for "PrivateTmp" and change it to "false", then save the file
sudo systemctl daemon-reload
sudo systemctl restart mysqld.service

score 0 · Answer 2 · answered Jun 18 '19 at 17:37

0

You might want to use LOAD DATA LOCAL INFILE if the file is on the machine's connected to the database and not on the server itself as LOAD DATA INFILE will look on the mysql server's filesystem.

answered Jun 18 '19 at 17:37

Alexandre Voyer

809
6
11

I should have been more clear, the file is on the same server as the mysql server. – vddox Jun 18 '19 at 17:41

How to allow mysql(mariadb) to read files from /tmp - Fedora 30

2 Answers2