[jboss-user] [jBPM] - PostgreSQL - empty database - install.demo.db does not exist

[wishmaster7]

wishmaster7 [https://community.jboss.org/people/wishmaster7] created the discussion

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

To view the discussion, visit: https://community.jboss.org/message/787619#787619

--------------------------------------------------------------
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 http://docs.jboss.org/jbpm/v5.4/userguide/ch.installer.html#d0e609 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".

ERROR tool.hbm2ddl.SchemaUpdate.execute  - could not get database metadata
select relname from pg_class where relkind='S' [42102-167]
[ ... ]
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?
--------------------------------------------------------------

Reply to this message by going to Community
[https://community.jboss.org/message/787619#787619]

Start a new discussion in jBPM at Community
[https://community.jboss.org/choose-container!input.jspa?contentType=1&containerType=14&container=2034]

[wishmaster7]

wishmaster7 [https://community.jboss.org/people/wishmaster7] created the discussion

"Re: PostgreSQL - empty database - install.demo.db does not exist"

To view the discussion, visit: https://community.jboss.org/message/788689#788689

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

Reply to this message by going to Community
[https://community.jboss.org/message/788689#788689]

Start a new discussion in jBPM at Community
[https://community.jboss.org/choose-container!input.jspa?contentType=1&containerType=14&container=2034]

[roxy1987]

roxy1987 [https://community.jboss.org/people/roxy1987] created the discussion

"Re: PostgreSQL - empty database - install.demo.db does not exist"

To view the discussion, visit: https://community.jboss.org/message/788783#788783

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

Reply to this message by going to Community
[https://community.jboss.org/message/788783#788783]

Start a new discussion in jBPM at Community
[https://community.jboss.org/choose-container!input.jspa?contentType=1&containerType=14&container=2034]

[wishmaster7]

wishmaster7 [https://community.jboss.org/people/wishmaster7] created the discussion

"Re: PostgreSQL - empty database - install.demo.db does not exist"

To view the discussion, visit: https://community.jboss.org/message/789253#789253

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

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

Reply to this message by going to Community
[https://community.jboss.org/message/789253#789253]

Start a new discussion in jBPM at Community
[https://community.jboss.org/choose-container!input.jspa?contentType=1&containerType=14&container=2034]

[roxy1987]

roxy1987 [https://community.jboss.org/people/roxy1987] created the discussion

"Re: PostgreSQL - empty database - install.demo.db does not exist"

To view the discussion, visit: https://community.jboss.org/message/789315#789315

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



I#nstead 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>
--------------------------------------------------------------

Reply to this message by going to Community
[https://community.jboss.org/message/789315#789315]

Start a new discussion in jBPM at Community
[https://community.jboss.org/choose-container!input.jspa?contentType=1&containerType=14&container=2034]

[doboss]

doboss [https://community.jboss.org/people/doboss] created the discussion

"Re: PostgreSQL - empty database - install.demo.db does not exist"

To view the discussion, visit: https://community.jboss.org/message/788789#788789

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

Reply to this message by going to Community
[https://community.jboss.org/message/788789#788789]

Start a new discussion in jBPM at Community
[https://community.jboss.org/choose-container!input.jspa?contentType=1&containerType=14&container=2034]

[wishmaster7]

wishmaster7 [https://community.jboss.org/people/wishmaster7] created the discussion

"Re: PostgreSQL - empty database - install.demo.db does not exist"

To view the discussion, visit: https://community.jboss.org/message/789257#789257

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

Reply to this message by going to Community
[https://community.jboss.org/message/789257#789257]

Start a new discussion in jBPM at Community
[https://community.jboss.org/choose-container!input.jspa?contentType=1&containerType=14&container=2034]

[doboss]

doboss [https://community.jboss.org/people/doboss] created the discussion

"Re: PostgreSQL - empty database - install.demo.db does not exist"

To view the discussion, visit: https://community.jboss.org/message/789328#789328

--------------------------------------------------------------
Here it is, hope it helps. :)
--------------------------------------------------------------

Reply to this message by going to Community
[https://community.jboss.org/message/789328#789328]

Start a new discussion in jBPM at Community
[https://community.jboss.org/choose-container!input.jspa?contentType=1&containerType=14&container=2034]

[wishmaster7]

wishmaster7 [https://community.jboss.org/people/wishmaster7] created the discussion

"Re: PostgreSQL - empty database - install.demo.db does not exist"

To view the discussion, visit: https://community.jboss.org/message/791456#791456

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

Reply to this message by going to Community
[https://community.jboss.org/message/791456#791456]

Start a new discussion in jBPM at Community
[https://community.jboss.org/choose-container!input.jspa?contentType=1&containerType=14&container=2034]

[doboss]

doboss [https://community.jboss.org/people/doboss] created the discussion

"Re: PostgreSQL - empty database - install.demo.db does not exist"

To view the discussion, visit: https://community.jboss.org/message/791586#791586

--------------------------------------------------------------
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/docs/DOC-48240 https://community.jboss.org/wiki/SimpleJBPM54InsideJBossAS711WebAppExample That example will also create the DB tables for you...
--------------------------------------------------------------

Reply to this message by going to Community
[https://community.jboss.org/message/791586#791586]

Start a new discussion in jBPM at Community
[https://community.jboss.org/choose-container!input.jspa?contentType=1&containerType=14&container=2034]

[roxy1987]

roxy1987 [https://community.jboss.org/people/roxy1987] created the discussion

"Re: PostgreSQL - empty database - install.demo.db does not exist"

To view the discussion, visit: https://community.jboss.org/message/792126#792126

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

Reply to this message by going to Community
[https://community.jboss.org/message/792126#792126]

Start a new discussion in jBPM at Community
[https://community.jboss.org/choose-container!input.jspa?contentType=1&containerType=14&container=2034]

[wishmaster7]

wishmaster7 [https://community.jboss.org/people/wishmaster7] created the discussion

"Re: PostgreSQL - empty database - install.demo.db does not exist"

To view the discussion, visit: https://community.jboss.org/message/792279#792279

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

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

Reply to this message by going to Community
[https://community.jboss.org/message/792279#792279]

Start a new discussion in jBPM at Community
[https://community.jboss.org/choose-container!input.jspa?contentType=1&containerType=14&container=2034]

[doboss]

doboss [https://community.jboss.org/people/doboss] created the discussion

"Re: PostgreSQL - empty database - install.demo.db does not exist"

To view the discussion, visit: https://community.jboss.org/message/792349#792349

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

Reply to this message by going to Community
[https://community.jboss.org/message/792349#792349]

Start a new discussion in jBPM at Community
[https://community.jboss.org/choose-container!input.jspa?contentType=1&containerType=14&container=2034]

[roxy1987]

roxy1987 [https://community.jboss.org/people/roxy1987] created the discussion

"Re: PostgreSQL - empty database - install.demo.db does not exist"

To view the discussion, visit: https://community.jboss.org/message/792363#792363

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

Reply to this message by going to Community
[https://community.jboss.org/message/792363#792363]

Start a new discussion in jBPM at Community
[https://community.jboss.org/choose-container!input.jspa?contentType=1&containerType=14&container=2034]

[Raghavendra Chary B]

Raghavendra Chary B [https://community.jboss.org/people/charybr] created the discussion

"Re: PostgreSQL - empty database - install.demo.db does not exist"

To view the discussion, visit: https://community.jboss.org/message/792740#792740

--------------------------------------------------------------
To set up jbpm 5.4 to use Postgresql, i followed the steps (with respect to postgresql) given in https://community.jboss.org/docs/DOC-47975 https://community.jboss.org/wiki/SetUpJBPM54FinalInstallerToUseMSSQLServer2008UsingJTDS.
Db tables were created both in jbpm5 and task databases.

Best Regards,
Chary
--------------------------------------------------------------

Reply to this message by going to Community
[https://community.jboss.org/message/792740#792740]

Start a new discussion in jBPM at Community
[https://community.jboss.org/choose-container!input.jspa?contentType=1&containerType=14&container=2034]