CODEWITHSUNDEEP
mysqlmariadbfedora
vddox
vddox

Reputation: 182

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

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

Upvotes: 2

Views: 1207

Answers (2)

Tomasz Kasperczyk
Tomasz Kasperczyk

Reputation: 2093

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

Upvotes: 2

Alexandre Voyer
Alexandre Voyer

Reputation: 819

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.

Upvotes: 1

Related Questions