Description:
Supposedly we end up with a table with a definition violating the row limit. It can happen after upgrading from MySQL 5.6 where innodb_strict_mode was by default disabled or the strict mode is disabled explicitly. The said warning, for example:
[Warning] [MY-011825] [InnoDB] Cannot add field `c11` in table `test`.`test` because after adding it, the row size is 8872 which is greater than maximum allowed size (8126) for a record on index leaf page.
is logged not only when one tries to alter the table (and add a new column as the message suggests), but also on the following other occasions:
* CREATE TABLE
* ALTER TABLE ... DROP COLUMN (even if dropping actually allows it to fit into size restrictions)
* Loading the table object into the table definition cache
I consider this report to be an extension to https://bugs.mysql.com/bug.php?id=113695
How to repeat:
mysql > select @@version;
+-----------+
| @@version |
+-----------+
| 8.4.2 |
+-----------+
1 row in set (0.00 sec)
mysql > set session innodb_strict_mode=0;
Query OK, 0 rows affected (0.00 sec)
mysql > CREATE TABLE `test` (
-> `c1` varchar(255),
-> `c2` varchar(255),
-> `c3` varchar(255),
-> `c4` varchar(255),
-> `c5` varchar(255),
-> `c6` varchar(255),
-> `c7` varchar(255),
-> `c8` varchar(255),
-> `c9` varchar(255),
-> `c10` varchar(255),
-> `c11` varchar(255),
-> PRIMARY KEY (`c1`(40))
-> ) ENGINE=InnoDB ROW_FORMAT=COMPACT;
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql > show warnings\G
*************************** 1. row ***************************
Level: Warning
Code: 139
Message: 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.
1 row in set (0.00 sec)
Error log new entry:
2024-08-28T14:55:50.916840Z 8 [Warning] [MY-011825] [InnoDB] Cannot add field `c11` in table `test`.`test` because after adding it, the row size is 8872 which is greater than maximum allowed size (8126) for a record on index leaf page.
mysql > set session innodb_strict_mode=1;
Query OK, 0 rows affected (0.00 sec)
mysql > insert into test set c1="foobar";
Query OK, 1 row affected (0.00 sec)
-- Restart the server and try to access the table:
mysql > select c1 from test;
+--------+
| c1 |
+--------+
| foobar |
+--------+
1 row in set, 1 warning (0.09 sec)
mysql > show warnings\G
*************************** 1. row ***************************
Level: Warning
Code: 139
Message: 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.
1 row in set (0.00 sec)
And again the same in error log:
2024-08-28T15:08:28.982806Z 8 [Warning] [MY-011825] [InnoDB] Cannot add field `c11` in table `test`.`test` because after adding it, the row size is 8872 which is greater than maximum allowed size (8126) for a record on index leaf page.
Another select, as the table object is already loaded, does not trigger the warning again:
mysql > select c1 from test;
+--------+
| c1 |
+--------+
| foobar |
+--------+
1 row in set (0.00 sec)
Removing the culprit column works without any warning:
mysql > alter table test drop column c11;
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
But surprisingly, the confusing error is printed into the log:
2024-08-28T15:11:39.717489Z 8 [ERROR] [MY-011825] [InnoDB] Cannot add field `c11` in table `test`.`test` because after adding it, the row size is 8872 which is greater than maximum allowed size (8126) for a record on index leaf page.
Suggested fix:
I think it would be more appropriate to log a more customized message instead of "Cannot add field" in each of these cases. It is certainly odd to read this when we only read from the table or drop a column.
