Bug #87512 Server crashes when querying data from non-existing partition
Submitted: 22 Aug 2017 20:24 Modified: 23 Aug 2017 0:30
Reporter: Yura Sorokin (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Partitions Severity:S6 (Debug Builds)
Version:5.6 OS:Any
Assigned to: CPU Architecture:Any
Tags: debug

[22 Aug 2017 20:24] Yura Sorokin
Description:
Selecting data from a range that does not correspond to any existing
partition causes server assertion in
'Partition_helper::handle_ordered_index_scan()'.

#0  0x00007f46eafa6741 in pthread_kill () from /lib64/libpthread.so.0
#1  0x0000000000a9ecd4 in my_write_core (sig=6) at /mnt/hgfs/repos/percona-server/mysys/stacktrace.c:424
#2  0x000000000072cbde in handle_fatal_signal (sig=6) at /mnt/hgfs/repos/percona-server/sql/signal_handler.cc:230
#3  <signal handler called>
#4  0x00007f46e97581d7 in raise () from /lib64/libc.so.6
#5  0x00007f46e97598c8 in abort () from /lib64/libc.so.6
#6  0x00007f46e9751146 in __assert_fail_base () from /lib64/libc.so.6
#7  0x00007f46e97511f2 in __assert_fail () from /lib64/libc.so.6
#8  0x0000000000dfb1b5 in ha_partition::handle_ordered_index_scan (this=0x7f46b808f990, buf=0x7f46b808d1a8 "", reverse_order=false) at /mnt/hgfs/repos/percona-server/sql/ha_partition.cc:5954
#9  0x0000000000dfa069 in ha_partition::common_first_last (this=0x7f46b808f990, buf=0x7f46b808d1a8 "") at /mnt/hgfs/repos/percona-server/sql/ha_partition.cc:5404
#10 0x0000000000df9fdd in ha_partition::index_last (this=0x7f46b808f990, buf=0x7f46b808d1a8 "") at /mnt/hgfs/repos/percona-server/sql/ha_partition.cc:5383
#11 0x00000000006429fa in handler::ha_index_last (this=0x7f46b808f990, buf=0x7f46b808d1a8 "") at /mnt/hgfs/repos/percona-server/sql/handler.cc:2886
#12 0x00000000007ae085 in join_read_last (tab=0x7f46b805a868) at /mnt/hgfs/repos/percona-server/sql/sql_executor.cc:2577
#13 0x00000000007ab176 in sub_select (join=0x7f46b8005d20, join_tab=0x7f46b805a868, end_of_records=false) at /mnt/hgfs/repos/percona-server/sql/sql_executor.cc:1259
#14 0x00000000007aab76 in do_select (join=0x7f46b8005d20) at /mnt/hgfs/repos/percona-server/sql/sql_executor.cc:936
#15 0x00000000007a8b15 in JOIN::exec (this=0x7f46b8005d20) at /mnt/hgfs/repos/percona-server/sql/sql_executor.cc:194
#16 0x0000000000808c09 in mysql_execute_select (thd=0x280ac90, select_lex=0x280d160, free_join=true) at /mnt/hgfs/repos/percona-server/sql/sql_select.cc:1101
#17 0x0000000000808efb in mysql_select (thd=0x280ac90, tables=0x7f46b8005210, wild_num=1, fields=..., conds=0x7f46b8005968, order=0x280d328, group=0x280d260, having=0x0, select_options=2147748608, result=0x7f46b8005cf8, unit=0x280cb18, select_lex=0x280d160) at /mnt/hgfs/repos/percona-server/sql/sql_select.cc:1222
#18 0x0000000000806ff4 in handle_select (thd=0x280ac90, result=0x7f46b8005cf8, setup_tables_done_option=0) at /mnt/hgfs/repos/percona-server/sql/sql_select.cc:110
#19 0x00000000007e1227 in execute_sqlcom_select (thd=0x280ac90, all_tables=0x7f46b8005210) at /mnt/hgfs/repos/percona-server/sql/sql_parse.cc:5186
#20 0x00000000007da014 in mysql_execute_command (thd=0x280ac90) at /mnt/hgfs/repos/percona-server/sql/sql_parse.cc:2694
#21 0x00000000007e3d32 in mysql_parse (thd=0x280ac90, rawbuf=0x7f46b8004fd0 "SELECT * FROM t1 WHERE dttm > '2017-01-19' ORDER BY id DESC", length=59, parser_state=0x7f46e02085d0) at /mnt/hgfs/repos/percona-server/sql/sql_parse.cc:6438
#22 0x00000000007d7022 in dispatch_command (command=COM_QUERY, thd=0x280ac90, packet=0x28dd911 "", packet_length=59) at /mnt/hgfs/repos/percona-server/sql/sql_parse.cc:1376
#23 0x00000000007d6060 in do_command (thd=0x280ac90) at /mnt/hgfs/repos/percona-server/sql/sql_parse.cc:1039
#24 0x000000000079da25 in do_handle_one_connection (thd_arg=0x280ac90) at /mnt/hgfs/repos/percona-server/sql/sql_connect.cc:982
#25 0x000000000079d7c2 in handle_one_connection (arg=0x280ac90) at /mnt/hgfs/repos/percona-server/sql/sql_connect.cc:899
#26 0x0000000000de118f in pfs_spawn_thread (arg=0x2828a40) at /mnt/hgfs/repos/percona-server/storage/perfschema/pfs.cc:1860
#27 0x00007f46eafa1dc5 in start_thread () from /lib64/libpthread.so.0
#28 0x00007f46e981a76d in clone () from /lib64/libc.so.6

How to repeat:
CREATE TABLE t1(
  id INT UNSIGNED NOT NULL,
  dttm DATETIME NOT NULL,
  PRIMARY KEY(id, dttm)
) ENGINE=InnoDB
PARTITION BY RANGE COLUMNS(dttm) (
  PARTITION pf_201612 VALUES LESS THAN ('20170101') ENGINE=InnoDB
);

SELECT * FROM t1 WHERE dttm > '2017-01-19' ORDER BY id DESC;
SELECT * FROM t1 WHERE dttm > '2017-01-19' ORDER BY id ASC;

Suggested fix:
This happens because 'ha_partition::partition_scan_set_up()' when
called with 'idx_read_flag == false' does not set 'm_part_spec.start_part' /
'm_part_spec.end_part' properly and therefore does not return
'HA_ERR_END_OF_FILE' (indicating empty result set).

Fix by setting 'm_part_spec' fields to first / last bit set in the
'm_part_info->read_partitions' bitmap.
[22 Aug 2017 20:30] Yura Sorokin
Possibly a duplicate of Bug #76418
[22 Aug 2017 20:31] Yura Sorokin
5.6 fix

Attachment: bug87512_5.6.diff (application/octet-stream, text), 2.31 KiB.

[22 Aug 2017 20:55] Yura Sorokin
5.7 fix

Attachment: bug87512_5.7.diff (application/octet-stream, text), 2.43 KiB.

[22 Aug 2017 23:20] MySQL Verification Team
Thank you for the bug report. I couldn't repeat anymore with current source server so it was somewhat fixed:

miguel@ural:~/dbs $ ./56c
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.38 Source distribution

Copyright (c) 2000, 2017, 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.6 > CREATE DATABASE db1;
Query OK, 1 row affected (0.01 sec)

mysql 5.6 > USE db1
Database changed
mysql 5.6 > CREATE TABLE t1(
    ->   id INT UNSIGNED NOT NULL,
    ->   dttm DATETIME NOT NULL,
    ->   PRIMARY KEY(id, dttm)
    -> ) ENGINE=InnoDB
    -> PARTITION BY RANGE COLUMNS(dttm) (
    ->   PARTITION pf_201612 VALUES LESS THAN ('20170101') ENGINE=InnoDB
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql 5.6 > 
mysql 5.6 > SELECT * FROM t1 WHERE dttm > '2017-01-19' ORDER BY id DESC;
Empty set (0.00 sec)

mysql 5.6 > SELECT * FROM t1 WHERE dttm > '2017-01-19' ORDER BY id ASC;
Empty set (0.00 sec)

mysql 5.6 > 
-----------------------------------------------------------------------
miguel@ural:~/dbs $ ./57c
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.20 Source distribution 2017-AUG-15

Copyright (c) 2000, 2017, 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.7 > CREATE DATABASE db1;
Query OK, 1 row affected (0.00 sec)

mysql 5.7 > USE db1
Database changed
mysql 5.7 > CREATE TABLE t1(
    ->   id INT UNSIGNED NOT NULL,
    ->   dttm DATETIME NOT NULL,
    ->   PRIMARY KEY(id, dttm)
    -> ) ENGINE=InnoDB
    -> PARTITION BY RANGE COLUMNS(dttm) (
    ->   PARTITION pf_201612 VALUES LESS THAN ('20170101') ENGINE=InnoDB
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql 5.7 > 
mysql 5.7 > SELECT * FROM t1 WHERE dttm > '2017-01-19' ORDER BY id DESC;
Empty set (0.00 sec)

mysql 5.7 > SELECT * FROM t1 WHERE dttm > '2017-01-19' ORDER BY id ASC;
Empty set (0.00 sec)

mysql 5.7 
-----------------------------------------------------------------------
miguel@ural:~/dbs $ ./80c
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.3-rc Source distribution 2017-AUG-15

Copyright (c) 2000, 2017, 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 8.0 > CREATE DATABASE db1;
Query OK, 1 row affected (0.01 sec)

mysql 8.0 > USE db1
Database changed
mysql 8.0 > CREATE TABLE t1(
    ->   id INT UNSIGNED NOT NULL,
    ->   dttm DATETIME NOT NULL,
    ->   PRIMARY KEY(id, dttm)
    -> ) ENGINE=InnoDB
    -> PARTITION BY RANGE COLUMNS(dttm) (
    ->   PARTITION pf_201612 VALUES LESS THAN ('20170101') ENGINE=InnoDB
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql 8.0 > 
mysql 8.0 > SELECT * FROM t1 WHERE dttm > '2017-01-19' ORDER BY id DESC;
Empty set (0.01 sec)

mysql 8.0 > SELECT * FROM t1 WHERE dttm > '2017-01-19' ORDER BY id ASC;
Empty set (0.00 sec)

mysql 8.0 >
[22 Aug 2017 23:23] MySQL Verification Team
Will test debug server.
[23 Aug 2017 0:27] MySQL Verification Team
Also not more repeatable on 5.7 and 8.0 versions:

c:\dbs>c:\dbs\5.7\bin\mysql -uroot --port=3570 -p --local-infile  --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.20-debug Source distribution 2017-AUG-15

Copyright (c) 2000, 2017, 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.7 > USE db1
Database changed
mysql 5.7 > CREATE TABLE t1(
    ->   id INT UNSIGNED NOT NULL,
    ->   dttm DATETIME NOT NULL,
    ->   PRIMARY KEY(id, dttm)
    -> ) ENGINE=InnoDB
    -> PARTITION BY RANGE COLUMNS(dttm) (
    ->   PARTITION pf_201612 VALUES LESS THAN ('20170101') ENGINE=InnoDB
    -> );
Query OK, 0 rows affected (0.36 sec)

mysql 5.7 >
mysql 5.7 > SELECT * FROM t1 WHERE dttm > '2017-01-19' ORDER BY id DESC;
Empty set (0.03 sec)

mysql 5.7 > SELECT * FROM t1 WHERE dttm > '2017-01-19' ORDER BY id ASC;
Empty set (0.02 sec)

mysql 5.7 >

c:\dbs>c:\dbs\8.0\bin\mysql -uroot --port=3580 -p --prompt="mysql 8.0 > " --default-character-set=utf8mb4
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.3-rc-debug Source distribution 2017-AUG-15

Copyright (c) 2000, 2017, 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 8.0 > CREATE DATABASE db1;
Query OK, 1 row affected (0.14 sec)

mysql 8.0 > USE db1
Database changed
mysql 8.0 > CREATE TABLE t1(
    ->   id INT UNSIGNED NOT NULL,
    ->   dttm DATETIME NOT NULL,
    ->   PRIMARY KEY(id, dttm)
    -> ) ENGINE=InnoDB
    -> PARTITION BY RANGE COLUMNS(dttm) (
    ->   PARTITION pf_201612 VALUES LESS THAN ('20170101') ENGINE=InnoDB
    -> );
Query OK, 0 rows affected (0.39 sec)

mysql 8.0 >
mysql 8.0 > SELECT * FROM t1 WHERE dttm > '2017-01-19' ORDER BY id DESC;
Empty set (0.06 sec)

mysql 8.0 > SELECT * FROM t1 WHERE dttm > '2017-01-19' ORDER BY id ASC;
Empty set (0.00 sec)
[23 Aug 2017 0:30] MySQL Verification Team
This bug is only repeatable on current source server on debug compiled server 5.6 not anymore on 5.7 and 8.0 versions:

2017-08-22 20:26:16 15036 [Note] Event Scheduler: Loaded 0 events
2017-08-22 20:26:16 15036 [Note] c:\dbs\5.6\bin\mysqld: ready for connections.
Version: '5.6.38-debug'  socket: ''  port: 3560  Source distribution 2017-AUG-15
Assertion failed: bitmap_is_set(&m_part_info->read_partitions, m_part_spec.start_part), file C:\build\2017AUG15\mysql-5.6\sql\ha_partition.cc, line 5952
R6010
- abort() has been called
23:28:31 UTC - mysqld got exception 0x80000003 ;
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=8388608
read_buffer_size=131072
max_used_connections=1
max_threads=151
thread_count=1
connection_count=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 67958 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x2c422c99090
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...
7ff7dc400245    mysqld.exe!my_sigabrt_handler()[my_thr_init.c:455]
7ffe68c9396f    MSVCR120D.dll!raise()
7ffe68ca2060    MSVCR120D.dll!abort()
7ffe68c8bf78    MSVCR120D.dll!_wassert()
7ff7dc904e6f    mysqld.exe!ha_partition::handle_ordered_index_scan()[ha_partition.cc:5952]
7ff7dc9041e7    mysqld.exe!ha_partition::common_first_last()[ha_partition.cc:5402]
7ff7dc9034fe    mysqld.exe!ha_partition::index_last()[ha_partition.cc:5380]
7ff7dbf0557b    mysqld.exe!handler::ha_index_last()[handler.cc:2887]
7ff7dc2c166c    mysqld.exe!join_read_last()[sql_executor.cc:2577]
7ff7dc2c0192    mysqld.exe!sub_select()[sql_executor.cc:1259]
7ff7dc2c5e3e    mysqld.exe!do_select()[sql_executor.cc:936]
7ff7dc2c4532    mysqld.exe!JOIN::exec()[sql_executor.cc:194]
7ff7dc24558f    mysqld.exe!mysql_execute_select()[sql_select.cc:1104]
7ff7dc238359    mysqld.exe!mysql_select()[sql_select.cc:1222]
7ff7dc237f6d    mysqld.exe!handle_select()[sql_select.cc:110]
7ff7dc045b43    mysqld.exe!execute_sqlcom_select()[sql_parse.cc:5187]
7ff7dc0373ac    mysqld.exe!mysql_execute_command()[sql_parse.cc:2695]
7ff7dc035e3a    mysqld.exe!mysql_parse()[sql_parse.cc:6439]
7ff7dc03f63a    mysqld.exe!dispatch_command()[sql_parse.cc:1379]
7ff7dc03e657    mysqld.exe!do_command()[sql_parse.cc:1039]
7ff7dc08ffe2    mysqld.exe!do_handle_one_connection()[sql_connect.cc:982]
7ff7dc08fe12    mysqld.exe!handle_one_connection()[sql_connect.cc:900]
7ff7dc8c5155    mysqld.exe!pfs_spawn_thread()[pfs.cc:1862]
7ff7dc3fe807    mysqld.exe!pthread_start()[my_winthread.c:62]
7ffe68b7a105    MSVCR120D.dll!_beginthreadex()
7ffe68b7a357    MSVCR120D.dll!_endthreadex()
7ffeabc62774    KERNEL32.DLL!BaseThreadInitThunk()
7ffead8d0d51    ntdll.dll!RtlUserThreadStart()

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (2c422fa9f80): SELECT * FROM t1 WHERE dttm > '2017-01-19' ORDER BY id DESCConnection ID (thread ID): 1
Status: NOT_KILLED