How to clean irrelative ActiveObjects data in Jira backup programmatically

[#1]

Hello!

In this article we will learn how to handle errors with ActiveObjects data upon restoring a Jira backup, and we will write a Jira plugin to clean the ActiveObjects data from a Jira backup file.

While installing a backup, we can come across errors with ActiveObjects data. For example, we can see the following errors:

unsupported field encountered: binary.

There was an error during import/export with <unknown plugin>:Could not import data.

What is Active objects?

The Jira database contains two kind of tables: system Jira tables like jiraissue, cwd_user, changegroup and so on and additional tables, which are added by plugins. Tables, which are added by plugins, are easy to distinguish: they all have the AO_XXXXXX_ prefix.

AO means Active Objects and XXXXXX is a hashcode generated by Jira for each plugin. This hashcode is needed in case if two different plugins have a table with the same name. Adding this prefix to tables with the same name, let us have both these tables in the Jira database.

Active Objects is an ORM (object relational mapping) layer into Atlassian products. You can read more about Active Objects here.

We can see all Active Objects tables, generated by plugins, if we go to cog item -> System -> Plugin Data Storage. Here is a screenshot from my Jira instance:

In the screenshot above we can see that the Atlassian Notifications plugin has three tables with the 21F425 hascode. 

AO errors

Let us see how these errors look like in real time. Here is an example of the unsupported field encountered: binary error:

 

Fix the unsupported field encountered: binary error

Let us try to fix the error. We should unzip the backup file. We will see two files inside: entities.xml and activeobjects.xml. The entities.xml file contains entities for Jira system tables. The activeobjects.xml contains entities for ActiveObjects data. We need to open the activeobjects.xml file and search for the "binary" word. In my case I have 10 entries 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>

We delete all <binary xsi:nil="true"/> entries in the activeobjects.xml file and try to import data again.

This time the unsupported field encountered: binary error was fixed but we have another error:

Let us see what is in the logs:

[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 we google about this error, we will find this KB. In this KB it is written, that we have to change precision for column#10 to -1. Let us try it:

<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>

 Let us change:

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

 to

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

and try to import again. And we have the same errors in the logs. KB did not help.

<uknown plugin>

If we have a closer look at the error, we will notice that ActiveObjects from <UKNOWN PLUGIN> could not be imported. Why UKNOWN?

If you have a look at my screenshot below, you will notice that some of the Active objects tables do not have the name of a plugin near them:

 We can see that for the tables in the red rectangle we have a plugin name, but for the tables in the blue rectangle  there is no plugin name. It can happen, if you installed a plugin, then you removed this plugin and restarted Jira with removing the .osgi_plugins folder. This kind of restart is sometimes recommended, when there are problems with plugins.

Our AIO_USER table also does not have a plugin name, that is why we can just remove this table. Let's find the definition of this table  in our activeobjects.xml file, remove this definition and restore our backup again. And we have 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 in the activeobjects file there is also data for the removed table. We need to find this data in the file and delete it. The data for our table begins with this tag:

 <data tableName="AO_6B9F04_AIO_USER">

and ends with:

 </data>

Let us delete everything between these tags and try to restore this backup again. And we have 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.

It means, that there are other tables in the removed plugin, which reference the deleted by us table. We would need to find all these tables and their data and remove them. Also there could be other tables, which would reference the removed tables. It would be much easier to write a program, to which we would pass AO_6B9F04, and this program would remove all tables, which name begins with AO_6B9F04.

Let's write such a plugin. This plugin will have a webwork, which will receive two parameters: the name of the backup file, which must be located in Jira home/import folder, and AO_XXXXXX, which is the prefix of all tables, which we have to remove.

You can take the source code of this plugin here.

CREATE A PLUGIN

Open terminal and run:

atlas-create-jira-plugin

 Answer the following way to the questions:

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

MODIFY POM.XML

Your pom.xml file should look like this:

https://bitbucket.org/alex1mmm/clean-backup/src/master/pom.xml 

CREATE WEBWORK, WEB SECTION AND WEB ITEM

Open terminal and run:

atlas-create-jira-plugin-module

Answer the following way to the questions:

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 (e.g. 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 (e.g. system.admin/globalsettings): admin_plugins_menu/clean-backup
Enter Link URL (e.g. /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

CHANGE WEBWORK AND SUCCESS.VM

Let's change our success.vm so that our webwork could accept two parameters: backup name and ao table prefix.

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

We will use SAX to work with xml files. We need to write a Filter to remove tables.

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

Then we modify our webwork file to clean tables:

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

BUILD AND RUN OUR PLUGIN

Go to the plugin folder and run 

atlas-run

After Jira started, put a backup file to Jira home/import and go to the following url in your browser:

http://localhost:2990/jira/secure/CleanBackup.jspa

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

Tables with the entered prefix will be removed from the backup file and you can restore the backup without errors.

That is all for this article. 

Thank you for reading.

[Gael Courcelle]

The Jar file of the plugin is it available ?

[Alexey Matveev]

It is not available. You would need to package this tutorial. It is a tutorial, not a real product.

[Gael Courcelle]

Ok I'll try thanks !

[Gael Courcelle]

I try your tutorial, but I have an error at the ultimate step of buildin my jar :(

[INFO] ------------------------------------------------------------------------
[INFO] BUILD FAILURE
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 7.309 s
[INFO] Finished at: 2019-02-19T08:49:45Z
[INFO] ------------------------------------------------------------------------
[ERROR] Failed to execute goal com.atlassian.maven.plugins:amps-dispatcher-maven-plugin:8.0.0:run (default-cli) on project clean-backup: Couldn't detect an AMPS plugin to dispatch to -> [Help 1]

[Eya BEN OTHMEN]

Hello @Alexey Matveev 

Im trying to restore to our DEV environment from a backup took from our PROD, but gettin an error at the end of the restore.

I cannot edit the activeobjects.xml file because it is very large. Is there another way please?

I can't open it with notepad+

Could you help me please?

[Sylvain Leduc]

You can use Emacs to edit large objects such as these. I did it, it works like a charm.

https://www.gnu.org/software/emacs/download.html