Why does PHP PDO get "SQLSTATE[42000] [1044] Access denied for user" when mysql command line works?

Question

My head is bloody from how hard I've been banging it against this wall for the past several hours. :(

As the title suggests, I've created a MySQL user that can access the database fine from the mysql command prompt on the database server. However, when I try to instantiate a new PDO object to access the database with that same user, I get:

SQLSTATE[42000] [1044] Access denied for user 'bob'@'localhost' to database 'my_database'

Here's how I created the user:

GRANT SELECT, DELETE, EXECUTE, INSERT, UPDATE ON my_database.* TO 'bob'@'localhost' IDENTIFIED BY 'some_password';

What could be the problem here?! Please someone throw me a bone! (FYI, the problem happens when I try to create a new PDO object...I catch a PDOException and that's the message).

I did FLUSH PRIVILEGES after the grant, and here's the output of SHOW GRANTS:

mysql> SHOW GRANTS FOR 'bob'@'localhost';
+------------------------------------------------------------------------------------------------------------+
| Grants for bob@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'bob'@'localhost' IDENTIFIED BY PASSWORD '.........................................' |
| GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON `my_database`.* TO 'bob'@'localhost'                      |
+------------------------------------------------------------------------------------------------------------+

And here's what mysql.db looks like for this user:

mysql> SELECT * FROM db WHERE User = 'bob'\G;
*************************** 1. row ***************************
                 Host: localhost
                   Db: my_database
                 User: bob
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: N
            Drop_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
         Execute_priv: Y
           Event_priv: N
         Trigger_priv: N

In case it matters, this is a four-node MySQL cluster running on Ubuntu 12.04 LTS.

EDIT: I've discovered that the problem only occurs when I try to access the server using Zend AMF. Any ideas why PDO wouldn't work with Zend AMF? Have I perhaps missed something in my Zend AMF setup?

Please edit your question to add further info. Code in comments is unreadable. — Álvaro González, Sep 15 '12 at 08:10
Presumably, `GRANT` directly updates the privilege cache of the server, so `FLUSH PRIVILEGES` should not be required. You only need it if you modify the `mysql.*` tables directly. — lanzz, Sep 15 '12 at 12:24
Well since you're running cluster you need to create that user on every sql node and grant him privileges on every node... have you done that? "It is important to keep in mind that the MySQL grant tables use the MyISAM storage engine. Because of this, those tables are not duplicated or shared among MySQL servers acting as SQL nodes in a MySQL Cluster." http://dev.mysql.com/doc/refman/5.0/en/mysql-cluster-security-mysql-privileges.html — Ivica, Sep 19 '12 at 12:16

score 5 · Answer 1 · answered Oct 12 '12 at 08:12

5

Try 'bob'@'127.0.0.1' instead. If php is accessing it via 127.0.0.1, it'll never be referred to as 'localhost' since the local DNS resolution didn't happen, and MySQL will deny access to it.

answered Oct 12 '12 at 08:12

Synthead

2,162
5
22
25

score 3 · Answer 2 · edited Apr 28 '15 at 16:32

For future Googlers, I had the same problem just now and I was pretty sure that password was correct. Yes password was correct indeed but the problem is how I generate password and how I keep password in config file.

If you use a random password generator like me make sure you don't have $ dollar sign in your password.

If you have $ in you password then make sure you keep you password in config file using single-quotes like this

$pass = 'randomchars$morerandom';

but not with double-quotes like this

$pass = "randomchars$morerandom";

Why does PHP PDO get "SQLSTATE[42000] [1044] Access denied for user" when mysql command line works?

2 Answers2