Error
Error Code:
1436
MySQL Error 1436: Thread Stack Overrun Exceeded
Description
MySQL Error 1436 indicates that a server thread has attempted to use more memory on its stack than was allocated. This typically occurs when complex operations, deep recursion within stored procedures, or specific user-defined functions exhaust the default or configured thread stack size.
Error Message
Thread stack overrun: %ld bytes used of a %ld byte stack, and %ld bytes needed. Use 'mysqld --thread_stack=#' to specify a bigger stack.
Known Causes
3 known causesComplex Queries or Stored Procedures
Queries or stored procedures involving deep recursion, extensive calculations, or large temporary data structures can quickly consume available thread stack space.
Inefficient User-Defined Functions (UDFs)
Custom user-defined functions (UDFs) that perform complex operations or manage large local variables might exceed the stack limit.
Insufficient Default Thread Stack Size
The default 'thread_stack' configuration might be too small for the specific demands of your MySQL server's workload and operations.
Solutions
3 solutions available1. Temporarily Increase Thread Stack Size (Quick Fix) easy
Dynamically adjust the thread stack size for the running MySQL server.
1
Connect to your MySQL server using a client like `mysql` or MySQL Workbench.
mysql -u your_user -p2
Execute the `SET GLOBAL` command to increase the `thread_stack` size. The error message usually indicates the amount needed. For example, if it says 'X bytes needed', you might try setting it to a value slightly larger than the default (often 192KB or 256KB). We'll use 512KB (524288 bytes) as an example.
SET GLOBAL thread_stack = 524288;3
Verify the change by checking the current value of `thread_stack`.
SHOW GLOBAL VARIABLES LIKE 'thread_stack';4
Note that this change is temporary and will be lost when the MySQL server restarts. For a permanent solution, see the next step.
2. Permanently Increase Thread Stack Size (Recommended) medium
Configure the thread stack size in the MySQL configuration file for a permanent solution.
1
Locate your MySQL configuration file. Common locations include `/etc/my.cnf`, `/etc/mysql/my.cnf`, or `/etc/mysql/mysql.conf.d/mysqld.cnf`.
2
Open the configuration file using a text editor with root privileges.
sudo nano /etc/mysql/my.cnf3
Under the `[mysqld]` section, add or modify the `thread_stack` directive. The error message will provide guidance on how much stack is needed. A common value to try if you're unsure is 256KB (262144 bytes) or 512KB (524288 bytes). For example:
[mysqld]
thread_stack = 5242884
Save the changes to the configuration file and exit the editor.
5
Restart the MySQL service for the changes to take effect.
sudo systemctl restart mysql6
After restarting, verify the `thread_stack` setting.
SHOW GLOBAL VARIABLES LIKE 'thread_stack';3. Analyze and Optimize Complex Queries advanced
Reduce the memory footprint of individual queries that might be contributing to stack overflows.
1
Identify the queries that are running when the error occurs. You might need to enable the slow query log or use `SHOW PROCESSLIST` to find them.
SHOW FULL PROCESSLIST;2
Use `EXPLAIN` to analyze the execution plan of problematic queries.
EXPLAIN SELECT ... FROM ... WHERE ...;3
Look for areas of inefficiency, such as full table scans, excessive use of temporary tables, or complex subqueries. Consider rewriting the query to be more efficient.
4
Ensure appropriate indexes are in place for your tables to speed up query execution and reduce the need for large temporary data structures.
5
Break down very complex queries into smaller, more manageable ones if possible.