Kune

It looks like some query is taking too much time and then, pooled connection expires.

There are 3 SQL queries in the algorithm. I am looking into the final SQL executed for every one.

• Query for Method SiteMapGenerator.getGroups()

select group0_.id as id7_, group0_.admissionType as admissio2_7_, group0_.backgroundImage as backgrou3_7_, group0_.backgroundMime as backgrou4_7_, group0_.createdOn as createdOn7_, group0_.defaultContent_id as default14_7_, group0_.defaultLicense_id as default15_7_, group0_.groupType as groupType7_, group0_.logo as logo7_, group0_.logoLastModifiedTime as logoLast8_7_, group0_.mimesubtype as mimesubt9_7_, group0_.mimetype as mimetype7_, group0_.longName as longName7_, group0_.shortName as shortName7_, group0_.socialNetwork_id as socialN16_7_, group0_.workspaceTheme as workspa13_7_ 
from groups group0_ where group0_.groupType<>1 order by group0_.createdOn DESC limit ?;

Executing the query w/o limit from a mySql client, it took 0.265 sec / 77.555 sec (execute / fetch)

• Query 2

select container0_.id as id1_, container0_.accessLists_id as accessLi7_1_, container0_.createdOn as createdOn1_, container0_.deletedOn as deletedOn1_, container0_.language_id as language8_1_, container0_.name as name1_, container0_.owner_id as owner9_1_, container0_.parent_id as parent10_1_, container0_.toolName as toolName1_, container0_.typeId as typeId1_ from containers container0_ where $GROUP_ID in (select group6_.id from groups group1_ inner join social_networks socialnetw2_ on group1_.socialNetwork_id=socialnetw2_.id inner join access_lists accesslist3_ on socialnetw2_.accessLists_id=accesslist3_.id inner join group_list grouplist4_ on accesslist3_.editors_id=grouplist4_.id, group_list_groups list5_, groups group6_ where container0_.owner_id=group1_.id and grouplist4_.id=list5_.group_list_id and list5_.list_id=group6_.id) or $GROUP_ID in (select group12_.id from groups group7_ inner join social_networks socialnetw8_ on group7_.socialNetwork_id=socialnetw8_.id inner join access_lists accesslist9_ on socialnetw8_.accessLists_id=accesslist9_.id inner join group_list grouplist10_ on accesslist9_.admins_id=grouplist10_.id, group_list_groups list11_, groups group12_ where container0_.owner_id=group7_.id and grouplist10_.id=list11_.group_list_id and list11_.list_id=group12_.id);

• Query 3

select content0_.id as id3_, content0_.accessLists_id as accessL13_3_, content0_.container_id as container14_3_, content0_.createdOn as createdOn3_, content0_.deletedOn as deletedOn3_, content0_.filename as filename3_, content0_.language_id as language15_3_, content0_.lastRevision_id as lastRev16_3_, content0_.license_id as license17_3_, content0_.mimesubtype as mimesubt5_3_, content0_.mimetype as mimetype3_, content0_.modifiedOn as modifiedOn3_, content0_.publishedOn as publishe8_3_, content0_.status as status3_, content0_.typeId as typeId3_, content0_.version as version3_, content0_.waveId as waveId3_ from contents content0_ where $GROUP_ID in (select group7_.id from containers container1_ inner join groups group2_ on container1_.owner_id=group2_.id inner join social_networks socialnetw3_ on group2_.socialNetwork_id=socialnetw3_.id inner join access_lists accesslist4_ on socialnetw3_.accessLists_id=accesslist4_.id inner join group_list grouplist5_ on accesslist4_.editors_id=grouplist5_.id, group_list_groups list6_, groups group7_ where content0_.container_id=container1_.id and grouplist5_.id=list6_.group_list_id and list6_.list_id=group7_.id) or $GROUP_ID in (select group14_.id from containers container8_ inner join groups group9_ on container8_.owner_id=group9_.id inner join social_networks socialnetw10_ on group9_.socialNetwork_id=socialnetw10_.id inner join access_lists accesslist11_ on socialnetw10_.accessLists_id=accesslist11_.id inner join group_list grouplist12_ on accesslist11_.admins_id=grouplist12_.id, group_list_groups list13_, groups group14_ where content0_.container_id=container8_.id and grouplist12_.id=list13_.group_list_id and list13_.list_id=group14_.id) order by content0_.modifiedOn DESC;

Initially it doens't look a long-last query problem. I've executed separately those queries for a sample of data input (groups) and they didn't take more than a couple of seconds.
We need to analyze Java code.

Something weird is that the exception is in the first (SiteMapGenerator.java:160).

So problem comes from a SELECT COUNT... (SiteMapGenerator.countNotClosedGroups(SiteMapGenerator.java:160))
We could start thinking on hibernate-pool configuration problem.

After today's comment, we could think that there is any problem with these queries. Let's suppose that system's performance cause the DB time out. We should make more tests, increasing the time out value for the c3p0 pool.