Technically, MDEV-371 implemented unique keys on hash indexes by creating a hidden virtual column and an index on it.

MariaDB Server 10.2 introduced indexed virtual columns (MDEV-5800), which included copying the InnoDB implementation from MySQL 5.7.

I believe that a table rebuild could technically be avoided if the hidden virtual column were created in a separate step and then the index be created on that column. The InnoDB in neither MySQL 5.7 nor MariaDB support the creation of an indexed virtual column in one go; it has to go via the inefficient copy_data_between_tables operation (ALGORITHM=COPY). InnoDB can create an index on a virtual column. There are further limitations related to a table rebuild (see MDEV-14341).

In this case a table rebuild is absolutely unnecessary; we’d only need to drop the incorrect index and the virtual column, followed by adding a correct virtual column and an index on it.

A source code comment in handler::check_long_hash_compatibility() mentions a sufficient but unnecessary measure:

int handler::check_long_hash_compatibility() const

{

  if (!table->s->old_long_hash_function())

    return 0;

  KEY *key= table->key_info;

  KEY *key_end= key + table->s->keys;

  for ( ; key < key_end; key++)

  {

    if (key->algorithm == HA_KEY_ALG_LONG_HASH)

    {

      /*

        The old (pre-MDEV-27653)  hash function was wrong.

        So the long hash unique constraint can have some

        duplicate records. REPAIR TABLE can't fix this,

        it will fail on a duplicate key error.

        Only "ALTER IGNORE TABLE .. FORCE" can fix this.

        So we need to return HA_ADMIN_NEEDS_ALTER here,

        (not HA_ADMIN_NEEDS_UPGRADE which is used elsewhere),

        to properly send the error message text corresponding

        to ER_TABLE_NEEDS_REBUILD (rather than to ER_TABLE_NEEDS_UPGRADE)

        to the user.

      */

      return HA_ADMIN_NEEDS_ALTER;

    }

  }

  return 0;

}

Yes, executing ALTER IGNORE TABLE…FORCE might fix this, but it would unnecessarily rebuild the table. If I understand correctly, the IGNORE keyword may cause some rows with duplicate keys to be filtered out when the table is being rebuilt. The IGNORE keyword will also preclude the use of anything else than ALGORITHM=COPY for ENGINE=InnoDB, because InnoDB can’t guarantee that it would return the same result; duplicate values in indexes could be flagged for different rows due to processing one index at a time, instead of inserting records in all indexes one row at a time.

In my opinion it could have been a better decision to require user intervention, to check which tables are affected, instead of running a risk that some records will be silently deleted on an upgrade. Something along the following lines might have worked:

1. Upgrade is refused if affected tables exist. The DBA is asked to run a generated script before upgrading, comprising a sequence of ALTER TABLE…DROP INDEX statements.
2. After upgrade, the DBA should run another generated script that would create the hidden virtual columns and the indexes on them. (Creating a unique hash index in one go probably requires an unnecessary table rebuild in InnoDB, as I noted in my previous comment.)
3. If any duplicate key errors are reported, the DBA should review the correct course of action, for example, how to merge the records for which duplicates exist, or which rows to delete.

Technically, it's not a bug. The behavior is very much intentional. So this is more a feature request about avoiding very slow upgrades.

I agree with marko, that automatically running lossy ALTER IGNORE TABLE could be not the best approach and perhaps an explicit user interaction should be required. The table could be marked corrupted and unusable, and a DBA will need to fix it manually after the upgrade. Old behavior can be achieved with running mariadb-check after the upgrade.

Can the community make changes to skip this kind of table rebuild during minor version upgrade if it's caused by a bug from community ?

I guess, we can add a switch that disables all table rebuilds on upgrade. So that mariadb-upgrade would only upgrade system tables, views, and print a report of what tables needs to be repaired.

If a table rebuilt is required, can the community make changes to only rebuild the problematic part, not the whole table during the minor version upgrade(in this case, the hashing index on TEXT type unique key)?

Perhaps we can use inplace alter API for that and ask InnoDB to drop/add an index inplace. marko?

Like I wrote in my first comment, InnoDB can create an index on a virtual column without rebuilding the table, but not at the same time with adding a virtual column. Assuming that no duplicates exist, the index could be rebuilt by issuing 3 separate ALTER TABLE operations: dropping the virtual column, adding the virtual column, and creating an index. Because the underlying virtual column is a hidden one, I am not sure if any actual ALTER TABLE syntax is usable for this. As far as I understand, there is no SQL syntax for the type of virtual column expression that is needed here.

Just to clarify: Running mariadb-upgrade isn't mandatory on minor version upgrades, right? So anyone who wants minimal downtime should just not run it to begin with in conjunction with minor version upgrades.

https://lists.mariadb.org/hyperkitty/list/developers@lists.mariadb.org/message/2PLV3VH36R5ZNBJ23E5OGWAT25PKNMB7/
https://mariadb.com/kb/en/downgrading-between-major-versions-of-mariadb/