glassfish_at_javadesktop.org wrote:
> 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.
It's a TopLink/EclipseLink way of handling queries that happen before any
updates - it'd use a nontransactional connection (no matter what your setting is).
Do your native queries do any updates?
There might be a hint that you can use to force the use of the transactional
connections. Non-transactional connections need to be explicitly
committed/rolledback by the caller.
Regards,
-marina
> 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
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe_at_glassfish.dev.java.net
> For additional commands, e-mail: users-help_at_glassfish.dev.java.net
>