Bug #65663 Signal 8 When Selecting with Range Greater than Last Partition
Submitted: 19 Jun 2012 0:36 Modified: 31 Jul 2012 8:21
Reporter: Jervin R Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Partitions Severity:S2 (Serious)
Version:5.5.23, 5.5.24, 5.5.25 OS:Linux
Assigned to: CPU Architecture:Any
Tags: partitioning, range

[19 Jun 2012 0:36] Jervin R
Description:
When SELECTing from a timestamp based range partition where the table has no partition to handle values less than MAXVALUE and the SELECT condition reads from the last partition or greater, mysqld gets a signal 8.

Repeatable with 5.5.23, 5.5.24 and 5.5.25, NOT with 5.5.20, 5.5.21, 5.5.22

How to repeat:
[revin@forge msb_5_5_24]$ ./use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.24 MySQL Community Server (GPL)

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 [localhost] {msandbox} (test) > show create table table_XXXXX \G
*************************** 1. row ***************************
       Table: table_XXXXX
Create Table: CREATE TABLE `table_XXXXX` (
  `record_type` smallint(5) unsigned NOT NULL,
  `timestamp` int(10) unsigned NOT NULL,
  `meta_pos_id` smallint(5) unsigned NOT NULL,
  `value` mediumint(8) unsigned NOT NULL,
  KEY `ts_rtype` (`timestamp`,`record_type`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (timestamp)
(PARTITION 2012y03m VALUES LESS THAN (1333238400) ENGINE = InnoDB,
 PARTITION 2012y04m VALUES LESS THAN (1335830400) ENGINE = InnoDB,
 PARTITION 2012y05m VALUES LESS THAN (1338508800) ENGINE = InnoDB,
 PARTITION 2012y06m VALUES LESS THAN (1341100800) ENGINE = InnoDB) */
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > SELECT COUNT(*) FROM table_XXXXX WHERE `timestamp` > 1338508800;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > SELECT COUNT(*) FROM table_XXXXX WHERE `timestamp` > 1341100799;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Suggested fix:
NA, but a workaround is to add a partition to handle values less than MAXVALUE.
[19 Jun 2012 0:38] Jervin R
Backtrace from core file during one of the crashes.

Attachment: 23293-backtrace.txt (text/plain), 13.89 KiB.

[19 Jun 2012 0:38] Jervin R
Error log from sandbox creation to crash then restart.

Attachment: 23293-error-log.txt (text/plain), 9.63 KiB.

[19 Jun 2012 0:50] Miguel Solorzano
Thank you for the bug report. Indeed repeatable with 5.5.24 but not anymore with current source:

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

h:\dbs>55

h:\dbs>h:\dbs\5.5\bin\mysql -uroot --port=3540 --prompt="mysql 5.5 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.24 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 5.5 >use test
Database changed
mysql 5.5 >CREATE TABLE `table_XXXXX` (
    ->   `record_type` smallint(5) unsigned NOT NULL,
    ->   `timestamp` int(10) unsigned NOT NULL,
    ->   `meta_pos_id` smallint(5) unsigned NOT NULL,
    ->   `value` mediumint(8) unsigned NOT NULL,
    ->   KEY `ts_rtype` (`timestamp`,`record_type`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    -> /*!50100 PARTITION BY RANGE (timestamp)
    -> (PARTITION 2012y03m VALUES LESS THAN (1333238400) ENGINE = InnoDB,
    ->  PARTITION 2012y04m VALUES LESS THAN (1335830400) ENGINE = InnoDB,
    ->  PARTITION 2012y05m VALUES LESS THAN (1338508800) ENGINE = InnoDB,
    ->  PARTITION 2012y06m VALUES LESS THAN (1341100800) ENGINE = InnoDB) */;
Query OK, 0 rows affected (0.88 sec)

mysql 5.5 >SELECT COUNT(*) FROM table_XXXXX WHERE `timestamp` > 1338508800;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql 5.5 >SELECT COUNT(*) FROM table_XXXXX WHERE `timestamp` > 1341100799;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql 5.5 >

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

d:\dbs>55

d:\dbs>d:\dbs\5.5\bin\mysql -uroot --port=3541 --prompt="mysql 5.5 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.26 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 5.5 >use test
Database changed
mysql 5.5 >CREATE TABLE `table_XXXXX` (
    ->   `record_type` smallint(5) unsigned NOT NULL,
    ->   `timestamp` int(10) unsigned NOT NULL,
    ->   `meta_pos_id` smallint(5) unsigned NOT NULL,
    ->   `value` mediumint(8) unsigned NOT NULL,
    ->   KEY `ts_rtype` (`timestamp`,`record_type`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    -> /*!50100 PARTITION BY RANGE (timestamp)
    -> (PARTITION 2012y03m VALUES LESS THAN (1333238400) ENGINE = InnoDB,
    ->  PARTITION 2012y04m VALUES LESS THAN (1335830400) ENGINE = InnoDB,
    ->  PARTITION 2012y05m VALUES LESS THAN (1338508800) ENGINE = InnoDB,
    ->  PARTITION 2012y06m VALUES LESS THAN (1341100800) ENGINE = InnoDB) */;
Query OK, 0 rows affected (0.24 sec)

mysql 5.5 >SELECT COUNT(*) FROM table_XXXXX WHERE `timestamp` > 1338508800;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.05 sec)

mysql 5.5 >SELECT COUNT(*) FROM table_XXXXX WHERE `timestamp` > 1341100799;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql 5.5 >
[19 Jun 2012 1:37] Jervin R
Miguel, have you tried with 5.5.25 official Oracle binaries as I did?
[21 Jun 2012 0:40] Jervin R
Not sure why this is still marked as can't repeat - have you tested with official Oracle binaries yet?
[20 Jul 2012 3:23] Jervin R
The problem still exists on 5.5.25a, not sure why this is still can't repeat?

[revin@forge mysql]$ sb 5525
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.25a MySQL Community Server (GPL)

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 [localhost] {msandbox} ((none)) > use test;
Database changed
mysql [localhost] {msandbox} (test) > CREATE TABLE `table_XXXXX` (
    ->   `record_type` smallint(5) unsigned NOT NULL,
    ->   `timestamp` int(10) unsigned NOT NULL,
    ->   `meta_pos_id` smallint(5) unsigned NOT NULL,
    ->   `value` mediumint(8) unsigned NOT NULL,
    ->   KEY `ts_rtype` (`timestamp`,`record_type`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    -> /*!50100 PARTITION BY RANGE (timestamp)
    -> (PARTITION 2012y03m VALUES LESS THAN (1333238400) ENGINE = InnoDB,
    ->  PARTITION 2012y04m VALUES LESS THAN (1335830400) ENGINE = InnoDB,
    ->  PARTITION 2012y05m VALUES LESS THAN (1338508800) ENGINE = InnoDB,
    ->  PARTITION 2012y06m VALUES LESS THAN (1341100800) ENGINE = InnoDB) */
    -> ;
Query OK, 0 rows affected (0.01 sec)

mysql [localhost] {msandbox} (test) > SELECT COUNT(*) FROM table_XXXXX WHERE `timestamp` > 1338508800;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > SELECT COUNT(*) FROM table_XXXXX WHERE `timestamp` > 1341100799;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql [localhost] {msandbox} (test) >
[29 Jul 2012 15:54] Valeriy Kravchuk
This is a know bug that is fixed in current code:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.28-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> CREATE TABLE `table_XXXXX` (
    -> `record_type` smallint(5) unsigned NOT NULL,
    -> `timestamp` int(10) unsigned NOT NULL,
    -> `meta_pos_id` smallint(5) unsigned NOT NULL,
    -> `value` mediumint(8) unsigned NOT NULL,
    -> KEY `ts_rtype` (`timestamp`,`record_type`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    -> /*!50100 PARTITION BY RANGE (timestamp)
    -> (PARTITION 2012y03m VALUES LESS THAN (1333238400) ENGINE = InnoDB,
    -> PARTITION 2012y04m VALUES LESS THAN (1335830400) ENGINE = InnoDB,
    -> PARTITION 2012y05m VALUES LESS THAN (1338508800) ENGINE = InnoDB,
    -> PARTITION 2012y06m VALUES LESS THAN (1341100800) ENGINE = InnoDB) */;
Query OK, 0 rows affected (0.07 sec)

mysql> SELECT COUNT(*) FROM table_XXXXX WHERE `timestamp` > 1338508800;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM table_XXXXX WHERE `timestamp` > 1341100799;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

So, please, wait for the official release of 5.5.27 with the fix.
[31 Jul 2012 2:17] Jervin R
Valeriy,

Thanks - looks like Miguel tested with 5.5.26 - is this coming to .26 or .27 as you mentioned?
[31 Jul 2012 8:21] Jervin R
Ahh, nvm, found my answer :) http://dev.mysql.com/doc/refman/5.5/en/news-5-5-26.html