[Mifos-developer] MySQL JDBC Driver Issue

[Chico Charlesworth]

Hi,

I'm using MySQL Server version: 5.0.51a-community-nt MySQL Community Edition (GPL) on Windows XP, and I've encountered that the Mifos application has many problems with the current Mifos JDBC driver (mysql-connector-java-3.1.13-bin.jar). I then tested it with MySQL server version 5.0.45, and it seems that the current Mifos JDBC driver works fine on that.

I have now since tried with the latest MySQL Connector JDBC driver (mysql-connector-java-5.1.5-bin.jar) and the Mifos applications works fine again using MySQL 5.0.51a.
 
In light of this, can we have a consensus on which MySQL version and JDBC driver should we be developing on?

My preference is for Mifos to upgrade to the latest JDBC driver (Connector/J 5.1) as that has many improvements, as can be seen here:
 - http://dev.mysql.com/doc/refman/5.0/en/connector-j-versions.html

Cheers
Chico

-- Exception Log Trace Example When Creating a Centre using MySQL 5.0.51a and mysql-connector-java-3.1.13-bin.jar --

Hibernate: insert into CUSTOMER (VERSION_NO, GLOBAL_CUST_NUM, DISPLAY_NAME, DISPLAY_ADDRESS, EXTERNAL_ID, TRAINED, TRAINED_DATE, C
REATED_DATE, UPDATED_DATE, MFI_JOINING_DATE, SEARCH_ID, MAX_CHILD_COUNT, CREATED_BY, UPDATED_BY, BLACKLISTED, CUSTOMER_ACTIVATION_
DATE , STATUS_ID, CUSTOMER_LEVEL_ID, LOAN_OFFICER_ID, CUSTOMER_FORMEDBY_ID, BRANCH_ID, PARENT_CUSTOMER_ID, DISCRIMINATOR) values (
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'CENTER')
ERROR, org.hibernate.util.JDBCExceptionReporter, ?, ? , ?, Data truncation: Data truncated for column 'CUSTOMER_ACTIVATION_DATE' a
t row 1, 15 Feb 2008 15:26:28
org.mifos.application.customer.exceptions.CustomerException: org.mifos.framework.exceptions.PersistenceException: org.hibernate.ex
ception.GenericJDBCException: could not insert: [org.mifos.application.customer.center.business.CenterBO]
        at org.mifos.application.customer.business.CustomerBO.save(CustomerBO.java:462)
        at org.mifos.application.customer.center.struts.action.CenterCustAction.create(CenterCustAction.java:211)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:585)
        at org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:274)
        at org.apache.struts.actions.DispatchAction.execute(DispatchAction.java:194)
        at org.mifos.framework.struts.action.BaseAction.execute(BaseAction.java:73)
        at org.mifos.framework.struts.action.MifosRequestProcessor.processActionPerform(MifosRequestProcessor.java:227)
        at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:224)
        at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1194)
        at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:432)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:710)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at org.mifos.framework.security.util.LoginFilter.doFilter(LoginFilter.java:109)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at org.mifos.framework.persistence.DatabaseVersionFilter.doFilter(DatabaseVersionFilter.java:39)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:263)
        at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
        at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:584)
        at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
        at java.lang.Thread.run(Thread.java:595)
Caused by: org.mifos.framework.exceptions.PersistenceException: org.hibernate.exception.GenericJDBCException: could not insert: [o
rg.mifos.application.customer.center.business.CenterBO]
        at org.mifos.framework.persistence.Persistence.createOrUpdate(Persistence.java:43)
        at org.mifos.application.customer.business.CustomerBO.save(CustomerBO.java:455)
        ... 32 more
Caused by: org.hibernate.exception.GenericJDBCException: could not insert: [org.mifos.application.customer.center.business.CenterB
O]
        at org.hibernate.exception.ErrorCodeConverter.handledNonSpecificException(ErrorCodeConverter.java:92)
        at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:80)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
        at org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:1747)
        at org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:2149)
        at org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:34)
        at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:239)
        at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:238)
        at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:158)
        at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:104)
        at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListene
r.java:184)
        at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:173)
        at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.performSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:96)
        at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:69)
        at org.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:416)
        at org.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:411)
        at org.mifos.framework.persistence.Persistence.createOrUpdate(Persistence.java:38)
        ... 33 more
Caused by: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data truncated for column 'CUSTOMER_ACTIVATION_DATE' at row 1
        at com.mysql.jdbc.SQLError.convertShowWarningsToSQLWarnings(SQLError.java:709)
        at com.mysql.jdbc.MysqlIO.scanForAndThrowDataTruncation(MysqlIO.java:3461)
        at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1198)
        at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:677)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1357)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1274)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1259)
        at org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:1729)
        ... 46 more
ERROR, org.mifos, MifosExceptionHandler, logException , 72, No resource is associated with key "Customer.CreateFailed"., 15 Feb 20
08 15:26:28
ERROR, org.mifos, MifosExceptionHandler, logException , 72, Customer.CreateFailed, 15 Feb 2008 15:26:28
org.mifos.application.customer.exceptions.CustomerException: org.mifos.framework.exceptions.PersistenceException: org.hibernate.ex
ception.GenericJDBCException: could not insert: [org.mifos.application.customer.center.business.CenterBO]
        at org.mifos.application.customer.business.CustomerBO.save(CustomerBO.java:462)
        at org.mifos.application.customer.center.struts.action.CenterCustAction.create(CenterCustAction.java:211)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:585)
        at org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:274)
        at org.apache.struts.actions.DispatchAction.execute(DispatchAction.java:194)
        at org.mifos.framework.struts.action.BaseAction.execute(BaseAction.java:73)
        at org.mifos.framework.struts.action.MifosRequestProcessor.processActionPerform(MifosRequestProcessor.java:227)
        at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:224)
        at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1194)
        at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:432)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:710)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at org.mifos.framework.security.util.LoginFilter.doFilter(LoginFilter.java:109)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at org.mifos.framework.persistence.DatabaseVersionFilter.doFilter(DatabaseVersionFilter.java:39)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:263)
        at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
        at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:584)
        at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
        at java.lang.Thread.run(Thread.java:595)
Caused by: org.mifos.framework.exceptions.PersistenceException: org.hibernate.exception.GenericJDBCException: could not insert: [o
rg.mifos.application.customer.center.business.CenterBO]
        at org.mifos.framework.persistence.Persistence.createOrUpdate(Persistence.java:43)
        at org.mifos.application.customer.business.CustomerBO.save(CustomerBO.java:455)
        ... 32 more
Caused by: org.hibernate.exception.GenericJDBCException: could not insert: [org.mifos.application.customer.center.business.CenterB
O]
        at org.hibernate.exception.ErrorCodeConverter.handledNonSpecificException(ErrorCodeConverter.java:92)
        at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:80)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
        at org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:1747)
        at org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:2149)
        at org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:34)
        at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:239)
        at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:238)
        at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:158)
        at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:104)
        at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListene
r.java:184)
        at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:173)
        at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.performSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:96)
        at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:69)
        at org.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:416)
        at org.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:411)
        at org.mifos.framework.persistence.Persistence.createOrUpdate(Persistence.java:38)
        ... 33 more
Caused by: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data truncated for column 'CUSTOMER_ACTIVATION_DATE' at row 1
        at com.mysql.jdbc.SQLError.convertShowWarningsToSQLWarnings(SQLError.java:709)
        at com.mysql.jdbc.MysqlIO.scanForAndThrowDataTruncation(MysqlIO.java:3461)
        at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1198)
        at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:677)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1357)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1274)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1259)
        at org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:1729)
        ... 46 more
Hibernate: insert into CUSTOMER_ADDRESS_DETAIL (LINE_1, LINE_2, LINE_3, CITY, STATE, COUNTRY, ZIP, PHONE_NUMBER, CUSTOMER_ID) valu
es (?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into MEETING (VERSION_NO, MEETING_TYPE_ID, MEETING_PLACE, START_DATE) values (?, ?, ?, ?)
Hibernate: insert into RECURRENCE_DETAIL (VERSION_NO, MEETING_ID, RECURRENCE_ID, RECUR_AFTER) values (?, ?, ?, ?)
Hibernate: insert into RECUR_ON_DAY (VERSION_NO, DAY_NUMBER, DETAILS_ID, DAYS, RANK_OF_DAYS) values (?, ?, ?, ?, ?)
Hibernate: insert into CUSTOMER_MEETING (UPDATED_FLAG, MEETING_ID, UPDATED_MEETING_ID, CUSTOMER_ID) values (?, ?, ?, ?)
ERROR, org.hibernate.util.JDBCExceptionReporter, ?, ? , ?, Column 'CUSTOMER_ID' cannot be null, 15 Feb 2008 15:26:28
Hibernate: insert into ACCOUNT (VERSION_NO, OFFSETTING_ALLOWABLE, GLOBAL_ACCOUNT_NUM, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATE
D_DATE, CLOSED_DATE, CUSTOMER_ID, OFFICE_ID, ACCOUNT_STATE_ID, ACCOUNT_TYPE_ID, PERSONNEL_ID) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?
, ?, ?, ?)
ERROR, org.hibernate.util.JDBCExceptionReporter, ?, ? , ?, Data truncation: Data truncated for column 'CREATED_DATE' at row 1, 15
Feb 2008 15:26:28
Hibernate: insert into CUSTOMER_SCHEDULE (VERSION_NO, INSTALLMENT_ID, ACTION_DATE, PAYMENT_STATUS, PAYMENT_DATE, MISC_FEES_CURRENC
Y_ID, MISC_FEES, MISC_FEES_PAID_CURRENCY_ID, MISC_FEES_PAID, MISC_PENALTY_CURRENCY_ID, MISC_PENALTY, MISC_PENALTY_PAID_CURRENCY_ID
, MISC_PENALTY_PAID, ACCOUNT_ID, CUSTOMER_ID) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
ERROR, org.hibernate.util.JDBCExceptionReporter, ?, ? , ?, Column 'ACCOUNT_ID' cannot be null, 15 Feb 2008 15:26:28
Hibernate: insert into CUSTOMER_SCHEDULE (VERSION_NO, INSTALLMENT_ID, ACTION_DATE, PAYMENT_STATUS, PAYMENT_DATE, MISC_FEES_CURRENC
Y_ID, MISC_FEES, MISC_FEES_PAID_CURRENCY_ID, MISC_FEES_PAID, MISC_PENALTY_CURRENCY_ID, MISC_PENALTY, MISC_PENALTY_PAID_CURRENCY_ID
, MISC_PENALTY_PAID, ACCOUNT_ID, CUSTOMER_ID) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
ERROR, org.hibernate.util.JDBCExceptionReporter, ?, ? , ?, Column 'ACCOUNT_ID' cannot be null, 15 Feb 2008 15:26:28

[Adam Monsen]

Hi Chico,

Thank you very much for sharing your findings!

On Fri, 2008-02-15 at 15:37 +0000, Chico Charlesworth wrote:
> My preference is for Mifos to upgrade to the latest JDBC driver
> (Connector/J 5.1) as that has many improvements, as can be seen here:
> - http://dev.mysql.com/doc/refman/5.0/en/connector-j-versions.html

I agree; seems like we should move to Connector/J 5.1. It sounds better
in all respects:
* compatible with more versions of MySQL
* fixes many bugs
* many improvements, as you mentioned

And this also seems like an opportune time to make the change since it
would hopefully ease pre-v1.1 development.

I have an idea... anyone who has a working test environment (e.g: you
have successfully run "ant run_test"), please try out the Connector/J
5.1 JAR on your Mifos development workstation. Once we see some
successful results from a few different platform, app server, OS, and
machine architecture combinations, maybe by then we're ready to go ahead
and upgrade the JAR.

Thoughts? Too much due diligence (Java is write once, run everywhere!)?
Too little (Java is write once, debug everywhere!)?

Here's where we could add our results from trying out the new library:
http://mifos.org/developers/wiki/UpgradeMySQLClientLib

--
Adam Monsen

-------------------------------------------------------------------------
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2008.
http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/

[Van Mittal-Henkle]

Thanks for looking into this Chico (and adding your thoughts Adam).

We definitely do want to move to the Connector/J 5.1 version.

As Adam mentions, the only precondition before making the change is just
to get verification that the test suite passes with the updated library
and that someone has done some smoke tests in a deployed instance of the
application to make sure it looks ok.

Once that is done we can commit the change and get it up on the test
server where it can get some additional scrutiny. If we find any issues
it's easy enough to roll back.

After getting the 5.1 lib in place then we can have a discussion about
which version of MySQL we should settle on for our recommended version.

Cheers,
--Van

> Thank you very much for sharing your findings!
>
> On Fri, 2008-02-15 at 15:37 +0000, Chico Charlesworth wrote:
>> My preference is for Mifos to upgrade to the latest JDBC driver
>> (Connector/J 5.1) as that has many improvements, as can be seen here:
>> - http://dev.mysql.com/doc/refman/5.0/en/connector-j-versions.html
>
> I agree; seems like we should move to Connector/J 5.1. It sounds
> better in all respects:
> * compatible with more versions of MySQL
> * fixes many bugs
> * many improvements, as you mentioned

-------------------------------------------------------------------------

[Stephen Horgan]

Just confirming that the new jar works for me. I've updated the wiki page to reflect this.
http://mifos.org/developers/wiki/UpgradeMySQLClientLib

- Steve

[Adam Monsen]

On Mon, 2008-02-18 at 13:01 +0000, Stephen Horgan wrote:
> Just confirming that the new jar works for me. I've updated the wiki
> page to reflect this.
> http://mifos.org/developers/wiki/UpgradeMySQLClientLib

Thank you, Steve!

Anyone out there on Mac OS X with time to try it out? The process should
be fairly straightforward.

--
Adam Monsen

[Van Mittal-Henkle]

Steve,

 

Thanks for getting this effort started.  I plan on getting the updated library in place soon and suggest that other developers do the same so that we can move ahead with this change.

 

--Van

---

From: mifos-devel...@lists.sourceforge.net [mailto:mifos-devel...@lists.sourceforge.net] On Behalf Of Stephen Horgan
Sent: Monday, February 18, 2008 5:01 AM
To: Developer
Subject: Re: [Mifos-developer] MySQL JDBC Driver Issue

[Adam Monsen]

Ok, we're up to four datapoints:
http://mifos.org/developers/wiki/UpgradeMySQLClientLib

How 'bout if we wait one more week in case anyone else wants to try it
out, then upgrade the library.

--
Adam Monsen

[Adam Monsen]

On Fri, 2008-02-22 at 14:21 -0800, Adam Monsen wrote:
> How 'bout if we wait one more week in case anyone else wants to try it
> out, then upgrade the library.

As promised...

Mifos now uses MySQL Connector/J version 5.1.5. Committed in revision
12340.

[Sam Birney]

Good thing the MySQL Connectory/J is open source, since I missed this deadline for certifying on Mac OS X I may have to fix this someday, but I'll bet it works already.