Tag Archives: mysql ERROR 1118

[Solved] MYSQL Import Error: ERROR 1118 (42000): Row size too large (> 8126)

When doing database restore recently, I encountered the following problems

ERROR 1118 (42000) at line 79532: Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRnt row format, BLOB prefix of 768 bytes is stored inline.

 

 

Solution:

Modify the mysql configuration file my.cnf:

innodb_file_format=Barracuda 
innodb_file_per_table=1 

Then restart mysql and execute on the target table:
Alter table <table_name> engine=innodb ROW_FORMAT=DYNAMIC; 

 

Finally re-import.

 

If it still doesn’t work, consider adjusting the parameters:

innodb_log_file_size = 256M try to increase this configuration

ERROR 1118 (42000) at line 1278: Row size too large > 8126

Error importing Zabbix SQL file into Mariadb Times while installing and deploying Zabbix
It’s just that the rows are too long to import,
STH over and over again!Although Baidu master does not give force, but through unremitting efforts, or let me find a solution.

Here is a solution, but I changed a lot of parameters before I found this one.
Add in my.conf

[mysqld]
innodb_strict_mode = 0

The result is not wrong, the big guy’s explanation for this is
Note: The innodb_strict_mode setting affects the handling of syntax errors in the CREATE TABLE, ALTER TABLE, and CREATE INDEX statements. Innodb_strict_mode also enables record size checking, so INSERT or UPDATE never fails because the record is too large for the selected page size.

And then I looked up the differences between these operations
1, CREATE INDEX must provide the INDEX name, for ALTER TABLE, will be automatically created, if you do not provide;
2, CREATE INDEX can only CREATE one INDEX at a time, ALTER TABLE can CREATE more than one
3, only ALTER TABLE can CREATE the primary key,

In case there are more than one parameter to avoid error, I post my modified parameters

max_allowed_packet      = 256M  #修改前是 16M
innodb_buffer_pool_size = 512M  #修改前 54
innodb_log_file_size = 30M
innodb_log_buffer_size  = 32M

#添加的
innodb_file_per_table   = 1  
innodb_large_prefix=1 
innodb_file_format = Barracuda
innodb_strict_mode = 0

Then restart Mariadb and you are done.