Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-21261

When performing instant add column, InnoDB ignores innodb_strict_mode for "row size too large" check

    XMLWordPrintable

    Details

      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

            Activity

              People

              Assignee:
              kevg Eugene Kosov
              Reporter:
              GeoffMontee Geoff Montee
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated: