SQL - Duplicates while REPLACE(UUID) on UPDATE QUERY

Question

I have a table with column for UUID on binary(16) format and i need to update all old entries, which value is not set. Trying query:

UPDATE sometable SET uuid=UNHEX(REPLACE(UUID(), '-', '')) WHERE uuid IS NULL;

And it returns error like this after updating only one row:

Duplicate entry '\xAD\x15\xEAoT\xAB\x11\xE7\x9B\x0F\xF0yYry\xD5' by key 'uuid'

Any ways to update all rows? I will be grateful for the help...

Answer 1

I was having the exact same problem. Ended up being a problem with:

• the database charset
• a change in MariaDB J/Connector starting at version 2.2.4
• the combination of UUID() and String processing functions (like REPLACE())

If you cannot change the DB charset, adding CONVERT() to the picture solves it:

UPDATE sometable SET uuid=UNHEX(REPLACE(CONVERT(UUID() using utf8mb4), '-', '')) WHERE uuid IS NULL;

-------------------------------------------------------- EDIT ------------------------------------------------------------

I've found another way to fix this, which may be preferred.

Apparently, if we knew about this and the DBs had been set up with the proper charset and collation, we wouldn't be having these problems.

Changing those now seems too risky and maybe a lot of work. So the next best option is to define those in the JDBC connection, by adding these options:

?sessionVariables=character_set_client=utf8mb4,character_set_results=utf8mb4,character_set_connection=utf8mb4,collation_connection=utf8_general_ci

i.e.:

jdbc:mariadb://localhost/dbName?sessionVariables=character_set_client=utf8mb4,character_set_results=utf8mb4,character_set_connection=utf8mb4,collation_connection=utf8_general_ci

Reference: https://jira.mariadb.org/browse/CONJ-417?focusedCommentId=91133&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-91133

https://stackoverflow.com/a/51393124/5154619