JIRA won't start - SQL/DB Error

[Kyle Mason]

Good afternoon,

I'm having a problem with a client's instance of JIRA being unable to start or connect.  It started out as a 2-fold problem with the Java memory issue that I've seen before on this forum.  After adjustments it seems to be one major issue remaining.  Not certain if the two are still related, so I figure it appropriate to mention.

We're running JIRA on a Windows Server 2012 R2 with a PostgreSQL database.  I am by NO means a DBA so on that front please talk to me like I'm 5 years old.

Here's a blurb of the error:

2021-01-26 13:46:32,944 JIRA-Bootstrap ERROR [c.a.jira.startup.LauncherContextListener] Unable to start JIRA.
com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:DELETE FROM public.rundetails WHERE START_TIME < ? (ERROR: could not read block 48896 in file "base/464887/465890": read only 0 of 8192 bytes)
at com.atlassian.jira.ofbiz.DefaultOfBizDelegator.removeByCondition(DefaultOfBizDelegator.java:144)
at com.atlassian.jira.ofbiz.WrappingOfBizDelegator.removeByCondition(WrappingOfBizDelegator.java:136)
at com.atlassian.jira.entity.Delete$DeleteWhereContext.execute(Delete.java:154)
at com.atlassian.jira.entity.EntityEngineImpl.delete(EntityEngineImpl.java:55)
at com.atlassian.jira.entity.Delete$DeleteWhereContext.execute(Delete.java:136)
at com.atlassian.jira.scheduler.OfBizRunDetailsDao.purgeHistory(OfBizRunDetailsDao.java:130)
at com.atlassian.jira.scheduler.OfBizRunDetailsDao.start(OfBizRunDetailsDao.java:70)
at com.atlassian.jira.scheduler.JiraCaesiumSchedulerService.startRunDetailsDao(JiraCaesiumSchedulerService.java:71)
at com.atlassian.jira.scheduler.JiraCaesiumSchedulerService.startImpl(JiraCaesiumSchedulerService.java:35)
at com.atlassian.scheduler.core.AbstractSchedulerService.start(AbstractSchedulerService.java:180)
at com.atlassian.jira.scheduler.JiraSchedulerLauncher.proceedIfAllClear(JiraSchedulerLauncher.java:36)
at com.atlassian.jira.scheduler.JiraSchedulerLauncher.start(JiraSchedulerLauncher.java:22)
at com.atlassian.jira.startup.ActiveServicesLauncher.start(ActiveServicesLauncher.java:56)
at com.atlassian.jira.startup.DefaultJiraLauncher.postDBActivated(DefaultJiraLauncher.java:170)
at com.atlassian.jira.startup.DefaultJiraLauncher.lambda$postDbLaunch$2(DefaultJiraLauncher.java:144)
at com.atlassian.jira.config.database.DatabaseConfigurationManagerImpl.doNowOrEnqueue(DatabaseConfigurationManagerImpl.java:304)
at com.atlassian.jira.config.database.DatabaseConfigurationManagerImpl.doNowOrWhenDatabaseActivated(DatabaseConfigurationManagerImpl.java:199)
at com.atlassian.jira.startup.DefaultJiraLauncher.postDbLaunch(DefaultJiraLauncher.java:135)
at com.atlassian.jira.startup.DefaultJiraLauncher.lambda$start$0(DefaultJiraLauncher.java:102)
at com.atlassian.jira.util.devspeed.JiraDevSpeedTimer.run(JiraDevSpeedTimer.java:31)
at com.atlassian.jira.startup.DefaultJiraLauncher.start(DefaultJiraLauncher.java:100)
at com.atlassian.jira.startup.LauncherContextListener.initSlowStuff(LauncherContextListener.java:154)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:DELETE FROM public.rundetails WHERE START_TIME < ? (ERROR: could not read block 48896 in file "base/464887/465890": read only 0 of 8192 bytes)
at org.ofbiz.core.entity.jdbc.SQLProcessor.executeUpdate(SQLProcessor.java:570)
at org.ofbiz.core.entity.GenericDAO.deleteByCondition(GenericDAO.java:1238)
at org.ofbiz.core.entity.GenericHelperDAO.removeByCondition(GenericHelperDAO.java:244)
at org.ofbiz.core.entity.GenericDelegator.removeByCondition(GenericDelegator.java:1370)
at com.atlassian.jira.ofbiz.DefaultOfBizDelegator.removeByCondition(DefaultOfBizDelegator.java:142)
Caused by: org.postgresql.util.PSQLException: ERROR: could not read block 48896 in file "base/464887/465890": read only 0 of 8192 bytes
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2455)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2155)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:288)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:430)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:356)
at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:135)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98)
at org.ofbiz.core.entity.jdbc.SQLProcessor.executeUpdate(SQLProcessor.java:562)

Steps we've taken to resolve:
I figured this part was a good place to start:
ERROR: could not read block 48896 in file "base/464887/465890": read only 0 of 8192 bytes

So taking a look at that "465890" file, I've backed it up, made a copy of a similar file (there are 36 other files with that name + .1, .2, .3, etc.), and also restored all the files from a backup.  The only change so far was that after the restoration the error then pointed to the next numerical file "465890.1".

I'm still new to the setup for this installation so I didn't want to go digging too hard without some consultation.  I appreciate any and all guidance.  Please let me know what other information I can give to help resolve this.

Thanks very much in advance for your time and expertise!

[Benjamin]

It's looks like the DB or index is corrupted.

Here's an article that very similar to your client situation. 

https://www.enterprisedb.com/blog/how-to-fix-postgresql-index-corruption-deal-repair-rebuild