I'm running a Java application that creates 50 threads as part of a threadpool; each thread running in an object that reads 1000 unique rows at a time from a MySQL table. So each object has it's own SqlManager class I created which can open an Sql connection.
wait_timeout is 28800, which would be 8 hours (am surprised it is that long), so I assume that a connection once established, won't expire until 8 hours are over. My program though, runs for more than 8 hours, and I've increased the max_connections variable to 5000 too.
Currently I've programmed each thread object to check if 2 hours have elapsed, and if it has, then close the MySQL connection and re-open the connection.
Is this an appropriate technique of dealing with the problem, or does the connection expire only if it hasn't been used for any query for a long time? My program constantly queries the database all through those hours.
Best Answer
If the problem is that your query functions/etc stop working as you've disconnected, write your own database wrapper and have the query functions error handler check to see if the database is disconnected, if it is, attempt to reconnect and put the query in an array with others to be executed on database connection success.
If you're lazy, just have it SELECT every hour or so and it shouldn't time out.