error “--secure-file-priv option” when save selection to csv
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
edited Dec 15 '15 at 8:39
marc_s
7,03053749
asked Dec 9 '15 at 12:32
TorenToren
131117
add a comment |
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
edited Dec 15 '15 at 8:39
marc_s
7,03053749
asked Dec 9 '15 at 12:32
TorenToren
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
add a comment |
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
edited Dec 15 '15 at 8:39
marc_s
7,03053749
asked Dec 9 '15 at 12:32
TorenToren
131117
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
mysql permissions
edited Dec 15 '15 at 8:39
marc_s
7,03053749
asked Dec 9 '15 at 12:32
TorenToren
131117
edited Dec 15 '15 at 8:39
marc_s
7,03053749
asked Dec 9 '15 at 12:32
TorenToren
131117
edited Dec 15 '15 at 8:39
marc_s
7,03053749
edited Dec 15 '15 at 8:39
marc_s
7,03053749
edited Dec 15 '15 at 8:39
marc_s
7,03053749
7,03053749
asked Dec 9 '15 at 12:32
TorenToren
131117
asked Dec 9 '15 at 12:32
TorenToren
131117
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
add a comment |
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
add a comment |
3 Answers
3
active
oldest
votes
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
...
edited Dec 15 '15 at 8:39
marc_s
7,03053749
answered Dec 9 '15 at 13:35
TorenToren
131117
add a comment |
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;
edited May 2 '18 at 18:37
Shaulinator
2,4181622
answered May 2 '18 at 18:07
user150497user150497
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
add a comment |
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.
answered 10 mins ago
MESVMESV
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.
add a comment |
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
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
...
edited Dec 15 '15 at 8:39
marc_s
7,03053749
answered Dec 9 '15 at 13:35
TorenToren
131117
add a comment |
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
...
edited Dec 15 '15 at 8:39
marc_s
7,03053749
answered Dec 9 '15 at 13:35
TorenToren
131117
add a comment |
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
...
edited Dec 15 '15 at 8:39
marc_s
7,03053749
answered Dec 9 '15 at 13:35
TorenToren
131117
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
...
edited Dec 15 '15 at 8:39
marc_s
7,03053749
answered Dec 9 '15 at 13:35
TorenToren
131117
edited Dec 15 '15 at 8:39
marc_s
7,03053749
edited Dec 15 '15 at 8:39
marc_s
7,03053749
edited Dec 15 '15 at 8:39
marc_s
7,03053749
7,03053749
answered Dec 9 '15 at 13:35
TorenToren
131117
answered Dec 9 '15 at 13:35
TorenToren
131117
answered Dec 9 '15 at 13:35
TorenToren
131117
131117
add a comment |
add a comment |
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;
edited May 2 '18 at 18:37
Shaulinator
2,4181622
answered May 2 '18 at 18:07
user150497user150497
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
add a comment |
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;
edited May 2 '18 at 18:37
Shaulinator
2,4181622
answered May 2 '18 at 18:07
user150497user150497
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
add a comment |
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;
edited May 2 '18 at 18:37
Shaulinator
2,4181622
answered May 2 '18 at 18:07
user150497user150497
1
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;
edited May 2 '18 at 18:37
Shaulinator
2,4181622
answered May 2 '18 at 18:07
user150497user150497
1
edited May 2 '18 at 18:37
Shaulinator
2,4181622
edited May 2 '18 at 18:37
Shaulinator
2,4181622
edited May 2 '18 at 18:37
Shaulinator
2,4181622
2,4181622
answered May 2 '18 at 18:07
user150497user150497
1
answered May 2 '18 at 18:07
user150497user150497
1
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
add a comment |
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
add a comment |
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.
answered 10 mins ago
MESVMESV
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.
add a comment |
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.
answered 10 mins ago
MESVMESV
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.
add a comment |
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.
answered 10 mins ago
MESVMESV
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.
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.
answered 10 mins ago
MESVMESV
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.
answered 10 mins ago
MESVMESV
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.
answered 10 mins ago
MESVMESV
1
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.
add a comment |
add a comment |
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
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