[MDEV-12572] Support ALGORITHM=/LOCK= syntax for CREATE/DROP INDEX Created: 2017-04-24  Updated: 2023-11-30

Status: Confirmed
Project: MariaDB Server
Component/s: Parser
Fix Version/s: None

Type: New Feature Priority: Major
Reporter: Andreas Neumann Assignee: Alexey Botchkov
Resolution: Unresolved Votes: 0
Labels: algorithm, drop-index, innodb, lock


 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?

 Comments   
Comment by Alice Sherepa [ 2017-04-26 ]

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

Comment by Sergei Golubchik [ 2017-05-12 ]

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

Generated at Thu Feb 08 07:58:45 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.