[Bug Report] Database Migration Failure

[lynsix]

Whenever I attempt to upgrade from 0.26.2 to 0.27+ database migration fails.
I'm able to roll back to 0.26.2 and Stash continues to function normally.
Have cleaned, and optimized database and attempted to rerun.

To Reproduce
Update docker container to 0.27, 0.27.1, or 0.27.2

Expected behavior
Would expect the migration to finish without errors as it always has in the past.

Screenshots
Not really a screenshot but just the error that is dumped while migrating the database.
What seems like the relevant part of the error to me: "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: error running migration for schema 62: UNIQUE constraint failed: performers.name in line 0: PRAGMA foreign_keys=OFF;"

Full Error Text

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: error running migration for schema 62: UNIQUE constraint failed: performers.name in line 0: PRAGMA foreign_keys=OFF;

CREATE TABLE performer_urls (
performer_id integer NOT NULL,
position integer NOT NULL,
url varchar(255) NOT NULL,
foreign key(performer_id) references performers(id) on delete CASCADE,
PRIMARY KEY(performer_id, position, url)
);

CREATE INDEX performers_urls_url on performer_urls (url);

-- drop url, twitter and instagram
-- make name not null
CREATE TABLE performers_new (
id integer not null primary key autoincrement,
name varchar(255) not null,
disambiguation varchar(255),
gender varchar(20),
birthdate date,
ethnicity varchar(255),
country varchar(255),
eye_color varchar(255),
height int,
measurements varchar(255),
fake_tits varchar(255),
career_length varchar(255),
tattoos varchar(255),
piercings varchar(255),
favorite boolean not null default '0',
created_at datetime not null,
updated_at datetime not null,
details text,
death_date date,
hair_color varchar(255),
weight integer,
rating tinyint,
ignore_auto_tag boolean not null default '0',
image_blob varchar(255) REFERENCES blobs(checksum),
penis_length float,
circumcised varchar[10]
);

INSERT INTO performers_new
(
id,
name,
disambiguation,
gender,
birthdate,
ethnicity,
country,
eye_color,
height,
measurements,
fake_tits,
career_length,
tattoos,
piercings,
favorite,
created_at,
updated_at,
details,
death_date,
hair_color,
weight,
rating,
ignore_auto_tag,
image_blob,
penis_length,
circumcised
)
SELECT
id,
name,
disambiguation,
gender,
birthdate,
ethnicity,
country,
eye_color,
height,
measurements,
fake_tits,
career_length,
tattoos,
piercings,
favorite,
created_at,
updated_at,
details,
death_date,
hair_color,
weight,
rating,
ignore_auto_tag,
image_blob,
penis_length,
circumcised
FROM performers;

INSERT INTO performer_urls
(
performer_id,
position,
url
)
SELECT
id,
'0',
url
FROM performers
WHERE performers.url IS NOT NULL AND performers.url != '';

INSERT INTO performer_urls
(
performer_id,
position,
url
)
SELECT
id,
(SELECT count(*) FROM performer_urls WHERE performer_id = performers.id)+1,
CASE
WHEN twitter LIKE 'http%://%' THEN twitter
ELSE 'https://www.twitter.com/' || twitter
END
FROM performers
WHERE performers.twitter IS NOT NULL AND performers.twitter != '';

INSERT INTO performer_urls
(
performer_id,
position,
url
)
SELECT
id,
(SELECT count(*) FROM performer_urls WHERE performer_id = performers.id)+1,
CASE
WHEN instagram LIKE 'http%://%' THEN instagram
ELSE 'https://www.instagram.com/' || instagram
END
FROM performers
WHERE performers.instagram IS NOT NULL AND performers.instagram != '';

DROP INDEX IF EXISTS performers_name_disambiguation_unique;
DROP INDEX IF EXISTS performers_name_unique;
DROP TABLE IF EXISTS performers;
ALTER TABLE performers_new rename to performers;

CREATE UNIQUE INDEX performers_name_disambiguation_unique on performers (name, disambiguation) WHERE disambiguation IS NOT NULL;
CREATE UNIQUE INDEX performers_name_unique on performers (name) WHERE disambiguation IS NULL;

PRAGMA foreign_keys=ON;

Stash Version: (from Settings -> About): 26.2 database schema version 58. Error appears to happen when migrating to schema 62.

Additional context
Created bug #5361 which was marked arshad-k7 added #5363 to fix the issue. 0.27.2 was released and bug was closed but it doesn't seem to resolve the issue.

[WithoutPants]

This appears to be a corruption in your database caused by the post-migration for schema version 42 not apparently running. As a result, it's allowed duplicate performer names to be created in your system. These performers will need to be renamed manually before you can migrate your system to the latest version (which will add the missing unique constraints as well).

You should be able to get the duplicate performers with the following graphql query, which can be run using <server>:<port>/playground:

mutation {
  querySQL(sql: """
SELECT id, name, COUNT(*)
FROM
    performers
WHERE disambiguation IS NULL
GROUP BY
    name
HAVING 
    COUNT(*) > 1
""") {
    columns
    rows
  }
}

[lynsix]

Cheers for this. I was starting to assume I must've had a duplicate performer without disambig. I couldn't find anything to filter in the WebUI beyond manually going through all 4600 performers and noticing a duplicate.

This fixed my issue. Thanks so much.