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:
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

[8 Apr 2006 8:01] David Stevenson
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.
[10 Apr 2006 12:41] Valeriy Kravchuk
Thank you for a problem report. Can you, please, prove that you have enough space in temporary directory? Send (upload)

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.
[12 Apr 2006 14:36] David Stevenson
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
[13 Apr 2006 9:00] Valeriy Kravchuk
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.
[13 Apr 2006 14:51] David Stevenson
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.
[17 Apr 2006 17:28] David Stevenson
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.

[17 Apr 2006 17:36] Valeriy Kravchuk
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...