Problems persisting SessionInfo with MySQL

[jclem]

I am using jBPM 5.4.  I am new to jBPM and setting up very simple scenarios to learn the foundation.  While learning how persistence works I am relying on JBPMHelper class.

Persisting with H2Server worked like a charm:

- Used supplied jBPM.properties file

- JBPMHelper.startH2server();

- JBPMHelper.setupDataSource();

My next step was to persist to a MySQL database.

- I made what I believe were all of the property modifications in the jBPM.properties file.  I double checked by printing out JBPMHelper.getProperties()

- I removed JBPMHelper.startH2Server

- My code fails on this line trying to establish a brand new session: StatefulKnowledgeSession ksession = JBPMHelper.newStatefulKnowlegeSession(kbase);

- The error deep in the Stack Trace:

  Caused by: org.hibernate.exception.DataException: could not insert: [org.drools.persistence.info.SessionInfo]

  Caused by: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '' for column 'startDate' at row 1

After diving deep in the debugger it appears that it is failing on this line in the JBPMHelper class:

  ksession = JPAKnowledgeService.newStatefulKnowledgeSession( kbase, null, env );

I checked properties in the EnvironmentManagerFactory and all of the hibernate properties seem to be fine regarding my MySQL database.

The database tables in the MySQL database are being created and specifically the SessionInfo table is being created.  However it is not setup to accept the value of '' for startDate.  However it is setup to accept the value null for startDate.

What configuration did I miss?  Please Help.

Thanks.

[marco.rietveld]

Hi Joe,

Could you paste the hibernate dialect that you're using (in your persistence.xml)? I believe you'll need to use the InnoDb dialect version if you're not using that.

- MySQLInnoDBDialect (Hibernate JavaDocs)

[jclem]

Marco,

The Dialect was org.hibernate.dialect.MySQLDBDialect

I modified it to your suggestion of org.hibernate.dialect.MySQL5InnoDBDialect

I am using MySQL version 5.6.10

After that modification I continued to get the same error:

Hibernate: insert into SessionInfo (lastModificationDate, rulesByteArray, startDate, OPTLOCK) values (?, ?, ?, ?)

0    16/04 12:00:49,565[main] ERROR hibernate.util.JDBCExceptionReporter.logExceptions  - Data truncation: Incorrect datetime value: '' for column 'startDate' at row 1

The column definition for SessionInfo.startDate is dateTime allowing NULL. MySQL does not equate '' to NULL.

I switched from using the JBPMHelper class to starting the session with the following code to perhaps shed more light:

[marco.rietveld]

Could you check the table definition in the database? (Maybe just delete the tables, and set the "hibernate.hbm2ddl.auto" property in your peristence.xml to update?)

This answer on stackoverflow implies that it could be that.

[jclem]

Marco,

I did one better and dropped the entire database and re-created it.

<property name="hibernate.hbm2ddl.auto" value="update" />

<property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5InnoDBDialect"/>

SessionInfo table definition:

Hibernate: insert into SessionInfo (lastModificationDate, rulesByteArray, startDate, OPTLOCK) values (?, ?, ?, ?)

0    23/04 14:02:36,705[main] ERROR hibernate.util.JDBCExceptionReporter.logExceptions  - Data truncation: Incorrect datetime value: '' for column 'startDate' at row 1

Should jBPM be trying to insert an empty string for startDate?  Why wouldn't it be picking the current date?

Thanks for your continued help on this issue.

[swiderski.maciej]

could it be related to locale settings of the jvm that is providing another format of the date too long for what data base expect....

[jclem]

My locale = en_US and the value jBPM is trying to use for startDate is '' so doubt the value being too long is the issue.

When I run the process I am starting with a database with no tables so the tables are being created on the fly.  Is there a more appropriate way to create the database schema before the process is run, perhaps some extra magic is being installed during database creation by jBPM that is not getting created with my approach?

[jclem]

Well a bit more information after setting the logger to DEBUG and tracing jdbc bindings.

Here is part of the stack trace after reconfiguring the logger:

12483 19/05 14:16:32,465[main] DEBUG org.hibernate.SQL.logStatement  - insert into SessionInfo (lastModificationDate, rulesByteArray, startDate, OPTLOCK) values (?, ?, ?, ?)

Hibernate: insert into SessionInfo (lastModificationDate, rulesByteArray, startDate, OPTLOCK) values (?, ?, ?, ?)

12485 19/05 14:16:32,467[main] TRACE hibernate.type.TimestampType.nullSafeSet  - binding null to parameter: 1

12486 19/05 14:16:32,468[main] TRACE hibernate.type.TimestampType.nullSafeSet  - binding '2014-05-19 14:16:31' to parameter: 3

12492 19/05 14:16:32,474[main] TRACE hibernate.type.IntegerType.nullSafeSet  - binding '0' to parameter: 4

12503 19/05 14:16:32,485[main] DEBUG hibernate.jdbc.AbstractBatcher.logClosePreparedStatement  - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)

12503 19/05 14:16:32,485[main] DEBUG hibernate.jdbc.ConnectionManager.aggressiveRelease  - aggressively releasing JDBC connection

12513 19/05 14:16:32,495[main] DEBUG hibernate.jdbc.ConnectionManager.closeConnection  - releasing JDBC connection [ (open PreparedStatements: 0, globally: 0) (open ResultSets: 0, globally: 0)]

12518 19/05 14:16:32,500[main] DEBUG hibernate.util.JDBCExceptionReporter.logExceptions  - could not insert: [org.drools.persistence.info.SessionInfo] [insert into SessionInfo (lastModificationDate, rulesByteArray, startDate, OPTLOCK) values (?, ?, ?, ?)]

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '' for column 'startDate' at row 1

As you can see the startDate parameter is being supplied a datetime value: binding '2014-05-19 14:16:31' to parameter: 3

I manually inserted a row into the SessionInfo table applying only a meaningful value to startDate with the value '2014-05-19 14:16:31' and it worked fine.  So that format is valid for MySQL.

What caught my eye is that there is no binding for Parameter 2 - rulesByteArray.

[jclem]

Well, it turns out that the version of the mysql jdbc connector I was using did not work with jBPM.  I tried using the one that came with the jBPM package and everything works great.

[manasa.redddy]

Hi,

Am facing the same issue "java.sql.SQLException: Incorrect datetime value: '' for column 'startDate' at row 1".

As per your suggestion, I downloaded jbpm full installer but i couldn't see any mysql driver with the package but there is only h2 driver. Kindly let me know from where can i get the mysql driver that come along with jbpm package?