MySQL Bugs: #27233: Table crashes all over the place on Partitioned tables

Bug #27233	Table crashes all over the place on Partitioned tables
Submitted:	16 Mar 2007 23:10	Modified:	23 Jun 2007 11:41
Reporter:	bo blanton	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S2 (Serious)
Version:	5.1.16	OS:	Linux (Fedora 5 x86_64)
Assigned to:		CPU Architecture:	Any
Tags:	partition table

Description:
Hi Guys,

i know this is a 'generic' bug because i have no idea what is causing it or even how.

Here is the case.

i have 5.1.16 set up as a slave to a 5.0.27 server (that is active in production).  the 5.1.16 is NOT active, and only acting as a minor test bed to see if things work on 5.1.xx that do on 5.0.xx.

Writes are very frequent (about 500 rows/sec) on large data sets (70+ million rows), hence the partition tables. Not all the tables are partitioned, just the ones with large data sets.

i notice almost every day the tables have crashed.  I mean ALL the partitioned tables have been marked as crashed. no other tables have. causing a repair.. and on a 70mil+ that takes a while, the slave gets way out of sync, and as it tries to catch up, about 10-12 hours later, they are crashed again.

I am partitioning on Primary keys in chunks of 15 partitions using the default partition function. using your compiled mysql 5.1.16..

the crash seemed to always say the #P#p0 partition (no others get reported)

slave.

How to repeat:
not sure how to 'repeat' it, other then setting up a high volume master -- slave where the slave is 5.1 and the master is 5.0 and partitioning a few tables to be Primary Key 15 partitions

Thank you for the report.

Please provide output of SHOW CREATE TABLE  and SHOW TABLE STATUS for one of partitioned tables which crushes often.

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

Sorry for some reason i never got the request for more info .. here is what you wanted..

I _had_ DELAYED_KEY_WRITE=1 on it for a while, i turned it off on the partitioned, and the crash situation got better (less table crashes) but it still does crash fairly regularly. 

SHOW CREATE TABLE

CREATE TABLE `table_name` (
  `aux_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `item_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `rank` smallint(4) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`aux_id`,`item_id`),
  KEY `rank` (`rank`),
  KEY `item_id` (`item_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=1 /*!50100 PARTITION BY KEY () PARTITIONS 15  */

SHOW TABLE STATUS where name like 'table_name'\G
*************************** 1. row ***************************
           Name: table_name
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 71222676
 Avg_row_length: 9
    Data_length: 641004084
Max_data_length: 0
   Index_length: 1262292992
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2007-02-26 15:47:01
    Update_time: 2007-04-19 17:24:07
     Check_time: 2007-04-19 17:24:22
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: pack_keys=1 partitioned
        Comment:

Please, check with anewer version, 5.1.18, and inform about the results.

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