Support Sequences

[dveeden]

What problem does this PR solve?

Issue Number: close #4559

What is changed and how it works?

As MariaDB supports Sequences it would be good if DM allows MariaDB→DM→TiDB for things like CREATE SEQUENCE.

Check List

Tests

• Manual test (add detailed scripts or steps below)

Questions

Will it cause performance regression or break compatibility?

Do you need to update user documentation, design documentation or monitoring documentation?

Release note

Support for `CREATE SEQUENCE`, `ALTER SEQUENCE` and `DROP SEQUENCE` was added. 

[dveeden]

Things that might need work:

• What would be a good way to add unittests or integration tests for this?
• There is a differences for when calling DROP TABLE on a sequence. Should we re-write that in DM?

MariaDB 10.6.11

mysql [localhost:10611] {msandbox} (test) > CREATE SEQUENCE s1;
Query OK, 0 rows affected (0.037 sec)

mysql [localhost:10611] {msandbox} (test) > SHOW FULL TABLES;
+----------------+------------+
| Tables_in_test | Table_type |
+----------------+------------+
| s1             | SEQUENCE   |
+----------------+------------+
1 row in set (0.001 sec)

mysql [localhost:10611] {msandbox} (test) > DROP TABLE IF EXISTS s1;
Query OK, 0 rows affected (0.038 sec)

mysql [localhost:10611] {msandbox} (test) > SHOW FULL TABLES;
Empty set (0.001 sec)

TiDB v7.4.0

sql> CREATE SEQUENCE s1;
Query OK, 0 rows affected (0.1646 sec)

sql> SHOW FULL TABLES;
+----------------+------------+
| Tables_in_test | Table_type |
+----------------+------------+
| s1             | SEQUENCE   |
+----------------+------------+
1 row in set (0.0014 sec)

sql> DROP TABLE IF EXISTS s1;
Query OK, 0 rows affected, 1 warning (0.0015 sec)
Note (code 1051): Unknown table 'test.s1'

sql> SHOW FULL TABLES;
+----------------+------------+
| Tables_in_test | Table_type |
+----------------+------------+
| s1             | SEQUENCE   |
+----------------+------------+
1 row in set (0.0013 sec)

[dveeden]

/cc @lance6716 @mjonss

[lance6716]

/check-issue-triage-complete

[ti-chi-bot[bot]]

@mjonss: adding LGTM is restricted to approvers and reviewers in OWNERS files.

In response to this:

Instructions for interacting with me using PR comments are available here. If you have questions or suggestions related to my behavior, please file an issue against the kubernetes/test-infra repository.

[lance6716]

/cc @lyzx2001

[lance6716]

/cc @GMHDBJD

[ti-chi-bot[bot]]

@lyzx2001: adding LGTM is restricted to approvers and reviewers in OWNERS files.

In response to this:

Instructions for interacting with me using PR comments are available here. If you have questions or suggestions related to my behavior, please file an issue against the kubernetes/test-infra repository.

[lance6716]

Strange that I didn't find ALTER SEQUENCE support in TiDB document https://docs.pingcap.com/tidb/stable/sql-statement-create-sequence . So I think at least we should not allow replicating this type of DDL.

And it will cause a behaviour change, so let PM start a review @frank945946

[dveeden]

Strange that I didn't find ALTER SEQUENCE support in TiDB document https://docs.pingcap.com/tidb/stable/sql-statement-create-sequence . So I think at least we should not allow replicating this type of DDL.

And it will cause a behaviour change, so let PM start a review @Frank945946

This is supported:

sql> CREATE SEQUENCE s1 MINVALUE=2;
Query OK, 0 rows affected (0.1489 sec)

sql> ALTER SEQUENCE s1 MINVALUE=3;
ERROR: 4136 (HY000): Sequence 'test.s1' values are conflicting

sql> ALTER SEQUENCE s1 MINVALUE=1;
Query OK, 0 rows affected (0.1619 sec)

sql> SELECT TIDB_VERSION()\G
*************************** 1. row ***************************
TIDB_VERSION(): Release Version: v7.6.0
Edition: Community
Git Commit Hash: 52794d985ba6325d75a714d4eaa0838d59425eb6
Git Branch: heads/refs/tags/v7.6.0
UTC Build Time: 2024-01-22 14:20:42
GoVersion: go1.21.5
Race Enabled: false
Check Table Before Drop: false
Store: tikv
1 row in set (0.0007 sec)

[ti-chi-bot[bot]]

@AilinKid: adding LGTM is restricted to approvers and reviewers in OWNERS files.

In response to this:

Instructions for interacting with me using PR comments are available here. If you have questions or suggestions related to my behavior, please file an issue against the kubernetes/test-infra repository.

[Frank945946]

As create, alter, and drop operations on sequences have already been supported in TiDB, LGTM supports migrating the sequence event from MariaDB to TiDB by DM. @dveeden @lance6716

[ti-chi-bot[bot]]

[APPROVALNOTIFIER] This PR is APPROVED

This pull-request has been approved by: AilinKid, asddongmen, lance6716, lyzx2001, mjonss

The full list of commands accepted by this bot can be found here.

The pull request process is described here

Needs approval from an approver in each of these files:

• ~~OWNERS~~ [asddongmen,lance6716]

Approvers can indicate their approval by writing /approve in a comment Approvers can cancel approval by writing /approve cancel in a comment

[ti-chi-bot[bot]]

[LGTM Timeline notifier]

Timeline:

• 2023-12-21 02:40:16.648585165 +0000 UTC m=+1101507.685812093: :ballot_box_with_check: agreed by asddongmen.
• 2024-03-06 07:54:02.126506176 +0000 UTC m=+240069.148752564: :ballot_box_with_check: agreed by lance6716.

[lance6716]

/retest

[lance6716]

/rest-required

[lance6716]

/test all

[lance6716]

/test-required

[wuhuizuo]

/test ?

[ti-chi-bot[bot]]

@wuhuizuo: The following commands are available to trigger required jobs:

• /test cdc-integration-kafka-test
• /test cdc-integration-mysql-test
• /test cdc-integration-pulsar-test
• /test cdc-integration-storage-test
• /test dm-compatibility-test
• /test dm-integration-test
• /test engine-integration-test
• /test verify
• /test wip-pull-build
• /test wip-pull-check
• /test wip-pull-unit-test-cdc
• /test wip-pull-unit-test-dm
• /test wip-pull-unit-test-engine

Use /test all to run the following jobs that were automatically triggered:

• pingcap/tiflow/ghpr_verify
• pingcap/tiflow/pull_cdc_integration_kafka_test
• pingcap/tiflow/pull_cdc_integration_pulsar_test
• pingcap/tiflow/pull_cdc_integration_storage_test
• pingcap/tiflow/pull_cdc_integration_test
• pingcap/tiflow/pull_dm_compatibility_test
• pingcap/tiflow/pull_dm_integration_test
• pingcap/tiflow/pull_engine_integration_test

In response to this:

/test ?

Instructions for interacting with me using PR comments are available here. If you have questions or suggestions related to my behavior, please file an issue against the kubernetes/test-infra repository.

[purelind]

/test all

[codecov[bot]]

Codecov Report

Attention: Patch coverage is 0% with 3 lines in your changes are missing coverage. Please review.

Project coverage is 57.4023%. Comparing base (36e9e1b) to head (15e8fda).

Additional details and impacted files

Components	Coverage Δ
cdc	61.7052% <ø> (+0.0182%)	:arrow_up:
dm	51.2295% <0.0000%> (+0.0231%)	:arrow_up:
engine	63.3667% <ø> (-0.0283%)	:arrow_down:

Flag	Coverage Δ
unit	57.4023% <0.0000%> (+0.0146%)	:arrow_up:

Flags with carried forward coverage won't be shown. Click here to find out more.

@@               Coverage Diff                @@
##             master     #10203        +/-   ##
================================================
+ Coverage   57.3877%   57.4023%   +0.0146%     
================================================
  Files           851        851                
  Lines        125227     125230         +3     
================================================
+ Hits          71865      71885        +20     
+ Misses        47967      47953        -14     
+ Partials       5395       5392         -3     

[lance6716]

/retest

[dveeden]

/test dm-integration-test

[dveeden]

/test dm-integration-test

[lance6716]

/retest