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
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.
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
directory within the source tree. The first file to edit there is php_mysql_structs.h. Edit the section after
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
. Here you have to make modifications in four places. First, in the block right after
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)
{
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
. 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
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.
