MySQL Bugs: #18389: Select statement malfunctioning.

Bug #18389	Select statement malfunctioning.
Submitted:	21 Mar 2006 13:39	Modified:	28 May 2013 10:56
Reporter:	Fahed Bizzari	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	4.0.25-standard	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
We are suffering from a problem similar to <a href="http://bugs.mysql.com/bug.php?id=16856">bug #16856</a>.

We have a rather large table having 371,639 rows, 35 columns and 9 indexes. The data is 115,298kb, the index is 68,538kb and the total is 183,836kb.

This table is polled very frequently for rows with certain field ='0'. However, it is now occassionaly happening that rows with certain field ='2' are also being called with this very same statement causind duplicate messages to be sent.

We checked the offending table, run repair/optimise operations and deleted the previously offending rows, but the problem has continued.

We are a <a href="http://www.tm4b.com/">bulk sms gateway</a> and messages that have been delivered a long time ago are being resubmitted for delivery in this situation.

Please advise.

How to repeat:
It happens irregularly and attempts to re-enact it manually have never succeeded.

The two links got printed out incorrectly. THey should have been:

http://bugs.mysql.com/bug.php?id=16856 - The similar bug
http://www.tm4b.com/ - Our site

Thank you for a problem report. Please, send the information requested in that bug #16856: table structure, sample data etc.

Please, send also the results you got and you expected.

Thanks for the fast reply.

The results included rows with status=2, when only status=0 should have been picked up.

Is the problem repeatable from mysql command line client? If no, please, send a  code of a test case (using ODBC calls?) that shows it. I was not able to get rows with status=2 in mysql client:

mysql> select * from mt_messages where status=0 limit 25;
Empty set (0.01 sec)

mysql> select distinct status from mt_messages;
+--------+
| status |
+--------+
| 2      |
+--------+
1 row in set (0.00 sec)

mysql> select * from mt_messages where status=0 limit 25;
Empty set (0.00 sec)

mysql> select * from mt_messages where status=0 limit 25;
Empty set (0.00 sec)

mysql> select * from mt_messages where status=0 limit 25;
Empty set (0.01 sec)

mysql> select * from mt_messages where status=0 limit 25;
Empty set (0.00 sec)

mysql> select * from mt_messages where status=0 limit 25;
Empty set (0.00 sec)

I do appreciate your assistance ever so much, but it is really difficult for the problem to be re-enacted as it happens irregularly.

Furthermore, the data we have provided you is a tiny percentage of that which exists.

I have re-indexed the status field with a hope that this rectifies the problem. I hope so!

Sorry to trouble you, but i just don't think you will be able to re-enact the problem as we ourselves have not been able to (although we have logged it's bad behaviour) to reach this conclusion.

Please, reopen this bug report when you'll have a test case: a set of steps that demonstrates the problem each and every time, on a latest available version of server and/or ODBC-driver.

Thanks for the help.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I'm closing this bug because I can not continue without feedback from the reporter. If you have new info, please reopen the report.