Over the weekend I took a stab to change the mifos' hibernate config
to use c3p0 connection pool (and turn on c3p0's debugging tools). Here
is the finding so far:
1. a casual simple test of the mifos webapp (simple clicking around
and creation some office, etc.) shows that the config works
functionally (not necessarily in all cases)
2. I ran the unit test suite using the c3p0 config. It resulted in
fair amount of errors, many of which seem to be due to some cascading
effect. The first one that fails shows some weird persistence error
that some SQL update fails, indicating that somehow some earlier SQL
operation was not committed. At this point, it is not clear to me
whether these errors indicate some existing issues on mifos code (that
got masked previously), or it might be due to bugs in the hibernate /
c3p0 we're using.
I suppose we could pursue some of the followings:
1. have an experimental c3p0 config run on a test server and put some
load. See if we could reproduce the problem, this time with stacktrace
on the problem.
2.someone look at the unit test failures using c3p0 conifg (attached
the first two here) to see if they could find any hint.
3. code review / inspection for raw SQL leaks. As Tom mentioned, some
of the use of raw SQL are problematic. I did a quick scan on the use
of raw SQL connection, and did see some problematic usage (such as
ResultSet, statements, not being closed defensively) .
4. code review / inspection for hibernate leaks: for the codes that
use hibernate, it is still possible to have leaks if some codes forgot
to close a session.
Thoughts?
- sam
Note: the stack traces of the first two test failed due to the new c3p0 config.
<testcase
classname="org.mifos.application.customer.persistence.TestCustomerPersistence"
name="testNumberOfMeetingsAttended" time="1.052">
<error message="Batch update returned unexpected row count from
update: 0 actual row count: 0 expected: 1"
type="org.hibernate.StaleStateException">org.hibernate.StaleStateException:
Batch update returned unexpected row count from update: 0 actual row
count: 0 expected: 1
at
org.hibernate.jdbc.BatchingBatcher.checkRowCount(BatchingBatcher.java:88)
at
org.hibernate.jdbc.BatchingBatcher.checkRowCounts(BatchingBatcher.java:74)
at
org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:57)
at
org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:154)
at
org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:71)
at
org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:66)
at
org.hibernate.jdbc.AbstractBatcher.prepareBatchStatement(AbstractBatcher.java:130)
at
org.hibernate.persister.entity.BasicEntityPersister.update(BasicEntityPersister.java:1924)
at
org.hibernate.persister.entity.BasicEntityPersister.updateOrInsert(BasicEntityPersister.java:1880)
at
org.hibernate.persister.entity.BasicEntityPersister.update(BasicEntityPersister.java:2120)
at
org.hibernate.action.EntityUpdateAction.execute(EntityUpdateAction.java:75)
at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:239)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:223)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:137)
at
org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:274)
at
org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:669)
at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:293)
at
org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:86)
at
org.mifos.framework.hibernate.helper.HibernateUtil.commitTransaction(HibernateUtil.java:230)
at
org.mifos.application.customer.persistence.TestCustomerPersistence.testNumberOfMeetingsAttended(TestCustomerPersistence.java:397)
</error>
</testcase>
<testcase
classname="org.mifos.application.customer.persistence.TestCustomerPersistence"
name="testNumberOfMeetingsMissed" time="0.04">
<error message="Row was updated or deleted by another transaction
(or unsaved-value mapping was incorrect):
[org.mifos.application.customer.client.business.ClientBO#101]"
type="org.hibernate.StaleObjectStateException">org.hibernate.StaleObjectStateException:
Row was updated or deleted by another transaction (or unsaved-value
mapping was incorrect):
[org.mifos.application.customer.client.business.ClientBO#101]
at
org.hibernate.persister.entity.BasicEntityPersister.check(BasicEntityPersister.java:1416)
at
org.hibernate.persister.entity.BasicEntityPersister.update(BasicEntityPersister.java:1956)
at
org.hibernate.persister.entity.BasicEntityPersister.updateOrInsert(BasicEntityPersister.java:1880)
at
org.hibernate.persister.entity.BasicEntityPersister.update(BasicEntityPersister.java:2120)
at
org.hibernate.action.EntityUpdateAction.execute(EntityUpdateAction.java:75)
at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:239)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:223)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:137)
at
org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:274)
at
org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:669)
at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:293)
at
org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:86)
at
org.mifos.framework.persistence.TestObjectPersistence.persist(TestObjectPersistence.java:104)
at
org.mifos.framework.util.helpers.TestObjectFactory.createMeeting(TestObjectFactory.java:1143)
at
org.mifos.application.customer.persistence.TestCustomerPersistence.createCenter(TestCustomerPersistence.java:1227)
at
org.mifos.application.customer.persistence.TestCustomerPersistence.createCenter(TestCustomerPersistence.java:1223)
at
org.mifos.application.customer.persistence.TestCustomerPersistence.testNumberOfMeetingsMissed(TestCustomerPersistence.java:413)
</error>
</testcase>
On Nov 16, 2007 5:08 PM, Tom Bostelmann <[EMAIL PROTECTED]> wrote:
> I haven't had much of a chance to look into this. But do a search in the
> code for 'java.sql.Connection'. This is probably the culprit.
>
> Based on what I've seen in the code, I would look there before I started
> worrying about Hibernate.
>
> From what I've seen, we're bypassing Hibernate in the reports functionality
> as well as other places. So, Connection management is most likely done
> explicitly there.
>
> If you're interested in gathering Hibernate statistics as well, I added a
> rough implementation of the StatisticsService in the MifosTestCase class.
> This keeps track of open connections and other very useful statistics (like
> the number of commits and such).
>
> Thanks for all the input everyone! :)
> -Tom
>
>
>
> On Nov 16, 2007 10:16 AM, Sam Lee <[EMAIL PROTECTED]> wrote:
> >
> >
> >
> > On Nov 15, 2007 9:50 PM, Amy Bensinger (Contractor)
> >
> > <[EMAIL PROTECTED]> wrote:
> > > What might also be interesting is if any DBA-types have workaround
> > > suggestions/scripts while the longer term solutions are considered?
> > >
> > >
> >
> > Workarounds: an ugly but fairly effective workaround is to
> > periodically force the application (mifos in this case) closes the
> > connections.
> >
> > Option 1: If a connection pool is used, one can also try to
> > reconfigure the connection pool to force connections to be closed
> > after certain amount of time. E.g., tricks one can use with c3p0:
> >
> >
> http://www.mchange.com/projects/c3p0/index.html#configuring_to_debug_and_workaround_broken_clients
> >
> >
> > Option 2: the most brute-force way would be to have a script to
> > periodically restarting the app server (in off peak hours, more leeway
> > if the deployment has multiple servers so that the restart can be
> > staggered).
> >
> > To those who knows mifos config better: is the db connection
> > properties all set in the confi/hibernate.properties and the
> > hibernate.cfg.xml (somewhere under the source tree) (at least for the
> > GK deployment)? If so, I could take a stab to figure out if we could
> > have it reconfigured to use c3p0 connection pool to timeout the
> > connections.
> >
> > Let me know.
> >
> > -------------------------------------------------------------------------
> >
> >
> >
> >
> > This SF.net email is sponsored by: Microsoft
> > Defy all challenges. Microsoft(R) Visual Studio 2005.
> > http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
> >
>
>
> -------------------------------------------------------------------------
> This SF.net email is sponsored by: Microsoft
> Defy all challenges. Microsoft(R) Visual Studio 2005.
> http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
>
-------------------------------------------------------------------------
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2005.
http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
