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

Question

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

价值领域为<代码>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 ;

为什么出现这一错误? 它并不像任何激烈的争.。还注意到该数据库几乎是空的,在<编码>社区中仅有两行——价值,在<代码>用户_field_log上没有浏览。

MySQL version: 5.1.44

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

从所看到的数值来看,差值为128 000,而我对面值为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.

至今,服务器的所有工作都按计划进行:

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.

“real 问题”,我猜测,为什么只有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

因此,按违约计算,等级为192K(32bits)或256K(64bits structures)。

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。

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.

最后,核对服务器记录中的一种错误(见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).

我希望这个问题得到答案。

Regards, -- Marc Alff

更新(2014-03-11),使“如何固定”更为明显。

很可能正在发生的是,在我的卷宗中,th子档案的违约值发生了变化。

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.

更新(2021-04-28),从以下地点检查:

使用表<代码>性能_schema.variables_info,以发现某一变量来自何处。

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

另一个例子:

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)

在这里,在我所报的案卷中,有线人。

Refman:

rel=“nofollow noreferer”> https://dev.mysql.com/doc/refman/8.0/en/ Performance-schema-variables-info-table.html。

Answer 2

尽管这不是解决办法,但快速解决办法可以是增加read的面积,在阁下的座右上加起来:

thread_stack = 256K

正如用户“foo”指出的,张贴整个触发代码可能更有助于发现真正的问题。

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

友情链接