MySQL error 1436: Thread stack overrun, with simple query

by

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

More Related Contents:

  1. Throw an error preventing a table update in a MySQL trigger
  2. How to abort INSERT operation in MySql trigger?
  3. MySql Error: Can’t update table in stored function/trigger because it is already used by statement which invoked this stored function/trigger
  4. MySQL Trigger after update only if row has changed
  5. mysql after insert trigger which updates another table’s column
  6. How to program a MySQL trigger to insert row into another table?
  7. TRIGGERs that cause INSERTs to fail? Possible?
  8. Use a trigger to stop an insert or update
  9. MySQL Fire Trigger for both Insert and Update
  10. MySQL trigger On Insert/Update events
  11. Can a MySQL trigger simulate a CHECK constraint? [duplicate]
  12. MySQL trigger to update a field to the value of id
  13. How can I set a maximum number of rows in MySQL table?
  14. mysql trigger stored trigger is already used by statement which invoked stored trigger
  15. My MySQL trigger doesn’t work, simple syntax, not complicated
  16. MySQL triggers cannot update rows in same table the trigger is assigned to. Suggested workaround?
  17. MySQL Trigger: Delete From Table AFTER DELETE
  18. Is it Possible to Enforce Data Checking in MySQL using Regular expression
  19. Can I launch a trigger on select statement in mysql?
  20. Using MySQL triggers to log all table changes to a secondary table
  21. MySQL: How do I use delimiters in triggers?
  22. MySQL listen notify equivalent
  23. MySQL disable all triggers
  24. Calling an url from a trigger in mysql
  25. How to search JSON data in MySQL?
  26. MySQL > Table doesn’t exist. But it does (or it should)
  27. Set NOW() as Default Value for datetime datatype?
  28. How to skip certain database tables with mysqldump?
  29. Count with IF condition in MySQL query
  30. mysql_fetch_row() vs mysql_fetch_assoc() vs mysql_fetch_array() [duplicate]

Leave a Comment