0

I can not write my query result to local file. I am sure my query produces output. I can view it. I use MySQL workbench on Ubuntu 18.04. After I added this line to write the output to a file:

INTO OUTFILE '/var/lib/mysql-files/myproject/out_files/mydata.txt' FIELDS TERMINATED BY ',';

I get this error:

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

I looked int previous posts. I made sure that the /var/lib/mysql-files is the specified path for writing. This is the output I get: mysql> SELECT @@GLOBAL.secure_file_priv;

+---------------------------+
| @@GLOBAL.secure_file_priv |
+---------------------------+
| /var/lib/mysql-files/     |
+---------------------------+
1 row in set (0.00 sec)

What is the problem?

lurker
  • 56,987
  • 9
  • 69
  • 103
user9371654
  • 2,160
  • 16
  • 45
  • 78
  • 1
    Possible duplicate of [How should I tackle --secure-file-priv in MySQL?](https://stackoverflow.com/questions/32737478/how-should-i-tackle-secure-file-priv-in-mysql) – abkrim Mar 03 '19 at 19:53

1 Answers1

0

The secure_file_priv must name the directory you output into exactly. You can't output into subdirectories.

Here's a demo I just did using MySQL 5.6 on my laptop:

mysql> select @@global.secure_file_priv;
+---------------------------+
| @@global.secure_file_priv |
+---------------------------+
| /tmp/                     |
+---------------------------+

mysql> select 123 into outfile '/tmp/foo';
Query OK, 1 row affected (0.00 sec)

Check that the data was written out:

$ cat /tmp/foo
123

Then try a subdirectory:

mysql> select 456 into outfile '/tmp/a/b/text';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

Even if I mkdir -p /tmp/a/b to make sure the subdirectory exists before I try this, it still fails.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828