Problem:

You have a well-written web application. It’s throwing exceptions, you’re handling the exceptions.  You’re starting db transactions and committing or rolling them back.  You are logging exceptions and errors to your database for forensics and review.

Two to three years pass, the web application, because it’s well written, modular and de-coupled, has grown and you’ve added new features – perhaps even released several major updates – suddenly you realize that not all logging is getting recorded.  Somehow, through the complexity of exception handling and commits and rollbacks you have gotten to the point where you are accidentally rolling your logging inserts back.  You may have alerted the dev team but there’s nothing recorded for them to review.

I have good news.

There is a solution.  It’s simple, and obvious (perhaps so obvious you’ve already taken this step yourself).

Use a different database connection. Transactions are connection specific.  Rolling back on your main database connection will leave your logging connection untouched.

If you’re going to use a different database connection, you might as well take the time to move your logging to a separate database as a best practice.

You should also employ lazy connecting – don’t connect to your logging db unless you need to log something.

Warning

Make sure you know which db connection to use if you’re using mysql and need the last insert id from an auto-increment logging table id.