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

Some ALTER TABLE .. fail when they worked before, and with a wrong error message

    XMLWordPrintable

Details

    Description

      CREATE TABLE t (a INT);
      		 
      INSERT INTO t VALUES (1),(2);
      		 
      ALTER TABLE t ALGORITHM=NOCOPY, ORDER BY a;
      		 
       
      		 
      DROP TABLE t;

      ALTER here can have actual operations before ALGORITHM, I just over-simplified it to allow ALTER be executed multiple times without intermediate changes.

      In the above example, before MDEV-16329 ALTER works with ALGORITHM=NOCOPY and ALGORITHM=INSTANT (and of course with COPY and DEFAULT), while fails with ALGORITHM=INPLACE:

      11.2 2867894ac6ca23fc2f82cfad2dd510351f597325

      		 
      MariaDB [test]> ALTER TABLE t ALGORITHM=NOCOPY, ORDER BY a;
      		 
      Query OK, 2 rows affected (0.044 sec)              
      Records: 2  Duplicates: 0  Warnings: 0
      		 
       
      		 
      MariaDB [test]> ALTER TABLE t ALGORITHM=INPLACE, ORDER BY a;
      		 
      ERROR 1845 (0A000): ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
      		 
      MariaDB [test]> 
      MariaDB [test]> ALTER TABLE t ALGORITHM=INSTANT, ORDER BY a;
      		 
      Query OK, 2 rows affected (0.056 sec)              
      Records: 2  Duplicates: 0  Warnings: 0

      This is in itself weird, and may be a bug.

      After MDEV-16329 the behavior changed. If the above was indeed a bug, then functionally the change may be correct, but it leaves us with a wrong error message:

      bb-11.2-oalter adcf5dfa8d646b9fbd2bb1a7a4f5a3bb8a7e659c

      		 
      MariaDB [test]> ALTER TABLE t ALGORITHM=NOCOPY, ORDER BY a;
      		 
      ERROR 1845 (0A000): ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
      		 
      MariaDB [test]> 
      MariaDB [test]> ALTER TABLE t ALGORITHM=INPLACE, ORDER BY a;
      		 
      ERROR 1845 (0A000): ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
      		 
      MariaDB [test]> 
      MariaDB [test]> ALTER TABLE t ALGORITHM=INSTANT, ORDER BY a;
      		 
      ERROR 1845 (0A000): ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY

      Regardless which algorithm is specified, it complains about INPLACE.

      Or, if the old behavior was valid and on whatever reason INPLACE was indeed not applicable, but NOCOPY and INSTANT were, then the new behavior is functionally a regression.

      Attachments

        Issue Links

          is caused by

          Task - A task that needs to be done. MDEV-16329 Engine-independent online ALTER TABLE

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30906 Variable alter_algorithm=COPY takes precedence over explicit ALGORITHM clause

          • Major - Major loss of function.
          • Stalled

          Activity

            People

              nikitamalyavin Nikita Malyavin
              elenst Elena Stepanova
              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.