Why does PHP PDO get "SQLSTATE[42000] [1044] Access denied for user" when mysql command line works?
Solution 1
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.
Solution 2
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";
Rob Johansen
BY DAY: Full stack developer for a software consultancy. BY NIGHT: Creator of Wordit (https://wordit.app), a word game that combines the gameplay of traditional word games with a deck of cards that empower you to make every turn count. FOR FUN: Teaching my son how to play the original Super Mario Bros on NES.
Updated on July 09, 2022Comments
-
Rob Johansen almost 2 years
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?