I'm running a Windows service, which connects to a MySql database via Entity Framework.
Every 10 seconds it issues a query to the database to see if any new records have been added. If it finds any, it attempts to process them.
Mostly, this works fine. However, if no data is added over an extended period when a row does get added the database throws an error back at me:
MySql.Data.MySqlClient.MySqlException (0x80004005): Fatal error encountered during command execution.
MySql.Data.MySqlClient.MySqlException (0x80004005): Fatal error encountered attempting to read the resultset.
MySql.Data.MySqlClient.MySqlException (0x80004005): Reading from the stream has failed.
System.IO.IOException: Unable to read data from the transport connection: An established connection was aborted by the software in your host machine.
System.Net.Sockets.SocketException: An established connection was aborted by the software in your host machine
After a lot of experimentation, I have discovered this is controlled by the
wait_timeout setting on the database. If I reduce this to a few seconds, I can easily replicate the error.
Solving it, however, is a different matter. I could increase
wait_timeout but this seems like a bad fix for all sorts of reasons. Not only is it generally seen as a good idea to have this set low, but it's not really a solution at all - any idle period that exceeds the new value will still trigger the error.
I'm struggling to understand why I'm getting this error at all, since I'm issuing queries to the database every five seconds. I've tried forcing the code to open a new connection every time, but it still throws the error when
wait_timeout is exceeded. I just can't see how that can be happening when I'm fetching a fresh connection.
I've also tried setting keepalive on the connection string, and setting a min/max on the thread pool to 10/20. Neither fixes the issue.
Oddly the only thing which does "fix" the issue is to record a log (it's saved in the same db) on every pass through the service. Again, this isn't a suitable fix because it will fill the log with garbage in no time at all. But it just increases my confusion: the logging framework uses a different connection - why does issuing a log command keep the EF connection alive?
There are threads and questions about this all over the place, most of which just suggest increasing
wait_timeout. What's going on here, and how do I fix it?