I tracked this error to native queries and found that timeouts didn't occur when I didn't use them. Of course the nonsense in my legacy schema requires me to use them, so I came up with a work around.
The problem I was having worked like this:
1. Glassfish opens a pool of connections to the mysql server and uses them
2. Some of those connections get used to perform native queries and (*I think*) the native query issues a START TRANSACTION without issuing a COMMIT. *I think* this screws up the connection and prevents it from being closed and re-opened when the failure happens.
2. Overnight or the weekend happens and wait_timeout on the mysql server expires, most connections handle this without error, but the one(s) that used the native query doesn't and you get the error.
So to fix this you could try to force a commit on the native query - tried adding @TransactionAttribute(TransactionAttributeType.REQUIRED) to methods using native queries, but it didn't work... maybe em.createNativeQuery("COMMIT").executeUpdate(); would work, I didn't think of it till just now.
You could write a thingy that hit the db to prevent the timeout, but I'm not sure how to write it such that it's guaranteed to hit all the connections in the pool.
Or you could perpetrate the nasty hack I used and set wait_timeout to longer than the system will ever go unused plus some to ensure all the connections in the pool get hit. My app rarely gets used on the weekend so I made it 72 hours and haven't had a problem since.
Hope this helps.
-
Loren
[Message sent by forum member 'loren_' (loren_)]
http://forums.java.net/jive/thread.jspa?messageID=351264