I'm trying to set up an high available MySQL "cluster", using a Master / Slave Replication configuration.
I want to the slave become master (e.g. not read-only but writeable) if the first node come down (like in this question : http://stackoverflow.com/questions/9409111/database-fail-over-in-jboss-data-sources or in my previous question ).
My original post ( JBoss/Alfresco cannot write on slave after failover ) has too much evolved (even if the configuration is quite the same). You can refer it to see more conf stuff.
<?xml version="1.0" encoding="UTF-8"?>
<datasources>
<local-tx-datasource>
<jndi-name>alfresco-datasource</jndi-name>
<connection-url>
jdbc:mysql://10.1.2.13:3306,10.1.2.14:3306/alfresco?user=alfresco&password=alfresco&autoReconnect=true&failOverReadOnly=false
</connection-url>
<driver-class>com.mysql.jdbc.Driver</driver-class>
<user-name>alfresco</user-name>
<password>alfresco</password>
<exception-sorter-class-name>
org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter
</exception-sorter-class-name>
<connection-property name="readOnly">false</connection-property>
<failOverReadOnly>false</failOverReadOnly>
<autoReconnect>true</autoReconnect>
<connection-property name="autoReconnect">true</connection-property>
<check-valid-connection-sql>/* ping */ SELECT 1</check-valid-connection-sql>
<valid-connection-checker-class-name>
org.jboss.resource.adapter.jdbc.vendor.MySQLValidConnectionChecker
</valid-connection-checker-class-name>
<initialTimeout>20</initialTimeout>
<min-pool-size>5</min-pool-size>
<!-- Don't set this any higher than max_connections on your
MySQL server, usually this should be a 10 or a few 10's
of connections, not hundreds or thousands -->
<max-pool-size>40</max-pool-size>
<!-- If you're using Connector/J 3.1.8 or newer, you can use our implementation
of these to increase the robustness "mysql-ds.xml" 64L, 3683C of the connection
pool. -->
<exception-sorter-class-name>
com.mysql.jdbc.integration.jboss.ExtendedMysqlExceptionSorter
</exception-sorter-class-name>
<valid-connection-checker-class-name>
com.mysql.jdbc.integration.jboss.MysqlValidConnectionChecker
</valid-connection-checker-class-name>
<metadata>
<type-mapping>mySQL</type-mapping>
</metadata>
</local-tx-datasource>
</datasources>
I'm aware my configuration is a bit redundant (user, password, readOnly,...)
I tried many many configuration, but the result is everytime the same for my application (Alfresco 4.0.1, which is using ibatis).
17:33:01,357 WARN [JBossManagedConnectionPool] Throwable while trying to match ManagedConnection, destroying connection: org.jboss.resource.connectionmanager.TxConnectionManager$TxConnectionEventListener@7610cca9[state=NORMAL mc=org.jboss.resource.adapter.jdbc.local.LocalManagedConnection@14669c8b handles=0 lastUse=1340724780486 permit=false trackByTx=false mcp=org.jboss.resource.connectionmanager.JBossManagedConnectionPool$OnePool@7da2391a context=org.jboss.resource.connectionmanager.InternalManagedConnectionPool@25531a66 xaResource=org.jboss.resource.connectionmanager.TxConnectionManager$LocalXAResource@5caf7423 txSync=null]
java.lang.reflect.UndeclaredThrowableException
at $Proxy259.toString(Unknown Source)
at java.lang.String.valueOf(String.java:2826)
at java.lang.StringBuilder.append(StringBuilder.java:115)
at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.checkValid(BaseWrapperManagedConnection.java:330)
at org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory.matchManagedConnections(LocalManagedConnectionFactory.java:402)
at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.getConnection(InternalManagedConnectionPool.java:225)
at org.jboss.resource.connectionmanager.JBossManagedConnectionPool$BasePool.getConnection(JBossManagedConnectionPool.java:690)
at org.jboss.resource.connectionmanager.BaseConnectionManager2.getManagedConnection(BaseConnectionManager2.java:404)
at org.jboss.resource.connectionmanager.TxConnectionManager.getManagedConnection(TxConnectionManager.java:424)
at org.jboss.resource.connectionmanager.BaseConnectionManager2.allocateConnection(BaseConnectionManager2.java:496)
at org.jboss.resource.connectionmanager.BaseConnectionManager2$ConnectionManagerProxy.allocateConnection(BaseConnectionManager2.java:941)
at org.jboss.resource.adapter.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:89)
at org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection(LocalDataSourceConnectionProvider.java:81)
at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:423)
at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:144)
at org.hibernate.jdbc.JDBCContext.connection(JDBCContext.java:119)
at org.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:57)
at org.hibernate.impl.SessionImpl.beginTransaction(SessionImpl.java:1326)
at org.springframework.orm.hibernate3.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:555)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:371)
at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:335)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:105)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
at $Proxy345.registerComponent(Unknown Source)
at org.alfresco.repo.module.AbstractModuleComponent.init(AbstractModuleComponent.java:318)
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.support.AbstractAutowireCapableBeanFactory.invokeCustomInitMethod(AbstractAutowireCapableBeanFactory.java:1544)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1485)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1417)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:519)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:456)
at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:291)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:288)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:190)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:580)
at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:895)
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:425)
at org.springframework.web.context.ContextLoader.createWebApplicationContext(ContextLoader.java:276)
at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:197)
at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:47)
at org.alfresco.web.app.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:63)
at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:3910)
at org.apache.catalina.core.StandardContext.start(StandardContext.java:4389)
at org.jboss.web.tomcat.service.deployers.TomcatDeployment.performDeployInternal(TomcatDeployment.java:321)
at org.jboss.web.tomcat.service.deployers.TomcatDeployment.performDeploy(TomcatDeployment.java:145)
at org.jboss.web.deployers.AbstractWarDeployment.start(AbstractWarDeployment.java:461)
at org.jboss.web.deployers.WebModule.startModule(WebModule.java:122)
at org.jboss.web.deployers.WebModule.start(WebModule.java:97)
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.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:157)
at org.jboss.mx.server.Invocation.dispatch(Invocation.java:96)
at org.jboss.mx.server.Invocation.invoke(Invocation.java:88)
at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:264)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:668)
at org.jboss.system.microcontainer.ServiceProxy.invoke(ServiceProxy.java:206)
at $Proxy38.start(Unknown Source)
at org.jboss.system.microcontainer.StartStopLifecycleAction.installAction(StartStopLifecycleAction.java:42)
at org.jboss.system.microcontainer.StartStopLifecycleAction.installAction(StartStopLifecycleAction.java:37)
at org.jboss.dependency.plugins.action.SimpleControllerContextAction.simpleInstallAction(SimpleControllerContextAction.java:62)
at org.jboss.dependency.plugins.action.AccessControllerContextAction.install(AccessControllerContextAction.java:71)
at org.jboss.dependency.plugins.AbstractControllerContextActions.install(AbstractControllerContextActions.java:51)
at org.jboss.dependency.plugins.AbstractControllerContext.install(AbstractControllerContext.java:348)
at org.jboss.system.microcontainer.ServiceControllerContext.install(ServiceControllerContext.java:297)
at org.jboss.dependency.plugins.AbstractController.install(AbstractController.java:1652)
at org.jboss.dependency.plugins.AbstractController.incrementState(AbstractController.java:938)
at org.jboss.dependency.plugins.AbstractController.resolveContexts(AbstractController.java:1082)
at org.jboss.dependency.plugins.AbstractController.resolveContexts(AbstractController.java:988)
at org.jboss.dependency.plugins.AbstractController.change(AbstractController.java:826)
at org.jboss.dependency.plugins.AbstractController.change(AbstractController.java:556)
at org.jboss.system.ServiceController.doChange(ServiceController.java:688)
at org.jboss.system.ServiceController.start(ServiceController.java:460)
at org.jboss.system.deployers.ServiceDeployer.start(ServiceDeployer.java:163)
at org.jboss.system.deployers.ServiceDeployer.deploy(ServiceDeployer.java:99)
at org.jboss.system.deployers.ServiceDeployer.deploy(ServiceDeployer.java:46)
at org.jboss.deployers.spi.deployer.helpers.AbstractSimpleRealDeployer.internalDeploy(AbstractSimpleRealDeployer.java:62)
at org.jboss.deployers.spi.deployer.helpers.AbstractRealDeployer.deploy(AbstractRealDeployer.java:55)
at org.jboss.deployers.plugins.deployers.DeployerWrapper.deploy(DeployerWrapper.java:179)
at org.jboss.deployers.plugins.deployers.DeployersImpl.doDeploy(DeployersImpl.java:1454)
at org.jboss.deployers.plugins.deployers.DeployersImpl.doInstallParentFirst(DeployersImpl.java:1172)
at org.jboss.deployers.plugins.deployers.DeployersImpl.doInstallParentFirst(DeployersImpl.java:1193)
at org.jboss.deployers.plugins.deployers.DeployersImpl.install(DeployersImpl.java:1113)
at org.jboss.dependency.plugins.AbstractControllerContext.install(AbstractControllerContext.java:348)
at org.jboss.dependency.plugins.AbstractController.install(AbstractController.java:1652)
at org.jboss.dependency.plugins.AbstractController.incrementState(AbstractController.java:938)
at org.jboss.dependency.plugins.AbstractController.resolveContexts(AbstractController.java:1082)
at org.jboss.dependency.plugins.AbstractController.resolveContexts(AbstractController.java:988)
at org.jboss.dependency.plugins.AbstractController.change(AbstractController.java:826)
at org.jboss.dependency.plugins.AbstractController.change(AbstractController.java:556)
at org.jboss.deployers.plugins.deployers.DeployersImpl.process(DeployersImpl.java:789)
at org.jboss.deployers.plugins.main.MainDeployerImpl.process(MainDeployerImpl.java:699)
at org.jboss.system.server.profileservice.repository.MainDeployerAdapter.process(MainDeployerAdapter.java:117)
at org.jboss.system.server.profileservice.repository.ProfileDeployAction.install(ProfileDeployAction.java:70)
at org.jboss.system.server.profileservice.repository.AbstractProfileAction.install(AbstractProfileAction.java:53)
at org.jboss.system.server.profileservice.repository.AbstractProfileService.install(AbstractProfileService.java:403)
at org.jboss.dependency.plugins.AbstractControllerContext.install(AbstractControllerContext.java:348)
at org.jboss.dependency.plugins.AbstractController.install(AbstractController.java:1652)
at org.jboss.dependency.plugins.AbstractController.incrementState(AbstractController.java:938)
at org.jboss.dependency.plugins.AbstractController.resolveContexts(AbstractController.java:1082)
at org.jboss.dependency.plugins.AbstractController.resolveContexts(AbstractController.java:988)
at org.jboss.dependency.plugins.AbstractController.install(AbstractController.java:778)
at org.jboss.dependency.plugins.AbstractController.install(AbstractController.java:543)
at org.jboss.system.server.profileservice.repository.AbstractProfileService.registerProfile(AbstractProfileService.java:308)
at org.jboss.system.server.profileservice.ProfileServiceBootstrap.start(ProfileServiceBootstrap.java:256)
at org.jboss.bootstrap.AbstractServerImpl.start(AbstractServerImpl.java:461)
at org.jboss.Main.boot(Main.java:221)
at org.jboss.Main$1.run(Main.java:556)
at java.lang.Thread.run(Thread.java:662)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed. Connection closed due to inability to ping any active connections.
at sun.reflect.GeneratedConstructorAccessor270.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:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1013)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
at com.mysql.jdbc.LoadBalancingConnectionProxy.invoke(LoadBalancingConnectionProxy.java:525)
at com.mysql.jdbc.FailoverConnectionProxy.invoke(FailoverConnectionProxy.java:129)
... 115 more
What is wrong with my configuration ?
Obivously, my MySQL servers are fully onlines, and I don't have any problem when I don't use failover url connection.
Indeed, is it important ? It seems to be just a WARN, but I'm quite worried about database integrity.
