Bug #121566 Out of memory when executing ALTER TABLE XXX MODIFY COLUMN XXXX

• Submitted: 21 Oct 6:01
• Reporter: Antoni Baran
• Status: Open
• Category: MySQL Server: InnoDB storage engine
• Severity: S1 (Critical)
• Version: 9.7.2
• OS: Linux
• CPU Architecture: Any

[21 Oct 6:01] Antoni Baran

Description:
The problematic below operation works without problems on MySQL 8.4.11 db but crashes on MySQL 9.7.2 with the same settings as MySQL 8.4.11

Memory setting: tried out with RAM 3.5 GB and RAM 26 GB same result

Tried out on MySQL 9.7.2 also with the following settings: temptable_max_ram = 128 MB, same result

How to repeat:
Step by Step

- have a db with the following table definition
CREATE TABLE `TEST` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `ONE` int NOT NULL,
  `TWO` int DEFAULT NULL,
  `THREE` int DEFAULT NULL,
  `FOUR` int DEFAULT NULL,
  `FIVE` int NOT NULL,
  `SIX` int DEFAULT NULL,
  `SEVEN` int DEFAULT NULL,
  `EIGHT` datetime NOT NULL,
  `NINE` int NOT NULL,
  `TEN` datetime NOT NULL,
  `ELEVEN` tinyint NOT NULL DEFAULT '0',
  `TWELVE` int NOT NULL DEFAULT '0',
  `THERTEEN` datetime NOT NULL,
  `FOURTEEN` varchar(200) DEFAULT NULL,
  `FIVETEEN` int DEFAULT NULL,
  `SIXTEEN` tinyint NOT NULL DEFAULT '0',
  `SEVENTEEN` decimal(12,1) DEFAULT NULL,
  `EIGHTEEN` int DEFAULT NULL,
  `NINETEEN` int NOT NULL DEFAULT '0',
  `TWENTY` int DEFAULT NULL,
  `TWENTY_ONE` int DEFAULT NULL,
  `TWENTY_TWO` int NOT NULL DEFAULT '0',
  `TWENTY_THREE` int DEFAULT NULL,
  `TWENTY_FOUR` datetime DEFAULT NULL,
  `TWENTY_FIVE` varchar(2100) DEFAULT NULL,
  `TWENTY_SIX` date DEFAULT NULL,
  `TWENTY_SEVEN` int DEFAULT NULL,
  `TWENTY_EIGHT` int DEFAULT NULL,
  PRIMARY KEY (`ID`,`ONE`),
) ENGINE=InnoDB AUTO_INCREMENT=565856 DEFAULT CHARSET=utf8mb3;

- create around 27000000 records in the above TEST table

- execute the following statement ALTER TABLE TEST MODIFY COLUMN SEVENTEEN DECIMAL(12,2) NULL;

Result: DB crashed due to out of memory

Suggested fix:
The db like in MySQL 8.4.11 must not crash