Not able to delete, move or clone issue. (LexoRankIntegrityException)

[Bob]

Hello, 

unfortunately we're not able to delete, move or clone issues. 
This issue is present since we installed our server. 

We are getting the error Message:

var/atlassian/application-data/jira/log/atlassian-jira.log.2:com.atlassian.greenhopper.manager.lexorank.LexoRankIntegrityException: Expected exactly 2 rows; the maximum marker row and the lowest ranked row for rank field[id=10700]
/var/atlassian/application-data/jira/log/atlassian-jira.log.2:com.atlassian.greenhopper.manager.lexorank.LexoRankIntegrityException: Expected exactly 2 rows; the maximum marker row and the lowest ranked row for rank field[id=11600]
/var/atlassian/application-data/jira/log/atlassian-jira.log.2:com.atlassian.greenhopper.manager.lexorank.LexoRankIntegrityException: Expected exactly 2 rows; the maximum marker row and the lowest ranked row for rank field[id=10500]
/var/atlassian/application-data/jira/log/atlassian-jira.log.2:com.atlassian.greenhopper.manager.lexorank.LexoRankIntegrityException: Expected exactly 2 rows; the maximum marker row and the lowest ranked row for rank field[id=10400]
/var/atlassian/application-data/jira/log/atlassian-jira.log.2:com.atlassian.greenhopper.manager.lexorank.LexoRankIntegrityException: Expected exactly 2 rows; the maximum marker row and the lowest ranked row for rank field[id=10100]

Please note, that this error is not related to a certain field ID, but happens with every field id.

The following articles did not work in our case.

https://community.atlassian.com/t5/Jira-Software-questions/Cannot-delete-move-et-actions-with-issues-due-to-the-proble-with/qaq-p/652749

https://confluence.atlassian.com/jirakb/com-mysql-jdbc-exceptions-jdbc4-mysqlsyntaxerrorexception-references-command-denied-to-user-872266070.html

https://confluence.atlassian.com/jirakb/reindexing-fails-with-expected-exactly-2-rows-error-in-jira-server-779158916.html

https://confluence.atlassian.com/jirakb/several-actions-such-as-issue-ranking-move-export-are-failing-779159169.html?_ga=2.125429644.704920931.1507581752-1571342893.1497082077&_gac=1.188669530.1507206442.Cj0KCQjwsNfOBRCWARIsAGITapYkvI0UJ3BiZaEzkPwI53LbolVuoFeC-bGXknM9cgVBFAQI1zGURWcaAjWxEALw_wcB

https://confluence.atlassian.com/jirakb/com-mysql-jdbc-exceptions-jdbc4-mysqlsyntaxerrorexception-references-command-denied-to-user-872266070.html

- We are using jira server on Ubuntu 18.04.4 LTS with mysql 14.14 Distrib 5.7.29

- We have a Mysql User jira_user and a corresponding database jiradb

mysql> SHOW GRANTS FOR 'jira_user'@'localhost';
+----------------------------------------------------------------+
| Grants for jira_user@localhost |
+----------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'jira_user'@'localhost' |
| GRANT ALL PRIVILEGES ON `jiradb`.* TO 'jira_user'@'localhost' |

Can anyone please help, as this is a very annoying issue. And we don't have any clue what is meant with "LexoRankIntegrityException: Expected exactly 2 rows; the maximum marker row and the lowest ranked row for rank field"

thanks in advance

Best regards,

Bob

[Bob · Accepted solution]

Ok, I fixed it via try and error myself. To be honest I am really upset that there is no possibility for us to contact atlassian and get support. I mean we were willing to pay for it, but no chance with a starter license. 

Anyway, if anyone is experiencing the same, here's what helped us.

Problem: 

Our problem was related to duplicate Rank fields. You can check this by going to:

Administration -> System -> Issues -> Custom Fields

Check our your Fields there, if you have duplicate entries there, you probably have the same problem as we did. 

Solution:

Detele the duplicate Fields. If you can delete them via "Actions" you're done, if you can't delete them, because the are "Locked" you need to unlock them first in the database.

Login to your Database, in our case it was Mysql.

1. Check for the fields you want to unlock:

 mysql> SELECT id,customfieldtypekey,cfname from customfield where customfieldtypekey like 'com.pyxis.greenhopper.jira%';

This will list the Fields, "cfname" is what you see in the Frontend.

2. Look for cossesponding item id to for those fields

mysql> SELECT * from managedconfigurationitem;

3. Place them into a sql statement to unlock them, for example:

UPDATE managedconfigurationitem set managed='false' where item_id in ('customfield_10300','customfield_10400','customfield_10500','customfield_10600','customfield_10700','customfield_10800','customfield_10900','customfield_11000','customfield_11100','customfield_11200','customfield_11300','customfield_11400','customfield_11500','customfield_11600','customfield_11700');

4. If you have unlocked them, you can now go back to the frontend and delete them.