MySQL Bugs: #37375: partition table

Bug #37375	partition table - index corruption
Submitted:	12 Jun 2008 16:29	Modified:	11 Aug 2008 18:43
Reporter:	jey Razack	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S2 (Serious)
Version:	Ver 5.1.24-rc redhat(x86_64)	OS:	Linux (RHEL 4)
Assigned to:		CPU Architecture:	Any
Tags:	Error: 126, partition index corruption, regression

Description:
mysqlimport: Error: 126, Incorrect key file for table './vxncore/Traffic#P#Y2008M06D12.MYI'; try to repair it, when using table:Traffic

Below is the description of the process:

The Traffic table is partitioned by day. We keep two previous days data + current day + 2 empty future partitions.

We have a cron job that (runs every day) drops partitions older than 2 days if any and add a new parition. We do this (dropping and adding partitions) while heavy mutliple instances of mysqlimports are running that load data into Traffic table. 

The process runs nice on the Mysql ver 5.1.22. But on Ver 5.1.24 once in a while the current day parition index is reported corrupt immediatly after we do the process of dropping and creating paritions. Note: we don't touch the current day partition.

How to repeat:
You have to run this with millions of records + multiple mysqlimports to see this issue. Create the table below and run several  mysqlimport processes contain TxnTime between '2008-06-11 00:00:00' and '2008-06-11 23:59:59'. Drop the Y2008M06D10 parition while leaving the import process are running.

Below is the table structure:

CREATE TABLE Traffic (
  Recno bigint unsigned NOT NULL AUTO_INCREMENT,
  TxnID varchar(16) NOT NULL DEFAULT '',
  TxnTime datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
   P1     varchar(30),
   P2     varchar(30),
   P3     varchar(30),
   P4     varchar(30),
   P5     varchar(30),
   P6     varchar(30),
   P7     varchar(30),
   P8     varchar(30),
   P9     varchar(30),
   P10     varchar(100),
   P11     varchar(100),
   P12     varchar(100),
   P13     varchar(100),
  PRIMARY KEY (`TxnTime`,`TxnID`),
  KEY `Recno` (`Recno`),
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 
 PARTITION BY RANGE ( to_days(TxnTime)) 
(PARTITION Y2008M06D10 VALUES LESS THAN (733569) ENGINE = MyISAM, 
 PARTITION Y2008M06D11 VALUES LESS THAN (733570) ENGINE = MyISAM, 
 PARTITION Y2008M06D12 VALUES LESS THAN (733571) ENGINE = MyISAM, 
 PARTITION Y2008M06D13 VALUES LESS THAN (733572) ENGINE = MyISAM, 
 PARTITION Y2008M06D14 VALUES LESS THAN (733573) ENGINE = MyISAM) 

Suggested fix:

We don't have this issue in 5.1.22, the table is properly locked by the RDBMS while it does the struture changes without corrupting the existing index files. so it is some thing to do with internal handler of RDBMS to properly lock the entire table not allowing data inserts / data changes/ data load gothrough while it modifies the structure.

or it could be that the handler placing the index data into the wrong slot so that the deletion of the old partition data may still have pointer reference in the current index partition file.

Thank you for a problem report. Please, try to repeat with a newer version, 5.1.25, and inform about the results.

Just to provide an update...

We currently masking the issue by rebuilding the current day partition before dropping and adding new partitions and it seems working...

For testing purpose we installed 5.1.24-rc on another box to simulate the issue what we see in the current production box and then we will upgrade to 5.1.25 to see the issue is gone. It may take few days to see the results.

Thank you for the update. Please, inform about the results of your test.

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