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

Bug #27233	Table crashes all over the place on Partitioned tables
Submitted:	17 Mar 2007 0:10	Modified:	23 Jun 2007 13:41
Reporter:	bo blanton
Status:	No Feedback
Category:	Server: Partition	Severity:	S2 (Serious)
Version:	5.1.16	OS:	Linux (Fedora 5 x86_64)
Assigned to:		Target Version:
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".