System not working after upgrade from H2 to MySQL

[Claude Brown]

Hi, 

After recently purchasing a license from Atlassian, I am not trying to upgrade the system from the H2 database to MySQL.  I've followed the intructions here:

https://confluence.atlassian.com/adminjiraserver/switching-databases-938846867.html

Everything has gone ok, except that after restoring the XML backup file many things are broken:

- The system started "unlicensed" and I had to re-enter the license key
- Our projects are completely missing, as are all the issues
- The Insight marketplace application says "unexpected error" 
- All the Insight data is missing

In short, it appears that the XML backup load didn't work.  It however gave me a message saying it was successful and had loaded in the order of 5,000 data items.  Strangely some items ARE restored; for example it knows our company name on the dashboard, plus in the marketplace it knows that we had the Insight app installed.

We are running on an Ubuntu 18.04 with MySQL 5.7.

I'm not really sure where to go from here.  I repeated the procedure after restoring the system (using a VM snapshot) and ended up in the same position.

Has anyone done this upgrade?  Any tips?

Thanks,

Claude.

[Claude Brown · Accepted solution]

DPK_J,

I found my mistake!   I came down to the MySQL login.  I created it without the "REFERENCES" permission because I'd followed the "pre v5.7.6" instructions.  However my database is 5.7.30, which is later.

I re-cloned the virtual-machine, took an XML backup, changed dbconfig.xml to point at MySQL (this time the user had "REFERENCES" permission) and loaded the XML backup.

Everything now working!  Thanks for your assistance.

Cheers,

Claude.

[DPKJ]

@Claude Brown  Welcome to the community.

I think you might have missed some step during switching database, I'm giving you outline of steps to follow and we can move ahead and see what you missed,

1. Backup your current system in XML format (https://confluence.atlassian.com/adminjiraserver073/backing-up-data-861253815.html)
2. Backup Jira Home and Installation directory (for safety)
3. Create new database in your MySQL Server, and make it accessible to your Jira server (https://confluence.atlassian.com/adminjiraserver/connecting-jira-applications-to-mysql-5-7-966063305.html)
4. Stop your Jira
5. Delete dbconfig.xml file from your Jira Home directory of existing installation
6. Start Jira
7. You will be fall in setup wizard, where you need to configure your Jira to use MySQL database (created in step - 3)
8. On Application Properties config page, select option to import your existing data. Provide your XML backup here (created in step-1)

I hope this helps. If you face any difficulty do come back.

[Claude Brown]

Thanks very much for your reply DPK.    Unfortunately these are exactly the steps I followed.  But mistakes happen, so I repeated the steps again carefully double-checking the instructions as I went along.  Same result.

I ensured the database was created with the correct character-set, and that my mysqld had the correct configuration; these were my commands:

CREATE DATABASE jira CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX on jira.* \ TO 'jiraweb'@'localhost' IDENTIFIED BY 'some-password';

FLUSH PRIVILEGES;

I added these mysql configuration lines, and restarted mysqld:

default-storage-engine=INNODB

character_set_server=utf8mb4

innodb_default_row_format=DYNAMIC

innodb_large_prefix=ON

innodb_file_format=Barracuda

innodb_log_file_size=2G

I did the XML backup beforehand - it generated a 680K .zip file.   After going through the setup (by deleting dbconfig.xml) the "Test Connection" worked fine.  I also ensured I selected "Import" in the last step - it reported about 5000 items imported.

Do you know if this process should also copy application data from the marketplace apps? I'm using "Insight" with Jira Service Desk and it has quite a lot of data in it.

[Claude Brown]

DPK_J,

I found my mistake!   I came down to the MySQL login.  I created it without the "REFERENCES" permission because I'd followed the "pre v5.7.6" instructions.  However my database is 5.7.30, which is later.

I re-cloned the virtual-machine, took an XML backup, changed dbconfig.xml to point at MySQL (this time the user had "REFERENCES" permission) and loaded the XML backup.

Everything now working!  Thanks for your assistance.

Cheers,

Claude.

[DPKJ]

Hurray! Great work.