Access denied error on select into outfile using Zend

Question

I'm trying to make a dump of a MySQL table on the server and I'm trying to do this in Zend. I have a model/mapper/dbtable structure for all my connections to my tables and I'm adding the following code to the mappers:

public function dumpTable()
{
    $db = $this->getDbTable()->getAdapter();
    $name = $this->getDbTable()->info('name');
    $backupFile = APPLICATION_PATH . 
                  '/backup/' . date('U') .
                  '_' . $name . '.sql'; 
    $query = "SELECT * INTO OUTFILE '$backupFile' FROM $name";
    $db->query( $query );               
}

This should work peachy, I thought, but

Message: Mysqli prepare error: Access denied for user 'someUser'@'localhost' (using password: YES)

is what this results in.

I checked the user rights for someUser and he has all the rights to the database and table in question. I've been looking around here and on the net in general and usually turning on "all" the rights for the user seems to be the solution, but not in my case (unless I'm overlooking something right now with my tired eyes + I don't want to turn on "all" on my production server).

What am I doing wrong here? Or, does anybody know a more elegant way to get this done in Zend?

score 2 · Accepted Answer · answered Mar 16 '10 at 10:06

2

Although I gave "all privileges" to the user in question, I did this on a per database basis instead of globaly. The privilige needed to use outfile however is FILE, an can only be set as a GLOBAL permission

answered Mar 16 '10 at 10:06

Peter

1,211
4
17
32

score 2 · Answer 2 · edited Dec 09 '12 at 22:26

2

If you have an issue with permissions you need to set the user mysql runs under to have the necessary permissions needed to access that folder. For example you have /tmp/filedumps, with a owner of www-data and group of www-data, you need to add the mysql user account to the group on debian/ubuntu you can do usermod -a -G www-data mysql.

This is how I solved my issues on *nix boxes.

edited Dec 09 '12 at 22:26

Jens Björnhager

5,632
3
27
47

answered Oct 14 '10 at 20:05

Anthony

21
1

score 1 · Answer 3 · edited May 23 '17 at 12:21

1

>$ echo "select count(predicate),subject from TableA group by subject"  | mysql -u yourusername -p yourdatabasename > ~/XYZ/outputfile.txt

Snarfed from this post that has a workaround to the FILE permissions not being granted: Query output to a file gives access denied error

edited May 23 '17 at 12:21

Community

1
1

answered Jun 08 '12 at 18:40

Lance Cleveland

3,098
1
33
36

score 1 · Answer 4 · answered Mar 15 '10 at 17:34

1

Does a normal SELECT work? If it doesn't either, I would say this is a simple case of a wrong password. If a normal SELECT works, make sure you granted the OUTFILE right to the exact user

 'someUser'@'localhost'

and not for example

  'someUser'@'%'

if I remember correctly, those two accounts would be treated differently and have separate rights settings.

answered Mar 15 '10 at 17:34

Pekka

442,112
142
972
1,088

Hey Pekka, yes, SELECT, INSERT, UPDATE etc works just fine and, according to phpmyadmin, someUser has "ALL PRIVILEGES" – Peter Mar 15 '10 at 17:39
1

@Peter strange. Done a `FLUSH PRIVILEGES` and/or restarted the server? – Pekka Mar 15 '10 at 17:41
1

@Peter can you try creating a 2nd user with all privileges and try it with that one, just to rule out duplicate records and such? – Pekka Mar 15 '10 at 17:41
@Pekka, tried both your suggestions (flush and new users with all privileges), still get the same access denied error – Peter Mar 15 '10 at 17:52
@Peter strange, then I don't know any further. Do you have the proper write rights in the directory you want to write the outfile to? – Pekka Mar 15 '10 at 17:55
@Pekka, yup, that seems to be OK. But, I'm wondering now, I'm using PhpMyAdmin to manage my users, but I seem to be missing the FILE privilege in my list of all privileges and I guess that's the one I need for OUTFILE. I tried granting it, trough sql input in phpmyadmin, but I get an error – Peter Mar 15 '10 at 17:58
@Pekka, almost there! "FILE" is only assigned as a GLOBAL permission, i just discovered, so granting someUser "FILE" right solved that problem, but now he is indeed complaining he can't write the file, so I guess, still some privilege errors to attend too! – Peter Mar 15 '10 at 18:04
Now I get the following error: Message: Mysqli statement execute error : Can't create/write to file '/var/www/ZendApp/application/backup/1268676308_table.sql' (Errcode: 13), yet that is a 777 chmod'ed directory. I guess I'm to tired to think straight, I'll search on tomorrow. – Peter Mar 15 '10 at 18:13
@Peter this sounds still strange, but I think you're definitely on the right track. Good luck tomorrow! – Pekka Mar 15 '10 at 18:24

Access denied error on select into outfile using Zend

4 Answers4