Comportamento do MySQL UUID()

Estou tendo exatamente esse mesmo problema agora.

Eu escrevi um exemplo de aplicativo java para demonstrar o problema:

MariaDBTestUUID.java:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class MariaDBTestUUID
{
    private Connection connect;
    private Statement statement;
    private ResultSet resultSet;

    public void updateUUID() throws Exception
    {
        try
        {
            // This will load the MySQL driver, each DB has its own driver
            Class.forName("org.mariadb.jdbc.Driver");
            // Setup the connection with the DB
            connect = DriverManager
                    // AWS Aurora (MySQL server 5.6.10) - found with SQL "show variables like '%version%';"
                    .getConnection("jdbc:mariadb://<hostname>:3306/<DB name>?user=<user>&password=<password>");
//                     // local MySQL server (5.7.22)
//                    .getConnection("jdbc:mariadb://localhost:3306/licenceserver4?user=root&password=<password>");
//                     // docker container MySQL server (5.7.30)
//                    .getConnection("jdbc:mariadb://localhost:3307/testDB?user=root&password=<password>");

            // PreparedStatements can use variables and are more efficient
            PreparedStatement preparedStatement =
                connect.prepareStatement("UPDATE testUUID SET new_id = uuid(), new_id2 = REPLACE(uuid(), '-', ''), new_id3 = UNHEX(REPLACE(uuid(), '-', ''))");
            preparedStatement.executeUpdate();

            queryStrings("select ID, new_ID, new_ID2, hex(new_ID3) as new_ID3 from testUUID");
            queryStrings("select count(distinct(new_ID)) as distinct_rows from testUUID");
            queryStrings("select count(distinct(new_ID2)) as distinct_rows2 from testUUID");
            queryStrings("select count(distinct(new_ID3)) as distinct_rows3 from testUUID");
        }
        catch (Exception e)
        {
            throw e;
        }
        finally
        {
            close();
        }
    }

    private void queryStrings(String sql) throws SQLException
    {
        System.out.println("Running \"" + sql + "\"... ");
        statement = connect.createStatement();
        // Result set get the result of the SQL query
        resultSet = statement
            .executeQuery(sql);
        writeResultSet(resultSet);
    }

    private void writeResultSet(ResultSet resultSet) throws SQLException
    {
        // ResultSet is initially before the first data set
        while (resultSet.next())
        {
            // we can only do this because we know they are all strings
            for (int i = 1; i <= resultSet.getMetaData().getColumnCount(); i++)
            {
                String value = resultSet.getString(i);
                System.out.print(resultSet.getMetaData().getColumnName(i) + ": " + value + ", ");
            }
            System.out.println();
        }
    }

    // You need to close the resultSet
    private void close()
    {
        try
        {
            if (resultSet != null)
            {
                resultSet.close();
            }

            if (statement != null)
            {
                statement.close();
            }

            if (connect != null)
            {
                connect.close();
            }
        }
        catch (Exception e)
        {
            System.out.println("Error closing:");
            e.printStackTrace();
        }
    }

    public static void main(String[] args) throws Exception
    {
        new MariaDBTestUUID().updateUUID();
    }
}

A versão da biblioteca cliente é definida aqui:

build.gradle:

plugins {
    id 'java'
}

sourceCompatibility = 11

repositories {
    mavenCentral()
}

dependencies {
    // DB driver
    runtimeOnly 'org.mariadb.jdbc:mariadb-java-client:2.2.4'
}

Para executar o aplicativo de teste, basta:

1 - conecte-se a cada um dos BDs e execute as seguintes consultas:

create table testUUID (ID int, new_ID varchar(100), new_ID2 varchar(100), new_ID3 BINARY(16));
insert into testUUID (id) values (1),(2),(3);

2 - substitua as informações de conexão mascaradas por motivos de segurança e execute-as em cada um dos BDs.

3 - defina a versão do cliente mariadb para usar no teste no build.gradle

4 - execute main()

Se a saída mostrar 3 para cada uma das consultas distintas, a instrução de atualização criou valores distintos em cada coluna. Se mostrar 1, a atualização definirá o mesmo valor para todas as linhas dessa coluna. Todas as consultas devem retornar 3.

O que eu vi, para todos os DBs:

• Com o cliente mariadb 2.2.3, ele retorna 3 para todas as consultas.
• Com o cliente mariadb 2.2.4, ele retorna 1 para a segunda e terceira consultas (new_ID2 e new_ID3).

Então, minhas descobertas até agora:

1. Tudo funcionou sem problemas (todas as colunas em cada linha foram definidas com valores distintos) até a versão 2.2.3 da biblioteca cliente mariadb. A partir da versão 2.2.4, ele começou a definir valores idênticos em todas as linhas para colunas que estávamos definindo replace(uuid(),'-', '')usando uma única instrução de atualização. Isso também acontece com várias outras versões do cliente mariadb, incluindo a mais recente (2.7.0).

2. O problema está relacionado à função REPLACE() ou ao aninhamento de chamadas de função na atualização. Definitivamente não é causado pela função UUID().

3. O problema ocorre igualmente no MySQL 5.7.22 (instalado localmente no meu ambiente), MySQL 5.7.30 (contêiner do docker de teste) e, mais importante, Aurora DB (MySQL 5.6.10).

-------------------------------------------------- -- EDITAR ----------------------------------------------- -----

Para mim, isso foi causado por uma conjunção de:

• o conjunto de caracteres do banco de dados
• uma mudança no MariaDB J/Connector a partir da versão 2.2.4
• a combinação de funções de processamento UUID() e String (como REPLACE())

Adicionando CONVERT() à imagem resolveu:

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

Para mais detalhes (e um upvote): https://stackoverflow.com/a/51393124/5154619

Eu tentei isso na versão 9 do MySQL/MariaDB:

CREATE TABLE t (
    i INT,
    myuuid CHAR(36) NULL
    ) ENGINE=InnoDB;

INSERT INTO t (i) VALUES
    (1), (2), (3), (4);

UPDATE t SET myuuid = UUID();

SELECT DISTINCT myuuid FROM t;

Cada versão tem 4 linhas diferentes, portanto, 4 uuids diferentes.

Se você tem um código que produz o mesmo uuid, o que você está fazendo de diferente?

O problema com a implementação e definição de uuid()no mysql é que ele não é gerado de forma verdadeiramente aleatória, de modo que podem ocorrer colisões especialmente ao fazê-lo em massa.

Melhor é usar o uuid v4, que garante aleatoriedade por toda parte veja uma implementação https://stackoverflow.com/a/32965744/5193536 é um pouco mais lento.