Search code examples
mysqlsqlkshinto-outfile

Mysql,issues with into outfile, an easy solution or a better way to do it?

at the moment I'm writing a simple script in ksh who should take some strings from a DB and list them on the shell. This is how it should work:

  • Query the DB for all the datas
  • Export them to a text file
  • Using awk to show the different columns

The problem is that two fields of the table I'm querying contain sentences. So awk, obviously, "thinks" that the white spaces between the different words are separators between columns, so I came up with the following solution:

  • SELECT * INTO OUTFILE "output" FIELDS TERMINATED BY ',' FROM table
  • Using awk to list the results (obviously the separator now is ',')

But here I have another issue, though I've

  • Granted the FILE privilege to the mysql user I'm using
  • Gave to mysql 777 on the directory where it should write

Even so I'm receiving the following error:

'path': Can't create/write to file 'file' (Errcode: 13)

Frustrated by this I've started surfing the web to find an answer, and I found an "incomplete" one. In a certain site they suggest to:

  • chown root:root /tmp

  • chmod 1777 /tmp

  • /etc/init.d/mysqld start

I thought that, if this solution works fine on /tmp then there shouldn't be any problem on any other directory I choose. Obviously I was totally wrong :). If the path I'm giving to the outfile option is /tmp/'file' then there's no problem, any other path returns me the previous error. So did I find a 'solution' to the problem? Well, yes but to use this script a someone have to:

  • Have a mysql user with the FILE privilege
  • Have the rights to 'rm' a file in /tmp (the output file of mysql is temporary)

And I don't think this is sweet&nice. So what I'm asking you? The following:

  • How can I force mysql to write a file wherever I want?
  • Have you more viable and 'sweet' solution than mine to suggest me?

Ah, I'm using Ubuntu 10.4. Thank you for any help.

Solution

  • A better solution is to redirect output:

    mysql --default-character-set=utf8 -uuser -ppass -s -e "SELECT * FROM mytable;" > results.txt

    Mind the -s option:

      -s, --silent        Be more silent. Print results with a tab as separator,
                      each row on new line.