topic Re: POSTGRES: db error upon restarting Activiti App in Alfresco Archive

POSTGRES: db error upon restarting Activiti App

mikeblum — Tue, 28 Jul 2015 17:14:37 GMT

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:

Re: POSTGRES: db error upon restarting Activiti App

mikeblum — Tue, 28 Jul 2015 17:16:11 GMT

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.

Re: POSTGRES: db error upon restarting Activiti App

mikeblum — Tue, 28 Jul 2015 18:33:30 GMT

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!

Re: POSTGRES: db error upon restarting Activiti App

mikeblum — Thu, 30 Jul 2015 16:17:12 GMT

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.

Re: POSTGRES: db error upon restarting Activiti App

cijujoseph — Fri, 31 Jul 2015 06:54:14 GMT

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

Ciju

Re: POSTGRES: db error upon restarting Activiti App

mikeblum — Fri, 31 Jul 2015 13:40:58 GMT

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

Thanks!

Re: POSTGRES: db error upon restarting Activiti App

mikeblum — Fri, 31 Jul 2015 16:28:53 GMT

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

Re: POSTGRES: db error upon restarting Activiti App

cijujoseph — Sun, 02 Aug 2015 12:29:37 GMT

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

Re: POSTGRES: db error upon restarting Activiti App

vasile_dirla — Mon, 03 Aug 2015 12:15:30 GMT

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

Re: POSTGRES: db error upon restarting Activiti App

mikeblum — Mon, 03 Aug 2015 12:26:13 GMT

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.

Re: POSTGRES: db error upon restarting Activiti App

mikeblum — Mon, 03 Aug 2015 12:27:11 GMT

Link to the postgres virtual instance:https://wiki.postgresql.org/wiki/PostgreSQL_For_Development_With_Vagrant