MySQL: Error Code: 1118 Row size too large (> 8126). Changing some columns to TEXT or BLOB

Question

I want to create a table of 325 column:

CREATE TABLE NAMESCHEMA.NAMETABLE 
(   
      ROW_ID TEXT NOT NULL ,        //this is the primary key

324 column of these types:
      CHAR(1), 
      DATE, 
      DECIMAL(10,0), 
      DECIMAL(10,7), 
      TEXT, 
      LONG,

) ROW_FORMAT=COMPRESSED;

I replaced all the VARCHAR with the TEXT and i have added Barracuda in the my.ini file of MySQL, this is the attributes added:

innodb_file_per_table=1
innodb_file_format=Barracuda
innodb_file_format_check = ON

but i still have this error:

Error Code: 1118
 Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.

EDIT: I can't change the structure of the database because it's legacy application/system/database. The create of a new table, it's an export of the legacy database.

EDIT2: i wrote this question that is similar to others but inside there are some solution that i found on internet like VARCHAR and Barracuda, but i still have that problem so i decided to open a new question with already the classic answer inside for seeing if someone have other answers

Solution 1

I struggled with the same error code recently, due to a change in MySQL Server 5.6.20. I was able to solve the problem by changing the innodb_log_file_size in the my.ini text file.

In the release notes, it is explained that an innodb_log_file_size that is too small will trigger a "Row size too large error."

http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-20.html

Solution 2

I tried all the solutions here, but only this parameter

innodb_strict_mode             = 0

solved my day...

From the manual:

The innodb_strict_mode setting affects the handling of syntax errors for CREATE TABLE, ALTER TABLE and CREATE INDEX statements. innodb_strict_mode also enables a record size check, so that an INSERT or UPDATE never fails due to the record being too large for the selected page size.

Solution 3

ERROR 1118 (42000) at line 1852:    
Row size too large (> 8126). Changing some columns to TEXT or 
     BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.

---

[mysqld]

innodb_log_file_size = 512M

innodb_strict_mode = 0

ubuntu 16.04 edit path:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

on MS Windows the path will be something like:

C:\ProgramData\MySQL\MySQL Server 5.7\my.ini

Don't forget to retart the service (or restart your machine)

Solution 4

The key parameter is: innodb_page_size

Support for 32k and 64k page sizes was added in MySQL 5.7. For both 32k and 64k page sizes, the maximum row length is approximately 16000 bytes.

The trick is that this parameter can be only changed during the INITIALIZATION of the mysql service instance, so it does not have any affect if you change this parameter after the instance is already initialized (the very first run of the instance).

innodb_page_size can only be configured prior to initializing the MySQL instance and cannot be changed afterward. If no value is specified, the instance is initialized using the default page size. See Section 14.6.1, “InnoDB Startup Configuration”.

So if you do not change this value in my.ini before initialization, the default value will be 16K, which will have row size limit of ~8K. Thats why the error comes up.

If you increase the innodb_page_size, the innodb_log_buffer_size must be also increased. Set it at least to 16M. Also if the ROW_FORMAT is set to COMPRESSED you cannot increase innodb_page_size to 32k, or 64K. It should be DYNAMIC (default in 5.7).

ROW_FORMAT=COMPRESSED is not supported when innodb_page_size is set to 32KB or 64KB. For innodb_page_size=32k, extent size is 2MB. For innodb_page_size=64k, extent size is 4MB. innodb_log_buffer_size should be set to at least 16M (the default) when using 32k or 64k page sizes.

Furthermore the innodb_buffer_pool_size should be increased from 128M to 512M at least, otherwise you will get an error on initialization of the instance (I do not have the exact error).

After this, the row size error gone.

The problem with this is that you have to create a new MySql instance, and migrate data to your new DataBase instance, from old one.

Parameters that I changed and works (after creating a new instance and initialized with the my.ini that is first modified with these settings):

innodb_page_size=64k
innodb_log_buffer_size=32M
innodb_buffer_pool_size=512M

All the settings and descriptions in which I found the solution can be found here:

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html

Hope this helps!

Regards!

Solution 5

Have similar issue this morning and following way saved my life:

Did you try to turn off the innodb_strict_mode?

SET GLOBAL innodb_strict_mode = 0;

and then try to import it again.

innodb_strict_mode is ON using MySQL >= 5.7.7, before it was OFF.

View more solutions