Monday, November 09, 2009

Solving the (HY000/2002): Can't connect to local MySQL server through socket '/var/mysql/mysql.sock' on Mac OS 10.5

I've been using the Apache version that comes pre-installed with the Leopard, and its companion installation of PHP, that can be enabled as described here. I figured that if Apple took the trouble to pre-install these, I'd better use them instead of re-installing them from scratch.

I also installed MySQL using the dmg package from mysql.com.

At this point PHP and MySQL seemed to be working fine on my system, each tried separately.

Things went ugly when I tried to call MySQL from within a PHP script. Calling $dbc = mysqli_connect(MSQL_HOST, MYSQL_USER, MYSQL_PASSWD, MYSQL_DB) yielded the HY000/2002 error, complaining about the inability to connect through /var/mysql/mysql.sock.

The docs for mysqli_connect indicate that if the host to connect to is the local host, the system will attempt to use a pipe instead of TCP/IP, perhaps for improved efficiency. So it looks like the /var/mysql/mysql.sock pipe is not present. A netstat -a | grep mysql indicates that that's indeed the case, instead there seems to be a /tmp/mysql.sock pipe on my system.

Nice, all I have to do is make use of that. A quick change to $dbc = mysqli_connect(MSQL_HOST, MYSQL_USER, MYSQL_PASSWD, MYSQL_DB, ini_get("mysqli.default_port"), '/tmp/mysql.sock') makes it work. Good times.

Not this is clearly a thing peculiar to my setup, I'd rather fix my setup instead of changing my code to work around this, especially since I need the code to be generic enough to run on a production server, with a different setup. So I take a look at the output provided by phpinfo. Here I see that MYSQL_SOCKET is /var/mysql/mysql.sock and mysqli.default_socket is not set. Then all I need to fix my system is to set mysqli.default_socket to '/tmp/mysql.sock'. To do that, I changed my /private/etc/php.ini to have the 'mysqli.default_socket =' line changed to 'mysqli.default_socket = /tmp/mysql.sock' and restart Apache (System Preferences -> Sharing -> Web Sharing disable and enable again).

Now my system is good to go, I can connect to MySQL from within PHP simply with $dbc = mysqli_connect(MSQL_HOST, MYSQL_USER, MYSQL_PASSWD, MYSQL_DB).

Note: The same issue may affect connecting through the regular mysql_connect, in which case you'll have to change your mysql.default_socket setting in a similar way.

No comments: