Mysterious warning
Recently, I was involved in an investigation whose goal was to find out the reason for a warning message like this:
|
1 |
[Warning] [MY-011825] [InnoDB] Cannot add field `c11` in table `db1`.`test` because after adding it, the row size is 8484 which is greater than maximum allowed size (8126) for a record on index leaf page. |
The message looks clear, isn’t it? Well, the problem was that this particular table had not been changed for years, and so no DDL (ALTER) query was involved here. Moreover, there were not even new writes to this table recently, yet this warning was logged occasionally.
Reproduction attempts
In the attempt to reproduce the problem, there was a first obstacle to even create the said table, as it violated the maximum row size. Below I am using a simple test case table definition that illustrates the real issue:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql > CREATE TABLE `test` ( -> `c1` varchar(255) NOT NULL, -> `c2` varchar(255) DEFAULT NULL, -> `c3` varchar(255) DEFAULT NULL, -> `c4` varchar(255) DEFAULT NULL, -> `c5` varchar(255) DEFAULT NULL, -> `c6` varchar(255) DEFAULT NULL, -> `c7` varchar(255) DEFAULT NULL, -> `c8` varchar(255) DEFAULT NULL, -> `c9` varchar(255) DEFAULT NULL, -> `c10` varchar(255) DEFAULT NULL, -> `c11` varchar(255) DEFAULT NULL, -> PRIMARY KEY (`c1`(40)) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT; ERROR 1118 (42000): 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. |
As the table uses the legacy COMPACT row format, the limitation on maximum row size is lower than in the DYNAMIC used by default in modern MySQL versions now, so changing the format would already allow the CREATE to succeed. Still, the customer’s table was there with COMPACT size. Also, the innodb_strict_mode was ON, which prevents such a table’s creation. Notably, the strict mode is by default enabled only since MySQL 5.7, so this table could have been created under MySQL 5.6 in the past.
So, when I create the table with a definition violating the row format limit, we get the expected warning:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
mysql > set session innodb_strict_mode=0; Query OK, 0 rows affected (0.00 sec) mysql > CREATE TABLE `test` ( … -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT; Query OK, 0 rows affected, 1 warning (0.10 sec) mysql > show warningsG *************************** 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) |
A warning gets printed in the error log, which is already a bit confusing as I am not adding a field but creating the table actually:
|
1 |
2024-08-21T06:21:59.729295Z 9 [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. |
Table definition cache
Later writing into the table does not trigger any warnings or errors regardless of InnoDB’s strict mode. So why does the warning happen occasionally in the error log? We discovered that it will happen whenever the table object is loaded into the table definition cache! Therefore, it can happen over time whenever the cache becomes too small to fit all user tables. The cache is cleared on restarts, so when I bounce the MySQL instance, and later try to access the same table, this is what happens:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
mysql > select c1 from test.test limit 1; +------+ | c1 | +------+ | fo1o | +------+ 1 row in set, 1 warning (0.01 sec) mysql > show warningsG *************************** 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) |
The exact same warning as we saw with CREATE, now also appears in the error log during a simple read:
|
1 |
2024-08-21T06:34:53.511227Z 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. |
This is a confusing message, definitely not expected upon a SELECT statement.
Once the table is loaded in the definition cache, the following queries no longer cause the warning!
So, whenever the table definition is not in the cache, any attempt to access it, even running SHOW CREATE TABLE will trigger the same message suggesting that we are adding a field…
Interestingly, when I remove the column to make the table compliant with the limitation, although MySQL executes the command without any complaints, again the confusing error is logged:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
mysql > alter table test drop column c11; Query OK, 0 rows affected (0.12 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql > show create table test.testG *************************** 1. row *************************** Table: test Create Table: CREATE TABLE `test` ( `c1` varchar(255) NOT NULL, `c2` varchar(255) DEFAULT NULL, `c3` varchar(255) DEFAULT NULL, `c4` varchar(255) DEFAULT NULL, `c5` varchar(255) DEFAULT NULL, `c6` varchar(255) DEFAULT NULL, `c7` varchar(255) DEFAULT NULL, `c8` varchar(255) DEFAULT NULL, `c9` varchar(255) DEFAULT NULL, `c10` varchar(255) DEFAULT NULL, PRIMARY KEY (`c1`(40)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT 1 row in set (0.00 sec) |
|
1 |
2024-08-21T06:43:09.193902Z 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. |
Totally not expected to find this log entry upon removing the column 😀
One more quirk about it: an attempt to re-add the culprit column, triggers two warnings instead of one, where one refers to a temporary table created during the ALTER:
|
1 2 3 4 5 6 |
mysql > set session innodb_strict_mode=0; Query OK, 0 rows affected (0.00 sec) mysql > alter table test add column `c11` varchar(100); Query OK, 0 rows affected, 2 warnings (0.12 sec) Records: 0 Duplicates: 0 Warnings: 2 |
|
1 2 |
2024-08-21T06:47:33.699354Z 8 [Warning] [MY-011825] [InnoDB] Cannot add field `c11` in table `test`.`#sql-ib1091-3131305568` because after adding it, the row size is 8484 which is greater than maximum allowed size (8126) for a record on index leaf page. 2024-08-21T06:47:33.732494Z 8 [Warning] [MY-011825] [InnoDB] Cannot add field `c11` in table `test`.`test` because after adding it, the row size is 8484 which is greater than maximum allowed size (8126) for a record on index leaf page. |
Partially, some weirdness of the said message was reported before: https://bugs.mysql.com/bug.php?id=113695, so I decided to file a supplement: https://bugs.mysql.com/bug.php?id=115954
Conclusion
Despite what an error message may suggest, sometimes the reason may be quite unexpected. Never trust it blindly; rather, try to get to the core.
Looking for more essential problem-solving tips? MySQL Performance Tuning is your guide to the most critical aspects of MySQL performance optimization.
Download the guide and unlock the full potential of your MySQL database today!
About the Author
Przemysław MalkowskiPrzemek joined Support Team at Percona in August 2012. Before that he spent over five years working for Wikia.com (Quantcast Top 50) as System Administrator where he was a key person responsible for seamless building up MySQL powered database infrastructure. Besides MySQL he worked on maintaining all other parts of LAMP stack, with main focus on automation, monitoring and backups.
