Unable to connect to MySQL through PHP script when using mysqli or PDO BUT mysql works.

Question

I am facing a weird situation. When I am trying to connect to MySql database using a "mysql" connection, it works.

mysql connection string -> mysql_connect($HOST, $USER, $PASSWORD, $DB); 

But the connection fails immediately fails when I use either "mysqli" or "PDO"

mysqli connection string -> mysqli_connect($HOST, $USER, $PASSWORD, $DB); 
PDO Connection string -> new PDO("mysql:host=$HOST;dbname=$DB", $USER, $PASSWORD);

The specific error that it throws is,

SQLSTATE[HY000] [2003] Can't connect to MySQL server on 'localhost' (10061

Can you guys help me out? Thanks.

Answer 1

I've run into this from time to time. The explanation is most often that MySQL Server is configured to use a socket file at one path, but php.ini's section on mysqli or pdo_mysql is looking for the socket file at another path.

This happens to me even though I install both PHP and MySQL from MacPorts. You'd think they'd have made the configurations for these two ports agree.

Either edit your php.ini to set the correct location of the socket file, or else specify the socket when you initiate a connection with mysqli or pdo_mysql.

pdo = new PDO("mysql:dbname=test;unix_socket=/opt/local/var/run/mysql5/mysqld.sock", 
  "username", "password")

$mysqli = new mysqli("localhost", "username", "password", "test",
  ini_get("mysqli.default_port"), "/opt/local/var/run/mysql5/mysqld.sock")

See also the article I wrote Error2003-CantConnectToMySQLServer.