Ranking issue fails with Data truncation: Data too long for column 'RANK' at row 1 error

Problem:

In systems with a large amount of users and issues, the ranking/clone/edit issue operation fails with the following error.

java.sql.DataTruncation: Data truncation
com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library:
Database:
	- name:Microsoft SQL Server
	- version:11.00.2100
	- minor version:0
	- major version:11
Driver:
	- name:jTDS Type 4 JDBC Driver for MS SQL Server and Sybase
	- version:1.2.4
java.sql.DataTruncation: Data truncation

The error may vary depending on your database type, mostly complaining on similar problem ie "value too long for type character varying(255)" in Postgres

Diagnosis:

To identify you are running into this bug, run the following SQL

1. SELECT "FIELD_ID", LENGTH("RANK") as rankLength, COUNT(1)  FROM "AO_60DB71_LEXORANK" GROUP BY "FIELD_ID", rankLength ORDER BY rankLength DESC
   

   This will show the sizes of rank values in the rank table. Note that rankLength values should not be approaching 255 (the maximum column size)

2. SELECT 'ROWS_IN_BUCKET_0', "FIELD_ID", coalesce(COUNT(1), 0) AS rows_in_bucket FROM "AO_60DB71_LEXORANK" WHERE "RANK" LIKE '0|%' GROUP BY "FIELD_ID"
   UNION
   SELECT 'ROWS_IN_BUCKET_1', "FIELD_ID", coalesce(COUNT(1), 0) AS rows_in_bucket FROM "AO_60DB71_LEXORANK" WHERE "RANK" LIKE '1|%' GROUP BY "FIELD_ID"
   UNION
   SELECT 'ROWS_IN_BUCKET_2', "FIELD_ID", coalesce(COUNT(1), 0) AS rows_in_bucket FROM "AO_60DB71_LEXORANK" WHERE "RANK" LIKE '2|%' GROUP BY "FIELD_ID" ORDER BY "FIELD_ID";

   This query will show the bucket distribution of rank values. If there are ranks in different buckets for a certain rank field and the maximum rankLength of the previous query is approaching 255 for the same rank field, then the system is close to rank exhaustion and will start generating ranks larger than the max column size thus triggering the SQL exception.

Workaround:

This will involve some manual database modification, please contact support while providing the information below:

1. the result of the following SQL

   SELECT * FROM "AO_60DB71_LEXORANK";
   

2. latest JIRA support zip
3. (If possible) a copy of an XML backup of your database (Administration > Backup System). You can anonymise if you wish by going to http://confluence.atlassian.com/display/JIRA/Anonymising+data?utm_source=support to speed up the testing process.