Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Where does MySQL on OSX write outfiles by default?

I'm trying to write some data from a MySQL select statement to a file on a Mac running Snow Leopard.

select date_base, fractile_v2, gics, count(gvkey_iid)
from master
where fractile_v2 <= 15 and
      fractile_v2  != 0
group by date_base, gics, fractile_v2
order by date_base, fractile_v2
limit 100000
INTO OUTFILE '/User/my-name/Desktop/gics_v2.csv'
FIELDS TERMINATED BY ',';

Unfortunately this generates the following error:

Error Code: 1. Can't create/write to file '/Users/andrew/Desktop/gics_v2.csv' (Errcode: 13)

which I'm assuming is a permissions issue.

When I replace the full file path '/User/my-name/Desktop/gics_v2.csv' with simply gics_v2.csv the statements seems to run. However I have no idea where the file is saved and I can't find it.

Does anyone know? And can anyone also suggest how I can solve the initial write error? I'm running MySQL as the root user.

like image 911
getting-there Avatar asked Jul 13 '12 02:07

getting-there


People also ask

Where does MySQL store databases on Mac?

mysql is found in /usr/local/mysql. Databases are stored in the data folder (/usr/local/mysql/data/databasename). This may be different depending on how you installed it.

Where is the MySQL config file on Mac?

/etc/mysql/my. cnf.

Where does MySQL store Outfile?

C:\ProgramData\MySQL\MySQL Server 5.6\data\name.csv Show activity on this post.

How do I Outfile in MySQL?

You can also use SELECT ... INTO OUTFILE with a VALUES statement to write values directly into a file. An example is shown here: SELECT * FROM (VALUES ROW(1,2,3),ROW(4,5,6),ROW(7,8,9)) AS t INTO OUTFILE '/tmp/select-values.


3 Answers

This is a permissions issue because you're trying to get the mysql user to write to your private home directory. Try writing to a folder you create in /usr/local/ instead and just to be safe, you can make permissions global read/write since it's your Mac.

Open Terminal on your Mac:

cd /usr/local

mkdir DbOutput

sudo chmod -R 777 DbOutput

Then back to your code but change path:

select date_base, fractile_v2, gics, count(gvkey_iid)

from master

where fractile_v2 <= 15 and

fractile_v2 != 0

group by date_base, gics, fractile_v2

order by date_base, fractile_v2

limit 100000

INTO OUTFILE '/usr/local/DbOutput/gics_v2.csv'

FIELDS TERMINATED BY ',';

like image 156
Mike S. Avatar answered Oct 11 '22 23:10

Mike S.


By default SELECT ... INTO OUTFILE puts the files in the directory where the data files live. So something like /path/to/datadir/databasename/gics_v2.csv

It writes files as the server, not the client. So it's not using your user's permissions, but rather the user that is running the mysqld process (which is usually _mysql on osx.)

You simply have to give it a path where that user is allowed to write.

like image 29
Gavin Towey Avatar answered Oct 11 '22 23:10

Gavin Towey


I know the question is out of date, but just FYI, on my Mac OS X 10.10, the OUTPUT directory is something like this: /usr/local/mysql-5.7.10-osx10.9-x86_64/data/databasename/***.csv.

like image 43
Ge Liu Avatar answered Oct 11 '22 22:10

Ge Liu