search

openequella / openEQUELLA

Core openEQUELLA sources
https://openequella.github.io/
Apache License 2.0
42 stars 44 forks source link

Oracle versions prior to 12c Release 2 cannot upgrade to/install openEQUELLA past 2020.2 #3021

Closed SammyIsConfused closed 2 years ago

SammyIsConfused commented 3 years ago

Describe the bug When upgrading to or installing openEQUELLA 2020.2 or above, with Oracle Database 12c Release 1 (12.1.0.2) or previous, migration will fail with a "Identifier too long" error.

This is because prior to Oracle Database 12c Release 2 there was a character limit for identifiers of 30 characters, and an index brought in in 2020.2 migration "CreateFacetedSearchClassificationTable" was longer than this. The index in question was an institution_id index on FACETED_SEARCH_CLASSIFICATION table - facetSearchClassificationInstitutionIndex which is longer than 30 characters.

To Reproduce Steps to reproduce the behavior:

  1. Install or upgrade openEQUELLA to 2020.2 or above, using an Oracle Database 12c Release 1 or earlier.
  2. Open the server administration page and note that the server requires data migration. Run the migration.
  3. Note it fails with this error message:

image

Expected behavior The index should not have such a long name, so that the migration will pass and openEQUELLA will be able to be used. Alternately, the minimum Oracle version for openEQUELLA should be increased to 12.2.0.1.

Stacktrace

Failed when doing migration database, got errors
Error running SQL: 'create index facetSearchClassificationInstitutionIndex on EQUELLA.faceted_search_classification (institution_id)'
Stacktrace:
ORA-00927 (Identifier too long) when doing database migration.
javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute statement
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:154)
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181)
at org.hibernate.query.internal.AbstractProducedQuery.executeUpdate(AbstractProducedQuery.java:1621)
at com.tle.core.migration.AbstractHibernateMigration.executeSqlStatements(AbstractHibernateMigration.java:69)
at com.tle.core.migration.AbstractHibernateMigration$1.run(AbstractHibernateMigration.java:60)
at com.tle.core.migration.AbstractHibernateMigration.runInTransaction(AbstractHibernateMigration.java:86)
at com.tle.core.migration.AbstractHibernateMigration.runSqlStatements(AbstractHibernateMigration.java:55)
at com.tle.core.migration.AbstractCreateMigration.migrate(AbstractCreateMigration.java:43)

Platform:

Additional context: I have created a manual data fix for this. This involves:

I have collated this into a .sql file, find attached as a zip. oracle121_fix_openEQUELLA.zip

Steps to apply:

  1. Extract the zip.
  2. Stop your openEQUELLA, if it is running.
  3. Log into your database via SQL*Plus and run the .sql file. From within an SQL*Plus console: 
    @path/to/sql/file/oracle121_fix_openEQUELLA.sql
  4. Ensure changes are committed to the database.
  5. Restart the openEQUELLA. You should be able to continue as normal.
snehanaik22 commented 3 years ago

Tested.

snehanaik22 commented 3 years ago

Closed by mistake.