DB Migration V2_6 fails because of wrong IDs

[ponchofiesta]

I started to work on our migration from v2 to v3. At first I try to migrate the DB using Flyway. On step V2_6 the IDs of all the property tables are changed to prevent duplicate entries.

I figured out that LAST_INSERT_ID() gets wrong IDs.

The migration step fails with message Duplicate entry '2' for key 'PRIMARY' and ends with this state:

• workpieceProperty starting with ID 1 containing 19228 records (OK)
• userProperty is empty (OK)
• templateProperty starting with ID 2 containing 19865 records (WRONG)
• processProperty starting with ID 19868 (OK)

I'm not sure if this can be fixed in our DB before all migration steps or if this is a bug in this migration step.

Flyway error:

[ERROR] Failed to execute goal org.flywaydb:flyway-maven-plugin:5.1.4:migrate (default-cli) on project kitodo-production: org.flywaydb.core.internal.command.DbMigrate$FlywayMigrateException: 
[ERROR] Migration V2_6__Store_properties_only_in_one_table.sql failed
[ERROR] -------------------------------------------------------------
[ERROR] SQL State  : 23000
[ERROR] Error Code : 1062
[ERROR] Message    : Duplicate entry '2' for key 'PRIMARY'
[ERROR] Location   : ./Kitodo-DataManagement/src/main/resources/db/migration/V2_6__Store_properties_only_in_one_table.sql (/home/mri/projects/kitodo-production-tub/./Kitodo-DataManagement/src/main/resources/db/migration/V2_6__Store_properties_only_in_one_table.sql)
[ERROR] Line       : 149
[ERROR] Statement  : INSERT INTO property (id, title, value, obligatory, dataType, choice, creationDate, container)
[ERROR]        SELECT id, title, value, obligatory, dataType, choice, creationDate, container
[ERROR]        FROM workpieceProperty

[ponchofiesta]

I don't know what the JOINs are for but without them and LAST_INSERT_ID() this migrations step works:

set @rank := 0;

-- for workpieceproperty
ALTER TABLE workpieceProperty
    CHANGE id id INT(11) NOT NULL;
ALTER TABLE workpieceProperty
    DROP PRIMARY KEY;

UPDATE workpieceProperty
    SET id=@rank:=@rank+1;
ALTER TABLE workpieceProperty
    CHANGE id id INT(11) NOT NULL AUTO_INCREMENT,
    ADD PRIMARY KEY(id);

-- for userproperty
ALTER TABLE userProperty
    CHANGE id id INT(11) NOT NULL;
ALTER TABLE userProperty
    DROP PRIMARY KEY;

UPDATE userProperty
    SET id=@rank:=@rank+1;
ALTER TABLE userProperty
    CHANGE id id INT(11) NOT NULL AUTO_INCREMENT,
    ADD PRIMARY KEY(id);

-- for templateproperty
ALTER TABLE templateProperty
    CHANGE id id INT(11) NOT NULL;
ALTER TABLE templateProperty
    DROP PRIMARY KEY;

UPDATE templateProperty
    SET id=@rank:=@rank+1;
ALTER TABLE templateProperty
    CHANGE id id INT(11) NOT NULL AUTO_INCREMENT,
    ADD PRIMARY KEY(id);

-- for processproperty
ALTER TABLE processProperty
    CHANGE id id INT(11) NOT NULL;
ALTER TABLE processProperty
    DROP PRIMARY KEY;

UPDATE processProperty
    SET id=@rank:=@rank+1;
ALTER TABLE processProperty
    CHANGE id id INT(11) NOT NULL AUTO_INCREMENT,
    ADD PRIMARY KEY(id);

[henning-gerhardt]

Migration was written with at least one stored user property. So you must store at least for one user a search in 2.x or add a dummy value into the table benutzereigenschaften. After this you can start the migration with flyway. This is mentioned into the migration guide which is not yet available :-(

[ponchofiesta]

OK OK but what about using the script above instead?

[matthias-ronge]

314/5000 This is a known bug: The migration will not work unless at least one user has defined at least one filter in version 2 (then userProperty is no longer empty).

If the fix works, I would suggest applying it. Can someone test that during the next migration?

[matthias-ronge]

The pull request has been merged. I close this issue.