8

I'm facing the following problem.

Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In

current row format, BLOB prefix of 768 bytes is stored inline.

It appears just when I'm uploading a image to the database (it has <1Mb). Precisely, just 100kb.

I've tried a lot of things: to change the properties "max_allowed_packet", "innodb_log_file_size" (namely, to increase the size to 512M) and nothing...

I dont know the cause of the trouble.

To illustrate, the table

TABLE(
    `passeio` int(4) unsigned NOT NULL COMMENT 'identitificador do passeio',
      `data_inclusao` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `nome_passeio` varchar(200) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
      `inicio` date NOT NULL,
      `fim` date NOT NULL,
      `por_que_ir` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
      `pdf_roteiro` mediumblob NOT NULL,
      `incluso` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
      `nao_incluso` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
      `valor_descricao` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
      `valor_vista` decimal(10,0) NOT NULL,
      `valor_total_parcelado` decimal(10,0) NOT NULL,
      `numero_parcelas` int(2) unsigned NOT NULL,
      `forma_pagamento` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
      `avisos_importantes` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
      `foto_principal` blob NOT NULL,
      `foto_2` blob NOT NULL,
      `foto_3` blob NOT NULL,
      `foto_4` blob NOT NULL,
      `foto_5` blob NOT NULL,
      `foto_6` blob NOT NULL,
      `foto_7` blob NOT NULL,
      `foto_8` blob NOT NULL,
      `foto_9` blob NOT NULL,
      `foto_10` blob NOT NULL,
      `foto_11` blob NOT NULL,
      `foto_12` blob NOT NULL,
      `foto_13` blob NOT NULL,
      `foto_14` blob NOT NULL,
      `foto_15` blob NOT NULL,
      `foto_16` blob NOT NULL,
      `foto_17` blob NOT NULL,
      `foto_18` blob NOT NULL,
      `foto_19` blob NOT NULL,
      `foto_20` mediumblob NOT NULL,
      `valor_entrada` decimal(10,0) NOT NULL,
      `data_partida` date NOT NULL,
      `local_partida_1` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
      `hora_partida_1` time NOT NULL,
      `local_partida_2` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
      `hora_partida_2` time NOT NULL,
      `local_partida_3` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
      `hora_partida_3` time NOT NULL,
      `local_partida_4` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
      `hora_partida_4` time NOT NULL,
      `local_partida_5` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
      `hora_partida_5` time NOT NULL,
      `local_partida_6` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
      `hora_partida_6` time NOT NULL
    ) ENGINE=InnoDB AUTO_INCREMENT=77 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;    

4 Answers 4

12

Bill Karwin has addressed this before in his answer to Row size error with MySQL

I also addressed this in the past : MySQL: Row size too large (> 8126)

Based on his post, and the fact that you still have several TEXT and VARCHAR fields, you should set the following values higher in my.cnf:

[mysqld]
max_allowed_packet = 1G
innodb_log_file_size = 2G
innodb_log_buffer_size = 512M

Then, restart mysqld.

Your comment

In act of despair, I've erased yesterday all the blob columns. I've leave just one. The problem disappeared . But I'll create them again an will try your approach. As soon as possible I return with the result. Thanks for the indication

You should not put 20 BLOB in one table. You should create table to hold the BLOBs

CREATE TABLE mi_fotos
(
    id INT NOT NULL AUTO_INCREMENT,
    passeio INT NOT NULL,
    foto BLOB,
    PRIMARY KEY (id)
) ENGINE=InnoDB;

Store your photos in this table and have passeio in this table link back to your original table.

0
2

I was trying to load a dumpfile and ran into this. It was failing on a CREATE TABLE statement.

I was able to at least get the CREATE TABLE statement to run by setting

innodb_strict_mode = 0

That'll turn this error into a warning. While loading the dump, this error repeated twice, but the table was created with all the fields:

[Warning] InnoDB: Cannot add field `field_GF20140210120838544997000000` in table `mydb`.`mytable` because after adding it, the row size is 7391 which is greater than maximum allowed size (7372) for a record on index leaf page.

Contrary to the warning, the field field_GF20140210120838544997000000 does exist.

I'm probably missing some data locally, but at least refactoring is a possibility now that I have it loaded.

0

I'm using mysql 5.7.36

The below changes to my.ini file worked for me:

max_allowed_packet = 900M
innodb-default-row-format = dynamic
innodb-lock-wait-timeout = 1200
innodb-log-buffer-size=512M
innodb-log-file-size = 1G

Although I cannot confirm, but I think the 1st 2 settings made the most impact.

Restart the service for the changes to take effect.

0

For me, just running OPTIMIZE TABLE <table_name> solved the problem.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.