Hey Justin,
Could you send me a proper patch with all your changes to the mysql
connector, to be sure that I don't miss anything?
Best regards,
Wim.
On 18 December 2014 at 01:58, Justin Hammond <jus...@dynam.ac> wrote:
> Hi Wim,
> I've not noticed any performance impact. As it doesn't execute any actual
> query, I suspect only network latency would be a factor.
>
> Unfortunately in my network setup, both timeouts and firewall related
> connection losses are a fact of life, and since putting this patch in
> place, I have a reasonably stable app now.
>
> By the way, after posting I noticed a (new?) MySQL option to execute a
> query on reconnects automatically. It would help the Init calls, but I
> suspect Wt would still face issues on the prepared statements with a stale
> handler, so the ping would still be required.
>
> Sent from my iPhone
>
> On 18 Dec, 2014, at 6:25 am, "Wim Dumon" <w...@emweb.be> wrote:
>
> 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
>>
> <inline.txt>
>
> <inline.txt>
>
>
>
> ------------------------------------------------------------------------------
> 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
>
>
------------------------------------------------------------------------------
Dive into the World of Parallel Programming! The Go Parallel Website,
sponsored by Intel and developed in partnership with Slashdot Media, is your
hub for all things parallel software development, from weekly thought
leadership blogs to news, videos, case studies, tutorials and more. Take a
look and join the conversation now. http://goparallel.sourceforge.net
_______________________________________________
witty-interest mailing list
witty-interest@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/witty-interest
