ranking and issue deleting problem after restoring from backup

[hakansrc]

Currently, I cannot change the order of the issues on the agile board and cannot delete any issues.

When I try to change the order "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." warning appears. I tried reindexing but the problem is not resolved. 

When I try to delete the issues, a problem appears related to LexoRank management. 

When I try to do balancing, Field ID is not VALID error appears. Moreover, it seems that I have 0 ranked issues.

[Andy Heinzer · Accepted solution]

Hi @hakansrc 

Sorry to hear about this problem.  I understand that you have restored a backup and since that time Jira's lexoranking is throwing errors.  From what I can see of the screenshots so far, it looks like the ranking field is missing data.

Check out the KB in Several actions such as issue ranking, move, export are failing.  You will likely need to run a SQL query against the database to better understand what is happening here.  For example, we can adapt that KB query to suit your environment and see what is returned by:

SELECT "FIELD_ID", "ID", "ISSUE_ID", "LOCK_HASH", "LOCK_TIME", "RANK",
"TYPE"
FROM "AO_60DB71_LEXORANK"
WHERE "FIELD_ID" = 10000 AND "TYPE" IN (0, 2);

If this was working correctly, there would be exactly two rows returned by this query.   But I'm guessing that you're missing a marker or have duplicates.  If that is the case, then that KB has steps you can follow to correct this problem by inserting those marker rows to that table.

If that doesn't resolve this, then yes, I'd also like to take a closer look at the atlassian-jira.log file from this time to better be able to see the details of the error messages here.

Let me know if you run into any problems with this.

Andy

[hakansrc]

the "FROM "AO_60DB71_LEXORANK" line returned syntax error. Does it mean that I do not have such an element in my database?

[Nic Brough -Adaptavist-]

Possibly.  What is the error you get?

[hakansrc]

mysql> USE jiradb2
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT "FIELD_ID", "ID", "ISSUE_ID", "LOCK_HASH",
-> "LOCK_TIME", "RANK",
-> "TYPE"
-> FROM "AO_60DB71_LEXORANK"
-> WHERE "FIELD_ID" = 10000 AND "TYPE" IN (0, 2);
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"
WHERE "FIELD_ID" = 10000 AND "TYPE" IN (0, 2)' at line 4

[Andy Heinzer]

That's an odd error.  It doesn't tell us what's in the table.  Rather there is a problem with the syntax of the query somehow.  Try this one instead:

SELECT * FROM "AO_60DB71_LEXORANK" WHERE "FIELD_ID" = 10000 AND "TYPE" IN (0, 2);

[hakansrc]

again,  a syntax error is shown.

SELECT * FROM "AO_60DB71_LEXORANK", WHERE "FIELD_ID" = 10000 AND "TYPE" IN (0, 2);
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", WHERE "FIELD_ID" = 10000 AND "TYPE" IN (0, 2)' at line 1

[Andy Heinzer]

Try this instead:

SELECT * FROM AO_60DB71_LEXORANK WHERE AO_60DB71_LEXORANK.TYPE IN (0, 2);

Did some research to find that MySQL doesn't use this syntax that is required in PostgreSQL.  This query should return exactly two rows for that rank field.  Please let us know the results of this query.

Andy

[hakansrc]

It returned;

mysql> SELECT * FROM AO_60DB71_LEXORANK WHERE AO_60DB71_LEXORANK.TYPE IN (0,2);
Empty set (0.00 sec)

[Andy Heinzer]

Try this. 

• Stop Jira,
• Run the following SQL commands to add these marker rows:

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

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

• Start Jira again.
• After Jira has started, try to do a re-index
• Once a re-index is complete, check the Lexorank management page to see if the ranking problem might be resolved by this alone.

If that doesn't solve the problem I'd be interested to see what the ranking management page has to say after these steps.

Andy

[hakansrc]

Thanks for the reply, the problem has been solved. Now I can rank and/or delete the issues as I want. 

Thank you so much 

[Nic Brough -Adaptavist-]

What is the error you get with the lexorank balancing?  Could you give us the full text?

[hakansrc]

The error when balancing and the integrity check results are provided below. Note that we have several projects with hundreds of issues.

[Nic Brough -Adaptavist-]

This looks like you have some quite severe corruptions in your database (probably limited to the ranking though)

I would want to tail the logs (read them as they are being written) as you try the rebalancing, the re-index and the integrity checks.  At least one of those (but probably all three) will throw errors into the logs telling us more about what is damaged.

[hakansrc]

The log shows the following message when I try balancingMoreover, when I try to delete an issue from the agile board, the following warning pops up and I cannot delete the issue.

[Nic Brough -Adaptavist-]

We need to see all of the error, not a screenshot of part of it.

Could you give us the text of it please?  Both the on-screen and the log

[hakansrc]

I tried to add here directly but I guess the size is too large. I uploaded the text of in this cloud link.

[Nic Brough -Adaptavist-]

Sorry, We have no access to that.