MySQL Bugs: #3968: Data Import - ALTER TABLE xxxx ENABLE KEYS

Bug #3968	Data Import - ALTER TABLE xxxx ENABLE KEYS
Submitted:	2 Jun 2004 16:07	Modified:	27 Aug 2004 14:03
Reporter:	Dave Wales	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.0.xx	OS:	Linux (MandrakeLinux)
Assigned to:	Matthew Lord	CPU Architecture:	Any

Description:
Hi,

I am in the middle of upgrading our system to mysql-4.0.20 and am having problems
importing large database tables from a mysqldump, the problem seems to be with the
building of indexes once the tables data has been imported. Mysql client hangs and
just sits there while the server thinks the import process is sleeping and eventually times out (a few hours later), I have tried this
with version 4.0.19 and it does the same in that version. I have also split the file up into
600 line parts and it always fails on the /*!40000 ALTER TABLE postcode_details ENABLE KEYS */;
line, it does do part of the indexing but halts half way through, I have only seen this
happen when the table is very large.. see details of the database and the structure below,
I don't know if you can replicate this senario from the information below but I believe it is
quite a large flaw in the current release. I can't send you the data as its to large but I can
tell you that I have managed to copy files over from the postcodes data directory to the
new system, mysqlcheck 'd the data and then re-dumped using 4.0.20 and tried to re-import
that dump and the same happens again so it does point to a bug somewhere in the ENABLE KEYS code on large tables.

How to repeat:
use postcodes;

mysql> desc postcode_details;
+---------------------------+------------------+------+-----+---------+----------------+
| Field                     | Type             | Null | Key | Default | Extra          |
+---------------------------+------------------+------+-----+---------+----------------+
| postcode_details_id       | int(10) unsigned |      | PRI | NULL    | auto_increment |
| postcode_id               | int(10) unsigned |      | MUL | 0       |                |
| department_of_company     | varchar(255)     | YES  |     | NULL    |                |
| company_name              | varchar(255)     | YES  |     | NULL    |                |
| sub_building              | varchar(255)     | YES  |     | NULL    |                |
| building_name             | varchar(255)     | YES  |     | NULL    |                |
| po_box_num                | varchar(255)     | YES  |     | NULL    |                |
| building_number           | int(10)          | YES  |     | NULL    |                |
| dependent_street          | varchar(255)     | YES  |     | NULL    |                |
| main_street               | varchar(255)     | YES  |     | NULL    |                |
| double_dependent_locality | varchar(255)     | YES  |     | NULL    |                |
| dependant_locality        | varchar(255)     | YES  |     | NULL    |                |
| post_town                 | varchar(255)     | YES  |     | NULL    |                |
| administrative_county     | varchar(255)     | YES  |     | NULL    |                |
| former_postal_county      | varchar(255)     | YES  |     | NULL    |                |
| traditional_county        | varchar(255)     | YES  |     | NULL    |                |
| sort_code                 | varchar(255)     | YES  |     | NULL    |                |
| user_category             | varchar(255)     |      | MUL |         |                |
| number_of_delivery_points | int(5)           | YES  |     | NULL    |                |
| delivery_point_suffix     | varchar(255)     | YES  |     | NULL    |                |
+---------------------------+------------------+------+-----+---------+----------------+

mysql> select count(*) from postcode_details;
+----------+
| count(*) |
+----------+
| 19270508 |
+----------+
1 row in set (0.00 sec)

mysql> desc postcodes;
+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| postcode_id | int(10) unsigned |      | PRI | NULL    | auto_increment |
| postcode    | varchar(8)       |      | UNI |         |                |
| city        | varchar(30)      |      |     |         |                |
| street      | varchar(100)     |      | MUL |         |                |
| county      | varchar(50)      |      | MUL |         |                |
| source      | varchar(20)      |      | MUL |         |                |
| timestamp   | timestamp(14)    | YES  |     | NULL    |                |
| db_src      | varchar(15)      | YES  |     | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+
8 rows in set (0.04 sec)

mysql> select count(*) from postcodes;
+----------+
| count(*) |
+----------+
|  1740076 |
+----------+

Suggested fix:
Check the code.

4.0.16 works fine with the same data dump if that helps.

Hi,

Can you provide me with the mysql>show create table postcode_details;?

I can try and come up with some dummy data to try and repeat the problem.

Here is the table def Matthew....

CREATE DATABASE postcodes;

USE postcodes;

-- MySQL dump 8.22
--
-- Host: localhost    Database: postcodes
---------------------------------------------------------
-- Server version       3.23.51

--
-- Table structure for table 'postcode_details'
--

CREATE TABLE postcode_details (
  postcode_details_id int(10) unsigned NOT NULL auto_increment,
  postcode_id int(10) unsigned NOT NULL default '0',
  department_of_company varchar(255) default NULL,
  company_name varchar(255) default NULL,
  sub_building varchar(255) default NULL,
  building_name varchar(255) default NULL,
  po_box_num varchar(255) default NULL,
  building_number int(10) default NULL,
  dependent_street varchar(255) default NULL,
  main_street varchar(255) default NULL,
  double_dependent_locality varchar(255) default NULL,
  dependant_locality varchar(255) default NULL,
  post_town varchar(255) default NULL,
  administrative_county varchar(255) default NULL,
  former_postal_county varchar(255) default NULL,
  traditional_county varchar(255) default NULL,
  sort_code varchar(255) default NULL,
  user_category varchar(255) NOT NULL default '',
  number_of_delivery_points int(5) default NULL,
  delivery_point_suffix varchar(255) default NULL,
  PRIMARY KEY  (postcode_details_id),
  KEY postcode_id (postcode_id),
  KEY user_category (user_category)
) TYPE=MyISAM;

CREATE TABLE postcodes (
  postcode_id int(10) unsigned NOT NULL auto_increment,
  postcode varchar(8) NOT NULL default '',
  city varchar(30) NOT NULL default '',
  street varchar(100) NOT NULL default '',
  county varchar(50) NOT NULL default '',
  source varchar(20) NOT NULL default '',
  timestamp timestamp(14) NOT NULL,
  db_src varchar(15) default NULL,
  PRIMARY KEY  (postcode_id),
  UNIQUE KEY postcode (postcode),
  KEY county (county),
  KEY street (street,city),
  KEY source (source)
) TYPE=MyISAM;

I haven't been able to repeat it with approx. 4 million rows of dummy data.  Do you have any non-
sensitive test data that I could use to try and reproduce the problem?

I have re-built one server as a FreeBSD box and installed mysql version 4.0.20, this OS didn't have a problem importing the same file to Mysql so it looks like something could be causing a problem on the Mandrake/Mysql combination...

Mandrake version which fails:- Mandrake Linux release 9.2

Not causing me a problem anymore as I would rather run on FreeBSD than Mandrake anyway but thanks for your help.