5

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...

J.Doe
  • 51
  • 2
  • Instead of using update, it may be a good idea to create table of original ids to new ids then join the new ids from that table into your existing queries... Or, update your tables from that created table. – Quentin Jun 19 '17 at 05:42
  • Is it possible that this entry already exists in another record somewhere? – Tim Biegeleisen Jun 19 '17 at 05:44
  • @Q. Holness, quite a complicated way, but thanks. – J.Doe Jun 19 '17 at 05:59
  • @Tim Biegeleisen, no... But it is possible to use the value of another unique column, for example convert it to MD5. However, this method is not entirely preferable. – J.Doe Jun 19 '17 at 05:59
  • Which MariaDB version is it? `Duplicate entry .. by key` doesn't look like a message created by the server, it should be `for` key. Have you quoted it verbatim? Could it be coming from the application? Please paste the output of `SHOW CREATE TABLE`, and also check if `SELECT UUID()` works as expected on your machine (consequent calls produce different values). – elenst Jun 19 '17 at 17:09
  • I think I'm facing the same problem. Are you using MariaDB JDBC driver 2.2.4 or above? I've created some code to replicate the issue, it's detailed here: https://dba.stackexchange.com/a/278540/217576 https://jira.mariadb.org/browse/CONJ-836 @j-doe Have you found a solution for it? – Davi Cavalcanti Oct 26 '20 at 05:41

2 Answers2

2

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

Davi Cavalcanti
  • 354
  • 2
  • 11
0

MariaDB connector use utf8mb4 ( normal ut8 on 4 bytes) since 2.2.4, that's not an issue (and in fact avoid lot of issue...)

Problem reside in MySQL 5.6/5.7. UUID is wrongly evaluate depending on collation. Using utf8mb4 create this problem. Bug is reported there https://bugs.mysql.com/bug.php?id=101820.

Diego Dupin
  • 1,106
  • 8
  • 9