1

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

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.

UPDATE 2015-01-26 23:15 EST

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.

  • 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. – Hellon Canella Machado Jan 26 '15 at 3:29
  • Interesting approach. Ok, I'll try it. I didn't know those possibilities. I'm newbie with mysql. Thanks for the advice. – Hellon Canella Machado Jan 26 '15 at 4:43
1

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.

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.