Note that there are some explanatory texts on larger screens.

plurals
  1. POMSSQL JDBC driver won't connect to mirror failoverPartner on first connect
    text
    copied!<p>I'm using C3P0 and the MS SQL JDBC 4 driver to automatically failover to a new database mirror when the database goes away. If it first connects to the principal DB, then the failover works and it switches seamlessly to the mirror DB. However, if the principal DB is down when the application starts, and the mirror DB is available to connect (tested with MSSQL Studio), then the application fails to start and fails to connect to the backup mirror. </p> <p>Here is the connection URL:</p> <pre><code>jdbc:sqlserver://PRINCIPALDB;databaseName=app_space;port=99999;failoverPartner=MIRRORDB </code></pre> <p>I have <code>c3p0.testConnectionOnCheckout</code> and <code>c3p0.preferredTestQuery</code> set, and <code>c3p0.acquireRetryAttempts</code> is NOT set (using default of 30).</p> <p>Why won't it connect to mirror DB initially when principal is down? We need this because if power went down or something and principal DB is down, and app server needs recycling, then failover won't help.</p> <p>Reference: </p> <p><a href="http://www.mchange.com/projects/c3p0/#configuring_recovery">http://www.mchange.com/projects/c3p0/#configuring_recovery</a></p> <p><a href="http://msdn.microsoft.com/en-US/library/aa342332%28v=sql.90%29">Using Database Mirroring (JDBC)</a> (MSDN uses unescaped parenthesis in their URLs!) <a href="http://msdn.microsoft.com/en-US/library/aa342332(v=sql.90)">http://msdn.microsoft.com/en-US/library/aa342332(v=sql.90)</a></p> <hr> <p>Here are some logs from the app.</p> <pre><code>&lt;14&gt;[APP]: INFO 20 Jul 2012 12:21:21,982 [main] net.sf.hibernate.connection.C3P0ConnectionProvider "C3P0 using driver: com.microsoft.sqlserver.jdbc.SQLServerDriver at URL: jdbc:sqlserver://PRINCIPAL;databaseName=APP_space;port=9999;failoverPartner=MIRRORDB" &lt;14&gt;[APP]: INFO 20 Jul 2012 12:21:21,982 [main] net.sf.hibernate.connection.C3P0ConnectionProvider "Connection properties: {user=USERNAME, password=PASSWORD}" &lt;14&gt;[APP]: INFO 20 Jul 2012 12:21:22,435 [main] net.sf.hibernate.transaction.TransactionFactoryFactory "Transaction strategy: net.sf.hibernate.transaction.JDBCTransactionFactory" &lt;14&gt;[APP]: INFO 20 Jul 2012 12:21:22,450 [main] net.sf.hibernate.transaction.TransactionManagerLookupFactory "No TransactionManagerLookup configured (in JTA environment, use of process level read-write cache is not recommended)" &lt;12&gt;[APP]: WARN 20 Jul 2012 12:29:17,279 [main] net.sf.hibernate.cfg.SettingsFactory "Could not obtain connection metadata" &lt;12&gt;java.sql.SQLException: Connections could not be acquired from the underlying database! &lt;12&gt; at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:106) &lt;12&gt; at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:529) &lt;12&gt; at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:128) &lt;12&gt; at net.sf.hibernate.connection.C3P0ConnectionProvider.getConnection(C3P0ConnectionProvider.java:33) &lt;12&gt; at net.sf.hibernate.cfg.SettingsFactory.buildSettings(SettingsFactory.java:84) </code></pre> <p>And here is a different type of error that it sometimes gives, with a deadlock warning.</p> <pre><code>&lt;14&gt;[APP]: INFO 20 Jul 2012 18:05:43,049 [main] net.sf.hibernate.connection.C3P0ConnectionProvider "C3P0 using driver: com.microsoft.sqlserver.jdbc.SQLServerDriver at URL: jdbc:sqlserver://PRINCIPALDB:9999;databaseName=APP_space;failoverPartner=MIRRORDB:9999" &lt;14&gt;[APP]: INFO 20 Jul 2012 18:05:43,049 [main] net.sf.hibernate.connection.C3P0ConnectionProvider "Connection properties: {user=USERNAME, password=PASSWORD}" &lt;14&gt;[APP]: INFO 20 Jul 2012 18:05:43,190 [main] com.mchange.v2.log.MLog "MLog clients using log4j logging." &lt;14&gt;[APP]: INFO 20 Jul 2012 18:05:43,518 [main] com.mchange.v2.c3p0.C3P0Registry "Initializing c3p0-0.9.1.2 [built 21-May-2007 15:04:56; debug? true; trace: 10]" &lt;14&gt;[APP]: INFO 20 Jul 2012 18:05:43,612 [main] net.sf.hibernate.transaction.TransactionFactoryFactory "Transaction strategy: net.sf.hibernate.transaction.JDBCTransactionFactory" &lt;14&gt;[APP]: INFO 20 Jul 2012 18:05:43,612 [main] net.sf.hibernate.transaction.TransactionManagerLookupFactory "No TransactionManagerLookup configured (in JTA environment, use of process level read-write cache is not recommended)" &lt;14&gt;[APP]: INFO 20 Jul 2012 18:05:43,658 [main] com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource "Initializing c3p0 pool... com.mchange.v2.c3p0.PoolBackedDataSource@616301db [ connectionPoolDataSource -&gt; com.mchange.v2.c3p0.WrapperConnectionPoolDataSource@d6ed198b [ acquireIncrement -&gt; 5, acquireRetryAttempts -&gt; 30, acquireRetryDelay -&gt; 1000, autoCommitOnClose -&gt; false, automaticTestTable -&gt; null, breakAfterAcquireFailure -&gt; false, checkoutTimeout -&gt; 0, connectionCustomizerClassName -&gt; null, connectionTesterClassName -&gt; com.mchange.v2.c3p0.impl.DefaultConnectionTester, debugUnreturnedConnectionStackTraces -&gt; false, factoryClassLocation -&gt; null, forceIgnoreUnresolvedTransactions -&gt; false, identityToken -&gt; 1bqq23w8o1a6dec41cwe1cd|20e1bfee, idleConnectionTestPeriod -&gt; 100, initialPoolSize -&gt; 10, maxAdministrativeTaskTime -&gt; 0, maxConnectionAge -&gt; 0, maxI... &lt;14&gt;...dleTime -&gt; 3600, maxIdleTimeExcessConnections -&gt; 0, maxPoolSize -&gt; 150, maxStatements -&gt; 1000, maxStatementsPerConnection -&gt; 0, minPoolSize -&gt; 10, nestedDataSource -&gt; com.mchange.v2.c3p0.DriverManagerDataSource@2c0fb781 [ description -&gt; null, driverClass -&gt; null, factoryClassLocation -&gt; null, identityToken -&gt; 1bqq23w8o1a6dec41cwe1cd|20360e46, jdbcUrl -&gt; jdbc:sqlserver://PRINCIPALDB:9999;databaseName=APP_space;failoverPartner=MIRRORDB:9999, properties -&gt; {user=******, password=******} ], preferredTestQuery -&gt; select * from CLUSTERSAFETY, propertyCycle -&gt; 0, testConnectionOnCheckin -&gt; false, testConnectionOnCheckout -&gt; false, unreturnedConnectionTimeout -&gt; 0, usesTraditionalReflectiveProxies -&gt; false; userOverrides: {} ], dataSourceName -&gt; null, factoryClassLocation -&gt; null, identityToken -&gt; 1bqq23w8o1a6dec41cwe1cd|6f3e49a8, numHelperThreads -&gt; 3 ]" &lt;12&gt;[APP]: WARN 20 Jul 2012 18:06:03,644 [Timer-0] com.mchange.v2.async.ThreadPoolAsynchronousRunner "com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@37f844f7 -- APPARENT DEADLOCK!!! Creating emergency threads for unassigned pending tasks!" &lt;12&gt;[APP]: WARN 20 Jul 2012 18:06:03,644 [Timer-0] com.mchange.v2.async.ThreadPoolAsynchronousRunner "com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@37f844f7 -- APPARENT DEADLOCK!!! Complete Status: Managed Threads: 3 Active Threads: 3 Active Tasks: com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@52783859 (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#0) com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@52bb855b (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#1) com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@153043cc (com.mchange.v2.asyn... &lt;12&gt;...c.ThreadPoolAsynchronousRunner$PoolThread-#2) Pending Tasks: </code></pre> <p>I ran a test program from the documentation with this connection:</p> <pre><code>jdbc:sqlserver://PRINCIPALDB:9999;databaseName=APP_space;portNumber=9999;failoverPartner=MIRRORDB:9999 </code></pre> <p>and it throw this exception, like it was trying a different port than I specified!</p> <pre><code>Connection to principal server failed, trying the mirror server. com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host MIRRORDB:9999, port 1433 has failed. Error: "null. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.". at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190) </code></pre> <p>The important point being that it tried to connect to port 1433 instead of the port that I specified, many different ways.</p>
 

Querying!

 
Guidance

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload