By albanikaperi
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
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!
The correct variable syntax is as shown below: secure-file-priv = “”
Try to check also the username privileges with the commands:
mysql> show grants for user; mysql> select * from user where User=‘user’ \G;
There are a couple options to get around this.
-Identify the directory specified from which you can import your file with a command like:
SHOW VARIABLES LIKE "secure_file_priv";
-Or, you can use LOCAL in your query. This way the file is imported by the client (not the server) and provided through the existing client connection.
LOAD DATA LOCAL INFILE "text.txt" INTO TABLE mytable;
You can find more on this here.
Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.
Full documentation for every DigitalOcean product.
The Wave has everything you need to know about building a business, from raising funding to marketing your product.
Stay up to date by signing up for DigitalOcean’s Infrastructure as a Newsletter.
New accounts only. By submitting your email you agree to our Privacy Policy
Scale up as you grow — whether you're running one virtual machine or ten thousand.
Sign up and get $200 in credit for your first 60 days with DigitalOcean.*
*This promotional offer applies to new accounts only.