MySQL Bugs: #74046: Mysql-Proxy freezes if used with MyODBC

Bug #74046	Mysql-Proxy freezes if used with MyODBC
Submitted:	24 Sep 2014 4:54	Modified:	21 Jun 2016 9:55
Reporter:	Jay J	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	2.2.14p2-5ubuntu5	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	disconnect, MySQL, mysql-proxy, ODBC, timeout

Description:
We are using Mysql Proxy in conjunction with MySQL ODBC connector in Asterisk.

Everything starts out fine - the requests go through without delay, and we are successfully getting all of the necessary data. 

However, after around 5 minutes of inactivity, ODBC can no longer query the data - the connection just hangs, and no reconnect is happening.

Notes:

-If no proxy is used, ODBC successfully reconnects after the connection times out
-If we restart Asterisk/ODBC - then the connection is established again, and will work for 5 more minutes before hanging

We have tried:

1) Building mysql-proxy from source as well as using the repo deb package (Ubuntu Trusty)
2) Multiple versions of MyODBC and unixODBC
3) Not using a lua filter
4) Using lua-keepalive
5) Using lua-pooling
6) Setting all mysql timeouts to the highest (although no issues exist if we take the proxy out of the equation)
7) Playing with various ODBC settings:
-Pooling/no pooling
-CPTimeout
-Option 3
-DontDLClose
8) Trying various client (Asterisk) odbc settings
-Connection sharing
-Limits
-Pre-connects
-Sanity checks
-Idle checks
9) Running the connection over HAProxy
-Asterisk-Haproxy-MySQLProxy-DB
-Asterisk-MysqlProxy-DB
-Asterisk-Haproxy-MysqlProxy-Haproxy-DB

How to repeat:
1)Establish an ODBC connection to MySQL via MySQLProxy
2)Wait for 5 minutes
3)Try to connect via isql

even with verbose enabled (-v), we see no output and no connection is made

Which version of mysql-proxy is used? "0.8" is very vague.

$ mysql-proxy --version

The one that we have currently is:

mysql-proxy 0.9.0
  chassis: mysql-proxy 0.9.0
  glib2: 2.40.0
  libevent: 2.0.21-stable
  LUA: Lua 5.1.5

Please note that we have tried multiple versions of Mysql Proxy, no avail

To isolate the issue further we have set up a basic checker that would perform select 1; every 5 seconds. That's what I see in my logs:

2014-10-07 13:24:07		6119801 query accepted :select 1
2014-10-07 13:24:24: [global] last message repeated 2 times

2014-10-07 13:24:24: [global] (debug) [network-mysqld.c:1272]: error on a connection (fd: -1 event: 0). closing client connection.
2014-10-07 13:24:58: [global] last message repeated 2 times

On version 8.5 however, the previous error message is not present

Our investigation has led us to a surprising conclusion: Mysql-Proxy is NOT causing this, but rather the ODBC driver is.

After extensiv research and testing we have found that the issue is related to ODBC freezing when a remotely-located MySQL backend goes away. Moreover, it fails to reconnect to it unless a significant amount of time (around 15-20 minutes) has passed.

This bug has been previously described here http://bugs.mysql.com/bug.php?id=63601 , but without a solution.

And as per this topic (http://forums.digium.com/viewtopic.php?f=1&t=90494) as well as this one (http://forums.asterisk.org/viewtopic.php?f=1&t=85954) and others, this is a fairly common problem, and I urge the maintainers of ODBC to consider fixing it. I will gladly help in testing, and, if that will help, am willing to provide a Dockerfile that will easily recreate that issue (or even access to a sandbox setup where the issue is present).

Thanks!

Hello Jay J,

Thank you for your feedback.
I could not repeat the issue at our end using with Connector / ODBC 5.3.6. 
If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Thanks,
Chiranjeevi.