List:MySQL ODBC« Previous MessageNext Message »
From:ddi Date:November 4 2005 11:48am
Subject:Regression in MyODBC 3.51.12
View as plain text  
Problem:
==========
There's a regression in MyODBC 3.51.12 vs. 3.51.10.


Details:
==========
Using MyODBC with .Net, the OdbcConnection.ConnectionState property no longer correctly
reflects the connection's state.
(It seems to be a side effect of the removal of auto-reconnection logic in MyODBC.)


How to reproduce:
===================

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.
ConnectionState 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.
ConnectionState is (incorrectly) still == ConnectionState.Open.


Workaround:
=============
We've implemented our own ConnectionState mechanism instead of using MyODBC's.


Proposed solution:
====================
I'm imagining that ConnectionState should say "Closed" when the connection has died behind
our back.
 
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 assumption is not valid (which IMHO renders ConnectionState somewhat useless), the
SQL_ATTR_CONNECTION_DEAD behaviour should at least be documented to be "weird".
Thread
Regression in MyODBC 3.51.12ddi4 Nov