Mysql 5.6 to mysql 8 upgradation throwing warning message the row size is xxx which is greater than maximum allowed size (8126)

Question

While upgrading existing mysql 5.6 to mysql 8 got the warning message logs in mysqld.err logs several times for each database

[Warning] [MY-011825] [InnoDB] Cannot add field abc in table dbName.myTable because after adding it, the row size is 8500 which is greater than maximum allowed size (8126) for a record on index leaf page.

Solution i got on multiple portal is to change row_format compact - > dynamic. is it full proof solution.need to confirm is there any chance of data loss?

Answer 1

The warning message you're encountering during your upgrade from MySQL 5.6 to MySQL 8 is likely related to the maximum row size limit. In MySQL 8, the maximum row size is limited to 8126 bytes. If the row size of a table exceeds this limit, it can result in the warning message you're seeing.

To resolve this issue, you have a few options:

1. Reduce the size of the columns in the table: This is the most straightforward solution. You can try reducing the size of columns in the table that are contributing to the large row size.

2. Use the BLOB or TEXT data types: If you need to store large amounts of data in a single column, consider using the BLOB or TEXT data types, which can store large amounts of data up to 4 GB.

3. Split the table into smaller tables: If the table is too large to be managed efficiently, you may want to consider splitting it into smaller, more manageable tables.

4. Increase the maximum row size limit: You can increase the maximum row size limit by setting the max_allowed_packet system variable to a higher value. However, this approach is not recommended as it can result in performance degradation and may cause other problems.

I recommend trying the first option, reducing the size of the columns, as it is the most straightforward solution and will not result in any performance or stability issues.