Alterar limite para “tamanho da linha Mysql muito grande”

Como posso mudar o limite?

Tamanho de linha muito grande (> 8126). Alterar algumas colunas para TEXT ou BLOB ou usar ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED pode ajudar. No formato de linha atual, o prefixo BLOB de 768 bytes é armazenado em linha.

Mesa:

 id int(11) No name text No date date No time time No schedule int(11) No category int(11) No top_a varchar(255) No top_b varchar(255) No top_c varchar(255) No top_d varchar(255) No top_e varchar(255) No top_f varchar(255) No top_g varchar(255) No top_h varchar(255) No top_i varchar(255) No top_j varchar(255) No top_title_a varchar(255) No top_title_b varchar(255) No top_title_c varchar(255) No top_title_d varchar(255) No top_title_e varchar(255) No top_title_f varchar(255) No top_title_g varchar(255) No top_title_h varchar(255) No top_title_i varchar(255) No top_title_j varchar(255) No top_desc_a text No top_desc_b text No top_desc_c text No top_desc_d text No top_desc_e text No top_desc_f text No top_desc_g text No top_desc_h text No top_desc_i text No top_desc_j text No status int(11) No admin_id int(11) No 

   

A pergunta foi feita no serverfault também.

Você pode dar uma olhada neste artigo que explica muito sobre o tamanho das linhas do MySQL. É importante observar que, mesmo se você usar campos TEXT ou BLOB, o tamanho da sua linha ainda pode ter mais de 8K (limite para InnoDB), pois armazena os primeiros 768 bytes para cada campo em linha na página.

A maneira mais simples de corrigir isso é usar o formato de arquivo Barracuda com o InnoDB. Isso basicamente se livra do problema ao armazenar apenas o ponteiro de 20 bytes nos dados de texto, em vez de armazenar os primeiros 768 bytes.


O método que funcionou para o OP foi:

  1. Adicione o seguinte ao arquivo my.cnf na seção [mysqld] .

     innodb_file_per_table=1 innodb_file_format = Barracuda 
  2. ROW_FORMAT=COMPRESSED a tabela para usar ROW_FORMAT=COMPRESSED .

     ALTER TABLE nombre_tabla ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8; 

Existe a possibilidade de que o acima ainda não resolva seus problemas. É um bug conhecido (e verificado) com o mecanismo InnoDB , e uma correção temporária para agora é fazer fallback para o mecanismo MyISAM como armazenamento temporário. Então, no seu arquivo my.cnf :

 internal_tmp_disk_storage_engine=MyISAM 

Eu encontrei esse problema recentemente e resolvi de uma maneira diferente. Se você estiver executando o MySQL versão 5.6.20, há um bug conhecido no sistema. Veja os documentos do MySQL

Importante Devido ao Bug # 69477, as gravações de redo log para campos BLOB grandes e armazenados externamente podem replace o ponto de verificação mais recente. Para resolver esse bug, um patch introduzido no MySQL 5.6.20 limita o tamanho do redo log que o BLOB grava em 10% do tamanho do arquivo de redo log. Como resultado desse limite, innodb_log_file_size deve ser configurado para um valor maior que 10 vezes o maior tamanho de dados BLOB encontrado nas linhas de suas tabelas mais o comprimento de outros campos de comprimento variável (campos do tipo VARCHAR, VARBINARY e TEXT).

Na minha situação, a tabela de blobs ofensivos tinha cerca de 16MB. Assim, a maneira que eu resolvi foi adicionando uma linha para my.cnf que assegurou que eu tinha pelo menos 10x essa quantidade e, em seguida, alguns:

innodb_log_file_size = 256M

Se você pode mudar o motor e usar MyISAM em vez de InnoDB, isso deve ajudar:

ENGINE=MyISAM

Existem duas ressalvas com o MyISAM (sem dúvida mais):

  1. Você não pode usar transactions.
  2. Você não pode usar restrições de chave estrangeira.

Eu gostaria de compartilhar uma resposta incrível, pode ser útil. Créditos Bill Karwin veja aqui https://dba.stackexchange.com/questions/6598/innodb-create-table-error-row-size-too-large

Eles variam de acordo com o formato do arquivo InnoDB. Atualmente existem 2 formatos chamados Antelope e Barracuda.

O arquivo de espaço de tabela central (ibdata1) está sempre no formato Antelope. Se você usar o arquivo por tabela, poderá fazer com que os arquivos individuais usem o formato Barracuda configurando innodb_file_format = Barracuda em my.cnf.

Pontos Básicos:

  1. Uma página de 16KB de dados do InnoDB deve conter pelo menos duas linhas de dados. Além disso, cada página tem um header e um rodapé contendo as sums de verificação da página e o número de sequência do registro, e assim por diante. É aí que você obtém seu limite de um pouco menos de 8 KB por linha.

  2. Os tipos de dados de tamanho fixo, como INTEGER, DATE, FLOAT, CHAR, são armazenados nessa página de dados primários e contam para o limite de tamanho da linha.

  3. Os tipos de dados de tamanho variável, como VARCHAR, TEXT e BLOB, são armazenados em páginas de estouro, portanto, não são totalmente incluídos no limite de tamanho de linha. No Antelope, até 768 bytes dessas colunas são armazenados na página de dados primários, além de serem armazenados na página de estouro. O Barracuda suporta um formato de linha dynamic, portanto, ele pode armazenar apenas um ponteiro de 20 bytes na página de dados principal.

  4. Os tipos de dados de tamanho variável também são prefixados com 1 ou mais bytes para codificar o comprimento. E o formato de linha InnoDB também possui uma matriz de deslocamentos de campo. Portanto, há uma estrutura interna mais ou menos documentada em seu wiki.

O Barracuda também suporta um ROW_FORMAT = COMPRESSED para obter mais eficiência de armazenamento para dados de estouro.

Eu também tenho que comentar que eu nunca vi uma tabela bem projetada exceder o limite de tamanho da linha. É um forte “cheiro de código” que você está violando a condição de grupos repetidos da Primeira Forma Normal.

Defina os seguintes no seu arquivo my.cnf e reinicie o servidor mysql.

 innodb_strict_mode = 0 

Depois de passar horas eu encontrei a solução: basta executar o seguinte SQL no seu administrador do MySQL para converter a tabela para MyISAM:

 USE db_name; ALTER TABLE table_name ENGINE=MYISAM; 

Eu tive o mesmo problema, isso resolveu para mim:

 ALTER TABLE `my_table` ROW_FORMAT=DYNAMIC; 

Da documentação do MYSQL:

O formato de linha DYNAMIC mantém a eficiência de armazenar toda a linha no nó de índice se ela se encheckbox (como os formatos COMPACT e REDUNDANT), mas esse novo formato evita o problema de preencher nós B-tree com um grande número de bytes de dados colunas longas. O formato DYNAMIC baseia-se na idéia de que, se uma parte de um valor de dados longos for armazenado fora da página, geralmente é mais eficiente armazenar todo o valor fora da página. Com o formato DYNAMIC, colunas mais curtas provavelmente permanecerão no nó B-tree, minimizando o número de páginas de estouro necessárias para qualquer linha.

Eu também encontrei o mesmo problema. Eu resolvo o problema executando o seguinte sql:

 ALTER ${table} ROW_FORMAT=COMPRESSED; 

Mas, eu acho que você deve saber sobre o armazenamento de linha .
Existem dois tipos de colunas: coluna de comprimento variável (como VARCHAR, VARBINARY e tipos BLOB e TEXT) e coluna de comprimento fixo . Eles são armazenados em diferentes tipos de páginas.

Colunas de tamanho variável são uma exceção a essa regra. Colunas como BLOB e VARCHAR que são muito longas para caber em uma página de tree B são armazenadas em páginas de disco alocadas separadamente, chamadas de páginas de estouro. Nós chamamos tais colunas de colunas fora da página. Os valores dessas colunas são armazenados em listas unicamente vinculadas de páginas de estouro, e cada uma dessas colunas tem sua própria lista de uma ou mais páginas de estouro. Em alguns casos, tudo ou um prefixo do valor da coluna longa é armazenado na tree B, para evitar o desperdício de armazenamento e eliminar a necessidade de ler uma página separada.

e quando o propósito de definir ROW_FORMAT é

Quando uma tabela é criada com ROW_FORMAT = DINÂMICO ou ROW_FORMAT = COMPRIMIDO, o InnoDB pode armazenar valores de coluna de tamanho variável longos (para os tipos VARCHAR, VARBINARY e BLOB e TEXT) totalmente fora da página, com o registro de índice clusterizado contendo apenas um ponteiro de byte para a página de estouro.

Quer saber mais sobre os formatos de linha DINÂMICO e COMPRIMIDO

As outras respostas abordam a pergunta feita. Vou abordar a causa subjacente: design de esquema pobre.

Não insira uma matriz nas colunas. Aqui você tem 3 * 10 colunas que devem ser transformadas em 10 linhas de 3 colunas em uma nova tabela (mais id , etc)

Sua mesa Main teria apenas

 id int(11) No name text No date date No time time No schedule int(11) No category int(11) No status int(11) No admin_id int(11) No 

Sua mesa extra ( Top ) teria

 id int(11) No -- for joining to Main seq TINYINT UNSIGNED -- containing 1..10 img varchar(255) No title varchar(255) No desc text No PRIMARY KEY(id, seq) -- so you can easily find the 10 top_titles 

Haveria 10 (ou menos? Ou mais?) Linhas em Top para cada id .

Isso elimina seu problema original e limpa o esquema. (Isso não é “normalização”, como debatido em alguns dos Comentários.)

Não mude para o MyISAM; está indo embora.
Não se preocupe com ROW_FORMAT .

Você precisará alterar seu código para fazer o JOIN e manipular várias linhas em vez de várias colunas.

Eu corri para este problema quando eu estava tentando restaurar um database mysql de backup de um servidor diferente. O que resolveu esse problema para mim foi adicionar certas configurações ao my.conf (como nas perguntas acima) e além disso alterar o arquivo de backup sql:

Passo 1: adicione ou edite as seguintes linhas no my.conf:

 innodb_page_size=32K innodb_file_format=Barracuda innodb_file_per_table=1 

Etapa 2 adicione ROW_FORMAT = DYNAMIC à tabela create statement no arquivo de backup sql da tabela que está causando este erro:

 DROP TABLE IF EXISTS `problematic_table`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `problematic_table` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, ... PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 ROW_FORMAT=DYNAMIC; 

a importante mudança acima é ROW_FORMAT = DINÂMICA; (que não foi incluído no arquivo de backup orignal sql)

fonte que me ajudou a resolver este problema: MariaDB e InnoDB MySQL Row tamanho muito grande