| Bug #74985 | 5.7 easily hits ERROR 1436 (HY000): Thread stack overrun errors on many expr's | ||
|---|---|---|---|
| Submitted: | 24 Nov 2014 16:56 | Modified: | 17 Apr 2015 14:54 |
| Reporter: | Shane Bester (Platinum Quality Contributor) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.7.5 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[24 Nov 2014 17:22]
MySQL Verification Team
Problem is PTI_expr_or::itemize seems recursive: > mysqld-debug.exe!check_stack_overrun(THD * thd, long margin, unsigned char * buf) Line 5041 C++ mysqld-debug.exe!Parse_tree_node::contextualize(Parse_context * pc) Line 42 C++ mysqld-debug.exe!Item::itemize(Parse_context * pc, Item * * res) Line 594 C++ mysqld-debug.exe!PTI_expr_or::itemize(Parse_context * pc, Item * * res) Line 192 C++ mysqld-debug.exe!PTI_expr_or::itemize(Parse_context * pc, Item * * res) Line 192 C++ mysqld-debug.exe!PTI_expr_or::itemize(Parse_context * pc, Item * * res) Line 192 C++ <cut> mysqld-debug.exe!PTI_expr_or::itemize(Parse_context * pc, Item * * res) Line 192 C++ mysqld-debug.exe!PTI_expr_or::itemize(Parse_context * pc, Item * * res) Line 192 C++ mysqld-debug.exe!PTI_expr_or::itemize(Parse_context * pc, Item * * res) Line 192 C++ mysqld-debug.exe!PTI_expr_or::itemize(Parse_context * pc, Item * * res) Line 192 C++ mysqld-debug.exe!PTI_expr_or::itemize(Parse_context * pc, Item * * res) Line 192 C++ mysqld-debug.exe!PTI_expr_or::itemize(Parse_context * pc, Item * * res) Line 192 C++ mysqld-debug.exe!PTI_context<10>::itemize(Parse_context * pc, Item * * res) Line 1096 C++ mysqld-debug.exe!MYSQLparse(THD * YYTHD) Line 11322 C++ mysqld-debug.exe!parse_sql(THD * thd, Parser_state * parser_state, Object_creation_ctx * creation_ctx) Line 7045 C++ mysqld-debug.exe!Prepared_statement::prepare(const char * packet, unsigned __int64 packet_len) Line 3324 C++ mysqld-debug.exe!mysql_sql_stmt_prepare(THD * thd) Line 2371 C++ mysqld-debug.exe!mysql_execute_command(THD * thd) Line 2497 C++ mysqld-debug.exe!sp_instr_stmt::exec_core(THD * thd, unsigned int * nextp) Line 922 C++ mysqld-debug.exe!sp_lex_instr::reset_lex_and_exec_core(THD * thd, unsigned int * nextp, bool open_tables) Line 401 C++ mysqld-debug.exe!sp_lex_instr::validate_lex_and_execute_core(THD * thd, unsigned int * nextp, bool open_tables) Line 657 C++ mysqld-debug.exe!sp_instr_stmt::execute(THD * thd, unsigned int * nextp) Line 833 C++ mysqld-debug.exe!sp_head::execute(THD * thd, bool merge_da_on_success) Line 781 C++ mysqld-debug.exe!sp_head::execute_procedure(THD * thd, List<Item> * args) Line 1505 C++ mysqld-debug.exe!mysql_execute_command(THD * thd) Line 4463 C++ mysqld-debug.exe!mysql_parse(THD * thd, Parser_state * parser_state) Line 5386 C++ mysqld-debug.exe!dispatch_command(enum_server_command command, THD * thd, char * packet, unsigned __int64 packet_length) Line 1252 C++ mysqld-debug.exe!do_command(THD * thd) Line 833 C++ mysqld-debug.exe!handle_connection(void * arg) Line 298 C++ mysqld-debug.exe!pfs_spawn_thread(void * arg) Line 2139 C++ mysqld-debug.exe!pthread_start(void * p) Line 73 C mysqld-debug.exe!_callthreadstartex() Line 376 C mysqld-debug.exe!_threadstartex(void * ptd) Line 359 C kernel32.dll!BaseThreadInitThunk () Unknown ntdll.dll!RtlUserThreadStart () Unknown
[25 Mar 2015 9:11]
Simon Mudd
Also broken in 5.7.6 (so not fixed yet). I'm currently using the following workaround: thread_stack = 512K
[25 Mar 2015 9:19]
Simon Mudd
See also: bug#76479 as this setting is not dynamic.
[13 Apr 2015 5:18]
Simon Mudd
Still broken in: - 5.7.6 - 5.7.7-rc
[17 Apr 2015 14:54]
Paul DuBois
Noted in 5.7.8, 5.8.0 changelogs. With a small thread stack, queries with many expressions could produce a thread stack overrun error.
Description: Queries with many expressions cannot be executed on 5.7 using a reasonably small thread_stack. +----------+-------------+-----------+ | numterms | query lengt | increment | +----------+-------------+-----------+ | 3601 | 61234 | 100 | +----------+-------------+-----------+ 1 row in set (45.97 sec) ERROR 1436 (HY000): Thread stack overrun: 246168 bytes used of a 262144 byte stack, and 16000 bytes needed. Use 'mysqld --thread_stack=#' to specify a bigger stack. mysql> select @@global.thread_stack,@@global.max_allowed_packet,version(); +-----------------------+-----------------------------+-----------+ | @@global.thread_stack | @@global.max_allowed_packet | version() | +-----------------------+-----------------------------+-----------+ | 262144 | 4194304 | 5.7.6-m16 | +-----------------------+-----------------------------+-----------+ 1 row in set (0.00 sec) --------------- 5.6 is able to execute much larger queries using same thread_stack: +----------+-------------+-----------+ | numterms | query lengt | increment | +----------+-------------+-----------+ | 950000 | 16150017 | 100000 | +----------+-------------+-----------+ 1 row in set (38.57 sec) How to repeat: On 5.6 and 5.7, with default thread stack run the testcase. 5.7 doesn't last long before erroring out. drop table if exists t1; create table `t1` (`a` int unsigned,`b` int unsigned, primary key (`a`,`b`)) engine=innodb; drop function if exists f1; drop procedure if exists p1; delimiter $ create procedure p1(p_min_terms int unsigned,p_max_terms int unsigned,p_incr int unsigned) begin declare v_sql longtext charset latin1; declare i int unsigned default 1; set i:=greatest(1,p_min_terms); repeat set @terms:=f1(i); set v_sql:=concat("delete from t1 where ",@terms); set @t:=v_sql; select i as 'numterms', length(@t) as 'query lengt',p_incr as 'increment'; prepare s from @t; execute s; deallocate prepare s; set i:=i + p_incr; until i > p_max_terms end repeat; end $ create function f1(p_numterms int unsigned) returns longtext begin declare ret longtext default ''; set ret:=concat(repeat("(a=1 and b=2) or ",greatest(p_numterms-1,1)),"(a=2 and b=1)"); return ret; end $ delimiter ; set @min:=1; set @max:=10000000; set @incr:=100; call p1(@min,@max,@incr); select @@global.thread_stack,@@global.max_allowed_packet,version();