[MDEV-28760] Row size too large error after multiple drops/adds to an InnoDB table Created: 2022-06-07  Updated: 2022-06-07  Resolved: 2022-06-07

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table, Platform SUSE, Storage Engine - InnoDB
Affects Version/s: 10.4.25, 10.6.8
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Bernd Jänichen Assignee: Marko Mäkelä
Resolution: Not a Bug Votes: 0
Labels: innodb
Environment:

openSUSE Leap 15.3

Attachments: File rowsizeTooBig.sql    
Issue Links:
Relates
relates to MDEV-15562 Instant DROP COLUMN or changing the o... Closed
relates to MDEV-20590 Introduce a file format constraint to... Closed

 Description   

After dropping/adding some columns multiple times to an InnoDB table the error "ERROR 1118 (42000) at line 405: 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" occurs.

This behaviour is inconsistent, at table creation time there is no error when i then drop some of the columns and add these again within multiple ALTER statements the error suddenly occurs.

If i repeat the drop/add statements often enough with "innodb_strict_mode = Off" the error message disappears after some repetition.

I can reproduce it always with attached sql-file.

The issue doesn't exist within MariaDB-10.3.34 but occurs at least in 10.4.25 and 10.6.8.

This message appears within the error-log: "[ERROR] InnoDB: Cannot add field `c_540` in table `test`.`testme` because after adding it, the row size is 8145 which is greater than maximum allowed size (8126 bytes) for a record on index leaf page."

 Comments   
Comment by Marko Mäkelä [ 2022-06-07 ]

Based on the description and a quick look at the rowsizeTooBig.sql, it seems to me that MDEV-15562 (instant DROP COLUMN) is working as designed.

MDEV-20590 introduced a way to disable the instant DROP COLUMN (as well as the MDEV-11369 instant ADD COLUMN) functionality. Instant ADD COLUMN adds some storage overhead as well, but it really is the instant DROP COLUMN that will ‘blow the bank’. Perhaps we should consider changing the default setting?

--echo #Do not allow MDEV-11369 instant ADD / MDEV-15562 instant DROP/reorder
 
--echo #If a table already was in conflicting format, force a rebuild on any subsequent ALTER TABLE.
 
--echo #Coincidentally, IMPORT TABLESPACE should work to any MariaDB 10.x version.
 
SET GLOBAL innodb_instant_alter_column_allowed=never;
 
--echo #Do not allow MDEV-15562 instant DROP/reorder but do allow MDEV-11369
 
--echo #If a table already was in conflicting format, force a rebuild on any subsequent ALTER TABLE.
 
SET GLOBAL innodb_instant_alter_column_allowed=add_last;
 
--echo #Allow any instant ALTER TABLE.
 
SET GLOBAL innodb_instant_alter_column_allowed=add_drop_reorder;

Comment by Bernd Jänichen [ 2022-06-07 ]

Thx, for the hint, that fixes it. I'll close the issue.

Comment by Bernd Jänichen [ 2022-06-07 ]

Oops seems i cannot close it.

Comment by Marko Mäkelä [ 2022-06-07 ]

I verified that rowsizeTooBig.sql will trigger ER_TOO_BIG_ROWSIZE on one of the ALTER TABLE statements unless SET innodb_strict_mode=OFF is in effect.

Everything seems to work as designed. It might make sense to change the default to innodb_instant_alter_column_allowed=add_last. If it is desired, it should be filed as a task, to be changed in a development branch.

Generated at Thu Feb 08 10:03:16 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.