Cannot reorder or delete any issues (ranking problem)

[OpOnion]

I cannot reorder or delete any issues on a fresh installation of the newest version of Jira Server (8.13.1) and mysql 8 after restoring the xml backup from an old installation. I get the following error trying to reorder: 

Jira Software cannot execute the rank operation. This board has recently been configured to use the Rank field. The system must be re-indexed before you can rank issues. Ask your administrator to perform a manual re-index.

When I try to delete an issue I get the following at the end:

Can someone help me with the issue?

I tried manual reindexing (Settings, System, Advanced, Indexing, Full re-index) which not change anything, and I tried this: https://confluence.atlassian.com/jirakb/unable-to-rank-issues-after-upgrading-jira-software-779158591.html which did not make a difference.

Also I only have 1 Rank field:

SELECT id, cfname from customfield WHERE customfieldtypekey = 'com.pyxis.greenhopper.jira:gh-lexo-rank';

+-------+--------+
| id | cfname |
+-------+--------+
| 10105 | Rank |
+-------+--------+
1 row in set (0.00 sec)

Referer URL: https://issue.oponion.ch/secure/RapidBoard.jspa?rapidView=1&view=planning.nodetail&selectedIssue=STPB-18&issueLimit=100

com.atlassian.greenhopper.manager.lexorank.LexoRankIntegrityException: Expected exactly 2 rows; the maximum marker row and the lowest ranked row for rank field[id=10105]

com.atlassian.greenhopper.manager.lexorank.LexoRankIntegrityException: Expected exactly 2 rows; the maximum marker row and the lowest ranked row for rank field[id=10105]
	at com.atlassian.greenhopper.manager.lexorank.LexoRankDaoImpl.getMaximumMarkerRowAndPreviousRow(LexoRankDaoImpl.java:363) [?:?]
	at com.atlassian.greenhopper.service.lexorank.LexoRankOperation.rankInitially(LexoRankOperation.java:154) [?:?]
	at com.atlassian.greenhopper.service.lexorank.LexoRankOperation.execute(LexoRankOperation.java:108) [?:?]
	at com.atlassian.greenhopper.manager.lexorank.LexoRankManagerImpl.performRankOperation(LexoRankManagerImpl.java:260) [?:?]
	at com.atlassian.greenhopper.manager.lexorank.LexoRankManagerImpl.getRankOrRankInitially(LexoRankManagerImpl.java:83) [?:?]
	at com.atlassian.greenhopper.customfield.lexorank.LexoRankCFType.getValueFromIssue(LexoRankCFType.java:109) [?:?]
	at com.atlassian.greenhopper.customfield.lexorank.LexoRankCFType.getValueFromIssue(LexoRankCFType.java:32) [?:?]
	at com.atlassian.jira.issue.fields.ImmutableCustomField.getValue(ImmutableCustomField.java:350) [classes/:?]
	at com.atlassian.jira.issue.managers.DefaultIssueDeleteHelper$DeletedIssueEventData.collectCustomFieldValues(DefaultIssueDeleteHelper.java:301) [classes/:?]
	at com.atlassian.jira.issue.managers.DefaultIssueDeleteHelper$DeletedIssueEventData.<init>(DefaultIssueDeleteHelper.java:288) [classes/:?]
	at com.atlassian.jira.issue.managers.DefaultIssueDeleteHelper.deleteIssue(DefaultIssueDeleteHelper.java:132) [classes/:?]
	at com.atlassian.jira.issue.managers.DefaultIssueManager.deleteIssue(DefaultIssueManager.java:743) [classes/:?]
	at com.atlassian.jira.issue.managers.RequestCachingIssueManager.deleteIssue(RequestCachingIssueManager.java:247) [classes/:?]
	at com.atlassian.jira.bc.issue.DefaultIssueService.delete(DefaultIssueService.java:427) [classes/:?]
	at com.atlassian.jira.bc.issue.DefaultIssueService.delete(DefaultIssueService.java:403) [classes/:?]
	at com.atlassian.jira.web.action.issue.DeleteIssue.doExecute(DeleteIssue.java:23) [classes/:?]
	at webwork.action.ActionSupport.execute(ActionSupport.java:165) [webwork-1.4-atlassian-30.jar:?]
	at com.atlassian.jira.action.JiraActionSupport.execute(JiraActionSupport.java:63) [jira-api-8.13.1.jar:?]
	at webwork.interceptor.DefaultInterceptorChain.proceed(DefaultInterceptorChain.java:39) [webwork-1.4-atlassian-30.jar:?]
	at webwork.interceptor.NestedInterceptorChain.proceed(NestedInterceptorChain.java:31) [webwork-1.4-atlassian-30.jar:?]
	at webwork.interceptor.ChainedInterceptor.intercept(ChainedInterceptor.java:16) [webwork-1.4-atlassian-30.jar:?]
	at webwork.interceptor.DefaultInterceptorChain.proceed(DefaultInterceptorChain.java:35) [webwork-1.4-atlassian-30.jar:?]
	at webwork.dispatcher.GenericDispatcher.executeAction(GenericDispatcher.java:225) [webwork-1.4-atlassian-30.jar:?]
	at webwork.dispatcher.GenericDispatcher.executeAction(GenericDispatcher.java:154) [webwork-1.4-atlassian-30.jar:?]
	at com.atlassian.jira.web.dispatcher.JiraWebworkActionDispatcher.service(JiraWebworkActionDispatcher.java:138) [classes/:?]
	at javax.servlet.http.HttpServlet.service(HttpServlet.jav

[Daniel Ebers · Accepted solution]

Hi OpOnion,

that is a lot of work you have done to diagnose the issue - which is good.

The second step would be to follow the steps in the following article:
https://confluence.atlassian.com/jirakb/several-actions-such-as-issue-ranking-move-export-are-failing-779159169.html

Could you please do this and report back on the status of the AO_60DB71_LEXORANK table - is it empty?

Let us hear from you as soon as you had the time to check.

Cheers,
Daniel

[OpOnion]

Thank you so much for looking at my issue!

mysql> select *from AO_60DB71_LEXORANK;

Empty set (0.00 sec)

So yes, it seems pretty empty...

If I try to insert those values:

INSERT INTO "AO_60DB71_LEXORANK" ("FIELD_ID", "ISSUE_ID", "LOCK_HASH", "LOCK_TIME", "RANK", "TYPE") VALUES (10105, -9223372036854775808, null, null, '0|000000:', 0);

I get: 

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"AO_60DB71_LEXORANK" ("FIELD_ID", "ISSUE_ID", "LOCK_HASH", "LOCK_TIME", "RANK", ' at line 1

The table looks like follows:

DESCRIBE AO_60DB71_LEXORANK;

+-----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+----------------+
| BUCKET | int | YES | | 0 | |
| FIELD_ID | bigint | NO | MUL | 0 | |
| ID | bigint | NO | PRI | NULL | auto_increment |
| ISSUE_ID | bigint | NO | MUL | 0 | |
| LOCK_HASH | varchar(255) | YES | MUL | NULL | |
| LOCK_TIME | bigint | YES | | NULL | |
| RANK | varchar(255) | NO | | NULL | |
| TYPE | int | NO | | 0 | |
+-----------+--------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

Does that help? Do you need more information?

Kind regards and thank you

Pascal

[Daniel Ebers]

Could you please try the following for the moment?

INSERT INTO AO_60DB71_LEXORANK (FIELD_ID, ISSUE_ID, LOCK_HASH, LOCK_TIME, RANK, TYPE) VALUES (10105, -9223372036854775808, null, null, '0|000000:', 0);

INSERT INTO AO_60DB71_LEXORANK (FIELD_ID, ISSUE_ID, LOCK_HASH, LOCK_TIME, RANK, TYPE) VALUES (10115, 9223372036854775807, null, null, '0|zzzzzz:', 2);

[OpOnion]

Thank you so much for helping, 

running those commands first didn't work because mysql didn't accept something near 'TYPE' for some reason but after executing everything like this:

INSERT INTO `jiradb`.`AO_60DB71_LEXORANK` (`FIELD_ID`, `ISSUE_ID`, `RANK`, `TYPE`) VALUES (10105, -9223372036854775808, '0|000000:', 0);

and starting jira up + doing a full reindex everything works as planned. Tank you so much for helping out!

Have a nice weekend!

Pascal