Retry Connection to database

[Michael Simon]

Is there a setting in the dbconfig.xml or other place to tell JIRA to re-try the connection to the database ever x seconds/minutes?  Our DB is controlled by a different group with their own schedule for maintenance.  We're not always notified so we don't know when to bounce the Tomcat instance.  Users end up with the famous "JIRA failed to establish a connection to your database".,..  I don't recall having this in JIRA 6.  OH and I'm currently on JIRA 7.13.  Planning on going to 8.x eventually.

[Patrick · Accepted solution]

This should work out of the box. There is a connection Pool which detects failing connections and re-create them.

Here is our Config (Postgres):

<validation-query>select 1</validation-query>
<min-evictable-idle-time-millis>60000</min-evictable-idle-time-millis>
<time-between-eviction-runs-millis>300000</time-between-eviction-runs-millis>
<pool-max-idle>20</pool-max-idle>
<pool-remove-abandoned>true</pool-remove-abandoned>
<pool-remove-abandoned-timeout>300</pool-remove-abandoned-timeout>
<pool-test-on-borrow>false</pool-test-on-borrow>
<pool-test-while-idle>true</pool-test-while-idle>

[Michael Simon]

Thank you for the response.  Sorry it has taken me so long to get back to this posting.  I had implemented these and it's still happening.  JIRA is coming up when the DB is down or the DB is being bounced when JIRA is up.  I don't know the exact timing as it happens when I'm off shift.  The error JIRA is showing users is.

[Patrick]

Can you provide the settings.xml and the Stracktrace?

How looks your DB Setup? Do you have some kind of HA-Setup there?

[Michael Simon]

dbconfig.xml

<?xml version="1.0" encoding="UTF-8"?>

<jira-database-config>
<name>defaultDS</name>
<delegator-name>default</delegator-name>
<database-type>oracle10g</database-type>
<jdbc-datasource>
<url>jdbc:oracle:thin:@//db.company.com:5124/XXX.company.com</url>
<driver-class>oracle.jdbc.OracleDriver</driver-class>
<username>jira</username>
<password>hahaha</password>
<pool-min-size>20</pool-min-size>
<pool-max-size>50</pool-max-size>
<pool-max-wait>30000</pool-max-wait>
<pool-max-idle>15</pool-max-idle>
<pool-remove-abandoned>true</pool-remove-abandoned>
<pool-remove-abandoned-timeout>300</pool-remove-abandoned-timeout>

<validation-query>select 1 from dual</validation-query>
<min-evictable-idle-time-millis>60000</min-evictable-idle-time-millis>
<time-between-eviction-runs-millis>300000</time-between-eviction-runs-millis>

<pool-test-while-idle>true</pool-test-while-idle>
<pool-test-on-borrow>false</pool-test-on-borrow>
</jdbc-datasource>
</jira-database-config>

Don't know the exact type of HA we have.  Fail over with replication yes. But in this case no failover is required or happening. 

2020-04-18 21:27:01,962 Caesium-1-1 ERROR ServiceRunner [NoModule] There was an error getting a DBCP datasource.
java.lang.RuntimeException: Unable to obtain a connection from the underlying connection pool
at org.ofbiz.core.entity.jdbc.interceptors.connection.ConnectionTracker.trackConnection(ConnectionTracker.java:52)
at org.ofbiz.core.entity.transaction.DBCPConnectionFactory.trackConnection(DBCPConnectionFactory.java:283)
at org.ofbiz.core.entity.transaction.DBCPConnectionFactory.getConnection(DBCPConnectionFactory.java:80)
at org.ofbiz.core.entity.ConnectionFactory.tryGenericConnectionSources(ConnectionFactory.java:69)
at org.ofbiz.core.entity.transaction.JNDIFactory.getConnection(JNDIFactory.java:173)
at org.ofbiz.core.entity.TransactionFactory.getConnection(TransactionFactory.java:114)
at org.ofbiz.core.entity.ConnectionFactory.getConnection(ConnectionFactory.java:59)
at org.ofbiz.core.entity.jdbc.SQLProcessor.getConnection(SQLProcessor.java:340)
at org.ofbiz.core.entity.jdbc.SQLProcessor.prepareStatement(SQLProcessor.java:456)
at org.ofbiz.core.entity.GenericDAO.createEntityListIterator(GenericDAO.java:870)
at org.ofbiz.core.entity.GenericDAO.selectListIteratorByCondition(GenericDAO.java:857)
at org.ofbiz.core.entity.GenericHelperDAO.findListIteratorByCondition(GenericHelperDAO.java:216)
at org.ofbiz.core.entity.GenericDelegator.findListIteratorByCondition(GenericDelegator.java:1243)
at com.atlassian.jira.ofbiz.DefaultOfBizDelegator.findListIteratorByCondition(DefaultOfBizDelegator.java:398)
at com.atlassian.jira.ofbiz.WrappingOfBizDelegator.findListIteratorByCondition(WrappingOfBizDelegator.java:278)
at com.atlassian.jira.entity.SelectQueryImpl$ExecutionContextImpl.forEach(SelectQueryImpl.java:227)
at com.atlassian.jira.entity.SelectQueryImpl$ExecutionContextImpl.asList(SelectQueryImpl.java:164)
at com.atlassian.jira.scheduler.OfBizRunDetailsDao.getUnsuccessfulIdsForJob(OfBizRunDetailsDao.java:148)
at com.atlassian.jira.scheduler.OfBizRunDetailsDao.addRunDetails(OfBizRunDetailsDao.java:111)
at com.atlassian.scheduler.core.AbstractSchedulerService.addRunDetails(AbstractSchedulerService.java:151)
at com.atlassian.scheduler.core.JobLauncher.launch(JobLauncher.java:98)
at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.launchJob(CaesiumSchedulerService.java:443)
at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.executeLocalJob(CaesiumSchedulerService.java:410)
at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.executeQueuedJob(CaesiumSchedulerService.java:388)
at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService$1.consume(CaesiumSchedulerService.java:285)
at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService$1.consume(CaesiumSchedulerService.java:282)
at com.atlassian.scheduler.caesium.impl.SchedulerQueueWorker.executeJob(SchedulerQueueWorker.java:65)
at com.atlassian.scheduler.caesium.impl.SchedulerQueueWorker.executeNextJob(SchedulerQueueWorker.java:59)
at com.atlassian.scheduler.caesium.impl.SchedulerQueueWorker.run(SchedulerQueueWorker.java:34)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLRecoverableException: IO Error: The Network Adapter could not establish the connection
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:774)
at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:688)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:39)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:691)
at org.apache.commons.dbcp2.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:39)
at org.apache.commons.pool2.impl.GenericObjectPool.create(GenericObjectPool.java:861)
at org.apache.commons.pool2.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:435)
at org.apache.commons.pool2.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:363)
at org.apache.commons.dbcp2.PoolingDataSource.getConnection(PoolingDataSource.java:134)
at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:1533)
at org.ofbiz.core.entity.transaction.DBCPConnectionFactory$1.call(DBCPConnectionFactory.java:285)
at org.ofbiz.core.entity.transaction.DBCPConnectionFactory$1.call(DBCPConnectionFactory.java:283)
at org.ofbiz.core.entity.jdbc.interceptors.connection.ConnectionTracker.trackConnection(ConnectionTracker.java:48)
... 29 more

Caused by: oracle.net.ns.NetException: The Network Adapter could not establish the connection
at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:523)
at oracle.net.resolver.AddrResolution.resolveAndExecute(AddrResolution.java:521)
at oracle.net.ns.NSProtocol.establishConnection(NSProtocol.java:660)
at oracle.net.ns.NSProtocol.connect(NSProtocol.java:286)
at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1438)
... 42 more
Caused by: java.io.IOException: Connection refused, socket connect lapse 0 ms. /171.128.232.106 49125 30000 1 true
at oracle.net.nt.TcpNTAdapter.connect(TcpNTAdapter.java:209)
at oracle.net.nt.ConnOption.connect(ConnOption.java:161)
at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:470)
... 47 more
Caused by: java.net.ConnectException: Connection refused
at sun.nio.ch.Net.connect(Net.java:454)
at sun.nio.ch.Net.connect(Net.java:446)
at sun.nio.ch.SocketChannelImpl.connect(SocketChannelImpl.java:648)
at java.nio.channels.SocketChannel.open(SocketChannel.java:189)
at oracle.net.nt.TimeoutSocketChannel.<init>(TimeoutSocketChannel.java:81)
at oracle.net.nt.TcpNTAdapter.connect(TcpNTAdapter.java:169)
... 49 more
2020-04-18 21:27:01,965 Caesium-1-1 ERROR ServiceRunner [c.a.s.caesium.impl.SchedulerQueueWorker] Unhandled exception thrown by job QueuedJob[jobId=CaesiumSchedulerService.RecoveryJob,deadline=1587259621954]

-----------------------------

JIRA/server get's bounced....

2020-04-18 21:41:26,051 JIRA-Bootstrap INFO [c.a.j.config.database.SystemDatabaseConfigurationLoader] Reading database configuration from /apps/jirahome/dbconfig.xml
2020-04-18 21:41:26,445 JIRA-Bootstrap ERROR [c.a.config.bootstrap.DefaultAtlassianBootstrapManager] Could not successfully test your database:
java.sql.SQLRecoverableException: IO Error: The Network Adapter could not establish the connection
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:774)
at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:688)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:39)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:691)
at java.sql.DriverManager.getConnection(DriverManager.java:664)
at java.sql.DriverManager.getConnection(DriverManager.java:247)
at com.atlassian.config.bootstrap.DefaultAtlassianBootstrapManager.getTestDatabaseConnection(DefaultAtlassianBootstrapManager.java:347)
at com.atlassian.jira.config.database.JdbcDatasource.getConnection(JdbcDatasource.java:211)
at com.atlassian.jira.config.database.DatabaseConfig.testConnection(DatabaseConfig.java:88)
at com.atlassian.jira.health.checks.DbConfigurationAndConnectionCheck.doPerform(DbConfigurationAndConnectionCheck.java:60)
at com.atlassian.jira.health.HealthCheckTemplate.perform(HealthCheckTemplate.java:23)
at com.atlassian.jira.health.DefaultHealthCheckExecutor.runCheck(DefaultHealthCheckExecutor.java:74)
at com.atlassian.jira.health.DefaultHealthCheckExecutor.lambda$applyAndCollectExceptions$1(DefaultHealthCheckExecutor.java:53)
at java.util.stream.ForEachOps$ForEachOp$OfRef.accept(ForEachOps.java:184)
at java.util.stream.ReferencePipeline$2$1.accept(ReferencePipeline.java:175)
at java.util.Iterator.forEachRemaining(Iterator.java:116)
at java.util.Spliterators$IteratorSpliterator.forEachRemaining(Spliterators.java:1801)
at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:481)
at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:471)

2020-04-18 21:41:26,462 JIRA-Bootstrap ERROR [c.a.jira.health.HealthChecks] JIRA couldn't connect to your database
2020-04-18 21:41:26,462 JIRA-Bootstrap ERROR [c.a.jira.health.HealthChecks] JIRA failed to establish a connection to your database.
This could be because:
- Your database isn't running
- The configuration of your dbconfig.xml file is incorrect (user, password, or database URL etc.)
- There is a network issue between JIRA and your database (e.g. firewall, database doesn't allow remote access etc.)

There are several other solutions you can try, review our documentation and see what works for you.

2020-04-18 21:41:26,465 JIRA-Bootstrap INFO [c.a.jira.startup.JiraStartupLogger] Running JIRA startup checks.
2020-04-18 21:41:26,465 JIRA-Bootstrap FATAL [c.a.jira.startup.JiraStartupLogger] Startup check failed. JIRA will be locked.

Then we reboot it again after DB is up and it works.  If JIRA can't self recover than I guess our script will use to start JIRA will need to be enhanced to check the status of the DB and not come up until the DB is able to connect.

[Patrick]

Hi Michael,

sorry, didn't notice your new answer.

This is a Oracle Database and i had the same issues with failing connection in case of Downtimes/Failover (Oracle RAC)

Yeah - restarting the service will fix this but we had to configure ONS (Oracle Notification Service) and FCF (Fast Connection Failover) to the Service (Java) to fix this...

https://docs.oracle.com/database/121/HABPT/config_fcf.htm#HABPT4969

Maybe you can try it out but this will need some time...

[Michael Simon]

The main problem isn't from a failover but a un-aligned maintenance schedule.  We're too big to always have every group perform maintenance at the same time and align which comes first.  I've sense developed a simple python script to attempt connection and retry every x seconds until the connection is a success.  Then it will exit and the remainder of the start up can happen successfully.