8 Replies Latest reply on Jul 1, 2014 9:40 PM by Joe Clem

    Problems persisting SessionInfo with MySQL

    Joe Clem Newbie

      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.

        • 1. Re: Problems persisting SessionInfo with MySQL
          Marco Rietveld Novice

          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)

          • 2. Re: Problems persisting SessionInfo with MySQL
            Joe Clem Newbie

            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:

             

            Screen Shot 2014-04-16 at 12.09.45 PM.png

            • 3. Re: Problems persisting SessionInfo with MySQL
              Marco Rietveld Novice

              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.

              • 4. Re: Problems persisting SessionInfo with MySQL
                Joe Clem Newbie

                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:

                 

                Screen Shot 2014-04-23 at 3.44.14 PM.png

                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.

                • 5. Re: Problems persisting SessionInfo with MySQL
                  Maciej Swiderski Master

                  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....

                  • 6. Re: Problems persisting SessionInfo with MySQL
                    Joe Clem Newbie

                    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?

                    • 7. Re: Problems persisting SessionInfo with MySQL
                      Joe Clem Newbie

                      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.

                      • 8. Re: Problems persisting SessionInfo with MySQL
                        Joe Clem Newbie

                        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.