Bug #32854 crash when using alter table partition then selecting
Submitted: 29 Nov 2007 17:59 Modified: 3 Jul 2008 11:43
Reporter: Jerry Bonner Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Partitions Severity:S1 (Critical)
Version:5.1.22-rc OS:Linux (centos 5)
Assigned to: Mattias Jonsson CPU Architecture:Any

[29 Nov 2007 17:59] Jerry Bonner 
Description:
Crash when altering tables, then selecting :

mysql> create table mm_accounting_archive2 select * from mm_accounting_archive;
Query OK, 467406 rows affected (3.94 sec)
Records: 467406  Duplicates: 0  Warnings: 0

mysql> select from_unixtime(min(time_stamp)), min(time_stamp), from_unixtime(max(time_stamp)), max(time_stamp) from mm_accounting_archive2;
+--------------------------------+-----------------+--------------------------------+-----------------+
| from_unixtime(min(time_stamp)) | min(time_stamp) | from_unixtime(max(time_stamp)) | max(time_stamp) |
+--------------------------------+-----------------+--------------------------------+-----------------+
| 2007-10-30 13:21:12            |      1193768472 | 2007-11-28 15:59:11            |      1196287151 | 
+--------------------------------+-----------------+--------------------------------+-----------------+
1 row in set (0.62 sec)

mysql> alter table mm_accounting_archive2 partition by range(time_stamp) ( partition p1007 values less than (1193893200) engine=archive, partition p1107 values less than (MAXVALUE) engine=archive);
Query OK, 467406 rows affected (9.82 sec)
Records: 467406  Duplicates: 0  Warnings: 0

mysql> check table mm_accounting_archive2;
+-------------------------------+-------+----------+----------+
| Table                         | Op    | Msg_type | Msg_text |
+-------------------------------+-------+----------+----------+
| radius.mm_accounting_archive2 | check | status   | OK       | 
+-------------------------------+-------+----------+----------+
1 row in set (1.71 sec)

mysql> alter table mm_accounting_archive2 remove partitioning;
Query OK, 467406 rows affected (11.20 sec)
Records: 467406  Duplicates: 0  Warnings: 0

mysql> show create table mm_accounting_archive2;
+------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                  | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mm_accounting_archive2 | CREATE TABLE `mm_accounting_archive2` (
  `username` varchar(50) NOT NULL DEFAULT '',
  `framed_address` varchar(24) DEFAULT NULL,
  `nas_port` int(10) DEFAULT NULL,
  `client_ip` varchar(21) DEFAULT NULL,
  `terminate_cause` varchar(30) DEFAULT NULL,
  `session_time` int(10) DEFAULT NULL,
  `delay_time` int(10) DEFAULT NULL,
  `record_type` varchar(30) DEFAULT NULL,
  `time_stamp` int(10) unsigned DEFAULT NULL,
  `modemno` int(10) unsigned DEFAULT NULL,
  `dnis` varchar(21) DEFAULT NULL,
  `caller_id` varchar(21) DEFAULT NULL,
  `session_id` varchar(30) DEFAULT NULL,
  `slotno` int(10) unsigned DEFAULT NULL,
  `handler` varchar(64) DEFAULT NULL
) ENGINE=ARCHIVE DEFAULT CHARSET=latin1 | 
+------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table mm_accounting_archive2 partition by range(time_stamp) ( partition p9999 values less than (MAXVALUE) engine=archive); 
Query OK, 467406 rows affected (11.13 sec)
Records: 467406  Duplicates: 0  Warnings: 0

mysql> alter table  mm_accounting_archive2 add partition (partition p1007 values less than (1193893200) engine=archive);
Query OK, 0 rows affected (0.27 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select from_unixtime(min(time_stamp)), min(time_stamp), from_unixtime(max(time_stamp)), max(time_stamp) from mm_accounting_archive2;
ERROR 2013 (HY000): Lost connection to MySQL server during query

0x820858b handle_segfault + 779
0x825b326 _Z21open_table_from_shareP3THDP14st_table_sharePKcjjjP8st_tableb + 982
0x82508e7 _Z17open_unireg_entryP3THDP8st_tableP10TABLE_LISTPKcPcjP11st_mem_rootj + 183
0x8256ef0 _Z10open_tableP3THDP10TABLE_LISTP11st_mem_rootPbj + 2480
0x82576fa _Z11open_tablesP3THDPP10TABLE_LISTPjj + 714
0x825795b _Z20open_and_lock_tablesP3THDP10TABLE_LIST + 43
0x82133ae _Z21execute_sqlcom_selectP3THDP10TABLE_LIST + 94
0x8217cbf _Z21mysql_execute_commandP3THD + 3759
0x8220099 _Z11mysql_parseP3THDPKcjPS2_ + 745
0x82205a5 _Z16dispatch_command19enum_server_commandP3THDPcj + 1269
0x82219a7 _Z10do_commandP3THD + 167
0x82111ff handle_one_connection + 239
0xb682db (?)
0xac212e (?)

After mysql reloads my original table (mm_accounting_archive) becomes corrupted. Perhaps just collateral damage from mysql crash.

071128 16:13:14 [ERROR] Couldn't repair table: radius.mm_accounting_archive
071128 16:13:24 [ERROR] Couldn't repair table: radius.mm_accounting_archive
071128 16:13:35 [ERROR] Couldn't repair table: radius.mm_accounting_archive
071128 16:13:45 [ERROR] Couldn't repair table: radius.mm_accounting_archive
071128 16:13:56 [ERROR] Couldn't repair table: radius.mm_accounting_archive
071128 16:14:07 [ERROR] Couldn't repair table: radius.mm_accounting_archive
071128 16:14:18 [ERROR] Couldn't repair table: radius.mm_accounting_archive
071128 16:14:28 [ERROR] Couldn't repair table: radius.mm_accounting_archive
071128 16:14:39 [ERROR] Couldn't repair table: radius.mm_accounting_archive
071128 16:14:50 [ERROR] Couldn't repair table: radius.mm_accounting_archive
071128 16:15:01 [ERROR] Couldn't repair table: radius.mm_accounting_archive

How to repeat:
CREATE TABLE `mm_accounting_archive2` (
  `username` varchar(50) NOT NULL DEFAULT '',
  `framed_address` varchar(24) DEFAULT NULL,
  `nas_port` int(10) DEFAULT NULL,
  `client_ip` varchar(21) DEFAULT NULL,
  `terminate_cause` varchar(30) DEFAULT NULL,
  `session_time` int(10) DEFAULT NULL,
  `delay_time` int(10) DEFAULT NULL,
  `record_type` varchar(30) DEFAULT NULL,
  `time_stamp` int(10) unsigned DEFAULT NULL,
  `modemno` int(10) unsigned DEFAULT NULL,
  `dnis` varchar(21) DEFAULT NULL,
  `caller_id` varchar(21) DEFAULT NULL,
  `session_id` varchar(30) DEFAULT NULL,
  `slotno` int(10) unsigned DEFAULT NULL,
  `handler` varchar(64) DEFAULT NULL
) ENGINE=MyISAM

alter table mm_accounting_archive2 partition by range(time_stamp) ( partition p1007 values less than (1193893200) engine=archive, partition p1107 values less than (MAXVALUE) engine=archive);

alter table mm_accounting_archive2 remove partitioning;

alter table mm_accounting_archive2 partition by range(time_stamp) ( partition p9999 values less than (MAXVALUE) engine=archive); 

alter table  mm_accounting_archive2 add partition (partition p1007 values less than (1193893200) engine=archive);

select from_unixtime(min(time_stamp)), min(time_stamp), from_unixtime(max(time_stamp)), max(time_stamp) from mm_accounting_archive2;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Got this trace on different box when duplicating 

0x820858b handle_segfault + 779
0x825b326 _Z21open_table_from_shareP3THDP14st_table_sharePKcjjjP8st_tableb + 982
0x82508e7 _Z17open_unireg_entryP3THDP8st_tableP10TABLE_LISTPKcPcjP11st_mem_rootj + 183
[2 Feb 2008 12:28] Mattias Jonsson 
Seems to be a duplicate of Bug#33429.
[3 Jul 2008 11:43] Mattias Jonsson 
Setting as can't repeat. It is verified in 5.1.22, but test fails to execute on >= 5.1.23 due to several bugfixes.

The problem was that in 5.1.22 it was possible to create range partitions out of order (e.g. first partition less than (MAXVALUE), second partition (5)).

If one alters the order of the range partitions in the testcase, it does not fail or crash. (and it is not allowed to select from such table in 5.1.23 and is not allowed to create or alter to such partition order in >= 5.1.24)