Red Hat Bugzilla – Bug 969278
User defined data sources are closed unexpectedly by dashbuilder - cannot be configured
Last modified: 2014-08-06 16:11:14 EDT
Created attachment 755172 [details]
Steps to reproduce with MySQL
Description of problem:
When configuring the application to use user-defined datasource X it is impossible to create Data provider based on datasource X. The error message is shown indicating that connection is already closed. I replicated this problem in 100% of cases testing both local H2 database and MySQL database.
Version-Release number of selected component (if applicable):
Seems 100% reproducible.
Tried in 2 cases:
1) H2-based dashbuilder demo started with: dashboard-builder/scripts/buildandrun.sh h2 + my local h2
2) .war distribution for JBoss EAP configured to use MySQL datasource
Steps to Reproduce:
1. Configure some local database filled with some sample data (in my case one PERSON table + few lines of data)
2. Configure the datasource in Workspace Showcase > Administration > External connections (see screenshot)
3. When configuring datasources make sure they are configured correctly using "Check DataSource" button - in my case both were configured correctly
4. Go to Administration > Data Providers and try to configure some new SQL provider based on DataSrouce configured in step 2
5. Select your DS configured in step 2 using "Datasource" and also try it using "Try" button.
After you select your previously configured datasource using "Datasource" select - red message appears under the "Query" text area.
The message states:
In case of H2 database:
Invalid data set. Please, review your query
The object is already closed [90007-168]
In case of MySQL database:
Invalid data set. Please, review your query
No operations allowed after connection closed.
Correctly configured datasource should work and it should be possible to extract data from it.
See screenshots for clarification.
Application logs with stacktraces provided for both cases.
This bug seems closely related to Bugzilla 965941. See the stack traces provided in the attachments.
This bug blocks testing, because we are unable to compose/configure custom dashboards which would use custom datasources.
Created attachment 755173 [details]
Steps to reproduce with H2
Created attachment 755174 [details]
Application log when using MySQL datasource
Created attachment 755175 [details]
Application log when using H2 datasource
The issue has been fixed. Now, external data source connections are working both in JDBC and JNDI modes.
Github commit: https://github.com/droolsjbpm/dashboard-builder/commit/56797d60a288b8826a0192ac547b2a1827ee1979
This issue was caused by the changes made by the following commit: https://github.com/droolsjbpm/dashboard-builder/commit/6188136f6c288150b082a17aa32097720d05f300
I'm afraid, that the problem is not fully solved yet.
I tried it with your latest commit (b40ebc16ce) from master branch on github - I built deployable war for JBoss EAP, configured to use local H2 datasource.
Then I followed the outlined reproducing steps and I get to the point where I'm selecting my (correctly configured) datasource in the configuration of Data provider.
There are several problems:
(1) After selecting the datasource (see screenshot attached) I now get a different error:
"Invalid data set. Please, review your query. Method is only allowed for a query. Use execute or executeUpdate instead of executeQuery, SQL statement: [90002-168]"
The application is obviously trying to perform the user specified "Query" immediately after user selects the datasource (i.e. before he even fills in the "Query" field). This problem disappears after user fills in some query. This can be also seen in application log (also attached). I would expect the "Query" to be performed only after user fills in the fileds and presses "Try" or "Save" button.
(2) There is also problem when using this datasource - most likely related to the change in management of connections:
I tried configuring a simple KPI panel that displays data from my datasource.
The data is displayed ok. But when I get back to "Data providers" page to delete my testing data provider the page loads very long and exception appears in the application log (see detail in the log file)
"[http-/127.0.0.1:8080-5 12/06/13 08:38:18] ERROR org.jboss.dashboard.provider.sql.SQLDataLoader (SQLDataLoader.java:76) - Cannot get datasource named local 
java.sql.SQLException: javax.resource.ResourceException: IJ000453: Unable to get managed connection for java:jboss/datasources/ExampleDS"
Created attachment 759969 [details]
After bugfix1: Application log
Created attachment 759970 [details]
After bugfix1: steps + new error
I also tested with custom MySQL based JNDI Datasource and both issues (1 and 2 mentioned above) are the same. Although it IS now possible to configure both types of datasources (JDNI and JDBC) the error message is presented after selecting datasource in data provider configuration.
And after some time clicking in the application (configuring KPI panel to use the custom data provider) we get to the state when it's impossible to get a connection to "local" datasource managed by the application -which causes GUI freezes.
Created attachment 760007 [details]
After bugfix1: Application log - MySQL datasource
Error checking in the SQL data provider editor has been improved in order to fix the issue (1).
Github commit: https://github.com/droolsjbpm/dashboard-builder/commit/d60e5664670f30a498595dac55134440c22b06ef
The issue (2) it's not reproducible, since now there is now way to delete a data provider which it's being used.
I confirm the issue (1) has been fixed - now it is possible to configure both JDBC based and and JNDI based data sources and to get data from them displayed by dashboard.
However the issue (2) seems to have been introduced by the fix. It manifests itself by keeping the connection(s) to local datasource open which leads to some GUI parts not being loaded and exceptions being displayed in "Unexpected error" window.
And it is not related to trying to delete data provider which is used by KPI panels - I know, such panels cannot be deleted because of referential integrity constraints. The web GUI freezes (no page content is displayed - se screenshot) and nuber of exceptions appear in the application log (also attached). In my case I manage to reproduce the problem in 100% of tries using the following actions:
Following steps 1-5 as in the first note (I have a datasource in JBoss EAP configured to use H2 database with url "jdbc:h2:~/test" - data are save on disk)
6) Go to other page (e.g. "Sample Dashboards") and create new instance of KPI panel configured to use our newly created testing datasource.
7) Navigate back to Administration > Data providers
8) The application freezes (waiting for localhost) as indicated on the screenshot and with the exception in the application log.
9) Eventually the panel "Data providers" is loaded but the exception is displayed.
One important thing to notice is, that the H2 database lock file (in my case "test.lock.db") remains in place from the moment the application is deployed and it is not deleted till the application server is stopped - it seems like the application is keeping the connection(s) open.
Created attachment 760467 [details]
After bugfix2: error in gui
Created attachment 760468 [details]
After bugfix2: application log
Now I tested it with the newly made productized dasbhuilder DR5 downloaded from http://jawa05.englab.brq.redhat.com/candidate/BPMS-6.0.0-DR5/bpms-6.0.0.Alpha-redhat-1-bpms-eap6.zip (before I tested with jboss deployable built from master github - commit d60e566) and the problem is the same.
We have been reviewing the database connectivity stuff and we have detected one use case (Showcase > Administration > External connections > Edit/Create Data Source > Button "Check data source") where the connection was not being closed.
The check functionality did request for a connection but it wasn't close it. We checked that, indeed, the h2.lock was kept until the application is shutdown.
This issue has been fixed. Now everything should work fine.
Github commit: https://github.com/droolsjbpm/dashboard-builder/commit/095fdbf0fb6225fa494d7249d23728142018cd5b
Please verify the updated bits in
This has included the upstream fixes.
Created attachment 761096 [details]
After bugfix3: application log
Created attachment 761098 [details]
After bugfix3: guit test video
I have bad news again. The the problem is still present.
I downloaded the updated bits and deployed them on EAP 6.1 (I also double checked via java decompiler, that the fix from latest commit is present in the deployed binaries).
Again after the steps described in comments 13 I get to the same application state - after clicking through the application pages I get to "Data providers" page and it doesn't load fully + there's the same exception stacktrace in the application log.
I attach application log + also a video of the gui test running and getting to the point when the application can't get the connection.
Comment about the video:
1) At 0:54 you can notice another issue, which I'll report in different bugzilla - when starting to edit KPI panel contents based on custom data provider, the "preview" of the modified KPI chart disappers.
2) At 1:06 you can see we get to the state when contents of a page "Data providers" is not fully loaded.
I'm trying to reproduce those last issues reported, but unfortunately I can't get to the point you mention.
As I see you are running on EAP 6.1 over Mysql and using an H2 file-based external data source, right?. Could you please send me the h2.db file so that I can try to reproduce it?
Also, any other extra information you can provide will be much appreciated.
I finally isolated the issue. It has nothing to do with configuration of user-defined data sources.
The steps to reproduce:
1) Go to dashbuilder page: Administration > Data Sources. Note this page contains 3 SQL Providers by default.
2) Each time the page is loaded (e.g. by navigating to it by clicking link in the management menu - or simply by clicking browser refresh button / F5 on keyboard) 3 new connections to the "local" application datasource are opened (nothe the correspondence of number of providers on the page and number of new connections opened). If your refresh the page enough times, the number of open connections exceeds the size of connection pool managed by the application server - and that is when the application freeze occurs.
The freeze problems was being caused by previous wrong commit (https://github.com/droolsjbpm/dashboard-builder/commit/56797d60a288b8826a0192ac547b2a1827ee1979) has been reverted.
The right solution has consisted in make it easier the handling of external data connections. We have tested all the use cases identified along this bug and now everything it seems to work fine:
.- No errors while creating a new data provider
.- Check controls to avoid deleting data provider in use.
.- No freezes.
.- No connections remain open.
Please, verify all the use cases carefully.
Github commit: https://github.com/droolsjbpm/dashboard-builder/commit/71edb8146f333de7c79d58392654da50102fac6c
I tested with latest commit bc2671dd2a on master branch from github.
I confirm that the following problems mentioned in this bugzilla are solved now:
1) Upon visiting the "Data Providers" page there are no longer connections left open -> no more freezes
2) No errors about connections unexpectedly closed when creating new Data provider
3) The "Check DataSource" button in "External Connections" panel no longer keeps connections to the DS open
This bz will be verified when the fix is present in the next productized build.
Verified. Tested with the updated DR5 binaries and works as described in Comment 28.