MySQL错误1436:线程堆栈溢出，带有简单查询 [英] MySQL error 1436: Thread stack overrun, with simple query

问题描述

我正在对表进行非常简单的更新，这也会触发一个非常简单的触发器，并且会给我错误

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.

我执行的查询:

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

值字段是text字段.因此，从理论上讲，它可能会变得安静.在这种情况下情况并非如此.

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

正在执行的触发器是:

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 ;

为什么显示此错误?它不像涉及任何繁重的查询.还要注意，数据库几乎是空的，在community_fields_values中只有2行，在user_field_log

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_values and no rows in the user_field_log

MySQL版本:5.1.44

MySQL version: 5.1.44

推荐答案

1436-线程堆栈溢出:131072字节堆栈使用了6136字节，而需要128000字节.

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

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

打印出所见错误的代码在sql/sql_parse.cc中， 函数check_stack_overrun():

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));

从看到的值中，边距为128000，而my_thread_stack_size为131072.

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

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

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);

STACK_MIN_SIZE的值为16000:

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:

• 代码执行触发器，该触发器通过以下方式实现 sp_head :: execute.
• MySQL运行时检查堆栈上至少有128000个字节
• 此检查失败(正确地如此)，并且触发器执行以错误结束.

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

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

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.

我想， real 问题是什么，为什么thread_stack仅在128K(131072)处存在.

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

在C/SQL/mysqld.cc中，名为"thread_stack"的服务器变量在其中实现为"my_thread_stack_size":

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是此参数的最小值. 默认值为DEFAULT_THREAD_STACK，该值在include/my_pthread.h中定义:

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

因此，默认情况下，堆栈大小应为192K(32位)或256K(64位体系结构).

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

首先，检查mysqld二进制文件的编译方式，以了解默认值是什么:

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

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

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

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

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.

第二，检查my.cnf以获取配置文件本身中提供的默认值. 显式地将值设置为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.

最后，检查服务器日志文件中是否存在诸如此类的错误(请参见sql/mysqld.cc):

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);

服务器代码调用:

• pthread_attr_setstacksize()设置堆栈大小
• pthread_attr_getstacksize()验证线程真正具有多少堆栈 并在日志中抱怨pthread库使用的较少.

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

长话短说，由于与服务器附带的默认值相比thread_stack太小，因此会看到该错误. 可能会发生:

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:

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

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

我希望这能回答问题.

关于， -马克·阿尔夫(Marc Alff)

Regards, -- Marc Alff

更新(2014-03-11)，以使解决方法"更加明显.

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

很有可能，my.cnf文件中的thread_stack文件的默认值已更改.

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.

这篇关于MySQL错误1436:线程堆栈溢出，带有简单查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！