MySQL into output file error

Question

I am very new with SQL but I have to extract some fields of a table stored in a sql file.

I have installed mysql and create the database and source the file. Now I wanted to execute a sql request in order to read all the elts of the table, extract the interesting fields and write them into cvs file:

SELECT * INTO OUTFILE '/home/cr/database/Dump2/program_info.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' FROM program_info;

When running the command I have the following error message:

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

Does anyone knows how to solve it ? I have struggled with this message all the afternoon but could not .

I am working on Linux ubuntu and my config file is /etc/my.cfg

[mysqld] read-only = 0 secure-file-priv = ""

Thank you for your help

check the mysqld command line. just because the directive isn't in the .ini/.cnf file doesn't mean it can't be specified elsewhere. — Marc B
– Marc B, Commented Jul 13, 2016 at 14:33
Thank you, running your command I have the following output: mysql 29141 0.1 1.1 1915592 183236 ? Ssl 16:51 0:00 /usr/sbin/mysqld — clam37
– clam37, Commented Jul 13, 2016 at 14:55

Shubham Mittal · Accepted Answer · 2016-07-13 16:02:58Z

3

MySQL is actually working as intended. The MySQL server booted with the --secure-file-priv option which basically restricts you from saving output into random directories. You need to check the startup parameters.

To do so, run the following command in the MySQL shell:

SHOW VARIABLES LIKE 'secure_file_priv';

The output would be what the MySQL Server currently thinks is the permitted location. You should be able to save your output to this location.

To disable the restriction, you need to edit the configuration file that declares it and restart the MySQL Server after changing the option.

MySQL could be reading the my.ini (or my.cnf) configuration file from a variety of locations. See Using Option Files for more information.

answered Jul 13, 2016 at 16:02

Shubham Mittal

1,57518 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TheBestBigAl Over a year ago

My files are outputting to the secure_file_priv location, but is it possible to put 2 subfolders in that directory and have it output to those? Currently it gives me a "permission denied" message but I'm not sure if there is a workaround so that I can export 2 different data sets to 2 locations, as they are ultimately still inside the secure_file_priv directory.

Collectives™ on Stack Overflow

MySQL into output file error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related