| Bug #4692 | DISABLE/ENABLE KEYS waste a space | ||
|---|---|---|---|
| Submitted: | 22 Jul 2004 9:19 | Modified: | 15 Nov 2007 7:15 |
| Reporter: | ladimir Kolpakov | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: MyISAM storage engine | Severity: | S3 (Non-critical) |
| Version: | 4.0 | OS: | Linux (linux) |
| Assigned to: | Ingo Strüwing | CPU Architecture: | Any |
[26 Jul 2004 20:30]
ladimir Kolpakov
OPTIMIZE TABLE after ENABLE KEYS fixes index size. So at least one workaround is known, however ENABLE KEYS behaves strange itself, despite it's extensively used in mysqld test base. --w
[9 Sep 2004 18:08]
Ingo Strüwing
The problem is that ENABLE KEYS creates a new index structure for every key that had been disabled before. DISABLE KEYS does not remove the index structures of the affected keys. There is currently no function for removing an index. But since it is already planned for optimized table administration, this bug will then be fixed then too.
[23 Dec 2005 12:12]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/388
[11 Aug 2006 14:41]
Bernhard Doebler
Is there an official production release where the fix was incorporated? It seems the problem exists in 5.0.22 on Windows. Best, Bernhard
[31 Aug 2006 13:34]
Ingo Strüwing
No. The fix was experimental only ("not to be pushed"). It has been rejected. But though it is now clear how to fix it, its priority is too low to get it in soon. :(
[2 Aug 2007 12:33]
ssenthil ssenthil
Hi, Please give this a higher priority since we also a do lot of "Disable keys,load data and then enable keys" and this results in a large index size and optimize table seems to stall all other queries as reported in http://forums.mysql.com/read.php?21,132924,132924#msg-132924 Our only option now is to try myisamchk. If your fix can make "alter table enable keys" faster and remove the need for optimize table ( i.e doesn't let index size to grow) ,then it is worth giving this a higher priority, since a lot of people use this procedure for inserting.
[2 Aug 2007 15:05]
Calvin Sun
Upgrade to P2 after discussion with Ingo.
[19 Oct 2007 16:17]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/35937 ChangeSet@1.2537, 2007-10-19 18:17:02+02:00, istruewing@stella.local +3 -0 Bug#4692 - DISABLE/ENABLE KEYS waste a space Disabling and enabling indexes on a non-empty table grows the index file. Disabling indexes just sets a flag per non-unique index and does not free the index blocks of the affected indexes. Re-enabling indexes creates new indexes with new blocks. The old blocks remain unused in the index file. Fixed by dropping and re-creating all indexes if non-empty disabled indexes exist when enabling indexes. Dropping all indexes resets the internal end-of-file marker to the end of the index file header. It also clears the root block pointers of every index and clears the deleted blocks chains. This way all blocks are declared as free.
[26 Oct 2007 13:29]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/36432 ChangeSet@1.2537, 2007-10-26 15:29:06+02:00, istruewing@stella.local +3 -0 Bug#4692 - DISABLE/ENABLE KEYS waste a space Disabling and enabling indexes on a non-empty table grows the index file. Disabling indexes just sets a flag per non-unique index and does not free the index blocks of the affected indexes. Re-enabling indexes creates new indexes with new blocks. The old blocks remain unused in the index file. Fixed by dropping and re-creating all indexes if non-empty disabled indexes exist when enabling indexes. Dropping all indexes resets the internal end-of-file marker to the end of the index file header. It also clears the root block pointers of every index and clears the deleted blocks chains. This way all blocks are declared as free.
[30 Oct 2007 15:07]
Ingo Strüwing
Queued to 6.0-engines, 5.1-engines. Not fixed in versions below 5.1.
[5 Nov 2007 9:58]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/37074 ChangeSet@1.2604, 2007-11-05 10:57:52+01:00, istruewing@stella.local +1 -0 Bug#4692 - DISABLE/ENABLE KEYS waste a space Fixed absurd compiler warnings of a Suse 10.1 system.
[5 Nov 2007 18:43]
Ingo Strüwing
We decided to push this patch into 5.0 too.
[6 Nov 2007 12:41]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/37178 ChangeSet@1.2558, 2007-11-06 13:41:32+01:00, istruewing@stella.local +3 -0 Bug#4692 - DISABLE/ENABLE KEYS waste a space Disabling and enabling indexes on a non-empty table grows the index file. Disabling indexes just sets a flag per non-unique index and does not free the index blocks of the affected indexes. Re-enabling indexes creates new indexes with new blocks. The old blocks remain unused in the index file. Fixed by dropping and re-creating all indexes if non-empty disabled indexes exist when enabling indexes. Dropping all indexes resets the internal end-of-file marker to the end of the index file header. It also clears the root block pointers of every index and clears the deleted blocks chains. This way all blocks are declared as free.
[6 Nov 2007 12:45]
Ingo Strüwing
This is a backport from 5.1. It is in fact the same patch now for 5.0. Approved for push to 5.0 by email.
[6 Nov 2007 13:47]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/37181 ChangeSet@1.2607, 2007-11-06 14:47:15+01:00, istruewing@stella.local +2 -0 Bug#4692 - DISABLE/ENABLE KEYS waste a space Post-merge fix. Moved test into 5.0 section.
[7 Nov 2007 8:09]
Ingo Strüwing
Queued to 6.0-engines, 5.1-engines, 5.0-engines.
[14 Nov 2007 9:41]
Bugs System
Pushed into 6.0.4-alpha
[14 Nov 2007 9:45]
Bugs System
Pushed into 5.1.23-rc
[14 Nov 2007 9:50]
Bugs System
Pushed into 5.0.52
[14 Nov 2007 11:02]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/37737 ChangeSet@1.2556, 2007-11-14 12:02:20+01:00, istruewing@stella.local +3 -0 Bug#4692 - DISABLE/ENABLE KEYS waste a space Post-pushbuild fix Added a purecov comment and a test for coverage of parallel enable keys.
[15 Nov 2007 7:15]
MC Brown
A note has been added to the 5.0.52, 5.1.23, 6.0.4 changelogs: Executing DISABLE KEYS and ENABLE KEYS on a non-empty table would cause the size of the index file for the table to grow considerable. This was because the DISABLE KEYS operation would only mark the existing index, without deleting the index blocks. The ENABLE KEYS operation would re-create the index, adding new blocks, while the previous index blocks would remain. Existing indexes are now dropped and recreated when the ENABLE KEYS statement is executed.
[28 Nov 2007 10:25]
Bugs System
Pushed into 6.0.4-alpha
[28 Nov 2007 10:26]
Bugs System
Pushed into 5.1.23-rc
[28 Nov 2007 10:27]
Bugs System
Pushed into 5.0.54
[4 Jan 2008 23:49]
Bernhard Doebler
I always kept this bug in my browser's favorites to keep track of it's progress. Thanks for finally fixing and a happy new year Bernhard
Description: DISABLE KEYS/ENABLE KEYS increase .MYI file size, where "myisamchk -r -q" does not. As long as myisamchk can't be used remotely lack of working SQL equivalent could be considered as a bug. How to repeat: #!/bin/bash # $Id$ # "ENABLE/DISABLE KEYS" bug test # #--w 07/2004############################################################ mysql="mysql -hlocalhost -utest -ptest -tvvv test" ######################################################################## function CreateTable() { cat <<EoD DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,acc VARCHAR(16) ,INDEX acc (acc) ) TYPE = MyISAM; INSERT INTO t1 (acc) VALUES ('1'),('2'),('3'); EoD } function reEnableKeys() { cat <<EoD ALTER TABLE t1 DISABLE KEYS; ALTER TABLE t1 ENABLE KEYS; EoD } function Update() { arg=$1 cat <<EoD ALTER TABLE t1 DISABLE KEYS; UPDATE t1 SET acc='$arg' WHERE id=1; ALTER TABLE t1 ENABLE KEYS; EoD } function Insert() { arg=$1 cat <<EoD ALTER TABLE t1 DISABLE KEYS; DELETE FROM t1 WHERE id='$arg'; INSERT INTO t1 (acc) VALUES ('$arg'); ALTER TABLE t1 ENABLE KEYS; EoD } ######################################################################## cd /var/lib/mysql/test || exit 1 echo ==== reEnable keys test ==== CreateTable | $mysql /bin/ls -alF t1.* for i in 1 2 do reEnableKeys | $mysql /bin/ls -alF t1.* done echo myisamchk -r -q t1 myisamchk -r -q t1 /bin/ls -alF t1.* echo ==== Update test ==== CreateTable | $mysql /bin/ls -alF t1.* for i in 3 4 do Update $i | $mysql /bin/ls -alF t1.* done echo myisamchk -r -q t1 myisamchk -r -q t1 /bin/ls -alF t1.* echo ==== Insert test ==== CreateTable | $mysql /bin/ls -alF t1.* for i in 1 2 do Insert $i | $mysql /bin/ls -alF t1.* done echo myisamchk -r -q t1 myisamchk -r -q t1 /bin/ls -alF t1.* ######################################################################## # $Log$ Suggested fix: unknown