Upgrading from Version 10 to Version 11

From FMR Knowledge Base
Revision as of 07:49, 12 April 2022 by Plazarou (talk | contribs) (Create a SQL file of database tables)
Jump to navigation Jump to search

Overview

The structure of the operating database has changed in Fusion Metadata Registry (FMR) version 11 making it incompatible with version 10. An existing FMR 10 installation cannot be directly upgraded to version 11, and a version 11 database cannot be used with version 10 of FMR.

If you attempt to start an FMR instance using a version 10 database it will fail to start. We recommend using an empty database and loading structures into it. To upgrade from 10 to 11 please follow the steps below.

If you wish to preserve structures and settings from a version 10 database please see the section below titled: Migrating Structural Metadata History and Settings

Backing Up an Existing Fusion Metadata Registry

To create a file containing all of the structural metadata within your Registry, please perform the following:

  • In your current version 10.X FMR, navigate to the Export Structures / Bulk Actions page.
  • Click on the green "Download all structures" button
  • In the modal that appears ensure that the format is specied as Fusion-JSON. You may choose to download as a Zip file so that the generated film is smaller in size.
  • Download the structures and store them safely as these will be used to upload in your Fusion Registry version 11

You may now stop the Apache Tomcat running this instance of FMR.

Starting the Version 11 Fusion Metadata Registry

If you attempt to start FMR against a version 10 database, the Registry will detect this and will halt the startup process. It is highly recommended to use an empty database.

To start FMR version 11

  • Deploy the war file into your existing Java WebServer (e.g. Apache Tomcat).
  • Start the Java Web Server and wait for the FMR to fully deploy
  • Using your browser navigate to the front page of FMR.
  • Step 1 of the install wizard will be shown. Supply the details of your new database.
  • On Step 2 of the install wizard set the server location and the root account.
  • Finish the install wizard.

You will now have a running FMR (although with no content) that has an associated database for persistance of your structures and settings

Login and Upload Structures

You should login to the Fusion Registry and upload the structure file you obtained from FMR version 10.

  • Login to the FMR
  • Navigate to the front page
  • Using the "Load Structures" control found at the top right of the page, load the structure file
  • Wait - the time taken to load will depend on the size of the structures present in your file.
  • Ensure that the structures have loaded by looking at individual pages or the Export Structures / Bulk Actions page to see an overview.

In the unlikely event that there are any issues loading the structures, please check the Web Server log files and also the "Server Integrity" page (located in the left-hand menu bar) which reports any structures that break the Registry Integrity. If you need help, please contact Metadata Technology for further support.

Migrating Structural Metadata History and Settings

When migrating from FMR 10 to version 11 it is simpler to migrate metadata via an exported SDMX JSON file (as described earlier in the page). However it is possible to preserve the following information from FMR 10:

  • All metadata structures
  • The individual history of each structure
  • Many of the settings
  • Some of the audit information

The instructions are detailed below. Within these instructions the database that is used for FMR 10 will be refered to as FMR_10_DB and the database used for FMR 11 as FMR_11_DB. Of course your database names will be different. FMR_11_DB should be an empty database.

For information about the purpose of each table in FMR, please refer to this page .

Instructions

To achieve this task we want to copy particular tables from the FMR 10 database to a clean database for FMR 11 and then make modifications to those tables before attempting to start FMR 11. The following instructions detail how to make an intermediary SQL file and edit that file.

Create a SQL file of database tables

Using the database tool of your choice (for example SQLYog) connect to your FMR 10 database. Create a file which is the SQL Dump of the table Structure and Data for the following tables (note the spelling of particular tables):

  • registry_audit
  • registry_maintainable_sdmx
  • registry_maintainables
  • registry_settings
  • registry_settings_dv
  • registry_settings_ser
  • sdmx_backup
  • sdmx_transaction
  • sdmx_transaction_item
  • tx_backup

How you perform this task is down to the individual tool and the database you are using.

For MySQL databases, the tool SqlYog has a convenient way to achieve this. Via the menu option: Database -> Backup/Export -> Backup Table(s) as SQL Dump... within the modal that appears, select only the tables listed above. Ensure the radio button "Structure and Data" is checked. And save this to a single file.

The file produced may be quite large - the size depends on the content of your Registry.

Edit the generated SQL file

View the generated file in an editor of your choice. Notepad will be sufficient for this, but feel free to use any editor you choose.

All occurrences of your FMR version 10 database need to be changed to reference the FMR version 11 database within the file. It is important to change these values otherwise when you execute the SQL file it will operate against the wrong database. Once these changes have been made save the file.

By way of example, generating a file from SqlYog will create a file containing 3 occurrences of the FMR_10_DB. These are:

  • In an intial comment
  • In a "CREATE DATABASE" statement
  • in a "USE" statement

Load the SQL file into your database

Now it is time to load the modified file against the version 11 database. At this stage this database should be empty. How the file is loaded and executed is down to the tool you are using.

Using SqlYog use the menu option: Database -> Import -> Execute SQL script. In the modal that appears select the file and click "Execute". Wait whilst the file executes.

Once it has finished check the output for errors. If there are no errors your version 11 database should now contain the 10 tables listed above.

Make final modifications to the tables and data

Run the following MySql script in a window. NOTE: change the first line to the actual name of your FMR_11_DB:

USE `FMR_11_DB`;
DELETE FROM registry_settings WHERE NAME = 'installed.version';
DELETE FROM registry_settings WHERE NAME = 'registry.url';
DELETE FROM registry_settings WHERE NAME LIKE 'EXCEL_TEMPLATE_PWD_%';
COMMIT;

Then

ALTER TABLE sdmx_transaction_item MODIFY COLUMN id VARCHAR(255) NOT NULL; COMMIT;

Start FMR 11

  1. Start the Tomcat that will be running FR11. This should direct to step 1 of the Install Wizard.
  2. On step 1 specify the details for database FMR_11_DB.
  3. The Registry should now show install page 2. If it doesn't something has gone wrong.
  4. On install wizard step 2 enter the password for the root user. Complete the install wizard.
  5. The front page of the Regsitry should be shown. Remember that security prevents viewing of unauthorized structures. Either use Fusion Security Manager to give permission to public or log on.
  6. Check that the structures are present. Check that individual structures have history