MySQL Bugs: #10164: Simple GROUP BY with index gives: ERROR 1032 (HY000): Can't find record in ''

Bug #10164	Simple GROUP BY with index gives: ERROR 1032 (HY000): Can't find record in ''
Submitted:	25 Apr 2005 23:20	Modified:	7 Jun 2005 12:39
Reporter:	Peter Brodersen (Candidate Quality Contributor)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	4.1.11	OS:	Linux (Linux 2.4 debian stable)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Hi,

I'm using 4.1.11-Debian_0.dotdeb.0-log (debian backport to stable) server, and Ver 14.7 Distrib 4.1.11 client.

I have a table where a simple query for a MyISAM table results in a spurious error message:
ERROR 1032 (HY000): Can't find record in ''

I'll attach the table files to the bug report.

This error only occurs when a key does not exist for the specified field.

I can't figure out where the error is. A copy of the table gives the same problem as well, and the "Can't find record in '' " error ought to contain a table name and not just an empty string at that position?

I have a couple of gigabytes free space.

How to repeat:
The following query:
SELECT COUNT(*) AS amount, mx FROM mxtest GROUP BY mx ORDER BY amount DESC LIMIT 40;
.. results in the following error after a few seconds:
ERROR 1032 (HY000): Can't find record in ''

A couple of SHOW PROCESSLIST during the query reveals the following stages (I might have missed some): Copying to tmp table, converting HEAP to MyISAM

The following gives the same error:
SELECT COUNT(*) AS amount, mx FROM mxtest IGNORE INDEX (mx) GROUP BY mx ORDER BY amount DESC LIMIT 40;

Even after:
ALTER TABLE mxtest DISABLE KEYS;
.. the error still occurs. In these two cases only after a significant amount of time (for what it's worth: a SHOW PROCESSLIST during the query shows "Sorting for group" as well)

The problem occurs as well, if I add an index on another field at the same table and GROUP BY that field as well:
SELECT COUNT(*) AS amount, domain FROM mxtest GROUP BY domain ORDER BY amount DESC LIMIT 40;
ERROR 1032 (HY000): Can't find record in ''
(assuming there is a key on domain)

Suggested fix:
ALTER TABLE mxtest DROP index mx;
seems to avoid the problem, but that's a poor workaround.

I have tried to repair the table and dropping and creating the index - even tried to CREATE TABLE copymxtest SELECT * FROM mxtest (on which table the query worked as long as no index was present - I added the index again, and the query failed).

I have uploaded mxtest.tgz (containing mxtest.MYI, mxtest.MYD and mxtest.frm) to ftp.mysql.com, pub/mysql/upload/

(I errorously uploaded domain_mx.tgz as well which I aborted - you can ignore and delete that file)

I could upload a mysqldump instead if necessary.

"This error only occurs when a key does not exist for the specified field."
should read:
"This error only occurs when a key *does* exist for the specified field."

Verified on Slackware 10.0.

Few notes:

I can recreate this on RH9 4.1.11-standard-log but it does not happen on RH9 4.1.7-standard-log

Another work around is to use IGNORE INDEX (keylist) for the problem index.

The exact same error also occurs after converting the table from MyISAM to InnoDB.

This looks like the same issue as of bug #9703 - I guess this bug could be closed?

This bug seems to be duplicate of BUG#9703 which is already fixed.
I just tested with the latest 4.1.13 build and the queries from the bug
report work fine.

Is this issue still floating around? We have a similar issue on NDB (cluster) tables once they get to a certain size. We are running version 4.1.14.