MySQL(5.6) "select * into outfile.." not creating files

Question

When I use this command on my local host as the root user it runs without issue but I can't seem to find the actual file.

SELECT * INTO OUTFILE 'C:\...\tableName.txt' 
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
FROM tableName;

When I try to run the query again it says the file is already created even when it's obviously not.

EDIT: fixed my query syntax

Is the MySQL server running under a user account that has permissions for the location? — Uueerdo, Jul 20 '15 at 23:36
@Uueerdo yes I had access to the location, the issue was I wasn't looking in proper the data directory — TCulos, Jul 21 '15 at 20:09

BK435 · Accepted Answer · 2015-07-21T00:38:04.600

There is an unnecessary FROM after your *. your query should look more like this:

SELECT * INTO OUTFILE 'C:\...\tableName.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\r\n'
FROM tableName;

Note: make sure that mysql has permission to write to 'C:\...\tableName.txt'

As for the file already being created error:

The file may have been created in another directory where mysql actually does have permission to write to, such as the data directory. This is why you are getting the message that the file has already been created now that you have run the query more than once.

From mysql command line run show variables like '%dirdata%';, you should see output that looks something like:

mysql> show variables like '%datadir%';
+---------------+-------------------------------------+
| Variable_name | Value                               |
+---------------+-------------------------------------+
| datadir       | c:\wamp\bin\mysql\mysql5.6.17\data\ |
+---------------+-------------------------------------+
1 row in set (0.35 sec)

Navigate in windows to that folder and you should find your file there.

score 1 · Answer 2 · answered Jul 21 '15 at 00:53

SELECT r INTO OUTFILE 'c:\dev\myA2.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM a2;

-- leads me to believe it works first time with rowcount output
-- but it does not put file in dev
-- run it again it says error 1086: File 'c:devmyA2.txt' already exists

so that means it wrote it to c:

the default of what mysql query engine has for c: at that time

I did not hunt for it !

the following works great (note the double slashes \\):

SELECT r INTO OUTFILE 'c:\\dev\\drew_so_answers\\myA2.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM a2;

MySQL(5.6) "select * into outfile.." not creating files

2 Answers2