Bug #11527 Setting myisam_repair_threads to >1 leads to corruption
Submitted: 23 Jun 2005 12:33 Modified: 4 Mar 2006 17:20
Reporter: Geert Vanderkelen Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S3 (Non-critical)
Version:4.1.12 OS:Any (*)
Assigned to: Ingo Strüwing CPU Architecture:Any
Tags: corruption, myisam, myisam_repair_threads

[23 Jun 2005 12:33] Geert Vanderkelen
Description:
Hi,

Setting the myisam_repair_threads to another value than the default 1, can or will
lead to table corruption when adding an index for example.

It has been tested under Linux and Windows, versions 4.1.12 and 5.0.7-beta.
Dual CPU machines give different results under Windows than under Linux.
On Windows using a Dual (or more) CPU, a value of 2 gives also corruption, where as
on a Dual Xeon under Linux it works with 2, but not with value 3 or 4 or 5.

Regards,

Geert

How to repeat:
Need alot of data, note the table MyISAM grows over 4Gb as it is Fixed Rows.

CREATE TABLE `bigtable` (
  `ModelpointId` int(10) unsigned NOT NULL default '0',
  `TimestepId` datetime NOT NULL default '0000-00-00 00:00:00',
  `Var1` float default NULL,
  `Var2` float default NULL,
  `Var3` float default NULL,
  `Var4` float default NULL,
  `Var5` float default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

mysql> show variables like 'myisam_repair_threads';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| myisam_repair_threads | 2     |
+-----------------------+-------+
1 row in set (0.00 sec)

mysql> load data infile 'bigtable_csvfile.txt' INTO TABLE bigtable FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
Query OK, 130771200 rows affected (11 min 46.32 sec)
Records: 130771200  Deleted: 0  Skipped: 0  Warnings: 0

mysql> create index ix1 on bigtable (ModelPointId);
Query OK, 130771200 rows affected (5 min 38.43 sec)
Records: 130771200  Duplicates: 0  Warnings: 0

mysql> show table status like 'bigtable'\G
*************************** 1. row ***************************
           Name: bigtable
         Engine: MyISAM
        Version: 9
     Row_format: Fixed
           Rows: 620675
 Avg_row_length: 33
    Data_length: 20482275
Max_data_length: 141733920767
   Index_length: 5062656
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2005-06-21 21:48:50
    Update_time: 2005-06-21 21:54:26
     Check_time: 2005-06-21 21:54:28
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:

Check table results in corrupted table.

Has been verfied by 3 parties on different machine using different Linux distro's and
different Windows versions.
[30 Jan 2006 14:00] Ingo Strüwing
Funny that you got different results for different numbers of myisam_repair_threads.

The current code (4.1 .. 5.1) uses the variable just to check if it is > 1. It starts one thread per index then.
[31 Jan 2006 9:49] Ingo Strüwing
In this test case there is ony one index. I proved by tracing that one thread is created only. Thus I can exclude threading/locking problems.
[9 Feb 2006 13:30] 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/2364
[16 Feb 2006 18:46] 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/2745
[4 Mar 2006 15:17] Ingo Strüwing
Pushed to 4.1.19, 5.0.20, and 5.1.8.

No test case. It requires >4GB data to repeat. I tested with a 7GB customer load file.
[4 Mar 2006 17:20] Paul DuBois
Noted in 4.1.19, 5.0.19, 5.1.8 changelogs.

Setting the <literal>myisam_repair_threads</literal> system
variable to a value larger than 1 could cause corruption of
large <literal>MyISAM</literal> tables. (Bug #11527)