Bug 969278

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 
http://jawa05.englab.brq.redhat.com/candidate/BPMS-6.0.0-DR5/bpms-6.0.0.Alpha-redhat-1-bpms-eap6.zip
f0d7a00c36dd45ea188cbe70a77aa5db  bpms-6.0.0.Alpha-redhat-1-bpms-eap6.zip
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.