MySQL Tuning: The PHP Auto-Reconnect Patch

Now this would be really funny if it weren’t so sad in so many aspects: I know of more than one company running MySQL. Ok, no news there. But the MySQL servers of said companies are dropping connections. Not twice a week or once a day but two or three times every bloody second. Investigation of the cause is underway but obviously that doesn’t help to fix the problem at hand.
Since the major platform in said companies is PHP, there’s another problem: Tests have shown that if a connection failed a subsequent connection request will go through just fine. While not ideal, the best solution for the moment would therefor be to enable the auto-reconnect feature built into every MySQL client. But for PHP, there is no option to do just that.

That’s because even while PHP is using the

mysql_option()

function – which is needed to enable auto-reconnect – internally, nobody cared to make it available as part of PHP’s language. Maybe it would be easy to do just that, but I found it easier to patch PHP directly to enable auto-reconnect by default. You want to know how? Read on.

{openx:6}

First, go and download a quite recent archive of the PHP sources from the PHP 5.2.x branch. I haven’t looked thoroughly into 5.3.x by now but as fas as I can see neither the still available MySQL support nor the newer mysqlnd sport an auto-reconnect feature so maybe the patch applies there, too.

After unpacking the sources go to the

ext/mysql

directory within the source tree. The first file to edit there is php_mysql_structs.h. Edit the section after

ZEND_BEGIN_MODULE_GLOBALS(mysql)

like this:

ZEND_BEGIN_MODULE_GLOBALS(mysql)
long default_link;
long num_links,num_persistent;
long max_links,max_persistent;
long allow_persistent;
long default_port;
char *default_host, *default_user, *default_password;
char *default_socket;
char *connect_error;
long connect_errno;
long connect_timeout;
int auto_reconnect;
long result_allocated;
long trace_mode;
ZEND_END_MODULE_GLOBALS(mysql)

Save and close the file, then open

php_mysql.c

. Here you have to make modifications in four places. First, in the block right after

PHP_INI_BEGIN

add a line like shown here:

PHP_INI_BEGIN()
STD_PHP_INI_BOOLEAN(“mysql.allow_persistent”, “1”, PHP_INI_SYSTEM, OnUpdateLong, allow_persistent, zend_mysql_globals, mysql_globals)
STD_PHP_INI_ENTRY_EX(“mysql.max_persistent”, “-1”, PHP_INI_SYSTEM, OnUpdateLong, max_persistent, zend_mysql_globals, mysql_globals, display_link_numbers)
STD_PHP_INI_ENTRY_EX(“mysql.max_links”, “-1”, PHP_INI_SYSTEM, OnUpdateLong, max_links, zend_mysql_globals, mysql_globals, display_link_numbers)
STD_PHP_INI_ENTRY(“mysql.default_host”, NULL, PHP_INI_ALL, OnUpdateString, default_host, zend_mysql_globals, mysql_globals)
STD_PHP_INI_ENTRY(“mysql.default_user”, NULL, PHP_INI_ALL, OnUpdateString, default_user, zend_mysql_globals, mysql_globals)
STD_PHP_INI_ENTRY(“mysql.default_password”, NULL, PHP_INI_ALL, OnUpdateString, default_password, zend_mysql_globals, mysql_globals)
PHP_INI_ENTRY(“mysql.default_port”, NULL, PHP_INI_ALL, OnMySQLPort)
STD_PHP_INI_ENTRY(“mysql.default_socket”, NULL, PHP_INI_ALL, OnUpdateStringUnempty, default_socket, zend_mysql_globals, mysql_globals)
STD_PHP_INI_ENTRY(“mysql.connect_timeout”, “60”, PHP_INI_ALL, OnUpdateLong, connect_timeout, zend_mysql_globals, mysql_globals)
STD_PHP_INI_ENTRY(“mysql.auto_reconnect”, “1”, PHP_INI_ALL, OnUpdateLong, auto_reconnect, zend_mysql_globals, mysql_globals)
STD_PHP_INI_BOOLEAN(“mysql.trace_mode”, “0”, PHP_INI_ALL, OnUpdateLong, trace_mode, zend_mysql_globals, mysql_globals)
PHP_INI_END()

Right after this, edit the block starting with

static PHP_GINIT_FUNCTION(mysql)

:

static PHP_GINIT_FUNCTION(mysql)
{
mysql_globals->num_persistent = 0;
mysql_globals->default_socket = NULL;
mysql_globals->default_host = NULL;
mysql_globals->default_user = NULL;
mysql_globals->default_password = NULL;
mysql_globals->connect_errno = 0;
mysql_globals->connect_error = NULL;
mysql_globals->connect_timeout = 0;
mysql_globals->auto_reconnect = 1;
mysql_globals->trace_mode = 0;
mysql_globals->result_allocated = 0;
}

The next modifications are to be done in the function

static void php_mysql_do_connect(INTERNAL_FUNCTION_PARAMETERS, int persistent)

. The first is around line 529 for PHP 5.2.9 (modifications in bold):

zend_bool free_host=0, new_link=0;
long connect_timeout;
my_bool auto_reconnect;

connect_timeout = MySG(connect_timeout);
auto_reconnect = MySG(auto_reconnect);

Staying in the same function, around line 649 edit like this:

if (connect_timeout != -1) {
mysql_options(&mysql->conn, MYSQL_OPT_CONNECT_TIMEOUT, (const char *)&connect_timeout);
}
mysql_options(&mysql->conn, MYSQL_OPT_RECONNECT, (my_bool *)&auto_reconnect);

And finally, still in the same function around line 755, the last modification:

if (connect_timeout != -1) {
mysql_options(&mysql->conn, MYSQL_OPT_CONNECT_TIMEOUT, (const char *)&connect_timeout);
}
mysql_options(&mysql->conn, MYSQL_OPT_RECONNECT, (my_bool *)&auto_reconnect);

After building a new PHP using the standard

./configure; make; make install

procedure you now have a PHP with an auto-reconnecting MySQL subsystem. Note, that the auto-reconnect option will only try to reconnect up to four times. After that it’ll still issue an error.