[MDEV-17409] MariaDB crashed on BETWEEN (opt_range.cc) Created: 2018-10-09  Updated: 2021-12-08

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.3.10, 10.3.11
Fix Version/s: None

Type: Bug Priority: Critical
Reporter: Sebastian Stamm Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: need_verification
Environment:

Windows Server 2012 R2

Attachments: File mysqld.dmp    

 Description    

181009 13:15:36 [ERROR] mysqld got exception 0xc0000005 ;
 
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.
 
 
 
To report this bug, see https://mariadb.com/kb/en/reporting-bugs
 
 
 
We will try our best to scrape up some info that will hopefully help
 
diagnose the problem, but since we have already crashed, 
something is definitely wrong and this may fail.
 
 
 
Server version: 10.3.10-MariaDB
 
key_buffer_size=134217728
 
read_buffer_size=67108864
 
max_used_connections=9
 
max_threads=65537
 
thread_count=15
 
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 266025 K  bytes of memory
 
Hope that's ok; if not, decrease some variables in the equation.
 
 
 
Thread pointer: 0xd8581db018
 
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!Field::eq()[field.h:886]
 
mysqld.exe!Item_bool_func::get_mm_parts()[opt_range.cc:7861]
 
mysqld.exe!Item_bool_func::get_ne_mm_tree()[opt_range.cc:7024]
 
mysqld.exe!Item_func_ne::get_func_mm_tree()[item_cmpfunc.h:828]
 
mysqld.exe!Item_bool_func::get_full_func_mm_tree()[opt_range.cc:7558]
 
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:7613]
 
mysqld.exe!calculate_cond_selectivity_for_table()[opt_range.cc:3078]
 
mysqld.exe!make_join_statistics()[sql_select.cc:4919]
 
mysqld.exe!JOIN::optimize_inner()[sql_select.cc:1896]
 
mysqld.exe!JOIN::optimize()[sql_select.cc:1450]
 
mysqld.exe!mysql_select()[sql_select.cc:4220]
 
mysqld.exe!handle_select()[sql_select.cc:370]
 
mysqld.exe!execute_sqlcom_select()[sql_parse.cc:6547]
 
mysqld.exe!mysql_execute_command()[sql_parse.cc:3769]
 
mysqld.exe!mysql_parse()[sql_parse.cc:8096]
 
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 (0xc177cbc040): SELECT absenceId 
 
 
FROM absence 
 
 
JOIN employee USING(employeeid)
 
 
 
WHERE name='...' AND absenceId <> -1
 
 
 
AND start BETWEEN '2018-12-24' AND '2018-12-24'
 
 
 
OR end BETWEEN '2018-12-24' AND '2018-12-24'
 
Connection ID (thread ID): 8
 
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=off,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
 
 
 
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
 
information that should help you find out what is causing the crash.
 
2018-10-09 13:15:47 28 [ERROR] mysqld.exe: Out of memory (Needed 67104792 bytes)



 Comments   
Comment by Alice Sherepa [ 2018-10-09 ]

Could you please add outut of the 

  SHOW CREATE TABLE absence;
 
  SHOW CREATE TABLE employee;

and your .cnf file(s)

Comment by Sebastian Stamm [ 2018-10-09 ] 

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

Comment by Alice Sherepa [ 2018-10-10 ]

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?

Comment by Vladislav Vaintroub [ 2018-10-11 ]

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.

Comment by Sebastian Stamm [ 2018-10-11 ]

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

Comment by Vladislav Vaintroub [ 2018-10-11 ]

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

Comment by Elena Stepanova [ 2018-11-11 ]

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.

Comment by Sebastian Stamm [ 2018-12-04 ]

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

Comment by Alice Sherepa [ 2019-01-07 ]

Do this tables contain confidential information? Would you be able to upload datadump of them to ftp.askmonty.org/private?

Comment by Elena Stepanova [ 2019-02-14 ]

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?

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