Bug #613 MySQL ignores DISABLE KEYS on Windows
Submitted: 6 Jun 2003 10:37 Modified: 16 Jul 2003 10:52
Reporter: Christian Stadler Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:4.0.13 OS:Windows (Windows 2000)
Assigned to: CPU Architecture:Any

[6 Jun 2003 10:37] Christian Stadler
Description:
I'm creating backups though mysqldump with the switches '-e -l -K --add-drop-table'. So it adds '/*!40000 ALTER TABLE tb_name DISABLE KEYS */;' to the dumps.

Unfortunately, this is being ignored running MySQL 4.0.13 on Win2k. It works fine, running 4.0.12 on Windows or 4.0.13 on SuSE Linux 8.2, so it's definately a problem with 4.0.13 and Windows.

How to repeat:
1. create a dump using the switches -e -l -K --add-drop-table or at least -K
2. replay the dump on a MySQL 4.0.13-Server on Windows. (maybe related to Win2k only)

Actual results:
Disable keys is being ignored and the index will be updated while the certain table is being restored.

Expected results:
The index is being created 'after' all data of the table has been restored.
[6 Jun 2003 13:21] MySQL Verification Team
Sorry I wasn't able to repeat:

C:\mysql\bin>mysqldump -e -l -K --add-drop-table test -uroot
-- MySQL dump 9.08
--
-- Host: localhost    Database: test
---------------------------------------------------------
-- Server version       4.0.13-max-debug-log

--
-- Table structure for table 'dumptest'
--

DROP TABLE IF EXISTS dumptest;
CREATE TABLE dumptest (
  id int(11) default NULL
) TYPE=MyISAM;

--
-- Dumping data for table 'dumptest'
--

*!40000 ALTER TABLE dumptest DISABLE KEYS */;
INSERT INTO dumptest VALUES (1);
*!40000 ALTER TABLE dumptest ENABLE KEYS */;

I did a test with a more large table and found the
same behavior than 4.0.12.
[16 Jun 2003 9:28] Christian Stadler
Maybe, it's being caused by tables, that contain Multiple-Column Indexes?

Try it with the following structure:

CREATE TABLE `aaaaaaa` (
  `bbbbbbb` int(10) unsigned NOT NULL default '0',
  `ccccccc` int(10) unsigned NOT NULL default '0',
  `ddddddd` smallint(5) unsigned NOT NULL default '0',
  KEY `bbbbbbb` (`bbbbbbb`,`ccccccc`)
) TYPE=MyISAM;
[16 Jun 2003 10:15] MySQL Verification Team
How do you know that this command is ignored ???
[16 Jun 2003 10:32] Christian Stadler
1. The restoration of that table is getting slower and slower. The duration is similar, as if I haven't used DISABLE KEYS (MySQL 4.0.12: ~5 mins to restore, MySQL 4.0.13: ~1 Hour to restore)
2. Usually after the data of a table using indexes has been restored, the restoration pauses some seconds to create the index, but this doesn't happen on Win2k for me
3. the MYI-Filesize of these tables increases during the whole process of restoring the table
[16 Jun 2003 10:52] MySQL Verification Team
Speed does not depend only on DISABLE KEYS.

Depends on settings too.

Besides, there is nothing in our DISABLE KEYS code that is OS dependent.

To check it, you can upload your dump (zipped) to:

ftp://support.mysql.com:/pub/mysql/secret
[14 Feb 2005 22:54] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".