5

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.

Jonathan Hall
  • 75,165
  • 16
  • 143
  • 189
getting-there
  • 1,409
  • 4
  • 18
  • 23
  • 1
    Not sure if [this](http://dev.mysql.com/doc/refman/5.0/en/cannot-create.html) helps. For what's it's worth, `find / -name "gics_v2.csv"` as root should locate the file on your computer – benjammin Jul 13 '12 at 03:02
  • 1
    @BenM thanks Ben, I had to use 'sudo' to find it but it's under /usr/local/mysql-5.5.17-osx10.6-x86_64/data/. You help it much appreciated. – getting-there Jul 13 '12 at 03:28

4 Answers4

4

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 ',';

Community
  • 1
  • 1
Mike S.
  • 4,806
  • 1
  • 33
  • 35
  • Thanks a lot Mike. I'm going to have to play around with the permissions a bit I think. It's been a while since I'd done this stuff. I appreciate the help. – getting-there Jul 13 '12 at 03:30
  • No problem. I've been using this site so much lately I felt the need to give back. This is my first answer and vote so thanks to you as well. ;-) – Mike S. Jul 13 '12 at 03:47
  • I don't agree with this -- it doesn't really answer the question, and advising someone to make directories 777 is kind of a hack. Better to use more appropriate permissions. – Gavin Towey Jul 13 '12 at 06:12
  • Also, check `secure_file_priv` http://stackoverflow.com/a/10757312/5973334 – Kuzeko Dec 14 '16 at 10:23
2

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.

Gavin Towey
  • 3,132
  • 15
  • 11
  • thanks for your help. I found where it was writing it to and created a new specific directory and gave the server user permission to write into. I appreciate your help. – getting-there Aug 09 '12 at 12:27
1

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.

Ge Liu
  • 382
  • 7
  • 14
0

I am running Mac OS X Server 10.7.4 and for me, the OUTFILE directory is /var/mysql/databasename/

Nicholas Shanks
  • 10,623
  • 4
  • 56
  • 80