Vilx-'s questions

Vamos supor que eu tenha uma consulta no formulário:

INSERT INTO tableA (...) select ... from tableB;

Agora, é possível que esta consulta, durante a execução, bloqueie INSERTinstruções simples para tableB? Informação adicional:

• Todas as tabelas são InnoDB
• Não há transações explícitas (autocommit está ativado)
• NÃO há uma FOR UPDATEcláusula na SELECTinstrução (nem tenho certeza se seria possível fazer isso em uma INSERT...SELECTconstrução)
• A consulta SELECT real é obviamente mais complicada e envolve agrupamento e uniões, mas isso não deve mudar nada, certo?
• O nível de isolamento da transação para todas as consultas é o padrãoREPEATABLE READ
• A versão precisa do banco de dados é MariaDB 10.0.12

Eu tenho duas tabelas no meu MariaDB (10.4.12) que armazenam imagens (JPEG). Eles são mediumblobs, normalmente em torno de 1 MB cada (embora varie). Quando uma nova imagem chega, ela é colocada na TempImagesmesa onde fica por... menos de um minuto. Geralmente 10-20 segundos. Em seguida, ele é movido para a FinalImagesmesa onde é mantido até o fim dos tempos.

As imagens aparecem raramente - uma a cada poucos minutos - então a TempImagesmesa está realmente vazia na maior parte do tempo. Do jeito que está atualmente, o pior que poderia ter são talvez 20 imagens ao mesmo tempo, e isso já está aumentando.

No entanto, por algum motivo, esta tabela continua a crescer. Ontem, ele atingiu mais de 30 GB de tamanho, então o descartamos e o recriamos porque o espaço em disco estava ficando escasso. Mas hoje já atingiu 150MB.

A engine é InnoDB (há transações envolvidas ao inserir/excluir na tabela) e innodb_file_per_table=ON.

Por que o InnoDB não reutiliza o espaço vazio na tabela, mas continua aumentando o tamanho do arquivo?

Aqui está o script de criação completo, talvez haja alguns detalhes extras importantes lá:

CREATE TABLE `TempImages` (
  `id` int(11) NOT NULL,
  `image` mediumblob NOT NULL,
  `small_image` mediumblob DEFAULT NULL,
  `mime_type` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `FK_DATA` FOREIGN KEY (`id`) REFERENCES `TempData` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

(Nota: isso não é o mesmo que não liberar espaço em disco . Espero que o espaço em disco permaneça alocado para a tabela, mas também espero que as novas linhas reutilizem o espaço que sobrou pelas linhas excluídas, conforme explicado no link pergunta. Por alguma razão, isso não está acontecendo.)

(Nota preventiva: a fragmentação também não deve ser um problema, pois a tabela está literalmente vazia na maioria das vezes)

Estou tentando mover nosso ambiente de desenvolvimento para um vagrant box (máquina virtual usando o Oracle virtualbox). Atualmente estou preso em um problema:

O ambiente precisa de um servidor MySQL e alguns bancos de dados. Eu tenho todos os scripts configurados que instalam o MySQL, criam e preenchem os bancos de dados, etc. Isso funciona.

Como os desenvolvedores provavelmente configurarão mais dados por conta própria, minha ideia foi colocar a pasta de dados do MySQL em uma pasta de caixa virtual compartilhada. Dessa forma, quando a máquina vagrant é destruída/recriada, a pasta de dados permanece e pode ser reutilizada sem perda de dados.

No entanto, às vezes (mas nem sempre, não sei o que o aciona) quando eu simplesmente suspendo e retomo a máquina virtual, o MySQL começa a reclamar que muitas de suas tabelas (mas não todas) estão corrompidas. A correção das tabelas resulta na perda de todos os dados nelas contidos. Obviamente isso é inaceitável.

Alguém tem alguma idéia de por que isso acontece e como evitá-lo?

Algumas pistas possíveis ou pistas falsas - desde que li que as pastas compartilhadas do virtualbox são lentas, aumentei o tamanho do pool de buffer do InnoDB para 1 GB, para que ele tenha muito espaço em cache. Nós usamos InnoDB e MyISAM em proporções de cerca de 50/50. Ainda não verifiquei se todas as tabelas com falha pertencem ao mesmo mecanismo - prestarei atenção a ele quando ele travar novamente.

Atualizar

Ha, eu consegui pegá-lo em flagrante! É realmente... suspense... TANTO InnoDB quanto MyISAM.

Selecionando de uma tabela MyISAM dá o erro

A tabela 'xxx' está marcada como travada e deve ser reparada

Tentando executar mysqlcheck -A -a(analisar todas as tabelas) na verdade TRAVA o MySQL. Os arquivos de log contam a história (editados para maior clareza):

[Warning] InnoDB: Retry attempts for writing partial data failed.
[ERROR] InnoDB: Write to file ./ib_logfile0failed at offset 29885952, 1024 bytes should have been written, only 0 were written. Operating system error number 71. Check that your OS and file system support files of this size. Check also that the disk is not full or a disk quota exceeded.
[ERROR] InnoDB: Error number 71 means 'Protocol error'
[ERROR] mysqld got signal 6 
Query (0x7fbc12501b50): ANALYZE TABLE `yyy`

E então, em um momento, systemctl reinicia o serviço e tudo funciona perfeitamente novamente.

Meu palpite é que a suspensão da máquina virtual libera os bloqueios que o virtualbox estava segurando nos arquivos abertos e, quando o virtualbox é iniciado novamente, ele não os adquire novamente. Então, do ponto de vista do MySQL, ele mantém um arquivo aberto, mas o virtualbox perdeu esse controle, então quando o MySQL tenta acessar o arquivo novamente, ele recebe um erro.