Connection Manager is not running at the specified port number - Pervasive 13.31.014 - Intermittent

[ianbtheog]

Greetings,
I am currently experiencing an issue with connecting to a Pervasive database,

For context, we have an ERP system which uses Pervasive as its database of choice. I've developed a web app (for internal use only - not internet facing - traffic is quite small) which connects to Pervasive to query/update the data from the ERP.

The web app doesn't make any connections itself to the database, instead it sends requests to a flask API which uses unixODBC and the Pervasive drivers to make the connection to the server hosting Pervasive. This works really well for the most part, however every so often the connection between Pervasive and the API breaks down and the following error message is logged.

2024-07-24 11:54:13.332949 - ('08S01', '[08S01] [PSQL][ODBC Client Interface][Client LNA]The ODBC Client Interface cannot access the data source because SQL Connection Manager is not running at the specified port number. Contact your system administrator for assistance. (10065) (SQLDriverConnect)')

Once the connection breaks down, all further requests will continue to present this error and fail. I would imagine from the error that this would imply that there is something wrong with network connection or the SQL Connection Manager. However ping and telnet both suggest that Pervasive remain reachable at all times, I wish I had tested the connection with isql.

I currently have a "rescue" script that is set as an error handler from within the flask API, such that when this error occurs during the day, the script readds the DSN and restarts the service. This works and means I don't have to babysit it but it does present an error message to the user temporarily whilst it sorts itself out.

The firewall on both the Pervasive server and the linux machine are either disabled/set to allow traffic, DNS resolves the name correctly and I can always ping it correctly (I did add it to the hosts file as it is a fixed IP address - just in case) and there's never anything in the Pervasive logs of note and all other connections continue to be serviced.

I have tried being more conscious about managing my connections each request opens one connection and closes the connection when the resources are cleared up.

Are there any other steps I can take to try and figure out this issue? I have seen some posts on here which don't have much in the way of definite conclusions.

I am aware Pervasive 13 is out of support now, and we have a quote to update however it's a big chunk of change and I haven't got management approval yet (and may not do)

I can add any code as requested.

Many Thanks,
Ian

 

[mirtheil]

I was going to ask a few questions but you've already answered them. Because the Pervasive engine still works for other clients, this seems to be related to the Flask API side of things.
Here are a few questions that I still have:
- Once you get the error, does it continue until you restart something?
- What does the connection string look like?
- What shows in the Pervasive Monitor for SQL Connections? Are you running into any limits especially on the SQL side of things?
- Has this been happening for a while or did it just start?
- Has it been getting worse? If it's worse, has anything changed?

Mirtheil

http://www.mirtheil.com

 

[ianbtheog]

Hello Mirtheil,

Thanks for you reply, in response to your questions (I am answering out of order):

- Has this been happening for a while or did it just start?

As far as I am aware, this has always been a problem since I started going down this route although it wasn't very frequent so I didn't notice until may be a month or two in and I chalked it down to oddities with the database.

As I have access to the log files, you can get a picture for how inconsistent the error is and how it's increasing in frequency up to the 23rd of July
* 17 November
* 22 November
* 24 January
* 25 January
* 5 February
* 13 May
* 10 July
* 11 July
* 15 July
* 23 July

​

- Has it been getting worse? If it's worse, has anything changed?

It has been becoming more frequent, what has changed is that we've started using tablets on our shop floor for managing work orders instead of using paper printouts. This means there are more hits to the flask API in general, the web app previously was quite sporadically used so I suppose there was less chance of encountering the issue. It seems to scale up with usage, I have reduced the amount of queries being made and optimised a couple of queries which were slow/resource intensive and this seems to have reduced the number of time although it's occurring quite frequently still.​

- Once you get the error, does it continue until you restart something?

Once the error appears it will continue apparently indefinitely and all requests will return an error, I have it now so that as soon as the error appears I run a script which resolves the error.
The code follows (details removed where appropriate):

#!/bin/bash

#Re-add the DSN to the system using the included dsnadd command included in Pervasive
/home/pervasive/psql/bin/dsnadd -dsn=MY_DSN -host=MY_HOST -db=MY_DB_NAME -uid=MY_USER_ID -pwd=MY_PASSWORD

#OPTIONAL: COPY GLOBAL CONFIG TO LOCAL PSQL USER DIRECTORY
cp /etc/odbc.ini /home/psql/.odbc.ini
#RESTART FLASK SERVICE
sudo service intranet2 restart

echo "Restarting Database Services"
/code]
[/indent]

I believe that doing one or the other is not sufficient to resolve the issue. I have to re-add the DSN then restart the service.

- What does the connection string look like?

DSN=DSN64;UID=MY_UID;PWD=MY_PWD

- What shows in the Pervasive Monitor for SQL Connections? Are you running into any limits especially on the SQL side of things?
I don't think I have seen anything out of the ordinary going on with the SQL connections, before when I was keeping connections alive and letting pyodbc deal with the connections it was using around 20-30 connections most of the time it never really went above this (about in line with what the ERP on a typical workstation is using), now it is currently not using any connections as they are closed relatively quickly once the request has been satisfied.

Many thanks,
Ian​