MySQL Campos de incremento automático são redefinidos por si só

Question

Temos uma tabela MySQL que possui um campo de incremento automático definido como INT (11). Essa tabela praticamente armazena uma lista de tarefas em execução em um aplicativo. A qualquer momento durante a vida útil do aplicativo, a tabela pode conter milhares de entradas ou estar completamente vazia (ou seja, tudo foi concluído).

O campo não é de chave estrangeira para qualquer outra coisa.

O incremento automático parece se redefinir aleatoriamente para zero, embora na verdade nunca tenhamos sido capazes de interceptar a redefinição.

O problema se torna evidente porque vemos o campo de auto incremento chegar a, digamos, 600.000 registros ou mais, e depois, um tempo depois, o campo de auto incremento parece estar rodando em baixa de 1000.

É quase como se o autoincremento fosse redefinido se a tabela estivesse vazia.

Isso é possível e, se for, como posso desativá-lo ou alterar a maneira como ele é redefinido?

Se não, alguém tem uma explicação do porquê de estar fazendo isso?

Obrigado!

    

Answer 1

The auto-increment counter is stored only in main memory, not on disk.

link

Por causa disso, quando o serviço (ou servidor) reiniciar, ocorrerá o seguinte:

After a server startup, for the first insert into a table t, InnoDB executes the equivalent of this statement: SELECT MAX(ai_col) FROM t FOR UPDATE;

InnoDB increments by one the value retrieved by the statement and assigns it to the column and to the auto-increment counter for the table. If the table is empty, InnoDB uses the value 1.

Então, em inglês simples, depois que o serviço MySQL é iniciado, ele não tem idéia de qual deve ser o valor do incremento automático para sua tabela. Portanto, quando você insere uma linha pela primeira vez, ele encontra o valor máximo do campo que usa o incremento automático, adiciona 1 a esse valor e usa o valor resultante. Se não houver linhas, começará em 1.

Este foi um problema para nós, pois estávamos usando a tabela e o recurso de incremento automático do mysql para gerenciar IDs em um ambiente multi-thread onde os usuários estavam sendo redirecionados para um site de pagamento de terceiros. Então tivemos que nos certificar de que o ID que o terceiro recebeu e nos enviou de volta era único e assim permaneceria (e, claro, há a possibilidade de o usuário cancelar a transação depois de ter sido redirecionado).

Então, estávamos criando uma linha, obtendo o valor de incremento automático gerado, excluindo a linha para manter a tabela limpa e encaminhando o valor para o site de pagamento. O que acabamos fazendo para corrigir a questão da maneira como o InnoDB lida com os valores da AI era o seguinte:

$query = "INSERT INTO transactions_counter () VALUES ();";
mysql_query($query);
$transactionId = mysql_insert_id();
$previousId = $transactionId - 1;
$query = "DELETE FROM transactions_counter WHERE transactionId='$previousId';";
mysql_query($query); 

Isso sempre mantém o mais recente transactionId gerado como uma linha na tabela, sem desnecessariamente explodir a tabela.

Espero que ajude alguém que possa se deparar com isso.

Editar (2018-04-18) :

Como Finesse mencionou abaixo, parece que o comportamento disso foi modificado no MySQL 8.0+.

link

O texto desse log de trabalho é defeituoso na melhor das hipóteses, no entanto, parece que o InnoDB nessas versões mais recentes agora suporta valores de autoinc persistentes durante as reinicializações.

-Gremio

    

Answer 2

Apenas um tiro no escuro - se o aplicativo estiver usando um TRUNCATE TABLE para esvaziar a tabela quando terminar o processamento, isso redefinirá o campo de incremento automático. Aqui está uma breve discussão sobre a questão. Embora esse link mencione que o InnoDB não redefine auto_incrementos em um trunc, isso foi reportado como um bug bug e fixado há alguns anos atrás.

Supondo que meu palpite esteja correto, você pode mudar de truncamento para exclusão para corrigir o problema.

    

Answer 3

Tivemos esse problema e descobrimos que, quando a tabela de otimização era executada em uma tabela vazia, o valor de incremento automático também era redefinido. Veja este relatório de bug do MySQL .

Como solução alternativa, você pode fazer:

ALTER TABLE a AUTO_INCREMENT=3 ENGINE=innoDB;

Em vez de OPTIMIZE TABLE .

Parece que é isso que o MySQL faz internamente (sem definir o valor do incremento Auto, obviamente)

    

Answer 4

Apenas uma reinicialização explícita desse valor, ou uma interrupção / recriação desse campo ou outra operação violenta semelhante deve redefinir um contador de autoincremento. (O TRUNCATE era uma teoria muito boa.) Parece impossível que você esteja de repente envolvendo um INT de 32 bits quando o último valor que você testemunha é de apenas 600k. Definitivamente não deve ser redefinido apenas porque a tabela é esvaziada. Você tem um bug no mysql ou algo no seu código PHP. Ou o cara no cubículo ao lado está brincando com você.

Você poderia depurar ativando o log binário , pois ele contém declarações como esta ao longo de:

SET INSERT_ID=3747670/*!*/;

Então, pelo menos, você pode ver todos os detalhes do que está acontecendo com essa tabela, inclusive antes de o contador ser redefinido.

    

Answer 5

ALTER TABLE table_name ENGINE = MyISAM

Trabalha para mim. Nossa mesa é sempre mantida muito pequena, então não há necessidade de InnoDB.

    

Answer 6

O InnoDB não armazena o valor de incremento automático no disco, portanto esquece quando o servidor MySQL é desligado. Quando o MySQL é iniciado novamente, o mecanismo InnoDB restaura o valor do incremento automático desta maneira: SELECT (MAX(id) + 1) AS auto_increment FROM table . Isso é um bug que é corrigido no MySQL versão 8.0.

Altere o mecanismo da tabela para resolver o problema:

ALTER TABLE table ENGINE = MyISAM

Ou atualize o servidor MySQL para a versão 8.0 quando for lançado.