Unable to login after restoring JIRA from a crashed drive

[Bernard Yim]

Hi,

We had a hard disk failure (boot partition crashed, drive only accessible via USB docker) and unfortunately we didn’t do any backups and can’t follow the standard backup + restore JIRA procedures.  Below steps were performed to restore JIRA on a brand new drive:

1. Install JIRA 6.2.2, MySQL 5.6.43 on RHEL 6.5, exact versions as old drive.

2. Copy below folders (and all subfolders) from old drive to new drive:

/var/lib/mysql (we used default MySQL folder)

/var/atlassian

/opt/atlassian

3. JIRA table cwd_application.ibd was corrupted and preventing mysql to startup properly.  The table is therefore manually re-created with empty content.

4. /opt/atlassian/jira/logs/catalina.out has below errors, while JIRA seemed to startup ok by showing the "...access JIRA through your web browser" line.

2019-03-14 17:28:00,469 localhost-startStop-1 ERROR      [core.entity.jdbc.DatabaseUtil] WARNING: Column "DEC_PROP_1" of table "JQUARTZ_SIMPROP_TRIGGERS" of entity "JQRTZSimplePropTriggers" is of type "DECIMAL(13,4)" in the database, but is defined as type "NUMERIC(13,4)" in the entity definition.

2019-03-14 17:28:00,470 localhost-startStop-1 ERROR      [core.entity.jdbc.DatabaseUtil] WARNING: Column "DEC_PROP_2" of table "JQUARTZ_SIMPROP_TRIGGERS" of entity "JQRTZSimplePropTriggers" is of type "DECIMAL(13,4)" in the database, but is defined as type "NUMERIC(13,4)" in the entity definition.

2019-03-14 17:28:49,819 localhost-startStop-1 ERROR      [atlassian.jira.startup.JiraStartupLogger] Cannnot obtain JIRA system information

2019-03-14 17:28:56,651 UpmScheduler:thread-1 WARN anonymous     [atlassian.upm.pac.PacClientImpl] Error when querying application info from MPAC: com.atlassian.util.concurrent.LazyReference$InitializationException: com.atlassian.cache.CacheException: com.atlassian.cache.CacheException: java.lang.IllegalArgumentException: No enum constant com.atlassian.crowd.model.application.ApplicationType.jira

2019-03-14 17:28:56,854 UpmScheduler:thread-1 ERROR anonymous     [atlassian.upm.schedule.AbstractUpmScheduledJob] Error executing job 'IncompatiblePluginsCheckPluginJob'

2019-03-14 17:31:57,571 QuartzScheduler_Worker-3 ERROR ServiceRunner    IncompatiblePluginsCheckPluginJob-job [atlassian.upm.schedule.AbstractUpmScheduledJob] Error executing job 'IncompatiblePluginsCheckPluginJob'

2019-03-14 17:32:58,091 QuartzScheduler_Worker-3 ERROR ServiceRunner    IncompatiblePluginsCheckPluginJob-job [atlassian.upm.schedule.AbstractUpmScheduledJob] Error executing job 'IncompatiblePluginsCheckPluginJob'

at com.atlassian.plugin.manager.DefaultPluginManager.broadcastIgnoreError(DefaultPluginManager.java:1772)

2019-03-14 17:34:17,782 localhost-startStop-1 ERROR      [core.entity.jdbc.DatabaseUtil] WARNING: Column "DEC_PROP_1" of table "JQUARTZ_SIMPROP_TRIGGERS" of entity "JQRTZSimplePropTriggers" is of type "DECIMAL(13,4)" in the database, but is defined as type "NUMERIC(13,4)" in the entity definition.

2019-03-14 17:34:17,783 localhost-startStop-1 ERROR      [core.entity.jdbc.DatabaseUtil] WARNING: Column "DEC_PROP_2" of table "JQUARTZ_SIMPROP_TRIGGERS" of entity "JQRTZSimplePropTriggers" is of type "DECIMAL(13,4)" in the database, but is defined as type "NUMERIC(13,4)" in the entity definition.

jira.projectkey.warning                       : admin.errors.must.specify.unique.project.key

2019-03-14 17:35:14,579 UpmScheduler:thread-1 WARN anonymous     [atlassian.upm.pac.PacClientImpl] Error when querying application info from MPAC: com.atlassian.marketplace.client.MpacException: javax.net.ssl.SSLPeerUnverifiedException: peer not authenticated

*********************************************************************************

JIRA 6.2.2 build: 6258 started. You can now access JIRA through your web browser.

*********************************************************************************

5. Cannot login any users from web browser.  /var/atlassian/application-data/jira/log/atlassian-jira-security.log has this error.  Same error when trying different users.

2019-03-14 17:55:50,035 http-bio-8010-exec-3 anonymous 1075x19x1 7l0x6n 192.168.1.107 /rest/gadget/1.0/login login : 'xxxx' tried to login but they do not have USE permission or weren't found. Deleting remember me cookie

6. We tried resetting password (to ‘sphere’ https://confluence.atlassian.com/jira/retrieving-the-jira-administrator-192836.html?_ga=2.66771883.409780365.1552366733-2034864233.1552275531) for user xxxx with below SQL but still couldn’t login.

update cwd_user set credential='uQieO/1CGMUIXXftw3ynrsaYLShI+GTcPS4LdUGWbIusFvHPfUzD7CZvms6yMMvA8I7FViHVEqr6Mj4pCLKAFQ==' where user_name='xxxx';

7. Checked https://confluence.atlassian.com/jirakb/unable-to-login-jira-after-restoring-from-backup-780864242.html?_ga=2.263330569.409780365.1552366733-2034864233.1552275531 and we have only one row in cwb_directory:

mysql> select id,directory_name,directory_position from cwd_directory;

+----+-------------------------+--------------------+

| id | directory_name          | directory_position |

+----+-------------------------+--------------------+

|  1 | JIRA Internal Directory |                  0 |

+----+-------------------------+--------------------+

1 row in set (0.00 sec)

8. We checked couple cwd_*** tables in MySQL and they all seem intact with correct data, except that cwd_application had to be recreated as blank table.

Anything we missed?  Thank you very much.

Bernard

[Daniel Eads · Accepted solution]

Hey Bernard,

Based on Jira starting and writing permission errors, it looks like your major blocker right now is the embedded Crowd not functioning properly with your existing users. I had a peek at cwd_application and think you may need one entry for internal Crowd to start working again. Here's the PostgreSQL statement for import (I believe for MySQL you may need to strip some quotes) - make sure you update your_jira_db on line 1 or remove it depending on what you need for MySQL:

INSERT INTO "your_jira_db"."cwd_application"(
"id",
"application_name",
"lower_application_name",
"created_date",
"updated_date",
"active",
"description",
"application_type",
"credential"
) VALUES (
1,
'crowd-embedded',
'crowd-embedded',
'2013-02-28 11:57:51.302-06',
'2013-02-28 11:57:51.302-06',
1,
'',
'CROWD',
'X'
);

After that row is created, give Jira a restart and try logging in again. We'll cross our fingers but keep working at it if more errors crop up!

Cheers,
Daniel

[Bernard Yim]

Hi Daniel,

Thanks for your valuable instruction.  I managed to login after inserting the record to cwd_application.   The next errors show up in catalina.out and atlassian-jira.log are all related to missing _pl_1.del:

2019-03-15 14:21:50,451 http-bio-8010-exec-10 ERROR xxxx 861x215x1 1j4buz1 192.168.1.107 /issues/ [issuenav.service.issuetable.DefaultIssueTableService] com.atlassian.jira.issue.index.SearchUnavailableException: com.atlassian.util.concurrent.LazyReference$InitializationException: com.atlassian.jira.util.RuntimeIOException: java.io.FileNotFoundException: /var/atlassian/application-data/jira/caches/indexes/issues/_pl_1.del (No such file or directory)
2019-03-15 14:21:59,602 http-bio-8010-exec-24 ERROR xxxx 861x226x4 1j4buz1 192.168.1.107 /rest/issueNav/1/issueTable [issuenav.service.issuetable.DefaultIssueTableService] com.atlassian.jira.issue.index.SearchUnavailableException: com.atlassian.util.concurrent.LazyReference$InitializationException: com.atlassian.jira.util.RuntimeIOException: java.io.FileNotFoundException: /var/atlassian/application-data/jira/caches/indexes/issues/_pl_1.del (No such file or directory)

The missing file "_pl_1.del" doesn't exist in my old drive either.  I did "touch _pl_1.del" but it didn't help.   Any chances we can rebuild or bypass that file to continue?

-rw------- 1 jira jira 115428 Jul 23 2018 _pl.tis
-rw------- 1 jira jira 1621 Jul 23 2018 _pl.tii
-rw------- 1 jira jira 99951 Jul 23 2018 _pl.prx
-rw------- 1 jira jira 1064 Jul 23 2018 _pl.nrm
-rw------- 1 jira jira 84792 Jul 23 2018 _pl.frq
-rw------- 1 jira jira 693 Jul 23 2018 _pl.fnm
-rw------- 1 jira jira 1700 Jul 23 2018 _pl.fdx
-rw------- 1 jira jira 325510 Jul 23 2018 _pl.fdt
-rw------- 1 jira jira 6111 Aug 6 2018 _pp.cfs
-rw------- 1 jira jira 5710 Aug 7 2018 _pr.cfs
-rw------- 1 jira jira 4822 Aug 8 2018 _px.cfs
-rw------- 1 jira jira 4854 Aug 8 2018 _py.cfs
-rw------- 1 jira jira 1358 Aug 9 2018 segments_pq
-rw------- 1 jira jira 5290 Aug 9 2018 _q2.cfs
-rw-rw-r-- 1 jira jira 0 Mar 15 14:17 _pl_1.del (<---created by me)

The behavior is somehow different on different users, some can see old issues but can't open the issue.   Some users can't see any old issues at all.

Thanks,

Bernard

[Bernard Yim]

Hi, we managed to dig up an old JIRA backup XML, and successfully restored the data and indexes.  This case is closed.

Thank you very much

Bernard

[Roman Kharytonov]

Had exactly the same issue and your advice helped!

Same insert for Oracle users:

insert into cwd_application (ID,APPLICATION_NAME,LOWER_APPLICATION_NAME,CREATED_DATE,UPDATED_DATE,ACTIVE,DESCRIPTION,APPLICATION_TYPE,CREDENTIAL) 
values (1,'crowd-embedded','crowd-embedded',to_date('28-FEB-13','DD-MON-RR'),to_date('28-FEB-13','DD-MON-RR'),1,null,'CROWD','X');

If anyone else ran into this problem, make sure that your admin user also has entry on app_user table:

https://confluence.atlassian.com/jirakb/jira-login-fails-with-user-exists-but-has-no-unique-key-mapping-373261121.html