MySQL Bugs: #18901: Re-occurance of Bug #12166

Bug #18901	Re-occurance of Bug #12166 - Unable to Index large table
Submitted:	8 Apr 2006 8:01	Modified:	17 Apr 2006 17:36
Reporter:	David Stevenson	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.18-nt and 5.0.18.max-nt	OS:	Windows (Windows XP Pro)
Assigned to:		CPU Architecture:	Any

Description:
When trying to CREATE INDEX on a large MyISAM file (24.1GB) at the end of the indexing operation, the table becomes dramatically shortened, and the message "Number of Rows has changed" is returned. For example, the attached example file gets truncated from 1.5bn rows to about 3.5m after the indexing operation.

The temp directory is about 192GB, and hence lack of temp space does not seem to be an issue.

How to repeat:
Save the attached 24.1GB file to the data directory and run the following command:

CREATE INDEX IXtSameWords_TargetDBID_RElatedDBID_SamewordID_ScaledDiff ON tSamewords(TargetDBID,RelatedDBID,SamewordID,ScaledDiff);

After a couple of hours, MySQL administrator will show that the table has been truncated.

Thank you for a problem report. Can you, please, prove that you have enough space in temporary directory? Send (upload)

set
dir c:
dir d:

commands results. Have you tried to repeat with, say, 2 times smaller table? And, finally, please, check it with a newer version, 5.0.19.

I'm on holiday and hence can't access my systems for another few days. I will respond as soon as I can to this request for info on my return

David Stevenson

OK, just reopen this bug report when you'll have that information. As 5.0.20 is officially released already, you may also try to install and repeat with this newer version.

I have now added the DIR files as requested to show the disk space.

The CREATE INDEX appears to work as long as the un-indexed files size is less than about 7GB and fails if it is larger.

The issue seems to be resolved in 5.0.20 - is there a delay before bug fixes make their way into the Community binary, given the bug fix was supposed to be pushed to 5.0.16 in Oct 2005?

Many thanks for your help.

David

Thank you for the additional information. I put this bug report into can't repeat state, as, if I understood you right, you can not repeat it with 5.0.20. Please, reopen it in case of similar problemx with this or next version.

Bugfix marked as pushed to 5.0.16 should be included in all binaries of 5.0.16, by the way...