PHP interafce mysql () does not work, but mysqli () works; What for?

Question

Synopsis: You can no longer connect to MariaDB from web applications after a reset / restore. This seems to be some difference between the way PHP handles the mysql () and mysqli () interfaces 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 faulty disk drive, I had a case of "database rot" when some InnoDB tables became unavailable, and finally a server crash. I followed the instructions on the webpage that the server error log entry pointed to and was able to start it, read-only, using "mysqld --innodb_force_recovery = 2". (Level 1 still crashed, and I did not dare to try level 3.)

In the "force_recovery" mode, I dumped all the databases (11 GB) in a logical (SQL) code, renamed the error directory and ran "scripts / mysql_install_db.sh" to initialize the empty data directory. Then I downloaded a logical backup with minor issues.

Now this is puzzling. I can access the database just fine using various tools including mysql CLI, phpMyAdmin, Sequel Pro, Valentia, etc.

But I can’t get into the database through any of the sites that I host, including several versions of MediaWiki and several instances of the home image database. It does not work in mysql_connect () with user credentials that work through the mysql CLI. But phpMyAdmin works using the same credentials!

So, I scanned the phpMyAdmirn code and found that it was using mysqli (), while broken web applications seemed to be using mysql (). phpMyAdmin has a configuration variable to manage this; I changed it from mysqli to mysql and it broke. Changed this to "mysqli" and it works again.

I have not changed the PHP code. I have not changed apache settings. I have not changed /etc/my.cnf. I have not changed /etc/php.ini. I have not changed any credentials. The only thing that has changed is dumping, restarting, and restoring all databases.

I think maybe some kind of magic MySQL system variable did not go through the dump / restore cycle.

I did phpinfo (), which indicates that mysqli () uses the appropriate socket: /tmp/mysql.sock, but mysql () uses /var/mysql/mysql.sock, which is NOT included in /etc/php.ini. A symbolic link was pointed to / var / mysql pointing to /tmp/mysql.sock, which makes me think that I was on this path before ... I tried to make it a hard link; still no joy.

I ran "php -info", and indeed, the compiled-socket was /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 in / var / mysql. Now web applications that use mysqli () no longer work, but also those that use mysql ()!

So, I'm pretty confused. Moreover, everything was fine before I did a dump / init / restore of the mysql data directory.

Thanks in advance for the advice!

(And yes, I know that mysql () is deprecated, but I put "PHP 5.3.8" right in front of me. Thanks to everyone who did not answer the question, indicating that you should not use legacy code for try to answer the question, instead I have outdated code for support!)

Answer 1

In this case, it was a socket location.

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

So, I went there and, of course, there was a file that installed the socket for mysql (but not mysqli) in the wrong location. Twice pah.

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

I have no idea why neither a hard link was created, nor a symbolic link between the two places (as explained in my question), and I don't know why a simple dump / restore of my databases will cause things to break, after several years of existence as it was. All I know is that hunting and changing the wrong location of the nest fixed things.

So, if you have problems with one interface, but not with another, be sure to read phpinfo () and check all the places that change the state of php, not just /etc/php.ini.

Thanks so much for the thoughtful and helpful comments!

Answer 2

Warning This extension was deprecated in PHP 5.5.0, and it was removed in PHP 7.0.0. Instead, the MySQLi or PDO_MySQL extension should be used. See Also MySQL: Choosing an API Guide. Alternatives to this feature include:

use MySQLi or PDO

 mysqli_connect() PDO::__construct()