MySQL 错误 1436：线程堆栈溢出，带有简单查询

MySQL error 1436: Thread stack overrun, with simple query

提问by Rene Pot

I am doing a very simple update on a table, which also triggers a really simple trigger, and it gives me the error

我正在对表做一个非常简单的更新，这也触发了一个非常简单的触发器，它给了我错误

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

The query I execute:

我执行的查询：

UPDATE field_values SET value = 'asaf' WHERE field_values.id =1

The value field is a textfield. So in theory it could become quiet big. Which is not the case in this situation.

值字段是一个text字段。所以理论上它可以变得安静大。在这种情况下，情况并非如此。

The trigger that's getting executed is:

正在执行的触发器是：

DELIMITER $$
    CREATE TRIGGER field_value_update_trigger BEFORE UPDATE ON community_fields_values
    FOR EACH ROW BEGIN
      INSERT INTO user_field_log (user_id, field_id, value) VALUES (NEW.user_id, NEW.field_id, NEW.value);
    END;
$$
DELIMITER ;

Why is this error showing? It's not like there is any heavy query involved. Also note that the database is almost empty, just 2 rows in community_fields_valuesand no rows in the user_field_log

为什么会显示这个错误？这不像涉及任何繁重的查询。还要注意，数据库几乎是空的，只有 2 行，community_fields_values而数据库中没有行user_field_log

MySQL version: 5.1.44

MySQL 版本：5.1.44

回答by Marc Alff

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

1436 - 线程堆栈溢出：131072 字节堆栈中使用了 6136 字节，需要 128000 字节。

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

错误 1436 对应于 mysql 5.1 代码中的 ER_STACK_OVERRUN_NEED_MORE ：

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() :

打印所见错误的代码在 sql/sql_parse.cc 函数 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.

从看到的值来看，margin 是 128000，my_thread_stack_size 是 131072。

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

对 check_stack_overrun() 试图保留 128000 字节的唯一调用来自：

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:

STACK_MIN_SIZE 的值为 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.

• 代码执行一个触发器，它是用 sp_head::execute 实现的。
• MySQL 运行时检查堆栈上是否至少有 128000 个字节
• 此检查失败（正确地如此），并且触发器执行以错误结束。

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.

MySQL 触发器执行所需的堆栈数量不取决于触发器复杂性本身，或所涉及表的内容/结构。

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

在什么真正的问题是，我想，为什么只在128K（131072）的thread_stack。

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

名为“thread_stack”的服务器变量在 C 中实现为 sql/mysqld.cc 中的“my_thread_stack_size”：

  {"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:

1024L*128L 是该参数的最小值。默认值为 DEFAULT_THREAD_STACK，在 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).

因此，默认情况下，堆栈大小应为 192K（32 位）或 256K（64 位架构）。

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

首先，检查mysqld二进制文件是如何编译的，看看默认值是多少：

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.

在我的系统上，我在 64 位平台上获得了 256K。

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.

如果有不同的值，也许有人用不同的编译选项构建服务器，例如 -DDEFAULT_THREAD_STACK（或只是修改了源代码）......我会质疑在这种情况下二进制文件的来源。

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.

其次，检查 my.cnf 以获取配置文件本身中提供的默认值。将值显式设置为 thread_stack 的行（并且具有低值）肯定会导致看到的错误。

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

最后，检查服务器日志文件是否有这样的错误（参见 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.

• pthread_attr_setstacksize() 设置堆栈大小
• pthread_attr_getstacksize() 用于验证线程实际拥有多少堆栈，如果 pthread 库使用较少，则会在日志中抱怨。

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:

长话短说，看到错误是因为与服务器附带的默认值相比，thread_stack 太小了。这可能发生：

• 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).

• 在进行服务器的自定义构建时，使用不同的编译选项
• 更改 my.cnf 文件中的默认值时
• 如果 pthread 库本身出现问题（理论上通过阅读代码，我自己从未见过）。

I hope this answer the question.

我希望这能回答这个问题。

Regards, -- Marc Alff

问候，——马克·阿尔夫

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

更新 (2014-03-11)，使“如何修复”更加明显。

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

发生的事情很可能是 my.cnf 文件中 thread_stack 文件的默认值已更改。

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.

那么如何修复它是微不足道的，找到在 my.cnf 文件中设置 thread_stack 的位置，然后删除设置（信任服务器代码提供合适的默认值，因此下次不会再发生这种情况）或增加堆栈尺寸。

回答by JayTaph

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

虽然不是解决方案，但一个快速的解决方法是通过在 my.cnf 中增加 thread_stack 大小来增加它：

thread_stack = 256K

线程堆栈 = 256K

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

正如用户“foo”指出的那样，发布整个触发代码可能更有助于检测真正的问题。