Red Hat Bugzilla – Bug 961679
BPMS dashbuilder does not support other databases than H2
Last modified: 2014-08-06 16:07:34 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:
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
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.
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
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:
* 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.
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
(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
[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
This file is present neither in dashbuilder-jboss-as7.0.war, nor in dashbuilder supplied with BPMS 6 DR4.
Created attachment 747221 [details]
dashboard-builder-jboss.log with processinstancelog not found error
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.
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.
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:
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)
Created attachment 747566 [details]
dashbuilder log for Oracle
Created attachment 747569 [details]
dashbuilder log for MSSQL
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.
It seems that adding dependency to oracle jdbc driver to jboss-deployment-structure.xml helps:
<module name="deployment.ojdbc6.jar" />
.... (the rest of the file)
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:
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.
Created attachment 747750 [details]
dashbuilder log for Oracle with resolved dependency issues
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.
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
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.
The problem seems to be fixed now. We tested deployment of dashbuilder build DR5 on JBoss EAP with the following databases and it works:
-MS SQL 2008
This BZ has been part of the 6.0.0 stream.