MySQL Bugs: #23825: wait_timeout error throwing

Bug #23825	wait_timeout error throwing
Submitted:	1 Nov 2006 0:37	Modified:	12 Dec 2006 21:40
Reporter:	Jared S (Silver Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	1.0.7, 1.0.8 and 5.0 SVN	OS:	Windows (WinXP)
Assigned to:		CPU Architecture:	Any
Tags:	Connection, established, host machine, wait_timeout

Description:
Hi,

I am a software developer being continuely fronted with the problem "Your software bombed out overnight"

I have change wait_timeout to 15 seconds in my.ini to try to replicate issue. Note MySQL Administrator is a joke for changing settings.  

When I replicated issue I found that my connection_statechange event actually correctly handles destruction and recreation of connection object.  BUT the next command to get called in my code, which is normally data reader, thows an error ALTHOUGH it was fired with new open connection and moreover actually returns results.

But unfortunaley this get caught in my error trapping as "An established connection was aborted by the software in your host machine"

How to repeat:
> edit my.ini 
> wait_timeout=30
> save & restart service
> add the following code to event

Private Sub CON0_StateChange(ByVal sender As Object, ByVal e As System.Data.StateChangeEventArgs) Handles CON0.StateChange

        If CON0.State <> Data.ConnectionState.Broken And CON0.State <> Data.ConnectionState.Closed Then Exit Sub

    mysql_disconnect() 'you will need to replace with you connection stuff
    mysql_connect()

end sub

> setup form with 2 buttons
  1. button for open connection
  2. button for running data reader query of some sort
> wait 15 seconds

> start project
> Connect
> wait 15
> attempt data reader and wath error thrown

Suggested fix:
Stop throwing the error.  It is old message and not applicable to new connection

I see it has been fixed in 1.0.8.  I guesss there is no need for you to chase your tail on this issue then.

Disregard my last comment.  It has not been fixed.

Hi Jared and thanks for your problem report. 
I would like you to attach VS project demonstrating this error. I would also like to know NET fw version you're using.

Hi Tonci,

To answer your questions I am using VS2005/NET 2.0.  I have attached a project that cleary shows the error after I have re-established a broken connection.

Hope my code and comments aren't to much for you.  I have installed the source code for .NET connector but VS would not let me debug it because of symbolic information missing. Do you know how to get around that?

Thanks heaps!
Jared Sullivan

Hi Jared. There is public RO source repository at http://svn.mysql.com/svnpublic/connector-net/
 - In *branches* you have latest 1.0 sources
 - In *tags* you have released versions
 - In *trunk* you have latest 5.0 sources
You can use TortoiseSVN, or any other tool, to get latest sources and build them yourself.

I will check on your test case as soon as possible.

Hi Jared and thanks for excellent test case. I wish I get one like this every time.

Verified as described by reporter with test case provided.

SMALL BUG IN MY EXAMPLE:

I did not realize that MySQL Session variables are different to MySQL Global variables.  And as result, my project may show different value for wait_timeout...no big issue, but I could re post if needed.

Jared, I saw that but it has no bearing on results. Thanks for excellent test case and your interest in MySQL.

I would like to change priority to S1, since NET connector is vital part to my solution.

Jared, fine by me.

Can someone explain how this test case should work?

I wrote the following test case and tested it with 5.0.2 and 5.0 SVN and it works.  One thing that should be noticed is that the state change event handler is run on a worker thread and not on the same thread that the original connectino was created on.  

        int stateChangeCount;
        [Test]
        public void WaitTimeoutExpiring()
        {
            MySqlConnection c = new MySqlConnection(GetConnectionString(true));
            c.Open();
            c.StateChange += new StateChangeEventHandler(c_StateChange);

            // set the session wait timeout on this new connection
            MySqlCommand cmd = new MySqlCommand("SET SESSION interactive_timeout=10", c);
            cmd.ExecuteNonQuery();
            cmd.CommandText = "SET SESSION wait_timeout=10";
            cmd.ExecuteNonQuery();

            stateChangeCount = 0;
            // now wait 10 seconds
            System.Threading.Thread.Sleep(15000);

            try
            {
                cmd.CommandText = "SELECT now()";
                object date = cmd.ExecuteScalar();
            }
            catch (Exception) { }
            Assert.AreEqual(1, stateChangeCount);
            Assert.AreEqual(ConnectionState.Closed, c.State);

            c = new MySqlConnection(GetConnectionString(true));
            c.Open();
            cmd = new MySqlCommand("SELECT now() as thetime, database() as db", c);
            using (MySqlDataReader r = cmd.ExecuteReader())
            {
                Assert.IsTrue(r.Read());
            }
        }

        void c_StateChange(object sender, StateChangeEventArgs e)
        {
            stateChangeCount++;
        }

The report Bug#24928 has been set to duplicate of this one.

OK, heres how it works.  Set session wait_timeout, Wait..., Run scalar, Error out on CMD0.ExecuteScalar, Automatically reconnect from CON0_StateChange event,

But the thing is, I was expecting CMD error to get cleared on new Connection, but since I now see that they are different objects, I think this may not be a bug.

Can I ask you a question.  How would you handle reconnection every 8 hours?

Let me have think about this, I am not ready to drop this issue a not a bug yet. 

Private Sub CON0_StateChange(ByVal sender As Object, ByVal e As System.Data.StateChangeEventArgs) Handles CON0.StateChange

        'ignore 4
        If CON0.State = Data.ConnectionState.Fetching Then Exit Sub
        If CON0.State = Data.ConnectionState.Executing Then Exit Sub
        If CON0.State = Data.ConnectionState.Connecting Then Exit Sub
        If CON0.State = Data.ConnectionState.Open Then Exit Sub

    Trace.WriteLine("Now automatically reconnecting from state change event..")
    mysql_disconnect() 'close connection
    mysql_connect(True) 'reopen connection  

        Try
        CMD0 = New MySql.Data.MySqlClient.MySqlCommand("select 1;", CON0)
        Trace.WriteLine(CMD0.ExecuteScalar.ToString)
        Catch ex As Exception
        Trace.WriteLine(ex.Message.ToString)
        Trace.WriteLine(ex.Message)
        Trace.WriteLine(ex.InnerException.Message)
        End Try

        If CON0.State = Data.ConnectionState.Closed Then
        End
        End If

End Sub

Ok, I have had a think about it, and I can capture connection loss through my SQL routine like so...

            If CON0.State <> ConnectionState.Open Then
           mysql_connect(True) 'reopen connection  
            mysql_hub(xType, xCall)
            Exit Sub
            End If

I have tested this and am happy with this.  Although I thought it would of been nice of mysql.data.dll to detect wait_timeout loss and throw event instead of waiting until next data query.

I will leave bug open - waiting for your feedback.
Jared Sullivan

1. The following code is the code to fix the wait_timeout bug. but the performance is very slow.

//declare following variables in global or class level
		MySqlConnection conn = null;
		string connectionString = "server=servername;database=databasename;user id=username;Password=pwd ";
		//Every time to query database will call following function to test database connection
		private bool TestConnectionToDatabase()
		{
			bool isConnecting=false;
		
			if (conn==null || conn.State==ConnectionState.Closed || conn.State==ConnectionState.Broken )
			{
				conn = new MySqlConnection(connectionString);
				conn.Open(); 
				isConnecting= true;			
			}
			else
			{
				//we will test database connection is active, create new connection if it is not active 
				if (conn.State==ConnectionState.Open)
				{
					try
					{
						MySqlCommand testCmd=conn.CreateCommand();
						if (testCmd==null)
							isConnecting= false;
						else
						{
							//you can use any simple SQL query to test connection
							testCmd.CommandText="SELECT Count(*) FROM INFORMATION_SCHEMA.TABLES " +
								"WHERE TABLE_TYPE = 'BASE TABLE' ";
							testCmd.ExecuteScalar();
							isConnecting= true;
						}
					}
					catch (Exception ex) //if the database connection is lost, reopen it
					{
						conn.Close();
						conn.Open();
						isConnecting= true;
					}
				}
				else
					isConnecting= true;
			}
		
			return isConnecting;
		}

2. I have found there is the property ConnectionTimeout (default is 15 mins) for MySqlConnection. What difference is it from wait_timeout? Does it affect
connection? That means connection will break after ConnectionTimeout is expired.

Please email to me if you have better solution for wait_timeout error.

Thanks.

Closed bug because have sufficient code to trap error at command execute level.