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

[16 Mar 2007 23:10] bo blanton
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
[19 Mar 2007 9:51] Sveta Smirnova
Thank you for the report.

Please provide output of SHOW CREATE TABLE  and SHOW TABLE STATUS for one of partitioned tables which crushes often.
[19 Apr 2007 23:00] Bugs System
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".
[20 Apr 2007 0:34] bo blanton
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:
[23 May 2007 11:41] Valeriy Kravchuk
Please, check with anewer version, 5.1.18, and inform about the results.
[23 Jun 2007 23:00] Bugs System
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".