MySQL Bugs: #65905: MySQL crashes when selecting from a table for a partition that does not exist

Bug #65905	MySQL crashes when selecting from a table for a partition that does not exist
Submitted:	16 Jul 2012 5:32	Modified:	18 Jul 2012 16:44
Reporter:	Tejas Nevrekar	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S2 (Serious)
Version:	5.5.24, 5.5.25a	OS:	Linux (CentOS 5/Fedora 16)
Assigned to:		CPU Architecture:	Any

Description:
When selecting data from a partitioned table that does not have the required partition, the MySQL DB crashes.
Here, as given in the steps to reproduce, the table is partitioned by time. When inserting into a table for which there is no partition, MySQL gracefully errors out.
However, when selecting data for which there is no partition, it crashes.

How to repeat:
DROP TABLE tab_raw;

CREATE TABLE tab_raw
(
   id1 INT NOT NULL,
   id2 INT NOT NULL,
   valueType INT NOT NULL,
   value DOUBLE NOT NULL,
   `minValue` DOUBLE DEFAULT NULL,
   `maxValue` DOUBLE DEFAULT NULL,
   timeStamp DATETIME NOT NULL,
   timePeriod TINYINT NOT NULL,
   validity TINYINT NOT NULL,
   opsType TINYINT NOT NULL
) ENGINE=innodb ROW_FORMAT=COMPRESSED
PARTITION BY RANGE ( to_days(timeStamp))
(
partition p0 values less than (to_days('2012-07-16 00:00:00')),
partition p1 values less than (to_days('2012-07-17 00:00:00'))
);

create INDEX tab_raw_idx1 on tab_raw (id1,id2);
create INDEX tab_raw_idx2 on tab_raw (id2);
create INDEX tab_raw_idx3 on tab_raw (valueType);
create INDEX tab_raw_idx4 on tab_raw (timeStamp asc);

/* INSERT executes successfully. */
INSERT INTO tab_raw (id1, id2, valueType, value, `minValue`, `maxValue`, timeStamp, timePeriod, validity, opsType)
             values (10, 20, 30, 40, 50, 60, '2012-07-16 00:00:10', 80, 90, 100);

/* INSERT fails as partition does not exist. */
INSERT INTO tab_raw (id1, id2, valueType, value, `minValue`, `maxValue`, timeStamp, timePeriod, validity, opsType)
             values (11, 21, 31, 41, 51, 61, '2012-07-17 00:00:10', 81, 91, 101);

/* SELECT crashes the DB as partition does not exist. */
INSERT INTO tab_raw (id1, id2, valueType, value, `minValue`, `maxValue`, timeStamp, timePeriod, validity, opsType)
SELECT *
FROM tab_raw
WHERE timeStamp >= '2012-07-17 00:00:01';

Also see the attached file for the crash dumps:
mysqld.txt - crash dump
mysqld.symbols - symbols for mysqld installation.
mysqld_resolved.txt - crash dump resolved by resolve_stack_dump

Suggested fix:
In stead of crashing when selecting data for which there is no partition, it should rather returns no rows.

MySQL Crash Dump & Symbols

Attachment: create-select-nopart.tgz (application/octet-stream, text), 215.26 KiB.

I can not repeat this with 5.5.27 (current mysql-5.5 tree):

[openxs@chief 5.5]$ bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.27-debug Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> DROP TABLE tab_raw;
ERROR 1051 (42S02): Unknown table 'tab_raw'
mysql> 
mysql> CREATE TABLE tab_raw
    -> (
    ->    id1 INT NOT NULL,
    ->    id2 INT NOT NULL,
    ->    valueType INT NOT NULL,
    ->    value DOUBLE NOT NULL,
    ->    `minValue` DOUBLE DEFAULT NULL,
    ->    `maxValue` DOUBLE DEFAULT NULL,
    ->    timeStamp DATETIME NOT NULL,
    ->    timePeriod TINYINT NOT NULL,
    ->    validity TINYINT NOT NULL,
    ->    opsType TINYINT NOT NULL
    -> ) ENGINE=innodb ROW_FORMAT=COMPRESSED
    -> PARTITION BY RANGE ( to_days(timeStamp))
    -> (
    -> partition p0 values less than (to_days('2012-07-16 00:00:00')),
    -> partition p1 values less than (to_days('2012-07-17 00:00:00'))
    -> );
Query OK, 0 rows affected, 4 warnings (0.13 sec)

mysql> 
mysql> create INDEX tab_raw_idx1 on tab_raw (id1,id2);
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create INDEX tab_raw_idx2 on tab_raw (id2);
Query OK, 0 rows affected (0.33 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create INDEX tab_raw_idx3 on tab_raw (valueType);
Query OK, 0 rows affected (0.23 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create INDEX tab_raw_idx4 on tab_raw (timeStamp asc);
Query OK, 0 rows affected (0.22 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> /* INSERT executes successfully. */
mysql> INSERT INTO tab_raw (id1, id2, valueType, value, `minValue`, `maxValue`, timeStamp, timePeriod, validity, opsType)
    ->              values (10, 20, 30, 40, 50, 60, '2012-07-16 00:00:10', 80, 90, 100);
Query OK, 1 row affected (0.03 sec)

mysql> 
mysql> /* INSERT fails as partition does not exist. */
mysql> INSERT INTO tab_raw (id1, id2, valueType, value, `minValue`, `maxValue`, timeStamp, timePeriod, validity, opsType)
    ->              values (11, 21, 31, 41, 51, 61, '2012-07-17 00:00:10', 81, 91, 101);
ERROR 1526 (HY000): Table has no partition for value 735066
mysql> 
mysql> INSERT INTO tab_raw (id1, id2, valueType, value, `minValue`, `maxValue`, timeStamp, timePeriod, validity, opsType)
    -> SELECT *
    -> FROM tab_raw
    -> WHERE timeStamp >= '2012-07-17 00:00:01';
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

Looks like the problem is solved in the meantime. Please, check with 5.5.25a or wait for the next official release.

I retried the same with 5.5.25a - http://www.mysql.com/downloads/mirror.php?id=408580 

Here are the results:

----------------------------------------------------------------------(centina@localhost) [sa]> source create-select-nopart.sql;
ERROR 1051 (42S02): Unknown table 'tab_raw'
Query OK, 0 rows affected (0.28 sec)

Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 1 row affected (0.01 sec)

ERROR 1526 (HY000): Table has no partition for value 735066
ERROR 2013 (HY000): Lost connection to MySQL server during query
----------------------------------------------------------------------

Thus the problem still exists. Am I missing anything else here?

mysql 5.5.25a crash dump

Attachment: mysqld-5.5.25a.txt (text/plain), 4.34 KiB.

This is a duplicate of internal bug that is fixed in 5.5.26+. Please, wait for the next official 5.5 release and re-open this bug report if you still get the crash with it.