Provide a way to export the (non-cached) SEQUENCE state via SQL for backup and restore

[kennytm]

Feature Request

Is your feature request related to a problem? Please describe:

In TiDB a sequence's (non-cached) state is determined by the sequence value (SID) and cycle count (SequenceCycle). While the SID can be retrieved using the SHOW TABLE NEXT_ROW_ID query:

MySQL [test]> SHOW TABLE seq NEXT_ROW_ID;
+---------+------------+-------------+--------------------+----------------+
| DB_NAME | TABLE_NAME | COLUMN_NAME | NEXT_GLOBAL_ROW_ID | ID_TYPE        |
+---------+------------+-------------+--------------------+----------------+
| test    | seq        | _tidb_rowid |                  1 | AUTO_INCREMENT |
| test    | seq        |             |               1001 | SEQUENCE       |
+---------+------------+-------------+--------------------+----------------+

the SequenceCycle can only be retrieved via internal API (the meta package), which is not possible via the SQL protocol.

See pingcap/br#242 (comment) for why the cycle count is important.

Describe the feature you'd like:

Provide a way to expose the cycle count. (Please make a decision.) Either simply extend SHOW TABLE NEXT_ROW_ID:

MySQL [test]> SHOW TABLE seq NEXT_ROW_ID;
+---------+------------+-------------+--------------------+----------------+
| DB_NAME | TABLE_NAME | COLUMN_NAME | NEXT_GLOBAL_ROW_ID | ID_TYPE        |
+---------+------------+-------------+--------------------+----------------+
| test    | seq        |             |               1001 | SEQUENCE       |
| test    | seq        |             |                  0 | SEQUENCE_CYCLE |
+---------+------------+-------------+--------------------+----------------+

or copy MariaDB's behavior to support SELECT-ing a sequence:

MySQL [test]> SELECT * FROM seq;
+-----------------------+---------------+---------------+-------------+-----------+------------+--------------+-------------+
| next_not_cached_value | minimum_value | maximum_value | start_value | increment | cache_size | cycle_option | cycle_count |
+-----------------------+---------------+---------------+-------------+-----------+------------+--------------+-------------+
|                  1001 |             1 |        999999 |           2 |         2 |       1000 |            1 |           0 |
+-----------------------+---------------+---------------+-------------+-----------+------------+--------------+-------------+

(Treating SEQUENCE as TABLE is MariaDB-specific behavior and does not work on PostgreSQL nor MS SQL Server.)

Describe alternatives you've considered:

Teachability, Documentation, Adoption, Migration Strategy:

[zz-jason]

The user story and feature request LGTM. While the SQL syntax and implementation details still need more investigation and discussion.

@bb7133 What's your opinion on the SQL syntax and implementation methods?

[zz-jason]

I prefer to follow the MariaDB behavior since the sequence feature and SQL syntax are more likely to be MariaDB compatible.