MySQL: can a table get stuck in the table cache?

I've asked this question on the ServerFault stackexchange, someone directed me here.

I'm a PHP developer and am responsible for a set of PHP scripts that run on a shared host running FreeBSD, that has a MySQL server (version 4.1.14-standard). Before anyone makes the remark: the decision to use a MySQL 4 server was not made by me, and any decision to upgrade is unfortunately also not mine to make.

Last January, I added a table to the database, and updated the PHP script so that each time the application is started, a row is inserted into this table. I set up the table so it started with a number of rows already in it. In the mean time, our client has been merrily using their application, causing several rows to be added to the table.

Last week, I wrote a PHP script that fetches all rows from the table and builds a report from the result, and ran it, only to find that several rows were missing. In fact, only the rows I started off with, seemed to be present in the report. I attributed this to no-one having used the application in the mean time. Yesterday, however, I decided to check if this was actually true. So I fired up phpMyAdmin, exported the database, and ran the report locally, only to find new data present and accounted for. So I checked if the code on the live site was up to date, and this is the case. Stumped, I ran the report on the live site again, and presto: there the missing rows were.

My reasoning:

• The issue has to be some kind of cache thing, because the table has two DATE columns, they are filled with INSERT queries, and possibly updated with UPDATE queries, each time with NOW() as the value, never any other value, and the dates in the table were spread across the past half year. Last week I saw no new rows since January, but yesterday morning I saw all these rows with these dates in them: the new rows cannot all have been added in the past week.

• I did not publish any code, or push data to the MySQL server, since I first ran the report.

The table cache does not store data, only MySQL table structs. The query cache cannot return old results because all the queries sing it are invalidated on write, and selects are blocked until that happens (actually causing some contention problems).

I will not discard a MySQL bug, as you are using a 9-year old unsupported version of the server, but you were too quick to discard cache issues/errrors on the other many layers of your application (client, server code, connector, your report code running on the wrong server, etc.).

To solve problems like this, you need to monitor queries sent and results returned at MySQL level and others, so you have enough information to identify the problem. I also recommend you to get familiar with the mysql command line client, as it is the best way to debug server issues.

StackExchange Database Administrators Q#73195, answer score: 3