| Bug #78367 | AUTO_RECONNECT Option: 4194304 does not work on ODBC Windows | ||
|---|---|---|---|
| Submitted: | 8 Sep 2015 12:21 | Modified: | 11 Sep 2015 11:47 |
| Reporter: | Jan Fex | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S2 (Serious) |
| Version: | 5.3.4 | OS: | Windows (Both 32 and 64 bit ODBC) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | automatic, ODBC, reconnect | ||
[9 Sep 2015 9:44]
Chiranjeevi Battula
Hello Jan Fex, Thank you for the bug report. I tried to reproduce the issue at my end using VS 2013 and Connector / ODBC 5.3.4 32/64 bit Unicode but couldn't trace out any issue and it is working as per manual. When the connection is broken the function is not going to silently swallow the error and error will returned, but re-trying the same function is going to succeed(If mysql_query() is failed, they have to call it again in the exception handler). User manual about AUTO_RECONNECT fields http://dev.mysql.com/doc/connector-odbc/en/connector-odbc-configuration-connection-paramet... Thanks, Chiranjeevi.
[9 Sep 2015 11:57]
Jan Fex
Thanks for the answer. A bit strange then that the version I built myself has different behavior. We are using ODBC from two different application. National Instrument Teststand that most likely are using the C ODBC interface (no access to their code) and our own C# application. When using our own build mysql connector the error is silently swallowed and the ODBC request succeed. When using standard installation of the connector we get error. On one MySQL server we have timeout on 10 min. An inactive connection will after this time be cleaned up on Server. If we use standard installation we run into problem when an application is idle more than 10 min. Teststand will fail update the database and the test will fail. Thanks Jan
[9 Sep 2015 12:43]
Chiranjeevi Battula
Hello Jan Fex, Thank you for your feedback. Please go through the Bug #76597, it may help you to solve your problem: Thanks, Chiranjeevi.
[10 Sep 2015 13:39]
Jan Fex
Where are using Connector/ODBC while Bug #76597 and related are server error or Connector/NET We cannot change setting on MySQL server. 76597 points to #72110 that is very similar to our problem. The proposed solution that unfortunately does not work with ODBC (ping() is not exposed) private void CheckConnection() { if(!_conn.Ping()) { _conn.Open(); } } Internally in MySQL there is a method that seems to be called before sending queries in file utility.c check_if_server_is_alive -- row 2221 at row 2226 if ( (ulong)(seconds - dbc->last_query_time) >= CHECK_IF_ALIVE ) -- CHECK_IF_ALIVE = 1800 seconds { if ( mysql_ping( &dbc->mysql ) ) { We tried that solution, Make a connection and a select kill connection on server wait at list half an hour do a new select This should trigger a call to mysql_ping() Unfortunately it is not working, still get ODBC 5.3(w) Driver][mysqld-5.1.67-0ubuntu0.11.10.1]Lost connection to MySQL server during query What I understand there should be a Open on error if matching above CheckConnection What I can see there are two solutions, either 1. Use our own built ODBC library that is a build of mysql-connector-odbc-5.3.4-src 2. Use a workaround in teststand where we force it to close current connection and open a new one for each test update. For our own C# application we can catch ODBC connection, check for Lost connection and send request once again However if would be nice for future version of ODBC driver that it on mysql_ping() does a new Open when ping failes. If would also be nice to be able to configure the CHECK_IF_ALIVE value through an Option in connection string. (alternative would be that mysqlclient.lib internally does similar) Thanks for your support Jan
[11 Sep 2015 7:45]
Bogdan Degtyariov
C# Test case
Attachment: bug78367.zip (application/zip, text), 10.35 KiB.
[11 Sep 2015 7:48]
Bogdan Degtyariov
Hi Jan, I attached the C# test case, which demonstrates how the auto-reconnect function works. It creates connection, executes a command, then connection is killed. Trying to execute the same command on the same connection fails and throws the error (but at that stage the connection is restored and retrying the same command again succeeds). Here is the log: Opening a new connection.... [CONNECTION OPENED] Executing SQL: SELECT 'OK From MySQL Server', connection_id() CONNECTION_ID: 21 SERVER REPLY: OK From MySQL Server Kill the connection ID=21 [CONNECTION ID=21 IS KILLED] Executing SQL: SELECT 'OK From MySQL Server', connection_id() Executing SQL: SELECT 'OK From MySQL Server', connection_id() EXPECTED ERROR! ERROR [08S01] [MySQL][ODBC 5.3(w) Driver][mysqld-5.7.8-rc-debug-log]Lost connection to MySQL server during query RETRYING COMMAND! Executing SQL: SELECT 'OK From MySQL Server', connection_id() CONNECTION_ID: 23 SERVER REPLY: OK From MySQL Server [TEST SUCCESS] Please note that the ODBC error is always thrown (even if auto-reconnect is enabled). However, executing the same command once again will work. In my test case the exception is caught and the same command is executed again.
[11 Sep 2015 7:52]
Bogdan Degtyariov
Please note that the first time CONNECTION_ID was 21, then after the error and automatic reconnect CONNECTION_ID became 23. (CONNECTION_ID = 22 was created to kill connection 21)
[11 Sep 2015 8:34]
Jan Fex
Thanks for the code. Here is my result: 64 BIT ODBC installed mysql-connector-odbc-5.3.4-win64.exe Opening a new connection.... [CONNECTION OPENED] Executing SQL: SELECT 'OK From MySQL Server', connection_id() CONNECTION_ID: 4 SERVER REPLY: OK From MySQL Server Kill the connection ID=4 [CONNECTION ID=4 IS KILLED] Executing SQL: SELECT 'OK From MySQL Server', connection_id() EXPECTED ERROR! ERROR [08S01] [MySQL][ODBC 5.3(w) Driver][mysqld-5.6.26]Lost connection to MySQL server during query RETRYING COMMAND! Executing SQL: SELECT 'OK From MySQL Server', connection_id() CONNECTION_ID: 6 SERVER REPLY: OK From MySQL Server [TEST SUCCESS] 32 BIT installation mysql-connector-odbc-5.3.4-win32.exe Opening a new connection.... [CONNECTION OPENED] Executing SQL: SELECT 'OK From MySQL Server', connection_id() CONNECTION_ID: 13 SERVER REPLY: OK From MySQL Server Kill the connection ID=13 [CONNECTION ID=13 IS KILLED] Executing SQL: SELECT 'OK From MySQL Server', connection_id() EXPECTED ERROR! ERROR [08S01] [MySQL][ODBC 5.3(w) Driver][mysqld-5.6.26]Lost connection to MySQL server during query RETRYING COMMAND! Executing SQL: SELECT 'OK From MySQL Server', connection_id() CONNECTION_ID: 15 SERVER REPLY: OK From MySQL Server [TEST SUCCESS] 32 bit ODBC built from source: mysql-connector-odbc-5.3.4-src + mysql-5.6.26-win32 for lib and include Opening a new connection.... [CONNECTION OPENED] Executing SQL: SELECT 'OK From MySQL Server', connection_id() CONNECTION_ID: 19 SERVER REPLY: OK From MySQL Server Kill the connection ID=19 [CONNECTION ID=19 IS KILLED] Executing SQL: SELECT 'OK From MySQL Server', connection_id() CONNECTION_ID: 21 SERVER REPLY: OK From MySQL Server [TEST SUCCESS] Thanks Jan
[11 Sep 2015 11:39]
Bogdan Degtyariov
Hi Jan, Thanks for running the tests. As I see with the driver built by you the exception did not happen at all. This might be convenient sometimes, but our vision of the problem is different. First of all, AUTO_RECONNECT option is dangerous because of many things: - session variables are reset - temp tables are dropped - prepared statements are gone - transactions are rolled back - other session-related stuff For the majority of applications if such things happens unnoticed it might break the logical data integrity. Therefore, with the way it works now we assure the disconnect problem does not go unnoticed on the user side. For the same reason the AUTO_RECONNECT option is not enabled by default anymore. I hope my explanations make sense to you. As demonstrated by the test code, the function works. It just works in a different way than you expected. Unfortunately, many other mysql users are mislead by the concept of automatic reconnecting. This is not a bug. You either need to put try/catch statements in the critical places and retry the operation when it fails due to disconnect or tweak the driver code just as you did in the last case.
Description: AUTO_RECONNECT Option: 4194304 does not work on 32 bit ODCB, mysql-connector-odbc-5.3.4-win32.msi an 64 bit installation, mysql-connector-odbc-5.3.4-winx64 The same is happening when using odbcad32.exe configuring System DSN which checked box "Enable automatic connection" It work when we build the code ourself by downloading mysql-connector-odbc-5.3.4-src - 32 bit version mysql-5.6.26-win32 - 32 bit version and build ODBC with Visual Studio Express 2013 using CMake with default parameters beside setting up path for MySQL include and lib that point to mysql-5.6.26-win32 If we in our code in connect.c line 371 remove the code if (ds->auto_reconnect) { mysql_options(mysql, MYSQL_OPT_RECONNECT, (char *)&on); } Then we get same behavior as the installed one, ODBC exception on select after connection is killed on server. How to repeat: Test application, in our case C# application Connect to server using MySQL workbench Test 1: Build test application for 64 bit.Connect to server. On server you can see the client connection. Kill it. Through application do a select query. Query generate ODBC exception. System.Data.Odbc.OdbcException (0x80131937): ERROR [08S01] [MySQL][ODBC 5.3(w) Driver][mysqld-5.1.67-0ubuntu0.11.10.1]Lost connection to MySQL server during query Test 2: Build test application for 32 bit.Connect to server. On server you can see the client connection. Kill it. Through application do a select query. Query generate ODBC exception. System.Data.Odbc.OdbcException (0x80131937): ERROR [08S01] [MySQL][ODBC 5.3(w) Driver][mysqld-5.1.67-0ubuntu0.11.10.1]Lost connection to MySQL server during query Copy all files from own build ODBC to C:\Program Files (x86)\MySQL\Connector ODBC 5.3. Repeat Test 2. In this case no ODBC exception, select return data as expected Connection string used: "Driver={MySQL ODBC 5.3 Unicode Driver};Server=<ip>;User=<user>;Password=<passw>;Option=4194304"