[Bug Report] database migration fails

[seeshepard]

Describe the bug
When trying to update stash from version 0.9 to 0.10 schema migration fails with the error

An error occurred migrating the database to the latest schema version. The backup database file was automatically renamed to restore the database.
error performing migration: UNIQUE constraint failed: images.path in line 0: DROP INDEX IF EXISTS images_path_unique;

CREATE UNIQUE INDEX images_path_unique ON images (path);

To Reproduce
Performing a schema migration, in my case from version 25 to 28.

Screenshots

Stash Version: (from Settings -> About):
Newest released - v0.10.0 f4b7838

Desktop (please complete the following information):

• OS: Win 10
• Browser: Firefox
• Version 93

[kermieisinthehouse]

This is probably a bug in the conditional migration code, it's somehow missing that there are still duplicates in the table before running. @gitgiggety

[seeshepard]

This is probably a bug in the conditional migration code, it's somehow missing that there are still duplicates in the table before running. @gitgiggety

Is there something I can do to fix it?

[WithoutPants]

This is a different bug. The conditional code is for migrating where there are non-unique checksums not paths.

Essentially, this is a database corruption. I suspect the bug that introduced this corruption will be fixed once that migration actually completes, but we need to fix the corruption first.

@seeshepard you're going to need to fix this manually with sqlite.

To see the duplicate images, you'll need to run this SQL:

SELECT path FROM images 
WHERE path IN ( 
    SELECT path FROM images 
    GROUP BY path
    HAVING COUNT(1) > 1
);

Once you have these, you can use the previous version of stash to delete the extra entries you don't want. Alternatively, you can just delete all duplicates (you won't get to choose what image entry to keep) by running this query:

DELETE FROM images 
WHERE path IN ( 
    SELECT path FROM images 
    GROUP BY path
    HAVING COUNT(1) > 1
)
AND id NOT IN (
    SELECT min(id) FROM images 
    GROUP BY path
    HAVING COUNT(1) > 1
);

Make a backup of your stash-go.sqlite file before doing any of this. If you need assistance getting and using sqlite, please let me know.

[seeshepard]

Thank you, it worked like a charm! For anyone in the future that runs into a similar problem here's a quick step by step:

1. Always make a backup first!
2. Go to https://www.sqlite.org/download.html and download sqlite-tools-win32
3. Move the sqlite3.exe file into the folder where your stash-go.sqlite db is located and run it
4. Type .open stash-go.sqlite and you are now in your stash database (you can confirm this by using .database command and getting the path to the database file as result)
5. Now you can just use the commands listed by WithoutPants.