Justin Hammond
2014-12-04 15:56:36 UTC
Hi,
I've been investigating why I started getting SQL errors after a few
hours of my app in production, but a restart of the app would fix things
(and it worked fine in our lab for days on end).
The issue came down to the mysql reconnection option that is setup when
we connect to the database. The problem is that upon reconnection:
1) the SQL statements in the init function are not called, so you get
SQL validation errors.
2) that any saved prepared statement is associated with old SQL
connections, and they error out with a "mysql went away" message when
the execute function was called in the MySQLStatement class. (I suspect
that there are further issues here... see below).
To fix this, I've added the following code blocks to the mySQL class in
the executeSQL, startTransaction, commitTransaction and
rollbackTransaction functions:
unsigned long id = mysql_thread_id(this->connection()->mysql);
if (mysql_ping(this->connection()->mysql) != 0) {
Wt::log("error") << "startTransaction: MySQL Error: " <<
mysql_error(this->connection()->mysql);
throw;
}
if (id != mysql_thread_id(this->connection()->mysql)) {
Wt::log("warning") << "startTransaction: MySQL reconnect " <<
mysql_thread_id(this->connection()->mysql);
this->clearStatementCache();
this->init();
}
(all it does is compare the connectionid (or threadid) of the connection
before and after we do a mysql_ping, and if they are different, then
clear the statementcache and rerun the init() functions. (I had to make
both public for fix 2 below).
for the mySQLStatement class, the fix is almost identical:
in the constructor:
unsigned long id = mysql_thread_id(conn_.connection()->mysql);
if (mysql_ping(conn_.connection()->mysql) != 0) {
Wt::log("error") << "myMySQLStatement: MySQL Error: " <<
mysql_error(conn_.connection()->mysql);
throw;
}
if (id != mysql_thread_id(conn_.connection()->mysql)) {
Wt::log("warning") << "myMySQLStatement: MySQL reconnect " <<
mysql_thread_id(conn_.connection()->mysql);
conn_.init();
}
and in the execute method I added the above, plus the following just
before the mysql_stmt_bind_param call:
if (stmt_->mysql != conn_.connection()->mysql)
stmt_->mysql = conn_.connection()->mysql;
This fixes the issue for us. I'm not sure if its a perfect fix for Wt,
but at least gets our production stable.
There is potential issues I see with this and storing prepared
statements:
1) native stored statements are tied to a MySQL connection (hence the
additional code in the execute function), but as far as I can tell,
stored statements in Dbo are stored per connection. So if you are using
a SQLConnectionPool, you might end up with duplicate statements stored
in each connection. I might be reading this wrong, so if I am ignore
me :)
To reproduce the issue above is quite simple. Fire up any app that talks
to a MySQL database, exercise the app so a few queries are fired, then
use the mysql command line (or phpMyAdmin) to kill the connections from
the server end. Refresh your app (or navigate around) and all of a
sudden either SQL statement errors, or "server has gone away" messages.
It would be good if this, or something similar can go into the next
release so I dont have to carry my own copy of the MySQL classes in my
app.
Cheers
Justin
I've been investigating why I started getting SQL errors after a few
hours of my app in production, but a restart of the app would fix things
(and it worked fine in our lab for days on end).
The issue came down to the mysql reconnection option that is setup when
we connect to the database. The problem is that upon reconnection:
1) the SQL statements in the init function are not called, so you get
SQL validation errors.
2) that any saved prepared statement is associated with old SQL
connections, and they error out with a "mysql went away" message when
the execute function was called in the MySQLStatement class. (I suspect
that there are further issues here... see below).
To fix this, I've added the following code blocks to the mySQL class in
the executeSQL, startTransaction, commitTransaction and
rollbackTransaction functions:
unsigned long id = mysql_thread_id(this->connection()->mysql);
if (mysql_ping(this->connection()->mysql) != 0) {
Wt::log("error") << "startTransaction: MySQL Error: " <<
mysql_error(this->connection()->mysql);
throw;
}
if (id != mysql_thread_id(this->connection()->mysql)) {
Wt::log("warning") << "startTransaction: MySQL reconnect " <<
mysql_thread_id(this->connection()->mysql);
this->clearStatementCache();
this->init();
}
(all it does is compare the connectionid (or threadid) of the connection
before and after we do a mysql_ping, and if they are different, then
clear the statementcache and rerun the init() functions. (I had to make
both public for fix 2 below).
for the mySQLStatement class, the fix is almost identical:
in the constructor:
unsigned long id = mysql_thread_id(conn_.connection()->mysql);
if (mysql_ping(conn_.connection()->mysql) != 0) {
Wt::log("error") << "myMySQLStatement: MySQL Error: " <<
mysql_error(conn_.connection()->mysql);
throw;
}
if (id != mysql_thread_id(conn_.connection()->mysql)) {
Wt::log("warning") << "myMySQLStatement: MySQL reconnect " <<
mysql_thread_id(conn_.connection()->mysql);
conn_.init();
}
and in the execute method I added the above, plus the following just
before the mysql_stmt_bind_param call:
if (stmt_->mysql != conn_.connection()->mysql)
stmt_->mysql = conn_.connection()->mysql;
This fixes the issue for us. I'm not sure if its a perfect fix for Wt,
but at least gets our production stable.
There is potential issues I see with this and storing prepared
statements:
1) native stored statements are tied to a MySQL connection (hence the
additional code in the execute function), but as far as I can tell,
stored statements in Dbo are stored per connection. So if you are using
a SQLConnectionPool, you might end up with duplicate statements stored
in each connection. I might be reading this wrong, so if I am ignore
me :)
To reproduce the issue above is quite simple. Fire up any app that talks
to a MySQL database, exercise the app so a few queries are fired, then
use the mysql command line (or phpMyAdmin) to kill the connections from
the server end. Refresh your app (or navigate around) and all of a
sudden either SQL statement errors, or "server has gone away" messages.
It would be good if this, or something similar can go into the next
release so I dont have to carry my own copy of the MySQL classes in my
app.
Cheers
Justin