Mysql – Connection to MySQL fails via PHP while command-line works fine

MySQLmysql-5.7

I have a script to install MySQL 5.7 and run tests on Travis CI (running Ubuntu 12).

The script contains these lines, to install MySQL in non-interactive mode, with no password:

add-apt-repository 'deb http://repo.mysql.com/apt/ubuntu/ precise mysql-5.7-dmr'
apt-get update

echo mysql-community-server mysql-community-server/root-pass password "" | debconf-set-selections
echo mysql-community-server mysql-community-server/re-root-pass password "" | debconf-set-selections

DEBIAN_FRONTEND=noninteractive apt-get -o Dpkg::Options::=--force-confdef -o Dpkg::Options::=--force-confold -q -y install mysql-server libmysqlclient-dev

The install runs smoothly, and using the mysql command-line utility, I can connect to the server using no password:

mysql --user=root -e "SELECT VERSION();"

+-----------+
| VERSION() |
+-----------+
| 5.7.8-rc  |
+-----------+

Now if I try to connect using PHP PDO:

new PDO('mysql:host=localhost', 'root', '');

I get the following error:

SQLSTATE[HY000] [1045] Access denied for user 'root'@'localhost' (using password: NO)

I tried to allow root to connect from any host:

GRANT ALL ON *.* to root@'%' IDENTIFIED BY '';
FLUSH PRIVILEGES;

Without success.

What can be the problem?

Best Answer

Found the answer in the MySQL manual, Pluggable Authentication:

As of MySQL 5.5.7, the server authenticates clients using a plugin. Selection of the proper account row from the mysql.user table is based on the user name and client host, as before, but the server authenticates the client by determining from the account row which authentication plugin applies for the client.

The MySQL package creates a root user using the auth_socket authentication plugin, so only connections via a socket are permitted, and the username of the Linux account is checked as well.

The mysql command uses a socket by default, and runs as root in my script, so it was allowed to connect.

According to the PHP manual, a PDO MySQL connection to localhost also uses a socket; but PHP is run as an unprivileged user, so the connection is refused by the auth_socket plugin.

I fixed my script by adding this line:

mysql --user=root -e \
"UPDATE mysql.user SET plugin='mysql_native_password'; FLUSH PRIVILEGES";

And it now works fine.

Related Question