Year: 2011

MySQL export CSV into OUTFILE triggers “access denied” error

I came across a weird problem today.  Gladly, the web is full of solutions, but I’m going to post this anyway, just to have it nearby for the next time.  I needed to export the results of some query into a CSV file directly from MySQL.  I prepared my query, made sure that I can see the correct results and than changed it to export into the file. The query looked something like this:

SELECT id, field1, field2, field3
INTO OUTFILE '/tmp/data.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM data_table
WHERE field1 = 0;

I was quite surprised to find myself staring at:

ERROR 1045 (28000): Access denied for user ‘db_user’@’localhost’ (using password: YES)

My database user definitely had full access to the database.  I definitely could see the results of the query before the redirect to the file.  And I definitely had enough permissions to create files in /tmp directory.  And on top of that, I’m sure I used MySQL export functionality a gadzillion times and it always worked without any problems.   What’s wrong this time?

A quick search around got me to this Stack Overflow question.  Apparently, database user has to be given a FILE privilege, which is global (not per-database).   Here is what I did to solve the problem (you’ll need to use MySQL root user of course):

USE mysql;
UPDATE user SET File_priv = 'Y' WHERE User = 'db_user';
FLUSH PRIVILEGES;

I think that it worked for me before was because I exported as root, who does have this permission set to ‘Y’.

Disable and enable CakePHP plugins on the fly

I am currently working on a rather large project which is based on CakePHP framework.  In order to simplify the task, I’ve broken down the whole application into a number of CakePHP plugins.  Now, however, I want to enable/disable plugins on the fly.  After a brief search around I couldn’t find how to do that.  Asking a question at #cakephp IRC channel did it.  RabidFire instantly replied with the link that gave me an idea.   30 seconds later I had a working solution.

CakePHP plugins extend AppController.  So all that one needs to do is add the following lines to app/app_controller.php (Using CakePHP 2.0, but it’s trivial to adopt for earlier versions):

public function beforeFilter() {
    $allowedPlugins = array('crm', 'articles');
    if (!empty($this->request->params['plugin']) && !in_array($this->request->params['plugin'], $allowedPlugins)) {
        throw new ForbiddenException();
    }
}

Upgrading to PHP 5.2.x on CentOS

Today while setting up yet another project on my hosting server.  The server runs CentOS 5.6, which means PHP 5.1.6 is used.  However the new project required PHP 5.2.0+.  It turned out upgrading PHP is trivial.  There is even a Howto Guide in CentOS wiki.  The steps are:

  1. Add CentOS Testing repository to yum.
  2. yum update PHP packages.

That’s all folks!