CODEWITHSUNDEEP
c#mysql.netchecksum
Mark Eastwood
Mark Eastwood

Reputation: 27

How do I perform a checksum of a MySQL table using C#?

I would like to check if my MySQL table has been changed, I thought doing a checksum on it might be a good solution. I'm using the official MySQL .NET connector.

Normally I use the MySqlDataReader to get data from my select query, however I'm not sure how it would work on the checksum query. This is what I have so far.

string query = "checksum table `" + name + "`";

string result = "";

if (isConnected())
{
   MySqlCommand cmd = new MySqlCommand(query, connection);
   cmd.ExecuteNonQuery();
}

I found the checksum query here.

Any help is appreciated!

Upvotes: 1

Views: 376

Answers (1)

Mike Brant
Mike Brant

Reputation: 71384

You have the right query, however you should understand the possible performance implications.

If the table is MyISAM (which it probably should not be if you are using a more recent version of MySQL) and you have the CHECKSUM=1 option defined for the table, then you can do a QUICK checksum, which should happen fast.

If you have a INNODB table, you are going to have to perform a full table scan to execute this command. This is probably not viable from a performance standpoint on an actively used table of reasonably large size.

I would suggest looking at the MySQL documentation for your options here.

http://dev.mysql.com/doc/refman/5.5/en/checksum-table.html

Another perhaps simple option would be to place a trigger on the table which update a timestamp in another table each time a record is inserted/updated/deleted. You can then just compare this timestamp against a given date to know if a change has been made in that time period.

Similarly, if your table doesn't already have an "On UPDATE CURRENT_TIMESTAMP" timestamp field, you can simply add one, place an index on it and query for the max value from that field. This obviously adds some data size and index size overhead to the table which might not be desirable. This obviously also wouldn't capture deletes (though you could easily check this by getting a count on your primary index field)

Upvotes: 3

Related Questions