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
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.