AI Saves the Day

An online customer encountered a rare problem with a database which stumped me; AI came to the rescue.

A friend of mine sells an insurance management system to agencies around the U.S. It's been a successful niche product for over 20 years, is written in Delphi 2007 so runs only on Windows, and uses the Firebird database on the back end. Until a few years ago, his customers all hosted their own local database server (or a designated user workstation) to hold the data. The application is very solid and chugs along with virtually no support calls related to corrupt databases.

About six years ago, his customers were clamoring for an online solution so he asked me to help and we set up a couple of servers and started hosting his software and database in the cloud. One of the servers runs the Firebird database which easily handles the 70-90 users among a couple of dozen insurance offices.

Recently we ran into a rare database problem. Every once in a great while, a PC will get disconnected from the database. Most of the time, the server recognizes it and cleans up the connection but in rare cases, it thinks the user is still connected. When this happens, the users is blocked and the only recourse in the past has been to restart the office database server. Each insurance agency has their own database but in the cloud, all the database requests go through one database server, so I didn't want to disrupt the other 80 or so users to get this one user back online.

I knew there must be a fix but I don't use the Firebird database very often and encounter errors like this even less so wasn't sure how to rectify the situation. After mulling over several possible ways to handle this, each with multiple downsides, I decided to ask AI for a suggestion.

Here's the short conversation I had with Claude:

DC: how do you tell firebird 2.15 to clear connections from a database?

AI: To clear connections from a Firebird 2.15 database, you can use the gfix utility with the -shut command. Here's how you can do it:

1. Open a command prompt or terminal.
2. Use the gfix command with the following syntax:

gfix -user SYSDBA -password masterkey -shut -force 0 database_path

Replace masterkey with your SYSDBA password if it's different, and database_path with the full path to your Firebird database file.

The -shut option shuts down the database, and -force 0 disconnects all users immediately.

3. After running this command, the database will be shut down and all connections will be cleared.
4. To bring the database back online, use:

gfix -user SYSDBA -password masterkey -online database_path

This method forcibly disconnects all users, so use it cautiously in a production environment. If you need a less disruptive approach or more specific control over which connections to clear, you might need to use Firebird's monitoring tables to identify specific connections and then use the API to terminate them individually.

Would you like me to explain any part of this process in more detail?

I was able to apply this fix quickly and it only affected this one insurance office, which was soon back to work.

This is the main reason I'm loving AI and using it more and more: it can find things I've forgotten about very quickly. It also allows me to be a jack of many trades and look like a master of all!