Bug #6755 ALTER TABLE ENABLE KEYS + INSERT == broken table
Submitted: 22 Nov 2004 21:01 Modified: 7 Dec 2004 8:09
Reporter: Jan Kneschke Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S3 (Non-critical)
Version:4.1.7 OS:Linux (Linux)
Assigned to: Alexey Botchkov CPU Architecture:Any

[22 Nov 2004 21:01] Jan Kneschke
Description:
After importing a chunk of GIS data into a MyISAM table covered by DISABLE and 
ENABLE KEYS I can't insert more data into the table. 
 
CREATE TABLE geo ( 
  geoobjid INT NOT NULL AUTO_INCREMENT, 
  line LINESTRING NOT NULL, 
  kind ENUM('po', 'pp', 'rr', 'dr', 'rd', 'ts', 'cl') NOT NULL DEFAULT 'po', 
  name VARCHAR(32), 
  PRIMARY KEY (geoobjid), 
  SPATIAL KEY (line), 
  INDEX (kind), 
  INDEX (name) 
) engine=myisam; 
 
ALTER TABLE geo DISABLE KEYS; 
INSERT INTO geo (name, kind, line) VALUES 
  ("A", "pp", GeomFromText("POINT(62.983333 9.750000)")), 
  ... ; 
ALTER TABLE geo ENABLE KEYS; 
/* updating indexes */ 
 
/* while updating indexes */ 
show processlist; 
... | Repair by sorting | ALTER TABLE geo ENABLE KEYS | 
 
/* alter table finished */ 
check table geo; 
| gis.geo | check | status   | OK       | 
 
 
INSERT INTO geo (name, kind, line) VALUES ("austria", "dn", 
GeomFromText('LINESTRING(14.9906 48.9887,14.9946 48.9904,14.9947 48.9916)')); 
ERROR 1034 (HY000) at line 603: Incorrect key file for table 'geo'; try to repair it 
 
check table geo; 
... | error    | Found key at page 23687168 that points to record outside datafile | 
 

How to repeat:
Import  
 
http://jan.kneschke.de/projects/mysql/myisam-gis-crash.sql.gz 
 
 

Suggested fix:
let it survive the ALTER TABLE ENABLE KEYS;
[23 Nov 2004 18:29] Victoria Reznichenko
Verified with 4.1.8-debug-log
[6 Dec 2004 7:01] Alexey Botchkov
bk commit - 4.1 tree (hf:1.2201) BUG#6755
[7 Dec 2004 8:09] Alexey Botchkov
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html