Bug #3685 LOAD DATA INFILE, AND SELECT, UPDATE
Submitted: 7 May 2004 0:00 Modified: 10 May 2004 15:39
Reporter: M M Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S1 (Critical)
Version:MySQL Server 4.18 OS:Linux (Mandrake Linux 9.0 (Linux 2.4))
Assigned to: CPU Architecture:Any

[7 May 2004 0:00] M M
Description:
Crash, or table unusable, after loading data using LOAD DATA INFILE, and reaching more than 2Gb.

Crash is generated on a simple UPDATE.

How to repeat:
LOAD DATA INFILE of 32 files (A,B,C,D, etc. files with phone numbers)
then
UPDATE on file.

(Sometimes crash appears even between LOAD DATA INFILE)
[7 May 2004 0:07] M M
After doing:
myisamchk -q ts_phonebook_big_new

Checking MyISAM file: ts_phonebook_big_new
Data records: 33367083   Deleted blocks:       0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check data record references index: 2
myisamchk: error: Key 2 doesn't point at same records that key 1
- check record links
myisamchk: error: Found wrong record at 2554363824
MyISAM-table 'ts_phonebook_big_new' is corrupted
Fix it using switch "-r" or "-o"

This coruption situation appears EVERY time I'm trying to do this simple steps like:
ALTER TABLE DISABLE KEYS
LOAD DATA INFILE
ALTER TABLE DISABLE KEYS
LOAD DATA INFILE
ALTER TABLE DISABLE KEYS
LOAD DATA INFILE
etc.
then:
/etc/init.d/mysql stop
and recreate all keys using:
myisamchk -rq table

Everything is not generating any error, but table is corupted when trying to work with it, using UPDATE etc.
I'm trying to get this huge table to work since 2 months by now, but never succeed to get over this errors.
[10 May 2004 15:39] Alexander Keremidarski
Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Additional info:

Please provide repeatable test case which should contain at least SHOW CREATE TABLE and some instructions on how to repeat this crash.

It must be also repeatable with MySQL binary downloaded from www.mysql.com and not with custom built one only.

Problems with 2GB file size are most often caused by filesystem limits.