Bug #9703 Error 1032 with GROUP BY query and large tables
Submitted: 7 Apr 2005 2:26 Modified: 12 May 2005 20:17
Reporter: Chris Lewis Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:4.1.11 OS:Linux (RedHat EL3)
Assigned to: Evgeny Potemkin CPU Architecture:Any

[7 Apr 2005 2:26] Chris Lewis
Description:
Upon upgrade to 4.1.11 today we began seeing this error on our homegrown forum system.  The error is:

ERROR 1032 (HY000): Can't find record in ''

I will attach a mysqldump of the table structures and data, since this is a query across several tables with several thousand rows and the problem goes away if I create a test table with few rows.  The SQL is below, and works correctly in 4.1.10:

select T.ThreadID,count(distinct M.MessageID) as Msgs, 
U1.LoginName,UP1.PrefValue,U2.LoginName,UP2.PrefValue,
max(M.WhenPosted) as LatestPost, count(distinct I.SolvedBy) as Solvers
from Thread T 
inner join Users U1 on (T.StartedBy=U1.UserID) 
inner join Message M on (T.ThreadID=M.ThreadID) 
left join Identified I on (T.ThreadID=I.ThreadID) 
left join UserPrefs UP1 on (U1.UserID=UP1.UserID and UP1.PrefName='ScreenName') 
left join Users U2 on (I.SolvedBy=U2.UserID) 
left join UserPrefs UP2 on (U2.UserID=UP2.UserID and UP2.PrefName='ScreenName') 
group by T.ThreadID 
order by T.WhenStarted desc

The problem goes away if I do *any* of the following:
 - remove ORDER BY clause
 - remove all the group-function (count() etc) columns
 - remove any PrefName or PrefValue column from select list
 - remove a significant portion of rows from Thread table

I suspect it is an index problem, maybe related to bug 9213, however I have checked that the table indexes do not match the problem at the bottom of bug 9213.  I have also tried optimizing tables to no avail.

How to repeat:
Create populated tables from attached mysqldump. Run SQL from description above.
[7 Apr 2005 2:29] Chris Lewis
I can't attache the mysqldump file since it's greater than 200KB.  Download the file from:
http://www.myfonts.com/bugtest.gz
[7 Apr 2005 5:22] Jorge del Conde
Thanks for your excellent bug report.
[29 Apr 2005 17:22] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/24487
[12 May 2005 20:17] Paul Dubois
Noted in 4.1.12 changelog.
[30 May 2005 9:47] Evgeny Potemkin
When new row inserted to memory buffer and there no room to contain it, error raised, data dumped to temporary file and server can continue, but error wasn't reset in end_write_groups() and leads query to fail.

changeset 1.2203