快捷導(dǎo)航

Mysql Error Code : 1436 Thread stack overrun

更新時間：2011年07月02日 23:50:36 投稿：mdxy-dxy

I meet with the error while calling stored procedures from the MySql in my Mac system server. It similar as the description below

ERRNO: 256
TEXT: SQLSTATE[HY000]: General error: 1436 Thread stack overrun: 4904 bytes used of a 131072 byte stack, and 128000 bytes needed. Use 'mysqld -O thread_stack=#' to specify a bigger stack.

According to the MySQL manual “The default (192KB) is large enough for normal operation. If the thread stack size is too small, it limits the complexity of the SQL statements that the server can handle, the recursion depth of stored procedures, and other memory-consuming actions” .

To resolve this issue you need to change the default value of parameter 'thread_stack' in /etc/my.cnf in your MySQL configuration file. I use the XAMPP for php/mysql development.

Once you set this value you need to restart MySQL as this value cannot be set dynamically.

you maybe also encounter with the message when you try to modify the my.cnf

"Cannot open file for writing: Permission denied"

We will try to use the 'chmod' instruction to change permission as usually. I seldom take the concrete permission into consideration, so I use always use the 'chmod 777'. but it resulted in another errors when I use the phpmyadmin, another tools included in XAMPP, after running 'chmod 777'.

After googling it, I get this file (my.cnf) permissions has to be 600. I change its permission and it works well now.

bug info

報錯信息:
java.sql.SQLException: Thread stack overrun: 5456 bytes used of a 131072 byte stack, and 128000 bytes needed. Use 'mysqld --thread_stack=#' to specify a bigger stack.

官方相應(yīng)信息:

The default (192KB) is large enough for normal operation. If the thread stack size is too small, it limits the complexity of the SQL statements that the server can handle, the recursion depth of stored procedures, and other memory-consuming actions

可以使用

show variables where `variable_name` = 'thread_stack';

查詢當(dāng)前數(shù)據(jù)庫的默認(rèn)線程棧的大小，一般情況下都能正常使用，但是當(dāng)查詢語句或者存儲過程復(fù)雜時會報Thread stack overrun錯誤，此時只要修改默認(rèn)配置就可以。

解決

windows: 修改mysql安裝目錄下的my-small.ini或者my.ini設(shè)置為256k,或者更大，然后重啟服務(wù)

[mysqld]
thread_stack = 256k
linux: 同樣要修改配置文件，但是?。?！，不是安裝目錄下的配置文件，是/etc/my.cnf,只有這個文件才能生效，然后重啟服務(wù)service mysql restart

[mysqld]
thread_stack = 256k

其實針對32位系統(tǒng)，32G內(nèi)存，一般設(shè)置為512K即可，據(jù)國外網(wǎng)站看到的，如果是64位的系統(tǒng)可以適當(dāng)增加，其實夠用就好了，沒必須剛開始設(shè)置的就很大。

您可能感興趣的文章: