#1 2018-12-21 10:07:26

zach.mathew
Member
Registered: 2015-04-17
Posts: 21

Connection to Postgres DB dropped: works when DB password re-entered

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 omitted

This 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 postgres

before 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

#2 2018-12-21 11:41:50

eduardo
Administrator
Registered: 2016-11-01
Posts: 898
Website

Re: Connection to Postgres DB dropped: works when DB password re-entered

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

#3 2018-12-27 06:11:36

zach.mathew
Member
Registered: 2015-04-17
Posts: 21

Re: Connection to Postgres DB dropped: works when DB password re-entered

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

#4 2019-01-02 13:24:47

eduardo
Administrator
Registered: 2016-11-01
Posts: 898
Website

Re: Connection to Postgres DB dropped: works when DB password re-entered

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

#5 2019-01-09 11:16:22

zach.mathew
Member
Registered: 2015-04-17
Posts: 21

Re: Connection to Postgres DB dropped: works when DB password re-entered

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

#6 2019-01-09 11:50:58

zach.mathew
Member
Registered: 2015-04-17
Posts: 21

Re: Connection to Postgres DB dropped: works when DB password re-entered

I just checked and the same issue exists for a fresh install of RS 3.0.5 as well.

Offline

#7 2019-01-09 12:13:41

zach.mathew
Member
Registered: 2015-04-17
Posts: 21

Re: Connection to Postgres DB dropped: works when DB password re-entered

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

#8 2019-01-09 14:58:58

eduardo
Administrator
Registered: 2016-11-01
Posts: 898
Website

Re: Connection to Postgres DB dropped: works when DB password re-entered

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

#9 2019-01-10 05:24:06

zach.mathew
Member
Registered: 2015-04-17
Posts: 21

Re: Connection to Postgres DB dropped: works when DB password re-entered

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

#10 2019-01-10 08:04:38

eduardo
Administrator
Registered: 2016-11-01
Posts: 898
Website

Re: Connection to Postgres DB dropped: works when DB password re-entered

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

#11 2019-01-10 10:27:54

zach.mathew
Member
Registered: 2015-04-17
Posts: 21

Re: Connection to Postgres DB dropped: works when DB password re-entered

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

#12 2019-01-10 10:47:29

eduardo
Administrator
Registered: 2016-11-01
Posts: 898
Website

Re: Connection to Postgres DB dropped: works when DB password re-entered

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

#13 2019-01-11 06:48:36

zach.mathew
Member
Registered: 2015-04-17
Posts: 21

Re: Connection to Postgres DB dropped: works when DB password re-entered

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

#14 2019-01-11 07:47:39

zach.mathew
Member
Registered: 2015-04-17
Posts: 21

Re: Connection to Postgres DB dropped: works when DB password re-entered

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

#15 Yesterday 08:54:03

eduardo
Administrator
Registered: 2016-11-01
Posts: 898
Website

Re: Connection to Postgres DB dropped: works when DB password re-entered

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

#16 Yesterday 10:35:02

zach.mathew
Member
Registered: 2015-04-17
Posts: 21

Re: Connection to Postgres DB dropped: works when DB password re-entered

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

Board footer

Powered by FluxBB