CODEWITHSUNDEEP
mysqlinnodb
PHP User
PHP User

Reputation: 2422

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.

Upvotes: 0

Views: 868

Answers (1)

Rick James
Rick James

Reputation: 142208

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.

Upvotes: 1

Related Questions