Can't find the file created by outfile in MySQL
MySQL may be writing the file into its own data directory, like /var/lib/mysql/<databasename>
for example. To specify the path, use a full path.
However, it must be a directory that is writable by the user account the MySQL server daemon is running under. For that reason, I'll often use /tmp
:
Specify the path you want to write to as in:
INTO OUTFILE '/tmp/mydata.csv'
And note that MySQL will write the file on the MySQL server, not on your client machine. Therefore remote connections will create output files on the remote server. See also SELECT INTO OUTFILE local ? for more details and workarounds.
Systemd & Linux
A note about writing to /tmp
on a Linux system running systemd
:
Some years after originally posting this, I found myself unable to locate a file written to /tmp
via
...INTO OUTFILE '/tmp/outfile.csv'
on a MariaDB 5.5 server running Fedora Linux with systemd
. Instead of writing the file directly to /tmp/outfile.csv
as specified, that directory and file were created beneath a systemd directory in /tmp
:
/tmp/systemd-mariadb.service-XXXXXXX/tmp/outfile.csv
While the file outfile.csv
itself and the tmp/
subdirectory were both created world-writable, the systemd service directory itself has 700 permissions and is root-owned, requiring sudo
access to retrieve the file within it.
Rather than specifying the absolute path in MariaDB as /tmp/outfile.csv
and specifying it relatively as outfile.csv
, the file was written as expected into MariaDB's data directory for the currently selected database.
SQL into outfile - where is the file stored? (MySQL, Windows)
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' ;
MySQL(5.6) select * into outfile.. not creating files
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.
Can't Find File Created By OUTFILE
Apparently the issue was with path. It is not taking absolute path 'C:\' instead when I used relative path it worked. Earlier it was saving in /bin/mysql/data/ folder when saved without any path but using ../../../www/ enabled me to bring it to my www folder.
where is the outfile created by mysql 5.6
Use the following syntax :
- you tell him where to put it !
SELECT * INTO OUTFILE 'c:/table.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM table
It normally stored in the DATA_DIR
parameter location.
why does SELECT INTO OUTFILE give file exists error even though file does not exist?
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
MySQL select into outfile does not write the file to the directory that I choose
The path doesn't seem write - if you are on Linux server then I would expect the path with contains slashes and not backslashes, if you are on Windows machine I would expect the driver letter to appears somewhere (and you can't write on a network folder - just local to the DB server machine).
Also, just to eliminate permissions issue or trying to override existing file, try to write to '/tmp/non_existing_file.csv'.
Related Topics
Session_Start() Creates New Session Every Refresh
Only Variables Should Be Passed by Reference In... on Line 13 Fail
Check If a Remote Page Exists Using PHP
Passing JavaScript Variables to PHP
Clean Way to Throw PHP Exception Through Jquery/Ajax and JSON
Friend of a Friend in PHP/Mysql
How to Check If an Entered Value Is Currency
Phpexcel Reader -- Help Required
Why Are Escape Characters Being Added to the Value of the Hidden Input
PHP - Count Frequency of Array Values
Ajax/PHP - How to Get Posted Data to Load into a Modal on the Same Page
Using Xampp, How to Swap Out PHP 5.3 for PHP 5.2
Curl Error 60: Ssl Certificate Prblm: Unable to Get Local Issuer Certificate