Hi
I am trying to capture the output of a SQL query in MySQL, to a text file using the following query.
select count(predicate),subject from TableA group by subject into outfile '~/XYZ/output.txt';
I get the following error.
ERROR 1045 (28000): Access denied for user 'username'@'%' (using password: YES)
Any idea, where am I going wrong? Is it some permission related issue?
cheers
outfile is it's own permission in mysql.
if you have ALL it's included
but if you just have a safe collection such as SELECT, INSERT, UPDATE, DELETE, DROP, CREATE, but not OUTFILE, into outfile will not work in queries.
The reason for this is that accessing files from within MySQL, even for write purposes, has certain security risks, because if you access a file from mysql you can access any file the mysql user has access to, thereby bypassing user based file permissions.
To get around this, you can run your query directly into the output of whatever shell/language your using to run the sql with.
here is a *nix example
>$ echo "select count(predicate),subject from TableA group by subject" | mysql -u yourusername -p yourdatabasename > ~/XYZ/outputfile.txt
but do it all on one line without the "\" or use the "\" to escape the line break
what's happening here is your running a query into the mysql client and it's spiting out the result, then your directing the output to a file. so the file is never called from within mysql it's called after mysql runs.
So use mysql to get the information and THEN dump the data to the file from your own user shell and you'll be fine.
Or find a way to get yourself the outfile mysql permission, either way.