Intermittent issues with Basic and Advanced Database Lookup tables

[Ramachandran R]

Dear Team,

We recently upgraded our Cloverleaf system from version 20.1.2 to 20.1.3 on RHEL 8.8 (Ootpa). Our database backend is Microsoft SQL Server 2014 (SP3-CU4-GDR). Since the upgrade, we’ve been encountering issues with basic and advanced database lookup tables. Initially, everything was functioning correctly, but after the upgrade, we’ve noticed intermittent problems. Both basic and advanced database lookups work for a while but then hang unexpectedly. We’ve attempted to restart the processes, but they become unresponsive and need to be terminated manually. Subsequently, after restarting, they work normally again for a period before encountering the same issue.

Despite monitoring closely, we haven’t been able to identify any specific errors or patterns when the problem occurs. Additionally, the issue seems to be random and isn’t consistently reproducible.

We’re reaching out for guidance on how to investigate and resolve this issue.

Topic

[Diana de Bruin]

Hello,

We’ve been experiencing the same sort of problems after we migrated from 20.1.2 to 20.1.3. And like you we couldn’t find any usefull info in the log files.

We solved most of our problems bij making sure that we run only 1 database action per process.

Diana

LUMC -Leiden University Medical Centre
The Netherlands

[Ramachandran R]

Dear Diana,

My bad, I didn’t see this response, it could have saved plenty of my time. Yes, this is what helped me.

[Keith McLeod]

I ran into an issue with the dblookup on the 2022.09.02 version.  The recommendation on mine was to upgrade the SQL server DB.  2014 I believe is marginal on what the current JDBC drivers support.  I have one system for pathology that has put off upgrading their version of SQL from 2008 R2. Luckily, they are upgrading that over the next several weeks. The current versions of SQL for other apps seems to work fine…  Something you might want to check…

• This reply was modified 7 months, 3 weeks ago by Keith McLeod.

[Ramachandran R]

After thorough testing with both SQL Server 2014 and SQL Server 2019 in Cloverleaf 20.1.3, it’s been confirmed that the issue lies not with the database, but with the patch itself.

Previously, we had a configuration where an Xlate pointed to the database-outbound thread within the same process. To resolve the issue, we had to separate the Xlate that utilizes the Advanced Database Lookup table into one process, and the database-outbound thread into another process.

In previous versions, such as 20.1.2, having both inbound and outbound processes within the same process, along with an Advanced Database Lookup table in Xlate, worked seamlessly for years. The sudden issue in 20.1.3 prompts the need for further investigation into its cause.

[Diana de Bruin]

Just got a respons from our support organisation and they told me that there are 2 new parameters in “Options >  Site Preferences > Database Configurations” to influence the number of accepted connections to a database.

Initial Pool Size & Maximum Pool Size, both parameters apparently  have a default value of 1.

I was adviced to put a higher value (ie. 5) in the Maximum Pool Size

Diana

LUMC -Leiden University Medical Centre
The Netherlands

[Ramachandran R]

Thank you. This looks to be added in the newer version. I will try and update here if this resolves our issue.

[Ramachandran R]

This solution is perfectly working fine. Thank you so much.

[Author]

Replies