Depois de uma queda de energia, as tabelas InnoDB de um servidor podem voltar sozinhas, enquanto uma tabela MyISAM fica inacessível, com uma mensagem de tabela danificada. O MyISAM não tem registro de alterações nem uma recuperação automática completa, e o modo como ele guarda os dados explica por que esses danos acontecem e como identificá-los.
Os arquivos de uma tabela MyISAM
- vendas.MYD: guarda as linhas da tabela.
- vendas.MYI: guarda os índices e um cabeçalho com o estado da tabela.
- vendas.frm: guarda a definição da tabela, até o MySQL 5.7. No MySQL 8, a definição fica no dicionário de dados, e a tabela ganha um arquivo com a extensão sdi.
O cabeçalho do MYI guarda, entre outras informações, o próximo valor do contador AUTO_INCREMENT, contadores usados pelo otimizador e a indicação de que a tabela está aberta para gravação.
O estado da tabela
Ao abrir uma tabela MyISAM para gravação, o MySQL marca no cabeçalho que a tabela está em uso e desfaz essa marca ao fechá-la corretamente. Quando o serviço para de forma abrupta, a marca fica no arquivo, e a tabela passa a ser vista como não fechada corretamente. O CHECK TABLE avisa esse estado com a mensagem 1 client is using or hasn't closed the table properly, e a tabela pode precisar de verificação e reparo antes do uso.
As alterações do MyISAM vão direto para os arquivos, sem um registro que permita refazê-las ou desfazê-las. Uma gravação interrompida no meio pode deixar uma linha pela metade no MYD ou um índice que não corresponde às linhas no MYI.
Como os danos acontecem
- a queda de energia ou o encerramento abrupto do serviço durante gravações;
- a falta de espaço no disco no meio de uma gravação;
- um antivírus ou um programa de cópia que abre os arquivos da pasta de dados enquanto o MySQL os usa;
- o uso do myisamchk sobre uma tabela que o servidor está usando ao mesmo tempo;
- os defeitos no disco.
Por isso, convém deixar a pasta de dados do MySQL fora da verificação em tempo real do antivírus e fora de programas que copiam arquivos abertos.
Os sinais de dano
- Tabela marcada como danificada: a mensagem is marked as crashed and should be repaired aparece com o código 145 ou 1194, conforme a versão e a operação. A tabela precisa ser reparada antes de voltar a ser usada.
- Último reparo falho: a mensagem is marked as crashed and last repair failed aparece com o código 144 ou 1195. Um reparo anterior não terminou.
- Arquivo de índice incorreto: a mensagem Incorrect key file aparece com o código 1034. O MySQL encontrou um problema no MYI ao usar a tabela.
- Arquivo ausente: a mensagem Can't find file aparece com o código 1017 quando falta um dos arquivos da tabela.
- Erro do engine: a mensagem Got error from storage engine, com os códigos 126, 127 ou 134, indica um índice ou um arquivo de dados danificado.
O erro 1034 nem sempre indica um dano na tabela. Uma consulta grande que monta uma tabela temporária em disco recebe o mesmo erro quando falta espaço na pasta temporária do MySQL, e a conferência do espaço livre vem antes de qualquer reparo.
A verificação e o reparo automático
CHECK TABLE vendas;: verifica a tabela pelo servidor, no modo MEDIUM, que é o padrão do MyISAM;CHECK TABLE vendas EXTENDED;: faz a verificação completa, mais demorada;myisamchk --check vendas: verifica os arquivos diretamente, executado na pasta do banco, com o serviço parado.
A verificação pelo servidor bloqueia as gravações na tabela enquanto dura. O myisamchk lê os arquivos sem passar pelo servidor, e por isso só é seguro com o serviço parado ou com a tabela fora de uso.
O MySQL pode verificar e reparar automaticamente as tabelas MyISAM não fechadas corretamente, no momento em que as abre, com a opção myisam_recover_options, chamada de myisam-recover no arquivo de configuração do MySQL 5.1. A opção vem desligada por padrão, e o comando SELECT @@myisam_recover_options; mostra o valor em uso. Com o valor BACKUP, o reparo automático guarda uma cópia do arquivo de dados antes de alterá-lo, e, com o valor FORCE, o reparo continua mesmo quando precisa descartar linhas.
O bloqueio da tabela inteira
O MyISAM bloqueia a tabela inteira a cada gravação. Uma leitura longa, como a de um relatório, faz as gravações na mesma tabela esperarem até o fim da leitura, e uma gravação na fila faz as leituras seguintes esperarem também. Para reduzir esse efeito, o MyISAM aceita inclusões no fim do arquivo durante uma leitura, quando a tabela não tem espaços livres deixados por exclusões. Na lista de conexões, os comandos que esperam aparecem no estado Locked, no MySQL 5.1, ou Waiting for table level lock, nas versões seguintes, e a espera dura até o fim do comando que bloqueia.
A conversão para o InnoDB
ALTER TABLE vendas ENGINE=InnoDB;: converte a tabela para o InnoDB.
A conversão traz as transações, o bloqueio por linha e a recuperação automática depois de uma queda. Ela exige que os dados possam ser lidos, espaço livre, uma janela de manutenção e a confirmação de que a aplicação funciona com o novo engine. Nas versões anteriores ao MySQL 5.6, o InnoDB não aceita índices de texto completo, e uma tabela MyISAM com esse tipo de índice só é convertida depois da retirada dele.
No suporte
Depois de uma queda de energia, convém verificar primeiro as tabelas MyISAM, que não se recuperam sozinhas. Caixas que param enquanto um relatório grande é gerado podem estar esperando o bloqueio de uma tabela MyISAM. Antes de qualquer reparo, os arquivos da tabela são copiados com o serviço parado.
Para guardar
- Uma tabela MyISAM tem o arquivo de dados MYD, o de índices MYI e, até o MySQL 5.7, o de definição frm.
- O MyISAM grava direto nos arquivos, sem registro de alterações, e uma gravação interrompida pode danificar a tabela.
- As mensagens de tabela marcada como danificada, de arquivo de índice incorreto e de arquivo ausente e os erros do engine são sinais de dano.
- O erro 1034 também aparece quando falta espaço para as tabelas temporárias.
- O MyISAM bloqueia a tabela inteira, e uma leitura longa faz as gravações esperarem.
Depois de uma queda de energia, só as tabelas MyISAM apresentaram erro. Por quê?
O MyISAM grava direto nos arquivos, sem um registro que permita refazer ou desfazer as alterações. Uma gravação interrompida deixa a tabela marcada como não fechada ou com arquivos inconsistentes. O InnoDB usa os próprios registros para se recuperar sozinho.
Uma consulta grande falhou com o erro 1034, de arquivo de índice incorreto. A tabela está danificada?
Não necessariamente. O mesmo erro aparece quando falta espaço em disco para a tabela temporária que a consulta monta. A conferência do espaço livre e um CHECK TABLE vêm antes de qualquer reparo.