It is currently 13 April 2021, 0:10 Advanced search

InDe 20.5 r10 Server Session SQL Server Connection Errors

Questions and answers on how to best use Instant Developer

InDe 20.5 r10 Server Session SQL Server Connection Errors

Postby ljwilson » 30 January 2021, 20:40

Hoping someone from Programma can give me some insight...

I've been slowly updating my production apps from 20.0 r9 to 20.5 r10. For my IIS/C# applications I had noticed some occasional SQL server connection errors in scheduled processes running in a Server Session on a couple of them, but just figured it was a network glitch and ignored it.

Last week I updated the last application, and right away saw the following errors for a scheduled process which reads in a couple of files each with several hundred records and inserts/updates into the SQL server database using classes. Normally the process takes under a minute. Note I don't get all of these errors during the same run--different ones during each nightly run:

  • No pending transaction.
  • This SqlTransaction has completed; it is no longer usable.
  • Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
  • Invalid operation. The connection is closed.
  • The requested operation cannot be completed because the connection has been broken.
  • Cannot find table 0.

Now the odd thing is I can run the exact same code through the Web Application, and it runs with no errors.

If the only change I make is to revert the Database Libraries back to InDe 20.0 r9, recompile and re-publish using ID Manager, then no problems at all.

Any idea what would have changed from 20.0 to 20.5 which would affect SQL Server connections in a Server Session?

These apps are running on Windows Server 2016 and 2019, accessing MS SQL Server 2014.

UPDATE 1:

I remembered I have a similar Java application running under Tomcat9 on a Redhat Enterprise Linux 8.3 Server, importing into a MariaDB 10 Database. I updated it to inde 20.5 r9 back on November 14th 2020. I started getting occasional errors in the log starting November 16th 2020. Once again the only thing changed was updating from InDe 20.0 r9 to 20.5 r9. These errors occur every 2 to 3 morning runs (the Server Session Import runs every morning). It errored out this morning--in the tomcat error log I see this:

Code: Select all
*********************************************************
** DATE : Mon Feb 01 06:00:43 EST 2021
** ERROR: Unable to read data

** OFFENDING COMMAND:


** PARAMETERS:

** SQL EXCEPTION:
java.sql.SQLException: No connection or command
        at com.progamma.IDRowSetReader.readData(IDRowSetReader.java:175)
        at sun.jdbc.rowset.MyCachedRowSet.execute(MyCachedRowSet.java:447)
        at sun.jdbc.rowset.MyCachedRowSet.execute(MyCachedRowSet.java:442)
        at com.progamma.IDCachedRowSet.execute(IDCachedRowSet.java:247)
        at com.progamma.IDCachedRowSet.execute(IDCachedRowSet.java:314)
        at com.progamma.IDConnection.OpenRS(IDConnection.java:315)
        at com.progamma.IDConnection.OpenRS(IDConnection.java:306)
        at com.progamma.doc.MDOInit.LoadFromDB(MDOInit.java:411)
        at com.progamma.doc.IDDocument.LoadFromDB(IDDocument.java:829)
        at com.progamma.doc.IDDocument.LoadFromDB(IDDocument.java:825)
        at MyWebEntryPoint.ProcessFile(MyWebEntryPoint.java:5762)
        at AutomatedUploads.ButtonProcessFiles(AutomatedUploads.java:1027)
        at AutomatedUploads.ManualProcessFiles(AutomatedUploads.java:671)
        at MyWebEntryPoint.MasterTimer(MyWebEntryPoint.java:7107)
        at MyWebEntryPoint.TimerTickCB(MyWebEntryPoint.java:3698)
        at com.progamma.is.ATimer.Tick(ATimer.java:173)
        at com.progamma.is.TimerHandler.Tick(TimerHandler.java:135)
        at com.progamma.is.ServerSession.Init(ServerSession.java:80)
        at com.progamma.is.ServerSession.run(ServerSession.java:46)
        at java.lang.Thread.run(Thread.java:748)
*********************************************************


So this is with Server Session Imports for both Java/Tomcat/MariaDB and C#/IIS 10/MSSQL 2014. Definitely something changed in the classes from 20.0 to 20.5. For the Java errors it is always when I'm updating an existing document. I'll see if I can narrow it down some more.

UPDATE 2:

I created a simplified version from one of my main apps to demonstrate the issue. Doing an insert into a database using classes in a loop, when the loop runs in a server session (IIS or cassini--either will have an issue). As mentioned earlier I've seen the issue with both MariaDB and MS SQL. It can happen after 20 records or 4995, but eventually the database connection will close and cause errors. Sometimes it spikes the CPU (IIS at 25%, Cassini at 50%), and will stay there with a hung connection. Though sometimes another session will create a new connection and inserts can resume.

How to test using the attached app:

  1. Unzip the attached project to your inde project directory.
  2. To match the project you will need a MS SQL database created named session_import. Change your database connection in the project as needed.
  3. A custom directory is embedded. It will create a cornerstone folder in your csharp folder with an import file of a single record. The code then inserts this same record over and over with a new id.
  4. The timer is set for one minute, so as soon as you compile the app it should start inserting records.
  5. You might have to let it run for a few minutes to get an error, or it might happen very quickly.
  6. If you want to disable the timer and run the process manually, use the Process Files Menu Option.
  7. Logging is sent to the Logs Form.
Remember, Calling the "Process Files" Menu option manually ALWAYS works. The only issue is when the same process runs in a Server Session with InDe 20.5 r10. Previous versions of InDe do not have this problem.
For me this is a show-stopper going to InDe 20.5. Almost every production app I have does some kind of scheduled server session, often processing hundreds to thousands of records on a daily basis.

Session Import Example Express.zip
InDe 20.5 Express
(780.01 KiB) Downloaded 17 times


Thanks...jack
ljwilson
 
Posts: 565
Joined: 26 November 2013, 14:15

Re: InDe 20.5 r10 Server Session SQL Server Connection Error

Postby ljwilson » 10 February 2021, 13:27

I just wanted to follow up that I submitted a malfunction report Friday evening (2021-02-05) and had a patch which fixed it the following Monday!

Progamma will fix it in the next release.

...jack
ljwilson
 
Posts: 565
Joined: 26 November 2013, 14:15

Re: InDe 20.5 r10 Server Session SQL Server Connection Error

Postby r.bianco » 11 February 2021, 7:35

Fine thanks :)
only work and no play makes jack a dull boy
r.bianco
 
Posts: 4392
Joined: 8 November 2010, 16:46

Re: InDe 20.5 r10 Server Session SQL Server Connection Error

Postby ljwilson » 2 April 2021, 12:49

As a follow-up--the latest InDe 20.5 r10b release which fixes an IOS notification issue does not have this patch in it. I went to my service account today and noticed the help desk had already uploaded patched versions of the 20.5 r10b ijlib.* files to fix this server session issue, without me requesting it!

I'm very impressed with the help desk staying on top of things--thank you!

...jack
ljwilson
 
Posts: 565
Joined: 26 November 2013, 14:15


Return to Tips & Tricks

Who is online

Users browsing this forum: No registered users and 1 guest

cron