You are not logged in.
Hi,
After a few days of ReportServer uptime and usage, this is the error that is shown :
The report could not be executed: Could not open connection to: jdbc:postgresql://192.168.1.111:5432/myDB with user: postgres. java.sql.SQLException: An attempt by a client to checkout a Connection has timed out.
To get help please contact an administrator.
Details
net.datenwerke.rs.core.service.reportmanager.exceptions.ReportExecutorException: The report could not be executed: Could not open connection to: jdbc:postgresql://192.168.1.111:5432/myDB with user: postgres. java.sql.SQLException: An attempt by a client to checkout a Connection has timed out. at Caused by: com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@2fe961b5 -- timeout at awaitAvailable()
at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1461) ~[c3p0-0.9.5.jar:0.9.5]
at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:639) ~[c3p0-0.9.5.jar:0.9.5]
at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:549) ~[c3p0-0.9.5.jar:0.9.5]
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutAndMarkConnectionInUse(C3P0PooledConnectionPool.java:756) ~[c3p0-0.9.5.jar:0.9.5]
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:683) ~[c3p0-0.9.5.jar:0.9.5]
... 66 common frames omittedThis gets fixed when the DB password is re-entered correctly (in DataSources) and applied (looks like the connection pool gets refreshed and many old idle connections to the DB are dropped) : checked this using
ps -eaf | grep postgresbefore and after the password re-entry.
Is there a c3p0 param that we can edit/add to resolve this ? We've noticed this problem since our upgrade to RS 3.0.4 (but unsure if this is related).
We use linux (Ubuntu-Server) to host RS, PostgreSQL 9.3 and tomcat 7.
Thanks,
Zach
Offline
Hi Zach,
what is your checkoutTimeout in your pool config? Maybe it is too low.
Check here:
https://stackoverflow.com/questions/436 … mchange-v2 ,
https://stackoverflow.com/questions/274 … attempt-by and
https://stackoverflow.com/questions/188 … -from-c3p0
Which reportserver did you use before performing the upgrade you mentioned?
Regards,
Eduardo
Offline
Hi Eduardo,
Thanks for your reply. This is my pool.cf
<?xml version="1.0" encoding="UTF-8"?>
<!--
ReportServer Configuration File
filename: datasources/pool.cf
Allows to configure the connection pool settings for database datasources.
-->
<configuration>
<pool>
<defaultconfig>
<maxPoolSize>40</maxPoolSize>
<initialPoolSize>10</initialPoolSize>
<acquireRetryAttempts>10</acquireRetryAttempts>
<acquireRetryDelay>500</acquireRetryDelay>
<checkoutTimeout>60000</checkoutTimeout>
<maxConnectionAge>7200</maxConnectionAge>
<maxIdleTime>3600</maxIdleTime>
</defaultconfig>
</pool>
</configuration>We were using RS v3.0.2 earlier.
Could you tell us what exactly happens when the password is re-entered (under datasources) and applied. That seems to clear out all the old idle connections that are crowding the pool...\
Thanks,
Zach
Offline
Hi Zach,
the connection pool is getting reseted when saving the respective datasource. We will check if this intended behavior is carried through with Postgres entirely. If not, we will get back to you.
Regards,
Eduardo
Offline
Hi Eduardo,
I'm running both RS v3.0.2 and RS 3.0.4 and the connection pool gets exhausted by idle connections in the 3.0.4 version. Monitoring this live in PGAdmin4. I've checked the configuration under File System /etc/datasources/pool.cf for both versions of RS, and the parameters are configured to exactly the same values (as mentioned above). I also checked that the same versions of the c3p0 and hibernate JARs exist in both versions of RS.
Only a tomcat restart or re-entering password and applying it seems to fix this issue in 3.0.4. This cannot be a long term solution for us..
Please help.
Thanks,
Zach
Offline
I just checked and the same issue exists for a fresh install of RS 3.0.5 as well.
Offline
I've checked the same with a dynamic list but this doesn't seem to cause an issue with respect to idle connections. Seems to be related to jasper reports only.
Offline
Hi Zach,
thanks for all the information. We don't quite understand something:
1. This is only happening with jasper reports, as you stated. I suppose you are testing one same report in three reportserver versions (3.0.2, 3.0.4, 3.0.5), is this correct? So you have three different reportserver installations, i.e. three different reportserver metadata datasources, all containing one same jasper report, which in all three cases is using the same postgres datasource, is this correct?
2. You said at the beginning of the thread: "After a few days of ReportServer uptime and usage". Later you said: "I just checked and the same issue exists for a fresh install of RS 3.0.5 as well." -> how did you check this (so quickly) for 3.0.5? Did you wait some days with the report idle and then this started happening in 3.0.5 or how exactly did you test this so quickly?
3. Can you please share the report configuration of your jasper report ? Please post some screenshots
4. The same for the PGAdmin4 live monitoring. Can you please post some screenshots of the correct behavior and of the incorrect behavior?
In the meanwhile, you can schedule reportserver to restart periodically, e.g. each night, with a cron job or similar. This could be a workaround until we identify the issue.
Regards,
Eduardo
Offline
Hi Eduardo ,
1. Yes, i have 3 tomcat instances hosted on different ports talking to 3 RS metadata datasources all containing the same jasper report.
2. I tested this by creating a dadget for the same report, and spamming the refresh button on the dadget until the DB pool was full. When the max capacity of the pool is reached, the report stops refreshing and the PGAdmin4 graph that shows db
connections maxed out at 40 (defauilt value).
I'm guessing this is a jasper + 3.0.4 related problem because
in 3.0.2 : Jasper Report Dadget spam doesnt fill the pool with Idle Connections.
in 3.0.4 and 3.0.5 : Jasper Report Dadget spam fills the pool with Idle Connections BUT a Dynamic List with the same query as the jasper report does not fill the connection pool.
The jasperreports JAR in RS 3.0.4 is a newer version iirc (6.4.*) but the one in 3.0.2 is older (6.2.*)
3. and 4 . Will try to do this today.
For now, the workaround we are using is setting the max pool size to 500 and scheduling a midnight tomcat restart so the pool doesnt get filled, but this is not a long term soution.
Thanks,
Zach
Offline
Hi Zach,
thanks again for this information, this should help us to reproduce the problem.
Just to make sure:
You say dynamic lists doesn't cause this behavior. So if you create a dadget based on a dynamic list report and spam the refresh button on the dadget, the DB pool does not get full? I.e. you perform exactly the same steps for a dynamic list and this works as it should. Is this correct ?
Fyi, we raised ticket RS-3261 for this issue.
We upgraded the jasper library in 3.0.3-6000 (https://reportserver.net/releasenotes/RS3.0.3.html). Would it be possible for you to test this on 3.0.3-6000 ? You can download 3.0.3-6000 here: http://www2.datenwerke.net/rsupdate/RS3 … ver-ee.zip
Please perform a clean install, i.e. just copy the files to your tomcat and execute the DDLs for your database.
Regards,
Eduardo
Offline
Hi Eduardo,
Yes, I'm positive that dynamic list does not cause this behavior.
you can check https://imgur.com/a/kCilnHx for detailed, step by step screenshots.
will check with RS 3.0.3 and get back to you soon.
Thanks,
Zach
Offline
Hi Zach,
thanks for all the screenshots. We will try to reproduce the problem by following your step by step guide.
When testing with 3.0.3, please make sure you test with the build version 6000, *not* with the 6003 build. This because the jasper library was updated in the 6000 build.
So please make sure you test with the release in this download link: http://www2.datenwerke.net/rsupdate/RS3 … ver-ee.zip
You can see all release notes for 3.0.3 here: https://reportserver.net/releasenotes/RS3.0.3.html
Regards,
Eduardo
Offline
Hi Eduardo,
I tested this on 3.0.3 and the issue isn't present in this version. Were you able to reproduce the issue in ver 3.0.4 and 3.0.5 ?
Regards,
Zach
Offline
Hi Eduardo,
Some additional info and context on what led us to this bug : The reason we switched from 3.0.2 to 3.0.4 was that we were able to work with a PLSQL based Jasper Report on 3.0.4 with some workarounds that I discovered and used.
We couldn't use the RS datasource connection for this particular report because of the
"cannot CREATE TEMP TABLE in a read only transaction" type error (I read in another post that reportserver Datasource connections used read only transactions by default and this behavior can only be altered by using a script. )
We set the data adapter connection to read off an XML file in jasper and added a parameter called DBXML that pointed to this XML file .
On RS, we uploaded this xml file in the File System /resources/rs folder, shared folder for web access and added a Global Constant with the same name as the parameter : DBXML. Now the jasper report was able to bypass the RS datasource completely and read the connection properties off the XML file uploaded in RS.
The next error was that the DB password couldnt be read in plain text (from the XML)...
No secret found for The addition of an identity-secrets.jar to the lib folder of RS fixed this. I found this jar online here : https://community.jaspersoft.com/questi … ter-config
This entire work around functioned perfectly for us...at least until we hit the DB Pool snag...which is a huge problem as well.
The PLSQL workaround (xml - upload in file system - global constant - identity-sercrets.jar ) doesn't seem to work in v3.0.2 and v3.0.3 build 6000 for some reason...
It'd be great if you could fix the db pool bug AND provide a fairly straightforward method to work with reports based on PLSQL.
Both of these solutions are critical for us.
Hope this info helped.
Thanks,
Zach
Offline
Hi Zach,
during all the tests we are talking about: are you bypassing the RS datasource as you described or are you using the RS datasource in your jasper report? In your second step screenshots I read "added a new data DB to test" so I suppose you are using the RS datasource to test. But please confirm and send some screenshots of your jasper report configuration.
As a first step we will identify this issue. I have prioritized this in the development queue.
So let's concentrate on this, afterwards we will see what we can do to provide a more straightforward method to work with reports based on PLSQL without the workaround you described.
So you tested on 3.0.3-6000 and the issue isn't present on this version. In 3.0.4-6004 the issue is present. Could you please test the two remaining builds 6002 and 6003? The links are:
3.0.3-6002: http://www2.datenwerke.net/rsupdate/RS3 … ver-ee.zip
3.0.3-6003: http://www2.datenwerke.net/rsupdate/RS3 … ver-ee.zip
When we identify the exact version this issue appeared on we can identify the cause.
Regards,
Eduardo
Offline
Hi Eduardo,
During all my tests (3.0.2, 3.0.3 - 6000 , 3.0.4 - 6004, 3.0.5) I used a simple jasper report which used the DB connection from DataSources in ReportServer (No bypass workaround involved).
The report I used to test this (by spamming the refresh button on a dadget that deployed this report) was :
<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.1.1.final using JasperReports Library version 6.1.1 -->
<!-- 2017-07-12T15:26:00 -->
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports [url=http://jasperreports.sourceforge.net/xsd/jasperreport.xsd]http://jasperreports.sourceforge.net/xsd/jasperreport.xsd"[/url] name="Replication_sts" pageWidth="250" pageHeight="180" columnWidth="210" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="78ca4c50-8eae-4b03-903e-3a4e995e06e6">
<property name="com.jaspersoft.studio.data.sql.tables" value=""/>
<property name="com.jaspersoft.studio.data.defaultdataadapter" value="nainiDB"/>
<property name="com.jaspersoft.studio.unit." value="pixel"/>
<queryString>
<![CDATA[select max(createtime) as r_date from patientreceiptitem]]>
</queryString>
<field name="r_date" class="java.sql.Timestamp"/>
<background>
<band splitType="Stretch"/>
</background>
<title>
<band height="52" splitType="Stretch">
<staticText>
<reportElement mode="Opaque" x="0" y="0" width="210" height="52" backcolor="#A3EB7F" uuid="2163b86e-d3bd-4087-92f1-2f190d03634e"/>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font fontName="Verdana" size="14" isBold="true"/>
</textElement>
<text><![CDATA[Latest PRI transaction on Report Server]]></text>
</staticText>
</band>
</title>
<summary>
<band height="80" splitType="Stretch">
<textField>
<reportElement mode="Opaque" x="0" y="0" width="210" height="80" backcolor="#FFDF80" uuid="e5a7f55b-4d05-48d5-bb00-d4b8cacd6e4c"/>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font size="20"/>
</textElement>
<textFieldExpression><![CDATA[new SimpleDateFormat("EEE, dd MMM yyyy HH:mm:ss").format($F{r_date})]]></textFieldExpression>
</textField>
</band>
</summary>
</jasperReport>I'll take a look at the other two builds of RS 3.0.3 - 6002 , 6003 and get back to you.
Thanks,
Zach
Offline
Hi Eduardo,
I can confirm that the DB pool bug doesnt exist in the other two builds of RS 3.0.3 - 6002 , 6003 either. I just tested them out. Were you able to replicate the bug at your end ? Any idea when a fix for this will be provided ?
Thanks ,
Zach
Offline
Hi Zach,
thanks for testing. We are in the process of finishing reportserver 3.0.6, so this will be probably not included in this version. I prioritized this issue and we will release a patch as soon as we fix this if we can confirm the bug. I will update here when I have more information.
Regards,
Eduardo
Offline
Hi Eduardo,
Any update on this ??
Zach
Offline
Hi Zach,
sorry for the late answer. As I mentioned, we are in the process of releasing reportserver 3.0.6 and many bugs/issues have been solved during this process.
We are currently looking at this issue. Could you please test the following script before / during / after the connection pool stress and post the output here?
package maintenance.dbpool;
import net.datenwerke.dbpool.*;
def service = GLOBALS.getRsService(DbPoolService.class);
def map = service.getPoolMap();
map.each { conf, pool ->
tout.println("------ pool -----");
tout.println( "url: " + conf.getJdbcUrl());
tout.println("MaxPoolSize: " + pool.getMaxPoolSize());
tout.println("NumBusyConnections: " + pool.getNumBusyConnectionsDefaultUser());
tout.println("NumConnections: " + pool.getNumConnectionsDefaultUser());
tout.println("NumIdleConnections: " + pool.getNumIdleConnectionsDefaultUser());
tout.println("NumThreadsAwaitingCheckout: " + pool.getNumThreadsAwaitingCheckoutDefaultUser());
tout.println("NumUnclosedOrphanedConnections: " + pool.getNumUnclosedOrphanedConnectionsDefaultUser());
}
""does the output match your observations during your tests?
Regards,
Eduardo
Offline
Hi Zach,
we are now reproducing the problem and are looking into the cause and a solution.
Thanks for your patience,
Regards,
Eduardo
Offline
Hi Zach,
good news! we found the problem and are testing a solution for this. If everything goes well, this will be included in ReportServer 3.0.6, released in the next couple of days.
Regards,
Eduardo
Offline
Hi Zach,
this will be fixed in ReportServer 3.0.6, released in the next couple of days. Thanks for your help on this.
Regards,
Eduardo
Offline
Hi Zach,
ReportServer 3.0.6 is released and this is available now as you can read in the release notes: https://reportserver.net/releasenotes/RS3.0.6.html
RS-3261 Bug Connection leak in Jasper reports
Regards,
Eduardo
Offline
Hi Eduardo,
Just checked it. Works fine. Thanks a lot. Any updates on a PLSQL fix ?
Regards,
Zach
Offline