Access denied error on select into outfile using Zend

Question

Hi, 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?

Answer 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.

Answer 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

Answer 3

Hello,

I need to do the same thing, and cannot find out how to set the GLOBAL PERMISSION of FILE - for example - it is neither in my /etc/mysql/my.cnf file, nor in either the /home/www/PhpMyAdmin-2.11.5 .../config.inc.php OR the sample config.inc.php file.

Is there a specific line to add to the /config.inc.php file that sets this? If so, what is it please?

Thanks,

Eddie

Answer 4

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.