[Hibernate / Spring / Cluster / JDBC] Erreur "Too many connections"

**wiss85** · 13/09/2012, 11h30

Bonjour,

J'ai un problème dans le configuration de cluster au niveau de DataSource.
J'ai deux bases de données (MySQL) Replication Master Master.

Dans la configuration ci-dessous, sans cluster l'application fonctionne correctement.

Configuration de data-source:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
    <bean id="mydataSource" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/zaas?autoReconnect=true"/>
        <property name="username" value="root"/>
        <property name="password" value="root"/>
        <property name="validationQuery" value="Select 1"></property>    
    </bean>

Une seule connexion est ouvert d'après la table PROCESSLIST de MySQL

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
mysql> show PROCESSLIST;
+----+------+-----------------+------+---------+------+-------+------------------+
| Id | User | Host            | db   | Command | Time | State | Info             |
+----+------+-----------------+------+---------+------+-------+------------------+
| 50 | root | localhost:51096 | zaas | Sleep   |    2 |       | NULL             |
| 51 | root | localhost       | NULL | Query   |    0 | NULL  | show PROCESSLIST |
+----+------+-----------------+------+---------+------+-------+------------------+
2 rows in set (0.00 sec)

Les problèmes commencent lors de l'ajout de la configuration de cluster comme suit :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
    <bean id="mydataSource" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost,192.168.1.161:3306/zaas?autoCommit=true&amp;autoReconnect=true&amp;failOverReadOnly=false"/>
        <property name="username" value="root"/>
        <property name="password" value="root"/>
        <property name="validationQuery" value="Select 1"></property>    
    </bean>

Le problème est que pour chaque requête, une connexion sera ouverte comme le montre la table PROCESSLIST :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> show PROCESSLIST;
+----+------+-----------------+------+---------+------+-------+------------------+
| Id | User | Host            | db   | Command | Time | State | Info             |
+----+------+-----------------+------+---------+------+-------+------------------+
| 51 | root | localhost       | NULL | Query   |    0 | NULL  | show PROCESSLIST |
| 53 | root | localhost:35600 | zaas | Sleep   |   94 |       | NULL             |
| 54 | root | localhost:35609 | zaas | Sleep   |   34 |       | NULL             |
| 55 | root | localhost:60089 | zaas | Sleep   |    5 |       | NULL             |
| 56 | root | localhost:60093 | zaas | Sleep   |    4 |       | NULL             |
| 57 | root | localhost:60094 | zaas | Sleep   |    4 |       | NULL             |
| 58 | root | localhost:60095 | zaas | Sleep   |    4 |       | NULL             |
| 59 | root | localhost:60096 | zaas | Sleep   |    3 |       | NULL             |
| 60 | root | localhost:60097 | zaas | Sleep   |    3 |       | NULL             |
| 61 | root | localhost:60098 | zaas | Sleep   |    3 |       | NULL             |
| 62 | root | localhost:60099 | zaas | Sleep   |    3 |       | NULL             |
| 63 | root | localhost:60100 | zaas | Sleep   |    2 |       | NULL             |
| 64 | root | localhost:60101 | zaas | Sleep   |    2 |       | NULL             |
+----+------+-----------------+------+---------+------+-------+------------------+
13 rows in set (0.00 sec)

Si le nombre de connexions dépasse les 100 connexions (la valeur par défaut de MySQL), nous avons un problème de "Too many connection"

Si nous nous arrêtons la bases de données localhost, le serveur utilise la deuxième base de données mais avec une seule connexion ouverte comme le montre la table PROCESSLIST :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
mysql> show PROCESSLIST;
+-----+------+------------------------------+------+---------+------+-------+------------------+
| Id  | User | Host                         | db   | Command | Time | State | Info             |
+-----+------+------------------------------+------+---------+------+-------+------------------+
| 372 | root | wissem-desktop-2.local:47227 | zaas | Sleep   |   36 |       | NULL             |
| 374 | root | wissem-desktop-2.local:47299 | NULL | Query   |    0 | NULL  | show PROCESSLIST |
+-----+------+------------------------------+------+---------+------+-------+------------------+
2 rows in set (0.00 sec)

Avec une exception dans le log

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
Thu Sep 13 11:24:15 CEST 2012 WARN: Connection to primary host failed
 
EXCEPTION STACK TRACE:
 
 
 
** BEGIN NESTED EXCEPTION ** 
 
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException
MESSAGE: Communications link failure
 
The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
 
STACKTRACE:
 
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
 
The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
    at sun.reflect.GeneratedConstructorAccessor71.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1118)
    at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:343)
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2308)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2122)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:774)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:49)
    at sun.reflect.GeneratedConstructorAccessor62.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:375)
    at com.mysql.jdbc.LoadBalancingConnectionProxy.createConnectionForHost(LoadBalancingConnectionProxy.java:320)
    at com.mysql.jdbc.FailoverConnectionProxy.createPrimaryConnection(FailoverConnectionProxy.java:132)
    at com.mysql.jdbc.FailoverConnectionProxy.invoke(FailoverConnectionProxy.java:122)
    at $Proxy21.setAutoCommit(Unknown Source)
    at org.apache.commons.dbcp.DelegatingConnection.setAutoCommit(DelegatingConnection.java:371)
    at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.setAutoCommit(PoolingDataSource.java:328)
    at org.hibernate.transaction.JDBCTransaction.toggleAutoCommit(JDBCTransaction.java:228)
    at org.hibernate.transaction.JDBCTransaction.commitAndResetAutoCommit(JDBCTransaction.java:173)
    at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:146)
    at org.springframework.orm.hibernate3.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:656)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:754)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:723)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:412)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:118)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
    at $Proxy39.searchByType(Unknown Source)
    at tn.alpha.zaas.web.admin.ListDomainBean.initDataTable2(ListDomainBean.java:266)
    at tn.alpha.zaas.web.admin.ListDomainBean.init2(ListDomainBean.java:260)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor$LifecycleElement.invoke(InitDestroyAnnotationBeanPostProcessor.java:340)
    at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor$LifecycleMetadata.invokeInitMethods(InitDestroyAnnotationBeanPostProcessor.java:293)
    at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor.postProcessBeforeInitialization(InitDestroyAnnotationBeanPostProcessor.java:130)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.applyBeanPostProcessorsBeforeInitialization(AbstractAutowireCapableBeanFactory.java:394)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1396)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:513)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:450)
    at org.springframework.beans.factory.support.AbstractBeanFactory$2.getObject(AbstractBeanFactory.java:328)
    at org.springframework.web.context.request.AbstractRequestAttributesScope.get(AbstractRequestAttributesScope.java:43)
    at org.springframework.web.context.request.SessionScope.get(SessionScope.java:92)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:324)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:189)
    at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1044)
    at org.springframework.beans.factory.access.el.SpringBeanELResolver.getValue(SpringBeanELResolver.java:56)
    at javax.el.CompositeELResolver.getValue(CompositeELResolver.java:53)
    at com.sun.faces.el.FacesCompositeELResolver.getValue(FacesCompositeELResolver.java:64)
    at org.apache.el.parser.AstIdentifier.getValue(AstIdentifier.java:45)
    at org.apache.el.parser.AstValue.getValue(AstValue.java:86)
    at org.apache.el.ValueExpressionImpl.getValue(ValueExpressionImpl.java:186)
    at com.sun.facelets.el.TagValueExpression.getValue(TagValueExpression.java:71)
    at javax.faces.component.UIOutput.getValue(UIOutput.java:173)
    at com.sun.faces.renderkit.html_basic.MenuRenderer.getCurrentSelectedValues(MenuRenderer.java:651)
    at com.sun.faces.renderkit.html_basic.MenuRenderer.renderOption(MenuRenderer.java:524)
    at com.sun.faces.renderkit.html_basic.MenuRenderer.renderOptions(MenuRenderer.java:757)
    at com.sun.faces.renderkit.html_basic.MenuRenderer.renderSelect(MenuRenderer.java:811)
    at com.sun.faces.renderkit.html_basic.MenuRenderer.encodeEnd(MenuRenderer.java:335)
    at javax.faces.component.UIComponentBase.encodeEnd(UIComponentBase.java:833)
    at javax.faces.component.UIComponent.encodeAll(UIComponent.java:896)
    at javax.faces.render.Renderer.encodeChildren(Renderer.java:137)
    at javax.faces.component.UIComponentBase.encodeChildren(UIComponentBase.java:809)
    at javax.faces.component.UIComponent.encodeAll(UIComponent.java:886)
    at javax.faces.component.UIComponent.encodeAll(UIComponent.java:892)
    at com.sun.facelets.FaceletViewHandler.renderView(FaceletViewHandler.java:571)
    at org.ajax4jsf.application.ViewHandlerWrapper.renderView(ViewHandlerWrapper.java:100)
    at org.ajax4jsf.application.AjaxViewHandler.renderView(AjaxViewHandler.java:176)
    at com.sun.faces.lifecycle.RenderResponsePhase.execute(RenderResponsePhase.java:106)
    at com.sun.faces.lifecycle.LifecycleImpl.phase(LifecycleImpl.java:251)
    at com.sun.faces.lifecycle.LifecycleImpl.render(LifecycleImpl.java:144)
    at javax.faces.webapp.FacesServlet.service(FacesServlet.java:245)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.ajax4jsf.webapp.BaseXMLFilter.doXmlFilter(BaseXMLFilter.java:206)
    at org.ajax4jsf.webapp.BaseFilter.handleRequest(BaseFilter.java:290)
    at org.ajax4jsf.webapp.BaseFilter.processUploadsAndHandleRequest(BaseFilter.java:388)
    at org.ajax4jsf.webapp.BaseFilter.doFilter(BaseFilter.java:515)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at tn.alpha.zaas.web.util.filter.SessionTimeoutFilter.doFilter(SessionTimeoutFilter.java:62)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:343)
    at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:109)
    at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:83)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
    at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:97)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
    at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:100)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
    at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:78)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
    at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:54)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
    at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:35)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
    at org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilter(BasicAuthenticationFilter.java:177)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
    at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:188)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
    at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:105)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
    at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:79)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
    at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:149)
    at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:237)
    at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:167)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:230)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:104)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:261)
    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
    at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:581)
    at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
    at java.lang.Thread.run(Thread.java:619)
Caused by: java.net.ConnectException: Connection refused
    at java.net.PlainSocketImpl.socketConnect(Native Method)
    at java.net.PlainSocketImpl.doConnect(PlainSocketImpl.java:333)
    at java.net.PlainSocketImpl.connectToAddress(PlainSocketImpl.java:195)
    at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:182)
    at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:366)
    at java.net.Socket.connect(Socket.java:519)
    at java.net.Socket.connect(Socket.java:469)
    at java.net.Socket.<init>(Socket.java:366)
    at java.net.Socket.<init>(Socket.java:209)
    at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:256)
    at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:292)
    ... 119 more
 
 
** END NESTED EXCEPTION **

Est-ce la bonne façon de configurer un cluster ?
L'url de configuration de DataSource : http://dev.mysql.com/doc/refman/5.0/...roperties.html

J'ai essayé plusieurs configurations :
- JNDI
- la configuration c3p0 comme suit:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
    <bean id="mydataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"     destroy-method="close">
        <property name="driverClass" value="com.mysql.jdbc.Driver"/>
        <property name="jdbcUrl"  value="jdbc:mysql://localhost:3306,192.168.1.161:3306/zaas?autoCommit=true&amp;autoReconnect=true&amp;autoReconnectForPools=true&amp;failOverReadOnly=false"/>
        <property name="user" value="root" />
        <property name="password" value="root" /> 
        <!-- these are C3P0 properties -->
        <!-- property name="acquireIncrement" value="${acquireIncrement}" /_-->
        <property name="minPoolSize" value="6" />
        <property name="maxPoolSize" value="10" />
        <property name="maxIdleTime" value="100" />
    </bean>

Quelqu'un saurait-il m'indiquer d'où peut venir le problème ?

Merci d'avance pour votre aide.

**NicoL__** · 18/10/2012, 11h14

Je suis pas certain que cela soit ce que tu veux mais peut-être qu'il faut utiliser :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
jdbc:mysql:loadbalance://localhost,192.168.1.161:3306/zaas?autoCommit=true&amp;autoReconnect=true&amp;failOverReadOnly=false;roundRobinLoadBalance=true

[Hibernate / Spring / Cluster / JDBC] Erreur "Too many connections"

Persistance des données Java

Discussions similaires

Partager

Partager