How to get rid of errors with tables of Active Object when restoring Jira from backup

In this article we will talk about how to get rid of errors with Active Objects tables when restoring Jira from backup and write a plugin to remove unnecessary Active Objects tables from the backup archive file.

What are Active Objects tables?

The Jira database contains two types of tables: system tables, such as jiraissue, cwd_user, changegroup, and tables added by plugins installed in Jira. These tables are Active Objects tables. They can be easily distinguished from system tables by the prefix AO_XXXXXX.
AO means Active Objects, and XXXXXX is the hashcode that Jira generates for each plug-in. Hashcode is needed in order to be able to create tables with the same name in different plugins. Since after adding the prefix AO_XXXXXX to tables with the same name, the table names start to differ, and therefore they can be created in the database.

Active Objects is an ORM layer in Atlassian products. Read more about it here .
')
In Jira, you can view information about all the tables created by each plugin, if you go to Gear -> System -> Plugin Data Storage. For example, on my Jira instance, the information on plugins looks like this:



The screenshot shows that the Atlassian Notifications plugin created three tables that have hashcode 21F425

AO errors

Now let's see how these errors look in real time. Here is an example of the "unsupported field encountered: binary" error:

We fix the error "unsupported field encountered: binary"

Let's try to fix this error. To do this, unzip the backup file. Inside we will see two files: entities.xml and activeobjects.xml.

The entities.xml file contains system table data. The file activeobjects.xml contains data from Active Objects tables. We just need the activeobjects.xml file.

Open the file activeobjects.xml file and look for the word “binary” in the file. In my case, found 10 matches. These coincidences look like this:

<row> <string>Alexey Matveev</string> <string>alexey.matveev@aaa.com</string> <integer>1</integer> <timestamp xsi:nil="true"/> <string xsi:nil="true"/> <integer xsi:nil="true"/> <binary xsi:nil="true"/> <string xsi:nil="true"/> <string xsi:nil="true"/> <integer xsi:nil="true"/> <string>Europe/Moscow</string> <string>alexey</string> <string>alexey</string> </row> 

Delete all the lines <binary xsi: nil = "true" /> in the file activeobjects.xml file and try to restore Jira again.

This time the error "unsupported field encountered: binary" is fixed, but we see a new error:



Let's look at the contents of the atlassian-jira.log file:

 [INFO] [talledLocalContainer] com.atlassian.activeobjects.spi.ActiveObjectsImportExportException: There was an error during import/export with <unknown plugin> (table AO_6B9F04_AIO_USER):Could not import data in table 'AO_6B9F04_AIO_USER' column #10, value is too big for column which size limit is 10, value is: [INFO] [talledLocalContainer] Europe/Moscow 

If you search for information about this error on the Internet, then we will find just such a KB .

In this KB, it is proposed to change the dimension of field No. 10 to -1. Let's try.

 <table name="AO_6B9F04_AIO_USER"> <column name="DISPLAY_NAME" primaryKey="false" autoIncrement="false" sqlType="-9" precision="255"/> <column name="EMAIL_ADDRESS" primaryKey="false" autoIncrement="false" sqlType="-9" precision="255"/> <column name="ID" primaryKey="true" autoIncrement="true" sqlType="4" precision="10"/> <column name="LAST_LOGIN_DATE" primaryKey="false" autoIncrement="false" sqlType="93" precision="23" scale="3"/> <column name="LOCALE" primaryKey="false" autoIncrement="false" sqlType="-9" precision="255"/> <column name="LOGIN_DAYS_COUNT" primaryKey="false" autoIncrement="false" sqlType="4" precision="10"/> <column name="O_AUTH_TOKEN" primaryKey="false" autoIncrement="false" sqlType="-4" precision="2147483647"/> <column name="O_AUTH_TOKEN_SECRET" primaryKey="false" autoIncrement="false" sqlType="-9" precision="255"/> <column name="TABLEAU_KEY" primaryKey="false" autoIncrement="false" sqlType="-9" precision="255"/> <column name="TENANT_ID" primaryKey="false" autoIncrement="false" sqlType="4" precision="10"/> <column name="TIME_ZONE" primaryKey="false" autoIncrement="false" sqlType="-9" precision="255"/> <column name="USERKEY" primaryKey="false" autoIncrement="false" sqlType="-9" precision="255"/> <column name="USERNAME" primaryKey="false" autoIncrement="false" sqlType="-9" precision="255"/> <foreignKey fromTable="AO_6B9F04_AIO_USER" fromColumn="TENANT_ID" toTable="AO_6B9F04_AIO_TENANT" toColumn="ID"/> </table> 

Change:

 <column name="TENANT_ID" primaryKey="false" autoIncrement="false" sqlType="4" precision="10"/> 

on

 <column name="TENANT_ID" primaryKey="false" autoIncrement="false" sqlType="4" precision="-1"/> 

and try to restore Jira from our backup.

In the log, we see again the same errors. KB did not help.

uknown plugin

Let's once again carefully read the error. We will see that the data from the UKNOWN PLUGIN plugin cannot be imported. Why from an unknown plugin (UKNOWN PLUIGN)?
If you look at the screenshot below, you can see that some Active Objects do not have the names of the plug-ins to which they belong:



You can see that the red box has the name of the plug-in, but there is no plug-in in the blue box. This can happen if we installed the plugin, then deleted it and restarted Jira with the deletion of the .osgi_plugins folder. This type of Jira restart is recommended if there are any problems with the work of plug-ins.

Our table AIO_USER does not have a plugin name, so we can simply delete the table.
Let's find the definition of this table in the file activeobjects.xml, remove the found definition and try to restore Jira from the backup.

We will see another error:

 [INFO] [talledLocalContainer] com.atlassian.activeobjects.spi.ActiveObjectsImportExportException: There was an error during import/export with <unknown plugin> (table AO_6B9F04_AIO_USER):Could not create prepared statement for SQL query, [INSERT INTO PUBLIC."AO_6B9F04_AIO_USER" ("DISPLAY_NAME", "EMAIL_ADDRESS", "ID", "LAST_LOGIN_DATE", "LOCALE", "LOGIN_DAYS_COUNT", "O_AUTH_TOKEN", "O_AUTH_TOKEN_SECRET", "TABLEAU_KEY", "TENANT_ID", "TIME_ZONE", "USERKEY", "USERNAME") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)] 

The problem is that we deleted the table definition, but did not delete the data of this table. Let's find the data in this table in the activeobjects.xml file and delete it.

The data on the table starts with this tag:

 <data tableName="AO_6B9F04_AIO_USER"> 

And end with this tag:

 </data> 

Let's remove these tags and everything between these tags, and try again to restore Jira from the backup.

We will get another error:

 There was a problem restoring ActiveObjects data for the <unknown plugin> plugin. Caught exception with following message: Table "AO_6B9F04_AIO_USER" not found; SQL statement: ALTER TABLE PUBLIC.AO_6B9F04_AIO_REPORT ADD CONSTRAINT fk_ao_6b9f04_aio_report_owner_id FOREIGN KEY (OWNER_ID) REFERENCES PUBLIC.AO_6B9F04_AIO_USER(ID) [42102-185]. Please check the log for details. 

This means that there are more tables in the plugin that reference this table, so we still need to delete all the tables that reference the remote table. Then everything can go through the chain: you may need to delete the tables of this plugin, which refer to the deleted tables.

Therefore, it will be much faster to delete all tables with the AO_6B9F04 prefix from the backup file programmatically.

Let's write a plugin that will remove the tables from the backup file by the given prefix. The plugin will consist of webwork, which will receive the name of the backup file (the backup file must be in JIRA_HOME / import) and the prefix.

The source code of the plugin can be found here .

Create a plugin

Open the terminal and execute the following command:

 atlas-create-jira-plugin 

You must answer the questions like this:

 Define value for groupId: : ru.matveev.alexey.plugins.jira.cleanbackup Define value for artifactId: : clean-backup Define value for version: 1.0.0-SNAPSHOT: : Define value for package: ru.matveev.alexey.plugins.jira.cleanbackup: : Confirm properties configuration: groupId: ru.matveev.alexey.plugins.jira.cleanbackup artifactId: clean-backup version: 1.0.0-SNAPSHOT package: ru.matveev.alexey.plugins.jira.cleanbackup Y: : Y 

Editing POM.XML

The pom.xml file should look like this:
bitbucket.org/alex1mmm/clean-backup/src/master/pom.xml

We create WEBWORK, WEB SECTION and WEB ITEM

We open the terminal and execute:

 atlas-create-jira-plugin-module 

You need to answer the questions like this:

 Choose a number (1/2/3/4/5/6/7/8/9/10/11/12/13/14/15/16/17/18/19/20/21/22/23/24/25/26/27/28/29/30/31/32/33/34): 31 Enter Plugin Module Name My Webwork Module: : cleanbackup Show Advanced Setup? (Y/y/N/n) N: : y Module Key cleanbackup: : Module Description The cleanbackup Plugin: : i18n Name Key cleanbackup.name: : i18n Description Key cleanbackup.description: : Enter Action Classname MyActionClass: : CleanBackup Enter Package Name ru.matveev.alexey.plugins.jira.cleanbackup.jira.webwork: : Enter Alias CleanBackup: : CleanBackup Enter View Name success: : success Enter Template Path /templates/cleanbackup/cleanbackup/success.vm: : Add Another View? (Y/y/N/n) N: : N Add Another Action? (Y/y/N/n) N: : N Add Another Plugin Module? (Y/y/N/n) N: : Y Choose a number (1/2/3/4/5/6/7/8/9/10/11/12/13/14/15/16/17/18/19/20/21/22/23/24/25/26/27/28/29/30/31/32/33/34): 30 Enter Plugin Module Name My Web Section: : CleanBackup Enter Location (eg system.admin/mynewsection): admin_plugins_menu Show Advanced Setup? (Y/y/N/n) N: : n Add Another Plugin Module? (Y/y/N/n) N: : Y Choose a number (1/2/3/4/5/6/7/8/9/10/11/12/13/14/15/16/17/18/19/20/21/22/23/24/25/26/27/28/29/30/31/32/33/34): 25 Enter Plugin Module Name My Web Item: : CleanAO Enter Section (eg system.admin/globalsettings): admin_plugins_menu/clean-backup Enter Link URL (eg /secure/CreateIssue!default.jspa): /secure/CleanBackup.jspa? Show Advanced Setup? (Y/y/N/n) N: : Y Module Key clean-ao: : Module Description The CleanAO Plugin: : i18n Name Key clean-ao.name: : i18n Description Key clean-ao.description: : Weight 1000: : Link Id clean-ao-link: : Enter Label Key clean-ao.label: : CleanBackup Enter Label Value CleanAO: : CleanAO Add Label Param? (Y/y/N/n) N: : n Add Icon? (Y/y/N/n) N: : n Add Tooltip? (Y/y/N/n) N: : n Add Resource (Y/y/N/n) N: : n Add Velocity Context Provider (Y/y/N/n) N: : n Add Plugin Module Param? (Y/y/N/n) N: : n Add Conditions? (Y/y/N/n) N: : n Add Another Plugin Module? (Y/y/N/n) N: : n 

Editing WEBWORK and SUCCESS.VM

Let's change our success.vm so that our webwork could take two parameters: the name of the backup and the prefix:

clean-backup / src / main / resources / templates / cleanbackup / cleanbackup / success.vm .

We will use SAX to work with xml files, so we will write a filter for deleting tables:

clean-backup / src / main / java / ru / matveev / alexey / plugins / jira / cleanbackup / jira / webwork / TableFilter.java .
Now we will make changes to webwork.

clean-backup / src / main / java / ru / matveev / alexey / plugins / jira / cleanbackup / jira / webwork / CleanBackup.java

We collect and run our plugin

Go to the plugin folder and execute:

 atlas-run 

After Jira has started, we put our backup file in JIRA_HOME / import and go to the browser at the following address:

localhost : 2990 / jira / secure / CleanBackup.jspa

Enter the name of the backup, the prefix and click on the Clean button:



The tables with the prefix that we entered will be deleted from the backup and after that you can restore Jira.