POSTGRES: db error upon restarting Activiti App

[mikeblum]

I'm moving our Activiti applicaation to a lcoal Postgres instance. With a clean database I can connect and ACtiviti generates the appropriate tables but after the first run I get the following error attached.

INFO 7/28/15 12:06 PM:liquibase: classpath:META-INF/liquibase/db-changelog-onpremise.xml::1::tijsrademakers: Successfully released change log lock
12:06:07,016 [localhost-startStop-1] WARN  org.springframework.web.context.support.AnnotationConfigWebApplicationContext  - Exception encountered during context initialization - cancelling refresh attempt
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'liquibase' defined in class path resource [com/activiti/conf/DatabaseConfiguration.class]: Invocation of init method failed;
nested exception is liquibase.exception.MigrationFailedException: Migration failed for change set classpath:META-INF/liquibase/db-changelog-onpremise.xml::1::tijsrademakers:
     Reason: liquibase.exception.DatabaseException: Error executing SQL CREATE TABLE public.hibernate_sequences (sequence_name VARCHAR(255), sequence_next_hi_value INT): ERROR: relation "hibernate_sequences" already exists

Here's a list of the tables generated in the Postgres instance:

                   List of relations
Schema |          Name           |   Type   |  Owner  
——–+————————-+———-+———-
public | act_evt_log             | table    | activiti
public | act_evt_log_log_nr__seq | sequence | activiti
public | act_ge_bytearray        | table    | activiti
public | act_ge_property         | table    | activiti
public | act_hi_actinst          | table    | activiti
public | act_hi_attachment       | table    | activiti
public | act_hi_comment          | table    | activiti
public | act_hi_detail           | table    | activiti
public | act_hi_identitylink     | table    | activiti
public | act_hi_procinst         | table    | activiti
public | act_hi_taskinst         | table    | activiti
public | act_hi_varinst          | table    | activiti
public | act_id_group            | table    | activiti
public | act_id_info             | table    | activiti
public | act_id_membership       | table    | activiti
public | act_id_user             | table    | activiti
public | act_re_deployment       | table    | activiti
public | act_re_model            | table    | activiti
public | act_re_procdef          | table    | activiti
public | act_ru_event_subscr     | table    | activiti
public | act_ru_execution        | table    | activiti
public | act_ru_identitylink     | table    | activiti
public | act_ru_job              | table    | activiti
public | act_ru_task             | table    | activiti
public | act_ru_variable         | table    | activiti
public | authority               | table    | activiti
public | cluster_config          | table    | activiti
public | databasechangelog       | table    | activiti
public | databasechangeloglock   | table    | activiti
public | hibernate_sequences     | table    | activiti
public | master_config           | table    | activiti
public | server_config           | table    | activiti
public | user_authority          | table    | activiti
public | user_info               | table    | activiti


It looks like Activiti already generated the correct tables. Do I have something mis-configured? From the docs I've configured the activti-app.properties and activiti-admin.properties like this in the tomcat/lib dir:

datasource.driver=org.postgresql.Driver
datasource.url=jdbcostgresql://10.1.1.14:15432/activiti
datasource.username=activiti
datasource.password=************

security.headers.frame=false
hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
#hibernate.show_sql=false
#hibernate.generate_statistics=false

I've added the postgres driver to the tomcat/lib directory and gave my activiti user ALL PRIVILEGES on the activiti database.

What version of postgres do I need to be using? I've got 9.3 and 9.4 deployed.

Thanks!

[mikeblum]

I rolled back to the in-memory H2 and don't get this exception. I need to move to a persisted DB since I'm creating tables in the database Activti is pointing to.

[mikeblum]

Tried with a clean MySQL install and ran into a similair issue, this time with the Authority table generated by Activiti:

INFO 7/28/15 1:26 PM:liquibase: classpath:META-INF/liquibase/db-changelog-onpremise.xml::1::tijsrademakers: Successfully released change log lock
01:26:22,083 [localhost-startStop-1] WARN  org.springframework.web.context.support.AnnotationConfigWebApplicationContext  - Exception encountered during context initialization - cancelling refresh attempt
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'liquibase' defined in class path resource [com/activiti/conf/DatabaseConfiguration.class]: Invocation of init method failed;
nested exception is liquibase.exception.MigrationFailedException: Migration failed for change set classpath:META-INF/liquibase/db-changelog-onpremise.xml::1::tijsrademakers:
     Reason: liquibase.exception.DatabaseException: Error executing SQL CREATE TABLE activiti.AUTHORITY (name VARCHAR(255) NOT NULL, CONSTRAINT PK_AUTHORITY PRIMARY KEY (name)): Table 'AUTHORITY' already exists
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1574)

And did the same configs with the Hibernate dialect and JDBC bindings:

datasource.driver=com.mysql.jdbc.Driver
datasource.url=jdbc:mysql://localhost:3306/activiti?characterEncoding=UTF-8
datasource.username=activiti
datasource.password=*********************************

with the hibernate dialect set accordingly:

hibernate.dialect=org.hibernate.dialect.MySQLDialect

Can i disable LiquidDatabase? It looks like Activiti correctly creates the tables upon start-up. What does the Liquid Database Configuration do?

Thanks!

[mikeblum]

I went bact o a Postgres install and get the following error from the activiti-admin that is stopping the WAR from starting up:

INFO 7/30/15 11:09 AM:liquibase: classpath:META-INF/liquibase/db-changelog.xml: 1::activiti: Table USER_INFO created
SEVERE 7/30/15 11:09 AM:liquibase: classpath:META-INF/liquibase/db-changelog.xml: 1::activiti: Change Set classpath:META-INF/liquibase/db-changelog.xml::1::activiti failed.  Error: Error executing SQL CREATE TABLE
public.AUTHORITY (name VARCHAR(255) NOT NULL, CONSTRAINT PK_AUTHORITY PRIMARY KEY (name)): ERROR: relation "authority" already exists
liquibase.exception.DatabaseException: Error executing SQL CREATE TABLE public.AUTHORITY (name VARCHAR(255) NOT NULL, CONSTRAINT PK_AUTHORITY PRIMARY KEY (name)): ERROR: relation "authority" already exists
        at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:61)

which results in the WAR stopping down river:

30-Jul-2015 11:09:55.123 SEVERE [localhost-startStop-1] org.apache.catalina.core.StandardContext.startInternal Context [/activiti-admin] startup failed due to previous errors
11:09:55,132 [localhost-startStop-1] INFO  com.activiti.conf.WebConfigurer  - Destroying Web application
30-Jul-2015 11:09:55.144 INFO [localhost-startStop-1] org.apache.catalina.startup.HostConfig.deployWAR Deployment of web application archive /home/mblum/work/activiti/activiti-1.3.0EA1/tomcat/webapps/activiti-admi
n.war has finished in 5,899 ms

Here were my steps for creating the activiti database:

1. login to postgres as the root postgres user:

su -
su postgres
psql
CREATE DATABASE activiti;
CREATE USER activiti WITH PASSWORD *****;
ALTER ROLE activiti SUPERUSER;
GRANT ALL PRIVILEGES ON DATABASE activiti TO activiti;

from there I can login to my postgres instance as the activiti user, create tables and the like. But it appears the activiti-app is properly creating the required tables I've dumped above but then the activiti-admin appears to fail. I gave its properties file the same configuration that the activiti-app.properties file possesses.

[cijujoseph]

It's probably because you are having the same configuration of activiti-app.properties for admin.
These two apps need two separate databases.

Ciju

[mikeblum]

You are correct Ciju, I didn't get the subtlety with the db configurations. The documentation doesn't make that distinction very explicit.

Thanks!

[mikeblum]

What version of postgres does the Activiti app require?

I've tried both 9.3 and 9.4 but get the following error when browsing to the tasks and process-instancces tabs in the app:

Hibernate: insert into USER_FILTER (app_id, filter_value, icon, filter_index, name, recent, type, user_id, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: select userfilter0_.id as id1_42_0_, userfilter0_.app_id as app_id2_42_0_, userfilter0_.filter_value as filter_v3_42_0_, userfilter0_.icon as icon4_42_0_, userfilter0_.filter_index as filter_i5_42_0_, u
serfilter0_.name as name6_42_0_, userfilter0_.recent as recent7_42_0_, userfilter0_.type as type8_42_0_, userfilter0_.user_id as user_id9_42_0_ from USER_FILTER userfilter0_ where userfilter0_.id=?
Hibernate: update USER_FILTER set recent=0 where recent=1 and user_id=? and type=? and (app_id is null)
11:04:57,585 [http-nio-8080-exec-8] WARN  org.hibernate.engine.jdbc.spi.SqlExceptionHelper  - SQL Error: 0, SQLState: 42883
11:04:57,586 [http-nio-8080-exec-8] ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper  - ERROR: operator does not exist: boolean = integer
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  Position: 45
11:04:57,609 [http-nio-8080-exec-8] ERROR com.activiti.rest.exception.RestExceptionHandlerAdvice  - Unhandled exception

From looking the the libs in tomcat/lib and the activiti-app libs we are using the following deps:

- hibernate-core-4.2.16.Final.jar
- hibernate-jpa-2.0-api-1.0.1.Final.jar

I've deployed the following postgres drivers tot the tomcat/lib directory:

postgresql-9.3-1100-jdbc4.jar (when I had 9.3 deployed)

and

postgresql-9.4-1201.jdbc4.jar

and

postgresql-9.4-1201.jdbc41.jar

respectively when I had 9.4 deployed.

Taking into account the fix from Ciju, here are my properties configs:

#activiti admin JDBC props
datasource.driver=org.postgresql.Driver
datasource.url=jdbcostgresql://localhost:15432/activiti_admin
datasource.username=activiti
datasource.password=*******

hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
hibernate.show_sql=true

#activiti app JDBC props
datasource.driver=org.postgresql.Driver
datasource.url=jdbcostgresql://localhost:15432/activiti
datasource.username=activiti
datasource.password=activiti


hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
hibernate.show_sql=true

and I disabled the h2 startup script inside the start-activiti.sh

[cijujoseph]

As per https://www.alfresco.com/services/subscription/supported-platforms, 9.3 is supported..Since 1.3.0 final version is officially available may be it's worth retrying again on the new s/w version.

Cheers,
Ciju

[vasile_dirla]

@mikeblum, please let us know some info about your setup:
Activiti version
OS version 

[mikeblum]

Using vagrant for the postgres dB configured to use 9.3. Checking psql returns 3.9. I'm running activiti 1.3.0 on Ubuntu 14.04 on the packaged tomcat.