Bug #66161 partition by range on multiple col. primary key w/ no maxvalue restarts server
Submitted: 2 Aug 2012 14:55 Modified: 2 Aug 2012 15:03
Reporter: Michael Morse Email Updates:
Status: Duplicate Impact on me:
Category:MySQL Server: Partitions Severity:S1 (Critical)
Version:5.5.24 OS:Any
Assigned to: CPU Architecture:Any
Tags: partition range maxvalue

[2 Aug 2012 14:55] Michael Morse
This is not common but critical if it does..

When creating a partitioned table by range on one column (apparently the first column), with a multiple column primary key, if the partitions do not cover all possible values (i.e. using maxvalue for the last partition), and any query above that partition range and within the datatype range (i.e. 2147483647  for INT SIGNED) will restart the mysql server, mysql.sock will disappear in the process and take down a production environment waiting for all processes to finish.

How to repeat:
window #1

mysqld_safe --defaults-file=/etc/my-whatever.cnf &

window #2

CREATE TABLE `table_A` (`id` int(1) NOT NULL AUTO_INCREMENT,  `a` tinyint(1) NOT NULL DEFAULT '0',  `b` int(5) DEFAULT NULL, `c` int(5) DEFAULT NULL,  PRIMARY KEY (`id`, `a`)) ENGINE=InnoDB partition by range (id)(PARTITION p0 VALUES LESS THAN (100));

case i

mysql> select * from table_A where id='99';
Empty set (0.00 sec)

case ii

mysql> select * from table_A where id='2147483648';
Empty set, 1 warning (0.00 sec)

case iii

mysql> select * from table_A where id='100';
ERROR 2013 (HY000): Lost connection to MySQL server during query

mysql> select * from table_A where id='2147483647';
ERROR 2013 (HY000): Lost connection to MySQL server during query

window #2 result for case iii

120801 15:00:45 mysqld_safe Number of processes running now: 0
120801 15:00:45 mysqld_safe mysqld restarted

Suggested fix:
assign it the following error code,

MySQL error code 1526 (ER_NO_PARTITION_FOR_GIVEN_VALUE): Table has no partition for value %-.64s
[2 Aug 2012 15:03] Valeriy Kravchuk
This is a known problem (see bug #65587). This bug is already fixed. Wait for the next GA release, 5.5.27.