我正在对表做一个非常简单的更新，这也触发了一个非常简单的触发器，它给了我错误
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对应mysql 5.1代码中的ER_STACK_OVERRUN_NEED_MORE:
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));
从看到的值来看，margin 是 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:
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.
真正的问题是什么，我猜，为什么 thread_stack 只有 128K (131072).
What the real question is, I guess, why is the thread_stack only at 128K (131072).
名为 'thread_stack' 的服务器变量在 C 中实现为 sql/mysqld.cc 中的 '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);
服务器代码调用:
长话短说，看到错误是因为与服务器附带的默认值相比，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:
我希望这能回答问题.
问候，——马克·阿尔夫
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.
更新(2021-04-28)，查看thread_stack来自哪里:
Update (2021-04-28), check where the thread_stack comes from:
使用表 performance_schema.variables_info 找出给定变量的来源.
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)
这里默认是出厂值(在mysqld二进制文件中编译).
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)
这里的thread_stack是在上报的my.cnf文件中设置的.
Here the thread_stack is set in the my.cnf file reported.
雷夫曼:
https://dev.mysql.com/doc/refman/8.0/en/performance-schema-variables-info-table.html
这篇关于MySQL 错误 1436:线程堆栈溢出，带有简单查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！