MySQL Bugs: #24502: reorganize partition closes connection.

Bug #24502	reorganize partition closes connection.
Submitted:	22 Nov 2006 12:49	Modified:	15 Jan 2007 15:22
Reporter:	Etienne Bonanno	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S1 (Critical)
Version:	5.1.14-BK, 5.1.12 Beta	OS:	Linux (Linux (CentOS))
Assigned to:	Alexey Botchkov	CPU Architecture:	Any
Tags:	Q1

Description:
Running the following query:
ALTER TABLE chips.OrigCalls_99 
REORGANIZE PARTITION p_X INTO ( 
    PARTITION p_122006 VALUES LESS THAN (TO_DAYS('2006-12-01')), 
    PARTITION p_X VALUES LESS THAN MAXVALUE 
  )

causes the server to close the connection. The reorganization on the partitions is not performed. The output in the error file is the below.

mysqld got signal 11;
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=268435456
read_buffer_size=1044480
max_used_connections=1
max_connections=100
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 466543
 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x9da0f08
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...
Cannot determine thread, fp=0xa0f236ac, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x81c4c0a
0x83237a4
0x81394c8
0x82c145a
0x81e5994
0x81e69e2
0x81e77d5
0x81e88a7
0xb44371
0x9abffe
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/using-stack-trace.html and follow
instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x9dc5728 = ALTER TABLE chips.OrigCalls_99
REORGANIZE PARTITION p_X INTO (
    PARTITION p_122006 VALUES LESS THAN (TO_DAYS('2006-12-01')),
    PARTITION p_X VALUES LESS THAN MAXVALUE
  )
thd->thread_id=1
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.

Number of processes running now: 0
061122 13:17:09  mysqld restarted
061122 13:17:09  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
061122 13:17:09  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 23247450.
InnoDB: Doing recovery: scanned up to log sequence number 0 23247450
061122 13:17:09  InnoDB: Started; log sequence number 0 23247450
061122 13:17:09 [Note] Recovering after a crash using mysql-bin
061122 13:17:09 [Note] Starting crash recovery...
061122 13:17:09 [Note] Crash recovery finished.
061122 13:17:09 [Note] /opt/mysql-5.1.12-beta/libexec/mysqld: ready for connections.
Version: '5.1.12-beta-log'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
061122 13:17:09 [Note] SCHEDULER: Loaded 0 events

How to repeat:
Run the above query on the below table:

CREATE TABLE `OrigCalls_99` (
  `uniqueId` varchar(32) NOT NULL,
  `aNumber` varchar(15) NOT NULL,
  `bNumber` varchar(30) NOT NULL,
  `callStartTS` datetime NOT NULL,
  `callAnswerTS` datetime DEFAULT NULL,
  `callEndTS` datetime NOT NULL,
  `totalDuration` int(11) NOT NULL,
  `disposition` varchar(20) NOT NULL,
  `amaFlags` varchar(20) NOT NULL,
  `userType` int(11) NOT NULL,
  `LRN` varchar(6) NOT NULL,
  `ratePlanId` int(11) NOT NULL,
  `callCost` int(11) NOT NULL,
  `billedDuration` int(11) NOT NULL,
  `errorId` int(11) NOT NULL,
  `asteriskError` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 
PARTITION BY RANGE (TO_DAYS(callStartTS)) SUBPARTITION BY HASH ( CONVERT(aNumber, SIGNED)) SUBPARTITIONS 40 (PARTITION p_112006 VALUES LESS THAN (TO_DAYS('2006-11-01')) ENGINE = InnoDB, PARTITION p_X VALUES LESS THAN MAXVALUE ENGINE = InnoDB);

Thank you for a bug report. Verified just as described, also with latest 5.1.14-BK on Linux:

mysql> select version();
+-------------------+
| version()         |
+-------------------+
| 5.1.14-beta-debug |
+-------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE `OrigCalls_99` (
    ->   `uniqueId` varchar(32) NOT NULL,
    ->   `aNumber` varchar(15) NOT NULL,
    ->   `bNumber` varchar(30) NOT NULL,
    ->   `callStartTS` datetime NOT NULL,
    ->   `callAnswerTS` datetime DEFAULT NULL,
    ->   `callEndTS` datetime NOT NULL,
    ->   `totalDuration` int(11) NOT NULL,
    ->   `disposition` varchar(20) NOT NULL,
    ->   `amaFlags` varchar(20) NOT NULL,
    ->   `userType` int(11) NOT NULL,
    ->   `LRN` varchar(6) NOT NULL,
    ->   `ratePlanId` int(11) NOT NULL,
    ->   `callCost` int(11) NOT NULL,
    ->   `billedDuration` int(11) NOT NULL,
    ->   `errorId` int(11) NOT NULL,
    ->   `asteriskError` int(11) NOT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    -> PARTITION BY RANGE (TO_DAYS(callStartTS)) SUBPARTITION BY HASH (
    -> CONVERT(aNumber, SIGNED)) SUBPARTITIONS 40 (PARTITION p_112006 VALUES LE
SS THAN
    -> (TO_DAYS('2006-11-01')) ENGINE = InnoDB, PARTITION p_X VALUES LESS THAN
MAXVALUE
    -> ENGINE = InnoDB);
Query OK, 0 rows affected (0.38 sec)

mysql> ALTER TABLE OrigCalls_99
    -> REORGANIZE PARTITION p_X INTO (
    ->     PARTITION p_122006 VALUES LESS THAN (TO_DAYS('2006-12-01')),
    ->     PARTITION p_X VALUES LESS THAN MAXVALUE
    ->   );
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
Number of processes running now: 0
061122 13:25:47  mysqld restarted

Resolved stack trace, if needed:

openxs@suse:~/dbs/5.1> bin/resolve_stack_dump  -s /tmp/mysqld51.sym -n 24502.st
ack
0x822ab8c handle_segfault + 412
0x83df63b _ZN14partition_info20check_partition_infoEP3THDPP10handlertonP7handler
P24st_ha_create_informationb + 895
0x817a0ec _Z21prep_alter_part_tableP3THDP8st_tableP13st_alter_infoP24st_ha_creat
e_informationP10handlertonPbPj + 5750
0x835d995 _Z17mysql_alter_tableP3THDPcS1_P24st_ha_create_informationP13st_table_
listR4ListI12create_fieldERS6_I3KeyEjP8st_orderbP13st_alt + 2871
0x8246b90 _Z21mysql_execute_commandP3THD + 5930
0x824db7c _Z11mysql_parseP3THDPcj + 420
0x8243d78 _Z16dispatch_command19enum_server_commandP3THDPcj + 1918
0x82435ea _Z10do_commandP3THD + 518
0x8242773 handle_one_connection + 973
0x40047aa7 _end + 931011367
0x4023ec2e _end + 933072046

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/17101

ChangeSet@1.2359, 2006-12-18 14:39:23+04:00, holyfoot@mysql.com +3 -0
  bug #24502 (reorganize partition closes connection)
  
  when REORGANIZE creates new partition, no_subparts for that partition
  isn't set right (call handler::set_partitions_defaults always returns 1)
  Normally the number of subpartitions should be inherited from the
  table.

Fixed in 5.1.15-beta

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented fix in 5.1.15 changelog.