search

geonetwork / core-geonetwork

GeoNetwork is a catalog application to manage spatially referenced resources. It provides powerful metadata editing and search functions as well as an interactive web map viewer. It is currently used in numerous Spatial Data Infrastructure initiatives across the world.
http://geonetwork-opensource.org/
GNU General Public License v2.0
403 stars 481 forks source link

Upgrading GN on Oracle database produces lots of errors #7930

Open igea-jure opened 3 months ago

igea-jure commented 3 months ago

Describe the bug When using Oracle DB, there are a number of SQL statements in GN upgrade scripts that won't run against the database.

To Reproduce Steps to reproduce the behavior:

  1. Have an existing older GN instance running on Oracle DB.
  2. Start a newer version of GN, which will trigger an upgrade process.
  3. See errors in log.

Expected behavior Upgrade DB objects without errors.

Additional context Trying to upgrade GN on Oracle DB from version 3.8.2 to 4.4.3, had to fix a lot of minor issues related to Oracle DB.

One of the first errors when upgrading (or installing, for that matter) GN on Oracle is ORA-01000: maximum open cursors exceeded. This is caused by an unreasonly high (for me at least) setting jdbc.basic.maxOpenPreparedStatements=1200 in file web/src/main/webResources/WEB-INF/config-db/jdbc.properties. On a default installation of Oracle DB, each session is limited to 300 open cursors.

Another small issue is Oracle JDBC connection string. In GN, this connection string still uses database SID. Nowadays most connections to the database are made with service name. Connection string differs slightly:

I will make a pull request with all changes that I had to make in order to upgrade the database.

Bear-LB commented 2 months ago

With version 4.2.5 I attempted a fresh install with Oracle and i had to change SID to SERVICE and increase open cursors. I then got a working homepage. But Geonetwork didn't create the Metadatastatus table, i manually had to create that. The metadata was inserted into the database, but elasticsearch couldn't rebuild or index it. My elasticsearch setup was not faulty because once i changed jdbc.properties to look to my Postgres server instead, everything worked. Do/did you have the same issue ?

7298

igea-jure commented 2 months ago

With version 4.2.5 I attempted a fresh install with Oracle and i had to change SID to SERVICE and increase open cursors. I then got a working homepage. But Geonetwork didn't create the Metadatastatus table, i manually had to create that. The metadata was inserted into the database, but elasticsearch couldn't rebuild or index it. My elasticsearch setup was not faulty because once i changed jdbc.properties to look to my Postgres server instead, everything worked. Do/did you have the same issue ? #7298

While trying to upgrade, I had the same problem regarding SID/SERVICE NAME, cursors and Users table not being able to handle empty password for user nobody. TBH I was only testing database side of things, nothing elasticsearch related.

Today we have made progress with a fresh install of GN on Oracle, I will post findings and solutions to #7298.