Bug #26393 Long idle times result NullreferenceException upon Connection.Open
Submitted: 15 Feb 2007 8:13 Modified: 9 Apr 2007 17:52
Reporter: Stephan Steiner Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S1 (Critical)
Version:5.0.3 OS:Microsoft Windows (Windows 2003 Server SP1)
Assigned to: CPU Architecture:Any

[15 Feb 2007 8:13] Stephan Steiner
Description:
I moved from MySQLConnectorCS to Connector.NET. I adapted my application which runs on two machines which host a MySQL server 5.0.22 and 5.0.27 respectively (both community editions).
The application is a service which thus runs 24/7. After having been idle overnight, the application is no longer able to connect to the database - the call to connect.Open returns a NullReferenceException with the following details:

System.NullReferenceException: Object reference not set to an instance of an object. at MySql.Data.MySqlClient.NativeDriver.Configure(MySqlConnection connection) at MySql.Data.MySqlClient.MySqlConnection.Open() at Database.GetData()

If the application makes regular connections in intervals between a few seconds and a few minutes (the normal profile during the day), everything works out okay, but having to restart the service each time the application has gone unused for a few hours is a major pain. 

How to repeat:
Compile and run this program.. it should abort at the second connection attempt with the above described exception.

using System;
using System.Collections.Generic;
using System.Text;
using System.Threading;
using MySql.Data.MySqlClient;

namespace MySQLBug
{
    class Program
    {

        static string hostname = "localhost", dbname = "test", login = "root", password = "mypass";
        static int hoursIdle = 10;
        static MySqlConnection conn;

        static void Main(string[] args)
        {
            while (connect(hostname, dbname, login, password))
            {
                disconnect();
                Thread.Sleep(new TimeSpan(hoursIdle, 0, 0));
            }
        }
        public static bool connect(string hostname, string dbname, string login, string password)
        {
            try
            {
                string connectionString = "server=" + hostname + ";uid=" + login + ";pwd=" + password + ";database=" + dbname;
                conn = new MySqlConnection(connectionString);
                conn.Open();
                if (conn.State == System.Data.ConnectionState.Open)
                    return true;
                else
                {
                    Console.WriteLine("DatabaseConnector.connect tried connecting. Connection state after attempt: " + conn.State.ToString(), 2);
                    disconnect();
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("DatabaseConnector.connect threw exception: " + e.Message + " stacktrace: " + e.StackTrace, 1);
                disconnect();
            }
            return false;
        }
        public static void disconnect()
        {
            try
            {
                if (conn != null)
                    conn.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine("DatabaseConnector.disconnect threw exception: " + e.Message, 1);
            }
        }
    }
}

Suggested fix:
No ideas yet - I'll try to create a debug build of the lib, run the service on my machine and see if I can step through the connector to see what's going wrong.
[8 Apr 2007 17:18] Tonci Grgin
Hi Stephan and thanks for your report. Sorry for the delay.

I tested your code against:
 - MySQL 5.0.38BK on WinXP Pro SP2 localhost
 - c/NET 5.0.6GA, NET FW 2.0
 - Server wait timeout is set to 180 (3 min)
 - Test case changed from waiting 10 hours to waiting 5 minutes

No problems found.

C:\mysql507\bin\mysqld-max-nt, Version: 5.0.38-log (Source distribution). started with:
TCP Port: 3306, Named Pipe: mypipe1
Time                 Id Command    Argument
070408 19:04:55	      1 Connect     root@localhost on test
		      1 Query       SHOW VARIABLES
		      1 Query       SHOW COLLATION
		      1 Query       SET character_set_results=NULL
		      1 Init DB     test
070408 19:09:55	      2 Connect     root@localhost on test
		      2 Query       SHOW VARIABLES
		      2 Query       SHOW COLLATION
		      2 Query       SET character_set_results=NULL
		      2 Init DB     test
070408 19:14:58	      3 Connect     root@localhost on test
		      3 Query       SHOW VARIABLES
		      3 Query       SHOW COLLATION
		      3 Query       SET character_set_results=NULL
		      3 Init DB     test

Can you please test with newer version and reopen the report if it fails?
[9 Apr 2007 17:26] Stephan Steiner
Since the program I'm using the connector in is a massive piece of code, I wrote the little sample based on how I think the bug could be reproduced, but without actually trying it (since it needs to run for a long time until the problem happens). My software also runs as a service, not an exe, which may or may not have an impact.
Either way, a few weeks ago I upgraded to the 5.0.5 connector and that took care of the problem (as far as I can tell from forum posting it also did the trick for other people having the same problem, but in a webserver environment). Thus, my suggestion would be to close this bug with a note that if you experience this particular behavior, you should upgrade your connector to the 5.0.5 release and see where you're at.
[9 Apr 2007 17:29] Stephan Steiner
Umm... I should really read all the comments till the end. Your change of the test scenario to a few minutes waiting is what makes the sample fail.. it needs to be idle for many hours or the problem will not occur. In my service application, the problem occurred the morning after.. people go home at 6pm so the service is idle until 8am the next morning.. and that's where problems appear.. the first user to attempt to log in will be refused as the service can no longer connect to the database..
Still, the issue has gone away since I upgraded to 5.0.5 and I've had the service running in this particular configuration since March 15th on a handful of separate machines and have yet to experience any database connectivity issues.
[9 Apr 2007 17:52] Tonci Grgin
Thank you for your bug report. This issue has already been fixed in the latest released version of that product (5.0.5 and up), which you can download at

  http://www.mysql.com/downloads/

Explanation: Stephan, I agree that we should close this as the problem has gone away.

> In my service application, the problem occurred the morning after.. people go home at 6pm so the service is idle until 8am the next morning.. and that's where problems appear.. 

I think it's all about wait_timeout. It is set to 8 hours by default so by changing *both* my wait_timeout server variable and thread time suspended I think I didn't change the nature of test case.