A common error in Mysql 1135 (HY000) errors caused by the bloodshed _mysql

Received a test today, the test environment front-end application of the MySQL database without connection, login to the MySQL server, view the error log, found that there are the following errors:

ERROR 1135 (HY000): Can ' t create a new thread (errno); If you are not out of available memory,you Can consult the manual For a possible os-dependent bug

The first reaction feeling may be related to the Ulimit limit connection number, the file descriptor is not enough. Next check the configuration piece/etc/security/limits.conf related results are as follows:

Copy Code code as follows:

#for Root
Root Soft Nofile 65535
Root Hard Nofile 65535
# End of File
MySQL Soft nproc 65536
MySQL Hard Nproc 65536
MySQL Soft nofile 65535
MySQL Hard nofile 65535

Configuration is not a problem, the MySQL ulimit limit has been opened.

However, execute the following command:

Copy Code code as follows:

# sudo-u Root bash-c "Ulimit-a"
Core file size (blocks,-c) 0
Data seg Size (Kbytes,-D) Unlimited
Scheduling Priority (-e) 0
File size (blocks,-f) Unlimited
Pending Signals (I.) 62591
Max locked Memory (Kbytes, L) 64
Max memory Size (Kbytes, M) Unlimited
Open files (-N) 1024
Pipe Size (bytes, p) 8
POSIX message queues (bytes,-Q) 819200
Real-time priority (-R) 0
Stack size (Kbytes,-s) 10240
CPU time (seconds,-t) unlimited
MAX User Processes (-u) 1024
Virtual Memory (Kbytes,-V) Unlimited
File locks (-X) Unlimited

The max user processes value was found to still be 1024.

In the CENTOS5, you only need to add the following two lines to the/etc/security/limits.conf:

Click (here) to collapse or open
Root Soft Nofile 65535
Root Hard Nofile 65535

The corresponding uilmit-u will be 65535.

Later guessed that the CENTOS6 user's ulimit limit is there any other configuration files to do related restrictions? Sure enough, found in the/etc/security/limits.d/directory, there is a profile named: 90-nproc.conf configuration file,
Open to see what content:

[Root@fztest ~]# cat/etc/security/limits.d/90-nproc.conf

Copy Code code as follows:

# Default limit for number of user ' s processes to prevent
# Accidental fork bombs.
# RHBZ #432903 for reasoning.

* Soft Nproc 1024

The "* Soft nproc 1024" in the configuration file/etc/security/limits.d/90-nproc.conf means that the max MAX user processes for any user is 1024, that is to say, No user of the system can be modified by ulimit-u. Is that really the case? Let's do the following verification operation:

Copy Code code as follows:

[Oracle@fztest ~]$ Ulimit-u 65535
-bash:ulimit:max User Processes:cannot Modify limit:operation not permitted
[Root@fztest ~]# Ulimit-u 65535
[Root@fztest ~]# Ulimit-u
65535

As a matter of fact, this limitation is a restriction on ordinary users other than root, and root can be modified immediately by Ulimit-u 65535来, only for the current session. Once you restart the server, it will fail (Restore MAX User processes-u 1024).

Next, try modifying the configuration file to verify that the value of max user processes changes.
After modifying 1024 in/etc/security/limits.d/90-nproc.conf to 65535, execute the following command:

Copy Code code as follows:

[Root@fztest ~]# sudo-u root bash-c "Ulimit-a"
Core file size (blocks,-c) 0
Data seg Size (Kbytes,-D) Unlimited
Scheduling Priority (-e) 0
File size (blocks,-f) Unlimited
Pending Signals (I.) 95191
Max locked Memory (Kbytes, L) 64
Max memory Size (Kbytes, M) Unlimited
Open files (-N) 65535
Pipe Size (bytes, p) 8
POSIX message queues (bytes,-Q) 819200
Real-time priority (-R) 0
Stack size (Kbytes,-s) 10240
CPU time (seconds,-t) unlimited
MAX User Processes (-u) 65535
Virtual Memory (Kbytes,-V) Unlimited
File locks (-X) Unlimited

This shows that the amendment takes effect. If you do not want to modify/etc/security/limits.d/90-nproc.conf this file, you can also add this limit to the/etc/rc.local file, so that its power-on application takes effect.
After the root user processes was successfully modified, the root user boot Mysqld_safe script continued to run stably for one morning, everything was OK. At this point, the ERROR 1135 (HY000): Can ' t create a new thread (errno 11) finally takes a paragraph.