24.4.5.3.4: I have a servlet/application that works fine for a day, and then stops working overnight
MySQL closes connections after 8 hours of inactivity. You either need to use a connection pool that handles stale connections or use the "autoReconnect" parameter (see Section 23.4.4.1, "Driver/Datasource Class Names, URL Syntax and Configuration Properties for Connector/J").
Also, you should be catching SQLExceptions in your application and dealing with them, rather than propagating them all the way until your application exits, this is just good programming practice. MySQL Connector/J will set the SQLState (see java.sql.SQLException.getSQLState() in your APIDOCS) to "08S01" when it encounters network-connectivity issues during the processing of a query. Your application code should then attempt to re-connect to MySQL at this point.
Note. Use of the autoReconnect option is not recommended because there is no safe method of reconnecting to the MySQL server without risking some corruption of the connection state or database state information. Instead, you should use a connection pool which will enable your application to connect to the MySQL server using an available connection from the pool. The autoReconnect facility is deprecated, and may be removed in a future release. What this means to you as an OFBizer using MySQL 5.0+:
I'm not an expert on setting up connection pools or whether OFBiz already can handle this. All I know is that OFBiz isn't set up OOTB to do this with the MySQL settings. So, what can one do? Well, for starters there is the peculiar statement:
MySQL closes connections after 8 hours of inactivity.
Since you can schedule services to run at particular times and repeat until infinity and so on, why not just not allow 8 hours of inactivity to occur. For my deployment, I've done the following:
Created a service to call it and then scheduled that service to run every 3.5 hours (Just in case there was a problem with the first call).
Note: it has been brought to my attention that this may not be sufficient for slightly busier but not quite busy enough deployments. A better solution is being looked into.
A better solution would be to adjust wait_timeout parameter in my.cnf. By default it is set to 28800 (which seems to be exactly 8h).
change or add the following line to your my.cnf and set it to a really large number:
wait_timeout=864000
