MySQL Bugs: #60120: MY sql partition rebuild causes mysqld service crash

Bug #60120	MY sql partition rebuild causes mysqld service crash
Submitted:	14 Feb 2011 9:56	Modified:	27 Mar 2011 14:44
Reporter:	lucian ticrea	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S1 (Critical)
Version:	5.1.48-community-log	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	my sql service crash partition rebuild

Description:
After having a corrupt partition under innodb engine, I tried to rebuild it, by using the command

ALTER TABLE `webfactory`.`readings_tbl` REBUILD PARTITION p30

"110214 11:46:16  InnoDB: Assertion failure in thread 2372 in file .\btr\btr0pcur.c line 402
InnoDB: Failing assertion: btr_page_get_prev(next_page, mtr) == buf_frame_get_page_no(page)
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html
InnoDB: about forcing recovery.
110214 11:46:16 - mysqld got exception 0xc0000005 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=270532608
read_buffer_size=65536
max_used_connections=2
max_threads=900
threads_connected=2
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 557936 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x3c5d0b70
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
00604FF1    mysqld.exe!btr_pcur_move_to_next_page()[btr0pcur.c:402]
00605309    mysqld.exe!btr_pcur_move_to_next()[btr0pcur.ic:372]
005C0F96    mysqld.exe!row_search_for_mysql()[row0sel.c:4342]
005AD810    mysqld.exe!ha_innobase::general_fetch()[ha_innodb.cc:4891]
005ADA6C    mysqld.exe!ha_innobase::rnd_next()[ha_innodb.cc:5079]
0056EB93    mysqld.exe!ha_partition::copy_partitions()[ha_partition.cc:1672]
005713D6    mysqld.exe!ha_partition::change_partitions()[ha_partition.cc:1616]
004812C0    mysqld.exe!handler::ha_change_partitions()[handler.cc:3440]
00564234    mysqld.exe!mysql_change_partitions()[sql_partition.cc:5196]
005690CD    mysqld.exe!fast_alter_partition_table()[sql_partition.cc:6423]
004BC9FA    mysqld.exe!mysql_alter_table()[sql_table.cc:7154]
00459497    mysqld.exe!mysql_execute_command()[sql_parse.cc:2925]
0045CAD8    mysqld.exe!mysql_parse()[sql_parse.cc:5998]
0045D504    mysqld.exe!dispatch_command()[sql_parse.cc:1243]
0045DF97    mysqld.exe!do_command()[sql_parse.cc:886]
0047E880    mysqld.exe!handle_one_connection()[sql_connect.cc:1134]
0067F7CB    mysqld.exe!pthread_start()[my_winthread.c:85]
0065ECA3    mysqld.exe!_callthreadstart()[thread.c:293]
2E302E30    
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 3C6673C8=ALTER TABLE `webfactory`.`readings_tbl` REBUILD PARTITION p30
thd->thread_id=1
thd->killed=NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
InnoDB: Thread 320 stopped in file .\os\os0sync.c line 271
InnoDB: Thread 1924 stopped in file .\os\os0sync.c line 271
InnoDB: Thread 3316 stopped in file .\sync\sync0arr.c line 337
"

How to repeat:
Have a corrupt partition, like p30
ALTER TABLE `webfactory`.`readings_tbl` REBUILD PARTITION p30

Please, send the output of SHOW CREATE TABLE ...\G for the problematic partition table. Do you use innodb_file_per_table=1?

Here you go

CREATE TABLE `readings_tbl` (
  `foreignid` VARCHAR(30) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `reading_date` DATETIME NOT NULL,
  `reading_time` INT(4) NOT NULL,
  `measure` DECIMAL(15,2) DEFAULT NULL,
  `measure_status` VARCHAR(4) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `measure2` DECIMAL(15,2) DEFAULT NULL,
  `measure2_status` VARCHAR(4) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `id` INT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`foreignid`,`reading_date`,`reading_time`),
  KEY `Index` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=171924011 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY KEY (foreignid)
PARTITIONS 100 */

Can you show entire error log (including where corruptions first happened)?

You might have to use innodb_force_recovery=4 and mysqldump/reload the entire table.

http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html

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