Server crashes when querying partitioning table MySQL_5.7.14

Bug #1657941 reported by DONGCHAN SUNG on 2017-01-20
12
This bug affects 2 people
Affects Status Importance Assigned to Milestone
MySQL Server
Unknown
Unknown
Percona Server moved to https://jira.percona.com/projects/PS
Status tracked in 5.7
5.5
Invalid
Undecided
Unassigned
5.6
Fix Released
High
Yura Sorokin
5.7
Fix Released
High
Yura Sorokin

Bug Description

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.

DONGCHAN SUNG (sdchan1) wrote :
Changed in percona-server:
status: New → Confirmed

Is this an upstream bug?

Nickolay Ihalainen (ihanick) wrote :

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]

tags: added: i164620
tags: added: upstream
Yura Sorokin (yura-sorokin) wrote :

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) wrote :

Also affects 5.6, but not 5.5

Yura Sorokin (yura-sorokin) wrote :

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

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

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers

Bug attachments

Remote bug watches

Bug watches keep track of this bug in other bug trackers.