error “--secure-file-priv option” when save selection to csv












2















I try to save a very large select (more than 70 milion rows into csv file) on Win 2012 R2 server



I execute query as root , but I think there is still some problem with privileges



select * 
INTO OUTFILE 'D:my_foldermy_file.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY 'n'
from my_table
where
...


Error




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




Thank you in advance for any help !










share|improve this question




















  • 1





    do a show show variables like 'secure_file_priv'; and give file path in that foldder or turn it off from cnf file and restart instance. You may also need to have write permissions on OS folder to write with MySQL running user.

    – Nawaz Sohail
    Dec 9 '15 at 13:10
















2















I try to save a very large select (more than 70 milion rows into csv file) on Win 2012 R2 server



I execute query as root , but I think there is still some problem with privileges



select * 
INTO OUTFILE 'D:my_foldermy_file.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY 'n'
from my_table
where
...


Error




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




Thank you in advance for any help !










share|improve this question




















  • 1





    do a show show variables like 'secure_file_priv'; and give file path in that foldder or turn it off from cnf file and restart instance. You may also need to have write permissions on OS folder to write with MySQL running user.

    – Nawaz Sohail
    Dec 9 '15 at 13:10














2












2








2


1






I try to save a very large select (more than 70 milion rows into csv file) on Win 2012 R2 server



I execute query as root , but I think there is still some problem with privileges



select * 
INTO OUTFILE 'D:my_foldermy_file.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY 'n'
from my_table
where
...


Error




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




Thank you in advance for any help !










share|improve this question
















I try to save a very large select (more than 70 milion rows into csv file) on Win 2012 R2 server



I execute query as root , but I think there is still some problem with privileges



select * 
INTO OUTFILE 'D:my_foldermy_file.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY 'n'
from my_table
where
...


Error




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




Thank you in advance for any help !







mysql permissions






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Dec 15 '15 at 8:39









marc_s

7,03053749




7,03053749










asked Dec 9 '15 at 12:32









TorenToren

131117




131117








  • 1





    do a show show variables like 'secure_file_priv'; and give file path in that foldder or turn it off from cnf file and restart instance. You may also need to have write permissions on OS folder to write with MySQL running user.

    – Nawaz Sohail
    Dec 9 '15 at 13:10














  • 1





    do a show show variables like 'secure_file_priv'; and give file path in that foldder or turn it off from cnf file and restart instance. You may also need to have write permissions on OS folder to write with MySQL running user.

    – Nawaz Sohail
    Dec 9 '15 at 13:10








1




1





do a show show variables like 'secure_file_priv'; and give file path in that foldder or turn it off from cnf file and restart instance. You may also need to have write permissions on OS folder to write with MySQL running user.

– Nawaz Sohail
Dec 9 '15 at 13:10





do a show show variables like 'secure_file_priv'; and give file path in that foldder or turn it off from cnf file and restart instance. You may also need to have write permissions on OS folder to write with MySQL running user.

– Nawaz Sohail
Dec 9 '15 at 13:10










3 Answers
3






active

oldest

votes


















1














On Win2012 I found file my.ini.



In the file there is a definition of secure_file_priv - a folder when I can download to or uplload from ,



So I change my query as following:



 SELECT * 
INTO OUTFILE 'D:<folder_defined_as_secure_file_priv>my_file.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY 'n'
FROM my_table
WHERE
...





share|improve this answer

































    0














    SHOW VARIABLES LIKE "secure_file_priv";

    secure_file_priv C:ProgramDataMySQLMySQL Server 5.7Uploads

    SELECT * INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/algo.csv'
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY 'n' FROM sams.trafico;





    share|improve this answer





















    • 1





      Would you be able to add a little explanation as to what the commands are doing and how this solves the posters problem?

      – Shaulinator
      May 2 '18 at 18:22



















    0














    I am using MySQL Server 8.0 on Windows 10. I tried to load data from csv file using the following commands:



    LOAD DATA INFILE 'C:ProgramDataMySQLMySQL Server 8.0UploadsWorld_cup_dataset.csv'
    INTO TABLE trial
    FIELDS TERMINATED BY ',';



    When I run above commands, I got



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



    I tried many methods to solve the problem. Finally, I changed the '" characters in the file path to '/' characters. I mean my new file path became C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/World_cup_dataset.csv and my problem were fixed. If you look at my.ini file located in the MySQL Server folder, the path of secure-file-priv is "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads". So, this solution makes sense.






    share|improve this answer








    New contributor




    MESV is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
    Check out our Code of Conduct.




















      Your Answer








      StackExchange.ready(function() {
      var channelOptions = {
      tags: "".split(" "),
      id: "182"
      };
      initTagRenderer("".split(" "), "".split(" "), channelOptions);

      StackExchange.using("externalEditor", function() {
      // Have to fire editor after snippets, if snippets enabled
      if (StackExchange.settings.snippets.snippetsEnabled) {
      StackExchange.using("snippets", function() {
      createEditor();
      });
      }
      else {
      createEditor();
      }
      });

      function createEditor() {
      StackExchange.prepareEditor({
      heartbeatType: 'answer',
      autoActivateHeartbeat: false,
      convertImagesToLinks: false,
      noModals: true,
      showLowRepImageUploadWarning: true,
      reputationToPostImages: null,
      bindNavPrevention: true,
      postfix: "",
      imageUploader: {
      brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
      contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
      allowUrls: true
      },
      onDemand: true,
      discardSelector: ".discard-answer"
      ,immediatelyShowMarkdownHelp:true
      });


      }
      });














      draft saved

      draft discarded


















      StackExchange.ready(
      function () {
      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f123290%2ferror-secure-file-priv-option-when-save-selection-to-csv%23new-answer', 'question_page');
      }
      );

      Post as a guest















      Required, but never shown

























      3 Answers
      3






      active

      oldest

      votes








      3 Answers
      3






      active

      oldest

      votes









      active

      oldest

      votes






      active

      oldest

      votes









      1














      On Win2012 I found file my.ini.



      In the file there is a definition of secure_file_priv - a folder when I can download to or uplload from ,



      So I change my query as following:



       SELECT * 
      INTO OUTFILE 'D:<folder_defined_as_secure_file_priv>my_file.csv'
      FIELDS TERMINATED BY ','
      ENCLOSED BY '"'
      LINES TERMINATED BY 'n'
      FROM my_table
      WHERE
      ...





      share|improve this answer






























        1














        On Win2012 I found file my.ini.



        In the file there is a definition of secure_file_priv - a folder when I can download to or uplload from ,



        So I change my query as following:



         SELECT * 
        INTO OUTFILE 'D:<folder_defined_as_secure_file_priv>my_file.csv'
        FIELDS TERMINATED BY ','
        ENCLOSED BY '"'
        LINES TERMINATED BY 'n'
        FROM my_table
        WHERE
        ...





        share|improve this answer




























          1












          1








          1







          On Win2012 I found file my.ini.



          In the file there is a definition of secure_file_priv - a folder when I can download to or uplload from ,



          So I change my query as following:



           SELECT * 
          INTO OUTFILE 'D:<folder_defined_as_secure_file_priv>my_file.csv'
          FIELDS TERMINATED BY ','
          ENCLOSED BY '"'
          LINES TERMINATED BY 'n'
          FROM my_table
          WHERE
          ...





          share|improve this answer















          On Win2012 I found file my.ini.



          In the file there is a definition of secure_file_priv - a folder when I can download to or uplload from ,



          So I change my query as following:



           SELECT * 
          INTO OUTFILE 'D:<folder_defined_as_secure_file_priv>my_file.csv'
          FIELDS TERMINATED BY ','
          ENCLOSED BY '"'
          LINES TERMINATED BY 'n'
          FROM my_table
          WHERE
          ...






          share|improve this answer














          share|improve this answer



          share|improve this answer








          edited Dec 15 '15 at 8:39









          marc_s

          7,03053749




          7,03053749










          answered Dec 9 '15 at 13:35









          TorenToren

          131117




          131117

























              0














              SHOW VARIABLES LIKE "secure_file_priv";

              secure_file_priv C:ProgramDataMySQLMySQL Server 5.7Uploads

              SELECT * INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/algo.csv'
              FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY 'n' FROM sams.trafico;





              share|improve this answer





















              • 1





                Would you be able to add a little explanation as to what the commands are doing and how this solves the posters problem?

                – Shaulinator
                May 2 '18 at 18:22
















              0














              SHOW VARIABLES LIKE "secure_file_priv";

              secure_file_priv C:ProgramDataMySQLMySQL Server 5.7Uploads

              SELECT * INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/algo.csv'
              FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY 'n' FROM sams.trafico;





              share|improve this answer





















              • 1





                Would you be able to add a little explanation as to what the commands are doing and how this solves the posters problem?

                – Shaulinator
                May 2 '18 at 18:22














              0












              0








              0







              SHOW VARIABLES LIKE "secure_file_priv";

              secure_file_priv C:ProgramDataMySQLMySQL Server 5.7Uploads

              SELECT * INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/algo.csv'
              FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY 'n' FROM sams.trafico;





              share|improve this answer















              SHOW VARIABLES LIKE "secure_file_priv";

              secure_file_priv C:ProgramDataMySQLMySQL Server 5.7Uploads

              SELECT * INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/algo.csv'
              FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY 'n' FROM sams.trafico;






              share|improve this answer














              share|improve this answer



              share|improve this answer








              edited May 2 '18 at 18:37









              Shaulinator

              2,4181622




              2,4181622










              answered May 2 '18 at 18:07









              user150497user150497

              1




              1








              • 1





                Would you be able to add a little explanation as to what the commands are doing and how this solves the posters problem?

                – Shaulinator
                May 2 '18 at 18:22














              • 1





                Would you be able to add a little explanation as to what the commands are doing and how this solves the posters problem?

                – Shaulinator
                May 2 '18 at 18:22








              1




              1





              Would you be able to add a little explanation as to what the commands are doing and how this solves the posters problem?

              – Shaulinator
              May 2 '18 at 18:22





              Would you be able to add a little explanation as to what the commands are doing and how this solves the posters problem?

              – Shaulinator
              May 2 '18 at 18:22











              0














              I am using MySQL Server 8.0 on Windows 10. I tried to load data from csv file using the following commands:



              LOAD DATA INFILE 'C:ProgramDataMySQLMySQL Server 8.0UploadsWorld_cup_dataset.csv'
              INTO TABLE trial
              FIELDS TERMINATED BY ',';



              When I run above commands, I got



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



              I tried many methods to solve the problem. Finally, I changed the '" characters in the file path to '/' characters. I mean my new file path became C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/World_cup_dataset.csv and my problem were fixed. If you look at my.ini file located in the MySQL Server folder, the path of secure-file-priv is "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads". So, this solution makes sense.






              share|improve this answer








              New contributor




              MESV is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
              Check out our Code of Conduct.

























                0














                I am using MySQL Server 8.0 on Windows 10. I tried to load data from csv file using the following commands:



                LOAD DATA INFILE 'C:ProgramDataMySQLMySQL Server 8.0UploadsWorld_cup_dataset.csv'
                INTO TABLE trial
                FIELDS TERMINATED BY ',';



                When I run above commands, I got



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



                I tried many methods to solve the problem. Finally, I changed the '" characters in the file path to '/' characters. I mean my new file path became C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/World_cup_dataset.csv and my problem were fixed. If you look at my.ini file located in the MySQL Server folder, the path of secure-file-priv is "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads". So, this solution makes sense.






                share|improve this answer








                New contributor




                MESV is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                Check out our Code of Conduct.























                  0












                  0








                  0







                  I am using MySQL Server 8.0 on Windows 10. I tried to load data from csv file using the following commands:



                  LOAD DATA INFILE 'C:ProgramDataMySQLMySQL Server 8.0UploadsWorld_cup_dataset.csv'
                  INTO TABLE trial
                  FIELDS TERMINATED BY ',';



                  When I run above commands, I got



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



                  I tried many methods to solve the problem. Finally, I changed the '" characters in the file path to '/' characters. I mean my new file path became C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/World_cup_dataset.csv and my problem were fixed. If you look at my.ini file located in the MySQL Server folder, the path of secure-file-priv is "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads". So, this solution makes sense.






                  share|improve this answer








                  New contributor




                  MESV is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                  Check out our Code of Conduct.










                  I am using MySQL Server 8.0 on Windows 10. I tried to load data from csv file using the following commands:



                  LOAD DATA INFILE 'C:ProgramDataMySQLMySQL Server 8.0UploadsWorld_cup_dataset.csv'
                  INTO TABLE trial
                  FIELDS TERMINATED BY ',';



                  When I run above commands, I got



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



                  I tried many methods to solve the problem. Finally, I changed the '" characters in the file path to '/' characters. I mean my new file path became C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/World_cup_dataset.csv and my problem were fixed. If you look at my.ini file located in the MySQL Server folder, the path of secure-file-priv is "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads". So, this solution makes sense.







                  share|improve this answer








                  New contributor




                  MESV is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                  Check out our Code of Conduct.









                  share|improve this answer



                  share|improve this answer






                  New contributor




                  MESV is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                  Check out our Code of Conduct.









                  answered 10 mins ago









                  MESVMESV

                  1




                  1




                  New contributor




                  MESV is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                  Check out our Code of Conduct.





                  New contributor





                  MESV is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                  Check out our Code of Conduct.






                  MESV is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                  Check out our Code of Conduct.






























                      draft saved

                      draft discarded




















































                      Thanks for contributing an answer to Database Administrators Stack Exchange!


                      • Please be sure to answer the question. Provide details and share your research!

                      But avoid



                      • Asking for help, clarification, or responding to other answers.

                      • Making statements based on opinion; back them up with references or personal experience.


                      To learn more, see our tips on writing great answers.




                      draft saved


                      draft discarded














                      StackExchange.ready(
                      function () {
                      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f123290%2ferror-secure-file-priv-option-when-save-selection-to-csv%23new-answer', 'question_page');
                      }
                      );

                      Post as a guest















                      Required, but never shown





















































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown

































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown







                      Popular posts from this blog

                      Liste der Baudenkmale in Friedland (Mecklenburg)

                      Single-Malt-Whisky

                      Czorneboh