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

Question

I encountered such a problem  Error Code  1290  The MySQL server is running with the --secure-file-priv option so it cannot execute this statement when I tried to execute MySQL statement  Windows    SELECT   FROM xxxx WHERE XXX INTO OUTFILE  report csv      FIELDS TERMINATED BY         ENCLOSED BY         LINES TERMINATED BY   n    When I execute it without   INTO OUTFILE  report csv      FIELDS TERMINATED BY         ENCLOSED BY         LINES TERMINATED BY   n    Then it works  Also  the same statement with INTO OUTFILE xxx actually worked before I reinstalled the MySQL server    Anybody has ideas how to deal with this error

User · Answer

I had to set  C  ProgramData MySQL MySQL Server 8 0 my ini  secure-file-priv      When I commented line with secure-file-priv  secure-file-priv was null and I couldn t download data

User · Answer

If you changed my ini and restarted mysql and you still get this error please check your file path and replace     to       I solved my proplem after replacing

User · Answer

The code above exports data without the heading columns which is weird  Here s how to do it  You have to merge the two files later though using text a editor   SELECT column name FROM information schema columns WHERE table schema    my app db  AND table name    customers  INTO OUTFILE  C  ProgramData MySQL MySQL Server 5 6 Uploads customers heading cols csv  FIELDS TERMINATED BY    OPTIONALLY ENCLOSED BY     LINES TERMINATED BY

User · Answer

A quick answer  that doesn t require you to edit any configuration files  and works on other operating systems as well as Windows   is to just find the directory that you are allowed to save to using   mysql gt  SHOW VARIABLES LIKE  secure file priv    ------------------ -----------------------    Variable name      Value                    ------------------ -----------------------    secure file priv    var lib mysql-files     ------------------ -----------------------  1 row in set  0 06 sec    And then make sure you use that directory in your SELECT statement s INTO OUTFILE clause   SELECT   FROM xxxx WHERE XXX INTO OUTFILE   var lib mysql-files report csv      FIELDS TERMINATED BY         ENCLOSED BY         LINES TERMINATED BY   n      Original answer  I ve had the same problem since upgrading from MySQL 5 6 25 to 5 6 26   In my case  on Windows   looking at the MySQL56 Windows service shows me that the options settings file that is being used when the service starts is C  ProgramData MySQL MySQL Server 5 6 my ini  On linux the two most common locations are  etc my cnf or  etc mysql my cnf     Opening this file I can see that the secure-file-priv option has been added under the  mysqld  group in this new version of MySQL Server with a default value   secure-file-priv  C  ProgramData MySQL MySQL Server 5 6 Uploads   You could comment this  if you re in a non-production environment    or experiment with changing the setting  recently I had to set secure-file-priv      in order to disable the default    Don t forget to restart the service after making changes   Alternatively  you could try saving your output into the permitted folder  the location may vary depending on your installation    SELECT   FROM xxxx WHERE XXX INTO OUTFILE  C  ProgramData MySQL MySQL Server 5 6 Uploads report csv      FIELDS TERMINATED BY         ENCLOSED BY         LINES TERMINATED BY   n    It s more common to have comma seperate values using FIELDS TERMINATED BY      See below for an example  also showing a Linux path    SELECT   FROM table INTO OUTFILE   var lib mysql-files report csv      FIELDS TERMINATED BY     ENCLOSED BY         ESCAPED BY        LINES TERMINATED BY   n

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

Examples related to mysql