geonetwork / geonetwork/core-geonetwork

Migration Issue in SQL Script / PostGres DB / Version 4.0.1 to 4.0.2

Open
#7,975 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
Java
Stars
521
Forks
514
Avg merge
6d 13h
Merged PRs (30d)
19

Description

**Describe the bug**
During the migration of an existing Geonetwork 3.12.6 Postgres database to version 4.2.2, the following problem was identified:

Error message when executing the migration script geonetwork/WEB-INF/classes/setup/sql/migrate/v402/migrate-default.sql.

```
WARN [geonetwork.database] - SQL failure for: ALTER TABLE guf_userfeedbacks_guf_rating ADD COLUMN GUF_UserFeedback_uuid varchar(255), error is:FEHLER: Spalte »guf_userfeedback_uuid« von Relation »guf_userfeedbacks_guf_rating« existiert bereits
```

> [!WARNING]
Column "guf_userfeedback_uuid" of relation "guf_userfeedbacks_guf_rating" already exists. The database migration stops.

**To Reproduce**
Steps to reproduce the behavior:
1. Increase loglevel to DEBUG for geonetwork.database
2. Start GeoNetwork migration from version 3.12.6 to version 4.2.5
3. Database migration stops during migration to version 4.0.2
4. The error appears in the log file

**Expected behavior**
The database migration does not stop if the column already exists. It continues with the subsequent statements within the migration script.

Possible solution: Add "IF NOT EXISTS" to the migration script for Postgres databases.
```
ALTER TABLE guf_userfeedbacks_guf_rating ADD COLUMN IF NOT EXISTS GUF_UserFeedback_uuid varchar(255);
UPDATE guf_userfeedbacks_guf_rating SET GUF_UserFeedback_uuid = GUF_UserFeedbacks_uuid;
ALTER TABLE guf_userfeedbacks_guf_rating DROP COLUMN IF EXISTS GUF_UserFeedbacks_uuid;
```

**Log file**
```
WARN [geonetwork.database] - SQL failure for: ALTER TABLE guf_userfeedbacks_guf_rating ADD COLUMN GUF_UserFeedback_uuid varchar(255), error is:FEHLER: Spalte »guf_userfeedback_uuid« von Relation »guf_userfeedbacks_guf_rating« existiert bereits
```

**Desktop (please complete the following information):**
- GeoNetwork Version: Migration Version 3.12.6 to 4.2.5. Error occurred in migration from 4.0.1 to version 4.0.2
- Server Application Tomcat

**Additional context**
We were able to fix the error by adapting the migration script by adding "IF NOT EXISTS" to the statement.
This works for PostGres databases.

Contributor guide

Open the contributing guide

Research direction

Open geonetwork/WEB-INF/classes/setup/sql/migrate/v402/migrate-default.sql and inspect the ALTER, UPDATE, and DROP statements for guf_userfeedbacks_guf_rating. Compare the migration behavior when GUF_UserFeedback_uuid already exists, then run the reported 3.12.6-to-4.2.5 migration against PostgreSQL. Done means the migration continues past this step without failing on an existing column.

Written by the indexing model from the issue text.

Assessment

Tech stack
postgresql, sql
Domain
databases
Issue type
Bug
Difficulty
2/5
Estimated time
1-3 hours
Activity status
Stale
Clarity
Clearly specified
Newbie friendliness
52/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.