Persistent database connections!

Written by SysOp on 2021-04-05 13:00:00 ; Posted in: SysOp, Website; 0 comments

I've been having problems with infrequent random 500 pages. This came to a head as I was experimenting with creating some pages for an as-yet-to-be-seen portion of the site. This is something I thought I had fixed. Well it was no longer a hard fail. But it was still happening. And acting like it was getting cached. Caching a failure?!?! Weird!

But I suspected this was still related to MariaDB server dropping the connection when it was idle for a certain period of time and C++DB not being prepared to handle that. I told it to use the "reconnect" client option! What gives? And has anyone else visited the site and seen this infrequently occurring error, ugly white terse 500 page? I don't do the "80% of the time it works 100% of the time" model. I only do "it works." At least if I can help it. Law 1 & 2 suck!

Change 1:

So what gives? Turns out that the MariaDB[1] client library was reconnecting as its supposed to. So why was I getting the 500 page? The weirder issue was that if I moved off onto another page they worked. So the connection had definitely been re-established. But try a few times on the broken page and it was always broken. Move off, do some other things, come back and viola it worked?!?! What the heck!

Then I ask myself: Should I use a different DB library? But I like C++DB's KISSified interface. And it seems to work well, outside of dealing with the disappearing server. Looking at other tools available I mostly found "gratuitous complexity". I HATE that! >:-[ I've posted to the C++DB/C++CMS email list at various stages in this investigation and heard nothing. But I figure its worth some time to find & fix as opposed to writing my own (again). There is a lot I've done that is already tied to this library. So off to do a deep dive into the C++DB source to figure out what's going on.

What I found was that queries are cached in MariaDB as "prepared statements". When that same query is used again C++DB attempts to re-use the prepared query, inject the parameters and return the results. The problems is that when the connection is dropped the MariaDB client loses the cached prepared query. C++DB doesn't know that and when it goes to reuse it the MariaDB library says, "No can do! The server went away." (paraphrased). Basically the error was getting cached! As soon as the query fell out of C++DB's cache it would all work again, as if nothing ever happened.

So the fix was actually quite simple. Bad docs on the MySQL site had me in the weeds determining how to detect this particular error but the patch to C++DB was relatively simple. Now it re-prepares the query when the "gone away" error occurs, but just once. If the error happens again it just assumes, like the MariaDB client, that the server is really gone. My patched version of the source can be found in the repos.


  1. Most of the stuff in the repositories, outside of CppDB is junk being used for testing and development. This repo will remain for the duration of the site. And other projects I patch for this site will be added.

  2. The original "upstream" source for CppDB is in the "upstream" branch for comparison.

  3. The repository section still needs considerable theming work.

Change 2:

One of my concerns in many of the websites I've developed over the years is: how do I know if someone is running into a software bug I'm not seeing? Django has an excellent solution for this. One I wanted to implement here. And in light of the above issue it would have already been helpful to have.

You don't want to expose site details to potential hackers and the average person can't make heads or tails from a detailed error message. So I've wanted to make sure I get emailed about crashes and hide the details from the end user. They can't do anything with them anyways. So I went on another deep dive, this time in the C++CMS source code. I now have things so "exceptions" (errors) get emailed to me when things go wrong. I'll be getting these changes up on the repository soon also.

My foundation is beginning to look very tidy. This is getting exciting. Now to make those error pages look better ...


I have to say that I really appreciate the efforts of Artyom in his development of C++CMS & C++DB. The code is well organized and pretty well thought out. I would not have been able to make these dives and changes sooo quickly if it weren't so.

Well done Artyom & contributors!

Foot notes:

  1. I use MariaDB, which was forked away from Oracle and made by the original author of MySQL. But what I mention in this post applies to both. For brevity I'm only naming "MariaDB".

  2. Need an "angry" emoji!