[ERROR] InnoDB: Cannot add field in table because after adding it, the row size is 8572 which is greater than maximum allowed size (8126) for a record on index leaf page.

One day, suddenly, we couldn’t reload a production backup.  Oh no!  It took a bit of digging around but here is the issue, should you find yourself running into it:

Using the InnoDB COMPACT ROW_FORMAT, there are some rules InnoDB uses to decide how much data to put on the first page and how much to put on overflow pages.  While VARCHAR and TEXT type columns are typically stored on overflow pages with no problem, when using the COMPACT row format, up to the first 767 bytes of each field will be stored on the primary page.

This error happens when the amount of actual data exceeds the allows limit for a row size on the main page, which is about 50% of the innodb_page_size variable.

https://mariadb.com/kb/en/library/innodb-compact-row-format/

If you have a circumstance with say, 11 TEXT or VARCHAR fields, each with at least 767 bytes of data, and running an innodb_page_size of 16k (which results in 8126 being max row size on main page) …… then 767*11=8437 stored on main page is greater than 8126 which is the “maximum allowed size for a record on index leaf page”, and you will get this error.

The solution is to investigate the use of either DYNAMIC or COMPRESSED row format, depending on your application.

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s