当mysql命令行工作时，为什么PHP PDO会收到“SQLSTATE [42000] [1044]用户拒绝访问”？

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:

正如标题所示，我创建了一个MySQL用户，可以从数据库服务器上的mysql命令提示符访问数据库。但是，当我尝试实例化一个新的PDO对象以使用同一个用户访问数据库时，我得到：

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

这可能是什么问题？！请有人给我一个骨头！（仅供参考，当我尝试创建一个新的PDO对象时会出现问题...我捕获了一个PDOException，这就是消息）。

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

授权后我做了FLUSH PRIVILEGES，这是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.db对于这个用户来说的样子：

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.

如果重要，这是一个在Ubuntu 12.04 LTS上运行的四节点MySQL集群。

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?

编辑：我发现问题只发生在我尝试使用Zend AMF访问服务器时。任何想法为什么PDO不能与Zend AMF合作？我可能在Zend AMF设置中遗漏了一些东西吗？

2 个解决方案

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

请尝试'bob'@'127.0.0.1'。如果php通过127.0.0.1访问它，它永远不会被称为'localhost'，因为本地DNS解析没有发生，MySQL将拒绝访问它。

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

对于未来的Google员工，我刚才遇到了同样的问题，我非常确定密码是否正确。是的密码确实是正确的，但问题是我如何生成密码以及如何在配置文件中保留密码。

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

如果您有$ in密码，请确保使用单引号将密码保存在配置文件中

$pass = 'randomchars$morerandom';

but not with double-quotes like this

但不是像这样的双引号

$pass = "randomchars$morerandom";

#1