Server crashes when querying partitioning table MySQL_5.7.14

[DONGCHAN SUNG]

Hi~

We found that the server was crashed by running a data lookup on the partition table. It only seems to happen if there is only one partition over the time period, and you can try the following simple test.

=================================================
create table `crash` (
`id` int(11) unsigned not null auto_increment,
`key` varchar(32) not null default '',
`msg` text,
`txt` varchar(255) not null default '',
`dttm` datetime not null default current_timestamp,
primary key (`id`,`dttm`),
key `ix_pippi_msg_01` (`key`)
) engine=innodb default charset=utf8mb4 row_format=dynamic
/*!50500 partition by range columns(dttm)
(partition pf_201612 values less than ('20170101') engine = innodb) */ ;

1)
Querying the following query will crash the server.
=================================================
mysql> select * from crash where dttm > '2017-01-19' order by id desc;
ERROR 2013 (HY000): Lost connection to MySQL server during query

2)
There is no crash when sorting forward, like this.
=================================================
mysql> select * from crash where dttm > '2017-01-19' order by id asc;
Empty set (0.00 sec)

The alternative is to configure the table with more than one partitioning definition as shown below, but it seems to require an inherent solution from a server crash perspective. (NO Crash!)
=================================================
alter table crash partition by range columns(dttm)
(partition pf_201611 values less than ('20161201') engine = innodb,
partition pf_201612 values less than ('20170101') engine = innodb);

mysql> select * from crash where dttm > '2017-01-19' order by id desc;
Empty set (0.00 sec)

mysql> select * from crash where dttm > '2017-01-19' order by id asc;
Empty set (0.00 sec)

This is an urgent situation that requires rapid improvement in terms of service stability. (I tested on Percona MySQL_5.7.14. )

Thanks.
Chan.

[Laurynas Biveinis (laurynas-biveinis)]

Is this an upstream bug?

[Nickolay Ihalainen (ihanick)]

Crashes mysql for both debug and release builds.
Looks similar to http://bugs.mysql.com/bug.php?id=76418

--source include/have_innodb.inc

drop table if exists t;
create table t(id int(11) unsigned not null auto_increment, dttm datetime not null default current_timestamp, msg text,primary key (id,dttm)) engine=innodb default charset=utf8mb4 row_format=dynamic partition by range columns(dttm) (partition pf_201612 values less than ('20170101') engine = innodb);

select * from t where dttm > '2017-01-19' order by id desc;

Backtrace from release build:
mysqld(Partition_helper::handle_ordered_index_scan(unsigned char*)+0x5c)[0xbc18fc]
mysqld(handler::ha_index_last(unsigned char*)+0x208)[0x7f9c78]
mysqld(join_read_last(QEP_TAB*)+0x7c)[0xc2b8fc]
mysqld(sub_select(JOIN*, QEP_TAB*, bool)+0x11b)[0xc3222b]
mysqld(JOIN::exec()+0x3b8)[0xc2ad18]
mysqld(handle_query(THD*, LEX*, Query_result*, unsigned long long, unsigned long long)+0x238)[0xc9d498]
mysqld[0x75f9dd]
mysqld(mysql_execute_command(THD*, bool)+0x3449)[0xc5d739]
mysqld(mysql_parse(THD*, Parser_state*)+0x625)[0xc608d5]
mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0x874)[0xc611c4]
mysqld(do_command(THD*)+0x1b7)[0xc627b7]
mysqld(handle_connection+0x2a0)[0xd26960]
mysqld(pfs_spawn_thread+0x1b4)[0xe9f284]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x8064)[0x7fa44bd28064]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7fa449d3062d]

[Yura Sorokin (yura-sorokin)]

Here is a reduced test case

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;

[Yura Sorokin (yura-sorokin)]

Also affects 5.6, but not 5.5

[Yura Sorokin (yura-sorokin)]

https://github.com/percona/percona-server/pull/1744 (MTR test case only)
https://github.com/percona/percona-server/pull/1745
https://github.com/percona/percona-server/pull/1746

[Yura Sorokin (yura-sorokin)]

Additional upstream bug reported (including suggested patches)
https://bugs.mysql.com/bug.php?id=87512

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1050