Database update fail 6.2.x to 6.4.x

[xlyric]

Hello,

It seems there was an error pushing the Postgres scripts for database updates between versions 6.2 and 6.3.
the script doen't exist

As a result, it is currently impossible to update the database to version 6.4.x during an update.

psql:Migration_6.3.0_to_6.4.0.sql:79: NOTICE: You are about to upgrade from LinShare : 6.3.0 to 6.4.0 psql:Migration_6.3.0_to_6.4.0.sql:79: NOTICE: Your actual version is: 6.2.0 psql:Migration_6.3.0_to_6.4.0.sql:79: NOTICE: Your database history is : psql:Migration_6.3.0_to_6.4.0.sql:79: INFO: 6.2.0 psql:Migration_6.3.0_to_6.4.0.sql:79: NOTICE: Your database system information is : PostgreSQL 16.9 (Debian 16.9) on x86_64-pc-linux, compiled by gcc (Debian 12) psql:Migration_6.3.0_to_6.4.0.sql:79: WARNING: You must be in version : 6.3.0 to run this script. You are actually in version: 6.2.0 psql:Migration_6.3.0_to_6.4.0.sql:79: WARNING: We are about to abort the migration script, all the following instructions will be aborted and transaction will rollback. psql:Migration_6.3.0_to_6.4.0.sql:79: INFO: You should expect the following error : "query has no destination for result data". psql:Migration_6.3.0_to_6.4.0.sql:79: ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead. CONTEXT: PL/pgSQL function ls_prechecks() line 29 at SQL statement psql:Migration_6.3.0_to_6.4.0.sql:80: ERROR: current transaction is aborted, commands ignored until end of transaction block psql:Migration_6.3.0_to_6.4.0.sql:82: ERROR: current transaction is aborted, commands ignored until end of transaction block psql:Migration_6.3.0_to_6.4.0.sql:95: ERROR: syntax error at or near "," LINE 3: ...OT NULL CONSTRAINT nonnull_account_contact_lists_account_id, ^ psql:Migration_6.3.0_to_6.4.0.sql:98: ERROR: current transaction is aborted, commands ignored until end of transaction block psql:Migration_6.3.0_to_6.4.0.sql:102: ERROR: current transaction is aborted, commands ignored until end of transaction block

Cordialy

[Cedric-Thomas]

Hello @xlyric there are 2 issues, this first one as described in "https://github.com/linagora/linshare/blob/master/documentation/EN/upgrade/linshare-upgrade-from-v6.3-to-v6.4.md" you should force a version upgrade on your database:

INSERT INTO version (id, version, creation_date) VALUES ((SELECT nextVal('hibernate_sequence')), '6.3.0', now());

the second one the script provided here : https://github.com/linagora/linshare-core/blob/6.4.1/src/main/resources/sql/postgresql/Migration_6.3.0_to_6.4.0.sql have a syntax error on :

CREATE TABLE account_contact_lists
(
    account_id      int8 NOT NULL CONSTRAINT nonnull_account_contact_lists_account_id,
    contact_list_id int8 NOT NULL CONSTRAINT nonnull_account_contact_lists_contact_list_id,
    can_view_contact_list_members bool ,
    CONSTRAINT pk_account_contact_lists PRIMARY KEY (account_id, contact_list_id),
    CONSTRAINT fk_account_contact_lists_account_id FOREIGN KEY (account_id) REFERENCES account (id),
    CONSTRAINT fk_account_contact_lists_contact_list_id FOREIGN KEY (contact_list_id) REFERENCES contact_list (id)
);

so you should use this instead :

CREATE TABLE account_contact_lists (
    account_id int8,
    contact_list_id int8,
    can_view_contact_list_members bool,
    CONSTRAINT nonnull_account_contact_lists_account_id CHECK (account_id IS NOT NULL),
    CONSTRAINT nonnull_account_contact_lists_contact_list_id CHECK (contact_list_id IS NOT NULL),
    CONSTRAINT pk_account_contact_lists PRIMARY KEY (account_id, contact_list_id),
    CONSTRAINT fk_account_contact_lists_account_id FOREIGN KEY (account_id) REFERENCES account (id),
    CONSTRAINT fk_account_contact_lists_contact_list_id FOREIGN KEY (contact_list_id) REFERENCES contact_list (id)
);

Also, please make a backup before playing with the Database in my case :

docker compose exec postgres psql -U linshare -d linshare -c "VACUUM;"
docker compose exec postgres pg_dump -U linshare -d linshare -W -f /linshare.backup.sql
docker compose cp postgres:/linshare.backup.sql .

then apply the fixed migration file :

docker compose exec postgres psql -U linshare -d linshare -c "INSERT INTO version (id, version, creation_date) VALUES ((SELECT nextVal('hibernate_sequence')), '6.3.0', now());"
docker compose cp ./Migration_6.3.0_to_6.4.0.sql postgres:/
docker compose exec postgres psql -U linshare -d linshare -f /Migration_6.3.0_to_6.4.0.sql

Sincerely,
Cédric

[xlyric]

Hi,

This is also what I noticed as a correction to make to the SQL query.
I also changed the parameter:
DECLARE version_from VARCHAR := '6.3.0';
--> DECLARE version_from VARCHAR := '6.2.0';

Cordialy
Cyril