Active Objects Sequence does not exist

Summary

This problem typically occurs after doing a database migration, e.g. from Oracle to Postgresql. Your instance of Jira may or may not have been upgraded/migrated, it is not a requirement.

What you might notice is that your plugins stop working correctly or not at all, and in the Jira logs, you see an error like the one below.

atlassian-jira.log

The reference you will see through the document.

AO_###### → refers to the Active Object Id, related to a specific plugin.

AO_######_[name] → Refers to the Table name.

AO_######_[name]_ID_seq → Refers to the Sequence name.

This is an example of what can be seen, N.B. it is not limited to any specific plugin table, that part is generic:

atlassian-jira.log

Diagnosis

1. While trying to use any specific plugin it won't work correctly or at all.

2. After reviewing the Logs you may find the next errors that say the sequence does not exist.

   atlassian-jira.log

   1 2 3 4 5 6 com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library: ... org.postgresql.util.PSQLException: ERROR: relation "public.AO_######_[name]_ID_seq" does not exist ... Caused by: org.postgresql.util.PSQLException: ERROR: relation "public.AO_######_[name]_ID_seq" does not exist

3. In the Database run the query below (where 'public.AO_######_[name]_ID_seq') is the sequence name in question. If you have a result, this means the sequence exists, if nothing is returned then it means it does not exist at all and must be created.

   1 SELECT relname FROM pg_class WHERE relname = 'public.AO_######_[name]_ID_seq';

Cause

While migrating the data to a new database, the specific sequence tables were not created at all.

Solution

1. Recreate the sequence manually, this will need to be done with the understanding that we need to set up the value as the latest.

   1. To know which table is the sequence related to.

      1 SELECT table_name, table_schema FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'AO_######_[name]'; 

   2. To know the latest value of your sequence.

      1 SELECT max("ID") FROM AO_######_[name]; 

   3. Before creating the sequence verify the type of data is the "ID" Attribute.

   4. To create the sequence, run the below queries.

      1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 # Create the sequence CREATE SEQUENCE public."AO_######_[name]_ID_seq" AS [integer|bigint] # _as the type of the ID field START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; # setup proper ownership ALTER TABLE public."AO_######_[name]_ID_seq" OWNER TO [jiradbuser]; ** jiradbuser -> refers on your Jira Db user on your instance. # setup proper value SELECT setval('[sequence_name]', [max ID value]);