Question

MySQL can't use "LOAD DATA INFILE", secure-file-priv option is preventing execution.

I am trying to run a simple code to mysql db but it’s preventing me to do so. The code is tested on my xampp environment and it works.

the code that i am trying to use is this:

LOAD DATA INFILE '/var/www/laravelapp/storage/app/file.txt' 
INTO TABLE test
FIELDS TERMINATED BY '|';

the error that I get is: ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

I have searched for a fix and a lot of people recommend to add secure-file-priv = "" to my.cnf file under the mysqld config group

But I know that i am missing smth because I have added the configuration line at my.cnf file restarted mysql server with the commands:

service mysql restart
or 
service mysql stop
service mysql start
or 
/etc/init.d/mysql restart

This is part of my.cnf file:

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
#
# * Basic Settings
#
secure-file-priv = ""
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 127.0.0.1
#
# * Fine Tuning

Also tried to add it at the end of the file, in other parts, or to change the syntax:

secure-file-priv = " "
secure_file_priv = ""
secure_file_priv = " "

but still without success.

Pls any idea how can I run:

LOAD DATA INFILE

Submit an answer

This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

Sign In or Sign Up to Answer

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

Want to learn more? Join the DigitalOcean Community!

Join our DigitalOcean community of over a million developers for free! Get help and share knowledge in Q&A, subscribe to topics of interest, and get courses and tools that will help you grow as a developer and scale your project or business.

Try to check also the username privileges with the commands:

mysql> show grants for user; mysql> select * from user where User=‘user’ \G;

The correct variable syntax is as shown below: secure-file-priv = “”

mysql> SHOW VARIABLES LIKE “secure_file_priv”;


+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+

the idea is that I want to run these files from my web server’s directory: /var/www/laravelapp/storage/app/ and not from : /var/lib/mysql-files/

When i try what you suggested I face this problem:

mysql> LOAD DATA LOCAL INFILE '/var/www/shoppingcart/storage/app/file.txt'  INTO TABLE prove FIELDS TERMINATED BY '|';

ERROR 1148 (42000): The used command is not allowed with this MySQL version

 mysql -V

mysql Ver 14.14 Distrib 5.5.53, for debian-linux-gnu (x86_64) using readline 6.3

I don’t know if there is another workaround this problem or should I just swap to MariaDB but then again I am not sure if the problem still persists there too.