PHP interafce mysql() not working, but mysqli() is working; why?

Question

Synopsis: can no longer connect to MariaDB from web apps after dump/restore. This seems to be some difference between the way PHP mysql() and mysqli() interfaces are dealt with in MariaDB.

MariaDB 10.1.8 on MacOS X 10.6.8 (Snow Leopard), with PHP 5.3.8 and Apache 2.2.24.

Due to a failing disk drive, I had a case of "database rot,” with some InnoDB tables becoming inaccessible, finally with the server crashing. I followed instructions in the web page pointed to by the server error log entry, and was able to get it to run, read-only, using “mysqld --innodb_force_recovery=2”. (Level 1 still crashed, and I dared not try level 3.)

In “force_recovery” mode, I did a logical (SQL code) dump of all databases (11 GB), renamed the faulty data directory, and ran “scripts/mysql_install_db.sh” to initialize an empty data directory. I then loaded the logical backup, with only minor problems.

Now it gets puzzling. I can access the database just fine, using a variety of tools, including the mysql CLI, phpMyAdmin, Sequel Pro, Valentia, etc.

But I cannot get into the database via any of the websites I host, including several versions of MediaWiki and several instances of a home-grown image base. It fails in mysql_connect() with login credentials that work via the mysql CLI. But phpMyAdmin works, using the same login credentials!

So I crawled through phpMyAdmirn code, and discovered it was using mysqli(), whereas the broken web apps seem to be using mysql(). phpMyAdmin has a configuration variable to control this; I changed it from “mysqli” to “mysql”, and it broke. Changed it back to “mysqli”, and it works again.

I have not changed any PHP code. I have not changed any apache settings. I have not changed /etc/my.cnf. I have not changed /etc/php.ini. I have not changed any login credentials. The ONLY thing that changed was dump, re-init, and restore of all databases.

I’m thinking perhaps some magic MySQL system variable setting didn’t make it through the dump/restore cycle.

I did phpinfo(), which indicates mysqli() is using the proper socket: /tmp/mysql.sock, but mysql() is using /var/mysql/mysql.sock, which is NOT enabled in /etc/php.ini. There was a symlink in /var/mysql pointing to /tmp/mysql.sock, which makes me think I've been down this path before... I tried making it a hard link; still no joy.

I ran "php -info", and indeed, the compiled-in socket is /var/mysql/mysql.sock. So I put "socket=/var/mysql/mysql.sock" in /etc/my.cnf, deleted the symlink in /var/mysql, and restarted both mysql and apache. The socket is there in /var/mysql. Now, the web apps that use mysqli() no longer work, but neither do the ones that use mysql()!

So I'm pretty confused. Especially since it was all working fine before I did a dump/init/restore of the mysql data directory.

Thanks in advance for any advice offered!

(And yes, I know mysql() is deprecated, but I did put "PHP 5.3.8" right up front. Thanks to everyone who didn't answer the question by pointing out that one should not use deprecated code for NEW development. Now if you're so clever, let's try to answer the question, instead! I have legacy code to maintain!)

Answer 1

In this case, it was the socket location.

Although I mentioned putting the proper socket location in /etc/php.ini, something else from phpinfo() caught my eye: "Scan this dir for additional .ini files: /usr/local/php5/php.d". This is apparently done after reading the master /etc/php.ini file. Ugh.

So I went in there, and sure enough, there was a file in there that was setting the socket for mysql (but not mysqli) to the wrong location. Double-ugh.

I edited the offending file to point to /tmp/mysql.sock, and did "apachectl graceful", and viola! (Or for those who don't like stringed instruments, voila!) It works again!

I have no idea why creating neither a hard link nor a symbolic link between those two locations (as explained in my question) fixed the problem, nor do I have any idea why a simple dump/restore of my databases would cause things to break, after years of having it the way it was. All I know is that hunting down and changing the bad socket location fixed things.

So if you're having problems with one interface, but not the other, be sure to read phpinfo() carefully, and to check all the locations that modify the php running state, not just /etc/php.ini.

Many thanks for the thoughtful and helpful comments!