Index Collision in UTF8 on MariaDB

Question

I am having a problem moving (converting) a DB from SQLite3 to MariaDB 10.3.29 server running on a Synology NAS.

One table, actor, has 3 columns

• actor_id
• name
• art_urls

Some actors have 2 entries in the SQLite 3 DB, one being the anglicized name, say Sinead Matthews, and another with special characters, Sinéad Matthews. actor_id is the primary key, so that is OK. But the schema creates a unique index based on name. The (to me) black box on MariaDB that creates the index sees both forms as the same and produces a collision that throws an error.

ERROR 1062 (23000): Duplicate entry 'Sinead Matthews' for key 'ix_actor_1'

My MariaDB is using utf8 for the character set. I did an insert of 1 row with the special character. A select query displays the name with the special character. Then trying to insert a row where the name does not have the special character throws the error.

How do I work around this?

Answer 1

I think you're having more of a collation issue as opposed to a character set issue. Do you know what the collation was in SQLite and what it now is in your MariaDB instance?

You can change the collation in MariaDB to something more appropriate like the default of latin1_swedish_ci which I believe would then allow the insert of the second row (treating those two values as different values). Collations can be specified at the server, database, table, and column level.

If you want to change it at the database level, which is typically most appropriate (i.e. you should generally use the same collation throughout the entire database) you can accomplish that with the ALTER DATABASE command. For example:

ALTER DATABASE YourDatabaseName CHARACTER SET='latin1'  COLLATE='latin1_swedish_ci';

For reference, here's the list of character sets MariaDB supports too, with latin1 being the default.

Answer 2

Don't have "unique index based on name"; use a non-unique index. The actor_id is needed to distinguish them. (cf Chris Evans, Adam Scott, Randy Jackson) (I get confused with Rick James.)

Do not have an array of anything (eg URLs) in a cell. Build a separate table.

It may not be trivial to convert the database; don't expect to do it with the push of a button.

Use CHARACTER SET utf8mb4. (I don't know of any names that need 4-byte UTF-8 characters, but it could happen.) Be sure to configure both the connection and the table to handle utf8mb4. Actually, the connection must indicate the encoding of the client. So, if the source is in latin1 (cpxxxx, etc), the so state. During INSERT, the data will be re-encoded into what the column of the table needs.

The COLLATION says whether "Sinéad" = "Sinead". From what you described, I don't understand where the Dup key is coming from. Please show us the two rows that are conflicting.

A MariaDB collation of utf8mb4_bin will treat those two spelling as different; a collation ending with _ci (for Case Insensitive) will treat them as equal.