| Bug #67818 | select statement broken? | ||
|---|---|---|---|
| Submitted: | 5 Dec 2012 19:05 | Modified: | 6 Dec 2012 16:07 |
| Reporter: | Richard Coco | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: FULLTEXT search | Severity: | S2 (Serious) |
| Version: | 5.5.20 | OS: | Linux (Using Amazon RDS) |
| Assigned to: | CPU Architecture: | Any | |
[5 Dec 2012 19:17]
Sveta Smirnova
Thank you for the report. Please submit dump of problem table.
[5 Dec 2012 19:22]
MySQL Verification Team
Thank you for the bug report. Please setup a test machine with the last server version 5.5.28 (your version 5.2.20 is quite older). If the issue continues please provide a complete test case, a dump file (create tables and insert data), print your real result data and expected ones. Thanks.
[5 Dec 2012 20:29]
Richard Coco
just for the record, I am using version 5.5.20, not 5.2.20 (as specified in a previous comment).
[5 Dec 2012 20:30]
Sveta Smirnova
Thank you for the feedback. Anyway this version is older than current 5.5.28 and we need repeatable test case.
[5 Dec 2012 20:48]
MySQL Verification Team
what's the bet "CHECK TABLE conference EXTENDED" shows it was corrupt :)
[5 Dec 2012 23:10]
Richard Coco
here is the result of that on the tables involved in the queries i am performing... mysql> CHECK TABLE conference EXTENDED; +-----------------------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-----------------------+-------+----------+----------+ | mycloud.conference | check | status | OK | +-----------------------+-------+----------+----------+ 1 row in set (0.70 sec) mysql> check table conference_notification; +------------------------------------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +------------------------------------+-------+----------+----------+ | mycloud.conference_notification | check | status | OK | +------------------------------------+-------+----------+----------+ 1 row in set (0.12 sec) I will try to find time to install a v5.5.28 as suggested, but i am currently under the gun and i do have a work-around for the bug/issue...
[5 Dec 2012 23:12]
Richard Coco
oops...forgot 'extended' for the 2nd table check. just in case it matters... mysql> check table conference_notification extended; +------------------------------------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +------------------------------------+-------+----------+----------+ | mycloud.conference_notification | check | status | OK | +------------------------------------+-------+----------+----------+ 1 row in set (0.12 sec)
[6 Dec 2012 0:28]
Elena Stepanova
For a note, there is no evidence so far that the select is actually doing something wrong. If there *are* rows with the numbers (1318899 1318899 and alike), which can happen if e.g. a client application sometimes misbehaves, or if the table was initially populated with some test data and not cleaned up afterwards, or on many other reasons, then quoted results are valid. SELECTs with LIMIT without ORDER BY may return whichever 10 rows they prefer. Same story for COUNT without GROUP BY, it may pick any value. I suppose COUNT with GROUP BY would have resolved the mystery.
[6 Dec 2012 15:26]
Richard Coco
I just *knew* I was going to regret posting this "bug". All along it felt like the proverbial "there's a bug in the compiler!" or "there's a bug in the kernel" false alarm. Well...the laugh is on me. it appears there was in fact a rouge test putting data in, taking data out in a DB instance to which it should never have been connected (it was apparently an accident, not malicious). Long story,short: "Never mind". I deeply apologize for the noise. feel free to close this "bug" entry...and excise my name from it! ;-) regards, rich
[6 Dec 2012 15:33]
Sveta Smirnova
Thank you for the feedback. To determine if this is MySQL bug or not we need dump of your problem table.
[6 Dec 2012 15:47]
Richard Coco
Sorry, I guess I was not sufficiently clear. There is no bug. My 'bug report' was a false-alarm due to foolishness on my end. feel free to close out my 'bug report' as "NO BUG". rich
[6 Dec 2012 16:07]
Sveta Smirnova
Hi Rich, thank you for this update. Closed as "not a bug" now.
Description: I am using the mysql client on an Amazon RDS MySQL 5.5.20 instance. In all other respects (see below) things are working fine. However, the following illustrates what appears to be a bug in MySQL. Here is a description of the DB table ('conference') in question: 'confGUID' is the primary key. 'name' is an INDEX in the table. mysql> describe conference; +-------------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------------+--------------+------+-----+---------+-------+ | confGUID | varchar(64) | NO | PRI | NULL | | | action | int(11) | YES | | NULL | | | name | varchar(64) | NO | MUL | NULL | | | dateCreated | datetime | YES | | NULL | | | dateUpdated | datetime | YES | | NULL | | | fedFailureReason | int(11) | YES | | NULL | | | fedStatus | int(11) | YES | | NULL | | | inFederationPIN | varchar(32) | YES | | NULL | | | inIsFederationEnabled | bit(1) | YES | | NULL | | | inIsLocked | bit(1) | YES | | NULL | | | inLegacyNumber | varchar(32) | YES | MUL | NULL | | | inLegacyPIN | varchar(32) | YES | | NULL | | | inNumParticipants | int(11) | YES | | 0 | | | inWebHookFormat | int(11) | YES | | NULL | | | inWebHookPwd | varchar(32) | YES | | NULL | | | inWebHookUname | varchar(32) | YES | | NULL | | | inWebHookURI | varchar(255) | YES | | NULL | | | isMediaSecured | bit(1) | YES | | NULL | | | remInfoConferenceName | varchar(64) | YES | | NULL | | | remInfoConferencePin | varchar(32) | YES | | NULL | | | remInfoConferenceDomain | varchar(64) | YES | | NULL | | | remInfoFromClientName | varchar(64) | YES | | NULL | | | remInfoRemoteEntityID | varchar(64) | YES | | NULL | | | remInfoInbound | bit(1) | YES | | NULL | | | remInfoIPCRequestID | varchar(64) | YES | | NULL | | | state | int(11) | YES | | NULL | | | uri | varchar(255) | YES | | NULL | | | version | int(11) | NO | | NULL | | | vmID | varchar(64) | YES | | NULL | | | webhookformat | int(11) | YES | | NULL | | | webhookpwd | varchar(32) | YES | | NULL | | | webhookuname | varchar(32) | YES | | NULL | | | webhookuri | varchar(255) | YES | | NULL | | | app_id | varchar(64) | NO | MUL | NULL | | | vm_id | varchar(64) | NO | MUL | NULL | | | numClients | int(11) | YES | | 0 | | +-------------------------+--------------+------+-----+---------+-------+ 36 rows in set (0.02 sec) Here is a broken query: mysql> select confGUID,name from conference limit 0, 10; +----------+---------+ | confGUID | name | +----------+---------+ | 1318899 | 1318899 | | 1679792 | 1679792 | | 1704197 | 1704197 | | 1790821 | 1790821 | | 1952495 | 1952495 | | 2322716 | 2322716 | | 2421745 | 2421745 | | 2467356 | 2467356 | | 2485216 | 2485216 | | 2632492 | 2632492 | +----------+---------+ 10 rows in set (0.02 sec) Absolutley no idea where those values are coming from! NOTE: I get the exact same results if I were to switch the order of 'name' and 'confGUID' in the above select. If I add a 3rd column, it works as expected! mysql> select name,confGUID,action from conference limit 0, 10; +------------------------+--------------------------------------+--------+ | name | confGUID | action | +------------------------+--------------------------------------+--------+ | Daves First Conference | 0003b794-ae5c-4d99-ada8-7d2f820f398b | 1 | | Daves First Conference | 00077b41-e7a3-4276-a35a-55d5838f4886 | 1 | | Daves First Conference | 00184022-8f1c-4e3c-9c92-9fef59881cb7 | 1 | | JzceVqrPyQSFprJ | 001c248e-482d-4a11-bfbc-882a6608ca7d | 1 | | WiHZnpyoYhNesmw | 001e33fa-4a21-4b3c-8d5e-d7c666be8665 | 1 | | WJcnIdqqjyUjALa | 002037db-22cb-40f2-9558-69b943df35e7 | 1 | | Daves First Conference | 0021b83b-f0f0-452b-8275-60fd0517f5d5 | 1 | | Daves First Conference | 00247b4c-1cb3-4817-b034-81b21069cf4d | 1 | | Daves First Conference | 002af987-381d-470d-abab-04736fe766a8 | 1 | | zMEsNwQclcXwVch | 00320b23-c646-444a-b212-f7559141b548 | 0 | +------------------------+--------------------------------------+--------+ 10 rows in set (0.02 sec) If I leave out confGUID it works... mysql> select name,action from conference limit 0, 10; +------------------------+--------+ | name | action | +------------------------+--------+ | Daves First Conference | 1 | | Daves First Conference | 1 | | Daves First Conference | 1 | | JzceVqrPyQSFprJ | 1 | | WiHZnpyoYhNesmw | 1 | | WJcnIdqqjyUjALa | 1 | | Daves First Conference | 1 | | Daves First Conference | 1 | | Daves First Conference | 1 | | zMEsNwQclcXwVch | 0 | +------------------------+--------+ 10 rows in set (0.02 sec) Even without the 'LIMIT' clause, something is broken... mysql> select name,confGUID,count(*) from conference; +---------+----------+----------+ | name | confGUID | count(*) | +---------+----------+----------+ | 1318899 | 1318899 | 27439 | +---------+----------+----------+ 1 row in set (0.03 sec) Not sure what else to say. I almost hesitate to submit this since it seems so fundamental a bug that I keep thinking it must be something I am doing. But much investigation indicates that it's not me... How to repeat: any time I run 'select confGUID,name from conference limit n, m;' the problem occurs. It also occurs if I use the "limit m offset n' syntax. Suggested fix: beats me...