Bug 961679 - BPMS dashbuilder does not support other databases than H2
BPMS dashbuilder does not support other databases than H2
Status: CLOSED CURRENTRELEASE
Product: JBoss BPMS Platform 6
Classification: JBoss
Component: BAM (Show other bugs)
6.0.0
Unspecified Unspecified
unspecified Severity high
: ---
: 6.0.0
Assigned To: David Gutierrez
Jan Hrcek
:
Depends On:
Blocks:
  Show dependency treegraph
 
Reported: 2013-05-10 05:09 EDT by Marek Winkler
Modified: 2014-08-06 16:07 EDT (History)
3 users (show)

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Cause: BLOB management classes not compatible with Oracle/SQLServer + EAP 6.1. Also a jBPM dashboard query was not working on Oracle. Consequence: Oracle and SQL server not supported. Fix: Fix both the conflictive class and SQL queries Result: Working in Oracle and SQLServer on EAP 6.1.
Story Points: ---
Clone Of:
Environment:
Last Closed: 2014-08-06 16:07:34 EDT
Type: Bug
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---


Attachments (Terms of Use)
dashbuilder log with error messages (439.51 KB, text/x-log)
2013-05-10 05:09 EDT, Marek Winkler
no flags Details
dashboard-builder-jboss.log with processinstancelog not found error (36.43 KB, text/plain)
2013-05-13 08:29 EDT, Marek Winkler
no flags Details
dashbuilder log for Oracle (31.69 KB, text/plain)
2013-05-14 05:12 EDT, Marek Winkler
no flags Details
dashbuilder log for MSSQL (150.73 KB, text/plain)
2013-05-14 05:12 EDT, Marek Winkler
no flags Details
dashbuilder log for Oracle with resolved dependency issues (47.72 KB, text/plain)
2013-05-14 12:11 EDT, Marek Winkler
no flags Details

  None (edit)
Description Marek Winkler 2013-05-10 05:09:27 EDT
Created attachment 746011 [details]
dashbuilder log with error messages

Description of problem:

The dashbuilder.war application seems not to support other databases than H2.

The JNDI datasource can be set in jboss-web.xml, however, there is no apparent place where to set appropriate hibernate dialect. The application seems that it is NOT using JPA (there is no persistence.xml file).

There is a file dashbuilder.war/WEB-INF/lib/dashbuilder-jboss-as7.0-configuration.jar containing org/jboss/dashboard/database/HibernateProperties.factory, which is a properties file setting:

hibernate.dialect=org.jboss.dashboard.database.H2Dialect

Unfortunately, changing the dialect in this file does not help.

I have also attempted to put hibernate.properties with correct dialect into WEB-INF, WEB-INF/classes, WEB-INF/classes/META-INF, but it did not help.

The log (see attachment) indicates the application is trying to execute SQL script for H2:

[ServerService Thread Pool -- 60 10/05/13 07:25:45] WARN  org.jboss.dashboard.database.DatabaseAutoSynchronizer (DatabaseAutoSynchronizer.java:96) - Running file 1-create-h2.sql []

If there is a way how to configure dashbuilder to use different DB than H2, please let me know.

Please be aware that the errors are logged into /tmp/dashboard-builder-jboss.log, which is a rather non-standard log file location.

Version-Release number of selected component (if applicable):

BPMS 6.0 DR4, EAP 6.1

How reproducible:
Configure dashbuilder.war to use a JNDI datasource on a different database than H2 (for example, PostgreSql or MySql). 

Steps to Reproduce:
1. Configure dashbuilder.war to use a JNDI datasource on a different database than H2 (for example, PostgreSql or MySql):
  -- change dashbuilder.war/jboss-web.xml 
2. Deploy application to EAP 6.1.
3. /tmp/dashboard-builder-jboss.log contains error messages indicating the app is trying to execute SQL scripts for H2. When you attempt to log in the application, nothing happens.
Comment 1 David Gutierrez 2013-05-10 11:18:46 EDT
1.- JPA support
-------------------------------------

The application is based on Hibernate 3.3 and is NOT JPA compliant. JPA support it's on the 2013 roadmap anyway. But not something for the 6.0 release.

2.- Hibernate version 
-------------------------------------

We'are about to upgrade the hibernate to the latest 4.2 release in order to align the dashbuilder deps with the JBoss EAP 6.1.

3.- Installation on other databases
-------------------------------------

We've just modified the application initialization process so that the hibernate dialect is detected automatically. These modifications kinda simplifies the installation process.

Now, in order to deploy, for instance, on JBoss/Postgres you should execute the following steps:

a.- Create an empty postgres database. 

b.- Install the postgres driver in JBoss.

c.- Create a JBoss datasource to connect to such database.

d.- Set the ref to the datasource in the following file: git/droolsjbpm/dashboard-builder/builder/src/main/jbossas7/WEB-INF/jboss-web.xml

      "<jndi-name>java:jboss/datasources/my_postgres_ds</jndi-name>" 

e.- Build a distribution for JBoss

$ cd git/droolsjbpm/dashboard-builder
$ mvn clean install -Dmaven.test.skip=true -P full

The war => git/droolsjbpm/dashboard-builder/builder/target/dashbuilder-jboss-as7.0.war

f.- Deploy the war

The application will connect to the database using the datasource specified at "d" and it will create the whole database schema automatically. The Hibernate dialect will be detected automatically as well.

4.- Supported databases
---------------------------
The application supports the following databases:

* H2
* Oracle 10g & 11g
* MS SQL Server 2005 & 2008
* MySQL 5.1 & 5.5
* PostgreSQL 8.4

The installation procedure can be run for any of these databases.








The configuration has been simplified a liitle bit in order to make it easy to set-up an installation on a non-H2 database.
Comment 2 Marek Winkler 2013-05-13 08:27:23 EDT
Thanks for additional information and fix. However, I am still not able to log into dashbuilder, even when building the application from github and following your installation procedure. We have tried PostgreSQL and MySQL.

The application does not produce errors during deployment and it seems that SQL script for the correct database is called now. However, when I try to log into the application, error log contains the following (see attachment for the complete log):

[ServerService Thread Pool -- 61 13/05/13 11:04:25] WARN  org.jboss.dashboard.database.DatabaseAutoSynchronizer (DatabaseAutoSynchronizer.java:91) - Running file 1-create-postgres.sql []
[ServerService Thread Pool -- 61 13/05/13 11:04:56] ERROR org.jboss.dashboard.dataset.sql.SQLDataSet$1 (SQLDataSet.java:115) - Error in load() SQLDataset. SQL = select total.processname, coalesce(total.instances,0) total, coalesce(active.instances_act,0) active, coalesce(completed.instances_compl,0) completed, coalesce(pending.instances_pend,0) pending, coalesce(suspended.instances_susp,0) suspended, coalesce(aborted.instances_abrt,0) aborted
          from
          (select pi.processinstanceid as pId, pi.processname as processname, count(*) as instances
         ....

(log part omitted)

org.postgresql.util.PSQLException: ERROR: relation "processinstancelog" does not exist
  Position: 417
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)

[http-/127.0.0.1:8080-1 13/05/13 11:05:10] WARN  org.jboss.dashboard.ui.NavigationManager (NavigationManager.java:326) - Couldn't reposition navigation to a valid ubication. []

Further investigation revealed that the "processinstancelog" is being created in file 

dashboard-builder/modules/dashboard-jbpm/src/main/webapp/WEB-INF/etc/sql/2-jbpm-historylog-postgres.sql

This file is present neither in dashbuilder-jboss-as7.0.war, nor in dashbuilder supplied with BPMS 6 DR4.
Comment 3 Marek Winkler 2013-05-13 08:29:16 EDT
Created attachment 747221 [details]
dashboard-builder-jboss.log with processinstancelog not found error
Comment 4 Marek Winkler 2013-05-13 10:21:42 EDT
I have tried logging into dashbuilder with H2 database and found out that the log errors (complaining about missing processinstancelog) are still there, however I am able to log into the application. Perhaps the inability to log into the application is not related with the SQL error messages in the log.

Will continue investigating the issue.
Comment 5 Marek Winkler 2013-05-13 11:09:37 EDT
I have managed to log into dashbuilder with PostgreSQL. The problem was probably caused by stale browser history - I will continue with verification on other databases.

Looks like those SQL errors in log were not related with the problem at all - created separate issue #962474 for removing them, as they are confusing.
Comment 6 Marek Winkler 2013-05-14 05:11:34 EDT
The results of smoke testing dashbuilder with the remaining databases are as follows, not all of them pass:

 - PostgreSQL 9.2 - passed
 - MySQL 5.5 - passed
 - Oracle 11gR2 - failed (see attached log)
 - MSSQL 2008 R2 - failed (see attached log)

All databases have been cleared before the test. We used dashbuilder built as suggested in a previous comment:

cd git/droolsjbpm/dashboard-builder
mvn clean install -Dmaven.test.skip=true -P full

The problem with Oracle seems to be caused by (see full log)

Error stack trace=java.lang.ClassNotFoundException: oracle.sql.BLOB from [Module "deployment.dashbuilder-jboss-as7.0.war:main" from Service Module Loader]

This class is located in oracle JDBC driver, which is deployed on the EAP, probably a missing/misconfigured dependency? Do you deploy Oracle JDBC driver as EAP module or as a deployment?

The MSSQL problem is displayed in the log as

Error stack trace=com.microsoft.sqlserver.jdbc.SQLServerException: The result set is closed.

(again see attached log for details)
Comment 7 Marek Winkler 2013-05-14 05:12:21 EDT
Created attachment 747566 [details]
dashbuilder log for Oracle
Comment 8 Marek Winkler 2013-05-14 05:12:55 EDT
Created attachment 747569 [details]
dashbuilder log for MSSQL
Comment 9 Marek Winkler 2013-05-14 11:02:48 EDT
The problem with ClassNotFoundException with Oracle could be caused by the same problem as described here: https://community.jboss.org/message/733029

Probably adding a dependency into jboss-deployment-structure.xml could work, I am going to check this.
Comment 10 Marek Winkler 2013-05-14 12:10:47 EDT
It seems that adding dependency to oracle jdbc driver to jboss-deployment-structure.xml helps:

<jboss-deployment-structure>
    <deployment>
	<dependencies>
      	    <module name="deployment.ojdbc6.jar" />
	</dependencies>
        <exclusions>

.... (the rest of the file)

</jboss-deployment-structure>

The above example assumes that Oracle JDBC driver is deployed onto EAP as deployment in ojdbc6.jar.

However, this leads to the next ClassNotFoundException pointing to c3p0 library. The assembly descriptor assembly-jboss-as-7_0.xml indeed excludes this library:

  <exclude>WEB-INF/lib/c3p0-*.jar</exclude>

When I addded the c3p0 library to the deployment, another exception is thrown:

Error stack trace=org.hibernate.HibernateException: JDBC connection object must be a oracle.jdbc.OracleConnection a org.apache.commons.dbcp.PoolableConnection or a com.mchange.v2.c3p0.impl.NewProxyConnection. Connection class is org.jboss.jca.adapters.jdbc.jdk6.WrappedConnectionJDK6

See attached log for details.

Probably the best solution would be to avoid direct work with JDBC driver and connection pool classes, but this might be a long term solution connected with the transition to JPA which is not going to happen soon.
Comment 11 Marek Winkler 2013-05-14 12:11:32 EDT
Created attachment 747750 [details]
dashbuilder log for Oracle with resolved dependency issues
Comment 12 David Gutierrez 2013-05-17 12:13:18 EDT
We recently pushed to github some changes regarding Oracle compatibility issues: https://github.com/droolsjbpm/dashboard-builder/commit/0f0ea19ddff3cabf883512d54275fd180f365292

We are still doing some final verifications but it seems that the WAR can be deployed succesfully on on EAP 6.1.

We still have pending checking the SqlServer stuff anyway. 
We expect to have this issue fixed throughout next week.
Comment 13 David Gutierrez 2013-05-22 10:52:46 EDT
We have finally fixed and tested the deployment on EAP 6.1 for Oracle 11g, SQLServer 2005 and SQLServer 2008. It's working for them now.

We also have added some extra steps to the JBoss deployment instructions, as you can see here: https://github.com/droolsjbpm/dashboard-builder/blob/master/builder/src/main/jbossas7/README.md
Comment 14 Marek Winkler 2013-05-23 05:21:41 EDT
Verified that Oracle 11g R2 and MSSQL 2008 R2 work in community dashbuilder version (commit 7e549c408f176c3fff72888c07fd0afb6e462d2c) at github. Will verify the fix in DR4 when it will be available.
Comment 15 Jan Hrcek 2013-06-11 07:23:36 EDT
The problem seems to be fixed now. We tested deployment of dashbuilder build DR5 on JBoss EAP with the following databases and it works:

-Oracle 11gR2
-PostgreSQL 9.2
-MySQL 5.5
-MS SQL 2008
Comment 19 Lukáš Petrovický 2014-02-07 11:15:22 EST
This BZ has been part of the 6.0.0 stream.

Note You need to log in before you can comment on or make changes to this bug.