Alice Sherepa added a comment - 2018-10-09 11:28 Could you please add outut of the SHOW CREATE TABLE absence; SHOW CREATE TABLE employee; and your .cnf file(s)

Sebastian Stamm added a comment - 2018-10-09 11:33 - edited CREATE TABLE `absence` ( `absenceId` int (10) NOT NULL AUTO_INCREMENT, `employeeId` int (10) NOT NULL , `start` date NOT NULL , ` end ` date NOT NULL , `absenceReasonId` int (11) NOT NULL , PRIMARY KEY (`absenceId`), KEY `FK_absence_employee` (`employeeId`), KEY `FK_absence_absencereason` (`absenceReasonId`), CONSTRAINT `FK_absence_absencereason` FOREIGN KEY (`absenceReasonId`) REFERENCES `absencereason` (`absenceReasonId`), CONSTRAINT `FK_absence_employee` FOREIGN KEY (`employeeId`) REFERENCES `employee` (`employeeId`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=10677 DEFAULT CHARSET=utf8 COMMENT= 'Absences of the employees.'   CREATE TABLE `employee` ( `employeeId` int (11) NOT NULL AUTO_INCREMENT, ` name ` varchar (50) NOT NULL , `exportName` varchar (50) DEFAULT NULL , `availability` decimal (3,1) NOT NULL , `skillId` int (11) NOT NULL DEFAULT 0, `unitId` int (11) NOT NULL DEFAULT 0, `locationId` int (11) NOT NULL DEFAULT 0, `departmentId` int (11) NOT NULL DEFAULT 0, `roles` set ( 'ADMIN' , 'EMPLOYEE' , 'PMO' , 'GPMO' , 'PROJECT_MANAGER' , 'LINE_MANAGER' ) DEFAULT 'EMPLOYEE' , `lastRole` enum( 'ADMIN' , 'EMPLOYEE' , 'PMO' , 'GPMO' , 'PROJECT_MANAGER' , 'LINE_MANAGER' ) DEFAULT 'EMPLOYEE' , `previousLogin` timestamp NULL DEFAULT NULL , `currentLogin` timestamp NOT NULL DEFAULT current_timestamp (), `type` enum( 'DEFAULT' , 'EXTERNAL' , 'STUDENT' , 'GENERIC' ) NOT NULL DEFAULT 'DEFAULT' , PRIMARY KEY (`employeeId`), UNIQUE KEY ` name ` (` name `), UNIQUE KEY `exportName` (`exportName`), KEY `FK_employee_department` (`departmentId`), KEY `FK_employee_location` (`locationId`), KEY `FK_employee_unit` (`unitId`), KEY `FK_employee_skill` (`skillId`), CONSTRAINT `FK_employee_department` FOREIGN KEY (`departmentId`) REFERENCES `department` (`departmentId`), CONSTRAINT `FK_employee_location` FOREIGN KEY (`locationId`) REFERENCES `location` (`locationId`), CONSTRAINT `FK_employee_skill` FOREIGN KEY (`skillId`) REFERENCES `skill` (`skillId`), CONSTRAINT `FK_employee_unit` FOREIGN KEY (`unitId`) REFERENCES `unit` (`unitId`) ) ENGINE=InnoDB AUTO_INCREMENT=1266 DEFAULT CHARSET=utf8   [client] port= 1433 plugin-dir=D:/MariaDB 10.3 /lib/plugin   [mysql] no-auto-rehash default -character-set=utf8   [mysqld] port= 1433     datadir=D:/MariaDB 10.3 /data tmpdir=D:/MariaDB 10.3 /temp   default -storage-engine=innodb sql-mode= "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" skip_name_resolve   max_connections= 200 max_connect_errors= 50   #query_cache_type = OFF query_cache_size=16M   tmp_table_size=2G max_heap_table_size=2G   max_allowed_packet=64M group_concat_max_len = 32768 table_definition_cache= 2048     sort_buffer_size=64M read_buffer_size=64M   mrr_buffer_size=16M   join_cache_level= 8 join_buffer_size=32M join_buffer_space_limit=64M   use_stat_tables=complementary optimizer_use_condition_selectivity= 4 histogram_size= 255   optimizer_switch = "mrr=on" optimizer_switch = "mrr_cost_based=on" optimizer_switch = "mrr_sort_keys=on" optimizer_switch = "optimize_join_buffer_size=on"     #*** INNODB Specific options ***   innodb_buffer_pool_size=160G innodb_buffer_pool_instances = 8 innodb-page-cleaners= 8   innodb_sort_buffer_size=16M   innodb_log_file_size=512M innodb_log_buffer_size=64M innodb_log_files_in_group = 4 innodb_log_compressed_pages= 1   innodb_flush_log_at_trx_commit= 0 innodb_adaptive_flushing= 1 innodb_flush_neighbors= 0     innodb_file_per_table= 1   innodb_read_io_threads= 32 innodb_write_io_threads= 24   innodb_adaptive_hash_index= 1   innodb_lru_scan_depth= 2048   innodb_io_capacity = 100000   innodb_max_dirty_pages_pct = 60 innodb_lock_wait_timeout = 120   innodb_doublewrite = 0   innodb_autoinc_lock_mode= 2   [mysqldump] quick max_allowed_packet = 32M Thanks, Sebastian

Alice Sherepa added a comment - 2018-10-10 09:56 I can not reproduce it right away. Could you please describe the crash more, was it only once or is it a repeatable one, how big are your tables, etc.? Or maybe you have the test case to demonstrate the problem?

Vladislav Vaintroub added a comment - 2018-10-11 15:03 sstamm , if you can reproduce that on your machine, reliably, maybe you can set core-file=1 in [mysqld] section of my.ini , and provide crashdump mysqld.dmp located in datadirectory.

Sebastian Stamm added a comment - 2018-10-11 15:27 - edited I can reproduce it on my machine, but I was not able to create a small example to re-produce it. Related to the size, the AUTO_INCREMENTs are matching. And I've attached the dump. mysqld.dmp Thanks, Sebastian

Vladislav Vaintroub added a comment - 2018-10-11 16:38 - edited Thanks for the dump Note : debugger informs that there was a null pointer access in the below code SEL_ARG * Item_bool_func::get_mm_leaf(RANGE_OPT_PARAM *param, Field *field, KEY_PART *key_part, Item_func::Functype type, Item *value) { uint maybe_null=(uint) field->real_maybe_null(); <-- here, field is NULL pointer SEL_ARG *tree= 0; the key itself is being passed as second parameter key_part->field sel_arg= get_mm_leaf(param, key_part->field, key_part, type, value); inside Item_bool_func::get_mm_parts(RANGE_OPT_PARAM *param, Field *field, Item_func::Functype type, Item *value) Unfortunately, the dump does not contain heap, so it is not possible to look into the affected key_part

Elena Stepanova added a comment - 2018-11-11 23:47 sstamm , Are you sure you haven't over-edited the query? Obfuscating names and other sensible values is of course fine, but the structure should remain intact. In its current form, it just doesn't look plausible. Could it be that you lost brackets and/or some clauses? After checking it, please also provide the output of EXPLAIN EXTENDED <the correct query>; SHOW WARNINGS; and SHOW INDEX IN employee; SHOW INDEX IN absence; Thanks.

Sebastian Stamm added a comment - 2018-12-04 09:24 - edited switched to 10.3.11: EXPLAIN EXTENDED also crashes MariaDB. Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment employee 0 PRIMARY 1 employeeId A 718       BTREE     employee 0 name 1 name A 718       BTREE     employee 0 exportName 1 exportName A 2     YES BTREE     employee 1 FK_employee_department 1 departmentId A 179       BTREE     employee 1 FK_employee_location 1 locationId A 22       BTREE     employee 1 FK_employee_unit 1 unitId A 20       BTREE     employee 1 FK_employee_skill 1 skillId A 79       BTREE     Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment absence 0 PRIMARY 1 absenceId A 7715       BTREE     absence 1 FK_absence_employee 1 employeeId A 964       BTREE     absence 1 FK_absence_absencereason 1 absenceReasonId A 8       BTREE     Query is: SELECT absenceId FROM absence JOIN employee USING(employeeid) WHERE name = 'Test, Test' AND START <= '2018-12-04' AND END >= '2018-12-04' AND absenceId != 8711 Problem causes 'absenceId != 8711' When i remove this, the query works. Returns only the row with 8711, with the condition, no rows are expected. Error with 10.3.11; Server version: 10.3 . 11 -MariaDB key_buffer_size= 134217728 read_buffer_size= 67108864 max_used_connections= 13 max_threads= 65537 thread_count= 9 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 266027 K bytes of memory Hope that's ok; if not, decrease some variables in the equation.   Thread pointer: 0x53dc6c4038 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... mysqld.exe!Item_bool_func::get_mm_leaf()[opt_range.cc: 8047 ] mysqld.exe!Item_bool_func::get_mm_parts()[opt_range.cc: 7886 ] mysqld.exe!Item_bool_func2_with_rev::get_func_mm_tree()[item_cmpfunc.h: 461 ] mysqld.exe!Item_bool_func::get_full_func_mm_tree()[opt_range.cc: 7564 ] mysqld.exe!Item_bool_func::get_full_func_mm_tree_for_args()[item_cmpfunc.h: 201 ] mysqld.exe!Item_bool_func2_with_rev::get_mm_tree()[item_cmpfunc.h: 488 ] mysqld.exe!Item_cond_and::get_mm_tree()[opt_range.cc: 7619 ] mysqld.exe!calculate_cond_selectivity_for_table()[opt_range.cc: 3078 ] mysqld.exe!make_join_statistics()[sql_select.cc: 5000 ] mysqld.exe!JOIN::optimize_inner()[sql_select.cc: 1889 ] mysqld.exe!JOIN::optimize()[sql_select.cc: 1453 ] mysqld.exe!mysql_select()[sql_select.cc: 4225 ] mysqld.exe!mysql_explain_union()[sql_select.cc: 25868 ] mysqld.exe!execute_sqlcom_select()[sql_parse.cc: 6485 ] mysqld.exe!mysql_execute_command()[sql_parse.cc: 3767 ] mysqld.exe!mysql_parse()[sql_parse.cc: 8095 ] mysqld.exe!dispatch_command()[sql_parse.cc: 1852 ] mysqld.exe!do_command()[sql_parse.cc: 1394 ] mysqld.exe!threadpool_process_request()[threadpool_common.cc: 358 ] mysqld.exe!tp_callback()[threadpool_common.cc: 186 ] ntdll.dll!RtlFreeUnicodeString() ntdll.dll!RtlFreeUnicodeString() KERNEL32.DLL!BaseThreadInitThunk() ntdll.dll!RtlUserThreadStart()   Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query ( 0x70fee64030 ): EXPLAIN EXTENDED   SELECT absenceId   FROM absence   JOIN employee USING(employeeid)   WHERE name= 'Test, Test' AND START <= '2018-12-04' AND END >= '2018-12-04' AND absenceId != 8711 Connection ID (thread ID): 416 Status: NOT_KILLED   Optimizer switch : index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=on,mrr_sort_keys=on,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on

Alice Sherepa added a comment - 2019-01-07 13:14 - edited Do this tables contain confidential information? Would you be able to upload datadump of them to ftp.askmonty.org/private?

Elena Stepanova added a comment - 2019-02-14 14:12 alice , it looks much like MDEV-18300 , except that I don't see any BLOBs here; but possibly with other configuration options mentioned here the test case from MDEV-18300 can be tweaked to avoid blobs?