Início / dba / Perguntas / 345532
Accepted
peppy
Asked: 2025-03-03 07:48:55 +0800 CST

Como truncar tabelas no cron job enquanto mysqldump está em execução? Prevenir erro 1412 "Table Definition Has Changed"

  • 772

Tenho um script de tarefa cron de manutenção que é executado a cada 5 minutos que TRUNCA uma tabela, a preenche novamente com dados de manutenção e, em seguida, a TRUNCA no final. Também executo um script mysqldump diário que faz backup de todas as minhas tabelas. Percebi recentemente que algumas das tabelas estavam faltando no backup. Após uma investigação mais aprofundada, descobri que o mysqldump está gerando este erro:

Error 1412 "Table Definition Has Changed"

Depois de investigar mais a fundo, aparentemente o problema é porque meu script de tarefa cron está usando TRUNCATE para esvaziar a tabela no meio de um backup do mysqldump.

Estou usando a opção --single-transaction, mas isso não faz diferença.

Existe alguma maneira de permitir que o mysqldump continue mesmo se uma tabela for truncada enquanto o mysqldump estiver em execução (e ainda fazer backup dessa tabela)? Isso precisa ser feito sem a opção --ignore-table. Descobri como fazer isso, mas prefiro incluir todas as minhas tabelas de manutenção no backup, caso o banco de dados precise ser completamente restaurado de um backup.

A única outra coisa que consegui pensar foi usar "DELETE FROM table" em vez de "TRUNCATE table", mas eu teria que passar por todos os meus scripts em todos os meus sites para mudar isso.

mysql

1 respostas

  1. Best Answer

    Ao usar mysqldump --single-transaction, está documentado que nenhuma outra sessão deve usar TRUNCATE TABLEou outro DDL.

    https://dev.mysql.com/doc/refman/8.4/en/mysqldump.html diz:

    Enquanto um --single-transactiondump estiver em andamento, para garantir um arquivo de dump válido (conteúdo de tabela correto e coordenadas de log binário), nenhuma outra conexão deve usar as seguintes instruções: ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. Uma leitura consistente não é isolada dessas instruções, então o uso delas em uma tabela a ser despejada pode fazer com que o SELECTque é executado pelo mysqldump para recuperar o conteúdo da tabela obtenha conteúdo incorreto ou falhe.

    https://dev.mysql.com/doc/refman/8.4/en/innodb-consistent-read.html diz:

    A leitura consistente não funciona em certas instruções DDL:

    • A leitura consistente não funciona em ALTER TABLEoperações que fazem uma cópia temporária da tabela original e excluem a tabela original quando a cópia temporária é criada. Quando você reemite uma leitura consistente dentro de uma transação, as linhas na nova tabela não ficam visíveis porque essas linhas não existiam quando o snapshot da transação foi tirado. Nesse caso, a transação retorna um erro: ER_TABLE_DEF_CHANGED, “A definição da tabela foi alterada, tente a transação novamente”.

    Esta documentação especifica DDL que cria uma cópia temporária, mas o mesmo erro ocorre para outro DDL que altera ou trunca uma tabela sem criar uma cópia temporária. Cf. https://bugs.mysql.com/bug.php?id=116132

    Há duas maneiras de resolver isso ao usar o mysqldump.

    1. A primeira solução é executar mysqldump --skip-lock-tables, sem usar a --single-transactionopção . Isso despeja cada tabela em seu próprio snapshot, então se outra sessão truncar a tabela, isso não entrará em conflito com o snapshot maior criado por --single-transaction.

      A desvantagem dessa solução é que seu backup será inconsistente. Ou seja, você não tem garantia de integridade referencial, porque tabelas podem ser modificadas durante o dump, e linhas de uma tabela podem ser saídas no dump que referenciam linhas em outra tabela que não existem no dump.

    2. A segunda solução é usar um bloqueio global para bloquear DDL como TRUNCATE TABLEenquanto o dump estiver em execução. Você pode usar mysqldump --lock-all-tablespara fazer isso. Se outra sessão tentar TRUNCATE TABLE, ela esperará até que o bloqueio global seja liberado após o dump terminar.

      A desvantagem dessa solução é que todos os outros DDL e DML também são bloqueados. Seu banco de dados fica basicamente fora de serviço (pelo menos para gravações) enquanto o dump está em execução.

    Outras soluções envolvem o uso de uma ferramenta de backup diferente ou exigem algumas alterações no código, e você disse que não queria fazer isso.

    • 1

relate perguntas