How should I tackle --secure-file-priv in MySQL?

Question

I am learning MySQL and tried using a LOAD DATA clause. When I used it as below:

LOAD DATA INFILE "text.txt" INTO table mytable;

I got the following error:

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

How do I tackle this error?

I have checked another question on the same error message, but still can’t find a solution.

I am using MySQL 5.6

Answer 1

It's working as intended. Your MySQL server has been started with --secure-file-priv option which basically limits from which directories you can load files using LOAD DATA INFILE.

You may use SHOW VARIABLES LIKE "secure_file_priv"; to see the directory that has been configured.

You have two options:

1. Move your file to the directory specified by secure-file-priv.
2. Disable secure-file-priv. This must be removed from startup and cannot be modified dynamically. To do this check your MySQL start up parameters (depending on platform) and my.ini.

Answer 2

I solved it using the LOCAL option in the command:

LOAD DATA LOCAL INFILE "text.txt" INTO TABLE mytable;

You can find more info here.

If LOCAL is specified, the file is read by the client program on the client host and sent to the server. The file can be given as a full path name to specify its exact location. If given as a relative path name, the name is interpreted relative to the directory in which the client program was started.

Answer 3

On Ubuntu 14 and Mysql 5.5.53 this setting seems to be enabled by default. To disable it you need to add secure-file-priv = "" to your my.cnf file under the mysqld config group. eg:-

[mysqld]
secure-file-priv = ""

Answer 4

I'm working on MySQL5.7.11 on Debian, the command that worked for me to see the directory is:

mysql> SELECT @@global.secure_file_priv;

Answer 5

Here is what worked for me in Windows 7 to disable secure-file-priv (Option #2 from vhu's answer):

1. Stop the MySQL server service by going into services.msc.
2. Go to C:\ProgramData\MySQL\MySQL Server 5.6 (ProgramData was a hidden folder in my case).
3. Open the my.ini file in Notepad.
4. Search for 'secure-file-priv'.
5. Comment the line out by adding '#' at the start of the line. For MySQL Server 5.7.16 and above, commenting won't work. You have to set it to an empty string like this one - secure-file-priv=""
6. Save the file.
7. Start the MySQL server service by going into services.msc.

Answer 6

@vhu I did the SHOW VARIABLES LIKE "secure_file_priv"; and it returned C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\ so when I plugged that in, it still didn't work.

When I went to the my.ini file directly I discovered that the path is formatted a bit differently:

C:/ProgramData/MySQL/MySQL Server 8.0/Uploads

Then when I ran it with that, it worked. The only difference was the direction of the slashes.

Answer 7

If the file is local to your machine use the LOCAL in your command

LOAD DATA LOCAL INFILE "text.txt" INTO table mytable;

Answer 8

This thread has been viewed 570k times at the time of this post. Honestly when did MySQL become our over protective unreasonable mom? What a time consuming attempt at security - which really only serves to shackle us!

After many searches and many attempts everything failed. My solution:

What worked for me was:

1. Import the .csv file via PhpMyAdmin import on older box (if large do at cmd line)
2. Generate a .sql file.
3. Download .sql file.
4. Import .sql file via MySQL Workbench.

Answer 9

The thing that worked for me:

1. Put your file inside of the folder specified in secure-file-priv.

   To find that type:

   mysql> show variables like "secure_file_priv";  
   

2. Check if you have local_infile = 1.

   Do that typing:

   mysql> show variables like "local_infile";
   

   If you get:

   +---------------+-------+
   | Variable_name | Value |
   +---------------+-------+
   | local_infile  | OFF   |
   +---------------+-------+
   

   Then set it to one typing:

   mysql> set global local_infile = 1;
   

3. Specify the full path for your file. In my case:

   mysql> load data infile "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/file.txt" into table test;
   

Answer 10

I had the same problem with 'secure-file-priv'. Commenting in the .ini file didn't work and neither did moving file in directory specified by 'secure-file-priv'.

Finally, as dbc suggested, making 'secure-file-priv' equal to an empty string worked. So if anyone is stuck after trying answers above, hopefully doing this will help.

Answer 11

For mysql 8.0 version you can do this:

mysql.server stop
mysql.server start --secure-file-priv=''

It worked for me on Mac High Sierra.

Answer 12

If you're running on Ubuntu, you may also need to configure Apparmor to allow MySQL to write to your folder, e.g. here's my configuration:

Add this line to file /etc/apparmor.d/usr.sbin.mysqld :

/var/lib/mysql-files/* rw

Then add these 2 config lines to /etc/mysql/my.cnf sections:

[client]
loose-local-infile = 1

[mysqld]
secure-file-priv = ""

Here's my SQL:

select id from blahs into outfile '/var/lib/mysql-files/blahs';

It worked for me. Good luck!

Answer 13

I created a NodeJS import script if you are running nodeJS and you data is in the following form (double quote + comma and \n new line)

INSERT INTO <your_table> VALUEs( **CSV LINE **)

This one is configured to run on http://localhost:5000/import.

I goes line by line and creates query string

"city","city_ascii","lat","lng","country","iso2","iso3","id"
"Tokyo","Tokyo","35.6850","139.7514","Japan","JP","JPN","1392685764",
...

server.js

const express = require('express'),
   cors = require('cors'),
   bodyParser = require('body-parser'),
   cookieParser = require('cookie-parser'),
   session = require('express-session'),
   app = express(),
   port = process.env.PORT || 5000,
   pj = require('./config/config.json'),
   path = require('path');

app.use(bodyParser.json());
app.use(cookieParser());
app.use(cors());


app.use(
   bodyParser.urlencoded({
      extended: false,
   })
);

var Import = require('./routes/ImportRoutes.js');

app.use('/import', Import);
if (process.env.NODE_ENV === 'production') {
   // set static folder
   app.use(express.static('client/build'));

   app.get('*', (req, res) => {
      res.sendFile(path.resolve(__dirname, 'client', 'build', 'index.html'));
   });
}

app.listen(port, function () {
   console.log('Server is running on port: ' + port);
});

ImportRoutes.js

const express = require('express'),
   cors = require('cors'),
   fs = require('fs-extra'),
   byline = require('byline'),
   db = require('../database/db'),
   importcsv = express.Router();

importcsv.use(cors());

importcsv.get('/csv', (req, res) => {

   function processFile() {
      return new Promise((resolve) => {
         let first = true;
         var sql, sqls;
         var stream = byline(
            fs.createReadStream('../PATH/TO/YOUR!!!csv', {
               encoding: 'utf8',
            })
         );

         stream
            .on('data', function (line, err) {
               if (line !== undefined) {
                  sql = 'INSERT INTO <your_table> VALUES (' + line.toString() + ');';
                  if (first) console.log(sql);
                  first = false;
                  db.sequelize.query(sql);
               }
            })
            .on('finish', () => {
               resolve(sqls);
            });
      });
   }

   async function startStream() {
      console.log('started stream');
      const sqls = await processFile();
      res.end();
      console.log('ALL DONE');
   }

   startStream();
});

module.exports = importcsv;

db.js is the config file

const Sequelize = require('sequelize');
const db = {};
const sequelize = new Sequelize(
   config.global.db,
   config.global.user,
   config.global.password,
   {
      host: config.global.host,
      dialect: 'mysql',
      logging: console.log,
      freezeTableName: true,

      pool: {
         max: 5,
         min: 0,
         acquire: 30000,
         idle: 10000,
      },
   }
);

db.sequelize = sequelize;
db.Sequelize = Sequelize;

module.exports = db;

Disclaimer: This is not a perfect solution - I am only posting it for devs who are under a timeline and have lots of data to import and are encountering this ridiculous issue. I lost a lot of time on this and I hope to spare another dev the same lost time.

Answer 14

I had all sorts of problems with this. I was changing my.cnf and all sorts of crazy things that other versions of this problem tried to show.

What worked for me:

The error I was getting

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

I was able to fix it by opening /usr/local/mysql/support-files/mysql.server and changing the following line:

$bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" -- $other_args >/dev/null &
  wait_for_pid created "$!" "$mysqld_pid_file_path"; return_value=$?

to

$bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" --secure-file-priv="" $other_args >/dev/null &
  wait_for_pid created "$!" "$mysqld_pid_file_path"; return_value=$?

Answer 15

This worked for me (had the additional problem of not being able to use LOCAL with my current MySQL version in the statement LOAD DATE INFILE ... )

sudo /usr/local/mysql/support-files/mysql.server start --secure-file-priv='' --local-infile

The above works for that given path on my machine; you may have to adjust your path.

Then use:

mysql -u root -p

One important point is that you should have the CSV in the MySQL data folder. In my machine it is located at: /usr/local/mysql-8.0.18-macos10.14-x86_64/data

You can change the folder permission if needed to drop a CSV in the data folder.

Setup:
macOS Catalina version 10.15.5
MySQL version 8.0.18

Answer 16

MySQL use this system variable to control where you can import you files

mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv | NULL  |
+------------------+-------+

So problem is how to change system variables such as secure_file_priv.

1. shutdown mysqld
2. sudo mysqld_safe --secure_file_priv=""

now you may see like this:

mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv |       |
+------------------+-------+

Answer 17

At macOS Catalina, I followed this steps to set secure_file_priv

1.Stop MySQL service

 sudo /usr/local/mysql/support-files/mysql.server stop

2.Restart MYSQL assigning --secure_file_priv system variables

sudo /usr/local/mysql/support-files/mysql.server start --secure-file-priv=YOUR_FILE_DIRECTORY

Note: Adding empty value fix the issue for me, and MYSQL will export data to directory /usr/local/mysql/data/YOUR_DB_TABLE/EXPORT_FILE

sudo /usr/local/mysql/support-files/mysql.server start --secure-file-priv=

Thanks

Answer 18

I had this problem on windows 10. "--secure-file-priv in MySQL" To solve this I did the following.

1. In windows search (bottom left) I typed "powershell".
2. Right clicked on powershell and ran as admin.
3. Navigated to the server bin file. (C:\Program Files\MySQL\MySQL Server 5.6\bin);
4. Typed ./mysqld
5. Hit "enter"

The server started up as expected.

Answer 19

Without changing any of the configuration files..

1. look for the value of secure_file_priv using the command posted by @vhu: SHOW VARIABLES LIKE "secure_file_priv".
2. define the full path for your query such as: select * from table into outfile 'secure_file_priv_PATH/OUTPUT-FILE' ... rest of your query

this worked for my in mysql-shell on ubuntu 18.04 LTS mysql 5.7.29

Answer 20

in Linux you have to edit my.cnf file in

/etc/mysql/my.cnf

and change 26 line number param like this :

secure-file-priv= <your data path directory like /home/user/data>

then restart your MySQL and try again.

in docker you have to mount your my.cnf file with my.cnf file in your container with this command in docker-compose or add manually :

volumes:
  - ./persistent:/var/lib/mysql
  - ./conf/my.cnf:/etc/mysql/my.cnf

next change /conf/my.cnf in your host and config secure-file-priv param like the upper approach, in addition, you have to mount your data in mysql container and set that path for secure-file-priv param and restart your services and finally, you can load your data.

you can check your config with this command :

SHOW VARIABLES LIKE "secure_file_priv";

Answer 21

For MacOS Mojave running MySQL 5.6.23 I had this problem with writing files, but not loading them. (Not seen with previous versions of Mac OS). As most of the answers to this question have been for other systems, I thought I would post the my.cnf file that cured this (and a socket problems too) in case it is of help to other Mac users. This is /etc/my.cnf

[client]
default-character-set=utf8

[mysqld]
character-set-server=utf8
secure-file-priv = ""
skip-external-locking

(The internationalization is irrelevant to the question.)

Nothing else required. Just turn the MySQL server off and then on again in Preferences (we are talking Mac) for this to take.

Answer 22

I added the LOCAL to the command, but it made another problem:

Loading local data is disabled - this must be enabled on both the client and server sides

For solving this I simply followed three steps in here