Reccuring bugs
When I left the office yesterday, I was stuck on one of the weirdest problem I had ever encountered. Errors I had fixed days ago started appearing. Debugging procedures, functions and triggers can be quite a pain. The reported errors can be about anywhere and have mostly nothing to do with the line of code which makes the call. At best, you get an error message and with some luck, you can guess which trigger is causing the problem. In my case, the error was referencing to a field never mentioned in any procedure, function or trigger.
I solved the problem 5 minutes after I got back in the office in the morning. Stepping away from the problem and getting some sleep did help. The problem was actually with the query cache. In some cases, pre-compiled queries were stored and kept using the old versions of the triggers. I had tried restarting the MySQL server yesterday, and it didn’t solve the problem. This morning, I restarted the Apache server and everything started working again. Working is a bold statement. I had made multiple small changes in those procedures in the past few days. When I tested them, they were working. In fact, the previous version was indeed working. The new one never ran. Basically, once I restarted the service, I got to solve a dozen minor problems.
So, was it query cache or prepared statements? I don’t really know. I doubt the problem was in the database server as I restarted it a couple of times. It might be PDO, the PDO MySQL driver or the MySQL library. No matter where the problem is from, from now on, I systematically restart the Apache server after each change in the stored procedure code.