MySQL Bugs: #613: MySQL ignores DISABLE KEYS on Windows

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

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.

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.

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;

How do you know that this command is ignored ???

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

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

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".