SQL problems importing xml into Jira Server, how to fix and want to know what the tables are for

[Thomas Barraro]

I was able to download the cloud xml for server, but I ran into when importing into Jira Server on premise

>> ->com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table
>> ‘jiradb.AO_21F425_MESSAGE_AO’ doesn’t exist
>>
>> ->com.atlassian.activeobjects.spi.ActiveObjectsImportExportException:
>> There was an error during import/export with <unknown plugin>:Error
>> executing update for SQL statement 'ALTER TABLE
>> AO_54307E_REQUESTTYPE_TO_ISSUE ADD CONSTRAINT
>> fk_ao_54307e_requesttype_to_issue_issue_id FOREIGN KEY (ISSUE_ID)
>> REFERENCES JIRAISSUE(ID)'

[Andy Heinzer]

What version of Jira Server are you using here?  There is an expectation that you would be using the latest version of Jira Server when importing a site backup from Cloud.

Also when you do this import are you using the import option in the setup wizard, that has the link to 'import from backup'?   Or did you complete the setup wizard and then try to use the System administration tools to try to do this import?  

For the sake of a Cloud to Server migration, you should be using the setup wizard in order to restore that data correctly to a Server instance.

[Thomas Barraro]

Hi Andrew I tried doing it your way, and ended up with this error

```(SQL Exception while executing the following:INSERT INTO jiraaction (ID, issueid, AUTHOR, actiontype, actionlevel, rolelevel, actionbody, CREATED, UPDATEAUTHOR, UPDATED, actionnum) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (Incorrect string value: '\xE2\x97\x8F fi...' for column 'actionbody' at row 1))```

could you please explain this further? 

In addition, what I don't understand, is how when I made a cloud server to server xml backup last time, which came after this issue was made(the one being referenced in the sql error, it worked fine. But when I created the same xml a few days ago, all this is happening? 

[Nic Brough -Adaptavist-]

This doesn't seem directly related at first, but are you importing into a MySQL installation?  I think one of your users has added some data that can't handle.

[Thomas Barraro]

Yes I am importing into mysql, but am curious what this wasn't an issue a couple of months ago when I tried.

[Nic Brough -Adaptavist-]

As I said, I think one of your users has added some data that can't handle.

The \xE2..... string looks like encoding for an emoji, which Postgres (the database behind Cloud) can handle, but MySQL falls over on it.

You can try setting a different encoding on the MySQL server (but this is not fully supported), or run the xml cleaner over the exported file - see https://confluence.atlassian.com/jira/removing-invalid-characters-from-xml-backups-12079.html

[KellyW]

Hi Thomas,

AO_21F425_MESSAGE_AO is specific to "Atlassian Notifications" plugin. 

For starter, check if "Atlassian Notifications" system plugin on the JIRA Server instance is installed and enabled.

Regards,
Kelly

[Martin Baltuhin]

we seem to hit the same issue with 8.13 (xml)export to another host. Is it safe to ignore if plugin is file and import did not fail? Log is flooded with that error during processing import.

[Nic Brough -Adaptavist-]

No, you should change the database settings or run the XML cleaner over the export.

[Martin Baltuhin]

what do you mean by database settings? what is the alternative to xml cleaner?

[Nic Brough -Adaptavist-]

I really do mean you need to look at the database settings.  I don't know how else to say that you need to configure your database to support your installation as per the Atlassian docs.

The XML cleaner is a nice tool for when we migrate between databases or make mistakes.  There's no real alternative to it beyond getting your people to not add bad data to your systems.