SELECT ... INTO OUTFILE ...

[stan]

Hi, I'm cleaning up one of my MySQL tables with about 8000 entries. I worked out a SELECT statement that lists duplicates in a specific column, but because the output is about 300 rows I'd like to save it into a file using the INTO OUTFILE 'name' construct in MySQL.

However, it produces the following error when typed in the mysql command line tool.

mysql> select kvknr,count(*) as n
[nbsp][nbsp][nbsp][nbsp][nbsp][nbsp] into outfile 'dup.tmp'
[nbsp][nbsp][nbsp][nbsp][nbsp][nbsp] from item
[nbsp][nbsp][nbsp][nbsp][nbsp][nbsp] where status = 1
[nbsp][nbsp][nbsp][nbsp][nbsp][nbsp] group by kvknr having n > 1;
ERROR 1045: Access denied for user: 'xdmo@localhost' (Using password: YES)


It looks like a priviledge error. Any suggestions on this?

Stan

[dean]

Tech will know better then I , but I had the same exact problem.

I fixed it by making a directory named /files/ and chmod'ing it to 777.


------------------
Beta News ? http://www.betazine.com

[stan]

Hi Dean.
I tried it as follows

% mkdir files
% chmod 777 files
% cd files
% mysql xdmo

and then issued the select command as described earlier.
The error message is still

ERROR 1045: Access denied for user: 'xdmo@localhost' (Using password: YES)

What did you do that did work?

- Stan

[Terra]

I had to tinker with this for a moment...

change to using FULL path:

into outfile '/big/dom/xdmo/blah/blah/blah/dup.tmp'

--
Terra
--Thinking absolutely can lead to relative results--
FutureQuest

[stan]

Hi Terra,

I tried that before but that resulted in the same error message. I'll try again in a minute, when the MySQL server is ok again. It now complains ERROR 1040: Too many connections

Ok, mysql client can connect again:


[dmo@FQ-Six:~ ]$ pwd
/big/dom/xdmo/dmo

[dmo@FQ-Six:~ ]$ mysql xdmo
mysql> select * into outfile '/big/dom/xdmo/dmo/xxx' from item where id=7500;
ERROR 1045: Access denied for user: 'xdmo@localhost' (Using password: YES)
mysql>[nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp]



- Stan
[This message has been edited by stan (edited 05-23-99)]

[pdstein]

I'm getting ERROR 1040: Too many connections now as well.[nbsp][nbsp]I guess that means it's time to call it quits for the night and go get a beer.

- Paul

[This message has been edited by pdstein (edited 05-23-99)]

[Terra]

I have fixed the connection problem...

You may or may not see a drastic improvement in speed...[nbsp][nbsp]TAZ has some extra memory that I've applied to mysqld in tuning it's operating parameters...

As far as the outfile, I will have to check on that...[nbsp][nbsp]My testing worked for me when I used the absolute paths...

I know that 'load from outfile' requires the File_Priv, but the 'select into outfile' should not require it...

Perhaps when I update TAZ to 3.22.22 this may change...[nbsp][nbsp]I will schedule TAZ for upgrades later this week...

--
Terra
--Free memory?!?!? - where?? -- how much?--
FutureQuest
[This message has been edited by ccTech (edited 05-23-99)]

[Terra]

Aha!

The problem is simple, just took a diversion to see the actual problem...[nbsp][nbsp]Actually, drinking last nights coffee - this morning jarred it loose... ;)

MySQL runs as user: mysql on our server (instead of root), therefore I think you are trying to create the outfile within your HOME directory which the user mysql has NO access to...

Do this and see if it works:
$mkdir /big/dom/xdmo/tmp
$chmod 777 /big/dom/xdmo/tmp

Then use that directory for your outfile location...
into outfile /big/dom/xdmo/tmp/dup.tmp

I believe that will work and get past the permission denied problems...

Hope this helps-

--
Terra
--Who needs Java anyways?--
FutureQuest

[stan]

Hi Terra,

I tried that in my second posting on this subject.
Checked the status of all parent directories now too, but no problem there.

Results (once with a world writable file, once with a open directory):


$ pwd
/big/dom/xdmo[nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp]
$ touch dup
$ chmod go+w dup
$ mysql xdmo
mysql> select * into outfile '/big/dom/xdmo/dup'
[nbsp][nbsp][nbsp][nbsp]-> from item where id=7500;
ERROR 1045: Access denied for user: 'xdmo@localhost' (Using password: YES)
mysql>[nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp]



$ mkdir tmp
$ chmod 777 tmp
$ mysql xdmo
mysql> select * into outfile '/big/dom/xdmo/tmp/dup'
[nbsp][nbsp][nbsp][nbsp]-> from item where id=7500;
ERROR 1045: Access denied for user: 'xdmo@localhost' (Using password: YES)
mysql>


- Stan

[Terra]

Ah sheesh, I found the problem...[nbsp][nbsp]After reading through the source code - I found that 'select ... INTO OUTFILE'requires the File_Priv as well, and not just for the 'LOAD DATA ... INFILE'.

I also found this in the documentation, which I missed when searching for 'File_Priv'...

>>>>
The file privilege gives you permission to read and write files on the server using the LOAD DATA INFILE and SELECT ... INTO OUTFILE statements. Any user to whom this privilege is granted can read or write any file that the MySQL server can read or write.
<<<<

Due to the nature of this command statement and it's ability to access/write any file that mysql owns, I cannot allow it's usage...[nbsp][nbsp]Nothing would stop customer A from reading customer B database files or an hostile attack overwriting one of mysql's control files...

It looks like the other alternative would be using mysqldump to extract your DB to a file...

I will have Deb look into placing this in our FAQ...

--
Terra
--win some, lose some--
FutureQuest

[stan]

Aha!

So it was a privilege thing after all.

Using mysqldump would not help much, as I was trying to use some of MySQLs intelligence to help me weed out the duplicates in my 8000 entry database.

I'll try and write a Perl script later this week that can be used to dump the output of a SELECT into a local file.

- Stan

[Vic]

Terra,

Even with you latest suggestion, it does not work for me.[nbsp][nbsp]I am getting the same error message.

select email, count(*) as n
[nbsp][nbsp][nbsp][nbsp][nbsp][nbsp] into outfile '/big/dom/xvics/tmp/emaildups.txt'
[nbsp][nbsp][nbsp][nbsp][nbsp][nbsp] from list
[nbsp][nbsp][nbsp][nbsp][nbsp][nbsp] group by email having n > 1;

[nbsp][nbsp]ERROR 1045: Access denied for user: 'xvics@localhost' (Using password: YES)

I also tried to use 'FROM INFILE' to load data from a text file and got the same error message.

Vic

[This message has been edited by Vic (edited 05-24-99)]

[stan]

FYI: (and maybe for the FAQ)

After I explained the priviledge problem to someone on the MySQL mailing list, someone else immediately suggested this solution:

$ mysql --quick -e 'select kvknr,count(*) as n from item where status = 1 group by kvknr having n > 1' xdmo > dup.txt

I should have thought of that one myself....

- Stan