geonetwork / geonetwork/core-geonetwork
Migration Issue in SQL Script / PostGres DB / Version 4.0.1 to 4.0.2
- 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
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