Pages

Friday, March 13, 2020

MySQL Troubleshooting: Error 1118 Row Size Too Large

A while back I found myself attempting to correct an issue relating to the restore of a MySQL database backup. The problem at hand was the result of a very large table that would halt the process with the following: 

Error 1118 Row Size Too Large.

The source of the error was a table that contained numerous fields, tallying in the hundreds. While the database contained this table with a large row size, it did not present any particular issues executing common operations such as INSERTs, UPDATEs or DELETEs. Problems were only present if one attempted to restore the database.

Following various attempts to correct the problem, the solution eventually revealed itself. As it turns out, in some versions of MySQL, a server parameter that enforces certain rules upon creating tables is enabled by default. This validation examines the row size of each table as it is being created and if it exceeds the row size limit for the server, an error 1118 occurs, causing the restore process to halt.

The server setting in question is:

innodb_strict_mode

The setting is either defined as on or off and only affects tables utilizing the InnoDB engine. 

Setting the parameter to off (innodb_strict_mode = 0) and restarting the server allows the restore process to complete. It is worth noting that turning off the setting merely changes the behavior of the server such that it reports row size violations as warnings instead of errors, but does not alter the ability of the server to support more fields per table. This latter point is important because attempts to add additional columns to a table with a large row size will also cause the referenced error.

More information on the setting is found at the following page:

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

 

1 comment:

  1. This is very educational content and written well for a change. It's nice to see that some people still understand how to write a quality post.! angel number 333 meaning

    ReplyDelete

Your comments, feedback and suggestions are welcome, but please refrain from using offensive language and/or berating others. Thank you in advance.