MySQL Error 1135 – SQLSTATE: HY000 (ER_CANT_CREATE_THREAD) occurs when the MySQL server is unable to create a new thread. This can happen for various reasons, such as reaching the operating system’s limit on the number of threads or running out of memory. The error message might also suggest an OS-dependent bug, but this is less common.
Understanding the Error
MySQL uses threads for handling client connections and performing other tasks. When the server reaches the maximum number of threads it can handle or if the system does not have enough resources to create a new thread, you will encounter this error.
The error message includes an error number
(errno %d) which can provide further insight. For example,
errno 11 typically indicates that the system’s resources are temporarily unavailable.
Diagnosing the Problem
To diagnose the issue, you should:
- Check the server’s available memory.
- Review the operating system’s thread limits.
- Examine the MySQL server’s thread configuration.
Checking Server Memory
Use system monitoring tools to check if the server is running out of memory:
free -m # On Unix/Linux systems
If memory is low, consider adding more RAM to the system or closing other applications to free up memory.
Reviewing OS Thread Limits
On Unix/Linux systems, you can check the maximum number of threads with:
ulimit -u # Maximum number of user process threads
To view or set the system-wide limits, you might need to inspect or modify the
Examining MySQL Configuration
thread_cache_size variables in MySQL:
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'thread_cache_size';
If these values are too high for your system to handle, you may need to adjust them.
Fixing the Error
Here are some strategies to resolve Error 1135:
Example 1: Increasing System Limits
If the thread limit is too low, increase the limit in the
/etc/security/limits.conf file (for Unix/Linux systems):
* soft nproc 4096
* hard nproc 4096
This sets the soft and hard limits for the number of processes for all users.
Example 2: Configuring MySQL Server Variables
thread_cache_size in the MySQL configuration file (usually
max_connections = 150 # Adjust to a reasonable number for your server
thread_cache_size = 8 # Adjust based on your workload
After making changes, restart the MySQL server.
Example 3: Optimizing Server Performance
Optimize your queries and database design to reduce the load on the server, which in turn can reduce the number of threads needed.
Example 4: Upgrading Hardware
If your server is consistently running out of memory or hitting resource limits, consider upgrading the hardware to provide more memory and processing power.
MySQL Error 1135 can be a sign of resource exhaustion or misconfiguration. By carefully reviewing and adjusting the operating system and MySQL server settings, you can resolve thread creation issues. Keep in mind the balance between server capabilities and MySQL configurations to ensure a stable and efficient database environment.