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: 0 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?
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?
(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.