MySQL Error 1290: MySQL server is running with the -secure-file-priv

I just setup a MySQL server on my Ubuntu 16 Lionode, setup the SSH tunneling to connect to localhost on a windows client when connecting with putty. The MySQL client that I am using can see the server and the database/tables just fine, however when I attempt to LOAD data into the tables I am met with the error in the title:

Error Code: 1290. The MySQL server is running with the –secure-file-priv option so it cannot execute this statement

Here is the query I attempted to run:

LOAD DATA INFILE 'C:/Users/Administrator/Desktop/text.txt'

INTO TABLE database.table

FIELDS TERMINATED BY '|'

LINES TERMINATED BY '\n'

I've tried looking for the file that has this option to try to comment out but have not found it. Is there something I can do on the query to get around this or does anyone know what setting to change in what file within the Ubuntu installation for MySQL?

Thanks in advance

4 Replies

Which MySQL client you are using ? When you connect remotely you need to use server IP instead of localhost

I'm using MySQL Workbench. I followed the Linode instructions for setting up the MySQL server and pointing it to local and connecting to the server through Putty and creating a tunnel for localhost. The client connects fine, just not allowing me in load any data from files.

UPDATE:

I've found a little more information on the error and the consensus is that the secure-file-priv needs to be disabled/commented out. I have ran the query "SHOW VARIABLES LIKE "serverfilepriv"" to find the path and was directed to /var/lib/mysql-files/ on my Ubuntu server. However, going to this directory shows no files.

ls /var/lib/mysql-files/ results in no files listed and when using NANO against mysql-files it states it's a directory (just tested for good measure).

ls -la in that directory results in this:

~~![](<URL url=)http://s32.postimg.org/kqpcx2ocl/Capture.jpg" />

I'm logged in as the root account, is there something I'm missing that would cause this file to not show within the directory? Thanks~~

I was misinterpreting the documentation. So the /var/lib/mysql-files is the default directory that the MySQL Server will read files from using the LOAD/INSERT commands. The documentation states that the secure-file-priv needs to be set to blank in order to be disabled/open. I have ran the mysqld –verbose --help to find the list of commands and have attempted to set it to blank using the mysqld --secure-file-priv command but it's still showing the file directory as being the default. Has anyone figured out how to disable this feature? Thanks

Reply

Please enter an answer
Tips:

You can mention users to notify them: @username

You can use Markdown to format your question. For more examples see the Markdown Cheatsheet.

> I’m a blockquote.

I’m a blockquote.

[I'm a link] (https://www.google.com)

I'm a link

**I am bold** I am bold

*I am italicized* I am italicized

Community Code of Conduct