Issue 16309

Geocode: "Error configuring autocommit"

16309
Reporter: trobertson
Assignee: omeyn
Type: Bug
Summary: Geocode: "Error configuring autocommit"
Priority: Blocker
Resolution: Fixed
Status: Closed
Created: 2014-09-10 21:15:45.736
Updated: 2014-09-19 11:03:58.323
Resolved: 2014-09-19 11:03:58.273
        
Description: Geocode occasionally returns the error shown in the attached screenshot.

The geocode service is running in a multi-threaded environment (e.g. tomcat) and has a BoneCP database pool.  TR believes this happens when a stale connection is called from the BoneCP pool for some reason.  See http://forums.activiti.org/content/mysql-exception for a similar example (MySQL though).  We should read around this and understand if the proposed fix is portable to BoneCP - e.g.:
  http://forum.jolbox.com/viewtopic.php?f=3&t=304&p=1305&hilit=idlemaxage%20maxconnectionage#p1305

This is taking out tasks on the analytics processing, and it has also been reported by Pensoft devs.  We have survived mostly due to implementing retry mechanisms in code, and relying on Hadoop retrying tasks.

CC'ing for info on this important issue [~omeyn] [~mdoering] [~fmendez]]]>
    

Attachment Screenshot 2014-09-10 20.59.04.png



Author: trobertson@gbif.org
Created: 2014-09-10 21:47:55.752
Updated: 2014-09-10 21:56:44.784
        
I propose we add:

{code}
  bindConstant().annotatedWith(Names.named("bonecp.maxConnectionAgeInSeconds")).to(120); // 2 mins
{code}

To this configuration:
  https://github.com/gbif/geocode/blob/master/geocode-ws/src/main/java/org/gbif/geocode/ws/guice/GuiceConfig.java#L93

This will be picked up the MyBatis BoneCPProvider here:
https://github.com/mybatis/guice/blob/master/src/main/java/org/mybatis/guice/datasource/bonecp/BoneCPProvider.java#L140

Note, this parameter defaults to 0 which is presumably never, and why I *believe* we see this issue:
https://github.com/wwadge/bonecp/blob/master/bonecp/src/main/java/com/jolbox/bonecp/BoneCPConfig.java#L131

What say others?

[~mdoering@gbif.org] I suspect this can affect CLB as well.  Because that extends common GBIF guice stuff, you can add it simply in the properties file, as all properties get bound in this line:
https://github.com/gbif/gbif-common-service/blob/master/src/main/java/org/gbif/service/guice/PrivateServiceModule.java#L79
So I think it can just be added on:
https://github.com/gbif/checklistbank/blob/master/checklistbank-ws/src/main/resources/checklistbank.properties
    


Author: omeyn@gbif.org
Comment: I've done some reading on this and agree with [~trobertson@gbif.org] - at least for the geo lookup proposal. I'll try this config and do a hard lookup (analytics) and see how it behaves.
Created: 2014-09-11 10:39:09.812
Updated: 2014-09-11 10:39:09.812


Author: mdoering@gbif.org
Comment: Ive added the maxAge property to the clb webservice config properties and also added it to the new, external CLI yaml configuration. Both default to 120 seconds: https://github.com/gbif/checklistbank/commit/64346dc14d95683e0e490af4f4d9cd58ddbcdb35
Created: 2014-09-11 11:10:13.749
Updated: 2014-09-11 11:10:13.749


Author: omeyn@gbif.org
Comment: I've committed this to geocoder as well, and initial lookup tests show significant improvement.
Created: 2014-09-19 11:03:58.32
Updated: 2014-09-19 11:03:58.32