[MDEV-21665] Server crashes in my_qsort2 / Filesort_buffer::sort_buffer - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.5
Fix Version/s: 10.5.1
Component/s: Optimizer
Labels:
None

Description

Notes:

To reproduce the problem, the test case below needs to be run with --mysqld=--innodb-page-size=4k. I don't know why exactly it is important, but it is.
The test case uses innodb_stats_persistent. It intentionally runs INSERT in two parts and ANALYZE in between. This way it fails for me every time. However, I suppose it can still be non-deterministic. If it is so and it doesn't fail for you, I suggest to try the alternative test case which is provided after the main one. In the alternative test case, after the test table is populated, the data in the innodb statistical tables is replaced and the server is restarted to pick up the statistics. The new data is not artificial, these are real values which were stored in a table when this test case failed "naturally". Hopefully this approach will make the test case deterministic.

Main test case
--source include/have_partition.inc
--source include/have_innodb.inc
--source include/have_innodb_4k.inc

--let $innodb_stats_persistent= `SELECT @@innodb_stats_persistent`
SET GLOBAL innodb_stats_persistent= ON;

CREATE TABLE t1 (
pk int,
a char(1),
b char(1),
c char(255),
d enum ('a','b'),
e enum ('c','d'),
f varchar(255),
g varchar(255),
h enum ('e','f'),
i varchar(255) CHARACTER SET ucs2,
j enum ('g','h'),
k text,
l char(255) CHARACTER SET ucs2,
m enum ('i','j'),
n text,
o char(255),
p varchar(255),
q text,
r enum ('k','l'),
primary key (pk)
) ENGINE=InnoDB PARTITION BY key (pk) partitions 2;

INSERT INTO t1 VALUES
(1,'c','r','g','a','c','t','k','e','a','g','c','t','i','c','d','s','f','k');

ANALYZE TABLE t1;

INSERT INTO t1 VALUES
(2,'e','i','u','a','c','s','z','e','c','g','n','c','i','o','e','o','p','l'),
(3,'n','s','r','b','d','r','z','f','g','h','e','a','j','o','b','p','n','k'),
(4,'h','c','x','a','c','c','k','e','c','g','b','w','i','b','l','f','h','l'),
(5,'v','g','h','b','d','l','a','f','v','h','k','o','j','c','y','p','p','k'),
(6,'f','q','f','a','c','v','o','e','e','g','u','m','i','e','n','k','u','l'),
(7,'w','y','c','b','d','m','d','f','t','h','p','z','j','s','q','v','b','k'),
(8,'w','z','m','a','c','b','o','e','g','g','q','v','i','y','w','d','c','l'),
(9,'a','v','e','b','d','u','l','f','h','h','w','b','j','x','a','z','e','k'),
(10,'w','n','p','a','c','k','g','e','t','g','h','a','i','c','f','g','p','l'),
(11,'p','k','v','b','d','o','c','f','c','h','e','r','j','c','r','t','o','k'),
(12,'s','q','v','a','c','i','n','e','d','g','o','s','i','g','l','c','t','l'),
(13,'b','y','b','b','d','k','a','f','h','h','y','g','j','s','d','b','s','k'),
(14,'w','s','e','a','c','d','h','e','d','g','g','r','i','w','i','r','f','l'),
(15,'s','h','x','b','d','d','m','f','c','h','f','e','j','s','u','e','t','k');

UPDATE t1 SET pk = pk+1000 ORDER BY pk LIMIT 9;

# Cleanup
DROP TABLE t1;
eval SET GLOBAL innodb_stats_persistent= $innodb_stats_persistent;

Alternative test case
--source include/have_partition.inc
--source include/have_innodb.inc
--source include/have_innodb_4k.inc

--let $innodb_stats_persistent= `SELECT @@innodb_stats_persistent`
SET GLOBAL innodb_stats_persistent= ON;

CREATE TABLE t1 (
pk int,
a char(1),
b char(1),
c char(255),
d enum ('a','b'),
e enum ('c','d'),
f varchar(255),
g varchar(255),
h enum ('e','f'),
i varchar(255) CHARACTER SET ucs2,
j enum ('g','h'),
k text,
l char(255) CHARACTER SET ucs2,
m enum ('i','j'),
n text,
o char(255),
p varchar(255),
q text,
r enum ('k','l'),
primary key (pk)
) ENGINE=InnoDB PARTITION BY key (pk) partitions 2;

INSERT INTO t1 VALUES
(1,'c','r','g','a','c','t','k','e','a','g','c','t','i','c','d','s','f','k'),
(2,'e','i','u','a','c','s','z','e','c','g','n','c','i','o','e','o','p','l'),
(3,'n','s','r','b','d','r','z','f','g','h','e','a','j','o','b','p','n','k'),
(4,'h','c','x','a','c','c','k','e','c','g','b','w','i','b','l','f','h','l'),
(5,'v','g','h','b','d','l','a','f','v','h','k','o','j','c','y','p','p','k'),
(6,'f','q','f','a','c','v','o','e','e','g','u','m','i','e','n','k','u','l'),
(7,'w','y','c','b','d','m','d','f','t','h','p','z','j','s','q','v','b','k'),
(8,'w','z','m','a','c','b','o','e','g','g','q','v','i','y','w','d','c','l'),
(9,'a','v','e','b','d','u','l','f','h','h','w','b','j','x','a','z','e','k'),
(10,'w','n','p','a','c','k','g','e','t','g','h','a','i','c','f','g','p','l'),
(11,'p','k','v','b','d','o','c','f','c','h','e','r','j','c','r','t','o','k'),
(12,'s','q','v','a','c','i','n','e','d','g','o','s','i','g','l','c','t','l'),
(13,'b','y','b','b','d','k','a','f','h','h','y','g','j','s','d','b','s','k'),
(14,'w','s','e','a','c','d','h','e','d','g','g','r','i','w','i','r','f','l'),
(15,'s','h','x','b','d','d','m','f','c','h','f','e','j','s','u','e','t','k');

delete from mysql.innodb_table_stats where database_name= 'test';
delete from mysql.innodb_index_stats where database_name= 'test';

INSERT INTO mysql.innodb_table_stats VALUES
('test','t1#P#p0','2020-02-04 16:43:24',1,1,0),
('test','t1#P#p1','2020-02-04 16:43:24',0,1,0);

INSERT INTO mysql.innodb_index_stats VALUES
('test','t1#P#p0','PRIMARY','2020-02-04 16:43:24','n_diff_pfx01',1,1,'pk'),
('test','t1#P#p0','PRIMARY','2020-02-04 16:43:24','n_leaf_pages',1,NULL,'Number of leaf pages in the index'),
('test','t1#P#p0','PRIMARY','2020-02-04 16:43:24','size',1,NULL,'Number of pages in the index'),

('test','t1#P#p1','PRIMARY','2020-02-04 16:43:24','n_diff_pfx01',0,1,'pk'),
('test','t1#P#p1','PRIMARY','2020-02-04 16:43:24','n_leaf_pages',1,NULL,'Number of leaf pages in the index'),
('test','t1#P#p1','PRIMARY','2020-02-04 16:43:24','size',1,NULL,'Number of pages in the index');

--source include/restart_mysqld.inc
SET GLOBAL innodb_stats_persistent= ON;

UPDATE t1 SET pk = pk+1000 ORDER BY pk LIMIT 9;

# Cleanup
DROP TABLE t1;
eval SET GLOBAL innodb_stats_persistent= $innodb_stats_persistent;

10.5 debug 287c1db7
#3 <signal handler called>
#4 __memcmp_sse4_1 () at ../sysdeps/x86_64/multiarch/memcmp-sse4.S:915
#5 0x0000557561716a1a in native_compare (length=0x7fce7529a320, a=0x7fce4016b570, b=0x7fce4016b500) at /data/src/10.5/mysys/ptr_cmp.c:49
#6 0x00005575617024e4 in my_qsort2 (base_ptr=0x7fce4016b500, count=15, size=8, cmp=0x5575617169e6 <native_compare>, cmp_argument=0x7fce7529a320) at /data/src/10.5/mysys/mf_qsort.c:154
#7 0x0000557561025f98 in Filesort_buffer::sort_buffer (this=0x7fce40146ce0, param=0x7fce7529a520, count=15) at /data/src/10.5/sql/filesort_utils.cc:186
#8 0x0000557560df67d3 in SORT_INFO::sort_buffer (this=0x7fce40146ce0, param=0x7fce7529a520, count=15) at /data/src/10.5/sql/filesort.h:144
#9 0x0000557560df37c8 in save_index (param=0x7fce7529a520, count=15, table_sort=0x7fce40146ce0) at /data/src/10.5/sql/filesort.cc:1387
#10 0x0000557560df0139 in filesort (thd=0x7fce40000b00, table=0x7fce40046a10, filesort=0x7fce7529aa10, tracker=0x7fce40014888, join=0x0, first_table_bit=0) at /data/src/10.5/sql/filesort.cc:324
#11 0x0000557560bd417b in mysql_update (thd=0x7fce40000b00, table_list=0x7fce400135d0, fields=..., values=..., conds=0x0, order_num=1, order=0x7fce40014210, limit=9, ignore=false, found_return=0x7fce7529aff0, updated_return=0x7fce7529b0b0) at /data/src/10.5/sql/sql_update.cc:774
#12 0x0000557560ab624f in mysql_execute_command (thd=0x7fce40000b00) at /data/src/10.5/sql/sql_parse.cc:4359
#13 0x0000557560ac34fa in mysql_parse (thd=0x7fce40000b00, rawbuf=0x7fce400134c8 "UPDATE t1 SET pk = pk+1000 ORDER BY pk LIMIT 9", length=46, parser_state=0x7fce7529b570, is_com_multi=false, is_next_command=false) at /data/src/10.5/sql/sql_parse.cc:7988
#14 0x0000557560aae5b9 in dispatch_command (command=COM_QUERY, thd=0x7fce40000b00, packet=0x7fce40137d61 "UPDATE t1 SET pk = pk+1000 ORDER BY pk LIMIT 9", packet_length=46, is_com_multi=false, is_next_command=false) at /data/src/10.5/sql/sql_parse.cc:1846
#15 0x0000557560aacd13 in do_command (thd=0x7fce40000b00) at /data/src/10.5/sql/sql_parse.cc:1364
#16 0x0000557560c4d2e1 in do_handle_one_connection (connect=0x557563574f20, put_in_cache=true) at /data/src/10.5/sql/sql_connect.cc:1422
#17 0x0000557560c4d010 in handle_one_connection (arg=0x557563574f20) at /data/src/10.5/sql/sql_connect.cc:1319
#18 0x00005575611a8eeb in pfs_spawn_thread (arg=0x5575634cdae0) at /data/src/10.5/storage/perfschema/pfs.cc:1869
#19 0x00007fce7de8f4a4 in start_thread (arg=0x7fce7529c700) at pthread_create.c:456
#20 0x00007fce7bfc3d0f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:97

Non-debug fails in almost the same way, with a slight difference in the stack trace:

#3  <signal handler called>

#4  __memcmp_sse4_1 () at ../sysdeps/x86_64/multiarch/memcmp-sse4.S:915

#5  0x0000559a8c85df37 in my_qsort2 (base_ptr=<optimized out>, count=count@entry=15, size=size@entry=8, cmp=0x559a8c8695f0 <native_compare>, cmp_argument=cmp_argument@entry=0x7f3e22bfb728) at /data/src/10.5/mysys/mf_qsort.c:154

#6  0x0000559a8c4a9fa7 in Filesort_buffer::sort_buffer (this=this@entry=0x7f3de405b530, param=param@entry=0x7f3e22bfb8a0, count=count@entry=15) at /data/src/10.5/sql/filesort_utils.cc:186

#7  0x0000559a8c3440a3 in SORT_INFO::sort_buffer (count=15, param=0x7f3e22bfb8a0, this=0x7f3de405b530) at /data/src/10.5/sql/filesort.h:144

#8  save_index (table_sort=0x7f3de405b530, count=15, param=0x7f3e22bfb8a0) at /data/src/10.5/sql/filesort.cc:1387

#9  filesort (thd=thd@entry=0x7f3de40009a8, table=table@entry=0x7f3de40226e8, filesort=filesort@entry=0x7f3e22bfbc70, tracker=0x7f3de40138e0, join=join@entry=0x0, first_table_bit=first_table_bit@entry=0) at /data/src/10.5/sql/filesort.cc:324

#10 0x0000559a8c20b802 in mysql_update (thd=thd@entry=0x7f3de40009a8, table_list=<optimized out>, fields=..., values=..., conds=<optimized out>, order_num=<optimized out>, order=0x7f3de40131e8, limit=9, ignore=false, found_return=0x7f3e22bfc130, updated_return=0x7f3e22bfc1f0) at /data/src/10.5/sql/sql_update.cc:774

#11 0x0000559a8c170589 in mysql_execute_command (thd=thd@entry=0x7f3de40009a8) at /data/src/10.5/sql/sql_parse.cc:4359

#12 0x0000559a8c172ff9 in mysql_parse (thd=thd@entry=0x7f3de40009a8, rawbuf=<optimized out>, length=46, parser_state=parser_state@entry=0x7f3e22bfc590, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false) at /data/src/10.5/sql/sql_parse.cc:7988

#13 0x0000559a8c1751ea in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x7f3de40009a8, packet=packet@entry=0x7f3de4007cc9 "UPDATE t1 SET pk = pk+1000 ORDER BY pk LIMIT 9", packet_length=packet_length@entry=46, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false) at /data/src/10.5/sql/sql_parse.cc:1846

#14 0x0000559a8c1768a3 in do_command (thd=0x7f3de40009a8) at /data/src/10.5/sql/sql_parse.cc:1364

#15 0x0000559a8c24aae4 in do_handle_one_connection (connect=<optimized out>, connect@entry=0x559a8e7fe838, put_in_cache=put_in_cache@entry=true) at /data/src/10.5/sql/sql_connect.cc:1422

#16 0x0000559a8c24ae22 in handle_one_connection (arg=arg@entry=0x559a8e7fe838) at /data/src/10.5/sql/sql_connect.cc:1319

#17 0x0000559a8c582d24 in pfs_spawn_thread (arg=0x559a8e85cad8) at /data/src/10.5/storage/perfschema/pfs.cc:1869

#18 0x00007f3e2f8314a4 in start_thread (arg=0x7f3e22bfd700) at pthread_create.c:456

#19 0x00007f3e2d965d0f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:97

The failure appeared in 10.5 branch with this commit:

commit f52bf92014efae6a1da9c2f26a7e3792ed5f5396

Author: Varun Gupta

Date:   Tue Jan 21 01:37:47 2020 +0530

    MDEV-21263: Allow packed values of non-sorted fields in the sort buffer

Attachments

Issue Links

relates to

MDEV-21263 Allow packed values of non-sorted fields in the sort buffer

Closed

Server crashes in my_qsort2 / Filesort_buffer::sort_buffer

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration