InnoDB does the following check for sequence table during check
table command:
- There should be only one index should exist on sequence table
- There should be only one row should exist on sequence table
- The leaf page must be the root page for the sequence table
- Delete marked record should not exist
- DB_TRX_ID and DB_ROLL_PTR of the record should be 0 and 1U << 55
The check go through the following steps:
1. Run check on the underlying engine. If not ok, then return.
2. Check that there's only one row in the table, and
2.1 warn if more than one row
2.2 return HA_ADMIN_CORRUPT if fewer than one row (i.e. 0 rows)
3. If the sequence is not initialised (e.g. after an ALTER TABLE ...
SEQUENCE=1), initialise the sequence by reading the sequence
metadata from the table. This will also flush the next_free_value,
i.e. set it to the next not cached value (SEQUENCE::reserved_until)
4. Check that the sequence metadata is valid, i.e. nothing out of
order e.g. minvalue < maxvalue etc. If invalid it reports
HA_ERR_SEQUENCE_INVALID_DATA
5. Check that the sequence has not been exhausted. It reports
ER_SEQUENCE_RUN_OUT as a warning if and only if a SELECT NEXTVAL
would do so
Limitations:
1. The check is independent of flags, so the vanilla check is the same
as CHECK ... EXTENDED or CHECK ... FOR UPGRADE etc.
2. When the check discovers invalid metadata from the table,
subsequent SELECT NEXTVAL will carry on (or fail) without this
piece of knowledge, independent of the CHECK. This is to ensure
consistency, i.e. CHECK does not modify behaviour of SELECT, and if
anything it makes more sense that SELECT reports
HA_ERR_SEQUENCE_INVALID_DATA in this case, regardless of prior
CHECK
