I am adding HA-JDBC support to an existing Tomcat application. I am testing
with 1 Tomcat writing to 4 local MySQL schemas on the same Windows PC with a
plan to move to 4 Tomcats + 4 Databases later.
Under heavy load, HA-JDBC works fine for my initial setup if I use MySQL
StandardSocketFactory. All 4 databases remain in sync in spite of 150
concurrent threads doing high volume C/R/U/D operations on my schema.
However, if I switch to MySQL NamedPipeSocketFactory, one of my 4 HA-JDBC
databases always becomes inactive at the start of the load peak.
I need help to resolve it. I have some theories to frame the discussion.
Server load in the product is unpredictable. To improve Tomcat DBCP
reliability in non-clustered mode, I use
connectionProperties="autoReconnect=true" to automatically fix broken or busy
connections under heavy load, and validationQuery="SELECT 1" to proactively
find and replace stale connections during slow periods.
DBCP with StandardSocketFactory (TCP/IP) does not experience any JDBC
connection loss for a local database, only for remote database, so that is why
StandardSocketFactory worked in my test with 4 local MySQL schemas. However,
NamedPipeSocketFactory can and will experience busy .\NamedPipe\mysql errors
which are silectly fixed if connectionProperties="autoReconnect=true". I think
HA-JDBC is inactivating my database if I use NamedPipeSocketFactory due to
those busy NamedPipe errors, even though I want MySQL Connector/J to handle
them transparently to HA-JDBC.
Here is my configuration. I use the InitialContext variables to
programmatically add databases to DriverDatabaseClusterMBean (not using
DataSources):
If possible, I want my HA-JDBC DataSource to do the validationQuery on my
databases to recover broken/busy sockets automatically. Is it ignoring my
autoReconnect=true for my database URLs? Does it ignore my validationQuery, or
will it run it against each database individually?
Do I have to switch to DataSourceDatabaseClusterMBean to allow Tomcat DBCP to
transparently manage broken/busy/idle connection recovery without affecting
HA-JDBC failure detection?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I turned on HA-JDBC logging in log4j.properties. Stack traces are below.
Basically MySQL autoReconnect is not working with my HA-JDBC configuration,
even though I specified it in my database URLs. The autoReconnect feature
works if using MySQL Connector/J directly instead of using HA-JDBC proxy.
Is there a way to fix my configuration? How can I get autoReconnect=true
working with DriverDatabaseClusterMBean and databases? I want to avoid using
DataSourceDatabaseClusterMBean and DataSources if possible (because I already
implemented with DriverDatabaseClusterMBean), so how can I enable
autoReconnect to avoid prematurely deactivating my database?
WARN (AbstractDatabaseCluster.java:236) Database my_schema2 from cluster
myCluster is not responding.
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.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAcce
ssorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstru
ctorAccessorImpl.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.GeneratedConstructorAccessor17.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstru
ctorAccessorImpl.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.NonRegisteringDriver.connect(NonRegisteringDriver.java:289)
at net.sf.hajdbc.sql.DriverDatabase.connect(DriverDatabase.java:123)
at net.sf.hajdbc.sql.DriverDatabase.connect(DriverDatabase.java:40)
at net.sf.hajdbc.sql.AbstractDatabaseCluster.test(AbstractDatabaseCluster.java
:248)
at net.sf.hajdbc.sql.AbstractDatabaseCluster.isAlive(AbstractDatabaseCluster.j
ava:230)
at net.sf.hajdbc.sql.AbstractDatabaseCluster$2.call(AbstractDatabaseCluster.ja
va:192)
at net.sf.hajdbc.sql.AbstractDatabaseCluster$2.call(AbstractDatabaseCluster.ja
va:189)
at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
at java.util.concurrent.FutureTask.run(FutureTask.java:138)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.j
ava:886)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:
908)
at java.lang.Thread.run(Thread.java:662)
Caused by: java.io.FileNotFoundException: \.\pipe\MySQL (All pipe instances
are busy)
at java.io.RandomAccessFile.open(Native Method)
at java.io.RandomAccessFile.<init>(RandomAccessFile.java:216)
at java.io.RandomAccessFile.<init>(RandomAccessFile.java:101)
at com.mysql.jdbc.NamedPipeSocketFactory$NamedPipeSocket.<init>(NamedPipeSocke
tFactory.java:58)
at
com.mysql.jdbc.NamedPipeSocketFactory.connect(NamedPipeSocketFactory.java:216)
at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:292)
... 21 more
ERROR (AbstractInvocationHandler.java:445) Database my_schema2 from cluster
myCluster was deactivated
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table
'my_schema2.mytable' doesn't exist
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I answered my own question. HA-JDBC is somehow blocking MySQL Connector/J from
getting my connectionProperty autoReconnect=true.
If autoReconnect=true is appended to URL it gets ignored. If
autoReconnect=true is in the semi-colon separated list of connectionProperties
then MySQL Connector/J handles all my reconnects for me and HA-JDBC never sees
the SQLException because it recovers.
This brings up a question I had about JDBC 4.0 support in HA-JDBC. Does it
differentiate between transient and permanent SQLException as per the
refactored JDBC 4.0 classes?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I assume HA-JDBC is discarding any connection properties appended to the url
instead of merging to the connectionProperties list. That is how the MySQL
Connector/J configuration works.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I am adding HA-JDBC support to an existing Tomcat application. I am testing
with 1 Tomcat writing to 4 local MySQL schemas on the same Windows PC with a
plan to move to 4 Tomcats + 4 Databases later.
Under heavy load, HA-JDBC works fine for my initial setup if I use MySQL
StandardSocketFactory. All 4 databases remain in sync in spite of 150
concurrent threads doing high volume C/R/U/D operations on my schema.
However, if I switch to MySQL NamedPipeSocketFactory, one of my 4 HA-JDBC
databases always becomes inactive at the start of the load peak.
I need help to resolve it. I have some theories to frame the discussion.
Server load in the product is unpredictable. To improve Tomcat DBCP
reliability in non-clustered mode, I use
connectionProperties="autoReconnect=true" to automatically fix broken or busy
connections under heavy load, and validationQuery="SELECT 1" to proactively
find and replace stale connections during slow periods.
DBCP with StandardSocketFactory (TCP/IP) does not experience any JDBC
connection loss for a local database, only for remote database, so that is why
StandardSocketFactory worked in my test with 4 local MySQL schemas. However,
NamedPipeSocketFactory can and will experience busy .\NamedPipe\mysql errors
which are silectly fixed if connectionProperties="autoReconnect=true". I think
HA-JDBC is inactivating my database if I use NamedPipeSocketFactory due to
those busy NamedPipe errors, even though I want MySQL Connector/J to handle
them transparently to HA-JDBC.
Here is my configuration. I use the InitialContext variables to
programmatically add databases to DriverDatabaseClusterMBean (not using
DataSources):
<Context path="/mycontextpath" reloadable="false">
<Environment type="java.lang.String" override="true" name="mysql_driverClassName" value="com.mysql.jdbc.Driver"/>
<Environment type="java.lang.String" override="true" name="mysql_connectionProperties" value="socketFactory=com.mysql.jdbc.NamedPip eSockeyFactory;alwaysSendSetIsolation=false"/>
<Environment type="java.lang.String" override="true" name="mysql_url1" value="jdbc:mysql://127.0.0.1:3306/myschema1?autoReconnect=true"/>
<Environment type="java.lang.String" override="true" name="mysql_url2" value="jdbc:mysql://127.0.0.1:3306/myschema2?autoReconnect=true"/>
<Environment type="java.lang.String" override="true" name="mysql_url3" value="jdbc:mysql://127.0.0.1:3306/myschema3?autoReconnect=true"/>
<Environment type="java.lang.String" override="true" name="mysql_url4" value="jdbc:mysql://127.0.0.1:3306/myschema4?autoReconnect=true"/>
<Resource name="jdbc/myDS" auth="Container" type="javax.sql.DataSource" <br="">maxActive="500" minIdle="5" maxIdle="25" maxWait="10000"
removeAbandoned="true" removeAbandonedTimeout="5" logAbandoned="true"
validationQuery="SELECT 1" testWhileIdle="true" timeBetweenEvictionRunsMillis="300000"
username="myusername" password="mypassword"
driverClassName="net.sf.hajdbc.sql.Driver" url="jdbc:ha-jdbc:cluster"/>
</Context>
If possible, I want my HA-JDBC DataSource to do the validationQuery on my
databases to recover broken/busy sockets automatically. Is it ignoring my
autoReconnect=true for my database URLs? Does it ignore my validationQuery, or
will it run it against each database individually?
Do I have to switch to DataSourceDatabaseClusterMBean to allow Tomcat DBCP to
transparently manage broken/busy/idle connection recovery without affecting
HA-JDBC failure detection?
I turned on HA-JDBC logging in log4j.properties. Stack traces are below.
Basically MySQL autoReconnect is not working with my HA-JDBC configuration,
even though I specified it in my database URLs. The autoReconnect feature
works if using MySQL Connector/J directly instead of using HA-JDBC proxy.
Is there a way to fix my configuration? How can I get autoReconnect=true
working with DriverDatabaseClusterMBean and databases? I want to avoid using
DataSourceDatabaseClusterMBean and DataSources if possible (because I already
implemented with DriverDatabaseClusterMBean), so how can I enable
autoReconnect to avoid prematurely deactivating my database?
WARN (AbstractDatabaseCluster.java:236) Database my_schema2 from cluster
myCluster is not responding.
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.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAcce
ssorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstru
ctorAccessorImpl.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.GeneratedConstructorAccessor17.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstru
ctorAccessorImpl.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.NonRegisteringDriver.connect(NonRegisteringDriver.java:289)
at net.sf.hajdbc.sql.DriverDatabase.connect(DriverDatabase.java:123)
at net.sf.hajdbc.sql.DriverDatabase.connect(DriverDatabase.java:40)
at net.sf.hajdbc.sql.AbstractDatabaseCluster.test(AbstractDatabaseCluster.java
:248)
at net.sf.hajdbc.sql.AbstractDatabaseCluster.isAlive(AbstractDatabaseCluster.j
ava:230)
at net.sf.hajdbc.sql.AbstractDatabaseCluster$2.call(AbstractDatabaseCluster.ja
va:192)
at net.sf.hajdbc.sql.AbstractDatabaseCluster$2.call(AbstractDatabaseCluster.ja
va:189)
at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
at java.util.concurrent.FutureTask.run(FutureTask.java:138)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.j
ava:886)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:
908)
at java.lang.Thread.run(Thread.java:662)
Caused by: java.io.FileNotFoundException: \.\pipe\MySQL (All pipe instances
are busy)
at java.io.RandomAccessFile.open(Native Method)
at java.io.RandomAccessFile.<init>(RandomAccessFile.java:216)
at java.io.RandomAccessFile.<init>(RandomAccessFile.java:101)
at com.mysql.jdbc.NamedPipeSocketFactory$NamedPipeSocket.<init>(NamedPipeSocke
tFactory.java:58)
at
com.mysql.jdbc.NamedPipeSocketFactory.connect(NamedPipeSocketFactory.java:216)
at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:292)
... 21 more
ERROR (AbstractInvocationHandler.java:445) Database my_schema2 from cluster
myCluster was deactivated
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table
'my_schema2.mytable' doesn't exist
I answered my own question. HA-JDBC is somehow blocking MySQL Connector/J from
getting my connectionProperty autoReconnect=true.
If autoReconnect=true is appended to URL it gets ignored. If
autoReconnect=true is in the semi-colon separated list of connectionProperties
then MySQL Connector/J handles all my reconnects for me and HA-JDBC never sees
the SQLException because it recovers.
This brings up a question I had about JDBC 4.0 support in HA-JDBC. Does it
differentiate between transient and permanent SQLException as per the
refactored JDBC 4.0 classes?
To be more clear, don't do this in your HA-JDBC if using DriverManager
databases configuration:
url="jdbc:mysql://127.0.0.1:3306/myschema1?autoReconnect=true"
connectionProperties="socketFactory=com.mysql.jdbc.NamedPipeSockeyFactory;alwa
ysSendSetIsolation=false"
Do this instead:
url="jdbc:mysql://127.0.0.1:3306/myschema"
connectionProperties="autoReconnect=true;socketFactory=com.mysql.jdbc.NamedPip
eSockeyFactory;alwaysSendSetIsolation=false"
I assume HA-JDBC is discarding any connection properties appended to the url
instead of merging to the connectionProperties list. That is how the MySQL
Connector/J configuration works.