Hey Justin,
I encountered this problem too and couldn't think of a solution other than
to avoid connection timeouts. I think I like your solution (much) more. Do
you have an idea on the performance impact of the frequent mysql_ping()
calls?
Best regards,
Wim.
On 4 December 2014 at 16:56, Justin Hammond <jus...@dynam.ac> wrote:
>
> 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
>
>
>
>
>
> ------------------------------------------------------------------------------
> Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
> from Actuate! Instantly Supercharge Your Business Reports and Dashboards
> with Interactivity, Sharing, Native Excel Exports, App Integration & more
> Get technology previously reserved for billion-dollar corporations, FREE
>
> http://pubads.g.doubleclick.net/gampad/clk?id=164703151&iu=/4140/ostg.clktrk
> _______________________________________________
> witty-interest mailing list
> witty-interest@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/witty-interest
>
------------------------------------------------------------------------------
Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
from Actuate! Instantly Supercharge Your Business Reports and Dashboards
with Interactivity, Sharing, Native Excel Exports, App Integration & more
Get technology previously reserved for billion-dollar corporations, FREE
http://pubads.g.doubleclick.net/gampad/clk?id=164703151&iu=/4140/ostg.clktrk
_______________________________________________
witty-interest mailing list
witty-interest@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/witty-interest
