| Bug #113695 | "MY-011825: Cannot add field xxx in table xxx..." error log message is incorrect | ||
|---|---|---|---|
| Submitted: | 20 Jan 7:52 | Modified: | 22 Jan 14:58 |
| Reporter: | Ke Yu (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
| Version: | 8.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[20 Jan 7:57]
Ke Yu
The bug can be repeated under innodb_strict_mode = off.
[22 Jan 11:21]
MySQL Verification Team
Hi Mr. Yu,
Thank you for your bug report.
However, we are unable to repeat your test case with MySQL 8.0.36. We get the following output:
ERROR 1118 (42000) at line 16: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`c1` varchar(255) DEFAULT 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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
Hence, this bug is already fixed in 8.0.36.
[22 Jan 14:09]
Ke Yu
Hi, this bug should be repeated under innodb_strict_mode = off. Your result is under innodb_strict_mode = on. This bug also exists in 8.0.36.
[22 Jan 14:23]
MySQL Verification Team
Hi Mr. Yu,
Again, this is expected behaviour.
According to our manual, when that mode is set to OFF, you get a warning and the message in the error log:
Level Code Message
Warning 139 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.
Warning 139 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 ***************************
Table: t
Create Table: CREATE TABLE `t` (
`c1` varchar(255) DEFAULT 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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT;
Hence, you have got warnings, which you ignored.
If you try to select 'c11' you will see that it does not exist.
That is why setting that mode to OFF is not recommended.
It is done at your own peril.
[22 Jan 14:33]
Ke Yu
Hi, The column `c11` is successfully added! And you should check the error log message ' Cannot add field `c11` in table `tpcc`.`t` because after adding it, the row size is 8716 which is greater than maximum allowed size (8126) for a record on index leaf page.' not the warning message of clients. You can then insert some data into table t and make queries: mysql> insert into t values(1,1,1,1,1,1,1,1,1,1,1); Query OK, 1 row affected (0.00 sec) mysql> select * from t; +------+------+------+------+------+------+------+------+------+------+------+ | c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | c9 | c10 | c11 | +------+------+------+------+------+------+------+------+------+------+------+ | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | +------+------+------+------+------+------+------+------+------+------+------+ 1 row in set (0.00 sec) mysql> select c11 from t; +------+ | c11 | +------+ | 1 | +------+ 1 row in set (0.00 sec)
[22 Jan 14:58]
MySQL Verification Team
Hi, Actually, we managed to repeat the behaviour. Not only that, but restarting MySQL server will show that table with 'c11' column included. However, no INSERT will work into that table and many other operations will fail. Hence, this requires that this option is described as VERY dangerous and we are proposing that we remove it from MySQL. There is no sense in fixing the behaviour for the mode that is quite dangerous. Hence, this bug is verified as a Documentation bug, while we will also propose that this option is deprecated. This report is now a Documentation bug and a separate bug that this option is disabled.
Description: When add a column in a table, the DDL is successfully executed. But error log reports an failed warning message "Cannot add field `c11` in table `test`.`t` because after adding it, the row size is 8716 which is greater than maximum allowed size (8126) for a record on index leaf page." It makes me wonder. How to repeat: 1. create a table: CREATE TABLE `t` ( `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) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT; 2. add a column. the DDL is successfully executed: mysql> alter table t add column `c11` varchar(255) after c10; Query OK, 0 rows affected, 2 warnings (0.02 sec) Records: 0 Duplicates: 0 Warnings: 2 3. 'show create table t;' can find that the column `c11` has been added: mysql> show create table t\G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `c1` varchar(255) DEFAULT 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 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT 1 row in set (0.00 sec) 4. bug error log reports the add column DDL is failed: [Warning] [MY-011825] [InnoDB] Cannot add field `c11` in table `test`.`t` because after adding it, the row size is 8716 which is greater than maximum allowed size (8126) for a record on index leaf page. Suggested fix: Correct the error log information.