Problem in connecting to remote db

Camunda 7 Topics Discussion & Questions
1

Hi camunda team.

Support of spring boot configuration for camunda is great. I have interesting question

I am using camunda-spring-boot-starter in one of my Spring boot Micro service with embedded h2 database,However I deployed the Camunda Web Apps ( Admin, cockpit , Tasklist ) in a seperate server and access the process states from Engine deployed in the micro service.

But the problem here I’m unable to connect the remote h2 database which is running in Spring boot micro service from Camunda Web Apps.

In applicationContext.xml, I’m making the below changes

After making those changes im getting below errors

13-May-2020 18:18:50.798 SEVERE [main] org.springframework.web.context.ContextLoader.initWebApplicationContext Context initialization failed
org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘processEngine’: FactoryBean threw exception on object creation; nested exception is org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Connection is broken: “unexpected status 1213486160” [90067-190])

Thanks in advance for the help.

2

@SKS make sure that are you using correct jdbc driver version which is compatible with database version.

Other possible reasons maybe using of H2 database are,

This can be caused due to stale java processes holding the H2 database files and having an explicit lock on it. Normally stopping the TSSS service should release the lock on the H2 database files but there may be certain scenarios where the stale java processes still have a hold on those files. Stop the service and Delete the ‘db.lock.db’ file from the H2 folder.

You need to use a TCP connection for multiple users. Start the server in TCP mode. Honesetly, it is pretty straight forward in the documentation.

DriverManager.getConnection("jdbc:h2:tcp://localhost/server~/dbname","username","password");

<property name="javax.persistence.jdbc.url" value="jdbc:h2:tcp://localhost/C:\Database\Data\production;"/>
3

Thanks @aravindhrs for the quick response. h2 database is running in one of the Spring boot micro service and camunda webapps(camunda engine) is running in another tomcat server. In applicationcontext.xml i have updated jdbc url with the value “jdbc:h2:tcp://localhost:10090/testdb”.
Below is the snippet

<bean id=“dataSource”

class=“org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy”>

<property name="targetDataSource">
  <bean class="org.apache.commons.dbcp.BasicDataSource">
    <property name="driverClassName" value="org.h2.Driver" />
	<!--property name="url" value="jdbc:h2:mem:process-engine1;MVCC=TRUE;TRACE_LEVEL_FILE=0;DB_CLOSE_ON_EXIT=FALSE" /-->
    <property name="url" value="jdbc:h2:tcp://localhost:10090/testdb" />
	
    <property name="username" value="sa" />
    <property name="password" value="" />
  </bean>
</property>

Also using correct version of database driver on both the side. i.e h2-1.4.200

is this correct approach what I’m following it. Thanks in advance.

4

@SKS you can add additional query parameters which is used in previous jdbc url.

MVCC=TRUE;TRACE_LEVEL_FILE=0;DB_CLOSE_ON_EXIT=FALSE

https://h2database.com/html/tutorial.html#using_server

5

No Luck. Just to understand better testdb is created and running in one of my Spring boot microservice.
I’ trying to connect to this h2 database from my camunda webapps server.

Below error occurs when I start my camunda webapps

13-May-2020 22:50:08.024 SEVERE [main] org.camunda.commons.logging.BaseLogger.logError ENGINE-12012 Exception on accessing the database connection: Cannot create PoolableConnectionFactory (Connection is broken: “unexpected status 1213486160” [90067-190])
13-May-2020 22:50:12.393 WARNING [main] org.springframework.context.support.AbstractApplicationContext.refresh Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘processEngine’: FactoryBean threw exception on object creation; nested exception is org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Connection is broken: “unexpected status 1213486160” [90067-190])
13-May-2020 22:50:12.393 SEVERE [main] org.springframework.web.context.ContextLoader.initWebApplicationContext Context initialization failed
org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘processEngine’: FactoryBean threw exception on object creation; nested exception is org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Connection is broken: “unexpected status 1213486160” [90067-190])

6

Multiple Connections to the Same Database: Client/Server

If you want to access the same database at the same time from different processes or computers, you need to use the client / server mode. In this case, one process acts as the server, and the other processes (that could reside on other computers as well) connect to the server via TCP/IP (or TLS over TCP/IP for improved security).

Automatic Mixed Mode

Multiple processes can access the same database without having to start the server manually. To do that, append ;AUTO_SERVER=TRUE to the database URL. You can use the same database URL independent of whether the database is already open or not. This feature doesn’t work with in-memory databases.

Try like this database URL:

jdbc:h2:/data/test;AUTO_SERVER=TRUE;AUTO_SERVER_PORT=9090

DriverManager.getConnection("jdbc:h2:/data/test;AUTO_SERVER=TRUE;AUTO_SERVER_PORT=9090");
7

@aravindhrs thanks for quick reponse. I followed this link Features to try out different options, but my bad.
I tried out Automatic Mixed Mode

  <property name="url" value="jdbc:h2:tcp://localhost:10990/mem:mydb;AUTO_SERVER=TRUE" />

image
image818×281 15.8 KB
my h2 db running on port 10090.

For standalone deployment I followed this link Install the Standalone Webapplication (.war) | docs.camunda.org

did i missing any basics here? I think this should be straight forward… please help me out.

Also,I observing the same error all the time when I make changes to the URL

8

when you have AUTO_SERVER=TRUE in query param, then tcp is not required in the jdbc url.

It should be like:

jdbc:h2:/data/mydb;AUTO_SERVER=TRUE;AUTO_SERVER_PORT=10990

Also note that :mem will not work for TCP connections. so remove :mem from connection url

9

I followed your approach i.e I have updated URL with <property name=“url”

value=“jdbc:h2:/data/testdb;AUTO_SERVER=TRUE;AUTO_SERVER_PORT=10090” />,

here how will my webapps know on which URL is my h2 db is running?

After updating the URL below error is logging into console

Cannot create PoolableConnectionFactory (A file path that is implicitly relative to the current working directory is not allowed in the database URL “jdbc:h2:/data/testdb;AUTO_SERVER=TRUE;AUTO_SERVER_PORT=10090”. Use an absolute path, ~/name, ./name, or the baseDir setting instead. [90011-190])

I could run a microservice with embedded engine. My standalone cockpit could be configured to access the micro-service’s database and thus I have a remote cockpit.
Could you please provide me one working remote(same computer or different computer) h2 db configuration?

10

Thanks for the answers. I could successfully able to connect to share database. I was making some silly mistakes.

11

Hi, can you please share configuration you used in spring-boot micro-service for h2 and in tomcat webapps with tcp which worked for you ? I am also facing the same issue but not able to find any solution.

12

Hi, can you please share configuration you used in spring-boot micro-service for h2 and in tomcat webapps with tcp which worked for you ? I am also facing the same issue but not able to find any solution

13