Is there a simple way in my ASP.NET app to programmatically find out whether the mysql db server is down?

Question

I use MySQL in my ASP.NET app (with the MySQL .NET Connector), is there anyway to programmatically check whether the db server is down? I came across MySQLConnection.Ping() but unfortunately that doesn't work the way it should (the connection has to be opened first using Open(), also it keeps on returning false even if the DB Server becomes available again). The only solution I came up with is to use the Open() method, if that throws an exception then the DB server is not available, is there a more reliable way to do this? I read somewhere that using Open() might not work as expected if connection pooling is used, this could result an exception being thrown even if the DB server is up.

If you want the details please read on:

In my app, I log all exceptions (first I try to log them to the DB Server - could be the same DB Server that's down but could be not, depending on the configuration - if that fails the exceptions are logged to a text file). Now I want to handle the case if the DB server is down, normally what will happen is that exceptions will keep on being thrown with almost every request to my app, this means the log will be full with those exceptions, the other problem is that I'll keep on serving error pages to users (or in other words the users will keep on seeing error pages with every request they make to the app), what I want to do is that if a db exception is thrown (ie. of type MySQLException) to check whether the DB server is up, if not then change some flag in my app and serve a page that contains something like the application is temporarily unavailable (the flag is checked in an httpModule and redirects or transfers to that page). I'm also planning to make it send me an SMS using an SMS proxy so that I could take an action, but this another story and is not related to this question.

Answer 1

I like this solution.. http://forums.asp.net/p/1066040/1542583.aspx

My solution is to have two connections. One with a very short timeout which I use for testing, and the other normal. I first make a dummy call to the short time out one. if that fails, I display something that is not from the database, otherwise, I proceed normally. that way the user doesn't see an error page.

The trick would be to make sure that timeout is in the "happy" range. A few seconds should do in a decent environment.

Answer 2

A solution that I've seen work for something like this (technically we were connecting to a SQL Server database in C#, but having used the MySQL connector briefly, I believe they work mostly the same) what we had was a single location where the connection was created and opened. In that one method, we wrap the SQLException (MySQLException for you) in a special "hey the database is borked" exception. Then in the exception handler/logger, you can just check for your special exception and and do whatever you need to do when the database is down.

The idea here is to not have to write a bunch of extra code that checks to make sure the database is there and working correctly before you make every call, but to ensure that when something bad happens you know that the database is the culprit and can handle that appropriately.

Answer 3

try {
    cmd.ExecuteNonQuery();
    // server is up
}
catch { 
    // server is down
}

Answer 4

Maybe you could try to connect with a raw TcpClient, ie. just attempt a connection and disconnect. Although this doesn't solve your problem with throwing exceptions.

try{
 TcpClient _socket = new TcpClient();
 _socket.Connect(host, port);
 _socket.Close();
}
catch (System.Net.Sockets.SocketException ex)
{
    if (ex.SocketErrorCode == System.Net.Sockets.SocketError.ConnectionRefused //Process down
        || ex.SocketErrorCode == System.Net.Sockets.SocketError.HostDown //Server turned off
        || ex.SocketErrorCode == System.Net.Sockets.SocketError.TimedOut //Not responding
       )
    {
      //=> offline mode
    }
}