My (SQL) WorkLog: MySQL - Thread stack overrun

Monday, June 15, 2009

MySQL - Thread stack overrun

Sometime you may come across a situation wherein a stored procedure(in mycase it was uGetBussinessDays which was working perfectly until this morning is now throwing a Thread stack overrun error) when called throws Thread stack overrun error.

Typically the error message would look like:

Error Code : 1436
Thread stack overrun: 6444 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 increase the default value of parameter thread_stack (128 in my my-small.cnf) to something 258K in your MySQL configuration file
.

6 comments:

aldo said...

Not because it breaks the mysql service and only get this error:
"InnoDB: Rolling back of trx id 0 2577591 completed
101028 15:50:53 InnoDB: Rollback of non-prepared transactions completed
101028 16:01:05 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
101028 16:01:05 InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 4196818045.
InnoDB: Doing recovery: scanned up to log sequence number 0 4196818045
101028 16:01:05 InnoDB: Started; log sequence number 0 4196818045
101028 16:01:05 [Note] C:\Archivos de programa\MySQL\MySQL Server 5.0\bin\mysqld-nt: ready for connections.
Version: '5.0.20-nt' socket: '' port: 3306 MySQL Community Edition (GPL)
101028 16:03:18 [Note] C:\Archivos de programa\MySQL\MySQL Server 5.0\bin\mysqld-nt: Normal shutdown

101028 16:03:18 InnoDB: Starting shutdown...
101028 16:03:21 InnoDB: Shutdown completed; log sequence number 0 4196824187
101028 16:03:21 [Note] C:\Archivos de programa\MySQL\MySQL Server 5.0\bin\mysqld-nt: Shutdown complete"

any help, thanks

Sharat Joshi said...

Umesh,

Thanks a lot for the useful post !

Sharat

Emile Baizel said...

Thanks for the tip. This resolved it for me. i am on Mac OS X and by default there is no my.cnf file. So I copied one and that's when this error started happening. Bumped it up to 192k and now it works great.

Anonymous said...

Umesh,

Does MySQL override the stack size set in linux?

or in other words,

Default stack size in Linux these days is 10 MB and by increasing/decreasing the stack size at MySQL level, are we sure that it will use those instead of Linux parameters?

Umesh Shastry said...

AFAIK, the answer is NO.
The value set for MySQL's parameter 'thread_stack' doesn't override the value set for the OS level limit.

Andree Ray said...

Thanx allot for this help full guide, works like a charm

Post a Comment