search

compbio-UofT / medsavant

MedSavant is a search engine for genetic variants
22 stars 9 forks source link

MySQL connection can timeout. #307

Open jvlasblom opened 10 years ago

jvlasblom commented 10 years ago

If a long time elapses between querying MySQL, the connection can timeout.
The connection should always be checked to see if it's lived prior to issuing any queries. Passing autoReconnect=true in the database connection string may fix it, but is supposedly not recommended: http://pages.citebite.com/p4x3a0r8pmhm

As a temporary measure, I've increased the wait_timeout from the default 8 hours to 1 week.

jvlasblom commented 10 years ago

(wait_timeout is a variable configurable in /etc/mysql-ib.cnf)

jlouback commented 10 years ago

I did a bit of research, found a solution in http://lists.mysql.com/java/8119 The gist of it is: a) - increase wait_timeout

b) Modify the following config variables: validationQuery (set to 'SELECT 1') >> or any non null string apparently testWhileIdle (set to 'true') timeBetweenEvictionRunsMillis (some positive integer) minEvictableIdleTimeMillis (set to something smaller than 'wait_timeout'). and if you don't mind a hit for every getConnection(): testOnBorrow (set to 'true')

From what I've seen, it's the only alternative as autoReconnect=true has issues with stale connections.