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

Variable alter_algorithm=COPY takes precedence over explicit ALGORITHM clause

Details

    Description

      ALTER TABLE with explicit unsupported algorithm is still executed when alter_algorithm variable is set to COPY. Apparently, the explicit algorithm is quietly ignored.

      10.4 99ee200b

      MariaDB [test]> create table t1 (a int);
      Query OK, 0 rows affected (0.039 sec)
       
      MariaDB [test]> alter table t1 force, algorithm=instant;
      ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=INPLACE
      MariaDB [test]> set alter_algorithm= copy;
      Query OK, 0 rows affected (0.000 sec)
       
      MariaDB [test]> alter table t1 force, algorithm=instant;
      Query OK, 0 rows affected (0.123 sec)              
      Records: 0  Duplicates: 0  Warnings: 0
      

      It contradicts the variable documentation, as well as common sense.

      Noticeably, it's not the universal rule "variable takes precedence over the clause", apparently it is specific to the COPY algorithm. It doesn't happen with alter_algorithm=INPLACE which is also supported for the operation:

      MariaDB [test]> set alter_algorithm= inplace;
      Query OK, 0 rows affected (0.000 sec)
       
      MariaDB [test]> alter table t1 force, algorithm=instant;
      ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=INPLACE
      

      Even if was at some point intentional (maybe some legacy reasons), I think it should be made uniform and consistent. I'm not sure however that it can be done post-GA, so I'm setting fix version to the next RC. Please adjust if needed.

      MTR

      create table t1 (a int);
       
      set alter_algorithm= DEFAULT;
       
      --error ER_ALTER_OPERATION_NOT_SUPPORTED
      alter table t1 force, algorithm=instant;
       
      set alter_algorithm= INPLACE;
       
      --error ER_ALTER_OPERATION_NOT_SUPPORTED
      alter table t1 force, algorithm=instant;
       
      set alter_algorithm= COPY;
       
      --error ER_ALTER_OPERATION_NOT_SUPPORTED
      alter table t1 force, algorithm=instant;
       
      # Cleanup
      drop table t1;
      

      mysqltest: At line 16: query 'alter table t1 force, algorithm=instant' succeeded - should have failed with errno 1845...
      

      Attachments

        Issue Links

          Activity

            Will likely be fixed as part of MDEV-30984:
            the new patch causes problems with main.mysql57_virtual:

            # We can't do online changes, as the MariaDB storage is incompatible with MySQL
            --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
            alter online table mysql57_virtual comment "I am now a MariaDB table", algorithm=nocopy;
            

            nikitamalyavin Nikita Malyavin added a comment - Will likely be fixed as part of MDEV-30984 : the new patch causes problems with main.mysql57_virtual: # We can't do online changes, as the MariaDB storage is incompatible with MySQL --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON alter online table mysql57_virtual comment "I am now a MariaDB table" , algorithm=nocopy;
            elenst Elena Stepanova added a comment - - edited

            For a note to interested parties, fixing this might potentially cause a problem with OM => NS replication, as always when something that was allowed before becomes prohibited.

            Still, I think it is an obvious error which needs to be fixed, and I don't see how it can be done without potentially breaking replication. Hopefully not so many users have it set to a non-default value and still use an explicit ALGORITHM clause in their ALTERs.

            Of course, it concerns having alter-algorithm as a startup option. If the variable is changed dynamically, like in the test case, it will cause replication problems anyway, as many dynamic variables do.

            elenst Elena Stepanova added a comment - - edited For a note to interested parties, fixing this might potentially cause a problem with OM => NS replication, as always when something that was allowed before becomes prohibited. Still, I think it is an obvious error which needs to be fixed, and I don't see how it can be done without potentially breaking replication. Hopefully not so many users have it set to a non-default value and still use an explicit ALGORITHM clause in their ALTERs. Of course, it concerns having alter-algorithm as a startup option. If the variable is changed dynamically, like in the test case, it will cause replication problems anyway, as many dynamic variables do.

            People

              nikitamalyavin Nikita Malyavin
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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