[MDEV-21261] When performing instant add column, InnoDB ignores innodb_strict_mode for "row size too large" check - Jira

XML

Word

Printable

Details

Type: Bug
Status: Confirmed (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.3.20, 10.4.10
Fix Version/s: 10.4(EOL)
Component/s: Data Definition - Alter Table, Storage Engine - InnoDB
Labels:
None

Description

Currently, when InnoDB performs an instant add column operation, it appears to ignore innodb_strict_mode for the "row size too large" check.

For example, let's say that we start with the following table:

CREATE OR REPLACE TABLE tab (

   col1 varchar(40) NOT NULL,

   col2 varchar(40) NOT NULL,

   col3 varchar(40) NOT NULL,

   col4 varchar(40) NOT NULL,

   col5 varchar(40) NOT NULL,

   col6 varchar(40) NOT NULL,

   col7 varchar(40) NOT NULL,

   col8 varchar(40) NOT NULL,

   col9 varchar(40) NOT NULL,

   col10 varchar(40) NOT NULL,

   col11 varchar(40) NOT NULL,

   col12 varchar(40) NOT NULL,

   col13 varchar(40) NOT NULL,

   col14 varchar(40) NOT NULL,

   col15 varchar(40) NOT NULL,

   col16 varchar(40) NOT NULL,

   col17 varchar(40) NOT NULL,

   col18 varchar(40) NOT NULL,

   col19 varchar(40) NOT NULL,

   col20 varchar(40) NOT NULL,

   col21 varchar(40) NOT NULL,

   col22 varchar(40) NOT NULL,

   col23 varchar(40) NOT NULL,

   col24 varchar(40) NOT NULL,

   col25 varchar(40) NOT NULL,

   col26 varchar(40) NOT NULL,

   col27 varchar(40) NOT NULL,

   col28 varchar(40) NOT NULL,

   col29 varchar(40) NOT NULL,

   col30 varchar(40) NOT NULL,

   col31 varchar(40) NOT NULL,

   col32 varchar(40) NOT NULL,

   col33 varchar(40) NOT NULL,

   col34 varchar(40) NOT NULL,

   col35 varchar(40) NOT NULL,

   col36 varchar(40) NOT NULL,

   col37 varchar(40) NOT NULL,

   col38 varchar(40) NOT NULL,

   col39 varchar(40) NOT NULL,

   col40 varchar(40) NOT NULL,

   col41 varchar(40) NOT NULL,

   col42 varchar(40) NOT NULL,

   col43 varchar(40) NOT NULL,

   col44 varchar(40) NOT NULL,

   col45 varchar(40) NOT NULL,

   col46 varchar(40) NOT NULL,

   col47 varchar(40) NOT NULL,

   col48 varchar(40) NOT NULL,

   col49 varchar(40) NOT NULL,

   col50 varchar(40) NOT NULL,

   PRIMARY KEY (col1)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Now let's say that we add one more column, while using the FORCE keyword, so that instant add column is avoided:

SET SESSION innodb_strict_mode=ON;

ALTER TABLE tab ADD COLUMN col51 varchar(40) NOT NULL,

   FORCE;

In this case, we do see a "row size too large" error:

MariaDB [db1]> ALTER TABLE tab ADD COLUMN col51 varchar(40) NOT NULL,

    ->    FORCE;

ERROR 1118 (42000): 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

But let's say that we leave out the FORCE keyword instead, so that the column is added instantly:

SET SESSION innodb_strict_mode=ON;

ALTER TABLE tab ADD COLUMN col51 varchar(40) NOT NULL;

In this case, we do not see the "row size too large" message as an error. Instead, we see it as a warning:

MariaDB [db1]> ALTER TABLE tab ADD COLUMN col51 varchar(40) NOT NULL;

Query OK, 0 rows affected, 1 warning (0.004 sec)

Records: 0  Duplicates: 0  Warnings: 1

MariaDB [db1]> SHOW WARNINGS;

+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------+

| Level   | Code | Message                                                                                                                                      |

+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------+

| Warning |  139 | 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. |

+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.000 sec)

InnoDB seems to be ignoring innodb_strict_mode in this case.

Attachments

Issue Links

relates to

MDEV-19292 "Row size too large" error when creating table with lots columns when row format is DYNAMIC or COMPRESSED

Closed

MDEV-20194 Warnings inconsistently issued upon CHECK on table from older versions

Closed

MDEV-20400 Implement a way to query an InnoDB table's max row size

Open

MDEV-20832 Don't print "row size too large" warnings in error log if innodb_strict_mode=OFF and log_warnings<=2

Closed

MDEV-20949 Stop performing unnecessary maximum row size check for DML

Closed

MDEV-21429 TRUNCATE and OPTIMIZE are being refused due to "row size too large"

Closed

MDEV-22585 Incorrect note being written to the error log when CREATE and ALTER fail due to Row Size Too Large and in all cases for TRUNCATE and OPTIMIZE TABLE.

Open

MDEV-23521 Implement an InnoDB row format that can use overflow pages for smaller columns

Open

(3 relates to)

Activity

People

Assignee:: Vladislav Lesin

Reporter:: Geoff Montee (Inactive)

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2019-12-09 18:49

Updated:: 2023-04-27 13:53

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.