Metadatastatus table not created with Oracle 19c and GN 4.2.5. Error importing template

[Bear-LB]

Possibly related to #5587 When geonetwork is deployed to use postgresql, it creates a metadatastatus table in the database. However, when Oracle database is used, it's not creating it. I then created the metadatastatus table manually by comparing the database between postgresql and oracle. Now it no longer gives the same error when importing the record, but the metadata or template doesn't pop up in Editor board When i import the metadata i are redirected to /srv/eng/catalog.search#/metadata/100 It shows a nonending loading icon, and if log level is set to TEST. There's thrown an error.

If I import the metadata with postgresql database used instead, I am redirected to /srv/eng/catalog.search#/metadata/14420e71-8a5b-0121-45f5-160448593181 and everything seems to work and record shows up in Editor board. I tried to check if the metadata record exists in Oracle 19c by manually going to the url with the UUID instead /srv/eng/catalog.search#/metadata/14420e71-8a5b-0121-45f5-160448593181 and that doesn't work. It gives the same loading icon error. However! If I go then go to the URL /srv/api/records/14420e71-8a5b-0121-45f5-160448593181/formatters/xml?approved=true it shows the imported metadata record as XML.

To Reproduce Steps to reproduce the behavior:

1. Click 'Contribute'
2. Click 'Import new records'
3. Click 'Choose or drop ressource here' Select metadata.xml file
4. Click 'Import'

Expected behavior Imported metadata record should be accessible and pop up in Editor board

Log file Log file for when metadatastatus wasn't created: ERROR [geonetwork.datamanager] - The metadata document index with id=103 is corrupt/invalid - ignoring it. Error: could not execute batch; SQL [insert into GEONETWORK.MetadataStatus (changeDate, changeMessage, closeDate, dueDate, metadataId, owner, relatedMetadataStatusId, statusId, userId, uuid, id, currentState, previousState, titles) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute batch ... Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet ... Caused by: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

Log file for when importing metadata after metadatastatus was manually created: WARN [geonetwork.settings] - Requested setting with name: metadata/import/restrict but null value found. Check the settings table.

Used Software

• Chrome and Firefox Browser
• GeoNetwork Version 4.2.5 Compiled with ojdbc8 Version19.6.0.0 included in web/pom.xml
• Tomcat 9 with Java 8
• Oracle 19c database using SERVICE connection

Additional context Manually created table in Oracle:

CREATE TABLE MetadataStatus
  (
id NUMBER not null, 
changeDate varchar2(30) not null, 
changeMessage varchar2(2048) not null, 
closeDate varchar2(30), 
currentState varchar2(4000), 
dueDate varchar2(30), 
metadataId number not null, 
owner NUMBER NOT NULL, 
previousState varchar2(4000),
titles varchar2(4000),
userId number not null, 
uuid VARCHAR2(255 CHAR) NOT NULL, 
relatedMetadataStatusId number,
statusId number not null, 
primary key(id),
    foreign key(statusId) references StatusValues(id),
    foreign key(relatedMetadataStatusId) references metadatastatus(id)
  );

[Bear-LB]

When I go into editor board, there's thrown a log, it says this:

DEBUG [org.hibernate.SQL] - select metadata0_.id as id1_28_0_, metadata0_.data as data2_28_0_, metadata0_.changedate as changedate3_28_0_, metadata0_.createdate as createdate4_28_0_, metadata0_.displayorder as displayorder5_28_0_, metadata0_.doctype as doctype6_28_0_, metadata0_.extra as extra7_28_0_, metadata0_.popularity as popularity8_28_0_, metadata0_.rating as rating9_28_0_, metadata0_.root as root10_28_0_, metadata0_.schemaId as schemaid11_28_0_, metadata0_.title as title12_28_0_, metadata0_.isTemplate as istemplate13_28_0_, metadata0_.isHarvested as isharvested14_28_0_, metadata0_.harvestUri as harvesturi15_28_0_, metadata0_.harvestUuid as harvestuuid16_28_0_, metadata0_.groupOwner as groupowner17_28_0_, metadata0_.owner as owner18_28_0_, metadata0_.source as source19_28_0_, metadata0_.uuid as uuid20_28_0_, metadataca1_.metadataId as metadataid1_29_1_, metadataca2_.id as categoryid2_29_1_, metadataca2_.id as id1_1_2_, metadataca2_.name as name2_1_2_ from Metadata metadata0_ left outer join MetadataCateg metadataca1_ on metadata0_.id=metadataca1_.metadataId left outer join Categories metadataca2_ on metadataca1_.categoryId=metadataca2_.id where metadata0_.id=? I made this query against the database and nothing returns

However if the last part of the query is removed 'where metadata0_.id=?` Then the metadata is listed. I assume something is wrong with how ID's are created

[Bear-LB]

When comparing Oracle to PostgreSQL database. I see that the table "Harvesthistory" and "spatial_ref_sys" are missing from the Oracle database. Geonetwork havn't created them.

Another issue with Oracle is that the Users table password column doesn't accept Null and will not create the 'Nobody' user. GeoNetwork won't start unless the password column is manually configured from the database to accept Null

[fxprunayre]

Never used Oracle with GeoNetwork, but maybe check that sequence are also created properly https://github.com/geonetwork/core-geonetwork/blob/main/domain/src/main/java/org/fao/geonet/domain/AbstractMetadata.java#L70 ?

spatial_ref_sys is unused in 4.x so it is not an issue.

Any SQL errors on Oracle logs ?

[Bear-LB]

@fxprunayre The metadata is imported and the IDs start with 100 and is incremented by 1 for each metadata imported. I suppose if the sequences were wrong, the ID's in the metadata ID column would be wrong. By default Oracle puts a cache with value 20 on Sequences. I've reployed geonetwork and the database and changed it to NOCACHE before importing metadata to make sure it only increments by 1. I've imported 4 metadata so far, the value is shown as 104 which i presume is the next number it would allocate if I were to import metadata for the 5th time.

There are no logs in Oracle alert log file.

The select query from the log mentioned above "where metadata0_.id=?" ... What is the questionmark supposed to depict ? If I replace the questionmark with a metadata ID, then the metadata is shown.

The metadata can be seen from the URL /srv/api/records/dbfe117a-2ce2-4b3f-8a16-0c9399e49fdb but not from URL /srv/eng/catalog.search#/metadata/dbfe117a-2ce2-4b3f-8a16-0c9399e49fdb and nothing shows up in Editor Board. Where should I look in the database in regards to that Error ?

Thanks alot for your time

[fxprunayre]

The metadata can be seen from the URL /srv/api/records/dbfe117a-2ce2-4b3f-8a16-0c9399e49fdb but not from URL /srv/eng/catalog.search#/metadata/dbfe117a-2ce2-4b3f-8a16-0c9399e49fdb and nothing shows up in Editor Board.

So if available in /srv/api/records/dbfe117a-2ce2-4b3f-8a16-0c9399e49fdb records is in the database and can be formatted using XSL. If not in /srv/eng/catalog.search#/metadata/dbfe117a-2ce2-4b3f-8a16-0c9399e49fdb something is wrong with the indexing. So http://localhost:9200/gn-records/_doc/dbfe117a-2ce2-4b3f-8a16-0c9399e49fdb is probably empty ?

Try to trigger indexing from admin console > tools > delete index and reindex and follow the log.

[Bear-LB]

Yes, i'm able to Curl http://localhost:9200/gn-records/_doc/dbfe117a-2ce2-4b3f-8a16-0c9399e49fdb.. And the geonetwork dashboard status was 'Records in index/db = 4/4'
However after clicking 'Delete index and reindex' , it's deleting the records but it's not able to reindex the records And now says "Records in index/db = 0/4" And now I cannot get output from curl http://localhost:9200/gn-records/_doc/dbfe117a-2ce2-4b3f-8a16-0c9399e49fdb Here's the logs.

DEBUG [org.springframework.security.access.hierarchicalroles.RoleHierarchyImpl] - getReachableGrantedAuthorities() - From the roles [jeevesNodeApplicationContext_, Monitor, Administrator, UserAdmin, Guest, Editor, Reviewer, RegisteredUser] one can reach [jeevesNodeApplicationContext_, Monitor, Administrator, UserAdmin, Guest, Editor, Reviewer, RegisteredUser] in zero or more steps.
DEBUG [org.springframework.security.web.access.intercept.FilterSecurityInterceptor] - Authorized filter invocation [PUT /srv/api/site/index?reset=true&asynchronous=true] with attributes [permitAll]
DEBUG [org.springframework.security.web.FilterChainProxy] - Secured PUT /srv/api/site/index?reset=true&asynchronous=true
DEBUG [org.springframework.orm.jpa.JpaTransactionManager] - Creating new transaction with name [org.fao.geonet.repository.GeonetRepositoryImpl.findById]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT,readOnly
DEBUG [org.springframework.orm.jpa.JpaTransactionManager] - Opened new EntityManager [SessionImpl(1696011832<open>)] for JPA transaction
DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] - Setting JDBC Connection [705777382, URL=jdbc:oracle:thin:@REDACTED:1521/REDACTED, UserName=GEONETWORK, Oracle JDBC driver] read-only
DEBUG [org.springframework.orm.jpa.JpaTransactionManager] - Exposing JPA transaction as JDBC [org.springframework.orm.jpa.vendor.HibernateJpaDialect$HibernateConnectionHandle@6eedc117]
DEBUG [org.springframework.orm.jpa.JpaTransactionManager] - Initiating transaction commit
DEBUG [org.springframework.orm.jpa.JpaTransactionManager] - Committing JPA transaction on EntityManager [SessionImpl(1696011832<open>)]
DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] - Resetting read-only flag of JDBC Connection [705777382, URL=jdbc:oracle:thin:@REDACTED:1521/REDACTED, UserName=GEONETWORK, Oracle JDBC driver]
DEBUG [org.springframework.orm.jpa.JpaTransactionManager] - Closing JPA EntityManager [SessionImpl(1696011832<open>)] after transaction
DEBUG [org.springframework.orm.jpa.JpaTransactionManager] - Creating new transaction with name [jeevesDispatchServlet]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT
DEBUG [org.springframework.orm.jpa.JpaTransactionManager] - Opened new EntityManager [SessionImpl(722585222<open>)] for JPA transaction
DEBUG [org.springframework.orm.jpa.JpaTransactionManager] - Exposing JPA transaction as JDBC [org.springframework.orm.jpa.vendor.HibernateJpaDialect$HibernateConnectionHandle@bd7cb7c]
DEBUG [org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerMapping] - Mapped to org.fao.geonet.api.site.SiteApi#index(boolean, boolean, boolean, String[], String, HttpServletRequest)
DEBUG [org.springframework.security.access.intercept.aopalliance.MethodSecurityInterceptor] - Authorized ReflectiveMethodInvocation: public org.springframework.http.HttpEntity org.fao.geonet.api.site.SiteApi.index(boolean,boolean,boolean,java.lang.String[],java.lang.String,javax.servlet.http.HttpServletRequest) throws java.lang.Exception; target is of class [org.fao.geonet.api.site.SiteApi] with attributes [[authorize: 'hasAuthority('Editor')', filter: 'null', filterTarget: 'null']]
DEBUG [geonetwork.index] - Index 'gn-records' removed.
DEBUG [geonetwork.index] - Index 'gn-records' created
DEBUG [org.springframework.orm.jpa.JpaTransactionManager] - Found thread-bound EntityManager [SessionImpl(722585222<open>)] for JPA transaction
DEBUG [org.springframework.orm.jpa.JpaTransactionManager] - Participating in existing transaction
DEBUG [org.hibernate.SQL] - select count(metadata0_.id) as col_0_0_ from Metadata metadata0_
DEBUG [org.hibernate.SQL] - select metadata0_.id as col_0_0_, metadata0_.changedate as col_1_0_ from Metadata metadata0_ order by metadata0_.changedate desc fetch first ? rows only
DEBUG [org.hibernate.SQL] - select count(metadatadr0_.id) as col_0_0_ from MetadataDraft metadatadr0_
DEBUG [org.hibernate.SQL] - select metadatadr0_.id as col_0_0_, metadatadr0_.changedate as col_1_0_ from MetadataDraft metadatadr0_ order by metadatadr0_.changedate desc fetch first ? rows only
DEBUG [org.hibernate.SQL] - select count(metadata0_.id) as col_0_0_ from Metadata metadata0_
DEBUG [org.hibernate.SQL] - select metadata0_.id as col_0_0_, metadata0_.changedate as col_1_0_ from Metadata metadata0_ order by metadata0_.changedate desc offset ? rows fetch next ? rows only
DEBUG [org.springframework.orm.jpa.JpaTransactionManager] - Found thread-bound EntityManager [SessionImpl(722585222<open>)] for JPA transaction
DEBUG [org.springframework.orm.jpa.JpaTransactionManager] - Participating in existing transaction
DEBUG [org.springframework.orm.jpa.JpaTransactionManager] - Found thread-bound EntityManager [SessionImpl(722585222<open>)] for JPA transaction
DEBUG [org.springframework.orm.jpa.JpaTransactionManager] - Participating in existing transaction
INFO  [geonetwork] - Using 1 thread(s) to process indexing job
WARN  [geonetwork.index] - Indexing 4 records with 1 threads.
WARN  [geonetwork.index] - Indexing range [0-4]/4 by threads 209.
DEBUG [org.springframework.data.repository.core.support.TransactionalRepositoryProxyPostProcessor$CustomAnnotationTransactionAttributeSource] - Adding transactional method 'existsById' with attribute: PROPAGATION_REQUIRED,ISOLATION_DEFAULT,readOnly
DEBUG [org.springframework.orm.jpa.JpaTransactionManager] - Creating new transaction with name [org.fao.geonet.repository.GeonetRepositoryImpl.existsById]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT,readOnly
DEBUG [org.springframework.orm.jpa.JpaTransactionManager] - Opened new EntityManager [SessionImpl(648651587<open>)] for JPA transaction
DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] - Setting JDBC Connection [156077235, URL=jdbc:oracle:thin:@REDACTED:1521/REDACTED, UserName=GEONETWORK, Oracle JDBC driver] read-only
DEBUG [org.springframework.orm.jpa.JpaTransactionManager] - Exposing JPA transaction as JDBC [org.springframework.orm.jpa.vendor.HibernateJpaDialect$HibernateConnectionHandle@4b8829ad]
DEBUG [org.hibernate.SQL] - select count(*) as col_0_0_ from Metadata metadata0_ where metadata0_.id=?
DEBUG [org.springframework.orm.jpa.JpaTransactionManager] - Initiating transaction commit
DEBUG [org.springframework.orm.jpa.JpaTransactionManager] - Committing JPA transaction on EntityManager [SessionImpl(648651587<open>)]
DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] - Resetting read-only flag of JDBC Connection [156077235, URL=jdbc:oracle:thin:@REDACTED:1521/REDACTED, UserName=GEONETWORK, Oracle JDBC driver]
DEBUG [org.springframework.orm.jpa.JpaTransactionManager] - Closing JPA EntityManager [SessionImpl(648651587<open>)] after transaction
DEBUG [org.springframework.data.repository.core.support.TransactionalRepositoryProxyPostProcessor$CustomAnnotationTransactionAttributeSource] - Adding transactional method 'findById' with attribute: PROPAGATION_REQUIRED,ISOLATION_DEFAULT,readOnly
DEBUG [org.springframework.orm.jpa.JpaTransactionManager] - Creating new transaction with name [org.fao.geonet.repository.GeonetRepositoryImpl.findById]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT,readOnly
DEBUG [org.springframework.orm.jpa.JpaTransactionManager] - Opened new EntityManager [SessionImpl(305403487<open>)] for JPA transaction
DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] - Setting JDBC Connection [1950555334, URL=jdbc:oracle:thin:@REDACTED:1521/REDACTED, UserName=GEONETWORK, Oracle JDBC driver] read-only
DEBUG [org.springframework.orm.jpa.JpaTransactionManager] - Exposing JPA transaction as JDBC [org.springframework.orm.jpa.vendor.HibernateJpaDialect$HibernateConnectionHandle@78a1c42f]
DEBUG [org.hibernate.SQL] - select metadata0_.id as id1_28_0_, metadata0_.data as data2_28_0_, metadata0_.changedate as changedate3_28_0_, metadata0_.createdate as createdate4_28_0_, metadata0_.displayorder as displayorder5_28_0_, metadata0_.doctype as doctype6_28_0_, metadata0_.extra as extra7_28_0_, metadata0_.popularity as popularity8_28_0_, metadata0_.rating as rating9_28_0_, metadata0_.root as root10_28_0_, metadata0_.schemaId as schemaid11_28_0_, metadata0_.title as title12_28_0_, metadata0_.isTemplate as istemplate13_28_0_, metadata0_.isHarvested as isharvested14_28_0_, metadata0_.harvestUri as harvesturi15_28_0_, metadata0_.harvestUuid as harvestuuid16_28_0_, metadata0_.groupOwner as groupowner17_28_0_, metadata0_.owner as owner18_28_0_, metadata0_.source as source19_28_0_, metadata0_.uuid as uuid20_28_0_, metadataca1_.metadataId as metadataid1_29_1_, metadataca2_.id as categoryid2_29_1_, metadataca2_.id as id1_1_2_, metadataca2_.name as name2_1_2_ from Metadata metadata0_ left outer join MetadataCateg metadataca1_ on metadata0_.id=metadataca1_.metadataId left outer join Categories metadataca2_ on metadataca1_.categoryId=metadataca2_.id where metadata0_.id=?
 DEBUG [org.springframework.orm.jpa.JpaTransactionManager] - Initiating transaction rollback
DEBUG [org.springframework.orm.jpa.JpaTransactionManager] - Rolling back JPA transaction on EntityManager [SessionImpl(305403487<open>)]
DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] - Resetting read-only flag of JDBC Connection [1950555334, URL=jdbc:oracle:thin:@REDACTED:1521/REDACTED, UserName=GEONETWORK, Oracle JDBC driver]
DEBUG [org.springframework.orm.jpa.JpaTransactionManager] - Closing JPA EntityManager [SessionImpl(305403487<open>)] after transaction

Then the error logs come

ERROR [geonetwork.datamanager] - The metadata document index with id=100 is corrupt/invalid - ignoring it. Error: Could not read entity state from ResultSet : EntityKey[org.fao.geonet.domain.Metadata#100]; nested exception is org.hibernate.exception.GenericJDBCException: Could not read entity state from ResultSet : EntityKey[org.fao.geonet.domain.Metadata#100]
org.springframework.orm.jpa.JpaSystemException: Could not read entity state from ResultSet : EntityKey[org.fao.geonet.domain.Metadata#100]; nested exception is org.hibernate.exception.GenericJDBCException: Could not read entity state from ResultSet : EntityKey[org.fao.geonet.domain.Metadata#100]
...
Caused by: org.hibernate.exception.GenericJDBCException: Could not read entity state from ResultSet : EntityKey[org.fao.geonet.domain.Metadata#100]
...
Caused by: java.sql.SQLException: Stream has already been closed
...
WARN  [geonetwork.index] - Record #100 (mode: full) indexed in 96ms

It then also generates that error for when it tries to reindex metadata with ID 101 and 102 and 103.

... I tried importing my fifth metadata with UUID 40c6858d-d1f5-4d0d-a0a9-055e54188292 The geonetwork dashboard now says "Records in index/db = 1/5" I did the command: curl http://localhost:9200/gn-records/_doc/40c6858d-d1f5-4d0d-a0a9-055e54188292 That elasticsearch record exists and it spewed out 8960 characters It also contained the words "id":"104" "metadataIdentifier":"40c6858d-d1f5-4d0d-a0a9-055e54188292" "recordGroup":"40c6858d-d1f5-4d0d-a0a9-055e54188292" "uuid":"40c6858d-d1f5-4d0d-a0a9-055e54188292" The ID and the UUID in the index record is the same as the one that shows in the database. The record exists in Elasticsearch when the metadata is imported but not when it tries to reindex.

I have a PostgreSQL test database as well and I can see that geonetwork is reliant on that record existing in gn-records index for it to show up in Editor Board. However even if the record exists in Elasticsearch, it won't pop up in Editor Board when Oracle is used. When I use PostgreSQL or H2 and I delete all Indexes in ElasticSearch and it says 'Records in index/db = 0/1. I can click "Delete index and reindex" It's able to reindex the records, but not when Oracle Database is used. Whatever Database I use, it uses the exact same Elasticsearch instance thats running on the same server, so I doubt it's the fault of Elasticsearch.

[Bear-LB]

Error is reproducable on Oracle 23C Developer Edition as well.

[jodygarnett]

Not many developers have access to oracle environment to be able to work on this, are you able to create a PR?

[Bear-LB]

Not many developers have access to oracle environment to be able to work on this, are you able to create a PR?

Im sadly not able to create a PR cus i'm not much of a developer myself. I would recommend that the geonetwork developers use Oracle Developer Edition with Docker, that's free and should be quick to set up a Oracle Environment https://www.oracle.com/database/free/get-started/