[SOLVED] secure_file_priv and mysqlimport

Issue

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»

Solution

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.

Answered By – Sandeep

Answer Checked By – Timothy Miller (BugsFixing Admin)

Leave a Reply

Your email address will not be published.