[MDEV-16399] Spider Crashes Server After Multiple INSERT SELECTS Created: 2018-06-05  Updated: 2019-04-30  Resolved: 2019-04-30

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - Spider
Affects Version/s: 10.3.7
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Adam Hilton Assignee: Kentoku Shiba (Inactive)
Resolution: Cannot Reproduce Votes: 0
Labels: None
Environment:

Windows Server 2012 R2 64-bit



 Description   

We use php to iterate through what we call PointIDs under SiteIDs to generate a sub-sampled table called `trendrecord_extract` (InnoDB) from a spider table called tr. In 10.2.14 this query worked fine completing all necessary iterations SiteIDs and PointIDs without a problem. In 10.3.7 the query now results in a server crash consistently part-way through the php loop resulting in the following error log output. To be clear, the queries we sending from php will iterate through specific SiteID and PointID combinations utilizing the same connection and be fine for the first couple iterations and then consistently crash; we had no such issue in 10.2.14. There is no specific SiteID-PointID combination that results in the crash. The following query that appears in the error below was executed fine after the server was brought back online.

180604 16:54:00 [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.7-MariaDB-log
key_buffer_size=33554432
read_buffer_size=2097152
max_used_connections=84
max_threads=65586
thread_count=69
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 246248 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
 
Thread pointer: 0x3244c6f438
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...
ha_spider.dll!spider_mysql_handler::append_list_item_select()[spd_db_mysql.cc:12
824]
ha_spider.dll!spider_group_by_handler::init_scan()[spd_group_by_handler.cc:1264]
 
mysqld.exe!Pushdown_query::execute()[group_by_handler.cc:49]
mysqld.exe!do_select()[sql_select.cc:18715]
mysqld.exe!JOIN::exec_inner()[sql_select.cc:4014]
mysqld.exe!JOIN::exec()[sql_select.cc:3807]
mysqld.exe!mysql_select()[sql_select.cc:4213]
mysqld.exe!handle_select()[sql_select.cc:370]
mysqld.exe!mysql_execute_command()[sql_parse.cc:4832]
mysqld.exe!mysql_parse()[sql_parse.cc:8025]
mysqld.exe!dispatch_command()[sql_parse.cc:1848]
mysqld.exe!do_command()[sql_parse.cc:1390]
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 (0x3259b0ff90): insert ignore into trendrecord_extract
                        (select SiteID,PointID,cast(concat(tr.datevalue,' ',tr.t
imevalue) as datetime) AS DateTime,rdValue,NumericValue,now() NewRecord
                                from tr
                                WHERE SiteID=30 AND datevalue > date_add(now(),
interval -10 day)
                                and pointid=57215 AND SourceID <> 11)
Connection ID (thread ID): 2464
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_p
ushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,fi
rstmatch=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_c
ost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,
join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_b
uffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_use
s_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.



 Comments   
Comment by Kentoku Shiba (Inactive) [ 2019-04-29 ]

adamh
I tried to reproduce this issue by using multiple queries that is like in the error log. (on MariaDB 10.3.7) But I could not reproduce this issue. Do you have more detail about reproducing this?

Comment by Adam Hilton [ 2019-04-29 ]

Kentoku

I do not. The only thing unique about this operation, compared to others that we routinely do, was the fact that it was INSERT-SELECT happening in quick succession by the same connection. We have been in 10.2 since running into this issue. I still wouldn't call this repeatable though, so you're welcome to close this issue for now. I'll re-open it if/when we move to 10.3 and experience it again. - Thanks

Comment by Kentoku Shiba (Inactive) [ 2019-04-30 ]

Thanks. It's closed for now. Please reopen when it is reproduce.

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