Known Issue Oracle Database - Revision history

Plazarou: /* The Issue */

2025-03-19T08:24:41Z

‎The Issue

Plazarou: /* Overview */

2025-03-19T08:23:28Z

‎Overview

Plazarou at 16:01, 12 March 2025

2025-03-12T16:01:49Z

Plazarou: /* The Issue */

2025-03-12T16:01:10Z

‎The Issue

Plazarou: /* Overview */

2025-03-12T15:58:36Z

‎Overview

Plazarou: /* Overview */

2025-03-12T15:58:26Z

‎Overview

Plazarou: /* The Issue */

2025-03-12T15:56:38Z

‎The Issue

Plazarou: /* The Issue */

2025-03-12T15:37:29Z

‎The Issue

Plazarou: /* Methods to Address */

2025-03-12T15:36:40Z

‎Methods to Address

Plazarou: /* The Issue */

2025-03-12T15:36:12Z

‎The Issue

@@ Line 8: / Line 8: @@
 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 original definitions within the codebase defined the columns as the type of "text". Hibernate combined with Oracle caused an issue where these columns were created within Oracle as varchar2(255). This may not always be long enough to store the required data. From version 11.19.5 of FMR onwards, these Hibernate definitions have been changed to longer values.
 The errors you may experience when using Oracle vary, but may be displayed in the FMR user interface like so:

@@ Line 1: / Line 1: @@
 == Overview ==
-The use of an Oracle Database as the persistent database storage for the FMR is supported and there is an article [[Install_Oracle | 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.
+The use of an Oracle Database as the persistent database storage for the FMR is supported and there is an article [[Install_Oracle | 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 because already created tables will not be updated by FMR.
 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.

@@ Line 47: / Line 47: @@
 You can try executing the following SQL DDL:
-   ALTER TABLE registry_settings MODIFY value VARCHAR2(2048);
+   ALTER TABLE registry_settings MODIFY value VARCHAR2(4000);
-   ALTER TABLE registry_environment MODIFY value VARCHAR2(4000);
+   ALTER TABLE registry_environment MODIFY name VARCHAR2(1024);
-   ALTER TABLE registry_environment MODIFY urlVARCHAR2(2048);
+   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);

@@ Line 28: / Line 28: @@
 ! Table Name !! Column Name !! Definition prior to 11.19.5 !! Definition after 11.19.5
 |-
-| registry_settings|| value || varchar2(255) || varchar2(2048)
+| registry_settings|| value || varchar2(255) || varchar2(4000)
 |-
-| registry_environment || value || varchar2(255) || varchar2(4000)
+| registry_environment || name || varchar2(255) || varchar2(1024)
 |-
 | registry_environment || url|| varchar2(255) || varchar2(2048)

@@ Line 2: / Line 2: @@
 The use of an Oracle Database as the persistent database storage for the FMR is supported and there is an article [[Install_Oracle | 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.
+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 ==

@@ Line 3: / Line 3: @@
 == The Issue ==
-Due to certain tables created by the FMR, being created with tables whose definition is not large enough to accommodate values that the FMR may require, particular errors may be observed when using the FMR. These errors vary but may be displayed in the FMR user interface like so:
 ::[[File:FMR-Oracle-ErrorMsg.png|Oracle Error Message|800px]]
@@ Line 31: / Line 36: @@
 | kafka_connection_settings || urn|| varchar2(255) || varchar2(2048)
 |}
 == How to determine if your environment is affected ==

← Older revision		Revision as of 15:37, 12 March 2025
Line 15:		Line 15:


−	Below is detailed the affected database tables and columns. It is also ~~shown what these columns will be~~ in versions prior to version 11.19.5 and what these columns should be defined as:	+	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:

	{\| class="wikitable"		{\| class="wikitable"

@@ Line 40: / Line 40: @@
 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 the following SQL DDL:
+You can try executing the following SQL DDL:
    ALTER TABLE registry_settings MODIFY value VARCHAR2(2048);

@@ Line 7: / Line 7: @@
 ::[[File:FMR-Oracle-ErrorMsg.png|Oracle Error Message|800px]]
 Or in the FMR logs:
    Caused by: java.sql.SQLSyntaxErrorException: ORA-01754: a table may contain only one column of type LONG