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:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.7.5 OS:Any
Assigned to: CPU Architecture:Any

[24 Nov 2014 16:56] Shane Bester
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();
[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.