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

Row size too large error after multiple drops/adds to an InnoDB table

Details

    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."

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-15562 Instant DROP COLUMN or changing the order of columns

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Task - A task that needs to be done. MDEV-20590 Introduce a file format constraint to ALTER TABLE

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            marko Marko Mäkelä added a comment -

            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;

            marko Marko Mäkelä added a comment - 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;
            bjaenichen Bernd Jänichen added a comment - - edited

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

            bjaenichen Bernd Jänichen added a comment - - edited Thx, for the hint, that fixes it. I'll close the issue.
            bjaenichen Bernd Jänichen added a comment -

            Oops seems i cannot close it.

            bjaenichen Bernd Jänichen added a comment - Oops seems i cannot close it.
            marko Marko Mäkelä added a comment -

            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.

            marko Marko Mäkelä added a comment - 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.

            People

              marko Marko Mäkelä
              bjaenichen Bernd Jänichen
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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