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

Table from older version requires table rebuild when adding column to table with multi-column index

Details

    Description

      How to reproduce:

      On a 10.4 server instance do:

      CREATE TABLE t1 (
      		 
        id int not null primary key,
      		 
        msg varchar(10),
      		 
        index(id, msg)
      		 
      ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

      Then upgrade to 10.5 and try to instant-add a column:

      alter table t1 add column i int, algorithm=instant;

      This fails with: 

      ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=INPLACE

      Same table created on 10.5 right away allows to add the column in instant mode just fine.

      Attachments

        Issue Links

          is blocked by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29481 mariadb-upgrade prints confusing statement

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Task - A task that needs to be done. MDEV-29672 Add MTR tests covering key and key segment flags and types

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

          Task - A task that needs to be done. MDEV-20760 Add Type_handler::KEY_pack_flags()

          • Major - Major loss of function.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-20704 An index on a double column erroneously uses prefix compression

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-20760 Add Type_handler::KEY_pack_flags()

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28727 ALTER TABLE ALGORITHM=NOCOPY does not work after upgrade

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29481 mariadb-upgrade prints confusing statement

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Task - A task that needs to be done. MDEV-29495 Generalize can_convert_xxx() hook engine API to cover any arbitrary data type

          • Major - Major loss of function.
          • Closed

          Activity

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

            hholzgra, thank you for the report. This resembles MDEV-28727, which is about an upgrade from 10.1. I suspect that the problem lies outside InnoDB also in this case, but I do not see yet how.

            To speed up analysis, could you please attach a minimal 10.4 data directory with which the ALTER TABLE statement would fail?

            Did you try specifying different collations for the column msg? From the InnoDB point of view it should make no difference, but for some presumably incorrect check in the SQL layer it might. It is the SQL layer that produces the operation flags for InnoDB. If an operation flag is set that is not supported in an instant operation by InnoDB, then an error will be reported, even if that flag were set unnecessarily.

            marko Marko Mäkelä added a comment - hholzgra , thank you for the report. This resembles MDEV-28727 , which is about an upgrade from 10.1. I suspect that the problem lies outside InnoDB also in this case, but I do not see yet how. To speed up analysis, could you please attach a minimal 10.4 data directory with which the ALTER TABLE statement would fail? Did you try specifying different collations for the column msg ? From the InnoDB point of view it should make no difference, but for some presumably incorrect check in the SQL layer it might. It is the SQL layer that produces the operation flags for InnoDB. If an operation flag is set that is not supported in an instant operation by InnoDB, then an error will be reported, even if that flag were set unnecessarily.
            marko Marko Mäkelä added a comment -

            A private comment mentioned HA_PACK_KEY, which I see was added MDEV-20760 (10.5.0).

            marko Marko Mäkelä added a comment - A private comment mentioned HA_PACK_KEY , which I see was added MDEV-20760 (10.5.0).
            hholzgra Hartmut Holzgraefe added a comment -

            > Did you try specifying different collations for the column msg?

            I didn't try using different collations, but I did try both with default charset, and with explicit latin1 or utf8 in the CREATE statement.

            > To speed up analysis, could you please attach a minimal 10.4 data directory with which the ALTER TABLE statement would fail?

            attaching in a minute

            hholzgra Hartmut Holzgraefe added a comment - > Did you try specifying different collations for the column msg? I didn't try using different collations, but I did try both with default charset, and with explicit latin1 or utf8 in the CREATE statement. > To speed up analysis, could you please attach a minimal 10.4 data directory with which the ALTER TABLE statement would fail? attaching in a minute

            People

              bar Alexander Barkov
              hholzgra Hartmut Holzgraefe
              Votes:
              1 Vote for this issue
              Watchers:
              7 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.