Bug 969278 - User defined data sources are closed unexpectedly by dashbuilder - cannot be configured
User defined data sources are closed unexpectedly by dashbuilder - cannot be ...
Product: JBoss BPMS Platform 6
Classification: JBoss
Component: BAM (Show other bugs)
Unspecified Unspecified
urgent Severity urgent
: DR6
: 6.0.0
Assigned To: David Gutierrez
Jan Hrcek
: TestBlocker
Depends On:
  Show dependency treegraph
Reported: 2013-05-31 02:42 EDT by Jan Hrcek
Modified: 2014-08-06 16:11 EDT (History)
3 users (show)

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Cause: The external connection is being closed within the execution of the current transaction. Consequence: Further invocations to the external connection raise an error. Fix: Remove the "close" invocation on the connection. Result: External connection calls no longer fail.
Story Points: ---
Clone Of:
Last Closed: 2014-08-06 16:11:14 EDT
Type: Bug
Regression: ---
Mount Type: ---
Documentation: ---
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---

Attachments (Terms of Use)
Steps to reproduce with MySQL (661.91 KB, image/png)
2013-05-31 02:42 EDT, Jan Hrcek
no flags Details
Steps to reproduce with H2 (595.64 KB, image/png)
2013-05-31 02:44 EDT, Jan Hrcek
no flags Details
Application log when using MySQL datasource (106.38 KB, text/x-log)
2013-05-31 02:45 EDT, Jan Hrcek
no flags Details
Application log when using H2 datasource (78.47 KB, text/x-log)
2013-05-31 02:45 EDT, Jan Hrcek
no flags Details
After bugfix1: Application log (182.81 KB, text/x-log)
2013-06-12 03:09 EDT, Jan Hrcek
no flags Details
After bugfix1: steps + new error (468.70 KB, image/png)
2013-06-12 03:10 EDT, Jan Hrcek
no flags Details
After bugfix1: Application log - MySQL datasource (53.08 KB, text/x-log)
2013-06-12 03:55 EDT, Jan Hrcek
no flags Details
After bugfix2: error in gui (1.02 MB, image/png)
2013-06-13 02:05 EDT, Jan Hrcek
no flags Details
After bugfix2: application log (109.19 KB, text/x-log)
2013-06-13 02:06 EDT, Jan Hrcek
no flags Details
After bugfix3: application log (106.49 KB, text/x-log)
2013-06-14 02:26 EDT, Jan Hrcek
no flags Details
After bugfix3: guit test video (18.64 MB, video/webm)
2013-06-14 02:28 EDT, Jan Hrcek
no flags Details

  None (edit)
Description Jan Hrcek 2013-05-31 02:42:28 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):

How reproducible:
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.

Actual results:
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.

Expected results:
Correctly configured datasource should work and it should be possible to extract data from it.

Additional info:
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.
Comment 1 Jan Hrcek 2013-05-31 02:44:16 EDT
Created attachment 755173 [details]
Steps to reproduce with H2
Comment 2 Jan Hrcek 2013-05-31 02:45:07 EDT
Created attachment 755174 [details]
Application log when using MySQL datasource
Comment 3 Jan Hrcek 2013-05-31 02:45:39 EDT
Created attachment 755175 [details]
Application log when using H2 datasource
Comment 6 David Gutierrez 2013-06-11 11:34:19 EDT
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
Comment 7 Jan Hrcek 2013-06-12 03:08:14 EDT
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-/ 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"
Comment 8 Jan Hrcek 2013-06-12 03:09:51 EDT
Created attachment 759969 [details]
After bugfix1: Application log
Comment 9 Jan Hrcek 2013-06-12 03:10:50 EDT
Created attachment 759970 [details]
After bugfix1: steps + new error
Comment 10 Jan Hrcek 2013-06-12 03:54:35 EDT
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.
Comment 11 Jan Hrcek 2013-06-12 03:55:59 EDT
Created attachment 760007 [details]
After bugfix1: Application log - MySQL datasource
Comment 12 David Gutierrez 2013-06-12 05:53:46 EDT
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.
Comment 13 Jan Hrcek 2013-06-13 02:04:26 EDT
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.
Comment 14 Jan Hrcek 2013-06-13 02:05:48 EDT
Created attachment 760467 [details]
After bugfix2: error in gui
Comment 15 Jan Hrcek 2013-06-13 02:06:40 EDT
Created attachment 760468 [details]
After bugfix2: application log
Comment 16 Jan Hrcek 2013-06-13 03:04:28 EDT
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.
Comment 18 David Gutierrez 2013-06-13 09:09:31 EDT
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
Comment 19 Ryan Zhang 2013-06-13 23:15:16 EDT
Please verify the updated bits in 
f0d7a00c36dd45ea188cbe70a77aa5db  bpms-6.0.0.Alpha-redhat-1-bpms-eap6.zip
This has included the upstream fixes.
Comment 20 Jan Hrcek 2013-06-14 02:26:12 EDT
Created attachment 761096 [details]
After bugfix3: application log
Comment 21 Jan Hrcek 2013-06-14 02:28:11 EDT
Created attachment 761098 [details]
After bugfix3: guit test video
Comment 22 Jan Hrcek 2013-06-14 02:29:43 EDT
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 23 Jan Hrcek 2013-06-14 02:37:22 EDT
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.
Comment 24 David Gutierrez 2013-06-17 03:49:32 EDT
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.
Comment 25 Jan Hrcek 2013-06-19 02:49:02 EDT
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.
Comment 26 David Gutierrez 2013-06-19 11:49:16 EDT
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
Comment 28 Jan Hrcek 2013-06-20 02:50:40 EDT
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.
Comment 30 Jan Hrcek 2013-06-20 08:23:03 EDT
Verified. Tested with the updated DR5 binaries and works as described in Comment 28.

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