Mysql Too connector connections error solution

The cause of this problem is:

The number of connections exceeds the value set by MySQL. It is related to max_connections and wait_timeout. The larger the value of wait_timeout, the longer the idle waiting time for the connection, which leads to the larger number of current connections.

Solution:

Modify the MySQL configuration file/etc/my. cnf and set it to max_connections = 1000 and wait_timeout = 5. If you do not have this setting, you can add it by yourself. After modification, restart the MySQL service. If this error is not reported frequently, the overall performance of the server should be optimized.

Supplement:

At work, you may often encounter the Too connector connections error. At this time, it is very embarrassing to be unable to access database management as a DBA. Of course, some people say they can modify the configuration file. However, to modify the configuration file, restart mysqld, which is not allowed on busy database servers. Therefore, the following methods can be used in emergencies, such as the following tests.

[Root @ mysql-server-01 msb_5_6_19] #./use
ERROR 1040 (HY000): Too connector connections
[Root @ mysql-server-01 msb_5_6_19] #
I used the MySQL sandbox environment above. For simple installation and use of the sandbox environment, see my previous article. Install and use MySQL Sandbox
As you can see, I have reported an error, and the prompt is very obvious, that is, the number of connections we configured is too small. Now it is used up. Now we just want to do some operations in the database, so we use the following method:

[Root @ mysql-server-01 ~] # Gdb-p $ (cat/root/sandboxes/msb_5_6_19/data/mysql_sandbox5619.pid)-ex "set max_connections = 500"-batch
[New LWP 27541]
[New LWP 27540]
[New LWP 27539]
[Thread debugging using libthread_db enabled]
0x00000031152df343 in poll () from/lib64/libc. so.6
[Root @ mysql-server-01 ~] #

Log on to the database again and check whether the maximum number of connections has been modified.

[Root @ mysql-server-01 msb_5_6_19] #./use
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 22
Server version: 5.6.19-log MySQL Community Server (GPL)

Copyright (c) 2000,201 4, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
Affiliates. Other names may be trademarks of their respective
Owners.

Type 'help; 'or' \ H' for help. Type' \ C' to clear the current input statement.

Mysql [localhost] {msandbox} (none)> show variables like 'max _ connections ';
+ ----------------- + ------- +
| Variable_name | Value |
+ ----------------- + ------- +
| Max_connections | 500 |
+ ----------------- + ------- +
1 row in set (0.00 sec)

Mysql [localhost] {msandbox} (none)>

In the thread_pool of Percona5.5, two parameters extra_port and extra_max_connections are provided to reserve additional connections. When the connection is full, we cannot enter the database for corresponding management.

Root @ localhost: (none) 23:18:00> show variables like '% extra % ';
+ ----------------------- + ------- +
| Variable_name | Value |
+ ----------------------- + ------- +
| Extra_max_connections | 1 |
| Extra_port | 10086 |
+ ----------------------- + ------- +
2 rows in set (0.00 sec)

Root @ localhost: (none) 23:18:04>

[Root @ mysql-server-01 user_3307] # netstat-nltp | grep 10086
Tcp 0 0 0.0.0.0: 10086 0.0.0.0: * LISTEN 29655/mysqld
[Root @ mysql-server-01 user_3307] #
I have used Port 10086 and the maximum number of connections is 1. Has such a considerate function. You must give a like

Summary:

Max_connections (the maximum number of connections allowed by this instance) and max_user_connections (the maximum number of connections allowed for each user) are usually controlled using two parameters ), we usually need to plan the number of connections in the early stage. Generally, we recommend that you do not set the value to more than 300. Because the performance of MySQL decreases sharply when the number of connections increases. If a large number of connections are required, thread_pool can be introduced. Therefore, we need to maintain the principle that the number of users created by the system (for applications) * max_user_connections <max_connections. In this way, no problems will occur in the beginning of the article.

How to troubleshoot Too connector connections error in java

MySQL error. [resolved] Data source rejected establishment of connection message from server: "Too has connections ";

[15:22:53, 461] [CustomExceptionHandler, 44]-Handle Exception: hibernate operation: Cannot open connection; uncategorized SQLException for SQL [???]; SQL state [08004]; error code [1040]; Data source rejected establishment of connection message from server: "Too has connections"; nested exception is Java. SQL. SQLException: Data source rejected establishment of connection message from server: "Too has connections"
Org. springframework. jdbc. UncategorizedSQLException: Hibernate operation: Cannot open connection; uncategorized SQLException for SQL [???]; SQL state [08004]; error code [1040]; Data source rejected establishment of connection message from server: "Too has connections"; nested exception is java. SQL. SQLException: Data source rejected establishment of connection message from server: "Too has connections"
Java. SQL. SQLException: Data source rejected establishment of connection message from server: "Too many connections"
At com. mysql. jdbc. MysqlIO. checkErrorPacket (MysqlIO. java: 1997)
At com. mysql. jdbc. MysqlIO. checkErrorPacket (MysqlIO. java: 1906)
At com. mysql. jdbc. MysqlIO. secureAuth411 (MysqlIO. java: 2535)
At com. mysql. jdbc. MysqlIO. doHandshake (MysqlIO. java: 817)
At com. mysql. jdbc. Connection. createNewIO (Connection. java: 1782)
At com. mysql. jdbc. Connection. <init> (Connection. java: 450)
At com. mysql. jdbc. NonRegisteringDriver. connect (NonRegisteringDriver. java: 411)
At java. SQL. DriverManager. getConnection (DriverManager. java: 525)
At java. SQL. DriverManager. getConnection (DriverManager. java: 140)
At org. springframework. jdbc. datasource. DriverManagerDataSource. getConnectionFromDriverManager (DriverManagerDataSource. java: 291)
At org. springframework. jdbc. datasource. DriverManagerDataSource. getConnectionFromDriverManager (DriverManagerDataSource. java: 277)
At org. springframework. jdbc. datasource. DriverManagerDataSource. getConnectionFromDriverManager (DriverManagerDataSource. java: 259)
At org. springframework. jdbc. datasource. DriverManagerDataSource. getConnection (DriverManagerDataSource. java: 241)
At org. springframework. orm. hibernate3.LocalDataSourceConnectionProvider. getConnection (LocalDataSourceConnectionProvider. java: 80)
At org. hibernate. jdbc. ConnectionManager. openConnection (ConnectionManager. java: 315)
At org. hibernate. jdbc. ConnectionManager. getConnection (ConnectionManager. java: 109)
At org. hibernate. jdbc. AbstractBatcher. prepareQueryStatement (AbstractBatcher. java: 105)
At org. hibernate. loader. Loader. prepareQueryStatement (Loader. java: 1561)
At org. hibernate. loader. Loader. Docquery (Loader. java: 661)
At org. hibernate. loader. Loader. doQueryAndInitializeNonLazyCollections (Loader. java: 224)
At org. hibernate. loader. Loader. doList (Loader. java: 2150)
At org. hibernate. loader. Loader. listIgnoreQueryCache (Loader. java: 2029)
At org. hibernate. loader. Loader. list (Loader. java: 2024)
At org. hibernate. loader. hql. QueryLoader. list (Fig. java: 369)
At org. hibernate. hql. ast. QueryTranslatorImpl. list (QueryTranslatorImpl. java: 300)
At org. hibernate. engine. query. HQLQueryPlan. Sort mlist (HQLQueryPlan. java: 146)
At org. hibernate. impl. SessionImpl. list (SessionImpl. java: 1093)
At org. hibernate. impl. QueryImpl. list (QueryImpl. java: 79)
At com. easou. framework. dao. hibernate. BaseDAOHibernate. pageListQuery (BaseDAOHibernate. java: 164)
At com.vitamin.cn. manager. news. dao. hibernate. NewsDAOHibernate. searchNews (NewsDAOHibernate. java: 38)
At com.vitamin.cn. manager. news. bl. impl. NewsManagerImpl. searchNews (NewsManagerImpl. java: 24)
At com.vitamin.cn. manager. news. action. NewsAction. listNews (NewsAction. java: 88)
At com.vitamin.cn.manager.news.action.NewsAction.exe cute (NewsAction. java: 35)
At org. apache. struts. action. RequestProcessor. processActionPerform (RequestProcessor. java: 419)
At org. apache. struts. action. RequestProcessor. process (RequestProcessor. java: 224)
At org. apache. struts. action. ActionServlet. process (ActionServlet. java: 1196)
At org. apache. struts. action. ActionServlet. doGet (ActionServlet. java: 414)
At javax. servlet. http. HttpServlet. service (HttpServlet. java: 689)
At javax. servlet. http. HttpServlet. service (HttpServlet. java: 802)
At org. apache. catalina. core. ApplicationFilterChain. internalDoFilter (ApplicationFilterChain. java: 252)
At org. apache. catalina. core. ApplicationFilterChain. doFilter (ApplicationFilterChain. java: 173)
At com. easou. framework. util. SetCharacterEncodingFilter. doFilter (SetCharacterEncodingFilter. java: 88)
At org. apache. catalina. core. ApplicationFilterChain. internalDoFilter (ApplicationFilterChain. java: 202)
At org. apache. catalina. core. ApplicationFilterChain. doFilter (ApplicationFilterChain. java: 173)
At org. apache. catalina. core. StandardWrapperValve. invoke (StandardWrapperValve. java: 213)
At org. apache. catalina. core. StandardContextValve. invoke (StandardContextValve. java: 178)
At org. apache. catalina. authenticator. AuthenticatorBase. invoke (AuthenticatorBase. java: 524)
At org. apache. catalina. core. StandardHostValve. invoke (StandardHostValve. java: 126)
At org. apache. catalina. valves. ErrorReportValve. invoke (ErrorReportValve. java: 105)
At org. apache. catalina. core. StandardEngineValve. invoke (StandardEngineValve. java: 107)
At org. apache. catalina. connector. CoyoteAdapter. service (CoyoteAdapter. java: 148)
At org. apache. jk. server. JkCoyoteHandler. invoke (JkCoyoteHandler. java: 199)
At org. apache. jk. common. HandlerRequest. invoke (HandlerRequest. java: 282)
At org. apache. jk. common. ChannelSocket. invoke (ChannelSocket. java: 767)
At org. apache. jk. common. ChannelSocket. processConnection (ChannelSocket. java: 697)
At org. apache. jk. common. ChannelSocket $ SocketConnection. runIt (ChannelSocket. java: 889)
At org. apache. tomcat. util. threads. ThreadPool $ ControlRunnable. run (ThreadPool. java: 684)
At java. lang. Thread. run (Thread. java: 595)

1. It may be a problem with mysql's max connections settings.
2. It may be multiple insert operations. The update operation does not close the session. You need to configure transaction support in spring.

Solution:

1. Reduce the time-out time of the session in tomcat to 20 (not required)
2. Provides transaction support for database insert or update operations with large processing capacity.

========================================================
The following is a solution:

Com. mysql. jdbc. exceptions. jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection, message from server: "Too has connections"

Cause:

The number of connections is full because the number of concurrent connections set in my. ini in your mysql installation directory is too small or the system is busy.

Solution:

Open the MYSQL installation directory, open MY. INI, and find max_connections (in approximately 93rd rows). The default value is 100, which is generally set to 500 ~ 1000 is more suitable. Restart mysql and solve the 1040 error.
Max_connections = 1000

MYSQL must be restarted to take effect.

CMD->

Net stop mysql

Net start mysql
 

Mysql cannot be started after innodb_log_file_size is changed.

Innodb_buffer_pool_size = 768 M
Innodb_log_file_size = 256 M
Innodb_log_buffer_size = 8 M
Innodb_additional_mem_pool_size = 4 M
Innodb_flush_log_at_trx_commit = 0
Innodb_thread_concurrency = 20
The above is a preliminary optimization of the innodb engine. It was found that there was a problem when innodb_log_file_size = 256M was updated. If this is added, the engine cannot be started,

Later, I realized that I had to STOP the service first, and then deleted the original file .........
Open/MySQL Server 5.5/data

Delete ib_logfile0, ib_logfile1 ...... ib_logfilen
Enable the option again and start successfully.

1. Modify the configuration file

Modify/etc/my. the cnf file is added to max_connections = N in [mysqld]. If you do not have this file, copy the file you need from the support-files folder in the compilation source code *. the cnf file is/etc/my. cnf. I'm using a my-medium.cnf with medium-sized server configuration. For example, the content of my [mysqld] is as follows:

[Mysqld]
Port = 3306
Socket =/tmp/mysql. sock
Skip-locking
Key_buffer = 160 M
Max_allowed_packet = 1 M
Table_cache = 64
Sort_buffer_size = 512 K
Net_buffer_length = 8 K
Read_buffer_size = 256 K
Read_rnd_buffer_size = 512 K
Myisam_sort_buffer_size = 8 M
Max_connections = 1000

Because I am not familiar with mysql, many parameters have not been modified. Haha ..

2. Users who do not use the mysqld script to start automatically.

Modify the $ MYSQL_HOME/bin/mysqld_safe file
Example:/usr/local/mysql/bin/mysqld_safe file
Grep-n 'max _ connection' $ MYSQL_HOME/bin/mysqld_safe
Modify the max_connections parameter value of the corresponding row number.
The above method is written based on the practices on the Internet.

Mysql cannot be started after innodb_log_file_size is changed.

Innodb_buffer_pool_size = 768 M
Innodb_log_file_size = 256 M
Innodb_log_buffer_size = 8 M
Innodb_additional_mem_pool_size = 4 M
Innodb_flush_log_at_trx_commit = 0
Innodb_thread_concurrency = 20
The above is a preliminary optimization of the innodb engine. It was found that there was a problem when innodb_log_file_size = 256M was updated. If this is added, the engine cannot be started,

Later, I realized that I had to STOP the service first, and then deleted the original file .........
Open/MySQL Server 5.5/data

Delete ib_logfile0, ib_logfile1 ...... ib_logfilen

Enable the option again and start successfully.