CODEWITHSUNDEEP
mysql
pocketfullofcheese
pocketfullofcheese

Reputation: 8847

why does SELECT INTO OUTFILE give file exists error even though file does not exist?

The file definitely does not exist, but I am getting an error anyway.

I do: 

$ rm /tmp/records_materialized_view.txt;
$ mysql ... 

> SELECT * FROM records_materialized_view INTO OUTFILE '/tmp/records_materialized_view.txt';

ERROR 1086 (HY000): File '/tmp/records_materialized_view.txt' already exists

Upvotes: 3

Views: 4390

Answers (2)

radtek
radtek

Reputation: 36280

Came across this answer when I had a similar issue.

I realized that SELECT INTO OUTFILE does not overwrite files, you have to clean them up yourself. So you will get this error the next time you write it. Not sure what is meant by in the above answer, that the files are written to a server file.

Also, SELECT INTO OUTFILE by default writes files relative to your db data directory. The default location i.e. 

/var/lib/mysql/

So if you check your db data directory, you should find your files there. Use an absolute path to control exactly where you want the file to end up. This is exactly what I was after, hopefully it helps somebody. Cheers.

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425311

SELECT INTO OUTFILE writes results to a server file.

Are you checking for the file existence on server?

If you want to select into a local file on your client machine, just redirect mysql output:

mysql mydb < script.sql > /tmp/records_materialized_view.txt

Upvotes: 4

Related Questions