[MDEV-7318] RENAME INDEX Created: 2014-12-14  Updated: 2021-08-23  Resolved: 2020-03-03

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table, Parser
Fix Version/s: 10.5.2

Type: Task Priority: Major
Reporter: James Briggs Assignee: Aleksey Midenkov
Resolution: Fixed Votes: 6
Labels: Compatibility, compat57, index

Issue Links:
Duplicate
duplicates MDEV-18196 ALTER TABLE RENAME INDEX Closed
Problem/Incident
causes MDEV-23356 InnoDB: Failing assertion: field->col... Closed
causes MDEV-25555 Server crashes in tree_record_pos aft... Closed
Relates
relates to MDEV-13301 Optimize DROP INDEX, ADD INDEX into R... Closed
relates to MDEV-16290 ALTER TABLE ... RENAME COLUMN syntax Closed
relates to MDEV-21889 IF EXISTS clause does not work for RE... Closed
relates to MDEV-25803 Inplace ALTER breaks MyISAM/Aria tabl... Closed

 Description   

1) MySQL 5.7 has RENAME INDEX, MariaDB should, too.

http://dev.mysql.com/doc/refman/5.7/en/alter-table.html
"RENAME INDEX old_index_name TO new_index_name" renames an index.

2) My permute-index tool creates synthetic index names that need to be renamed later for esthetic reasons (ie. idx_jb_007.)

 Comments   
Comment by Marko Mäkelä [ 2017-09-18 ]

For what it is worth, I would always have liked ALTER TABLE…RENAME COLUMN syntax when I implemented the InnoDB counterparts of MySQL WL#5534 (ALTER TABLE…ALGORITHM=INPLACE) in MySQL 5.6. Currently the way to rename columns is error-prone, because you will have to specify the old type and ⟦NOT⟧ NULL of the column. Make a subtle mistake, and you will be punished with ALGORITHM=COPY that will convert the column data type.

MySQL 5.7 introduced RENAME INDEX syntax, but no RENAME COLUMN.

That said, I think that it should be technically possible to rename an index by specifying DROP INDEX old_name, ADD INDEX new_name(old_index_column). In practice, InnoDB would unnecessarily drop and create an index, even though only the name is changing.

Comment by Michael Xu [ 2017-11-15 ]

This is a really useful feature, please consider RENAME CHECK as well.

Comment by Matthias Dieter Wallnöfer [ 2017-11-15 ]

MySQL 8.0 has got ALTER TABLE…RENAME COLUMN. So I think it is definitely worth to bring it also to MariaDB due to the reasons cited by @marko and in the MySQL Worklog Task WL#10761.

Although not a standard instruction it is also supported by Oracle and PostgreSQL.

Comment by Artem Russakovskii [ 2019-09-18 ]

MDEV-13301 says renaming indexes was added in 10.4.4.

Should this ticket be marked as fixed too then?

Comment by Sergei Golubchik [ 2019-09-19 ]

No, the description of MDEV-13301 is a bit misleading.

After MDEV-13301 the server can automatically detect if a pair of DROP INDEX, ADD INDEX simply recreate an index under a different name without changing index definition. In this case internally MariaDB will rename the index without rebuilding it.

But there is still no explicit ALTER TABLE ... RENAME INDEX command, so this MDEV-7318 is still valid.

Comment by Matthias Dieter Wallnöfer [ 2019-10-11 ]

Issue for ALTER TABLE...RENAME COLUMN, would be nice if also this feature could make it into release 10.5 like the other one.

Comment by Marko Mäkelä [ 2019-10-14 ]

Already in MySQL 5.6 or MariaDB 10.0 you could rename columns, but only by using the CHANGE COLUMN syntax, which requires the full column specification. If you accidentally changed the column specification in some way (such as omitting NOT NULL, or specifying the wrong length for a character column), the operation would involve more than just renaming the column. Already back in summer or autumn 2012 when I was implementing the InnoDB part of ALTER TABLE operations in MySQL 5.6, I argued that the RENAME COLUMN syntax should be supported.

RENAME INDEX was introduced in MySQL 5.7 and RENAME COLUMN in MySQL 8.0.

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