JIRA migration from mysql to postgres failed

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

2 answers

1 accepted

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 sad

0 vote

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?

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)

 

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! 

Suggest an answer

Log in or Join to answer
Community showcase
Teodora [Botron]
Published Feb 15, 2018 in Marketplace Apps

Jira Inferno: The Nine Circles of Jira Administration Hell

If you spend enough time as a Jira admin - whether you are managing a single, mid-sized instance, a large enterprise one or juggling multiple instances at once - you will eventually find yourself in ...

1,196 views 6 19
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot