MySQL error 1436: Thread stack overrun, with simple query

Solution 1:

1436 - Thread stack overrun: 6136 bytes used of a 131072 byte stack, and 128000 bytes needed.

The error 1436 corresponds to ER_STACK_OVERRUN_NEED_MORE in the mysql 5.1 code :

malff@linux-8edv:include> pwd
/home/malff/BZR_TREE/mysql-5.1/include
malff@linux-8edv:include> grep 1436 mysqld_error.h
#define ER_STACK_OVERRUN_NEED_MORE 1436

The code printing the error seen is in sql/sql_parse.cc, function check_stack_overrun() :

bool check_stack_overrun(THD *thd, long margin,
                         uchar *buf __attribute__((unused)))
{
  long stack_used;
  DBUG_ASSERT(thd == current_thd);
  if ((stack_used=used_stack(thd->thread_stack,(char*) &stack_used)) >=
      (long) (my_thread_stack_size - margin))
  {
    char ebuff[MYSQL_ERRMSG_SIZE];
    my_snprintf(ebuff, sizeof(ebuff), ER(ER_STACK_OVERRUN_NEED_MORE),
                stack_used, my_thread_stack_size, margin);
    my_message(ER_STACK_OVERRUN_NEED_MORE, ebuff, MYF(ME_FATALERROR));

From the values seen, margin is 128000, and my_thread_stack_size is 131072.

The only call to check_stack_overrun() that tries to reserve 128000 bytes is from:

bool
sp_head::execute(THD *thd)
{
  /* Use some extra margin for possible SP recursion and functions */
  if (check_stack_overrun(thd, 8 * STACK_MIN_SIZE, (uchar*)&old_packet))
    DBUG_RETURN(TRUE);

The value of STACK_MIN_SIZE is 16000:

malff@linux-8edv:sql> pwd
/home/malff/BZR_TREE/mysql-5.1/sql
malff@linux-8edv:sql> grep STACK_MIN_SIZE *.h
mysql_priv.h:#define STACK_MIN_SIZE          16000   // Abort if less stack during eval.

So far, everything works as expected for the server:

• the code executes a trigger, which is implemented with sp_head::execute.
• the MySQL runtime checks that there is at least 128000 bytes on the stack
• this check fails (rightly so), and the trigger execution ends with an error.

The amount of stack needed by the MySQL trigger execution does not depends on the trigger complexity itself, or the content / structure of the tables involved.

What the real question is, I guess, why is the thread_stack only at 128K (131072).

The server variable named 'thread_stack' is implemented in C as 'my_thread_stack_size' in sql/mysqld.cc :

  {"thread_stack", OPT_THREAD_STACK,
   "The stack size for each thread.", &my_thread_stack_size,
   &my_thread_stack_size, 0, GET_ULONG, REQUIRED_ARG,DEFAULT_THREAD_STACK,
   1024L*128L, ULONG_MAX, 0, 1024, 0},

1024L*128L is the minimum value for this parameter. The default value is DEFAULT_THREAD_STACK, which is defined in include/my_pthread.h:

#ifndef DEFAULT_THREAD_STACK
#if SIZEOF_CHARP > 4
/*
  MySQL can survive with 32K, but some glibc libraries require > 128K stack
  To resolve hostnames. Also recursive stored procedures needs stack.
*/
#define DEFAULT_THREAD_STACK    (256*1024L)
#else
#define DEFAULT_THREAD_STACK    (192*1024)
#endif
#endif

So, by default, the stack size should be 192K (32bits) or 256K (64bits architectures).

First, check how the mysqld binary was compiled, to see what is the default value:

malff@linux-8edv:sql> pwd
/home/malff/BZR_TREE/mysql-5.1/sql
malff@linux-8edv:sql> ./mysqld --no-defaults --verbose --help | grep thread_stack
...
  --thread_stack=#    The stack size for each thread.
thread_stack                      262144

On my system, I got 256K on a 64 bits platform.

If there are different values, maybe someone build the server with different compiling options, such as -DDEFAULT_THREAD_STACK (or just modified the source) ... I would question where the binary is coming from in that case.

Second, check my.cnf for default values provided in the configuration file itself. A line setting a value to thread_stack explicitly (and with a low value) would definitively cause the error seen.

Last, check the server log file for an error such as this (see sql/mysqld.cc) :

sql_print_warning("Asked for %lu thread stack, but got %ld",
                  my_thread_stack_size, (long) stack_size);

The server code calls:

• pthread_attr_setstacksize() to set the stack size
• pthread_attr_getstacksize() to verify how much stack a thread really have and complains in the log if the pthread library used less.

Long story short, the error is seen because the thread_stack is too small compared to the default values shipped with the server. This can happen:

• when doing custom builds of the server, with different compiling options
• when changing the default value in the my.cnf file
• if something went wrong in the pthread library itself (in theory from reading the code, I never have seen it myself).

I hope this answer the question.

Regards, -- Marc Alff

Update (2014-03-11), to make the "how to fix" more obvious.

What is going on, in all likelihood, is that the default value for thread_stack file was changed in the my.cnf file.

How to fix it is trivial then, find where thread_stack is set in the my.cnf file, and either remove the setting (trusting the server code to provide a decent default value, so this does not happen again next time) or increase the stack size.

Update (2021-04-28), check where the thread_stack comes from:

Use table performance_schema.variables_info to find out where a given variable comes from.

mysql> select * from variables_info where VARIABLE_NAME = 'thread_stack';
+---------------+-----------------+---------------+-----------+----------------------+----------+----------+----------+
| VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE            | SET_TIME | SET_USER | SET_HOST |
+---------------+-----------------+---------------+-----------+----------------------+----------+----------+----------+
| thread_stack  | COMPILED        |               | 131072    | 18446744073709550592 | NULL     | NULL     | NULL     |
+---------------+-----------------+---------------+-----------+----------------------+----------+----------+----------+
1 row in set (0.01 sec)

Here the default is the factory value (compiled in the mysqld binary).

Another example:

mysql> select * from variables_info where VARIABLE_NAME = 'thread_stack';
+---------------+-----------------+----------------------------------------------------------------+-----------+----------------------+----------+----------+----------+
| VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH                                                  | MIN_VALUE | MAX_VALUE            | SET_TIME | SET_USER | SET_HOST |
+---------------+-----------------+----------------------------------------------------------------+-----------+----------------------+----------+----------+----------+
| thread_stack  | EXPLICIT        | /home/malff/CODE/GIT/GIT_TRUNK/build-dbg/mysql-test/var/my.cnf | 131072    | 18446744073709550592 | NULL     | NULL     | NULL     |
+---------------+-----------------+----------------------------------------------------------------+-----------+----------------------+----------+----------+----------+
1 row in set (0.00 sec)

Here the thread_stack is set in the my.cnf file reported.

Refman:

https://dev.mysql.com/doc/refman/8.0/en/performance-schema-variables-info-table.html

Solution 2:

Although not a solution, but a quick fix could be to increase the thread_stack size by incrementing it in your my.cnf:

thread_stack = 256K

As user "foo" pointed out, posting the whole trigger code might be more helpful in order to detect the real problem.