Can't Create New Tokens, Not showing Existing Tokens In UI

Problem

Users can't create new tokens and/ or can't see existing tokens in the UI.

Cause

Cause 1

After upgrading API Token Authentication from versions that have now been long removed from the marketplace (1.9.0 and 1.9.1), one can't create new tokens (1) and/ or see existing ones (2) with the errors pictured below. The problem only affects customers using Microsoft SQL databases. The two versions mentioned were available in the marketplace from January 18/ 19th to January 27th 2022.

Cause 2

After restoring a backup, at least one token record in the database has a null value in a field that doesn't allow null values. The errors displayed should pretty much match the screenshot from above. We've seen that once only where a Postgres SQL database was used. The exact cause remains unclear, but we suspect a problem with Atlassian's code to restore a database backup.

Solution

For Cause 1

Run a query to see if you are really affected by cause 1:

Depending on where you run these queries or on what database type you are using you might need to omit the double quotes around the field and table names.
Committing the update statements is only required if you don't have auto-commit enabled for the database connection that you are using for the procedure.

SELECT COUNT(*) FROM "AO_01D1A0_ATA_TOKEN"
where "RATE_LIMIT_BUCKET_LIFETIME" is null and "RATE_LIMIT_BUCKET_SIZE" is null

If the query returns a count greater than zero, plan downtime for Jira, Confluence or Bitbucket to execute and commit the following update statement against our database table:

UPDATE "AO_01D1A0_ATA_TOKEN" set "RATE_LIMIT_BUCKET_LIFETIME" = 0, "RATE_LIMIT_BUCKET_SIZE" = 0
where "RATE_LIMIT_BUCKET_LIFETIME" is null and "RATE_LIMIT_BUCKET_SIZE" is null

For Cause 2

Run a query to see if you are really affected by cause 2:

SELECT COUNT(*) FROM "AO_01D1A0_ATA_TOKEN" WHERE "TOKEN_SCOPE" is null

If the query returns a count greater than zero, plan downtime for Jira, Confluence or Bitbucket to execute and commit the following update statement against our database table:

UPDATE "AO_01D1A0_ATA_TOKEN" SET "TOKEN_SCOPE" = 2 WHERE "TOKEN_SCOPE" is null

Tokens created without scope before the feature was actually available will be updated with the Read/ Write scope with that statement.

Last But Not Least

It's unlikely that you are affected by both causes 1 and 2 but should that be the case you can run the two update statements below during the same downtime.