[MDEV-17594] MariaDB keeps crashing on a simple query Created: 2018-11-01  Updated: 2018-11-02  Resolved: 2018-11-02

Status: Closed
Project: MariaDB Server
Component/s: Partitioning
Affects Version/s: 10.3.10
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Kazem Yalpour Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: None

Issue Links:
Duplicate
is duplicated by MDEV-16241 Assertion `inited==RND' failed in ha... Closed

 Description   

MariaDB keeps crashing on a simple query :
SELECT tb3.ID FROM tb3 INNER JOIN tb1 on(tb1.ID = tb3.ID) WHERE `CDate` >= '2018-08-03' AND `RID` = 1234 AND `UID` = 123

But if i remove `CDate` condition from the query it execute successfully.

This is the error when MariaDB crashes :

181101 20:32:46 [ERROR] mysqld got signal 11 ;
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.

Server version: 10.3.10-MariaDB-1:10.3.10+maria~stretch
key_buffer_size=134217728
read_buffer_size=8388608
max_used_connections=1
max_threads=153
thread_count=7
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 6401350 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7f71e80009a8
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...
stack_bottom = 0x7f7ad00b2cf8 thread_stack 0x30000
/usr/sbin/mysqld(my_print_stacktrace+0x2e)[0x564b08a038fe]
/usr/sbin/mysqld(handle_fatal_signal+0x41f)[0x564b084b447f]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x110c0)[0x7f7ad85d90c0]
/usr/sbin/mysqld(+0x8d269d)[0x564b0863969d]
/usr/sbin/mysqld(+0x8d2fe7)[0x564b08639fe7]
/usr/sbin/mysqld(_ZN10DsMrr_impl10dsmrr_initEP7handlerP15st_range_seq_ifPvjjP17st_handler_buffer+0x4a3)[0x564b083e1b73]
/usr/sbin/mysqld(+0x8c4338)[0x564b0862b338]
/usr/sbin/mysqld(+0xc5bfc6)[0x564b089c2fc6]
/usr/sbin/mysqld(_ZN18QUICK_RANGE_SELECT5resetEv+0xe4)[0x564b085af124]
/usr/sbin/mysqld(_ZN26QUICK_ROR_INTERSECT_SELECT5resetEv+0x39)[0x564b085ae6d9]
/usr/sbin/mysqld(_Z21join_init_read_recordP13st_join_table+0x44)[0x564b0831dc94]
/usr/sbin/mysqld(_Z10sub_selectP4JOINP13st_join_tableb+0x118)[0x564b0830f928]
/usr/sbin/mysqld(_ZN4JOIN10exec_innerEv+0xa49)[0x564b0832e849]
/usr/sbin/mysqld(_ZN4JOIN4execEv+0x33)[0x564b0832ea83]
/usr/sbin/mysqld(_Z12mysql_selectP3THDP10TABLE_LISTjR4ListI4ItemEPS4_jP8st_orderS9_S7_S9_yP13select_resultP18st_select_lex_unitP13st_select_lex+0xef)[0x564b0832ebcf]
/usr/sbin/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0xf8)[0x564b0832f4f8]
/usr/sbin/mysqld(+0x48a89b)[0x564b081f189b]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x6ca5)[0x564b082dcf85]
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_statebb+0x21a)[0x564b082df11a]
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcjbb+0x1c81)[0x564b082e1b21]
/usr/sbin/mysqld(_Z10do_commandP3THD+0x131)[0x564b082e2801]
/usr/sbin/mysqld(_Z24do_handle_one_connectionP7CONNECT+0x25a)[0x564b083b2f9a]
/usr/sbin/mysqld(handle_one_connection+0x3d)[0x564b083b311d]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x7494)[0x7f7ad85cf494]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x3f)[0x7f7ad6b29acf]

 Comments   
Comment by Elena Stepanova [ 2018-11-01 ]

Could you please provide the output of 

SHOW CREATE TABLE tb1;
 
SHOW INDEX IN tb1;
 
SHOW CREATE TABLE tb3;
 
SHOW INDEX IN tb3;

and attach your cnf file(s)?

Comment by Kazem Yalpour [ 2018-11-02 ]

Thanks Elena.

I found out the problem will be resolve if i remove partitioning from `tb1` table.

This is tables structure :

CREATE TABLE `tb1` (
`ID` int(11) NOT NULL,
`Ftb1` varchar(1000) COLLATE utf8_persian_ci DEFAULT NULL,
`Ftb2` int(11) DEFAULT NULL,
`Ftb3` int(11) DEFAULT '-1',
`CDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`RID` int(11) DEFAULT NULL,
`UID` int(11) DEFAULT NULL,
`Ftb4` int(11) NOT NULL DEFAULT '0',
`Ftb5` varchar(2000) COLLATE utf8_persian_ci DEFAULT NULL,
`Ftb6` int(4) NOT NULL DEFAULT '0',
`Ftb7` int(11) DEFAULT '0',
`Ftb8` int(11) DEFAULT '0',
`Ftb9` varchar(140) COLLATE utf8_persian_ci DEFAULT NULL,
`Ftb10` int(4) NOT NULL DEFAULT '1',
`Ftb11` varchar(20) COLLATE utf8_persian_ci DEFAULT NULL,
`Ftb12` text COLLATE utf8_persian_ci,
`Ftb13` varchar(50) COLLATE utf8_persian_ci DEFAULT NULL,
`Ftb14` int(1) NOT NULL DEFAULT '0' COMMENT '0=one signer, 1=multi signers'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci
PARTITION BY RANGE COLUMNS(CDate)
(
PARTITION tb1_p1 VALUES LESS THAN ('2000-03-21') ENGINE=InnoDB,
PARTITION tb1_p2 VALUES LESS THAN ('2006-03-20') ENGINE=InnoDB,
PARTITION tb1_p3 VALUES LESS THAN ('2008-03-21') ENGINE=InnoDB,
PARTITION tb1_p4 VALUES LESS THAN ('2010-03-20') ENGINE=InnoDB,
PARTITION tb1_p5 VALUES LESS THAN ('2012-03-21') ENGINE=InnoDB,
PARTITION tb1_p6 VALUES LESS THAN ('2014-03-20') ENGINE=InnoDB,
PARTITION tb1_p7 VALUES LESS THAN ('2015-03-21') ENGINE=InnoDB,
PARTITION tb1_p8 VALUES LESS THAN ('2016-03-20') ENGINE=InnoDB,
PARTITION tb1_p9 VALUES LESS THAN ('2017-03-21') ENGINE=InnoDB,
PARTITION tb1_p10 VALUES LESS THAN ('2018-03-21') ENGINE=InnoDB,
PARTITION tb1_p11 VALUES LESS THAN ('2019-03-21') ENGINE=InnoDB,
PARTITION tb1_p12 VALUES LESS THAN ('2020-03-20') ENGINE=InnoDB,
PARTITION tb1_p1000 VALUES LESS THAN ('2100-03-21') ENGINE=InnoDB
);

– Indexes for table `tb1`
–
ALTER TABLE `tb1`
ADD PRIMARY KEY (`ID`,`CDate`),
ADD KEY `Ftb2` (`Ftb2`),
ADD KEY `RID` (`RID`),
ADD KEY `UID` (`UID`),
ADD KEY `Ftb12` (`Ftb12`(255)),
ADD KEY `Ftb1` (`Ftb1`(255)),
ADD KEY `Ftb5` (`Ftb5`(255)),
ADD KEY `Ftb10` (`Ftb10`),
ADD KEY `Ftb6` (`Ftb6`),
ADD KEY `Ftb4` (`Ftb4`),
ADD KEY `CDate` (`CDate`),
ADD KEY `Ftb3` (`Ftb3`),
ADD KEY `ID` (`ID`,`Ftb10`,`Ftb4`,`Ftb6`);

---------------------------------------------

CREATE TABLE `tb3` (
`ID` int(11) NOT NULL,
`DID` int(11) DEFAULT NULL,
`LID` int(11) DEFAULT NULL,
`Finished` tinyint(1) NOT NULL DEFAULT 0,
`Subject` varchar(256) COLLATE utf8_persian_ci DEFAULT NULL,
`Note` tinytext COLLATE utf8_persian_ci DEFAULT NULL,
`FullTextField` text COLLATE utf8_persian_ci NOT NULL,
`Field_1` date DEFAULT NULL ,
`Field_2` date DEFAULT NULL ,
`Field_4` float DEFAULT NULL ,
`Field_5` smallint(5) DEFAULT NULL ,
`Field_6` varchar(20) COLLATE utf8_persian_ci DEFAULT NULL ,
`Field_7` varchar(100) COLLATE utf8_persian_ci DEFAULT NULL ,
`Field_9` varchar(500) COLLATE utf8_persian_ci DEFAULT NULL ,
`Field_10` varchar(500) COLLATE utf8_persian_ci DEFAULT NULL ,
`Field_13` varchar(500) COLLATE utf8_persian_ci DEFAULT NULL ,
`Field_15` varchar(255) COLLATE utf8_persian_ci DEFAULT NULL ,
`Field_16` varchar(255) COLLATE utf8_persian_ci DEFAULT NULL ,
`Field_17` varchar(255) COLLATE utf8_persian_ci DEFAULT NULL ,
`Field_18` varchar(255) COLLATE utf8_persian_ci DEFAULT NULL ,
`Field_20` tinyint(1) DEFAULT NULL ,
`Field_21` tinyint(1) DEFAULT NULL ,
`CounterField` varchar(50) COLLATE utf8_persian_ci DEFAULT NULL,
`Field_26` date DEFAULT NULL ,
`Field_27` varchar(255) COLLATE utf8_persian_ci DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci ROW_FORMAT=COMPACT;

– Indexes for table `tb3`
–
ALTER TABLE `tb3`
ADD PRIMARY KEY (`ID`),
ADD KEY `LID` (`LID`),
ADD KEY `Field_1` (`Field_1`),
ADD KEY `Field_2` (`Field_2`),
ADD KEY `Field_4` (`Field_4`),
ADD KEY `Field_5` (`Field_5`),
ADD KEY `DID` (`DID`),
ADD KEY `Field_6` (`Field_6`);
ALTER TABLE `tb3` ADD FULLTEXT KEY `FullTextField` (`FullTextField`);

Comment by Elena Stepanova [ 2018-11-02 ]

Thanks for the information, I was able to reproduce it.
It appears to be the same problem as MDEV-16241. Since it has some extra information and is close to the resolution already, I suggest to keep tracking the problem there, if it's okay with you. I will add your use case to MDEV-16241.

--source include/have_partition.inc
 
--source include/have_innodb.inc
 
 
 
CREATE TABLE `tb1` (
 
  `ID` int(11) NOT NULL,
 
  `CDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 
  `RID` int(11) DEFAULT NULL,
 
  `UID` int(11) DEFAULT NULL,
 
  PRIMARY KEY (`ID`,`CDate`),
 
  KEY `RID` (`RID`),
 
  KEY `UID` (`UID`),
 
  KEY `ID` (`ID`)
 
) ENGINE=InnoDB
 
 PARTITION BY RANGE  COLUMNS(`CDate`)
 
(PARTITION `tb1_p1` VALUES LESS THAN ('2000-03-21') ENGINE = InnoDB,
 
 PARTITION `tb1_p12` VALUES LESS THAN ('2020-03-20') ENGINE = InnoDB,
 
 PARTITION `tb1_p1000` VALUES LESS THAN ('2100-03-21') ENGINE = InnoDB);
 
INSERT INTO `tb1` VALUES (1,'2033-05-03 01:23:33',9,NULL),(2,'2026-07-05 20:39:29',NULL,NULL);
 
 
 
CREATE TABLE `tb3` (
 
  `ID` int(11) NOT NULL,
 
  `DID` int(11) DEFAULT NULL,
 
  `LID` int(11) DEFAULT NULL
 
) ENGINE=MyISAM;
 
 
 
INSERT INTO `tb3` VALUES (3,NULL,NULL),(4,NULL,1707212800);
 
 
 
SELECT tb3.ID FROM tb3 INNER JOIN tb1 on(tb1.ID = tb3.ID) WHERE `CDate` >= '2018-08-03' AND `RID` = 1234 AND `UID` = 123;
 
 
 
# Cleanup
 
DROP TABLE tb1, tb3;

10.3 debug a33c0e3f34af

 
mysqld: /data/src/10.3/sql/handler.h:3096: int handler::ha_rnd_end(): Assertion `inited==RND' failed.
 
181102 18:47:33 [ERROR] mysqld got signal 6 ;
 
 
 
#7  0x00007fe505542ee2 in __assert_fail () from /lib/x86_64-linux-gnu/libc.so.6
 
#8  0x000055d77af0f61a in handler::ha_rnd_end (this=0x7fe4a80a7bf8) at /data/src/10.3/sql/handler.h:3096
 
#9  0x000055d77b1591bc in DsMrr_impl::dsmrr_init (this=0x7fe4a80a8040, h_arg=0x7fe4a80a7bf8, seq_funcs=0x7fe4a80a7b30, seq_init_param=0x7fe4a8096f50, n_ranges=1, mode=72, buf=0x7fe4a8096eb8) at /data/src/10.3/sql/multi_range_read.cc:1016
 
#10 0x000055d77b5d212a in ha_innobase::multi_range_read_init (this=0x7fe4a80a7bf8, seq=0x7fe4a80a7b30, seq_init_param=0x7fe4a8096f50, n_ranges=1, mode=72, buf=0x7fe4a8096eb8) at /data/src/10.3/storage/innobase/handler/ha_innodb.cc:20268
 
#11 0x000055d77ba04030 in ha_partition::multi_range_read_init (this=0x7fe4a80a7348, seq=0x7fe4fa885bd0, seq_init_param=0x7fe4a8049720, n_ranges=1, mrr_mode=72, buf=0x7fe4fa885b90) at /data/src/10.3/sql/ha_partition.cc:6440
 
#12 0x000055d77b40d679 in QUICK_RANGE_SELECT::reset (this=0x7fe4a8049720) at /data/src/10.3/sql/opt_range.cc:11432
 
#13 0x000055d77b3f61ff in QUICK_ROR_INTERSECT_SELECT::reset (this=0x7fe4a8048600) at /data/src/10.3/sql/opt_range.cc:1650
 
#14 0x000055d77b01ea86 in join_init_read_record (tab=0x7fe4a8049a48) at /data/src/10.3/sql/sql_select.cc:20220
 
#15 0x000055d77b01c884 in sub_select (join=0x7fe4a80173a0, join_tab=0x7fe4a8049a48, end_of_records=false) at /data/src/10.3/sql/sql_select.cc:19300
 
#16 0x000055d77b01be20 in do_select (join=0x7fe4a80173a0, procedure=0x0) at /data/src/10.3/sql/sql_select.cc:18843
 
#17 0x000055d77aff4c2d in JOIN::exec_inner (this=0x7fe4a80173a0) at /data/src/10.3/sql/sql_select.cc:4035
 
#18 0x000055d77aff409e in JOIN::exec (this=0x7fe4a80173a0) at /data/src/10.3/sql/sql_select.cc:3829
 
#19 0x000055d77aff530e in mysql_select (thd=0x7fe4a8000b00, tables=0x7fe4a8014fe8, wild_num=0, fields=..., conds=0x7fe4a8016e30, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fe4a8017378, unit=0x7fe4a80049b0, select_lex=0x7fe4a8005120) at /data/src/10.3/sql/sql_select.cc:4234
 
#20 0x000055d77afe71ec in handle_select (thd=0x7fe4a8000b00, lex=0x7fe4a80048e8, result=0x7fe4a8017378, setup_tables_done_option=0) at /data/src/10.3/sql/sql_select.cc:382
 
#21 0x000055d77afb1ddb in execute_sqlcom_select (thd=0x7fe4a8000b00, all_tables=0x7fe4a8014fe8) at /data/src/10.3/sql/sql_parse.cc:6547
 
#22 0x000055d77afa845d in mysql_execute_command (thd=0x7fe4a8000b00) at /data/src/10.3/sql/sql_parse.cc:3769
 
#23 0x000055d77afb5d9d in mysql_parse (thd=0x7fe4a8000b00, rawbuf=0x7fe4a8014d18 "SELECT tb3.ID FROM tb3 INNER JOIN tb1 on(tb1.ID = tb3.ID) WHERE `CDate` >= '2018-08-03' AND `RID` = 1234 AND `UID` = 123", length=120, parser_state=0x7fe4fa8875f0, is_com_multi=false, is_next_command=false) at /data/src/10.3/sql/sql_parse.cc:8091
 
#24 0x000055d77afa2f6b in dispatch_command (command=COM_QUERY, thd=0x7fe4a8000b00, packet=0x7fe4a81670d1 "", packet_length=120, is_com_multi=false, is_next_command=false) at /data/src/10.3/sql/sql_parse.cc:1850
 
#25 0x000055d77afa198f in do_command (thd=0x7fe4a8000b00) at /data/src/10.3/sql/sql_parse.cc:1395
 
#26 0x000055d77b10919c in do_handle_one_connection (connect=0x55d77d7cd560) at /data/src/10.3/sql/sql_connect.cc:1402
 
#27 0x000055d77b108f20 in handle_one_connection (arg=0x55d77d7cd560) at /data/src/10.3/sql/sql_connect.cc:1308
 
#28 0x000055d77b59c0df in pfs_spawn_thread (arg=0x55d77d7d5890) at /data/src/10.3/storage/perfschema/pfs.cc:1862
 
#29 0x00007fe507219494 in start_thread (arg=0x7fe4fa888700) at pthread_create.c:333
 
#30 0x00007fe5055ff93f in clone () from /lib/x86_64-linux-gnu/libc.so.6

10.3 non-debug ASAN 49d506cd1d7

 
==27126==ERROR: AddressSanitizer: SEGV on unknown address 0x6253232cac48 (pc 0x55fa6a444976 sp 0x7f2e88c4b580 bp 0x0000bebebebe T27)
 
    #0 0x55fa6a444975 in ha_innobase::innobase_get_index(unsigned int) /data/src/10.3/storage/innobase/handler/ha_innodb.cc:9392
 
    #1 0x55fa6a4466cf in ha_innobase::change_active_index(unsigned int) /data/src/10.3/storage/innobase/handler/ha_innodb.cc:9427
 
    #2 0x55fa69ade790 in handler::ha_index_init(unsigned int, bool) /data/src/10.3/sql/handler.h:3027
 
    #3 0x55fa69ade790 in DsMrr_impl::dsmrr_init(handler*, st_range_seq_if*, void*, unsigned int, unsigned int, st_handler_buffer*) /data/src/10.3/sql/multi_range_read.cc:1016
 
    #4 0x55fa6a41fb93 in ha_innobase::multi_range_read_init(st_range_seq_if*, void*, unsigned int, unsigned int, st_handler_buffer*) /data/src/10.3/storage/innobase/handler/ha_innodb.cc:20316
 
    #5 0x55fa6ab6f7ca in ha_partition::multi_range_read_init(st_range_seq_if*, void*, unsigned int, unsigned int, st_handler_buffer*) /data/src/10.3/sql/ha_partition.cc:6440
 
    #6 0x55fa6a0ae546 in QUICK_RANGE_SELECT::reset() /data/src/10.3/sql/opt_range.cc:11411
 
    #7 0x55fa6a0acf98 in QUICK_ROR_INTERSECT_SELECT::reset() /data/src/10.3/sql/opt_range.cc:1650
 
    #8 0x55fa6987967b in join_init_read_record(st_join_table*) /data/src/10.3/sql/sql_select.cc:20207
 
    #9 0x55fa6983f5a4 in sub_select(JOIN*, st_join_table*, bool) /data/src/10.3/sql/sql_select.cc:19287
 
    #10 0x55fa698badf8 in do_select /data/src/10.3/sql/sql_select.cc:18830
 
    #11 0x55fa698badf8 in JOIN::exec_inner() /data/src/10.3/sql/sql_select.cc:4035
 
    #12 0x55fa698bbb03 in JOIN::exec() /data/src/10.3/sql/sql_select.cc:3829
 
    #13 0x55fa698bbf02 in mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) /data/src/10.3/sql/sql_select.cc:4234
 
    #14 0x55fa698be88b in handle_select(THD*, LEX*, select_result*, unsigned long) /data/src/10.3/sql/sql_select.cc:382
 
    #15 0x55fa694f46a4 in execute_sqlcom_select /data/src/10.3/sql/sql_parse.cc:6547
 
    #16 0x55fa697aa642 in mysql_execute_command(THD*) /data/src/10.3/sql/sql_parse.cc:3769
 
    #17 0x55fa697b2cb8 in mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool) /data/src/10.3/sql/sql_parse.cc:8089
 
    #18 0x55fa697b9cc7 in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool) /data/src/10.3/sql/sql_parse.cc:1850
 
    #19 0x55fa697bbe26 in do_command(THD*) /data/src/10.3/sql/sql_parse.cc:1395
 
    #20 0x55fa69a61287 in do_handle_one_connection(CONNECT*) /data/src/10.3/sql/sql_connect.cc:1402
 
    #21 0x55fa69a6178a in handle_one_connection /data/src/10.3/sql/sql_connect.cc:1308
 
    #22 0x55fa6a3f3d26 in pfs_spawn_thread /data/src/10.3/storage/perfschema/pfs.cc:1862
 
    #23 0x7f2e943ca493 in start_thread (/lib/x86_64-linux-gnu/libpthread.so.0+0x7493)
 
    #24 0x7f2e927b093e in __clone (/lib/x86_64-linux-gnu/libc.so.6+0xe893e)

Generated at Thu Feb 08 08:37:39 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.