JIRA migration from mysql to postgres failed

[RVal]

Hi 

I was migrating JIRA from mysql to postresql database. Following Atlassian instructions I created postgres database and gave jira user full access to it. After successfully connecting to this postgres DB from JIRA connection wizard I tried to restore database from previously stored XML backup.

After running for a while it gave me 500 error complaining that some AO relation can not be found. When I checked postgres DB I see that indeed none of the Active Objects tables were created. Also I noticed there are 2 xml files inside exported ZIP file - entities.xml and activeobjects.xml. Is that possible that for some reason only entities.xml got imported and not activeobjects.xml ?

Any help is appreciated.

Thanks

[RVal · Accepted solution]

Looks like this is a known issue with restoring JIRA XML backup to PostgreSQL. I was able to find and use this workaround published by Atlassian: https://confluence.atlassian.com/jirakb/jira-import-fails-due-to-character-encoding-440108201.html

I wish they had included it into their migration doc so I would not have to spend so much time looking for it 

[Nic Brough -Adaptavist-]

That is a possibility, but there are a load of root-causes it might be.

Could you

1) Tell us what the "500 error complaining..." actually said?

2) have a look at the application's log file at the point at which you got that error to see if it tells you more about the root cause?

[RVal]

Thank you Nic for response, here is more details

1) 500 error says

java.lang.RuntimeException: org.apache.jasper.JasperException: 

com.atlassian.cache.CacheException: 

com.atlassian.activeobjects.internal.ActiveObjectsSqlException: 

There was a SQL exception thrown by the Active Objects library:
Database:
	- name:PostgreSQL
	- version:9.3.1
	- minor version:3
	- major version:9
Driver:
	- name:PostgreSQL Native Driver
	- version:PostgreSQL 9.0 JDBC4 (build 801)

org.postgresql.util.PSQLException: 

ERROR: relation "public.AO_60DB71_RAPIDVIEW" does not exist

2) Not sure if it is causing the issue but error I found in atlassian-jira.log is

SQL Exception while executing the following:

INSERT INTO public.jiraaction (ID, issueid, AUTHOR, actiontype, 

actionlevel, rolelevel, actionbody, CREATED, UPDATEAUTHOR, UPDATED, actionnum)

 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) 

(ERROR: invalid byte sequence for encoding "UTF8": 0x00)

[casupport]

This information help me solve the same sort of issue. We were migrating from MSSQL to PostgreSQL and I recalled seeing the SQL Exception in my error log. I ran the XML cleaner against my export and it did correct a block of "corrupt" data.

Afterwards the import process gave a notification it was successful, where prior it was failing silently and dropping back to the restore screen. Checking the database showed that all the AO tables which were previously failing to import were created and filled.

Thanks!