Asked  7 Months ago    Answers:  5   Viewed   69 times

I've written a big sql script that creates a CSV file. I want to call a cronjob every night to create a fresh CSV file and have it available on the website.

Say for example I'm store my file in '/home/sites/example.com/www/files/backup.csv'

and my SQL is

SELECT * INTO OUTFILE '/home/sites/example.com/www/files/backup.csv'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY 'n'
  FROM ( ....

MySQL gives me an error when the file already exists

File '/home/sites/example.com/www/files/backup.csv' already exists

Is there a way to make MySQL overwrite the file?

I could have PHP detect if the file exists and delete it before creating it again but it would be more succinct if I can do it directly in MySQL.

 Answers

36

No, there's no way to overwrite it. From the docs:

file_name cannot be an existing file, which among other things prevents files such as /etc/passwd and database tables from being destroyed.

It might be a better idea to use a different filename each night, as having multiple backups means you can recover from problems that have existed for more than a day. You could then maintain a symlink that always points at the latest complete csv.

Wednesday, March 31, 2021
 
Viralk
answered 7 Months ago
68

You need to escape the double quote as: " instead of ""

$result=mysql_query("select * from jos_users INTO OUTFILE 'users.csv' FIELDS ESCAPED BY '"' TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY 'n' ");

An un-escaped " will prematurely terminate the string.

Example:

This is incorrect: "A " is a double quote"
This is correct: "A " is a double quote"

Wednesday, March 31, 2021
 
DilbertDave
answered 7 Months ago
70

Try executing this SQL command:

> grant all privileges 
  on YOUR_DATABASE.* 
  to 'asdfsdf'@'localhost' 
  identified by 'your_password';
> flush privileges; 

It seems that you are having issues with connecting to the database and not writing to the folder you’re mentioning.

Also, make sure you have granted FILE to user 'asdfsdf'@'localhost'.

> GRANT FILE ON *.* TO 'asdfsdf'@'localhost';
Thursday, June 3, 2021
 
tadman
answered 5 Months ago
21

Argh. It was a freakin' typo, my 's should have been /'s

So my query is now this:

SELECT * FROM   mysql.db INTO OUTFILE "C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/db_pipe.csv" FIELDS TERMINATED BY '|' ENCLOSED BY '"'  LINES TERMINATED BY 'n';
Monday, August 9, 2021
 
exxed
answered 3 Months ago
66

Be default it stored in the MySQL's data directory. And you can check your data directory by command:

show variables like 'datadir';

You can't store it locally with this command running on remote server, you need to download the file from the server.

You can also use this command:

SELECT * FROM table INTO OUTFILE 'mysqlresults.csv' FIELDS TERMINATED BY ','OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY 'n' ;
Tuesday, October 12, 2021
 
FyodorX
answered 2 Weeks ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share