Suspected indexing problems TPS file after table reconfigure and index rebuild

1

Environment: Windows Server 2022 with Widows 11 WS’s. Clarion 9 exe, 20 dll’s, and 3 dozen or so tables. In the last 26 years I have done this same thing dozens of times. Add some fields to a table, rebuild the table, change the Dictionary, recompile all the dll’s and the exe. All has been good, never had an issue. I just did this a few weeks ago with 3 TPS tables, no problems. I found it necessary to modify an additional table.

I have one primary file that has joins to multiple tables, one of these tables was modified.

When I test the Clarion app after the one table rebuild and recompiles. Everything works fine on a single workstation and also running the app on the Server where the databases are stored.

As soon as ANOTHER USER starts the application and selects window form for THE MAIN file search. Blue circle just spins. Also the WS where I was using the app then behaves the same, where it HAD JUST WORKED MOMENTS previous. If I log in to Server and try the app with databases on server THAT WORKS.

I did not change the MAIN file I only changed one of the files that joins to it.

Selecting other window forms with different types of Index accesses works. For example: the Transactions file I modified, searching persons by name or address, So it seems to just effect the main file that links to the Transactions file.

This all works fine on my single WS development system and the network WS development system, with only a SINGLE user after modifications.

This happened two weeks ago, reexamined all the work. Tried it again by rebuilding the TPS on a WS instead of the Server. Same thing this weekend.

Does this look familiar to anyone? Any suggestions on where to look or different procedure?

I do modifications and recompiles pretty frequently, so I am baffled.

Using the previous programs and table FIXES THE PROBLEM.

2

I just realized something. The “other” user failed to get the NEW APPLICATION, this happens automatically when logging in but she was already logged in. I had her get the application that matched the newly rebuilt table and this is when things went wrong with accessing records. This happened the same two weeks ago. This was not unusual to me since I have seen this multiple times in 26 years. However, not with Clarion 9 that I can remember.

Is there some issue with hitting a TPS file, in Clarion 9, with the wrong record configuration that could be a causing this issue? Grabbing at straws. . .

3

Usually indicates the program has hung or crashed.

Path Key Value (DWORD)
HKEY_CURRENT_USER\Control Panel\Desktop AutoEndTasks 1
HKEY_CURRENT_USER\Control Panel\Desktop HungAppTimeout 2000
HKEY_CURRENT_USER\Control Panel\Desktop WaitToKillAppTimeout 5000
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control WaitToKillServiceTimeout 5000

Is the server caching the files for each user?

Checking for some sort of folder and thus file caching will be my first port of call.

Powershell
Get-SmbShare -Name "YourShareName" to view the current -CachingMode parameter (Manual, Documents, Programs, or None)

I know Group Policy can signature hash an exe in order to allow or deny running it. I dont know if any security software like antivirus could be providing some sort of automated action to allow, deny or cache. Lots of different types of server AV programs out there, and I’ve seen some good ones in my time and some which require alot of setup.

The system admin should just set the folder up to allow the exe full Read/Write access to the folder, this then lets your apps create, read, write and delete files & subfolders.

The exe file path would be something like \\ServerSMBShareForTPSFolder\Myfile.tps where\\ServerSMBShareForTPSFolder\ would be the folder shared that contains the tps files.

If the SMB share is pointing directly to the folder, thats the simplest, but if say the drive root C:, S: is a SMB share, then check Permissions and the Folder Traversal bypass in group policy.
You might have a situation where you dont have permission to traverse down to the TPS folder but somehow you are able to… usually because of some other obscure security setting being misconfigured.

Edit.
If you do find a security setting or permission out of place, I’d create a user account for each non-window service. Service’s have more permissions than even the Administrators group. Nothing monitor’s pipes, out of the box I find windows very open to put it mildly. Start running webservers on the same box and you are asking for trouble. I speak from experience, its why I dont do this sort of stuff now.

4

I’ll research things you posted. App has been in use for 26 years. On Windows Server 2022 for 3 years. Four weeks ago I rebuilt 3 tables and recompiled with no problems. This time rebuilt one table and recompiled and does not work. IF YOU LET the form sit it will eventually come back but when you search for a file the same thing. It application acts like it’s searching/waiting for something. To restore this I changed the name of the NEW tps file and then changed the previous one to the correct name. Put the dll’s and exe back to the ones used on Friday and bingo it all works again. “Law of parsimony” seems more to apply here,just need to figure out what that one simple thing could be.

How do folks usually rebuild indexes? I have just been using TOPSCAN to rebuild all indexes.

5

I assume you have checked this cuz it does sound like some sort of caching/oplocks problem, especially the wait time seen before anything happens, but cant rule out some other AV/security type thing happening. Microsoft Defender can be slow letting things through so I disable it from monitoring some folders. Even when there is no web browser open or any other client app using the web, Wireshark will show a windows box communicates with Microsoft online all the time.

6

I’d say Yes.
So just to be clear about the scenario.
You’re changing the file layout and the exe to use the new layout,
but one user is already logged in using the old program and doesn’t get the new exe.
They then access the new file using the old program.
Yes that will absolutely corrupt the file. When they start the exe they should get an error 47 but sounds like thats not happening.
I’ve also seen something similar if not all dll’s were recompiled to get the new file layout.

7

I have Group Policy that gets the application on login. User WS was locked over the weekend, so they just entered password. Application was not reloaded on WS, so the old exe and dll’s were used. BTW in the last 26 years this has happened before. There is an error and if they SignOut completely and back in they get the any new updates. Since it’s happened before with no issue, I didn’t think anything of it. UNTIL THIS HAPPENED and I started going through every possible event/situation I could think off. There is an error that is displayed, I don’t remember what the number is. I am trying again on Tuesday night. So I’ll make sure everyone gets the message to logout. I work remotely.

8

I’ll try this. Tues morning 8/12 same thing. This time all users got the new programs that go with the file that I modified. Users open app and select form for file search, then it starts “waiting” blue circle. When this is happening I can log into Windows Server 2022 and run the app there and IT WORKS, while everyone else is waiting. For the users the blue circle stops and then they type something to search and the same thing happens. Restoring the file and the programs that were compiled LAST MONTH when I changed other tables. . .Returns everything to a sane state. So something has definitely changed somewhere. WHAT I DID notice that is NEW is when I first use the “recently” compiled programs I get a box from Windows Defender that says does not recognize program “Do not Run”, If you click on “more info” you can select “Run Anyway”. This has never happened before. When I restore the programs compiled A MONTH AGO, this DOES NOT HAPPEN. Why does exe and dlls from a month ago NOT DO this and ones compiled a few weeks ago for TPS file update generate this Defender popup? Investigating

9

Got new info, trying this simple thing first: Something has changed on my Windows Server 2022 network in the last few weeks. Don’t know what or when BUT: If .exe is not authorized to run on, Windows Defender will block SMB ports; tcp 445, 139; udp ports: 137,138. Working on Group Policy to push this to workstations. WHY IT WORKS for Clarion app and dlls compiled in June BUT NOT THE ONES compiled in July, I have no idea. My experience would tell me that “MICROSOFT likely has NO IDEA either”.

10

Have a look at the windows Defender exclusions here:

MS Windows Defender - Exclusions

This is probably going to be the most relevant for your situation.

Processes to Exclude

This exists because I’ve had Defender prevent VMware workstation from running and guest OS’s.

Are you using Capesoft’s File Manager?

I havent used it for years since moving to MS SQL server, but does it create temporary files or does everything in memory now?

If it does create temp files, check the exe has permission to create temporary files in the folder and the user has permission to create new files in the folder.

This is the part of windows defender that phones home, its detected a change in the app and will block it, thats why I say check the exclusions in the my github link above.

Because of the way Windows works, its extremely vulnerable to viruses so Defender is MS attempt to mitigate bad software running even in a windows server environment…

There are times a popup toaster message will not appear so you wont even see the message you mention. Thats why I would just exclude Defender from monitoring the app and its files on the server.

Personally I would create a new Active Directory group for your app and users. It will contain things better. Unfortunately MS Active Directory doesnt go so far to allow adding programs to AD groups to limit their scope, thats group policy job, so where permission hacks/leaks occur is when someone is Admin and can traverse folders and access drives because Group Policy is not setup properly or just in out the box configuration. Security on windows is all a bit piece meal imo and thats where problems occur. Some companies dont even know they have been hacked because they dont log their network traffic either!

Anyway this youtube vid should walk you through creating an AD group and adding a user to the group if you want to go that far.

11

Problem that I was having was: After rebuilding ONE TPS file by adding two fields and recompile dll’s and exe. . .More than one user on Windows network cause long delays in accessing file that was linked to the TPS file modified.

  1. Created “test environment” on server that used the same resources. New file and recompiled programs NOT performing as current production.
  2. I was rebuilding the file using SAME METHOD I have used since Clarion 4 in the 1990’s. Unload the file and read in text file, split into fields and STREAM/APPEND to new TPS.
  3. Used TOPSCAN to rebuild indexes SAME PROBLEM so THEN USED program to FULLBUILD ON and BUILD while waiting for events to finish.
  4. 1,2,3 results the same.
  5. Generated a file conversion program from the Dictionary, added code with FULLBUILD ON and BUILD wait for events to finish.
  6. WHEN I OPENED THIS FILE in TOPSCAN, unknown error reported and could open.

So far I HAVE NEVER seen anything like this. Next I. . .

Used TPSFIX against the current production file that I needed to update.

Possible Errors Found: 1

Last Error: Record data overrun

After this file was rebuilt, I used the “File Conversion” program from the Dictionary with this newly “fixed” file.

TOOK THIS newly converted FILE to my “Test Environment” and VOILA the PROBLEM IS GONE.

Wow! I had to eliminate everything that suggested this was ALL MY FAULT or SYSTEM fault, been here many times in the last 50 years.

The interesting thing is the “unloaded” file that was failing. I was simply reading the TPS file sequentially and generating a delimited text file that I would read back in and then move each element into the new TPS record and STREAM/APPEND and build index. There MUST HAVE been some corrupted data from the record overrun that caused an index to get corrupted on build, which is why my problem looked like an indexing issue.

This was new for me since I have never had any TPS file issues, 47 files some with millions of records.

LESSON HERE for me: If there is a bizarre unknown problem involving a TPS file, the first step is have TPSFIX read the file.

12

How many Users are we talking about?

What is the network like?
All self contained in a single building?
Are you using long (100metre) runs (Cat5/6) (100metre) with repeaters/switches?
Shielded cables, terminated properly?

A single bad network cable will cause big (minute long) delays with a network using ISAM files like TPS, DBase etc.

If you you are using Managed switches, check for packet retries and other faults in the switch logs.
Unmanaged switches tend not to have that sort of functionality, they are just plug and play.
Hubs broadcast to all ports, so you can plug Wireshark into one port and use Promiscuous mode to listen to everything to see if there are packet retries taking place.

I’ve seen it where industrial equipment will cause interference but it wasnt used all the time so it was a periodic problem until they put better shielded cables in.

Moving to SQL server would be prefered. Its less network traffic so less delays all round except when windows users log on and off and synch their userprofile between the server and the workstation if using windows roaming profiles.

Edit.
For a quick fix, I would use the IP Driver on the windows server with the TPS files. This makes ISAM files work a bit like a SQL server, so the bulk of the record fetching and sorting takes place on the Windows Server, like an SQL server, both do this in memory for maximum speed gains, then send the results down to the workstation.

Using ISAM files without the IP Driver means all the records from the file(s) sorted by a Key order have to be sent over the network where the workstation does the final sorting, and filtering when and where a filter is used, before displaying on screen. This is the slowest way and generates the most amount of network traffic.

13

No problems with TPS files for 27 years. Client will balk at paying for this development. Slow access problem disappeared after using TPSFIX for “Record data overrun” and then converting that file with two new fields and rebuilding indexes. The record data overrun was causing some data issue that affected the building of indexes (Occam’s Razor). I am going to unload the OLD file and the NEWLY converted fixed file and do a data compare, to see if I can discover what data, if any, caused this. There is over a million records in this file.

14

Small TPS files are generally not a problem, especially with todays hardware, like Gb switches, SSD/NVMe hard drives, more ram, faster ram and faster CPU’s with more instruction sets, I mean Xeon’s just fly.

But, roll back 20 years and the hardware back then was a lot slower and once those TPS files start growing, the time taken for record and file locks to negotiate becomes much more noticeable.

Saying that, there’s more built into windows today, and arguably Windows has got slower in places with some of the extra security built in.

If using ABC, you can increase the length of time spent waiting for record & file locking in the classes, and should be able to do similar with the Clarion templates but even the hard disk file system can be a factor. NTFS has file streams which can make things faster than a FAT32 or ExFat file system.

A million records sound like a lot, but how many bytes is the record length?

This is where file checksums wont work sadly. You might be able to spot some thing using a hex editor like the free HxD, but a million records could be like looking for that needle in a haystack. You could probably build some RegEx strings to scan for the problem but its not going to be any mean feat.

One thing I have found in the past with corrupted TPS, is to use TopScan to look at the data using the different keys. Usually one of the Keys has messed up and you can see problems with the sort order. In fact I would probably do this first before using a Hex editor.

If you log and analyse the network traffic, you should get see how much data is being shifted over the network. If you know your user base, you might be able to see what parts of the program generate alot of network traffic which could be used to see if any changes to the TPS file structure could reduce the network traffic.

For example if monthly reports generate alot, consider a background process/service/dct trigger that builds a TPS file with the collated monthly data. Its what all the mainframers and early PC users had to do, to get around the lack of speed in the olden days.

If this file conversion process isnt running on the server, see if it can happen on the server, then you speed up the conversion process and free up the network. Networks can introduce problems, especially when under load so removing factors from the conversion process can all help.

15

Thanks for all the good advice, I will look into IP Drivers. Record size is 205 bytes, and there are 13 users logged into Clarion app. I could not find any issues causing my problem. I only know that using TPSFIX to find a Record data overrun and then using the output to reconfigure the file and rebuild keys FIXED THE issue I was seeing. After 20 years of essentially maintenance mode on this software. . .Starting Sept of 2023 I replaced Server and software, Workstations, network equipment and started working on numerous software features that have been on hold; for a Client I had in the 1990’s when I was fulltime self-employed. I am finding lots of things that have been overlooked and unknown for 20 years; particularly since I spent the last 9 of 20 years on Linux development in C and Python. I am “retired” now. Ha Ha.

16

Its not alot in terms of size, the record limit is about 8000 bytes but a million records isnt a small number.

I would try the IP driver first. I think Capesofts FM2 or FM3 rebuilds keys in a similar way after conversion.

Having been self employed for virtually all my adult life in the UK, I wont ever have the luxury of retiring.

17

I sort-of-feel-that, This is part time for me now, I will need to find a Clarion developer in Maryland to turn this system over too; in a few years. It was started in 1997 and has been the life blood of this legal services firm. There was full-time work for the first 5-6 years of development. Now the time spreads from days to weeks a year. Except the last two years it’s been a few months a year.

18

Maryland only, to keep things within a single jurisdiction or other reasons?

There are loads of Clarion devs around, but keeping up with legislation is a job in itself.

Here in the UK, keeping up with “compliance” paperwork (regulations & legislation) for the financial sector has grown enormously over the decades and there are loads of tax havens around the world that money just gravitates too for some strange reason… :wink:

19

Software generates a lot of documents for Maryland Court System. Plus laws are changed that affect court forms and various processing requirments.