Error "Unable to load data from dataset ..." when report has "too many" widgets

Question

We have a report with > 8 widgets. When this report is called from a high performance client, only some widgets are processed properly. Others thorw the "unable to load data from dataset..." error. Looking into the logs, we see java.sql.SQLException: Cannot get a connection, pool error Timeout waiting for idle object.

When the same report is called from a "slow" client, everything works fine. So apparently, there is a problem with too many parallel connections. We tried to change some parameters but can't solve the problem. Does anybody has an idea?

Reports with less widgets usually work fine with all kind of clients.

Thanks for all help!

Comments

Hi,

could you please add more details on how you deployed Knowage? For example, did you used our official Helm chart or you used a custom deployment? Could you provide us you custom deployment?

If you used our Helm chart we can implement the customization of the connection pool in the near future.

---

I am not aware of "your" helm chart but set up an own one. I would be happy to use an official chart. Could you please point me to the source? Of course I can share our chart as well; but apparently it would make sense to use your chart.
Thanks so far!

---

Hi,

you could find our official Helm chart here: https://github.com/KnowageLabs/Knowage-Helm/tree/knowage-server-7.4

I will try to make the metadata and cache connection pool configurable in the next days.

Take also in consideration the anwer of @kstang  because if you have created a Knowage Datasource selecting the JDBC type, at end of the panel you used to create the datasource there are all the configuration of the pool of that datasource. See the screenshot at: https://imgur.com/a/D26ASHd

Answer 1

please increase your database concurrent connection limit and retry

Comments

Thanks for all the answers! We double-checked the configs.

I am not sure which DB causes the problems. Basically, we have access to three (mysql/mariadb) databases:
1) Knowage meta data / repository

2) Knowage cache

3) Data warehouse mart layer (the data to be reported)

Knowage meta data is configured in the server.xml. Knowage cache is also configured in server.xml, but additionally in the UI (?). Data warehouse reporting data is configured in UI only. We increased pool sizes everywhere without any change. Are there any other ideas or hints?

---

We tried several changes to the databasye connection settings.. This had no effect at all on the error. It sometimes occurs and sometimes not. Here again is the stack trace we get. Any further help would be very much appreciated because with those errors the real nice reporting application is not usable by our managers :-(

Caused by: it.eng.spagobi.commons.dao.SpagoBIDAOException: An unexpected error occured while loading dataset whose label is equal to [iam_dashboard_Q07]

at it.eng.spagobi.tools.dataset.dao.DataSetDAOImpl.loadDataSetByLabel(DataSetDAOImpl.java:1117)

at it.eng.spagobi.api.common.AbstractDataSetResource.getDataStore(AbstractDataSetResource.java:193)

... 70 more

Caused by: it.eng.spagobi.commons.dao.SpagoBIDAOException: An error occured while creating the new transaction

at it.eng.spagobi.tools.dataset.dao.DataSetDAOImpl.loadDataSetByLabel(DataSetDAOImpl.java:1099)

... 71 more

Caused by: org.hibernate.exception.GenericJDBCException: Cannot open connection

at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:140)

at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:128)

at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)

at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:52)

at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:449)

at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:167)

at org.hibernate.jdbc.JDBCContext.connection(JDBCContext.java:160)

at org.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:81)

at org.hibernate.impl.SessionImpl.beginTransaction(SessionImpl.java:1473)

at it.eng.spagobi.tools.dataset.dao.DataSetDAOImpl.loadDataSetByLabel(DataSetDAOImpl.java:1096)

... 71 more

Caused by: java.sql.SQLException: Cannot get a connection, pool error Timeout waiting for idle object

at org.apache.tomcat.dbcp.dbcp2.PoolingDataSource.getConnection(PoolingDataSource.java:142)

at org.apache.tomcat.dbcp.dbcp2.BasicDataSource$PaGetConnection.run(BasicDataSource.java:76)

at org.apache.tomcat.dbcp.dbcp2.BasicDataSource$PaGetConnection.run(BasicDataSource.java:72)

at java.security.AccessController.doPrivileged(Native Method)

at org.apache.tomcat.dbcp.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:738)

at org.hibernate.connection.DatasourceConnectionProvider.getConnection(DatasourceConnectionProvider.java:92)

at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:446)

... 76 more

Caused by: java.util.NoSuchElementException: Timeout waiting for idle object

at org.apache.tomcat.dbcp.pool2.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:443)

at org.apache.tomcat.dbcp.pool2.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:353)

at org.apache.tomcat.dbcp.dbcp2.PoolingDataSource.getConnection(PoolingDataSource.java:136)

... 82 more