Row size too large (> 8126)

Question

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;    

Answer 1

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.

Answer 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.

Answer 3

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.

Answer 4

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