secure_file_priv and mysqlimport

All we need is an easy explanation of the problem, so here it is.

Question: How can we avoid errors of the following type with mysqlimport when the data file is in the current directory?

mysqlimport: Error: 13, Can’t get stat of
‘/var/lib/mysql/«dbname»/«tablename».txt’ (OS errno 2 – No such file
or directory), when using table: «tablename»

We are using MySQL version 8.0.28.


Background:

Our issue started with LOAD DATA INFILE giving the following error:

The MySQL server is running with the –secure-file-priv option so it
cannot execute this statement

This issue was resolved by following the tip from https://computingforgeeks.com/how-to-solve-mysql-server-is-running-with-the-secure-file-priv-error/

Basically we edited the MySQL options file to set secure‐file‐priv = "".
Additionally we ensured that the owner and group of the file is set to mysql.

With these changes LOAD DATA INFILE is working like a charm. However, these changes have had no impact on mysqlimport.

mysqlimport gives the following error:

mysqlimport: Error: 13, Can’t get stat of
‘/var/lib/mysql/«dbname»/«tablename».txt’ (OS errno 2 – No such file
or directory), when using table: «tablename»

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

We were able to fix the problem by first enabling LOCAL capability for LOAD DATA

mysql> SET GLOBAL local_infile = TRUE;

After that we started invoking mysqlimport with --local option.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply