Possible Bug: Select returning incorrect rows
Posted by:
mysql
Date: October 14, 2005 01:45AM
For a while we have problems on a production server. A simple query ending in "WHERE status = 0" returns rows having status = 1.
We have checked and double-checked the code and have come to the conclusion that the problem is not there. So it's MySQL, the OS, the discs or something else. Since I don't know how to debug this problem to get enough information to file a bug report I'm asking you to help me gather enough information.
- The details:
OS: Debian 3.1
uname: Linux vampire 2.6.7-1-386 #1 Thu Jul 8 05:08:04 EDT 2004 i686 GNU/Linux
MySQL Server version: 4.0.24_Debian-10sarge1-log
Device is software raid managed by mdadm.
# mdadm --version
mdadm - v1.9.0 - 04 February 2005
- The table:
CREATE TABLE `Messages` (
`messageId` int(10) unsigned NOT NULL default '0',
`partnerId` smallint(5) unsigned NOT NULL default '0',
`shortcodeId` smallint(5) unsigned NOT NULL default '0',
`targetId` mediumint(8) unsigned NOT NULL default '0',
`msisdnId` int(10) unsigned NOT NULL default '0',
`status` tinyint(3) unsigned NOT NULL default '0',
`type` tinyint(3) unsigned NOT NULL default '0',
`contentType` tinyint(3) unsigned NOT NULL default '0',
`timestamp` datetime NOT NULL default '0000-00-00 00:00:00',
`networkcode` char(5) NOT NULL default '',
`gatewayId` tinyint(3) unsigned NOT NULL default '0',
`contentId` int(10) unsigned default NULL,
PRIMARY KEY (`messageId`),
KEY `Messages_FKIndex2` (`contentId`),
KEY `Messages_FKIndex3` (`contentId`),
KEY `Messages_FKIndex4` (`msisdnId`),
KEY `Messages_FKIndex1` (`shortcodeId`),
KEY `Messages_FKIndex5` (`targetId`),
KEY `Messages_FKIndex6` (`gatewayId`),
KEY `Messages_direction` (`type`),
KEY `Messages_type` (`contentType`),
KEY `Messages_timestamp` (`timestamp`),
KEY `Messages_FKIndex7` (`partnerId`),
KEY `status` (`status`),
KEY `typeStatusGatewayId` (`type`,`status`,`gatewayId`)
) TYPE=MyISAM
- The query:
# fetch incoming messages
query = """
SELECT messageId, status
FROM Messages
WHERE type = 0
AND status = 0
"""
- The Problem:
When we check the status field on the returned rows the status field is sometimes 1.
- Additional Information:
This query runs every couple of seconds on a table that grows with about 22k rows a day. Normally the problem happens a couple of times a day but between '2005-10-04 14:11:26' and '2005-10-13 21:48:59' it didn't happen. It also happens that I dist-upgraded the morning of the 13th.
Okay. That's about the information I can give you. Please help me in identifying this problem.
Thank you.
Olivier