MySQL Bugs: #120370: Regression (from 8.0.45) on Row size too large

Bug #120370	Regression (from 8.0.45) on Row size too large
Submitted:	28 Apr 16:33	Modified:	22 May 14:45
Reporter:	Homer Simpsons	Email Updates:
Status:	Open	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S2 (Serious)
Version:	8.0.46	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	row size too large

Description:
The following SQL works on MySQL 8.0.45 but breaks on MySQL 8.0.46. Commenting out the `columns227` make it works on MySQL 8.0.46.

A patch version should probably not break this kind of schema.

The error raised is:
```log
#1118 - Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.
```

There release note at https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-46.html does not mention anything regarding row size changes.

Note that it is rather tedious to create an oracle account:
- It requires a lot of private details
- The rbot check does not work on firefox in incognito

Nonetheless, thanks a lot for the MySQL product !

How to repeat:
Run the following statement to run into the issue:
```sql
CREATE TABLE `test1` (
  `id` char(36) COLLATE utf8mb4_unicode_ci NOT NULL,
  `columns0` int NOT NULL,
  `columns1` int NOT NULL,
  `columns2` int NOT NULL,
  `columns3` char(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns4` int DEFAULT NULL,
  `columns5` int DEFAULT NULL,
  `columns6` int DEFAULT NULL,
  `columns7` int DEFAULT NULL,
  `columns8` int DEFAULT NULL,
  `columns9` int DEFAULT NULL,
  `columns10` int DEFAULT NULL,
  `columns11` int DEFAULT NULL,
  `columns12` int DEFAULT NULL,
  `columns13` int DEFAULT NULL,
  `columns14` int DEFAULT NULL,
  `columns15` int DEFAULT NULL,
  `columns16` int DEFAULT NULL,
  `columns17` int DEFAULT NULL,
  `columns18` int DEFAULT NULL,
  `columns19` int DEFAULT NULL,
  `columns20` int DEFAULT NULL,
  `columns21` char(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns22` char(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns23` int DEFAULT NULL,
  `columns24` int DEFAULT NULL,
  `columns25` int DEFAULT NULL,
  `columns26` int DEFAULT NULL,
  `columns27` int DEFAULT NULL,
  `columns28` char(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns29` char(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns30` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns31` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns32` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `columns33` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns34` double DEFAULT NULL,
  `columns35` double DEFAULT NULL,
  `columns36` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns37` varchar(25) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns38` varchar(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns39` varchar(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns40` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns41` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns42` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns43` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns44` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `columns45` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns46` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `columns47` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `columns48` datetime DEFAULT NULL,
  `columns49` datetime DEFAULT NULL,
  `columns50` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns51` varchar(25) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns52` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns53` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns54` datetime DEFAULT NULL,
  `columns55` datetime DEFAULT NULL,
  `columns56` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns57` tinyint(1) DEFAULT NULL,
  `columns58` varchar(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns59` varchar(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns60` varchar(120) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns61` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns62` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns63` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns64` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns65` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns66` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns67` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns68` varchar(25) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns69` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns70` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns71` tinyint(1) DEFAULT NULL,
  `columns72` tinyint(1) DEFAULT NULL,
  `columns73` tinyint(1) DEFAULT NULL,
  `columns74` tinyint(1) DEFAULT NULL,
  `columns75` tinyint(1) DEFAULT NULL,
  `columns76` tinyint(1) DEFAULT NULL,
  `columns77` tinyint(1) DEFAULT NULL,
  `columns78` tinyint(1) DEFAULT NULL,
  `columns79` tinyint(1) DEFAULT NULL,
  `columns80` tinyint(1) DEFAULT NULL,
  `columns81` tinyint(1) DEFAULT NULL,
  `columns82` tinyint(1) DEFAULT NULL,
  `columns83` tinyint(1) DEFAULT NULL,
  `columns84` tinyint(1) DEFAULT NULL,
  `columns85` tinyint(1) DEFAULT NULL,
  `columns86` datetime DEFAULT NULL,
  `columns87` tinyint(1) DEFAULT NULL,
  `columns88` datetime DEFAULT NULL,
  `columns89` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns90` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns91` tinyint(1) DEFAULT NULL,
  `columns92` varchar(6) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns93` tinyint(1) DEFAULT NULL,
  `columns94` tinyint(1) DEFAULT NULL,
  `columns95` tinyint(1) DEFAULT NULL,
  `columns96` tinyint(1) DEFAULT NULL,
  `columns97` tinyint(1) DEFAULT NULL,
  `columns98` tinyint(1) DEFAULT NULL,
  `columns99` tinyint(1) DEFAULT NULL,
  `columns101` tinyint(1) DEFAULT NULL,
  `columns102` tinyint(1) DEFAULT NULL,
  `columns103` tinyint(1) DEFAULT NULL,
  `columns104` tinyint(1) DEFAULT NULL,
  `columns105` tinyint(1) DEFAULT NULL,
  `columns106` datetime DEFAULT NULL,
  `columns107` tinyint(1) DEFAULT NULL,
  `columns108` tinyint(1) DEFAULT NULL,
  `columns109` tinyint(1) DEFAULT NULL,
  `columns110` tinyint(1) DEFAULT NULL,
  `columns111` tinyint(1) DEFAULT NULL,
  `columns112` tinyint(1) DEFAULT NULL,
  `columns113` varchar(6) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns114` tinyint(1) DEFAULT NULL,
  `columns115` int DEFAULT NULL,
  `columns116` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns117` datetime DEFAULT NULL,
  `columns118` tinyint(1) DEFAULT NULL,
  `columns119` datetime DEFAULT NULL,
  `columns120` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns121` datetime DEFAULT NULL,
  `columns122` datetime DEFAULT NULL,
  `columns123` tinyint(1) DEFAULT NULL,
  `columns124` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns125` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns126` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns127` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns128` tinyint(1) DEFAULT NULL,
  `columns129` tinyint(1) DEFAULT NULL,
  `columns130` tinyint(1) DEFAULT NULL,
  `columns131` tinyint(1) DEFAULT NULL,
  `columns132` tinyint(1) DEFAULT NULL,
  `columns133` tinyint(1) DEFAULT NULL,
  `columns134` tinyint(1) DEFAULT NULL,
  `columns135` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns136` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns137` varchar(25) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns138` tinyint(1) DEFAULT NULL,
  `columns139` tinyint(1) DEFAULT NULL,
  `columns140` longtext COLLATE utf8mb4_unicode_ci,
  `columns141` varchar(40) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns142` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns143` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns144` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns145` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns146` datetime DEFAULT NULL,
  `columns147` longtext COLLATE utf8mb4_unicode_ci,
  `columns148` tinyint(1) DEFAULT NULL,
  `columns149` datetime DEFAULT NULL,
  `columns150` datetime DEFAULT NULL,
  `columns151` datetime DEFAULT NULL,
  `columns152` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns153` datetime DEFAULT NULL,
  `columns154` datetime DEFAULT NULL,
  `columns155` longtext COLLATE utf8mb4_unicode_ci,
  `columns156` datetime DEFAULT NULL,
  `columns157` json DEFAULT NULL,
  `columns158` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns159` datetime DEFAULT NULL,
  `columns160` datetime DEFAULT NULL,
  `columns161` longtext COLLATE utf8mb4_unicode_ci,
  `columns162` datetime DEFAULT NULL,
  `columns163` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `columns164` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns165` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns166` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns167` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns168` tinyint(1) DEFAULT NULL,
  `columns169` tinyint(1) NOT NULL DEFAULT '0',
  `columns170` datetime DEFAULT NULL,
  `columns171` tinyint(1) DEFAULT NULL,
  `columns172` tinyint(1) DEFAULT NULL,
  `columns173` datetime DEFAULT NULL,
  `columns174` double DEFAULT NULL,
  `columns175` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns176` tinyint(1) DEFAULT NULL,
  `columns177` datetime DEFAULT NULL,
  `columns178` datetime DEFAULT NULL,
  `columns179` datetime DEFAULT NULL,
  `columns180` tinyint(1) DEFAULT NULL,
  `columns181` tinyint(1) DEFAULT '0',
  `columns182` tinyint(1) DEFAULT '0',
  `columns183` double DEFAULT NULL,
  `columns184` double DEFAULT NULL,
  `columns185` varchar(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns186` int DEFAULT NULL,
  `columns187` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns188` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns189` tinyint(1) DEFAULT NULL,
  `columns190` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns191` char(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns192` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns193` longtext COLLATE utf8mb4_unicode_ci,
  `columns194` longtext COLLATE utf8mb4_unicode_ci,
  `columns195` longtext COLLATE utf8mb4_unicode_ci,
  `columns196` longtext COLLATE utf8mb4_unicode_ci,
  `columns197` longtext COLLATE utf8mb4_unicode_ci,
  `columns198` int DEFAULT NULL,
  `columns199` datetime DEFAULT NULL,
  `columns200` datetime DEFAULT NULL,
  `columns201` datetime DEFAULT NULL,
  `columns202` tinyint(1) DEFAULT NULL,
  `columns203` varchar(120) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns204` double DEFAULT NULL,
  `columns205` tinyint(1) DEFAULT NULL,
  `columns206` char(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns207` char(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns208` char(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns209` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `columns210` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns211` datetime DEFAULT NULL,
  `columns212` datetime DEFAULT NULL,
  `columns213` double DEFAULT NULL,
  `columns214` double DEFAULT NULL,
  `columns215` json DEFAULT NULL,
  `columns216` datetime DEFAULT NULL,
  `columns217` datetime DEFAULT NULL,
  `columns218` datetime DEFAULT NULL,
  `columns219` datetime DEFAULT NULL,
  `columns220` char(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `columns221` datetime DEFAULT NULL,
  `columns222` int DEFAULT NULL,
  `columns223` int DEFAULT NULL,
  `columns224` tinyint(1) DEFAULT NULL,
  `columns225` tinyint(1) DEFAULT NULL,
  `columns226` int DEFAULT NULL,
  `columns227` VARCHAR(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
```

Suggested fix:
Usual "row size too large" fix:
- Reduce the number of columns
- Convert columns to TEXT / BLOB

Looks similar to https://bugs.mysql.com/bug.php?id=120323

Hello,
Have you tried 
`SET innodb_strict_mode=OFF;`
before issuing the CREATE TABLE?

> Have you tried `SET innodb_strict_mode=OFF;` before issuing the CREATE TABLE?

I did not. I saw your message on https://bugs.mysql.com/bug.php?id=120323#c557088 and from what I understand this would allow this statement to pass.

I still think it is valid to revert this patch here. From what I understand it is trading a possible insert-time (runtime) issue with a create-table (compile time) one, which is great in terms of correctness in my point of view. But which breaks previously passing statements in a patch release.

That said, I'm personally okay with both options as using `SET innodb_strict_mode=OFF;` could be seen as a quick fix for this statement(I did not try it yet).

(Mandatory xkcd https://xkcd.com/1172/)