Bug #4812 Index creation on UTF8 columns corrupts table.

• Submitted: 29 Jul 2004 19:37
• Modified: 30 Jul 2004 0:27
• Reporter: Shaji Sebastian
• Status: Can't repeat
• Category: MySQL Server: MyISAM storage engine
• Severity: S2 (Serious)
• Version: 4.1.3-beta, 4.1.2-alpha
• OS: Linux (Linux 2.4.9)
• Assigned to: Matthew Lord
• CPU Architecture: Any

[29 Jul 2004 19:37] Shaji Sebastian

Description:
Index creation on UTF8 columns fails intermittently with error message:
Message: Incorrect key file for table 'mytable'; try to repair it.

The operating system is Redhat Linux.  The output of "uname -a" is:
Linux myserver 2.4.9-e.27smp #1 SMP Tue Aug 5 15:49:54 EDT 2003 i686 unknown

I am using a pre-compiled version of Mysql:
Linux (x86, libc6, Intel C++ Compiler)

I have hit this problem fairly often with mysql 4.1.2-alpha and 4.1.3-beta whenever I try to create an index containing a varchar column.  All our tables use UTF8, so I can't tell you whether non-UTF8 columns exhibit the same problem.

One characteristic of our data is that most of our tables contain tens of millions of records and we have large amounts of data in European and Asian languages.  

This bug may be related to Bug#1166 and Bug#3822.

How to repeat:
The table definition is as follows
create table mytable (
  m tinyint,
  id bigint,
  ctext varchar(255),
  primary key (m, id),
  index (m, ctext(64)))
  max_rows=100000000;

This table is in a database created with the following line:
create database mydb character set utf8;

I typically create the table, disable the keys with
alter table mytable disable keys;
insert a few tens of millions of rows into it, and then enable the indexes with
alter table mytable enable keys;
It chugs away and after a while I get the error message:
ERROR 1034 (HY000): 126 for record at pos 1109643448

check table mytable;
+---------------+-------+----------+---------------------------------------------------+
| Table         | Op    | Msg_type | Msg_text                                          |
+---------------+-------+----------+---------------------------------------------------+
| mydb.mytable | check | warning  | Table is marked as crashed and last repair failed |
| mydb.mytable | check | error    | Key in wrong position at page 740263936           |
| mydb.mytable | check | error    | Corrupt                                           |
+---------------+-------+----------+---------------------------------------------------+

Unfortunately, because of corporate policy, I cannot send you test data.

[29 Jul 2004 19:40] Shaji Sebastian

I should add that we only use MyIsam tables.

[30 Jul 2004 0:27] Matthew Lord

I was unable to repeat this on 4.1.3-beta-pc-linux or 4.1.3-beta-pc-linux-icc.

Thi is how I tried to repeat it:

 CREATE TABLE `mytable` (
  `m` tinyint(4) NOT NULL default '0',
  `id` bigint(20) NOT NULL default '0',
  `ctext` varchar(255) default NULL,
  PRIMARY KEY  (`m`,`id`),
  KEY `m` (`m`,`ctext`(64))
) ENGINE=MyISAM DEFAULT CHARSET=utf8 MAX_ROWS=100000000

alter table mytable disable keys;

 load data infile '/tmp/bug4812.sql' into table mytable fields terminated by "," optionally enclosed 
by "'" (m, id, ctext);

alter table mytable enable keys;

I used a simple perl program to create the sql file:

#!/usr/bin/perl

for( $x=1; $x < 10000000; $x++){
  print "$x, " . ($x + 1) . ", 'blah-$x'\n";
}

exit;

Have you been able to repeat this problem on other machines?  Can you run some
diagnostic tests on your disk(s)?

Best Regards,