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

Support ALGORITHM=/LOCK= syntax for CREATE/DROP INDEX

Details

    Description

      I used MariaDB as alternative to a MySQL-Server. For a software-package I have to make updates for an existing database with prepared sql-files.

      After an update from mariadb 5 to mariadb 10.1 can use ALGRITHM in CREATE INDEX - queries. But the DROP INDEX - queries (DROP INDEX name ON tbl ALGORITHM = INPLACE LOCK = NONE;) dies with following Message:

      ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ALGORITHM = INPLACE LOCK = NONE' at line 3;

      The message is the same errormessage, which I have in mariaDB 5 for my CREATE INDEX ..., ALGORITHM = INPLACE, LOCK = NONE;. So it seems, that the documented ALGORITHM-Function isn't implemented yet?

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          alice Alice Sherepa added a comment -

          Confirmed with a testcase below :

          MariaDB [test]> select version();
          		 
          +-----------------+
          		 
          | version()       |
          		 
          +-----------------+
          		 
          | 10.1.22-MariaDB |
          		 
          +-----------------+
          		 
          1 row in set (0.00 sec)
          		 
           
          		 
          MariaDB [test]> CREATE TABLE `aa`(
          		 
              -> `a` int NOT NULL
          		 
              -> ) ENGINE=InnoDB;
          		 
          Query OK, 0 rows affected (0.31 sec)
          		 
           
          		 
          MariaDB [test]> INSERT INTO aa VALUES(1),(2),(3);
          		 
          Query OK, 3 rows affected (0.06 sec)
          		 
          Records: 3  Duplicates: 0  Warnings: 0
          		 
           
          		 
          MariaDB [test]> 
          MariaDB [test]> CREATE INDEX name ON aa(a) ALGORITHM = INPLACE lock=none;
          		 
          Query OK, 0 rows affected (0.50 sec)
          		 
          Records: 0  Duplicates: 0  Warnings: 0
          		 
           
          		 
          MariaDB [test]> Drop INDEX `name` ON aa  ALGORITHM = INPLACE lock=none;
          		 
          ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ALGORITHM = INPLACE lock=none' at line 1

          alice Alice Sherepa added a comment - Confirmed with a testcase below : MariaDB [test]> select version(); + -----------------+ | version() | + -----------------+ | 10.1.22-MariaDB | + -----------------+ 1 row in set (0.00 sec)   MariaDB [test]> CREATE TABLE `aa`( -> `a` int NOT NULL -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.31 sec)   MariaDB [test]> INSERT INTO aa VALUES (1),(2),(3); Query OK, 3 rows affected (0.06 sec) Records: 3 Duplicates: 0 Warnings: 0   MariaDB [test]> MariaDB [test]> CREATE INDEX name ON aa(a) ALGORITHM = INPLACE lock=none; Query OK, 0 rows affected (0.50 sec) Records: 0 Duplicates: 0 Warnings: 0   MariaDB [test]> Drop INDEX ` name ` ON aa ALGORITHM = INPLACE lock=none; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ALGORITHM = INPLACE lock=none' at line 1
          serg Sergei Golubchik added a comment - - edited

          We cannot add new syntax in 10.0 or any other GA version, see https://mariadb.com/kb/en/mariadb/release-criteria/

          So, I've moved this to 10.3. Meanwhile, I've corrected the manual, apparently this syntax was never supported.

          As a workaround you can use ALTER TABLE ... DROP INDEX

          serg Sergei Golubchik added a comment - - edited We cannot add new syntax in 10.0 or any other GA version, see https://mariadb.com/kb/en/mariadb/release-criteria/ So, I've moved this to 10.3. Meanwhile, I've corrected the manual, apparently this syntax was never supported. As a workaround you can use ALTER TABLE ... DROP INDEX
          marostegui Manuel Arostegui added a comment -

          Any chances to get this fixed for 10.6? It's been a few years already.

          marostegui Manuel Arostegui added a comment - Any chances to get this fixed for 10.6? It's been a few years already.

          People

            nikitamalyavin Nikita Malyavin
            cyper Andreas Neumann
            Votes:
            0 Vote for this issue
            Watchers:
            10 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.