Major DB Crash: HTTP Status 500 - Could not determine database type. (User not found)

[Thomas Jerman]

Hello,

we have some major crash/problem as it seems the database disappeared or something related to it crashed very badly in a permanent manner. The usual "quick fixes" (restart JIRA service, restart host PC, etc.) did not help.

I need a *quick* solution, as our whole team relies on JIRA for running their projects.
We do have daily backups of everything, the entire /JIRA_AppData directory as well as daily .zip file containing entities.xml (around 18 MB) and activeobjects.xml (1.6 MB).

Please provide me either with
- a way how to restore the backed up database to the non-functioning JIRA (as Tomcat just always gives the error message "HTTP Status 500 - Could not determine database type. (User not found: SA)", and I cannot login anywhere anymore)
- or the advice to use the "opportunity" to just wipe our entire JIRA installation clean, download the latest JIRA, and install from scratch and load the backed up database with the /JIRA_AppData from the old installation (if this works smoothly!!!)

According to the logfiles all of this occured in the early morning hours two days ago on the 17th Oct, when the daily backup-script-run shuts down JIRA, performs the backup, and activates JIRA again:

localhost.2018-10-16.log (320 B filesize)
********************************************************************************
Okt 16, 2018 5:03:33 AM org.apache.catalina.core.ApplicationContext log
INFORMATION: org.tuckey.web.filters.urlrewrite.UrlRewriteFilter INFO: destroy called
Okt 16, 2018 5:07:22 AM org.apache.catalina.core.ApplicationContext log
INFORMATION: org.tuckey.web.filters.urlrewrite.UrlRewriteFilter INFO: loaded (conf ok)

jira230914150550-stdout.2018-10-16.log (248 kB filesize)
********************************************************************************

localhost.2018-10-17.log (84 MB filesize due to the errors)
********************************************************************************
Okt 17, 2018 5:03:35 AM org.apache.catalina.core.ApplicationContext log
INFORMATION: org.tuckey.web.filters.urlrewrite.UrlRewriteFilter INFO: destroy called
Okt 17, 2018 5:06:59 AM org.apache.catalina.core.ApplicationContext log
INFORMATION: org.tuckey.web.filters.urlrewrite.UrlRewriteFilter INFO: loaded (conf ok)
Okt 17, 2018 5:06:59 AM org.apache.catalina.core.StandardWrapperValve invoke
SCHWERWIEGEND: Servlet.service() for servlet [servlet-module-container-servlet] in context with path [] threw exception
org.ofbiz.core.util.GeneralRuntimeException: Could not determine database type. (User not found: SA)
at org.ofbiz.core.entity.config.DatasourceInfo.getDatabaseTypeFromJDBCConnection(DatasourceInfo.java:409)
at org.ofbiz.core.entity.GenericDAO.selectListIteratorByCondition(GenericDAO.java:826)
at org.ofbiz.core.entity.GenericDAO.selectByAnd(GenericDAO.java:725)
at org.ofbiz.core.entity.GenericHelperDAO.findByAnd(GenericHelperDAO.java:150)
at org.ofbiz.core.entity.GenericDelegator.findByAnd(GenericDelegator.java:901)
at org.ofbiz.core.entity.GenericDelegator.findByAnd(GenericDelegator.java:879)
at org.ofbiz.core.entity.GenericDelegator.findByAnd(GenericDelegator.java:848)
...
...
...

jira230914150550-stdout.2018-10-17.log (413 MB (!!) filesize due to the errors)
********************************************************************************

Also the /JIRA_AppData/database/ folder now contains no database, but only 4 small files:
- jiradb.lck (16 B filesize)
- jiradb.log (empty)
- jiradb.properties (429 B filesize)
- jiradb.script (60 B filesize)

Normally there were always 2 files, the same jiradb.properties file, and a 17 MB large jiradb.script file.

 THANKS a lot for help....

[robinhp82]

when I looked at the individual service.bat file in the /bin directory, all four JIRA installations had of course different service names there.
I did look at some resources of how to do that, which indicate that if these conditions are met it should be possible:
- https://confluence.atlassian.com/jira/running-multiple-instances-of-jira-on-one-machine-222200571.html
- https://community.atlassian.com/t5/Jira-questions/2-Jira-instances-on-Same-host/qaq-p/55202
and as far as I understood all conditions were met, which is mainly
- I had different installation folders for home and data directory
- different ports for each instance
- generated separate testing licenses for each instance
- each using its own integrated tomcat,

but to no prevail. After trying around for the last day, I failed to make the other instances work simultaneously and I gave up on it.

Instead, I wiped all again, reinstalled 6.3.6 from scratch, and then loaded the XML database. The resoration procedure was done within less than a minute successfully, and it looks like I got back all the issues and projects which we had couple of days ago.
There are lots of gadget errors on the dashboard, and other issues, as this fresh installation does not contain any plugins, upgrades or other licenses (tempo, ...) that we used. Also I did not care about the application data folder.

Because what I did in the end was I took the resulting jiradb.script (whih was around 17 MB after importing from the XML dump) and jiradb.properties file from the /JIRA_AppData/database folder on the test-installation PC, and copied them to the corresponding destination on our server.
After starting the JIRA service there, it was up and running again with all our stuff, projects, issues and attached data in place. All time loggings here, plugins working, also system health checks mostly positive.
It seems like we have it all back, in old glory, and that with no new installation needed, no new setting all up from scratch, no headache of not working things which should work easily out of the box, etc.
At some point, when I will have the time for it, I will go through all of that lengthy update procedures, and the HSQL database thing....

Lets hope this is the end of the story regarding this issue! :)
Thanks for support and best regards

[robinhp82]

Hello Nic,

thanks for your rapid answer. BTW my name is Robin, just had to use the account of my colleague for the init post as he purchased the JIRA license, but its me who performs all the admin tasks (when maintenance is needed).

I was afraid that this would have to come at some point (setting up JIRA all new from scratch), as our installation is already around five years old, and was running fine with a minimum of maintenance so far.
Also the thing with the database was known (I could not overlook the nice warning at the bottom right corner on the dashboard!), but the effort/time to implement whatever would have/will have to be necessary was off-putting me,
and the system was running fine with regular daily backups in place in case of an emergency, so the decision was made to just skip that part.

I was already downloading the latest JIRA version (7.12.3) in the mean time, and installed it with a new trial license on a test PC to verify everything will work before actually destroying the old instance on our server.
What I now realised is, that I cannot directly restore the backup from our old JIRA (which is 6.3.6 from 2014), and I apparently have to go through some iterative upgrade steps..... Will research on how to do that in more detail now.
But I hope it should work, and there are no other surprises to be expected, also considering the JIRA_AppData folder - or do you know of anything else that I should be careful about ?

According to the upgrade steps, since we have 6.3.x, it looks as if I also have to install at least JIRA 6.4 and JIRA CORE 7.0.X, and make some intermediate database imports / upgrades and exports with each, in order to reach the latest 7.12.3, correct ?
Is this procedure only related to the XML files, or also somehow to the /JIRA_AppData directory ?
Also, we have a Stash/BitBucket installation which is linked to JIRA (with existing connections between issues and repositories, etc.), do we also need to do something there ?

Btw., finally I managed to get our JIRA installation to work again, but unfortunately its a database state/version which is one month old with all loggings and issues of the last month missing.
I just removed those 4 faulty jiradb.xxxx files from the /JIRA_AppData/database/ folder, and put the last existing good version (only two files , the *.properties and the large *.script file) from the NAS backup there.
Due to another unrelated problem the NAS backup files are a month old, and I only have the two XML files as produced by the JIRA-backup from two days ago.
In order to restore some temporary JIRA operation in the mean time, can you tell me how to produce that jiradb.script file from the XML-backup file ?

Thanks

[Nic Brough -Adaptavist-]

Nearly.  You don't need to upgrade.

I would actually download and install 6.3.6 and restore the latest XML into that.  That's the only way of recreating an hsql database from the backups.

[robinhp82]

...OK, after I successfully installed the latest JIRA 7.12.3 which worked out of the box (on standard ports 8080 and 8005), I also installed our old 6.3.6 version, as well as the intermediate versions 6.4 and 7.0.12 which I would need if I wanted to do the upgrade.
Of course each on different ports, i.e. 8081 to 8083 and 8006 to 8008.
And guess what, none of any other installation after the first one is able to start the service. I always get "The service name is invalid" error message 2185.
Found an issue dealing with that problem: https://jira.atlassian.com/browse/JRASERVER-21496
Besides the fact that this is for an ancient version, trying this (with Tomcat7 and Tomcat8 in my case for trying to run the various JIRA versons) was also resulting with the same message (invalid service).
When manually running "service.bat install" I get the various folder names as output, but always a "Failed installing 'JIRAxxxxxxxx' service" message.

Why is this not working as expected?

[Nic Brough -Adaptavist-]

I suspect it's because you've got two installations, but the installer just creates one service with the same name.  I'd guess it's looking at the wrong installation.

[Nic Brough -Adaptavist-]

Ok, I think you've already answered your own question, by picking on the right response.

"wipe our entire JIRA installation clean, download the latest JIRA, and install from scratch and load the backed up database with the /JIRA_AppData from the old installation"

Once you have that working, export it to XML, shut it down again, and reconfigure it to use a postgres, mysql, oracle or ms-sql database, restart it and import the xml into the new database.

The reason for this:  Your question mentions "jiradb.properties file, and a 17 MB large jiradb.script file." and the log "org.hsqldb.jdbcDriver", which tells me your system was using the internal database.

The internal database is not intended for real use, it's a cheap and easy quick-start for a demo or dev/test system you're going to throw away quite rapidly.  It does not scale, and it is known to fail catastrophically without warning, leaving a load of unrecoverable garbage in the database files.  This is what has happened to you here.

[Thomas Jerman]

Here is just some extra information which I forgot to add before (output from jira230914150550-stdout.2018-10-17.log)

****************
JIRA starting...
****************

2018-10-17 05:06:58,537 localhost-startStop-1 INFO [atlassian.jira.startup.JiraStartupLogger]

___ Environment _____________________________

JIRA Build : 6.3.6#6336-sha1:cf1622c62a612607f341bda9491a04918e09ebfd
Build Date : Tue Sep 16 00:00:00 CEST 2014
JIRA Installation Type : Standalone
Application Server : Apache Tomcat/7.0.55 - Servlet API 3.0
Java Version : 1.8.0_11 - Oracle Corporation
Current Working Directory : C:\JIRA

...
...
..

2018-10-17 05:06:58,709 localhost-startStop-1 INFO [atlassian.jira.startup.JiraHomeStartupCheck] The jira.home directory 'C:\JIRA_AppData' is validated and locked for exclusive use by this instance.
2018-10-17 05:06:58,724 localhost-startStop-1 INFO [jira.config.database.SystemDatabaseConfigurationLoader] Reading database configuration from C:\JIRA_AppData\dbconfig.xml
2018-10-17 05:06:58,802 localhost-startStop-1 INFO [atlassian.jira.startup.JiraStartupLogger] Running JIRA startup checks.
2018-10-17 05:06:58,802 localhost-startStop-1 INFO [atlassian.jira.startup.JiraStartupLogger] JIRA pre-database startup checks completed successfully.
2018-10-17 05:06:59,193 localhost-startStop-1 ERROR [NoModule] Error getting datasource via DBCP: JdbcDatasourceInfo{uri='jdbc:hsqldb:C:\JIRA_AppData/database/jiradb', driverClassName='org.hsqldb.jdbcDriver', username='sa', password='********', isolationLevel='null', connectionProperties=null, connectionPoolInfo=ConnectionPoolInfo{maxSize=20, minSize=20, initialSize=null, maxIdle=20, maxWait=30000, sleepTime=300000, lifeTime=600000, deadLockMaxWait=600000, deadLockRetryWait=10000, validationQuery=null, minEvictableTimeMillis=4000, timeBetweenEvictionRunsMillis=5000, poolPreparedStatements=null, testOnBorrow=null, testOnReturn=null, testWhileIdle=null, maxOpenPreparedStatements=null, numTestsPerEvictionRun=null, removeAbandonedTimeout=300, validationQueryTimeout=null, defaultCatalog=null}}
org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (User not found: SA)
at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1549)
at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1388)
at org.apache.commons.dbcp.BasicDataSource.setLogWriter(BasicDataSource.java:1134)
at org.ofbiz.core.entity.transaction.DBCPConnectionFactory.getConnection(DBCPConnectionFactory.java:110)
at org.ofbiz.core.entity.ConnectionFactory.tryGenericConnectionSources(ConnectionFactory.java:69)
...
...