[mythtv-users] MySQL related BE deadlocks - collective wisdom needed

[Michael T. Dean]

On 07/27/2011 03:59 AM, David Edwards wrote:
> On 26 July 2011 18:10, Michael T. Dean wrote:
>> Warpme, out of curiosity, what happens if you revert
>> https://github.com/MythTV/mythtv/commit/528324d8
> Interesting... is there a reason that custom reconnect code was written? I
> think that the MySQL client lib has an auto-reconnect option that can be set
> when initially creating the DB connection. We successfully use this at work
> (it works seamlessly for us), although this is from Perl code using DBI
> rather than C++ with Qt. I see a comment in mythdbcon.cpp about reconnect
> behaviour from 2003, but that could be out of date.
>
> I also found this which looks very relevant:
> https://bugreports.qt.nokia.com//browse/QTBUG-17140

If for no other reason, because we've never had a released version of 
MythTV whose minimum required Qt version was 4.6 or higher--and 
MYSQL_OPT_RECONNECT support was added to the Qt-MySQL driver in Qt 4.6.  
We had discussed it back in 2010, but since we required Qt 4.5+ at that 
time, it wasn't an option.  I had meant to look at re-enabling it when 
Qt 4.6 became a requirement, but hadn't yet remembered.

Note that before MySQL 5, reconnecting connections were the default in 
the MySQL C API (so, were always enabled for the Qt-MySQL driver).  
Unfortunately, depending on how the application is written, 
reconnections may cause issues (since reconnections have effects on 
transactions, table locks, temporary tables, session and user variables, 
prepared statements, and such), so MySQL 5.0.3 changed the default to 
disable automatic reconnections (and MySQL 5.0.13 added 
MYSQL_OPT_RECONNECT to make it an application-controllable option).  
Since the Qt-MySQL driver didn't have support for specifying 
MYSQL_OPT_RECONNECT, it was impossible to get reconnecting connections 
with MySQL 5.0.3+ in Qt applications.  This actually caused a huge 
number of problems for us way back in the day (around March 2005)--even 
before we switched to Qt4 and made MySQL 5.0.15+ a requirement for 
MythTV.  Therefore, much code was written to work around issues.  
Finally in Qt4.6, Qt devs added support for specifying MYSQL_OPT_RECONNECT.

Since 0.24 was released, we've changed the minimum Qt version to 4.6 (as 
of May 22), so I will probably change the code to enable 
MYSQL_OPT_RECONNECT.  Thanks for the reminder.

Mike