MySQL Forums
Forum List  »  MyISAM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Possible Bug: Select returning incorrect rows
2357
October 14, 2005 01:45AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.