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

Incorrect NULL value handling for instantly dropped BLOB columns

Details

    Description

      I run the following test case, in which the INSERT statement should populate the value. However, it failed and returns an error, indicating the dropped column seems to still exist.

      CREATE TABLE t0 (c1 INT, c2 BLOB) ROW_FORMAT=REDUNDANT;
      		 
      ALTER TABLE t0 DROP c2;
      		 
      INSERT INTO t0 (c1) VALUES (0); -- [42000][1118] (conn=10) Row size too large (> 8123). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

      Attachments

        Issue Links

          is caused by

          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

          Activity

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

            I can confirm the bug. It only seems to affect ROW_FORMAT=REDUNDANT, which is the name that was given to the original InnoDB format when I introduced a new default format in MySQL 5.0.3, called ROW_FORMAT=COMPACT. Starting with MySQL 5.7 and MariaDB Server 10.2, there is innodb_default_row_format, with the default value dynamic.

            By default, DROP COLUMN is not rebuilding the table, but only marking the column as hidden. This was implemented in MDEV-15562. The error looks bogus, because the INSERT should be able to replace the hidden column value with an empty or NULL value. In ROW_FORMAT=REDUNDANT, NULL values for fixed-length columns reserve space for the fixed length (say, an INT that is NULL would occupy 4 bytes plus the null bit). I assume that there is a bug in how BLOB that are NULL are being handled for instantly dropped columns.

            Here is a variation of test case with a work-around that allows the table to be rebuilt and hence the INSERT to succeed:

            --source include/have_innodb.inc
            		 
            CREATE TABLE t0 (c1 INT, c2 BLOB) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
            		 
            SET GLOBAL innodb_instant_alter_column_allowed=add_last;
            		 
            ALTER TABLE t0 DROP c2;
            		 
            INSERT INTO t0 SET c1=0;

            marko Marko Mäkelä added a comment - I can confirm the bug. It only seems to affect ROW_FORMAT=REDUNDANT , which is the name that was given to the original InnoDB format when I introduced a new default format in MySQL 5.0.3, called ROW_FORMAT=COMPACT . Starting with MySQL 5.7 and MariaDB Server 10.2, there is innodb_default_row_format , with the default value dynamic . By default, DROP COLUMN is not rebuilding the table, but only marking the column as hidden. This was implemented in MDEV-15562 . The error looks bogus, because the INSERT should be able to replace the hidden column value with an empty or NULL value. In ROW_FORMAT=REDUNDANT , NULL values for fixed-length columns reserve space for the fixed length (say, an INT that is NULL would occupy 4 bytes plus the null bit). I assume that there is a bug in how BLOB that are NULL are being handled for instantly dropped columns. Here is a variation of test case with a work-around that allows the table to be rebuilt and hence the INSERT to succeed: --source include/have_innodb.inc CREATE TABLE t0 (c1 INT , c2 BLOB) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; SET GLOBAL innodb_instant_alter_column_allowed=add_last; ALTER TABLE t0 DROP c2; INSERT INTO t0 SET c1=0;
            John Jove John Jove added a comment -

            Thanks for the detailed explanation. I change the issue title accordingly.

            John Jove John Jove added a comment - Thanks for the detailed explanation. I change the issue title accordingly.

            People

              thiru Thirunarayanan Balathandayuthapani
              John Jove John Jove
              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.