mysql class - how to check connectivity?

[Red Squirrel]

Right now, if the SQL server goes down, I just get SQL errors. I could parse out the text to figure out the connection is lost and go from there, but is there a better way to determine if the connection is no longer available?

[mshdsp]

Your SQL connection object should have a connection state property. You could test for the state prior to your program communicating with the SQL server. Alternatively, you could catch the specific exception that's thrown when the SQL connection is severed.

[Arjay]

Are you leaving the Sql connection object open for any length of time (instead of opening it, doing work, and immediately closing it)?

[Red Squirrel]

I leave it open at all times though this app is pretty much constantly doing update queries to keep up with the changes in the game world. Every 1-2 seconds a tally of all modified objects is done then a series of queries are performed. What I want to do is detect loss of connectivity, throw some kind of warning in game or email sent to me, then write the queries to file. As soon as the server comes back it will perform the queries in the file, in right order, then catch up.

Right now I'm catching the query error exception which could be anything such as a syntax error, but that's also where I would get the connection error, so it's where I want to check connectivity.

[vcdebugger]

try to create a timer function where you can write the code to check the connectivity every some fixed duration depending on your requirement ..

[Arjay]

You may try to just open the connection each time you wish to write. With the pooling and caching mechanisms of todays db providers, you may find that there isn't much difference on performance (as compared to always leaving the connection open). You also may find that by holding the connection open, you are getting a 'stale' connection. Try it the other way to find out for sure.

Can you tell me a bit about the layout of your app? Is it one or more C# clients connecting to a MySql db? Is the db local or on the network? I might be able to suggest a caching mechanism.

[Red Squirrel]

Basically this app was not designed to be DB driven, so I've sorta went through hoops and managed to get it going. The app is a game server so the world is dynamic and constantly changing. Creatures move, items change positions, people weild different items etc. Every time an item is changed it is "Flagged" and goes into an array. A flag is set on whether the item was added, modified, or deleted (so I use the right type of query). Then a loop runs through this array and generates queries which are put into another array, then the game resumes. This process takes maybe 0.1 secs so it is not noticable.

There is then a thread that submits all the queries one by one. This cycle repeats about once a second. In most cases there is only 1-2 items that changed but it can jump to 100's very fast if a player walks near a group of creatures (creatures only move if someone is near or has been near for a set time)

So I'd be opening and closing the connection quite often and it would still not work for if the connection really is down. (ex: stopping the service.)

I will look into if I can catch the lost connection exception directly and see if that works out, for the query process.

I will experiment with open/close connection either way though, as I could still catch connectivity issues there.

[Red Squirrel]

I've been googling for a mysql connection state parameter of some sort but no go. There is one for sql but not for mysql.

Anyone happen to know exactly how I can do this?

This is what I'm trying to do:


public bool Query(string query)
{
if(m_con.State != m_con.ConnectionState.Open)
{
ThrowError("query...","Seem to have lost connection, will try to reconnect...");

try
{
m_con.Open();
}
catch
{
ThrowError(query,"FATAL:Unable to connect to DB");
return false;
}
}

try
{
if(m_Reader!=null) m_Reader.Close();

MySqlCommand command = m_con.CreateCommand();
command.CommandTimeout = 0;
command.CommandText = query;

m_Reader = command.ExecuteReader();
}
catch (Exception ex)
{


ThrowError(query,ex.ToString());
return false;
}

Debugger.Write(String.Format("mysql{0}",m_conid),"Query:{0}",query);

return true;
}



That code does not compile as ConnectionState does not exist, is there something else I can check against?

[Arjay]

Have you tried reading the MySqlConnection documentation?

http://dev.mysql.com/doc/refman/5.0/es/connector-net-examples-mysqlconnection.html#connector-net-examples-mysqlconnection-state

[sotoasty]

This seems to work for me.

private bool CheckState()
{
if (m_con != null)
{
if (m_con.State != System.Data.ConnectionState.Closed)
{
return true;
}
else
{
return false;
}
}
else
{
return false;
}
}