JIRA Entity Mismatch with Database Schema
Symptoms
During normal usage, JIRA throws exceptions indicating mismatches between the DB entity and the database schema:
SQL Exception while executing the following:
INSERT INTO external_entities (ID, NAME, entitytype) VALUES (?, ?, ?) (***: cannot insert NULL into ("ATLASSIAN"."EXTERNAL_ENTITIES"."TYPE"))
During startup, errors look like:
Column "TYPE" of table "external_entities" of entity "ExternalEntity" exists in the database but has no corresponding field.
...
Table named "ATTACHMENTDATA" exists in the database but has no corresponding entity.
...
Or:
[core.entity.jdbc.DatabaseUtil] Table named "PUBLIC.AO_60DB71_COLUMN" exists in the database but has no corresponding entityOr:
[core.entity.jdbc.DatabaseUtil] Column "ISSUETYPE" of table "workflowschemeentity" of entity "WorkflowSchemeEntity" exists in the database but has no corresponding field
[core.entity.jdbc.DatabaseUtil] Entity "WorkflowSchemeEntity" has 4 fields but table "workflowschemeentity" has 8 columns.
Or:
main WARN [core.entity.jdbc.DatabaseUtil] Table named "BIN$OEX/KLL8IFJGRAAHKGVYDG==$0" exists in the database but has no corresponding entityCause
There are four known causes for errors such as there:
- Another application is sharing the JIRA database schema, or the "external_entities" table is corrupted in some way. This may happen after upgrading JIRA.
- Caused by GreenHopper, especially when utilizing Labs features. Table names that start with "AO_60DB71" are GreenHopper tables.
- JIRA is using a database that requires a schema (such as Oracle or MSSQL), but no schema is defined. In this case, all tables will be stored on the root schema, and occasionally other applications will store tables under this schema. This must be fixed, as in our Incorrect MS SQL Schema Used KB.
- A unique quirk in Oracle. Try emptying the Oracle recycling bin.
In the first example above, JIRA and Confluence are sharing the same Schema. In the last example, GreenHopper Rapid Boards have been activated and de-activated repeatedly while the feature was in Labs. The last example is the unique quirk in Oracle.
Resolution - Multiple Applications using the same database or external_entities table is corrupt
Overview
Create an independent database schema for each Atlassian application, or specify permissions for the database users so the schemas are separated. In Oracle, remove the 'select any table' permission.
Check the external_entities table
Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
By default, the external_entities table should just contain 3 fields (ID, NAME and entitytype). Use your favorite database explorer and investigate the "external_entities" table. For example, in MySQL:
mysql> show columns from external_entities;
+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| ID | decimal(18,0) | NO | PRI | NULL | |
| NAME | varchar(255) | YES | MUL | NULL | |
| entitytype | varchar(255) | YES | | NULL | |
+------------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)If you see more than these three columns listed, try dropping the extra column and see if it solves the problem. For example:
ALTER TABLE external_entities DROP COLUMN type;Split the two application instances and recreate the external_entities and trustedapp tables
Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
- Find an off-business time, shut down both JIRA and the other application instance. In this example, Confluence.
- Clone the current JIRA database to a new one.
- Point Confluence to the new cloned database.
Make the below adjustments for the current JIRA database:
DROP TABLE external_entities; DROP TABLE trustedapp; CREATE TABLE external_entities (ID NUMERIC NOT NULL, NAME NVARCHAR(255), entitytype NVARCHAR(255), CONSTRAINT PK_external_entities PRIMARY KEY (ID)); CREATE TABLE trustedapp (ID NUMERIC NOT NULL, APPLICATION_ID NVARCHAR(255), NAME NVARCHAR(255), PUBLIC_KEY NTEXT, IP_MATCH NTEXT, URL_MATCH NTEXT, TIMEOUT NUMERIC, CREATED DATETIME, CREATED_BY NVARCHAR(255), UPDATED DATETIME, UPDATED_BY NVARCHAR(255), CONSTRAINT PK_trustedapp PRIMARY KEY (ID));
The above SQL syntax may need to be modified, depending on your DBMS.- Restart JIRA.
- Back up the JIRA data using an XML Backup.
- Shut down the JIRA instance.
- Drop the current JIRA database and create a brand new one with the same port/schema/character_encoding.
- Restart the JIRA instance, which will automatically re-create all JIRA tables into the brand new JIRA database.
- Restore the JIRA XML backup.
- If using JIRA for user management, follow the Delegate user guide to rebuild the user sharing between JIRA and Confluence.
Resolution - GreenHopper Labs
Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
This resolution will remove all your GreenHopper settings and ranking. You will need to reset the GreenHopper settings and ranking after performing the steps listed here. These steps are ONLY valid for older versions of GreenHopper running on JIRA 4.4.X, or upgrading from that version (GreenHopper 5.8.7) to newer versions.
- Make sure the database user has the following permissions:
- CREATE SESSION
- CREATE TABLE
- CREATE TRIGGER
- CREATE SEQUENCE
- Stop JIRA.
Drop all AO tables:
drop table AO_60DB71_COLUMN cascade constraints; drop table AO_60DB71_COLUMNSTATUS cascade constraints; drop table AO_60DB71_ISSUERANKING cascade constraints; drop table AO_60DB71_ISSUERANKINGLOG cascade constraints; drop table AO_60DB71_QUICKFILTER cascade constraints; drop table AO_60DB71_RANK_ISSUE_LINK cascade constraints; drop table AO_60DB71_RAPIDVIEW cascade constraints; drop table AO_60DB71_STATSFIELD cascade constraints; drop table AO_60DB71_SWIMLANE cascade constraints; The above SQL syntax may need to be modified, depending on your DBMS.- Restart JIRA.