improving repair of tables

This task is to ensure we have a clear definition and rules of how to repair or optimize a table.

The rules are:

• REPAIR should be used with tables that are crashed and are unreadable (hardware issues with not readable blocks, blocks with 'unexpected data' etc)
• OPTIMIZE table should be used to optimize the storage layout for the table (recover space for delete rows and optimize the index structure.
• ALTER TABLE table_name FORCE should be used to rebuild the .frm file (the table definition) and the table (with the original table row format). If the table is from and older MariaDB/MySQL release with a different storage format, it will convert the data to the new format. ALTER TABLE ... FORCE is used as part of mariadb-upgrade.

Here follows some more background:

The 3 ways to repair/fix/optimize a table are:
1) ALTER TABLE table_name FORCE" (not other options).
As an alias we allow: "ALTER TABLE table_name ENGINE=original_engine"
2) "REPAIR TABLE" (without FORCE)
3) "OPTIMIZE TABLE"

All of the above commands will optimize row space usage (which means that space will be needed to hold a temporary copy of the table) and re-generate all indexes. They will also try to replicate the original table definition as exact as possible.

For ALTER TABLE and "REPAIR TABLE without FORCE", the following will hold:
If the table is from an older MariaDB version and data conversion is needed (for example for old type HASH columns, MySQL JSON type or new TIMESTAMP format) "ALTER TABLE table_name FORCE, algorithm=COPY" will be used.

The differences between the algorithms are
1) Will use the fastest algorithm the engine supports to do a full repairof the table (except if data conversions are is needed).
2) Will use the storage engine internal REPAIR facility (MyISAM, Aria). However if there is a data incompatibility noticed (old MariaDB version) then ALTER TABLE ... FORCE, and thus the ALGORITHM=COPY will be used, as REPAIR cannot fix data version incompatibilities. One can use REPAIR ... FORCE to force the internal REPAIR to be used.
If the engine does not support REPAIR then "ALTER TABLE FORCE, ALGORITHM=COPY" will be used.
If there was data incompatibilities (which means that FORCE was used) then there will be a warning after REPAIR that ALTER TABLE FORCE is still needed.
The reason for this is that REPAIR may be able to go around data errors (wrong incompatible data, crashed or unreadable sectors) that
ALTER TABLE cannot do.
3) Will use the storage engine internal OPTIMIZE. If engine does not support optimize, then "ALTER TABLE FORCE" is used.

The above will ensure that ALTER TABLE FORCE is able to correct almost any logical errors in the row or index data. In case of corrupted blocks then REPAIR possible followed by ALTER TABLE is needed.
This is important as mariadb-upgrade executes ALTER TABLE table_name FORCE for any table that must be re-created.

If one specifies an algorithm together with ALTER TABLE FORCE, things will work as before (except if data conversion is required as then the COPY algorithm is enforced).

Here is a detailed list of changes:

• Added new internal return value from ha_check_for_upgrade() HA_ADMIN_NEEDS_DATA_CONVERSION to mark that ALTER TABLE FORCE is needed to fix the table.
• ALTER TABLE will, if ha_check_for_upgrade() returns HA_ADMIN_NEEDS_DATA_CONVERSION, force the COPY algorithm to be used. If another algorithm is specified on the command line there will be an error that says that ALGORITHM=COPY is needed.
  • This is needed as only ALGORITHM=COPY can fix errors related to HA_ADMIN_NEEDS_DATA_CONVERSION.
• If the version of the .frm table is very old ( <= version 10) then we will not update the .frm version number in the .frm file without a full frm rebuild, as this can lead to
  newer MariaDB versions not being able to read the .frm file.
  *Repair will not update .frm version if ha_check_for_upgrade() shows that the table is requiring a rebuild.
• handler::check_long_hash_compatibility() now returns HA_ADMIN_NEEDS_DATA_CONVERSION if table is old.
• Added a new flag to be used with ALTER TABLE: recreate_identical_table, which tells the storage engine that we want to create a table with the identical table structure as it had before. This fixes some bugs in InnoDB, see list below.
• This flag is set in the following cases:
  • • ALTER TABLE xxx engine=original_engine (old way to optimize a table without any other options)
    • ALTER TABLE xxx FORCE (no other options)
    • OPTIMIZE TABLE for engines that does not have an internal optimize procedure and SPECIAL_NO_NEW_FUNC is set
    • REPAIR TABLE, in the case ha_check_for_upgrade() tells us that an ALTER TABLE is required and FORCE is not used.
• REPAIR TABLE FORCE was added to allow one to run the internal REPAIR TABLE even if ha_check_for_upgrade() says that a ALTER TABLE FORCE is preferable (this is mostly for testing and should never have to be used if mariadb-upgrade was properly run).

The rule that we should keep the original table row format when using ALTER TABLE ... FORCE, fixes the following bugs in InnoDB when using ALTER TABLE ... FORCE

• No error for INNODB_DEFAULT_ROW_FORMAT=COMPACT even if row length
  would be too wide. (Independent of innodb_strict_mode).
• Tables using "InnoDB symlinks" will be continue to be symlinked.
  (Independent of the setting of --symbolic-links)