1 2 Previous Next 15 Replies Latest reply on Jan 18, 2013 2:51 AM by charybr

    PostgreSQL - empty database - install.demo.db does not exist

    wishmaster7

      Hello everybody,

       

      I have downloaded the last version of jBPM (5.4.0), unzipped it, launched "ant install.demo", installed a local PostgreSQL database and follow the documentation to create the database with the matching user and configured jBPM to connect to this database. At the end of "3.7.3 Quickstart", when I want to reinstall the demo, it says BUILD FAILED - target "install.demo.db" does not exist in the project "jBPM.install".

       

      At this point, I have a database called "jbpm5" with a user called "jbpm5", and a database called "task" with a user called "task". But they are empty. Then I launch Eclipse, create a new jBPM project with "a more advanced process including human task and persistence", and when I run the testProcess() method of the jUnit test class, it gives me an error ending by :

       

      ERROR tool.hbm2ddl.SchemaUpdate.execute  - could not get database metadata

      org.h2.jdbc.JdbcSQLException: Table "PG_CLASS" not found; SQL statement:

      select relname from pg_class where relkind='S' [42102-167]

       

      [ ... ]

       

      Caused by: org.h2.jdbc.JdbcSQLException: Sequence "SESSIONINFO_ID_SEQ" not found; SQL statement:

      select nextval ('SESSIONINFO_ID_SEQ') [90036-167]

          at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)

          at org.h2.message.DbException.get(DbException.java:169)

          at org.h2.message.DbException.get(DbException.java:146)

          at org.h2.schema.Schema.getSequence(Schema.java:474)

          at org.h2.expression.Function.getSequence(Function.java:1281)

          at org.h2.expression.Function.getValueWithArgs(Function.java:1129)

          at org.h2.expression.Function.getValue(Function.java:447)

          at org.h2.command.dml.Select.queryFlat(Select.java:519)

          at org.h2.command.dml.Select.queryWithoutCache(Select.java:618)

          at org.h2.command.dml.Query.query(Query.java:307)

          at org.h2.command.dml.Query.query(Query.java:277)

          at org.h2.command.dml.Query.query(Query.java:36)

          at org.h2.command.CommandContainer.query(CommandContainer.java:86)

          at org.h2.command.Command.executeQuery(Command.java:191)

          at org.h2.server.TcpServerThread.process(TcpServerThread.java:306)

          at org.h2.server.TcpServerThread.run(TcpServerThread.java:149)

          at java.lang.Thread.run(Unknown Source)

       

          at org.h2.engine.SessionRemote.done(SessionRemote.java:538)

          at org.h2.command.CommandRemote.executeQuery(CommandRemote.java:151)

          at org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:96)

          at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

          at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

          at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

          at java.lang.reflect.Method.invoke(Unknown Source)

          at bitronix.tm.resource.jdbc.BaseProxyHandlerClass.invoke(BaseProxyHandlerClass.java:63)

          at $Proxy35.executeQuery(Unknown Source)

          at org.hibernate.id.SequenceGenerator.generate(SequenceGenerator.java:98)

          ... 47 more

       

       

      I think the problem comes from the missing "install.demo.db" task in the ant file. I've searched if a script exists to create the tables in my database, but found nothing.

       

      Could someone please tell me what I have to do to create the required tables and sequences? Or, if I'm totally wrong, where is the problem?

        • 1. Re: PostgreSQL - empty database - install.demo.db does not exist
          roxy1987

          install.demo.db is mentioned in the user guide but is not in build.xml. But you dont need that. Could you post your build.properties file content here? I am not sure if you did all the configuration changes.

          • 2. Re: PostgreSQL - empty database - install.demo.db does not exist
            wishmaster7

            here is my build.properties ; all my changes concern the last 13 lines : commenting H2 db and uncommenting postgresql db.

            • 3. Re: PostgreSQL - empty database - install.demo.db does not exist
              roxy1987

              Ok. Your build.properties file looks fine. Lot of other files are modified during this process but I would like to believe that the configuration done by you is okay. Before you run the sample project. Just give the ant script start.demo. This will start your jboss server and will deploy the war files. Including human task war file. Once these files are successfully deployed(You can check successful deployment by going into the directory \jbpm-installer\jboss-as-7.1.1.Final\standalone\deployments\ and looking for .DEPLOYED files. You have to wait until .DEPLOYED files are generated for each war), the task service will start by itself and will create tables in your task db. Similarly just run jbpm-console once and start a process. If you are able to do that then you should have tables in your db. If not then you may have missed some configuration.

              Meanwhile could you post the following files too :

               

              standalone.xml

              build.xml

              \task-service\resources\META-INF\persistence.xml

              db\persistence.xml

              and also the name of the jar file you are using for jdbc with postgre.

               

              BTW, i should tell you, I am not an expert myself but have done the DB migration plenty of times so I might be able to help you here..

               

              CHeers.

              • 4. Re: PostgreSQL - empty database - install.demo.db does not exist
                doboss

                I opened a Jira for the missing task last week some time. But roxy1987 is right, by default the files are setup to create the schema for you the first time they run, so that is one way to get the tables created.

                 

                I have the tables created in PostgreSQL 9.1 so if you can't get it working, and just want me to send you the create statements etc, let me know and I can export them and upload them.

                • 5. Re: PostgreSQL - empty database - install.demo.db does not exist
                  wishmaster7

                  Hi roxy1987,

                   

                  Here are my configuration files, including the jar file downloaded in db\driver\ (the file download link is in build.properties, db.driver.download.url  ).

                   

                  I think I went too fast in Eclipse; I have an error before that, when the server is starting; I didn't notice it because the console doesn't show anything, but in Jboss' server.log I can see errors :

                   

                  08:19:31,030 ERROR [org.hibernate.tool.hbm2ddl.SchemaUpdate] (MSC service thread 1-1) HHH000388: Unsuccessful: create table Attachment (id bigint generated by default as identity, accessType integer, attachedAt timestamp, attachmentContentId bigint not null, contentType varchar(255), name varchar(255), attachment_size integer, attachedBy_id varchar(255), TaskData_Attachments_Id bigint, primary key (id))

                  08:19:31,046 ERROR [org.hibernate.tool.hbm2ddl.SchemaUpdate] (MSC service thread 1-1) ERREUR: erreur de syntaxe sur ou près de « generated »

                    Position : 36

                   

                  [...]

                   

                  erreur de syntaxe sur ou près de =>  syntax error on or close to

                   

                  So, I undertstand that Jboss, at startup, tries to create the tables, but fails. In D:\Projects\jbpm-installer\jboss-as-7.1.1.Final\standalone\deployments, I find the .deployed files.

                   

                  Searching for the reason, I think Jboss doesn't take the right driver (generated by default is a typical H2 command). If you can find something I did wrong in my configuration, it might rescue me !

                  • 6. Re: PostgreSQL - empty database - install.demo.db does not exist
                    wishmaster7

                    Hi doboss,

                     

                    Until I find the problem, it might help me to create the tables manually and go forward in my project. So yes please, if you can give me the structure creation sql file, I would be thankful.

                    • 7. Re: PostgreSQL - empty database - install.demo.db does not exist
                      roxy1987

                      Your configuration looks goood. I hope the name iof the driver jar file you use is postgresql-jdbc.jar

                       

                      This is what your subsystem tag looks like

                      <subsystem xmlns="urn:jboss:domain:datasources:1.0">

                      <datasources>

                      <datasource jndi-name="java:jboss/datasources/jbpmDS" pool-name="jbpmDS" enabled="true" use-java-context="true">

                      <connection-url>jdbc:postgresql://localhost:5432/jbpm5</connection-url>

                      <driver>postgresql</driver>

                      <pool></pool>

                      <security>

                      <user-name>jbpm5</user-name>

                      <password>jbpm5</password>

                      </security>

                      </datasource>

                      <drivers>

                      <driver name="postgresql" module="org.postgresql">

                      <xa-datasource-class>org.postgresql.xa.PGXADataSource</xa-datasource-class>

                      </driver>

                      </drivers>

                      </datasources>

                      </subsystem>

                      And this is what the original one is :

                       

                       

                      Instead of just replacing the entire thing, you should only modify the values in the original. Because the fragment in the user guide is from previous versions. This may not be the cause of the problem but just modify and check.

                      Everything else looks good. Lemme know..

                      <subsystem xmlns="urn:jboss:domain:datasources:1.0">

                      <datasources>

                      <datasource jndi-name="java:jboss/datasources/ExampleDS" pool-name="ExampleDS" enabled="true" use-java-context="true">

                      <connection-url>jdbc:h2:mem:test;DB_CLOSE_DELAY=-1</connection-url>

                      <driver>h2</driver>

                      <security>

                      <user-name>sa</user-name>

                      <password>sa</password>

                      </security>

                      </datasource>

                      <datasource jta="true" jndi-name="java:jboss/datasources/jbpmDS" pool-name="H2DS" enabled="true" use-java-context="true" use-ccm="true">

                      <connection-url>jdbc:h2:~/jbpm</connection-url>

                      <driver>h2</driver>

                      <pool>

                      <min-pool-size>1</min-pool-size>

                      <max-pool-size>4</max-pool-size>

                      <prefill>false</prefill>

                      <use-strict-min>false</use-strict-min>

                      <flush-strategy>FailingConnectionOnly</flush-strategy>

                      </pool>

                      <security>

                      <user-name>sa</user-name>

                      </security>

                      <validation>

                      <check-valid-connection-sql>SELECT 1</check-valid-connection-sql>

                      <validate-on-match>false</validate-on-match>

                      <background-validation>false</background-validation>

                      </validation>

                      </datasource>

                      <drivers>

                      <driver name="h2" module="com.h2database.h2">

                      <xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>

                      </driver>

                      </drivers>

                      </datasources>

                      </subsystem>

                      • 8. Re: PostgreSQL - empty database - install.demo.db does not exist
                        doboss

                        Here it is, hope it helps.

                        1 of 1 people found this helpful
                        • 9. Re: PostgreSQL - empty database - install.demo.db does not exist
                          wishmaster7

                          Hello,

                           

                          Now I have created the tables and sequences with doboss' script. The server is running but I have a new error after I connect to the jBPM Console (with user krisv) :

                           

                          08:47:47,359 ERROR [org.drools.persistence.SingleSessionCommandService] (http-localhost-127.0.0.1-8080-4) Could not commit session: java.lang.RuntimeException: Could not find session data for id 1

                              at org.drools.persistence.SingleSessionCommandService.initKsession(SingleSessionCommandService.java:231) [drools-persistence-jpa-5.5.0.Final.jar:5.5.0.Final]

                              at org.drools.persistence.SingleSessionCommandService.<init>(SingleSessionCommandService.java:195) [drools-persistence-jpa-5.5.0.Final.jar:5.5.0.Final]

                              at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) [rt.jar:1.7.0_10]

                              at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) [rt.jar:1.7.0_10]

                              at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) [rt.jar:1.7.0_10]

                              at java.lang.reflect.Constructor.newInstance(Constructor.java:525) [rt.jar:1.7.0_10]

                              at org.drools.persistence.jpa.KnowledgeStoreServiceImpl.buildCommandService(KnowledgeStoreServiceImpl.java:100) [drools-persistence-jpa-5.5.0.Final.jar:5.5.0.Final]

                              at org.drools.persistence.jpa.KnowledgeStoreServiceImpl.loadStatefulKnowledgeSession(KnowledgeStoreServiceImpl.java:83) [drools-persistence-jpa-5.5.0.Final.jar:5.5.0.Final]

                              at org.drools.persistence.jpa.JPAKnowledgeService.loadStatefulKnowledgeSession(JPAKnowledgeService.java:131) [knowledge-api-5.5.0.Final.jar:5.5.0.Final]

                              at org.jbpm.integration.console.session.MVELSingleSessionManager.loadSessionFromTemplate(MVELSingleSessionManager.java:150) [jbpm-gwt-core-5.4.0.Final.jar:5.4.0.Final]

                              at org.jbpm.integration.console.session.MVELSingleSessionManager.getSession(MVELSingleSessionManager.java:300) [jbpm-gwt-core-5.4.0.Final.jar:5.4.0.Final]

                              at org.jbpm.integration.console.StatefulKnowledgeSessionUtil.initializeStatefulKnowledgeSession(StatefulKnowledgeSessionUtil.java:104) [jbpm-gwt-core-5.4.0.Final.jar:5.4.0.Final]

                              at org.jbpm.integration.console.StatefulKnowledgeSessionUtil$SessionHolder.<clinit>(StatefulKnowledgeSessionUtil.java:84) [jbpm-gwt-core-5.4.0.Final.jar:5.4.0.Final]

                              at org.jbpm.integration.console.StatefulKnowledgeSessionUtil.getStatefulKnowledgeSession(StatefulKnowledgeSessionUtil.java:88) [jbpm-gwt-core-5.4.0.Final.jar:5.4.0.Final]

                              at org.jbpm.integration.console.SessionInitializer.<init>(SessionInitializer.java:25) [jbpm-gwt-core-5.4.0.Final.jar:5.4.0.Final]

                              at org.jbpm.integration.console.ProcessManagement.<init>(ProcessManagement.java:38) [jbpm-gwt-core-5.4.0.Final.jar:5.4.0.Final]

                              at org.jbpm.integration.console.ManagementFactory.createProcessManagement(ManagementFactory.java:22) [jbpm-gwt-core-5.4.0.Final.jar:5.4.0.Final]

                              at org.jbpm.integration.console.ManagementFactory.createProcessManagement(ManagementFactory.java:19) [jbpm-gwt-core-5.4.0.Final.jar:5.4.0.Final]

                              at org.jboss.bpm.console.server.ProcessMgmtFacade.getProcessManagement(ProcessMgmtFacade.java:89) [classes:]

                              at org.jboss.bpm.console.server.ProcessMgmtFacade.getDefinitionsJSON(ProcessMgmtFacade.java:125) [classes:]

                           

                          [ ... ]

                           

                          I think it is not able to create the session; the sessioninfo table is in the database but I don't know why it can't create (or load?) a session. Any idea?

                          • 10. Re: PostgreSQL - empty database - install.demo.db does not exist
                            doboss

                            Could it be the case I may have dumped the database with some data in it by accident?

                             

                            Also, I have posted a more complete and standalone version of JBPM in a web app here: https://community.jboss.org/wiki/SimpleJBPM54InsideJBossAS711WebAppExample That example will also create the DB tables for you...

                            • 11. Re: PostgreSQL - empty database - install.demo.db does not exist
                              roxy1987

                              Wishmaster7,

                               

                              Why would you wanna create the schema using script when persistence is doing this for you. You were not able to connect at the begining and manually creating the schema did not change anything. So ofcourse you are still not able to connect.

                              My advice, clean your installation and start configuration again. There should be something that you missed and looking at your configuration files, I missed it too. You might catch the problem if you repeat the whole process again.

                               

                              Regards.

                              • 12. Re: PostgreSQL - empty database - install.demo.db does not exist
                                wishmaster7

                                The problem above is with jBPM 5.4 . I also tried with jBPM 5.3 and with this version I can connect to PostgreSQL database, starting jBoss creates the schema. But then I have the following error in the jbpm-console :

                                Caused by: java.lang.NullPointerException

                                    at org.jbpm.integration.console.StatefulKnowledgeSessionUtil.checkPackagesFromGuvnor(StatefulKnowledgeSessionUtil.java:447) [jbpm-gwt-core-5.3.0.Final.jar:]

                                    at org.jbpm.integration.console.CommandDelegate.getProcesses(CommandDelegate.java:67) [jbpm-gwt-core-5.3.0.Final.jar:]

                                    at org.jbpm.integration.console.ProcessManagement.getProcessDefinitions(ProcessManagement.java:42) [jbpm-gwt-core-5.3.0.Final.jar:]

                                    at org.jboss.bpm.console.server.ProcessMgmtFacade.getDefinitionsJSON(ProcessMgmtFacade.java:125) [classes:]

                                    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [:1.7.0_10]

                                    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) [:1.7.0_10]

                                    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [:1.7.0_10]

                                    at java.lang.reflect.Method.invoke(Method.java:601) [:1.7.0_10]

                                    at org.jboss.resteasy.core.MethodInjectorImpl.invoke(MethodInjectorImpl.java:140) [resteasy-jaxrs-2.2.1.GA.jar:]

                                    at org.jboss.resteasy.core.ResourceMethod.invokeOnTarget(ResourceMethod.java:255) [resteasy-jaxrs-2.2.1.GA.jar:]

                                    at org.jboss.resteasy.core.ResourceMethod.invoke(ResourceMethod.java:220) [resteasy-jaxrs-2.2.1.GA.jar:]

                                    at org.jboss.resteasy.core.ResourceMethod.invoke(ResourceMethod.java:209) [resteasy-jaxrs-2.2.1.GA.jar:]

                                    at org.jboss.resteasy.core.SynchronousDispatcher.getResponse(SynchronousDispatcher.java:519) [resteasy-jaxrs-2.2.1.GA.jar:]

                                    ... 24 more

                                 

                                With jBPM 5.4, the error was when starting jBoss :

                                08:19:31,030 ERROR [org.hibernate.tool.hbm2ddl.SchemaUpdate] (MSC service thread 1-1) HHH000388: Unsuccessful: create table Attachment (id bigint generated by default as identity, accessType integer, attachedAt timestamp, attachmentContentId bigint not null, contentType varchar(255), name varchar(255), attachment_size integer, attachedBy_id varchar(255), TaskData_Attachments_Id bigint, primary key (id))

                                08:19:31,046 ERROR [org.hibernate.tool.hbm2ddl.SchemaUpdate] (MSC service thread 1-1) ERREUR: erreur de syntaxe sur ou près de « generated »

                                 

                                and this is why I tried to create the schema with a sql script.

                                 

                                 

                                 

                                I will try a new installation from scratch with jBPM 5.4, and if it still doesn't work I will try MySQL.

                                • 13. Re: PostgreSQL - empty database - install.demo.db does not exist
                                  doboss

                                  wishmaster7,

                                   

                                  I am pretty sure it has nothing to do with JBPM 5.4 combined with PostgreSQL as that is exactly what I am doing. (I am using postgres 9.1). You might try dropping the web app example I created into your jboss installation and see if it connects. It does not depend on anything except the drivers and has instructions on how to set those up as well. If you can drop my web app into your installation, and it works, than you know it's some sort of configuration in your setup.

                                   

                                  HTH

                                  • 14. Re: PostgreSQL - empty database - install.demo.db does not exist
                                    roxy1987

                                    Wishmaster7,

                                     

                                    It has to work with any db. I use DB2 with jbpm5, so believe me you can run this with any. Please post again if you face problems.

                                    1 2 Previous Next