Confluence startup fails, SQL state XX001, could not extract ResultSet

[Robert Levandowski]

Last month, I upgraded my Confluence install to 6.15.1 using the same in-place procedure I've used repeatedly in the past. It started and ran successfully at the time, but now, a month later, a simple system reboot has resulted in:

Unable to start up Confluence. Fatal error during startup sequence: confluence.lifecycle.core:mananagedjobs (Start and stop the Managed Scheduled Jobs)

JDBC exception on Hibernate data access: SQLException for SQL [n/a]; SQL state [XX001]; error code [0]; could not extract ResultSet; nested exception is org.hibernate.exception.GenericJDBCException: could not extract ResultSet

Confluence is installed on an AMD Zen-based Proxmox VM server, in a Linux container running CentOS 7 (kernel 4.15.18-14-pve). The database is PostgreSQL 9.6 (9.6+181+deb9u2) in a separate Linux container running Debian 9. The database server also holds a database for a JIRA instance that's in a third Linux container; the JIRA instance is working fine. The three containers share a private VLAN that does not leave the VM server for database connections (that is, PostgreSQL only binds to that VLAN).

The sequence of events leading to the error:

1. I applied recent software updates to the Proxmox VM server and to the containers (due to the MDS flaws recently announced) using apt and/or yum.
2. I shut down the Confluence and JIRA containers manually, using the shutdown command.
3. I rebooted the Proxmox server, which caused the database container to shut down in an orderly fashion.
4. Upon reboot, JIRA came back up fine, but Confluence failed to start as shown above, and consistently fails the same way.

Troubleshooting already tried:

1. I tried rolling the Confluence VM back to a snapshot prior to installing the updates. This didn't help.
2. I tried stopping all three containers, restoring the database container from a snapshot backup made while the system was idle overnight the night prior to the reboot, and restarting the containers. The database and JIRA came up without issues; Confluence had the same error.

I tried looking for Confluence backups. I had previously had Confluence set to automatically take backups. It appears that at some point in the last few months, that setting was lost, possibly as a result of migration to the current VM architecture or the in-place upgrade to 6.15.1. So, I only have the backups created nightly by the Proxmox VM.

What is the cause of this error, and how can I resolve it?

[Robert Levandowski]

The database table public.scheduler_run_details was corrupted. I was able to get Confluence up again by running the following psql commands:

\c wikidb
SET zero_damaged_pages = on;
VACUUM FULL public.scheduler_run_details;
VACUUM ANALYZE public.scheduler_run_details;
REINDEX DATABASE wikidb;

After which I restarted the Confluence instance and it came up to the "didn't start last time but did now" screen, after which it appears to work normally.

I'm not sure if the corruption was due to the Proxmox shutdown sequence, an artifact of the recent Confluence in-place upgrade, or a combination...

[Hong Ma]

This helped me a lot. Thanks!