[MDEV-28822] Table from older version requires table rebuild when adding column to table with multi-column index Created: 2022-06-13  Updated: 2023-01-26  Resolved: 2022-10-26

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table
Affects Version/s: 10.5.16
Fix Version/s: 10.5.18, 10.6.11, 10.7.7, 10.8.6, 10.9.4, 10.10.2

Type: Bug Priority: Critical
Reporter: Hartmut Holzgraefe Assignee: Alexander Barkov
Resolution: Fixed Votes: 1
Labels: regression-10.5

Attachments: File data-10.4.tar.gz    
Issue Links:
Blocks
is blocked by MDEV-29481 mariadb-upgrade prints confusing stat... Closed
is blocked by MDEV-29672 Add MTR tests covering key and key se... Closed
Problem/Incident
is caused by MDEV-20760 Add Type_handler::KEY_pack_flags() Closed
Relates
relates to MDEV-20704 An index on a double column erroneous... Closed
relates to MDEV-20760 Add Type_handler::KEY_pack_flags() Closed
relates to MDEV-28727 ALTER TABLE ALGORITHM=NOCOPY does not... Closed
relates to MDEV-29481 mariadb-upgrade prints confusing stat... Closed
relates to MDEV-29495 Generalize can_convert_xxx() hook eng... Closed

 Description   

How to reproduce:

On a 10.4 server instance do:

CREATE TABLE t1 (
 
  id int not null primary key,
 
  msg varchar(10),
 
  index(id, msg)
 
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

Then upgrade to 10.5 and try to instant-add a column:

alter table t1 add column i int, algorithm=instant;

This fails with: 

ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=INPLACE

Same table created on 10.5 right away allows to add the column in instant mode just fine.

 Comments   
Comment by Marko Mäkelä [ 2022-06-14 ]

hholzgra, thank you for the report. This resembles MDEV-28727, which is about an upgrade from 10.1. I suspect that the problem lies outside InnoDB also in this case, but I do not see yet how.

To speed up analysis, could you please attach a minimal 10.4 data directory with which the ALTER TABLE statement would fail?

Did you try specifying different collations for the column msg? From the InnoDB point of view it should make no difference, but for some presumably incorrect check in the SQL layer it might. It is the SQL layer that produces the operation flags for InnoDB. If an operation flag is set that is not supported in an instant operation by InnoDB, then an error will be reported, even if that flag were set unnecessarily.

Comment by Marko Mäkelä [ 2022-06-14 ]

A private comment mentioned HA_PACK_KEY, which I see was added MDEV-20760 (10.5.0).

Comment by Hartmut Holzgraefe [ 2022-06-14 ]

> Did you try specifying different collations for the column msg?

I didn't try using different collations, but I did try both with default charset, and with explicit latin1 or utf8 in the CREATE statement.

> To speed up analysis, could you please attach a minimal 10.4 data directory with which the ALTER TABLE statement would fail?

attaching in a minute

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