How did you tidy up my code so easy and well, I am sure you did not just rewrite it?!.

Sorry to disappoint, but I spent about 15 minutes with my editor. A couple of macros for in/undenting, but mostly just by hand. It's a simple, almost mechanical process that I apply to most every piece of badly structured code I look at in detail. It's second nature, and helps me understand the code.

The most complex task was converting your substrs to unpack. I marked all the lines containing "substr", copied them to another buffer, sorted them into order and did a little block c&p to create the variable list and template. That's when I discovered the missing keywords.

For solving your described problem. The first thing I would seek to eliminate, is any bad interaction with the DB.

I'd set a version going that instead of performing the actual DB calls, have it just write the formatted SQL to a file. That will allow you to avoid losing the data because it will be a simple process to use your DBs command line client to apply the SQL manually.

The particular possibility I'm thinking of here is that client is running your DB server out of handles. A few years ago I encountered a similar problem with MySql where the server was configured with a relatively low number of concurrent handles, and a relatively long timeout (~900 seconds). This meant that after a period of particulary high activity, inbound connections to the DB would block awaiting a handle to timeout and become available.

The solution--beyond upping the number available and trimming back the timeout--was to ensure that all statement handles were correctly finished; and all DB handles were correctly disconnected.

You are doing the latter, but not the former, and it might be the cause here. The nice thing is that by writing the SQL to a file, you can quickly find out if it is without data loss.

Anyway, if not, the tidied up code should be easier to debug. You might (for example) consider using Devel::Trace to determine where in the code it is hanging.