PHP User PHP User - 22 days ago 10
MySQL Question

MySQL InooDB error [42000][1118] Row size too large (> 8126) main solution did not work

I'm having this problem which appeared suddenly without updating local server at all any it was working fine. I tried to update data in a text field of MySQL 5.6 InnoDB but i got this error (table contains 24 text fields with only 1 row)

[42000][1118] 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.


So I searched and found this solution (change in my.ini) and restarted MySQL service Solution page

max_allowed_packet = 1G
innodb_log_file_size = 2G
innodb_log_buffer_size = 512M


But it didn't solve the problem then searched again and found this solution here in stackoverflow Solution page and restarted MySQL service

innodb_log_file_size = 10G


and tried to use compression like this

ALTER TABLE pages ROW_FORMAT=compressed


but got this warning

Warning Code : 1478
InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope.
Warning Code : 1478
InnoDB: assuming ROW_FORMAT=COMPACT.


And it didn't solve the problem too. So how to solve this problem on localhost and on the server without restarting the MySQL service because the hosting would refuse restarting.

Answer

Plan A (work around): Build a parallel table ("Vertical partitioning"). Move some of the big fields to it. The new table can have the same PRIMARY KEY (but not AUTO_INCREMENT). Use JOIN when you need to SELECT both sets of columns.

Plan B: Let's see SHOW CREATE TABLE so we can critique the columns.

Comments