6

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!)

Jan Steinman
  • 325
  • 3
  • 11
  • 3
    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 function include: – Abhishek Sharma Dec 22 '15 at 06:24
  • Details http://stackoverflow.com/questions/34088373/call-to-undefined-function-mysql-connect-after-upgrade-to-php-7/34088446#34088446 – Abhishek Sharma Dec 22 '15 at 06:25
  • have a look at http://php.net/manual/en/migration55.deprecated.php – Chetan Ameta Dec 22 '15 at 06:25
  • Yes, I know it's deprecated FOR NEW VERSIONS OF PHP! No, I'm not going to upgrade until it works. I have legacy code to maintain, and it is unhelpful to suggest that I get involved in a massive porting effort at this point. CERTAINLY I would not use deprecated code on a new project, and just as certainly, I will break many things if I take legacy code that was working very recently and make massive changes to it. – Jan Steinman Dec 22 '15 at 23:36
  • 1
    What errors does the script show, if there are? If not, have your set `error_reporting` to show all errors? – Revenant Dec 23 '15 at 00:41
  • Good suggestion! I set "error_reporting(E_ALL)", but nothing interesting came up in the log. I do have MediaWiki configured to dump stack on errors, and it simply shows that the connection did not happen. I'll hack the call to mysql_connect() to see what the specific return code was, rather than just throwing an error on failure. – Jan Steinman Dec 23 '15 at 07:23
  • 1
    My wild guess is: It's related to the password scheme used by the MySQL server. see https://dev.mysql.com/doc/refman/5.5/en/old-client.html – VolkerK Dec 28 '15 at 07:07
  • Thanks for the password scheme suggestion! I did have one database that refused credentials for mysql_connect until I re-set the password, but that didn't fix *all* databases. – Jan Steinman Jan 03 '16 at 23:37

2 Answers2

2

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!

Jan Steinman
  • 325
  • 3
  • 11
0

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 function include:

use MySQLi or PDO

mysqli_connect()

PDO::__construct()
Abhishek Sharma
  • 6,689
  • 1
  • 14
  • 20
  • 1
    In other words; "who cares why, you have to switch, so do it!". – Rick James Dec 22 '15 at 20:32
  • 2
    This is rude and unhelpful. Yes, I need to switch. No, I'm not going to do it until I can get things working the way they were. Any professional programmer knows that when you're having a problem, you change one thing at a time until you fix it. Anyone who has been paid to maintain legacy code knows you don't just go upgrading all over the place whenever you have a problem. – Jan Steinman Dec 22 '15 at 23:31