Description:
We use a MEMORY table as our user authorization and timeout table. A row is inserted at sign-in using a PHP time() timestamp value 30 minutes greater than the current time. We SELECT the user's row and update it with a new 30-minute timeout timestamp value at every action in our application.
We noticed recently that many customers are repeatedly getting "kicked" out of our app after only a few seconds or minutes of access. Some have this happen repeatedly as often as 6 times in a row.
In my testing on my development machine I was able to reproduce the problem on several occasions and actually see a row disappear from the MEMORY table in-between selects and updates. NOTE: our application has no delete logic at all, so the only way a row can be removed is when the server is restarted and the MEMORY table refreshes.
I have tried dropping the table and recreating it (thinking the structure may be corrupt), but it has not helped with the problem.
Looking at the binary log DOES show the records being inserted and updated, but no other information appears as to why the row might have been removed (for example, a DELETE).
The problems occurs on our production server running MySQL 5.1.40 on Mac OSX Server 10.6.3 as well as our development machines running MySQL 5.1.40 on Mac OSX Client 10.6.3.
The table DEF is as follows:
CREATE TABLE IF NOT EXISTS `sys_auth` (
`co_id` smallint(5) unsigned NULL,
`usernumber` int(10) unsigned NULL,
`cust_id` int(10) unsigned NULL,
`last_timestamp` int(10) unsigned NULL,
`access_ctrl_timestamp` int(10) unsigned NULL,
PRIMARY KEY (`usernumber`),
KEY `co_id` (`co_id`),
KEY `cust_id` (`cust_id`)
) ENGINE=MEMORY DEFAULT CHARSET=latin1 COMMENT='Used for fast user authentication';
How to repeat:
Repeating this issue consistently has been impossible. Although I have witnessed rows disappearing during my testing. I have not been able to come up with any specific set of circumstances that seem to cause the issue to occur in any repeatable fashion.
However, I have set up a debugging mechanism in our code that will detect when a row that should be in the sys_auth authentication table is no longer available. I am seeing anywhere from 10 - 150 occurrences a day of this problem which means it happens quite often. For example: of 40 user sign-in's today, there were 10 occurrences of a MEMORY table row disappearing.
Suggested fix:
No fix known.
My only workaround to-date has been to change the table from MEMORY to InnoDB. This obviously is not ideal for high traffic volume as I now have an extra two database hits for every click in our application.
