Bug #96363 Incorrect "Too many columns" for mysql create table with comments
Submitted: 29 Jul 11:00 Modified: 29 Jul 11:37
Reporter: dennis gao Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.7.23, 5.7.27 OS:CentOS (7.3)
Assigned to: CPU Architecture:Any

[29 Jul 11:00] dennis gao
Description:
For a table with large number of columns and each column with long comment string, mysql may fail to create table due to "Too many columns".
But if we delete all comments, the table can be created normally.

How to repeat:
Use sql1.txt to create table will fail due to "ERROR 1117 (HY000): Too many columns":
But use sql2.txt  can be executed normally:

Suggested fix:
The innodb column num limit is 1017, it should not be affected by the column comment.
[29 Jul 11:01] dennis gao
the create table sql will fail

Attachment: sql1.txt (text/plain), 108.33 KiB.

[29 Jul 11:01] dennis gao
the create table sql will success

Attachment: sql2.txt (text/plain), 31.80 KiB.

[29 Jul 11:37] Umesh Shastry
Hello Dennis Gao,

Thank you for the report and test case.
Observed that 5.7.27 is affected.

Thanks,
Umesh
[29 Jul 11:41] Umesh Shastry
- 5.7.27

bin/mysql -uroot -S /tmp/mysql_ushastry.sock test < ../mysql-8.0.17/1.sql
ERROR 1117 (HY000) at line 1: Too many columns

- 8.0.17 - looks like this issue is no longer seen

bin/mysql -uroot -S /tmp/mysql_ushastry.sock test < ./1.sql
bin/mysql -uroot -S /tmp/mysql_ushastry.sock test -e 'show create table test.test_db\G'
.
| test_db | CREATE TABLE `test_db` (
  `ID` char(38) NOT NULL COMMENT '电电电电电电',
  `COL_1` decimal(21,6) DEFAULT NULL COMMENT '(电电电电)电电电电电电电电电电电电电电电-1第1行第3列单元格值',
  `COL_2` decimal(21,6) DEFAULT NULL COMMENT '(电电电电)电电电电电电电电电电电电电电电-1第2行第3列单元格值',
.
  `COL_772` decimal(21,6) DEFAULT NULL COMMENT '(电电电电)电电电电电电电电电电电电电电电-1第60行第7列单元格值',
  `COL_773` decimal(21,6) DEFAULT NULL COMMENT '(电电电电)电电电电电电电电电电电电电电电-1第61行第7列单元格值',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |