[MDEV-32668] tables with UNIQUE blob columns cannot be alter_algorithm=INSTANT modified Created: 2023-11-03  Updated: 2023-11-03

Status: Open
Project: MariaDB Server
Component/s: Data Definition - Alter Table
Affects Version/s: 10.4.31
Fix Version/s: 10.4, 10.6, 10.11

Type: Bug Priority: Major
Reporter: Daniel Black Assignee: Nikita Malyavin
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Blocks
blocks MDEV-17468 Avoid table rebuild on operations on ... Stalled
Problem/Incident
is caused by MDEV-371 Unique indexes for blobs Closed
Relates
relates to MDEV-11369 Instant add column for InnoDB Closed
relates to MDEV-14341 Allow LOCK=NONE in table-rebuilding A... Open
relates to MDEV-15562 Instant DROP COLUMN or changing the o... Closed
relates to MDEV-18790 Server crash in fields_in_hash_keyinf... Closed
relates to MDEV-20918 INSTANT algorithm doesn't work with u... Open

 Description    

create table test (t text,  unique index i(t));
 
LTER TABLE test  ADD COLUMN test INT NULL AFTER t;
 
ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: INPLACE ADD or DROP of virtual columns cannot be combined with other ALTER TABLE actions. Try ALGORITHM=COPY

While the unique index i doesn't take place in the later, it does prevent it from instantly happening.

Early in the alter_table processing compare_keys_but_name compares the existing table key and the table keys in the new structure, and determines they are different.

This is because:

  • the table_key got treated by re_setup_keyinfo_hash and had HA_NOSAME stripped from the table_key->flags.
  • comparing further down in the same function, the comparsion of parts fails as the new_part->field and fields aren't populated.

This results in the ALTER TABLE considering the index to be different, and not allowing the ALGORITHM=INSTANT later.

 Comments   
Comment by Marko Mäkelä [ 2023-11-03 ]

An instant ADD or DROP column (MDEV-11369, MDEV-15562) will create a new InnoDB table metadata object (dict_table_t). The logic that we imported from MySQL 5.7 for handling operations on virtual columns in InnoDB native ALTER TABLE is of rather low quality. This is why these rather artificial restrictions exist.

This could possibly be fixed by fixing MDEV-14341.

Comment by Marko Mäkelä [ 2023-11-03 ]

Sorry, it looks like fixing MDEV-17468 is what would fix exactly this.

Comment by Daniel Black [ 2023-11-03 ]

It gets ha_alter_info->handler_flags flags ALTER_ADD_UNIQUE_INDEX and ALTER_ADD_STORED_BASE_COLUMN before it even reaches ha_innobase::check_if_supported_inplace_alter, so will have a flags != 0 even after the TODO: uncomment the flags below is lifted.

Comment by Daniel Black [ 2023-11-03 ]

reordering dependency for this to block MDEV-17468 - right alter info is required before InnoDB can succeed.

Generated at Thu Feb 08 10:33:05 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.