dmy

MacPorts, MySQL, PHP5 and that socket thing ..

written by admin 2661 days ago. Last update at 2013-04-02 16:29:26. Categories macosx, macports. Tags , , .

I’m using MacPorts to have a variety of Open-Source software on my computer. Amongst them MySQL and Apache’s web server with PHP. In addition I’m also running Wordpress, a popular blog software, and Mediawiki, the software behind Wikipedia. Both applications are written in PHP and can be configured to use MySQL as database.

I install those two web applications myself rather than using MacPorts to do so. The installation is basically nothing more than unzipping the distribution package and to modify the configuration files to make sure they connect to my MySQL database on my localhost. However, while installing Wordpress went smooth, I run into database connection failures when installing Mediawiki. It took me some time to figure out what is going on here.

If you have weird connection problems using PHP and MySQL then try one or both of the following solutions:

  • Solution: Use 127.0.0.1 instead of localhost as host of your MySQL database server. This has a rather surprising effect, see below.
  • Solution: Change your PHP’s configuration file, which is /opt/local/etc/php5/php.ini, to contain the following two lines:
    1. Tell PHP about the Unix domain socket to use
         mysql.default_socket = /opt/local/var/run/mysql5/mysqld.sock
      
    2. In addtion make sure that PHP’s save mode is off (notice that this feature has been deprecated and removed in PHP 6), otherwise your previous modification will be gracefully ignored:
          safe_mode = Off
      

    Notice that in addition you need to restart your Apache web server so that your changes are taken into account. You can do that with apachectl restart.

Background Information

Neither Mediawiki nor Wordpress (nor most other PHP based web application accessing MySQL) implement the gory details how to connect to a database. Usually they just delegate this to mysql_connect();, a API function provided by PHP.

PHP in turn uses the functionality of a (shared) library provided by MySQL, i.e. the MySQL driver. MySQL allows you to connect to the database via two routes:

  1. via Internet sockets (TCP/IP); and via
  2. UNIX domain sockets

So, how does one choose between this two routes? The answer is rather surprising. MySQL decided to give localhost a new meaning. If you specify localhost, then the UNIX domain socket route is chosen. If you say 127.0.0.1 instead, then you connect via an Internet socket. While this is probably the most natural thing for those MySQL folks, it’s a rather confusing fact for all other humans. It is rather carved deep in our brain for years now that 127.0.0.1 and localhost is the same thing.

After I have changed both Mediawiki and Wordpress to use localhost I had for the first time a situation where both web applications behaved in the very same way. They both did not work. Then, when I changed to 127.0.0.1, both worked fine. Btw, notice that localhost is the default hostname used by Mediawiki while 127.0.0.1 is the default host suggested by Wordpress.

Using 127.0.0.1 is usually fine, however I do want to use the UNIX socket and I was wondering what went wrong.

The problem is that MacPorts MySQL port patches the original MySQL sources to use socket /opt/local/var/run/mysql5/mysqld.sock instead /tmp/mysql.sock. This change is not particular well documented. One would assume that this kind of change goes into an appropriate my.conf configuration file. That’s actually not the case. Even worse, MacPorts MySQL port does not even ship with such a configuration file.

Changing the default socket location is one thing, not telling us and the PHP5 maintainer this is another sin. So, while MySQL happily uses that weird socket location, PHP5 still expects /tmp/mysql.sock. In other words, PHP and MySQL are not working out of the box when using MacPorts. At least not when relying on Unix domain sockets. It does work when using Internet domain sockets cause that default port, being 3306, has not changed. Btw, the MacPorts maintainer of PHP and MySQL is one and the same person. Sounds familiar? Yes, I also have been thinking about Dr. Jekyll and Mr. Hyde. No serious. I assume that there is a serious reason why this does not work out of the box as we all wish. I just don’t get it.