Known Issue Oracle Database

Overview

The use of an Oracle Database as the persistent database storage for the FMR is supported and there is an article here about setting up the database and installing the appropriate JDBC Driver. However, in all versions of FMR before version 11.19.5 there is a potential issue with using an Oracle database. Database tables are automatically created by FMR but in versions before 11.19.5 these tables may be created with particular columns too short to accommodate the information needed to be stored. This has been addressed in subsequent FMR releases, but simply upgrading to a new release may not address this issue.

Please note that we have experienced this issue in Oracle 19c. Other versions of Oracle may manifest the issue in a different manner, or may not suffer from the issue. It is recommended checking your database to ensure that the table definitions are able to accommodate required data.

The Issue

The reason for this issue is that the codebase of FMR uses the Hibernate library to generate tables automatically in the database. Hibernate, by design, does not modify existing table definitions. This means that any database tables created in a prior release of FMR may be affected and will not be modified by moving to version 11.19.5 or later.

The original definitions within the codebase where that the columns were of a type called "text". Hibernate combined with Oracle caused an issue where these columns were defined as varchar2(255). This may not always be long enough to store the required data. From version 11.19.5 of the FMR onwards, these Hibernate definitions have been changed.

The errors you may experience when using Oracle vary, but may be displayed in the FMR user interface like so:

Or errors displayed in the FMR logs:

 Caused by: java.sql.SQLSyntaxErrorException: ORA-01754: a table may contain only one column of type LONG

 Exception executing batch [java.sql.BatchUpdateException: ORA-12899: value too large for column "FMR"."REGISTRY_SETTINGS"."VALUE" (actual: 431, maximum: 255)

Below is detailed the affected database tables and columns. It also shows the column definitions in versions prior to version 11.19.5 and what these columns should be defined as in versions from 11.19.5 onwards:

Table Name	Column Name	Definition prior to 11.19.5	Definition after 11.19.5
registry_settings	value	varchar2(255)	varchar2(4000)
registry_environment	name	varchar2(255)	varchar2(1024)
registry_environment	url	varchar2(255)	varchar2(2048)
registry_roles_mapping	urn	varchar2(100)	varchar2(255)
kafka_connection_settings	urn	varchar2(255)	varchar2(2048)

How to determine if your environment is affected

Using the database tool of your choice, view the tables listed above and check the column structure. If this does not match that as expected, please follow the instructions in the next section.

Methods to Address

Using the database tool of your choice, access the database and locate the tables which may have issues. Modify the columns so that they are large enough to contain information that may be required. Please note that the table may already contain content so it is important not to lose any data.

You can try executing the following SQL DDL:

 ALTER TABLE registry_settings MODIFY value VARCHAR2(4000);
 ALTER TABLE registry_environment MODIFY name VARCHAR2(1024);
 ALTER TABLE registry_environment MODIFY url VARCHAR2(2048);
 ALTER TABLE registry_roles_mapping MODIFY urn VARCHAR2(255);
 ALTER TABLE kafka_connection_settings MODIFY urn VARCHAR2(2048);