[MDEV-18583] Avoid copying when changing the type of an indexed column Created: 2019-02-14  Updated: 2019-03-20  Resolved: 2019-02-20

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table, Storage Engine - InnoDB
Affects Version/s: 10.4.3
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Marko Mäkelä Assignee: Eugene Kosov (Inactive)
Resolution: Duplicate Votes: 0
Labels: instant, types

Issue Links:
Duplicate
duplicates MDEV-17301 Change of COLLATE unnecessarily requi... Closed

 Description   

It was the intention of MDEV-15564 to allow ALGORITHM=NOCOPY for changing the character set or collation of an indexed column. Currently, such operations will degrade to ALGORITHM=COPY:

Please see the test innodb.instant_alter_charset. We would expect results like

ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: ADD INDEX. Try ALGORITHM=NOCOPY

but instead, we currently get the following:

ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY

I believe that this is due to the following in Field_str::is_equal() and Field_varstring::is_equal():

  if (!Type_handler::Charsets_are_compatible(field_charset, new_field->charset,
 
					     part_of_a_key))
 
    return IS_EQUAL_NO;

Maybe we need variants of IS_EQUAL_PACK_LENGTH and IS_EQUAL_PACK_LENGTH_EXT that indicate that the column is indexed, and therefore the SQL layer must request the indexes to be dropped and added.

 Comments   
Comment by Eugene Kosov (Inactive) [ 2019-02-18 ]

Issue description is a bit incorrect. It's possible to change charset of an indexed field, e.g.:

create table t (
 
  a varchar(50) charset ascii,
 
  unique key a_key (a)
 
) engine=innodb;
 
 
 
alter table t modify a varchar(50) charset utf8mb4, algorithm=instant;
 
 
 
drop table t;

What requires copy is key part tests like this one:

create table key_part_change_and_rename (
 
  a char(100) charset ascii,
 
  b char(100) charset ascii,
 
  unique key ab (a,b)
 
) engine=innodb;
 
 
 
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
 
alter table key_part_change_and_rename
 
  change a b char(100) charset utf8mb4,
 
  change b a char(100) charset utf8mb4,
 
  drop index ab,
 
  add unique key ab(a,b),
 
  algorithm=instant;
 
 
 
drop table key_part_change_and_rename;

This requires copy because CHAR can't be changed instantly. When I change type to VARCHAR it requires NOCOPY which is fine.

I think there is nothing to do with that issue at all. What do you think marko?

Comment by Marko Mäkelä [ 2019-02-20 ]

kevg, thanks for the observation.
When it comes to avoiding rebuild on CHAR, there is the related task MDEV-18584.

Do we already have test cases that cover the instantaneous change of VARCHAR, followed by possible rebuild of the indexes that depend on the columns? If not, then such tests should be added.

Related question, possibly to be answered by bar: Do we always have to rebuild indexes when changing from utf8mb3 to utf8mb4? Are there any utf8mb4 collations that are guaranteed to sort utf8mb3 data in the same way as an some particular utf8mb3 collation? For example, it would seem to me that we could instantly convert from utf8mb3_bin or utf8_bin to utf8mb4_bin.

Comment by Marko Mäkelä [ 2019-02-20 ]

This is a duplicate of MDEV-17301.

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