Your comment was added to the bug successfully.
Bug #14639 Regression in Connector/ODBC 3.51.12
Submitted: 4 Nov 2005 13:22 Modified: 13 Sep 2007 13:55
Reporter: d di (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:3.51.12 OS:Windows (Windows XP SP2)
Assigned to: Bogdan Degtyariov CPU Architecture:Any

[4 Nov 2005 13:22] d di 
Description:
There's a regression in MyODBC 3.51.12 vs. 3.51.10.

Using MyODBC with .Net, the OdbcConnection.State property no longer correctly reflects the connection's state.

(It seems to be a side effect of removal of some auto-reconnection logic in MyODBC.)

How to repeat:
Use the following test application and the steps provided below.

Test app (C#):
---------------
using System;
using System.Data;
using System.Data.Odbc;

class MySqlConnectTest {
	static string user = "INSERT USER HERE";
	static string password = "INSERT PASSWORD HERE";
	static string server = "INSERT SERVER HERE";

	static OdbcCommand connect() {
		string c = "Driver={MySQL ODBC 3.51 Driver};Server=" + server + ";Uid=" + user + ";Pwd=" + password + ";OPTION=3";
		OdbcConnection conn = new OdbcConnection(c);
		conn.Open();
		OdbcCommand cmd = conn.CreateCommand();
		cmd.CommandText = "SELECT VERSION()";
		return cmd;
	}

	static void Main(string[] args) {
		OdbcCommand cmd;

		Console.Out.WriteLine("Opening connection...");
		cmd = connect();
		cmd.ExecuteNonQuery();

		Console.Out.WriteLine("Ok.  Kill database or connection now, then press Enter.");
		Console.In.ReadLine();

		// Simulate a continuously running program that use only 1 connection=
		for (int i = 5; i > 0; i--) {
			try {
				if ((cmd.Connection.State | ConnectionState.Open) == ConnectionState.Open)
					Console.Out.WriteLine("Connection state '" + cmd.Connection.State + "' - executing query.");
				else {
					Console.Out.WriteLine("Connection state '" + cmd.Connection.State + "', opening and executing query.");
					cmd = connect();
				}
				cmd.ExecuteNonQuery();
			} catch (Exception e) {
				Console.Out.WriteLine("Error: " + e.Message + "\n");
			}
		}
		Console.Out.WriteLine("\nDone - press Enter.");
		Console.In.ReadLine();
	}
}
---------------

The above test application is similar to application logic that we use to recreate a connection when it has been closed behind our back for one reason or another.

You will probably find TCPView from Sysinternals (http://www.sysinternals.com) handy to simulate a database restart.

1. Compile above C# code with fx. csc.exe.
2. Install MyODBC 3.51.10 or 3.51.12 (run the test once with both).
3. Run test application
4. When it says "kill connection now", use TCPView to close the connection.
5. Observe the difference in behaviour.....

Test results:
===============

MyODBC 3.51.10:
----------------
X:\>MySqlConnectTest.exe
Opening connection...
Ok.  Kill database or connection now, then press Enter.
[ Note: killed connection here ]

Connection state 'Open' - executing query.
Connection state 'Open' - executing query.
Connection state 'Open' - executing query.
Connection state 'Open' - executing query.
Connection state 'Open' - executing query.

Done - press Enter.
----------------

Seems MyODBC 3.51.10 reconnects behind our back.
State is (correctly) == ConnectionState.Open.

MyODBC 3.51.12:
----------------
X:\>MySqlConnectTest.exe
Opening connection...
Ok.  Kill database or connection now, then press Enter.
[ Note: killed connection here ]

Connection state 'Open' - executing query.
Error: ERROR [HYT00] [MySQL][ODBC 3.51 Driver][mysqld-4.1.9-standard-log]MySQL server has gone away

Connection state 'Open' - executing query.
Error: ERROR [HYT00] [MySQL][ODBC 3.51 Driver][mysqld-4.1.9-standard-log]MySQL server has gone away

Connection state 'Open' - executing query.
Error: ERROR [HYT00] [MySQL][ODBC 3.51 Driver][mysqld-4.1.9-standard-log]MySQL server has gone away

Connection state 'Open' - executing query.
Error: ERROR [HYT00] [MySQL][ODBC 3.51 Driver][mysqld-4.1.9-standard-log]MySQL server has gone away

Connection state 'Open' - executing query.
Error: ERROR [HYT00] [MySQL][ODBC 3.51 Driver][mysqld-4.1.9-standard-log]MySQL server has gone away

Done - press Enter.
----------------

Seems MyODBC 3.51.12 does _not_ reconnect behind our back.
State is (incorrectly) still == ConnectionState.Open.

Suggested fix:
I'm imagining that State should say "Closed" at least when:
* the connection has died behind our back and
* we've tried executing a query which failed due to the above.
 
The .Net documentation says:
"Calling the State property on an open connection increases application overhead because each such call results in a SQL_ATTR_CONNECTION_DEAD call to the underlying ODBC driver to determine if the connection is still valid."

If the first assumption is not valid (which IMHO renders the connection state information somewhat useless), the SQL_ATTR_CONNECTION_DEAD behaviour should at least be documented to be "weird".

Workaround:
=============
We've now implemented our own ConnectionState mechanism instead of using MyODBC's.
[6 Nov 2005 10:30] Vasily Kishkin 
Thanks for the bug report and test case. MyODBC 3.51.12 really does not re-connect. MyODBC 3.51.10 works fine.
[6 Nov 2005 10:31] Vasily Kishkin 
Test case

Attachment: 14639.zip (application/force-download, text), 5.84 KiB.
[7 Nov 2005 7:41] d di 
Just for the record, I think it's fine that it doesn't reconnect.

(It would of course be very nice with an option to do automatic reconnection, but other than that I don't see a problem in leaving the reconnection logic and _choice_ to the application programmer.)

My problem with the current behaviour is that Connector/ODBC doesn't reflect the fact that the connection has died into the State property.  I think it's pretty clear that it should..
[15 Dec 2005 11:02] d di 
Martin J. Evans has a problem that seems very similar to me.

Read about it here:
http://lists.mysql.com/myodbc/10517
[16 Dec 2005 14:08] d di 
"me" --> "mine", obviously ;-).

Looking at a piece of old myodbc code, it seems that the problem could also reside in mysql_ping(), in which case this bug should maybe be recategorized to 'MySQL Server'.
[2 Mar 2006 9:07] d di 
To help narrow things down, I've tested 3.51.11-2.

It's ok, so this bug must have been introduced in 3.51.12.
[6 Mar 2006 8:08] Jim Clark 
I have the same Problem with ODBC 3.51.12.
My problem is i can't change the software which uses odbc.
So after 8 hours idle, my sql server closed connection and
in my software i get the message server has gone away.

I think best solution is to make an option in odbc driver where
the user can set if automatic reconnect is allowed or not.

Where can i download the 3.51.11-2 to correct my problem until
the next release of odbc is ready for download ?

thanks !!

jim
[10 Mar 2006 7:17] Peter Harvey 
1. Possible problem with mysql_ping()

                The 4.1 doc says when mysql_ping() fails we can get one
                of the following errors from mysql_errno();

                    CR_COMMANDS_OUT_OF_SYNC
                    CR_SERVER_GONE_ERROR
                    CR_UNKNOWN_ERROR   

                But if you do a mysql_ping() after bringing down the server
                you get CR_SERVER_LOST.

                Code has been changed to CR_SERVER_LOST. Bogdan
                please create a new bug for this problem and follow up.
                If retval changes then we need to change the code to
                match the documentation again.

2. SQL_ATTR_CONNECTION_DEAD

                This was using CHECK_IF_ALIVE which meant it was not
                always catching lost connection. Now does mysql_ping()
                without CHECK_IF_ALIVE. We may want CHECK_IF_ALIVE
                to be a connection/dsn option but not part of this bug.

3. Closed state seems to stick

                 The OdbcCommand.Connection.State seems to stick
                 once it catchs lost connection and becomes Closed.
                 OdbcCommand.Connection.State is supposed to get
                 SQL_ATTR_CONNECTION_DEAD but fails to request
                 it once in Closed state. 

I will provide David with a driver with fixs to try. Bogdan please follow up and close this bug as per Davids experience. Also; check for related bugs and Close as needed.
[10 Mar 2006 14:03] d di 
Thanks for the thorough analysis, Peter :-).
(I would have helped more, but I can't get MyODBC to compile..)

It works!

To be specific, it now works like it did in 3.51.10, which is great.

It's not *exactly* as I would prefer it to:

I would've preferred that when the connection dies, that it actually would reflect to OdbcConnection.State, so that I can control how many reconnects are attempted, timeouts etc.  This MyODBC will (like 3.51.10) just reconnect silently in the background, and so the OdbcConnection.State that we see from .NET is always 'Open' (with our test case at least).

It doesn't really matter to me since we've wrapped the entire ODBC namespace with a class that provides timeout functionality.  Just thought that I'd mention that there seems to be no way to control the reconnection login and that that could be an issue for others..  Hmm.

Regarding point 3), how exactly did you achieve the 'Closed' status, and how did you cause MyODBC to open the connection again without .NET reflecting it in State?  I'm guessing you shut down the database entirely so any automatic reconnects fail, but then what?

I'm curious because I need to test that our dead connection handling does the proper thing when it encounters 'Closed' with 3.51.13.

Also regarding point 3, I think it is in fact not only when the connection state is Closed that OdbcConnection.State fails to call SQL_ATTR_CONNECTION_DEAD, but always..  I've just checked OdbcConnection.State using Reflector, and it seems it's a quite dumb property - OdbcConnection.get_State() returns System.Data.ProviderBase.DbConnectionInternal._state, which is just a field with no logic in it.  It seems to me that this particular statement in the .NET docs: "Calling the State property on an open connection [...] causes a SQL_ATTR_CONNECTION_DEAD call [...]" is completely bogus.  Simply querying 'State' does not in itself cause that call.

Thanks a bunch for fixing!
[16 Mar 2006 10:39] Jim Clark 
Where can I download the 3.51.13. We still have that problem with our
Version 3.51.12. 

Thanks

Jim
[16 Mar 2006 11:12] d di 
For download instructions, see:
http://lists.mysql.com/myodbc/10629
(or search the MyODBC list archives.)

Note that this 3.51.13 is a prerelease made available for community review, not the final version.
[11 May 2006 16:50] Paul DuBois 
Not enough information is given to produce a changelog entry.
What is the version number for the fix? Is the problem reported
actually the bug that was fixed?  Thanks.
[9 Aug 2006 20:15] d di 
Paul writes:
> What is the version number for the fix?

I don't know.
Perhaps you'll need to reopen this issue to get Peter's attention so he can answer it.

> Is the problem reported actually the bug that was fixed?
Yes!
The issue as reported is fixed in a release candidate of MyODBC 3.51.13.

That RC also has serious heap corruption issues though, and the MyODBC developers has dropped the idea of getting another stable version out of the door, so it seems like this will not be fixed in an actual release before the entirely new connector (Connector/ODBC 5.0) is released.

Note/opinion about mysql_ping():
It would be optimal if mysql_ping() didn't reconnect automatically.  That way we'd have a chance to restore the connection parameters when we reconnect (active database, character sets, whatever) and also keep statistics on how often a reconnect is needed.

For others, the statistics thing does not matter, and automatic reconnect would be nice.  Still, mysql_ping() should not reconnect unless it can restore the connection to the same state that it was in.

Perhaps an option to turn reconnects on/off (if they're fixed) would be good.

(I haven't looked at the code recently, mysql_ping() might already have been changed for the better for all that I know.)
[9 Mar 2007 19:11] Jim Winstead 
Re-opening so this will be re-verified (or closed, I hope) with 3.51.14.
[8 May 2007 12:14] d di 
Wow.  Long time ago.  Let's see if I can remember..

MyODBC 3.51.14:
----------------
X:\>MySqlConnectTest.exe
Opening connection...
Ok.  Kill database or connection now, then press Enter.
[ Note: killed connection here ]

Connection state 'Open' - executing query.
Error: ERROR [HY000] [MySQL][ODBC 3.51 Driver]
       [mysqld-5.0.18-standard-log]MySQL server has gone away

Connection state 'Open' - executing query.
Error: ERROR [HY000] [MySQL][ODBC 3.51 Driver]
       [mysqld-5.0.18-standard-log]MySQL server has gone away

Connection state 'Open' - executing query.
Error: ERROR [HY000] [MySQL][ODBC 3.51 Driver]
       [mysqld-5.0.18-standard-log]MySQL server has gone away

Connection state 'Open' - executing query.
Error: ERROR [HY000] [MySQL][ODBC 3.51 Driver]
       [mysqld-5.0.18-standard-log]MySQL server has gone away

Connection state 'Open' - executing query.
Error: ERROR [HY000] [MySQL][ODBC 3.51 Driver]
       [mysqld-5.0.18-standard-log]MySQL server has gone away

Done - press Enter.
----------------

With 3.51.14, the Connection.State property seems to be malfunctioning, reporting "Open" when the connection is dead.

Here's an excerpt from the .NET documentation:
"Calling the State property on an open connection increases application overhead because each such call causes a SQL_ATTR_CONNECTION_DEAD call to the underlying ODBC driver to determine whether the connection is still valid."

Clearly the connection is no longer valid, thus State should be 'Closed' after the first connection error occurs, not 'Open'.

The bug is still there in 3.51.15 as well..
[13 Jun 2007 22:56] Jared S 
Should be noted that NET Connector does not update connection state until next command is executed.  Then an error is thrown.  I hate to question your bug, but couldn't you trap failed query and then further catch state = closed?
[13 Jun 2007 23:04] d di 
> Should be noted that NET Connector does
> not update connection state until next
> command is executed.  Then an error is thrown.

This bug is about Connector/ODBC, not Connector/NET.
(That said, the above doesn't sound consistent with MSDN docs..)

> couldn't you trap failed query and then
> further catch state = closed?

State is never set to closed.

(But granted I could do all sorts of ugly workarounds,
and I do have one in place, of course ;-).)
[10 Aug 2007 13:56] Bogdan Degtyariov 
In order to make MyODBC reconnect after the server is restarted etc there is an auto-reconnect option in the driver. It can be enabled by either adding OPTION=4194304 to the connection string or through the GUI Dialog for DSNs.

The test project without this option displayed the following results:
--------------------------------------------------------------------
C:\Projects\bugs\14639\bin\Debug>ConsoleApplication1.exe
Opening connection...
Ok.  Kill database or connection now, then press Enter.

Connection state 'Open' - executing query.
Error: ERROR [HY000] [MySQL][ODBC 3.51 Driver][mysqld-5.0.37-community-nt-log]MySQL server has gone away

Connection state 'Open' - executing query.
Error: ERROR [HY000] [MySQL][ODBC 3.51 Driver][mysqld-5.0.37-community-nt-log]MySQL server has gone away

Connection state 'Open' - executing query.
Error: ERROR [HY000] [MySQL][ODBC 3.51 Driver][mysqld-5.0.37-community-nt-log]MySQL server has gone away

Connection state 'Open' - executing query.
Error: ERROR [HY000] [MySQL][ODBC 3.51 Driver][mysqld-5.0.37-community-nt-log]MySQL server has gone away

Connection state 'Open' - executing query.
Error: ERROR [HY000] [MySQL][ODBC 3.51 Driver][mysqld-5.0.37-community-nt-log]MySQL server has gone away

Done - press Enter.

After I modified the connection string by adding the option mentioned above the situation changed:
--------------------------------------------------------------------
C:\Projects\bugs\14639\bin\Debug>ConsoleApplication1.exe
Opening connection...
Ok.  Kill database or connection now, then press Enter.

Connection state 'Open' - executing query.
Connection state 'Open' - executing query.
Connection state 'Open' - executing query.
Connection state 'Open' - executing query.
Connection state 'Open' - executing query.

Done - press Enter.

I would not recommend MyODBC 3.51.18 for testing as there was made an ADO-incompatible change. This has been fixed in 3.51.19, which is now spreading to the mirrors and will be available for downloading very soon. However, the auto reconnection option works well with MyODBC 3.51.17. Can you please test this option and let me know if it resolved the problem?
Thanks.
[10 Aug 2007 14:07] d di 
I'll test it when I get back to the relevant workstation..

Based on the output of your test above, the ConnectionState seems to remain set to 'Open' even though the database is gone and would clearly not respond to a mysql_ping().

That means it is still broken.

(Grep this issue for ".net documentation" to find the old/desired/correct behaviour.)
[14 Aug 2007 13:42] Bogdan Degtyariov 
It turns that MyODBC has to check for CR_SERVER_LOST and CR_SERVER_GONE_ERROR states. Below is the output from the test case I got after changing the condition for dead connections in the driver:
-----------------------------------------------------------
C:\Projects\bugs\14639\bin\Debug>ConsoleApplication1.exe
Opening connection...
Ok.  Kill database or connection now, then press Enter.

Error: The connection is dead.

Connection state 'Closed' - executing query.
Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is Closed.

Connection state 'Closed' - executing query.
Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is Closed.

Connection state 'Closed' - executing query.
Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is Closed.

Connection state 'Closed' - executing query.
Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is Closed.

Done - press Enter.
[14 Aug 2007 13:45] Bogdan Degtyariov 
I forgot to mention that option=4194304 makes mysql_ping to reconnect, so the connection reestablishes automatically and the state remains "Open".
[14 Aug 2007 14:14] d di 
Looks like your fix is working but my example is broken!

This bit OR:

 if ((cmd.Connection.State | ConnectionState.Open) == ConnectionState.Open)

Should have been a bit AND:

 if ((cmd.Connection.State & ConnectionState.Open) == ConnectionState.Open)

Sorry about that.
[14 Aug 2007 14:21] d di 
Regarding option=4194304, good to see that it is an option and not default!

It is useful behaviour for some applications, but for others it would be a fault to automatically reconnect - such as
 - timing critical apps (where the connection establishing time(out) is an issue)
 - apps with connection state (USEd database, temporary tables, ...)

Nice.
[14 Aug 2007 15:47] Bogdan Degtyariov 
Patch and test case

Attachment: bug14639.diff (application/octet-stream, text), 2.16 KiB.
[6 Sep 2007 22:18] Bogdan Degtyariov 
The fix for this bug has been committed, and will be part of MyODBC 3.51.20.
Thanks for the bug report.
[13 Sep 2007 11:36] MC Brown 
A note has been added to the 3.51.20 changelog: 

SQLGetConnectAttr() did not reflect the connection state correctly.
[13 Sep 2007 13:55] d di 
Beautiful, thanks guys!
[18 Dec 2007 19:29] frank urcoh 
biblioteca

Attachment: biblioteca.mdb (application/octet-stream, text), 0 bytes.
[8 Feb 2010 18:52] Ohad Kravchick 
Hi Guys,

I have 3.51.27.00 and I think the bug still exists.
I am receiving "[MySQL][ODBC 3.51 Driver][mysqld-5.1.43-community]MySQL server has gone away" while the Connection state is 1 = Connected.