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.
It can't be a browser cache issue, because I wrote the report script last week: if it were a browser cache issue, it would have been a cache entry from January. Since the script did not exist then, the information can't come from my browser cache; and besides, we web developers clear our browser caches weekly, if not several times per day and I am no exception.
If it's a cache issue and it's not a browser cache issue, it has to be a MySQL cache issue.


However, it can't be a MySQL cache issue either, because the query cache gets invalidated as soon as a new row is inserted, which has happened several times the last half year. It also can't be the table cache, because table_cache is set to 64, open_tables is 64, and opened_tables is about 13 million. So if there is something wrong with the table cache at all, I'd expect "too new" data, instead of "too stale" data.

My working theory, for lack of a better one, is that somehow, despite my reasoning, the table got "stuck" in the MySQL table cache, and that exporting the table data with phpMyAdmin caused the table to "unstick".

All database queries the application does, are done with PHP's mysql_* functions: again, not my decision: I'd much rather use PDO, but am stuck with PHP 4. Anyway, the application does not use Memcached, so that's ruled out.

My question is: why did I get the older version of the table data, and is there any way I can prevent this from happening again?

The table in question:

CREATE TABLE `offset` (
  `id` int(10) NOT NULL auto_increment,
  `version` int(10) NOT NULL default '0',
  `user_id` int(10) NOT NULL default '0',
  `client` varchar(255) NOT NULL default '',
  `created_on` datetime default NULL,
  `modified_on` datetime default NULL,
  `hidden` tinyint(1) default '0',
  PRIMARY KEY  (`id`,`version`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=302 ;
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.