configuration to use a postgre SQL schema for camunda_bpm 7

[cweber]

Hi everybody,

------------------------------------------------------------------
My problem:
I can't get the engine work with a postgre SQL schema.
------------------------------------------------------------------

My situation:
- I try your "loan approval example" setup with the api and the rest api and it works out of the box.

- I changed the database to postgre SQL in the default public schema and it works.

- I created a postgreSQL schema "camunda_bpm" and created the tables with the same script I used before and I get an error (look at the end of my post)
The engine did not find the needed tables that were stored in the "camunda_bpm" schema.
------------------------------------------------------------------

What I tried:
- made my "camunda_bpm" schema to a public one
- created a camunda user that get the schema as a default schema
- try to use the "SimpleDriverDataSource" with the ?search_path=camunda_bpm extension

------------------------------------------------------------------
My settings in the applicationContext.xml:
------------------------------------------------------------------
<!--datasource -->
<bean id="dataSource"
class="org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy">
<property name="targetDataSource">
<bean class="org.springframework.jdbc.datasource.SimpleDriverDataSource">
<property name="driverClass" value="org.postgresql.Driver" />
<property name="url" value="jdbc:postgresql://localhost:5432/myDatabase" />
<property name="username" value="postgres" />
<property name="password" value="abc123" />
</bean>
</property>
</bean>

<!--transaction manager -->
<bean id="transactionManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource" />
</bean>

<!--process engine configuration -->
<bean id="processEngineConfiguration"
class="org.camunda.bpm.engine.spring.SpringProcessEngineConfiguration">
<property name="processEngineName" value="default" />
<property name="dataSource" ref="dataSource" />
<property name="transactionManager" ref="transactionManager" />
<property name="databaseSchemaUpdate" value="false" />
<property name="jobExecutorActivate" value="false" />
<property name="deploymentResources" value="classpath*:*.bpmn" />
<!-- property name="databaseTablePrefix" value="camunda_bpm." />-->
</bean>


---------------------------------------------------------
Consolen log
---------------------------------------------------------
SEVERE: Error while closing command context
org.apache.ibatis.exceptions.PersistenceException:
### Error querying database. Cause: org.postgresql.util.PSQLException: FEHLER: Relation „act_ge_property“ existiert nicht
Position: 20
### The error may exist in org/camunda/bpm/engine/impl/mapping/entity/Property.xml
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: select VALUE_ from ACT_GE_PROPERTY where NAME_ = 'schema.version'
### Cause: org.postgresql.util.PSQLException: FEHLER: Relation „act_ge_property“ existiert nicht
Position: 20
at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:104)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:95)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:59)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:54)
at org.camunda.bpm.engine.impl.db.DbSqlSession.getDbVersion(DbSqlSession.java:830)
at org.camunda.bpm.engine.impl.db.DbSqlSession.dbSchemaCheckVersion(DbSqlSession.java:764)
at org.camunda.bpm.engine.impl.db.DbSqlSession.performSchemaOperationsProcessEngineBuild(DbSqlSession.java:1173)
at org.camunda.bpm.engine.impl.SchemaOperationsProcessEngineBuild.execute(SchemaOperationsProcessEngineBuild.java:25)
at org.camunda.bpm.engine.impl.interceptor.CommandExecutorImpl.execute(CommandExecutorImpl.java:24)
at org.camunda.bpm.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:42)
at org.camunda.bpm.engine.spring.SpringTransactionInterceptor$1.doInTransaction(SpringTransactionInterceptor.java:42)
at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130)
at org.camunda.bpm.engine.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:40)
at org.camunda.bpm.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:32)
at org.camunda.bpm.engine.impl.ProcessEngineImpl.executeSchemaOperations(ProcessEngineImpl.java:100)
at org.camunda.bpm.engine.impl.ProcessEngineImpl.<init>(ProcessEngineImpl.java:83)
at org.camunda.bpm.engine.impl.cfg.ProcessEngineConfigurationImpl.buildProcessEngine(ProcessEngineConfigurationImpl.java:358)
at org.camunda.bpm.engine.spring.SpringProcessEngineConfiguration.buildProcessEngine(SpringProcessEngineConfiguration.java:60)
at org.camunda.bpm.engine.spring.ProcessEngineFactoryBean.getObject(ProcessEngineFactoryBean.java:56)
at org.camunda.bpm.engine.spring.ProcessEngineFactoryBean.getObject(ProcessEngineFactoryBean.java:32)
at org.springframework.beans.factory.support.FactoryBeanRegistrySupport.doGetObjectFromFactoryBean(FactoryBeanRegistrySupport.java:142)
at org.springframework.beans.factory.support.FactoryBeanRegistrySupport.getObjectFromFactoryBean(FactoryBeanRegistrySupport.java:102)
at org.springframework.beans.factory.support.AbstractBeanFactory.getObjectForBeanInstance(AbstractBeanFactory.java:1442)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:248)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:193)
at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:353)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.instantiateUsingFactoryMethod(AbstractAutowireCapableBeanFactory.java:1015)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:911)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:485)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:456)
at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:294)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:225)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:291)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:193)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:609)
at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:918)
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:469)
at org.springframework.web.context.ContextLoader.configureAndRefreshWebApplicationContext(ContextLoader.java:383)
at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:283)
at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:111)
at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4887)
at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5381)
at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1559)
at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1549)
at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334)
at java.util.concurrent.FutureTask.run(FutureTask.java:166)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)
at java.lang.Thread.run(Thread.java:722)
Caused by: org.postgresql.util.PSQLException: FEHLER: Relation „act_ge_property“ existiert nicht
Position: 20
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:410)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:56)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:70)
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:57)
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:267)
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:141)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:105)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:81)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:101)
... 49 more

2013-10-19 10:26:43 INFO DefaultListableBeanFactory:433 - Destroying singletons in org.springframework.beans.factory.support.DefaultListableBeanFactory@758381af: defining beans [dataSource,transactionManager,processEngineConfiguration,processEngine,repositoryService,runtimeService,taskService,historyService,managementService,de.mettenmeier.bpm.autostart.Starter#0,fetchPositionDataService,org.springframework.context.annotation.internalConfigurationAnnotationProcessor,org.springframework.context.annotation.internalAutowiredAnnotationProcessor,org.springframework.context.annotation.internalRequiredAnnotationProcessor,org.springframework.context.annotation.internalCommonAnnotationProcessor,org.springframework.context.annotation.ConfigurationClassPostProcessor$ImportAwareBeanPostProcessor#0]; root of factory hierarchy
2013-10-19 10:26:43 ERROR ContextLoader:307 - Context initialization failed
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'processEngine': FactoryBean threw exception on object creation; nested exception is org.camunda.bpm.engine.ProcessEngineException: Process engine persistence exception
at org.springframework.beans.factory.support.FactoryBeanRegistrySupport.doGetObjectFromFactoryBean(FactoryBeanRegistrySupport.java:149)
at org.springframework.beans.factory.support.FactoryBeanRegistrySupport.getObjectFromFactoryBean(FactoryBeanRegistrySupport.java:102)
at org.springframework.beans.factory.support.AbstractBeanFactory.getObjectForBeanInstance(AbstractBeanFactory.java:1442)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:248)

[Bernd Rücker (camunda)]

Hi cweber.

I think this is an issue with PostgreSQL JDBC driver - are you sure you
are correctly setting the seach path? Quick google pointed me to:
http://stackoverflow.com/questions/4168689/is-it-possible-to-specify-schem
a-when-connecting-to-postgres-with-jdbc

Actually I am neither a Spring nor a PostgreSQL guru - but I think
somewhere there is the problem (so in this case one step BEFORE camunda
BPM - the exception just tells you that the tables for the engine are
missing).

Hope that helps a bit?

Maybe somebody else has experience with PostgreSQL schemas and Spring here
on the list?

Cheers
BErnd

-----Urspr�ngliche Nachricht-----
Von: camunda-...@googlegroups.com
[mailto:camunda-...@googlegroups.com] Im Auftrag von cweber
Gesendet: Samstag, 19. Oktober 2013 10:41
An: camunda-...@googlegroups.com
Betreff: [camunda-bpm-users] configuration to use a postgre SQL schema for
camunda_bpm 7

--
You received this message because you are subscribed to the Google Groups
"camunda BPM users & process application developers" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to camunda-bpm-us...@googlegroups.com.
To post to this group, send email to camunda-...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/camunda-bpm-users/90e8b70d-3825-44c7-807
6-e4a0deaedfd0%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[cweber]

Thanks Bernd for your reply.

"Actually I am neither a Spring nor a PostgreSQL guru" - Me, too. Thats my problem.

I have read the linked page before and I tried it with the search parameter and a driver version higher then 9.1 But no effect. Tables were not found by the engine.

Am Dienstag, 22. Oktober 2013 13:12:29 UTC+2 schrieb Bernd Rücker:
> Hi cweber.
>
>
>
> I think this is an issue with PostgreSQL JDBC driver - are you sure you
>
> are correctly setting the seach path? Quick google pointed me to:
>
> http://stackoverflow.com/questions/4168689/is-it-possible-to-specify-schem
>
> a-when-connecting-to-postgres-with-jdbc
>
>
>
> Actually I am neither a Spring nor a PostgreSQL guru - but I think
>
> somewhere there is the problem (so in this case one step BEFORE camunda
>
> BPM - the exception just tells you that the tables for the engine are
>
> missing).
>
>
>
> Hope that helps a bit?
>
>
>
> Maybe somebody else has experience with PostgreSQL schemas and Spring here
>
> on the list?
>
>
>
> Cheers
>
> BErnd
>
>
>

> -----Urspr�ngliche Nachricht-----

> FEHLER: Relation �act_ge_property� existiert nicht

>
> Position: 20
>
> ### The error may exist in
>
> org/camunda/bpm/engine/impl/mapping/entity/Property.xml
>
> ### The error may involve defaultParameterMap ### The error occurred while
>
> setting parameters ### SQL: select VALUE_ from ACT_GE_PROPERTY where NAME_
>
> = 'schema.version'
>
> ### Cause: org.postgresql.util.PSQLException: FEHLER: Relation
>

> �act_ge_property� existiert nicht

> �act_ge_property� existiert nicht

Am Dienstag, 22. Oktober 2013 13:12:29 UTC+2 schrieb Bernd Rücker:
> Hi cweber.
>
>
>
> I think this is an issue with PostgreSQL JDBC driver - are you sure you
>
> are correctly setting the seach path? Quick google pointed me to:
>
> http://stackoverflow.com/questions/4168689/is-it-possible-to-specify-schem
>
> a-when-connecting-to-postgres-with-jdbc
>
>
>
> Actually I am neither a Spring nor a PostgreSQL guru - but I think
>
> somewhere there is the problem (so in this case one step BEFORE camunda
>
> BPM - the exception just tells you that the tables for the engine are
>
> missing).
>
>
>
> Hope that helps a bit?
>
>
>
> Maybe somebody else has experience with PostgreSQL schemas and Spring here
>
> on the list?
>
>
>
> Cheers
>
> BErnd
>
>
>

> -----Urspr�ngliche Nachricht-----

> FEHLER: Relation �act_ge_property� existiert nicht

>
> Position: 20
>
> ### The error may exist in
>
> org/camunda/bpm/engine/impl/mapping/entity/Property.xml
>
> ### The error may involve defaultParameterMap ### The error occurred while
>
> setting parameters ### SQL: select VALUE_ from ACT_GE_PROPERTY where NAME_
>
> = 'schema.version'
>
> ### Cause: org.postgresql.util.PSQLException: FEHLER: Relation
>

> �act_ge_property� existiert nicht

> �act_ge_property� existiert nicht

Am Dienstag, 22. Oktober 2013 13:12:29 UTC+2 schrieb Bernd Rücker:
> Hi cweber.
>
>
>
> I think this is an issue with PostgreSQL JDBC driver - are you sure you
>
> are correctly setting the seach path? Quick google pointed me to:
>
> http://stackoverflow.com/questions/4168689/is-it-possible-to-specify-schem
>
> a-when-connecting-to-postgres-with-jdbc
>
>
>
> Actually I am neither a Spring nor a PostgreSQL guru - but I think
>
> somewhere there is the problem (so in this case one step BEFORE camunda
>
> BPM - the exception just tells you that the tables for the engine are
>
> missing).
>
>
>
> Hope that helps a bit?
>
>
>
> Maybe somebody else has experience with PostgreSQL schemas and Spring here
>
> on the list?
>
>
>
> Cheers
>
> BErnd
>
>
>

> -----Urspr�ngliche Nachricht-----

> FEHLER: Relation �act_ge_property� existiert nicht

>
> Position: 20
>
> ### The error may exist in
>
> org/camunda/bpm/engine/impl/mapping/entity/Property.xml
>
> ### The error may involve defaultParameterMap ### The error occurred while
>
> setting parameters ### SQL: select VALUE_ from ACT_GE_PROPERTY where NAME_
>
> = 'schema.version'
>
> ### Cause: org.postgresql.util.PSQLException: FEHLER: Relation
>

> �act_ge_property� existiert nicht

> �act_ge_property� existiert nicht

Am Dienstag, 22. Oktober 2013 13:12:29 UTC+2 schrieb Bernd Rücker:
> Hi cweber.
>
>
>
> I think this is an issue with PostgreSQL JDBC driver - are you sure you
>
> are correctly setting the seach path? Quick google pointed me to:
>
> http://stackoverflow.com/questions/4168689/is-it-possible-to-specify-schem
>
> a-when-connecting-to-postgres-with-jdbc
>
>
>
> Actually I am neither a Spring nor a PostgreSQL guru - but I think
>
> somewhere there is the problem (so in this case one step BEFORE camunda
>
> BPM - the exception just tells you that the tables for the engine are
>
> missing).
>
>
>
> Hope that helps a bit?
>
>
>
> Maybe somebody else has experience with PostgreSQL schemas and Spring here
>
> on the list?
>
>
>
> Cheers
>
> BErnd
>
>
>

> -----Urspr�ngliche Nachricht-----

> FEHLER: Relation �act_ge_property� existiert nicht

>
> Position: 20
>
> ### The error may exist in
>
> org/camunda/bpm/engine/impl/mapping/entity/Property.xml
>
> ### The error may involve defaultParameterMap ### The error occurred while
>
> setting parameters ### SQL: select VALUE_ from ACT_GE_PROPERTY where NAME_
>
> = 'schema.version'
>
> ### Cause: org.postgresql.util.PSQLException: FEHLER: Relation
>

> �act_ge_property� existiert nicht

> �act_ge_property� existiert nicht