Bug #35554 Maria: DISABLE/ENABLE KEYS grows index file indefinitely

• Submitted: 25 Mar 2008 14:01
• Modified: 22 Apr 2008 14:03
• Reporter: Guilhem Bichot
• Status: Closed
• Category: MySQL Server: Maria storage engine
• Severity: S3 (Non-critical)
• Version: 5.1-maria
• OS: Linux
• Assigned to: Michael Widenius
• CPU Architecture: Any

[25 Mar 2008 14:01] Guilhem Bichot

Description:
running ALTER TABLE DISABLE KEYS and ENABLE KEYS in a loop grows the MYI file each time

How to repeat:
CREATE TABLE t1 (
  line varchar(100) not null,
  kind ENUM('po', 'pp', 'rr', 'dr', 'rd', 'ts', 'cl') NOT NULL DEFAULT 'po',
  name VARCHAR(32),
  KEY (line)
) engine=maria;

INSERT INTO t1 (name, kind, line) VALUES 
  ("Aadaouane", "pp", GeomFromText("POINT(32.816667 35.983333)")),
  ("Aadassiye", "pp", GeomFromText("POINT(35.816667 36.216667)")),
  ("Aadbel", "pp", GeomFromText("POINT(34.533333 36.100000)")),
  ("Aadchit", "pp", GeomFromText("POINT(33.347222 35.423611)")),
  ("Aadchite", "pp", GeomFromText("POINT(33.347222 35.423611)")),
  ("Aadchit el Qoussair", "pp", GeomFromText("POINT(33.283333 35.483333)")),
  ("Aaddaye", "pp", GeomFromText("POINT(36.716667 40.833333)")),
  ("'Aadeissa", "pp", GeomFromText("POINT(32.823889 35.698889)")),
  ("Aaderup", "pp", GeomFromText("POINT(55.216667 11.766667)")),
  ("Qalaat Aades", "pp", GeomFromText("POINT(33.503333 35.377500)")),
  ("A ad'ino", "pp", GeomFromText("POINT(54.812222 38.209167)")),
  ("Aadi Noia", "pp", GeomFromText("POINT(13.800000 39.833333)")),
  ("Aad La Macta", "pp", GeomFromText("POINT(35.779444 -0.129167)")),
  ("Aadland", "pp", GeomFromText("POINT(60.366667 5.483333)")),
  ("Aadliye", "pp", GeomFromText("POINT(33.366667 36.333333)")),
  ("Aadloun", "pp", GeomFromText("POINT(33.403889 35.273889)")),
  ("Aadma", "pp", GeomFromText("POINT(58.798333 22.663889)")),
  ("Aadma Asundus", "pp", GeomFromText("POINT(58.798333 22.663889)")),
  ("Aadmoun", "pp", GeomFromText("POINT(34.150000 35.650000)")),
  ("Aadneram", "pp", GeomFromText("POINT(59.016667 6.933333)")),
  ("Aadneskaar", "pp", GeomFromText("POINT(58.083333 6.983333)")),
  ("Aadorf", "pp", GeomFromText("POINT(47.483333 8.900000)")),
  ("Aadorp", "pp", GeomFromText("POINT(52.366667 6.633333)")),
  ("Aadouane", "pp", GeomFromText("POINT(32.816667 35.983333)")),
  ("Aadoui", "pp", GeomFromText("POINT(34.450000 35.983333)")),
  ("Aadouiye", "pp", GeomFromText("POINT(34.583333 36.183333)")),
  ("Aadouss", "pp", GeomFromText("POINT(33.512500 35.601389)")),
  ("Aadra", "pp", GeomFromText("POINT(33.616667 36.500000)")),
  ("Aadzi", "pp", GeomFromText("POINT(38.100000 64.850000)"));

flush table t1;
--exec ls -l var/master-data/test/t1.M?I
ALTER TABLE t1 DISABLE KEYS;
ALTER TABLE t1 ENABLE KEYS;
--exec ls -l var/master-data/test/t1.M?I
ALTER TABLE t1 DISABLE KEYS;
ALTER TABLE t1 ENABLE KEYS;
--exec ls -l var/master-data/test/t1.M?I
ALTER TABLE t1 DISABLE KEYS;
ALTER TABLE t1 ENABLE KEYS;
--exec ls -l var/master-data/test/t1.M?I
ALTER TABLE t1 DISABLE KEYS;
ALTER TABLE t1 ENABLE KEYS;
--exec ls -l var/master-data/test/t1.M?I

results:
+flush table t1;
+-rw-rw---- 1 guilhem users 16384 Mar 25 16:56 var/master-data/test/t1.MAI
+ALTER TABLE t1 DISABLE KEYS;
+ALTER TABLE t1 ENABLE KEYS;
+-rw-rw---- 1 guilhem users 24576 Mar 25 16:56 var/master-data/test/t1.MAI
+ALTER TABLE t1 DISABLE KEYS;
+ALTER TABLE t1 ENABLE KEYS;
+-rw-rw---- 1 guilhem users 32768 Mar 25 16:56 var/master-data/test/t1.MAI
+ALTER TABLE t1 DISABLE KEYS;
+ALTER TABLE t1 ENABLE KEYS;
+-rw-rw---- 1 guilhem users 40960 Mar 25 16:56 var/master-data/test/t1.MAI
+ALTER TABLE t1 DISABLE KEYS;
+ALTER TABLE t1 ENABLE KEYS;
+-rw-rw---- 1 guilhem users 49152 Mar 25 16:56 var/master-data/test/t1.MAI

MyISAM does not have the bug.

Suggested fix:
The fix for BUG#4692 was not merged to Maria.
Please check all the changes imported into MyISAM by this merge:
Thu Dec 20 16:42:24 2007 jamppa [C=1960] (148115 lines) 
and apply them to Maria.

[27 Mar 2008 9:37] Sergei Golubchik

see also bug#4692

[22 Apr 2008 14:03] Guilhem Bichot

Fixed in ChangeSet@1.2623, 2008-04-01 17:57:30+03:00, [e-mail] +21 -0
  Merge of changes in MyISAM since December 16 -> April 1
  Fixes bugs:
  Bug#28837  MyISAM storage engine error (134) doing delete with self-join
  Bug#31277  myisamchk --unpack corrupts table
  Bug#4692   DISABLE/ENABLE KEYS waste a space
  Bug#31305  myisam tables crash when they are near capacity