mysql数据库报错1709_hive 元数据库 Too many connections

元数据换成mysql，请参考： mysql 元数据库，spark采用的是长连接，当spark关闭时，连接会处于睡眠状态。长时间不处理，mysql的连接数就会被占满。

1，java报错如下

5月 01 01:36:14 namenode1 start.sh[402144]: Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Too many connections

5月 01 01:36:14 namenode1 start.sh[402144]: at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

5月 01 01:36:14 namenode1 start.sh[402144]: at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)

5月 01 01:36:14 namenode1 start.sh[402144]: at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)

5月 01 01:36:14 namenode1 start.sh[402144]: at java.lang.reflect.Constructor.newInstance(Constructor.java:423)

5月 01 01:36:14 namenode1 start.sh[402144]: at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)

5月 01 01:36:14 namenode1 start.sh[402144]: at com.mysql.jdbc.Util.getInstance(Util.java:386)

5月 01 01:36:14 namenode1 start.sh[402144]: at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015)

5月 01 01:36:14 namenode1 start.sh[402144]: at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4187)

5月 01 01:36:14 namenode1 start.sh[402144]: at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4119)

5月 01 01:36:14 namenode1 start.sh[402144]: at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:927)

5月 01 01:36:14 namenode1 start.sh[402144]: at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1709)

5月 01 01:36:14 namenode1 start.sh[402144]: at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1252)

5月 01 01:36:14 namenode1 start.sh[402144]: at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2488)

5月 01 01:36:14 namenode1 start.sh[402144]: at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2521)

5月 01 01:36:14 namenode1 start.sh[402144]: at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2306)

5月 01 01:36:14 namenode1 start.sh[402144]: at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:839)

5月 01 01:36:14 namenode1 start.sh[402144]: at com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:49)

5月 01 01:36:14 namenode1 start.sh[402144]: at sun.reflect.GeneratedConstructorAccessor56.newInstance(Unknown Source)

5月 01 01:36:14 namenode1 start.sh[402144]: at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)

5月 01 01:36:14 namenode1 start.sh[402144]: at java.lang.reflect.Constructor.newInstance(Constructor.java:423)

5月 01 01:36:14 namenode1 start.sh[402144]: at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)

5月 01 01:36:14 namenode1 start.sh[402144]: at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:421)

5月 01 01:36:14 namenode1 start.sh[402144]: at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:350)

5月 01 01:36:14 namenode1 start.sh[402144]: at java.sql.DriverManager.getConnection(DriverManager.java:664)

5月 01 01:36:14 namenode1 start.sh[402144]: at java.sql.DriverManager.getConnection(DriverManager.java:208)

5月 01 01:36:14 namenode1 start.sh[402144]: at com.jolbox.bonecp.BoneCP.obtainRawInternalConnection(BoneCP.java:361)

5月 01 01:36:14 namenode1 start.sh[402144]: at com.jolbox.bonecp.BoneCP.(BoneCP.java:416)

5月 01 01:36:14 namenode1 start.sh[402144]: ... 100 more

2，查看一下mysql连接情况

mysql> show processlist;

+------+----------+-----------------+------+-------------+---------+---------------------------------------------------------------+------------------+

| Id | User | Host | db | Command | Time | State | Info |

+------+----------+-----------------+------+-------------+---------+---------------------------------------------------------------+------------------+

| 42 | rep_user | namenode2:50226 | NULL | Binlog Dump | 1402393 | Master has sent all binlog to slave; waiting for more updates | NULL |

| 6709 | hive | datanode1:32942 | hive | Sleep | 3433 | | NULL |

| 6710 | hive | datanode1:32944 | hive | Sleep | 3433 | | NULL |

| 6711 | hive | datanode1:32946 | hive | Sleep | 3433 | | NULL |

| 6712 | hive | datanode1:32948 | hive | Sleep | 3433 | | NULL |

| 6713 | hive | datanode1:32950 | hive | Sleep | 3416 | | NULL |

| 6714 | hive | datanode1:32952 | hive | Sleep | 3416 | | NULL |

| 6715 | hive | datanode1:32954 | hive | Sleep | 3414 | | NULL |

| 6716 | hive | datanode1:32956 | hive | Sleep | 3414 | | NULL |

| 6717 | hive | datanode1:32958 | hive | Sleep | 3193 | | NULL |

| 6718 | hive | datanode1:32960 | hive | Sleep | 3193 | | NULL |

| 6719 | hive | datanode1:32962 | hive | Sleep | 3193 | | NULL |

| 6720 | hive | datanode1:32964 | hive | Sleep | 3193 | | NULL |

| 6721 | hive | datanode1:32968 | hive | Sleep | 3174 | | NULL |

| 6722 | hive | datanode1:32970 | hive | Sleep | 3174 | | NULL |

3，mysql长连接默认检测时间

mysql> show variables like 'wait_timeout';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| wait_timeout | 28800 |

+---------------+-------+

1 row in set (0.01 sec)

mysql> show variables like 'interactive_timeout';

+---------------------+-------+

| Variable_name | Value |

+---------------------+-------+

| interactive_timeout | 28800 |

+---------------------+-------+

1 row in set (0.01 sec)

mysql默认设置中，如果一个 数据库 连接超过8小时没有使用，服务器将断开这条连接

4，解决办法

# vim /etc/my.cnf //增加以下内容,并重启

[mysqld]

max_connections=1000

interactive_timeout=1800

wait_timeout=1800

增大连接数，并且半个小时检查一下长连接