Intermittent MySQL problems after PHP 4.3.0 upgrade.. help!

[Ville]

I upgraded my FreeBSD 4.6 system's PHP to 4.3.0 today, and started seeing strange database errors on my vBulletin 2.2.9 (http://viestit.etusivu.net). The following error shows up rather frequently but intermittently when browsing the forum:

Warning: mysql_pconnect() [function.mysql-pconnect]: Link to server lost, unable to reconnect in /usr/web/etusivu/viestit/admin/db_mysql.php on line 38
There seems to have been a slight problem with the database.
Please try again by pressing the refresh button in your browser.
An E-Mail has been dispatched to our Technical Staff, who you can also contact if the problem persists.
We apologise for any inconvenience.

Clicking refresh once or twice helps, the page is shown.
I get via email:

Database error in vBulletin :
Link-ID == false, connect failed
mysql error:
mysql error number:
Date: Thursday 02nd of January 2003 09:56:22 PM
Script: /online.php?s=
Referer: http://viestit.etusivu.net/

mysqladmin -u root -p variables can be seen at
http://www.astronite.com/mysqlvariables.txt

and the extended output script output at
http://www.astronite.com/extended.txt

The server is otherwise in relatively light use, there are no other scripts that actively use the database (that is located on the same server). Also the vbulletin is in light use, normally with less than 10 concurrent users. I got db errors very rarely before the PHP upgrade.

Any idea as to what might be the cause..?

Thanks!

[Wayne Luke]

The PHP upgrade may be the cause. With every PHP release lately, they have changed the way fundamental things work and that has caused problems.

[ccd1]

Can anyone provide confirmation? I'm upgrading and moving VB's to it on Sunday.

[Ville]

Hmm... I turned off vBulletin persistent connections, and it seems it may have helped (at least I haven't gotten any more error messages).

[Wayne Luke]

According to the changelog for PHP 4.3.0 there is a new php.ini option that can affect your MySQL connections. I haven't downloaded the package or documentation yet, but it may be something you want to look into/

Added php.ini option "mysql.connect_timeout" to set the timeout limit for mysql_connect() and mysql_pconnect().

They also changed how the functions worked slightly with a new optional parameters that might affect your connections:

Added optional parameter "flags" to mysql_connect() and mysql_pconnect() which can be used to set usage of idle timeout and different protocols, SSL and compressed. (Georg)

[Ville]

Problem solved (I think)

Lack of mysql.connect_timeout in php.ini was probably the cause. Once I added it in I could turn pconnect back on in vBulletin's config, and everything works fine.

It's a good idea to review php.ini for other changes as well. A program I've found invaluable in situations such as this where I have to compare two config files is Beyond Compare.

[Ville]

Bzzzt... wrong!

The problem I described in the first message of this thread still plagues me. It's intermittent, and apparently not limited to vBulletin only - other PHP applications experience it occasionally as well.

I've upgraded MySQL to the latest 3.24 version (3.24.54), and made sure the php.ini has the appropriate parameters to disable the timeouts that affect pconnect(), i.e.

mysql.allow_persistent = On
mysql.max_persistent = -1
mysql.max_links = -1
mysql.connect_timeout = -1 

The load is not the issue since there are generally only a handful of users on rather powerful server at any given time (CPU load average is generally 0.2% or so). I'm using FreeBSD 4.6 and Apache 1.3.27.

I've rebooted the system, but that made no difference, either. The problems, as I mentioned in the first message, began when I upgraded to PHP 4.3.0. It would suck to have to go back to 4.2.3, but I'm starting to run out of ideas of what to try.

I've updated the the mysqladmin -u root -p variables output and the extended mysql diagnostics script output.

Thanks for any ideas!!

[Ville]

Resolved, really (I think

It seems that changing the database server setting in admin/config.php from the server name to "localhost" did the trick. At the same time the board operations became noticeably quicker.

So, while I don't know why it helped (why did pconnect() fail when access to the database looped through the network), the problem is fixed. Obviously, connect() wasn't as much affected by the problem, probably because the connect() connections are more momentary than pconnect() connections that keep the link open for the lasting of a session.