Bug 23487

Summary: Utf8mb4 collation causes problems with missing tables when upgrading the database
Product: Koha Reporter: Andreas Hedström Mace <andreas.hedstrom.mace>
Component: Architecture, internals, and plumbingAssignee: Bugs List <koha-bugs>
Status: CLOSED WONTFIX QA Contact: Testopia <testopia>
Severity: normal    
Priority: P5 - low CC: afrane55, dcook, mengu, pasi.kallinen
Version: unspecified   
Hardware: All   
OS: All   
GIT URL: Change sponsored?: ---
Patch complexity: --- Documentation contact:
Documentation submission: Text to go in the release notes:
Version(s) released in:
Circulation function:

Description Andreas Hedström Mace 2019-08-21 21:06:02 UTC
Changing the default encoding to utf8mb4 and collation to utf8mb4_unicode_ci, can create duplicate unique keys as it ignores differences in accented characters vs non-accented characters. An example would be the Swedish letter ö, which is treated like the letter o. More is discussed here: https://stackoverflow.com/questions/47119794/mysql-mariadb-unable-to-handle-unique-keys-with-when-using-utf8mb4

When upgrading a large database to any version with the new encoding and collation, there is a chance of duplicate unique keys crashing the collation change of the table (or tables) and as a result corrupting InnoDB tablespace. The final result is one or more tables that both exist and don't exist, and thus can't be deleted or created again. We, not so fondly, called them Schrödinger's tables.

For Stockholm University Library, we had about 30 duplicate unique keys in the borrowers table (out of ~220K patrons), mostly in userid. Which crashed the database every time we tried to upgrade until we found the cause. The workaround solution was to change/remove these duplicates, but perhaps a better solution for the community would be to change the default encoding/collation for columns with unique keys? Perhaps utf8mb4_bin?
Comment 1 Katrin Fischer 2023-12-08 21:21:01 UTC
This has been reported 4 years ago and I believe it might have been caused by a changed collation on the borrowers table.
Should this be marked WONTFIX at this point?
Comment 2 David Cook 2024-01-30 01:01:14 UTC
(In reply to Katrin Fischer from comment #1)
> This has been reported 4 years ago and I believe it might have been caused
> by a changed collation on the borrowers table.
> Should this be marked WONTFIX at this point?

I think this could be marked as WONTFIX.

It sounds like an unfortunate migration issue, but I think "dave" and "davé" probably should be considered as equivalent for a unique index. 

When I try to make two patron records with those as the userid and cardnumber, I get the following warning:

The following fields are wrong. Please fix them.

Username already exists or could not create unique new one.
Cardnumber already in use.

So I think that sounds right to me.