CODEWITHSUNDEEP
mysql
jan5
jan5

Reputation: 1179

Is there any way to eliminate ERROR 1086 (HY000) in MYSQL

I am trying to export result of query to a text file using outfile command ERROR 1086 (HY000): File 'test.txt' already exists is prompted when i execute it second time.

        select * into outfile 'c:/test.txt' from test

I actaully want to replace the existing file.Is there any way to replace the existing file

Upvotes: 3

Views: 10367

Answers (4)

Lenny
Lenny

Reputation: 13

  1. make secure-file-priv="" in file C:/ProgramData/MySQL/MySQL Server 8.0/my.ini
  2. delete file 'c:/test.txt' from disk.
  3. if you want to put the file in a folder, change "/" to "\" i.e. select * into outfile 'c:\folder\test.txt' from test
  4. Also try to change the path to the file to another disk that has more rights, for example 'D:/test.txt'

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115650

From MySQL docs, SELECT ... INTO Syntax:

The SELECT ... INTO OUTFILE 'file_name' form of SELECT writes the selected rows to a file. The file is created on the server host, so you must have the FILE privilege to use this syntax. file_name cannot be an existing file, which among other things prevents files such as /etc/passwd and database tables from being destroyed. As of MySQL 5.0.19, the character_set_filesystem system variable controls the interpretation of the file name.

What you could do is use a client command such as mysql -e "SELECT ..." > file_name to generate the file.

Upvotes: 1

user319198
user319198

Reputation:

No, there's no way to overwrite it.

Refer From the docs:

Upvotes: 1

Devart
Devart

Reputation: 122042

From the reference - file_name cannot be an existing file

SELECT syntax

So, you should remove old file yourself, or specify another name.

Upvotes: 4

Related Questions