Is this repeatable when starting with 10.1.21 or later? I am asking because of MDEV-11623.

Could you please attach a copy of the data directory before step 3?

Provided requested feedback.

I can reproduce this with CS0404325.tar.gz but not with 10.1.48.tar.gz.

alter table pet4 ADD i1 INTEGER, algorithm=instant;

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

The culprit is instant_alter_column_possible(), which returns false here (tested in 10.6):

	if (ha_alter_info->handler_flags

	    & ((INNOBASE_ALTER_REBUILD | INNOBASE_ONLINE_CREATE)

	       & ~ALTER_DROP_STORED_COLUMN

	       & ~ALTER_STORED_COLUMN_ORDER

	       & ~ALTER_ADD_STORED_BASE_COLUMN

	       & ~ALTER_COLUMN_NULLABLE

	       & ~ALTER_OPTIONS)) {

		return false;

	}

The flags are 0x41800000000 and the allowed flags are as follows:

   0x0000555d4ff67710 <+3552>:	movabs $0x2001a980000400,%rax

   0x0000555d4ff6771a <+3562>:	and    0x78(%rcx),%rax

That is, the flag 0x800000000 is not expected:

// Add non-unique, non-primary index

#define ALTER_ADD_NON_UNIQUE_NON_PRIM_INDEX  (1ULL << 35)

That flag was set here:

#0  0x0000555d4f88ee20 in fill_alter_inplace_info (thd=0x7f29b8009558, table=0x7f29b80250f8, varchar=false, ha_alter_info=0x7f29d0068088) at /mariadb/10.6/sql/sql_table.cc:6821

#1  0x0000555d4f887f3d in mysql_alter_table (thd=0x7f29b8009558, new_db=0x7f29b800e158, new_name=0x7f29b800e570, create_info=0x7f29d006a4c8, table_list=0x7f29b801ede0, alter_info=0x7f29d006a3e0, order_num=0, 

    order=0x0, ignore=false, if_exists=false) at /mariadb/10.6/sql/sql_table.cc:10150

#2  0x0000555d4f942e04 in Sql_cmd_alter_table::execute (this=0x7f29b801f5e8, thd=0x7f29b8009558) at /mariadb/10.6/sql/sql_alter.cc:542

#3  0x0000555d4f795753 in mysql_execute_command (thd=0x7f29b8009558, is_called_from_prepared_stmt=false) at /mariadb/10.6/sql/sql_parse.cc:5996

#4  0x0000555d4f7834b0 in mysql_parse (thd=0x7f29b8009558, rawbuf=0x7f29b801ecd0 "alter table pet4 ADD i1 INTEGER, algorithm=instant", length=50, parser_state=0x7f29d006baf8)

    at /mariadb/10.6/sql/sql_parse.cc:8029

The SQL layer appears to ‘think’ that adding a column will require an index to be created, even though it is absolutely not necessary here. I am not at all familiar with the code outside InnoDB, and the .frm file. I do not think that anything changed on the InnoDB side with respect to this.

Sorry, that was wrong flag described in the deleted comment.

Until MDEV-20704 there was (HA_BINARY_PACK_KEY | HA_VAR_LENGTH_KEY) erroneously added to the indexes like in pet4. Since ALTER now sees this flag mismatch between old and newly created frm (compare_keys_but_name()) it denies to do the NOCOPY algorithm.

"ALTER TABLE pet4 FORCE (no algorithm option)" does HA_ALTER_INPLACE_COPY_NO_LOCK which means it rebuilds clustered index (and secondary indexes always/if needed, marko please correct me).

The simplest way to check if the table is affected by MDEV-20704 is to run:

alter table pet4 force, algorithm=nocopy;

if it fails with error

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

then the table is affected by the bug. If it doesn't fail, the table is OK for more NOCOPY operations. Since FORCE is NOCOPY itself it will be fast in any case.

If you want to check the table and don't even rewrite FRM, this is possible with debug build:

SET debug_dbug='+d,alter_table_rollback_new_index';

alter table pet4 force, algorithm=nocopy;

this may result in two errors:

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

ERROR 1105 (HY000): Unknown error

By error code 1845 or 1105 you can filter out tables programmatically.

SET debug_dbug=default;

rob.schwyzer@mariadb.com Hmm, I prepared an answer for `alter_table_rollback_new_index` check and suddenly got an idea that `ALTER TABLE FORCE` can be possible just for `ALGORITHM=NOCOPY`. I did not even suspect that it might not be supported, my apologies.

So, my second part about debug build and `alter_table_rollback_new_index` check is still true. As for non-debug build you may do an instant add and drop column instead:

alter table pet5 add column __test__ int, algorithm=nocopy;

alter table pet5 drop column __test__, algorithm=nocopy;

#1. cannot be done without #3.

According to MDEV-20704 the issue happens with tables created prior 10.1.42, 10.2.28, etc (see Fix Versions) that have index by DOUBLE column. bar should tell if the other types were affected.

#3 is a feature (if it will not be separate binary which fixes FRMs but even in this case I'd abstain from running it for all users) and 10.5 is not the version where we add new features (can be exceptions).

May be we can add ignoring flags which are not supported by an engine (for now I have no ideas how it can be implemeted, maybe special constant in engine to make bit AND with flags written in the frm)...

Since there is no direct knowledge that only DOUBLE is affected (MDEV-20704 was about FIELDFLAG_BLOB sideffect) I'm going to fix it in most generic way:

1. if FRM version is prior MDEV-20704 fix CHECK TABLE fails with error:

test.pet4       check   error   Table rebuild required. Please do "ALTER TABLE `pet4` FORCE" or dump/reload to fix it!

2. mysqlcheck --auto-repair (and mysql_upgrade) when sees such table issues ALTER TABLE FORCE.

Please review bb-10.4-midenok2

OK to push

Did I understand the fix correctly that it actually continues to require such tables to be rebuilt when upgrading the server? Is there any way to avoid unnecessarily rebuilding the data of the table, and to adjust or tolerate old-format .frm files?

I can still reproduce the reported problem with the following test:

--source include/have_innodb.inc

CREATE TABLE pet4 (build_time double(18,7) DEFAULT NULL, KEY idx1 (build_time))

ENGINE=InnoDB;

let $datadir=`select @@datadir`;

FLUSH TABLES;

remove_file $datadir/test/pet4.frm;

copy_file std_data/pet4.frm $datadir/test/pet4.frm;

ALTER TABLE pet4 ADD i1 INTEGER, ALGORITHM=INSTANT;

DROP TABLE pet4;

This is with the pet4.frm from CS0404325.tar.gz:

tar xzf CS0404325.tar.gz  ./CS0404325/pet4.frm

mv CS0404325/pet4.frm mysql-test/std_data/

mysqltest: At line 10: query 'ALTER TABLE pet4 ADD i1 INTEGER, ALGORITHM=INSTANT' failed: 1845: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=INPLACE

I intend to fix this in MDEV-29481. As part of that, the previous ‘fix’ would be reverted.