JIRA HSQLBD index corruption problem

[Tukaram Bhukya [L&T INFOTECH]]

We recently added/created several issues to the one of project of JIRA. Quite a few of those records cannot be accessed. I was able to print out an excel log of the issues but those issues are not accessible.

but when i click on any one of issues displayed message "The issue you are trying to view does not exist" .
Could you tell me if those issues does not exist or saved to data base , so from where these issues displaying for view either from temporary memory or some dummy table etc. Our end customer wants to know reason from where those issues displayed before applied re-index.

Unfortunately we are using HSQL database for production . please provide your valuable root cause to understand the issue.

[Nic Brough -Adaptavist-]

HSQL is stored in (nearly) plain text, so you could take a copy of the database.script file and have a look through that for your lost data.

However, you need to work on the assumption that you've lost everything. As Norman says, you should NOT have used it for production, and frankly, I'd forget looking for your damaged/lost data for now, your top priority at the moment is to move it on to a supported database before you lose anything else.

[Tukaram Bhukya [L&T INFOTECH]]

Is HSQLDB use Lucene (search) database ?

[Andris Grinbergs]

No - Jira use it for search. And Jira builds it from your main database (in this case HSQLDB).

[Nic Brough -Adaptavist-]

No. The index is Lucene.

HSQLDB is the data store which holds all your issue data (except attachments). You can lose the Lucene index and rebuild it from the data store. You lose the data store, you lose everything.

You really need to move from HSQLDB to PostGreSQL, MySQL, Oracle or MS-SQL. Now.

[Pablo Beltran]

Has anyone tried H2? Even it is not officially supported, it is a very reliable and robust database with a good performance. And it is very close to HSQL, so it might work nicely...

[Nic Brough -Adaptavist-]

I don't think that's of much use here, this system needs to be moved onto a supported database as soon as possible, then the lost data looked into.

Speculating about other databases isn't really relevant (although it is a good general question - I'd like to see others supported, and I've had it working fine on others as well)

[Andris Grinbergs]

You wrote: "Our end customer wants to know reason from where those issues displayed before applied re-index."

I assume they were displayed from Lucene (search) database. After reindex it was updated from HSQL.

[Andris Grinbergs]

It seams you may want to execute Jira builtin integritiy check.

[Norman Abramovitz]

I guess you ignored the warnings about using HSQL for production. You need to run sql commands to know for sure, but the database is corrupted.

https://confluence.atlassian.com/display/JIRA/Running+SQL+commands+in+a+HSQL+database

[Norman Abramovitz]

HSQLDB

Supported for evaluation use only

JIRA ships with a built-in database (HyperSQL DataBase or HSQLDB). While this database is suitable for evaluation purposes, it is susceptible to data loss during system crashes. Hence, for production environments we strongly recommend that you configure JIRA to use an external database.