Início / dba / Perguntas / 157305
Accepted
Collin Dauphinee
Asked: 2016-12-06 22:55:55 +0800 CST

Como posso definir o valor auto_increment de uma coluna em uma tabela InnoDB existente, sem incorrer em tempo de inatividade?

  • 772

Eu tenho uma tabela InnoDB cuja coluna de chave primária é definida da seguinte forma:

id INT NOT NULL AUTO_INCREMENT

O valor auto_increment desta coluna atingiu INT_MAX, portanto, todas as inserções estão falhando com um erro de chave duplicada. No entanto, esta tabela tem valores de ID não utilizados de 0 a 1,8 milhão, portanto, gostaria de redefinir o valor auto_increment para 0 para ganhar tempo para que uma correção de longo prazo seja implementada.

Não consigo encontrar nenhuma maneira de fazer isso sem incorrer em um tempo de inatividade significativo. A funcionalidade principal dos meus servidores de aplicativos é interrompida se eles não puderem ler esta tabela (mas não conseguir escrever, como agora, tudo bem).

Eu considerei as duas soluções a seguir, sem sucesso:

Configurando o valor auto_increment com ALTER TABLE. Isso não é possível porque reconstrói a tabela, o que leva muito tempo.

Criando uma nova tabela com esquema idêntico e usando RENAME TABLEpara trocar na nova tabela com a coluna começando em 0 e, em seguida, preenchendo-a com dados da tabela antiga. Isso também não é possível, porque o processo de preenchimento com clobber o valor auto_increment (e acredito que bloquearia as inserções do aplicativo durante o preenchimento).

Existe alguma outra abordagem que eu possa adotar aqui ou estou completamente sem sorte?

mysql

1 respostas

  1. Best Answer

    Morda a bala e aproveite o tempo de inatividade.

    Definir o valor de incremento automático para a tabela é inútil, pois ela será redefinida de MAX(id)+1qualquer maneira.

    ALTER TABLE-- Acho que não INPLACEfuncionará neste caso, então será uma cópia completa da tabela. (Especialmente se idfor o PRIMARY KEY.)

    pt-online-schema-change-- cópia completa. Seu benefício é que a mesa continua utilizável. Mas sua tabela não pode ser usada por outro motivo, ou seja, a incapacidade de adicionar outro id.

    O que alterar? Se você tiver INT, terá um limite de 2 bilhões; mudar para INT UNSIGNEDlhe daria um limite de 4 bilhões. O tamanho da tabela não mudará (além do embaralhamento de BTrees). BIGINTé de 8 bytes e dá a você um limite inalcançável. Cuidado: Todas as tabelas que fazem referência a esta precisam alterar o tipo de dados ao mesmo tempo. Isso envolve mais ALTER TABLEs.

    Reutilizar 0..1.8M? Você pode definir ids explicitamente como faz INSERTs, mas isso provavelmente requer alterações de código (e risco).

    Esta pode ser a solução mais rápida: mova as linhas superiores de 1,8 milhão para baixo fazendo algo como

    UPDATE tbl
    SET id = id - (some constant: about 2B -1.8M)
    WHERE id > (the same constant);

    e reinicie o servidor para que ele possa descobrir o novo arquivo MAX(id). Cuidado: Esta técnica não funcionará para a versão 8.0. CUIDADO: Caso outras tabelas utilizem este id (como FK ou for JOINing, você também deve fazer um similar UPDATEna tabela delas para ajustar o foo_idnela.

    Cuidado: Não defina nenhum id para 0 ou menos que 0. (Acho que UPDATEestá correto a esse respeito.) Você terá espaço para apenas 1,8 milhões de linhas antes de ter problemas novamente.

    Lacunas? Você tem muitas lacunas em ids? Eles são causados ​​por INSERT IGNORE, REPLACEe vários outros tipos de INSERT, além de (é claro) DELETE. Muitos dos métodos de 'inserção' que 'gravam' ids podem ser recodificados para eliminar a gravação e geralmente não são mais lentos.

    Seguindo em frente... Nem sempre é 'certo' ter um AUTO_INCREMENT PRIMARY KEYem todas as mesas. Eu estimo que 2/3 das tabelas estão melhores com um 'PK natural' - ou seja, alguma coluna (ou combinação de colunas) que é UNIQUEe não muito volumosa.

    • 1

relate perguntas