[MDEV-33449] improving repair of tables - Jira

Details

Type: New Feature
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Fix Version/s: 11.5.1
Component/s: Data Definition - Alter Table
Labels:
- Preview_11.5

Description

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)

Attachments

Issue Links

blocks

MDEV-32188 make TIMESTAMP use whole 32-bit unsigned range

Closed

MDEV-34182 simplify the REPAIR TABLE usage

Open

causes

MDEV-33826 Assertion `tl->table == __null' failed in THD::open_temporary_table

Closed

MDEV-33955 Concurrent DDL on InnoDB table and REPAIR on InnoDB stats table leads to "table not found" error

Open

MDEV-34096 REPAIR sometimes de-optimizes an InnoDB table

Open

MDEV-34134 Assertion failure in online_alter_log_row or server hang upon replicating REPAIR on mysql.gtid_slave_pos

Open

MDEV-35820 REPAIR does not fix encrypted partitioned Aria table after upgrade from 10.4

Open

MDEV-35933 repair removes wrong line for sequence with engine InnoDB

Open

includes

MDEV-33655 Remove alter_algorithm

Closed

relates to

MDEV-32869 SELECT EXTENDED for the recovery of corrupted data

Open

MDEV-32188 make TIMESTAMP use whole 32-bit unsigned range

Closed

(3 causes, 1 includes, 2 relates to)

Activity

Ascending order - Click to sort in descending order

Marko Mäkelä added a comment - 2024-02-13 06:28

In addition to the mentioned 3 destructive ways of repairing a table (ALTER TABLE, REPAIR TABLE, OPTIMIZE TABLE), there is a 4th way that involves copying data from the corrupted table to another table by CREATE TABLE…SELECT or INSERT…SELECT. Unlike the destructive repair variants, this allows for some experimentation.

It could be the case that not the contents of the table but the transaction metadata is corrupted. In such cases, as noted in MDEV-32869, it could be helpful if SELECT could deliver more information of the transaction metadata so that the DBA can make informed decisions on which records are valid and should be preserved.

Marko Mäkelä added a comment - 2024-02-13 06:28 In addition to the mentioned 3 destructive ways of repairing a table ( ALTER TABLE , REPAIR TABLE , OPTIMIZE TABLE ), there is a 4th way that involves copying data from the corrupted table to another table by CREATE TABLE…SELECT or INSERT…SELECT . Unlike the destructive repair variants, this allows for some experimentation. It could be the case that not the contents of the table but the transaction metadata is corrupted. In such cases, as noted in MDEV-32869 , it could be helpful if SELECT could deliver more information of the transaction metadata so that the DBA can make informed decisions on which records are valid and should be preserved.

Elena Stepanova added a comment - 2024-02-16 13:47 - edited

The description doesn't explain what was changed, it mostly talks about "how things should be". It may be good for a KB article or a blog post, not so much as a commit comment / task description.

But even in this capacity, there is a lack of clarity here. For example, it says, apparently referring to "2) "REPAIR TABLE" (without FORCE)" listed earlier:

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.

It says twice "if <something>, then ALTER TABLE FORCE will be used", but it doesn't say will be used by whom/what.
Does it mean that REPAIR will automatically (internally) trigger ALTER TABLE FORCE? Or does it mean that a user is supposed to use ALTER TABLE FORCE if REPAIR is not supported or data incompatibility noticed?

Here is a practical example (it is only one random example, it is given to demonstrate the general lack of clarity above, not to be fixed in isolation):

On a 5.5 server, I create an InnoDB table as

create table t4 (pk int auto_increment primary key, a int, b int, key(b));

insert into t4 values (1,2,3);

Then I start bb-11.4-timestamp branch on this directory and run mysql_upgrade. The upgrade runs smoothly and doesn't say anything special about the table:

test.t4                                            OK

Then I go to check the table. It says

MariaDB [test]> check table t4 extended;

+---------+-------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table   | Op    | Msg_type | Msg_text                                                                                                                                                                                                                            |

+---------+-------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| test.t4 | check | Warning  | InnoDB: Clustered index record with stale history in table `test`.`t4`: COMPACT RECORD(info_bits=0, 5 fields): {[4]    (0x80000001),[6]      (0x000000000B01),[7]    3  (0x82000001330110),[4]    (0x80000002),[4]    (0x80000003)} |

| test.t4 | check | status   | OK                                                                                                                                                                                                                                  |

+---------+-------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

2 rows in set (0.001 sec)

So, which situation from the task description does it reflect and what should be applied here? If it's something that must be solved with ALTER TABLE .. FORCE, then it appears from the description that mysql_upgrade should have taken care of that, but it doesn't (maybe because CHECK TABLE .. FOR UPGRADE shows nothing here).
If it's something that is meant to be fixed by REPAIR TABLE (with or without FORCE), it doesn't work, because InnoDB doesn't support REPAIR, neither does it trigger ALTER TABLE FORCE:

MariaDB [test]> repair table t4;

+---------+--------+----------+---------------------------------------------------------+

| Table   | Op     | Msg_type | Msg_text                                                |

+---------+--------+----------+---------------------------------------------------------+

| test.t4 | repair | note     | The storage engine for the table doesn't support repair |

+---------+--------+----------+---------------------------------------------------------+

1 row in set (0.001 sec)

MariaDB [test]> repair table t4 force;

+---------+--------+----------+---------------------------------------------------------+

| Table   | Op     | Msg_type | Msg_text                                                |

+---------+--------+----------+---------------------------------------------------------+

| test.t4 | repair | note     | The storage engine for the table doesn't support repair |

+---------+--------+----------+---------------------------------------------------------+

1 row in set (0.002 sec)

MariaDB [test]> check table t4 extended;

+---------+-------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table   | Op    | Msg_type | Msg_text                                                                                                                                                                                                                            |

+---------+-------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| test.t4 | check | Warning  | InnoDB: Clustered index record with stale history in table `test`.`t4`: COMPACT RECORD(info_bits=0, 5 fields): {[4]    (0x80000001),[6]      (0x000000000B01),[7]    3  (0x82000001330110),[4]    (0x80000002),[4]    (0x80000003)} |

| test.t4 | check | status   | OK                                                                                                                                                                                                                                  |

+---------+-------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

2 rows in set (0.002 sec)

It can be fixed by triggering ALTER TABLE FORCE manually (or by OPTIMIZE, which is also not supported, but unlike REPAIR, it triggers table recreation). But I don't see why it should be so from the description.

Elena Stepanova added a comment - 2024-02-16 13:47 - edited The description doesn't explain what was changed, it mostly talks about "how things should be". It may be good for a KB article or a blog post, not so much as a commit comment / task description. But even in this capacity, there is a lack of clarity here. For example, it says, apparently referring to "2) "REPAIR TABLE" (without FORCE)" listed earlier: 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. It says twice "if <something>, then ALTER TABLE FORCE will be used", but it doesn't say will be used by whom/what. Does it mean that REPAIR will automatically (internally) trigger ALTER TABLE FORCE? Or does it mean that a user is supposed to use ALTER TABLE FORCE if REPAIR is not supported or data incompatibility noticed? Here is a practical example ( it is only one random example, it is given to demonstrate the general lack of clarity above, not to be fixed in isolation ): On a 5.5 server, I create an InnoDB table as create table t4 (pk int auto_increment primary key , a int , b int , key (b)); insert into t4 values (1,2,3); Then I start bb-11.4-timestamp branch on this directory and run mysql_upgrade. The upgrade runs smoothly and doesn't say anything special about the table: test.t4 OK Then I go to check the table. It says MariaDB [test]> check table t4 extended; + ---------+-------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | + ---------+-------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | test.t4 | check | Warning | InnoDB: Clustered index record with stale history in table `test`.`t4`: COMPACT RECORD(info_bits=0, 5 fields): {[4] (0x80000001),[6] (0x000000000B01),[7] 3 (0x82000001330110),[4] (0x80000002),[4] (0x80000003)} | | test.t4 | check | status | OK | + ---------+-------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.001 sec) So, which situation from the task description does it reflect and what should be applied here? If it's something that must be solved with ALTER TABLE .. FORCE, then it appears from the description that mysql_upgrade should have taken care of that, but it doesn't (maybe because CHECK TABLE .. FOR UPGRADE shows nothing here). If it's something that is meant to be fixed by REPAIR TABLE (with or without FORCE), it doesn't work, because InnoDB doesn't support REPAIR, neither does it trigger ALTER TABLE FORCE : MariaDB [test]> repair table t4; + ---------+--------+----------+---------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | + ---------+--------+----------+---------------------------------------------------------+ | test.t4 | repair | note | The storage engine for the table doesn 't support repair | +---------+--------+----------+---------------------------------------------------------+ 1 row in set (0.001 sec) MariaDB [test]> repair table t4 force; +---------+--------+----------+---------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------+--------+----------+---------------------------------------------------------+ | test.t4 | repair | note | The storage engine for the table doesn' t support repair | + ---------+--------+----------+---------------------------------------------------------+ 1 row in set (0.002 sec) MariaDB [test]> check table t4 extended; + ---------+-------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | + ---------+-------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | test.t4 | check | Warning | InnoDB: Clustered index record with stale history in table `test`.`t4`: COMPACT RECORD(info_bits=0, 5 fields): {[4] (0x80000001),[6] (0x000000000B01),[7] 3 (0x82000001330110),[4] (0x80000002),[4] (0x80000003)} | | test.t4 | check | status | OK | + ---------+-------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.002 sec) It can be fixed by triggering ALTER TABLE FORCE manually (or by OPTIMIZE, which is also not supported, but unlike REPAIR, it triggers table recreation). But I don't see why it should be so from the description.

Sergei Golubchik added a comment - 2024-03-23 13:12

to test this feature separately please use bb-11.5-~~MDEV-32188~~-timestamps

Sergei Golubchik added a comment - 2024-03-23 13:12 to test this feature separately please use bb-11.5- MDEV-32188 -timestamps

Elena Stepanova added a comment - 2024-05-07 12:35

As of bb-11.5-MDEV-32188-timestamps 65b4a991a

The introduction of the new syntax REPAIR .. FORCE remains unjustified in my opinion.

From the user perspective, I can't think of a realistic use case when a user would want to repair a breakage only if it can be done by internal engine means or only if it can be done on the server layer. If a user runs REPAIR TABLE, they want it repaired, and the server should run necessary levels of repair automatically. Also, the modifier FORCE is misleading, as FORCE usually implies "everything that would be done otherwise plus possibly something else", but that's not the case here at all – most often FORCE means that less will be done than would be without FORCE (e.g. for engines which don't support REPAIR); or, at most, something different will be done with FORCE.

The current version of the task description, however, suggests that FORCE is introduced for testing purposes. It leaves less room for counter-argument as for testing purposes it doesn't have to be very logical from the user perspective; but I don't know what testing purposes could justify introduction of a new user-facing syntax. Maybe it should have been a debug variable instead, or something alike.

These reservations aside, if the task description is to be taken for granted, I have no objections against releasing this development with 11.5.

There are some open issues (MDEV-33955, MDEV-34096) but I don't consider them an obstacle for the feature release.

The testing (or more precisely the bug recognition) related to REPAIR is impaired by problems existing in older versions. An example of a regression which could have easily escaped detection was ~~MDEV-33826~~, which was masked by a very similarly looking bug MDEV-23207. There may be some other issues which remain hidden like that and will only be discovered either by luck or when the old ones are fixed.

Since the feature is a pre-requisite for ~~MDEV-32188~~ and resides in the same branch, it cannot be pushed into main just yet, but should better wait for ~~MDEV-32188~~ (which I expect will be signed off for 11.5 as well).

Elena Stepanova added a comment - 2024-05-07 12:35 As of bb-11.5-MDEV-32188-timestamps 65b4a991a The introduction of the new syntax REPAIR .. FORCE remains unjustified in my opinion. From the user perspective, I can't think of a realistic use case when a user would want to repair a breakage only if it can be done by internal engine means or only if it can be done on the server layer. If a user runs REPAIR TABLE , they want it repaired, and the server should run necessary levels of repair automatically. Also, the modifier FORCE is misleading, as FORCE usually implies "everything that would be done otherwise plus possibly something else", but that's not the case here at all – most often FORCE means that less will be done than would be without FORCE (e.g. for engines which don't support REPAIR); or, at most, something different will be done with FORCE . The current version of the task description, however, suggests that FORCE is introduced for testing purposes. It leaves less room for counter-argument as for testing purposes it doesn't have to be very logical from the user perspective; but I don't know what testing purposes could justify introduction of a new user-facing syntax. Maybe it should have been a debug variable instead, or something alike. These reservations aside, if the task description is to be taken for granted, I have no objections against releasing this development with 11.5. There are some open issues ( MDEV-33955 , MDEV-34096 ) but I don't consider them an obstacle for the feature release. The testing (or more precisely the bug recognition) related to REPAIR is impaired by problems existing in older versions. An example of a regression which could have easily escaped detection was MDEV-33826 , which was masked by a very similarly looking bug MDEV-23207 . There may be some other issues which remain hidden like that and will only be discovered either by luck or when the old ones are fixed. Since the feature is a pre-requisite for MDEV-32188 and resides in the same branch, it cannot be pushed into main just yet, but should better wait for MDEV-32188 (which I expect will be signed off for 11.5 as well).

MariaDB Server

improving repair of tables

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration