sync-diff-inspector: add annotation for create table info

Leavrth commented 2 years ago

Signed-off-by: Leavrth jianjun.liao@outlook.com

What problem does this PR solve?

Issue Number: close #678

What is changed and how it works?

for version < 5, regard primary key as nonclustered. so we can add annotation for create table info, if there is no annotation about clustered.

Check List

Tests

Unit test
Manual test (add detailed scripts or steps below)
No code

$ ./sync_diff_inspector --config config1.toml
debug: show table `db_test`.`t`'s create table info:
CREATE TABLE `t` (
  `i` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`i`) /*T![clustered_index] NONCLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=30001/*!90000 SHARD_ROW_ID_BITS=2 */
debug: show table `db_test`.`t0`'s create table info:
CREATE TABLE `t0` (
  `id` bigint(20) NOT NULL,
  `term` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] NONCLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin/*!90000 SHARD_ROW_ID_BITS=2 */
debug: show table `db_test`.`t1`'s create table info:
CREATE TABLE `t1` (
  `id` bigint(20) NOT NULL,
  `term` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] NONCLUSTERED */,
  KEY `term` (`term`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin/*!90000 SHARD_ROW_ID_BITS=2 */
A total of 3 tables need to be compared

debug: show table `db_test`.`t`'s create table info:
CREATE TABLE `t` (
  `i` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`i`) /*T![clustered_index] NONCLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=30001/*!90000 SHARD_ROW_ID_BITS=
Progress [>------------------------------------------------------------] 0% 0/0
debug: show table `db_test`.`t1`'s create table info:
CREATE TABLE `t1` (
  `id` bigint(20) NOT NULL,
  `term` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] NONCLUSTERED */,
  KEY `term` (`term`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin/*!90000 SHARD_ROW_ID_BITS=2 */
debug: show table `db_test`.`t0`'s create table info:
CREATE TABLE `t0` (
  `id` bigint(20) NOT NULL,
  `term` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] NONCLUSTERED */
Comparing the table structure of ``db_test`.`t`` ... equivalent
Comparing the table structure of ``db_test`.`t1`` ... equivalent
Comparing the table structure of ``db_test`.`t0`` ... equivalent
Comparing the table data of ``db_test`.`t1`` ... equivalent
Comparing the table data of ``db_test`.`t`` ... equivalent
Comparing the table data of ``db_test`.`t0`` ... equivalent
_____________________________________________________________________________
Progress [============================================================>] 100% 0/0
A total of 3 table have been compared and all are equal.
You can view the comparision details through '/tmp/sync-diff.output.2022-09-23T09.50.11+0800/sync_diff.log'

mysql> show create table db_test.t;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                  |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `i` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=30001/*!90000 SHARD_ROW_ID_BITS=2 */ |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> show create table db_test.t0;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                        |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t0    | CREATE TABLE `t0` (
  `id` bigint(20) NOT NULL,
  `term` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin/*!90000 SHARD_ROW_ID_BITS=2 */ |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)                                                                                                                                                                                                         

mysql> show create table db_test.t1;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                               |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` bigint(20) NOT NULL,
  `term` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `term` (`term`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin/*!90000 SHARD_ROW_ID_BITS=2 */ |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

ti-chi-bot commented 2 years ago

[REVIEW NOTIFICATION]

This pull request has been approved by:

3pointer

To complete the pull request process, please ask the reviewers in the list to review by filling /cc @reviewer in the comment. After your PR has acquired the required number of LGTMs, you can assign this pull request to the committer in the list by filling /assign @committer in the comment to help you merge this pull request.

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

Reviewer can indicate their review by submitting an approval review. Reviewer can cancel approval by submitting a request changes review.

Leavrth commented 2 years ago

table's pkishandle is true

mysql> select _tidb_rowid from db_test.t1 limit 1;
ERROR 1054 (42S22): Unknown column '_tidb_rowid' in 'field list'

$ ./sync_diff_inspector --config config1.toml
debug: show table `db_test`.`t1`'s create table info:
CREATE TABLE `t1` (
  `id` bigint(20) NOT NULL,
  `term` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
A total of 1 tables need to be compared

debug: show table `db_test`.`t1`'s create table info:
CREATE TABLE `t1` (
  `id` bigint(20) NOT NULL,
  `term` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
Comparing the table structure of ``db_test`.`t1`` ... equivalent
Comparing the table data of ``db_test`.`t1`` ... equivalent
_____________________________________________________________________________
Progress [============================================================>] 100% 0/0
A total of 1 table have been compared and all are equal.
You can view the comparision details through '/tmp/sync-diff.output.2022-09-23T12.36.47+0800/sync_diff.log'

table's pkishandle is false


mysql> select _tidb_rowid from db_test.t1 limit 0;
Empty set (0.00 sec)

mysql> select _tidb_rowid from db_test.t0 limit 0; Empty set (0.00 sec)

$ ./sync_diff_inspector --config config1.toml debug: show table db_test.t0's create table info: CREATE TABLE t0 ( id bigint(20) NOT NULL, term varchar(100) DEFAULT NULL, PRIMARY KEY (id) /T![clustered_index] NONCLUSTERED / ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin/!90000 SHARD_ROW_ID_BITS=2 /

debug: show table db_test.t1's create table info: CREATE TABLE t1 ( id bigint(20) NOT NULL, term varchar(100) DEFAULT NULL, PRIMARY KEY (id) /T![clustered_index] NONCLUSTERED / ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

A total of 2 tables need to be compared

debug: show table db_test.t1's create table info: CREATE TABLE t1 ( id bigint(20) NOT NULL, term varchar(100) DEFAULT NULL, PRIMARY KEY (id) /T![clustered_index] NONCLUSTERED / ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin Progress [>------------------------------------------------------------] 0% 0/0 debug: show table db_test.t0's create table info: CREATE TABLE t0 ( id bigint(20) NOT NULL, term varchar(100) DEFAULT NULL, PRIMARY KEY (id) /T![clustered_index] NONCLUSTERED / ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin/!90000 SHARD_ROW_ID_BITS=2 / Comparing the table structure of db_test`.`t1 ... equivalent Comparing the table structure of db_test`.`t0 ... equivalent Comparing the table data of db_test`.`t1 ... equivalent Comparing the table data of db_test`.`t0 ... equivalent

Progress [============================================================>] 100% 0/0 A total of 2 table have been compared and all are equal. You can view the comparision details through '/tmp/sync-diff.output.2022-09-23T12.41.47+0800/sync_diff.log'

ti-chi-bot commented 2 years ago

@jackysp: Thanks for your review. The bot only counts approvals from reviewers and higher roles in list, but you're still welcome to leave your comments.

In response to [this](https://github.com/pingcap/tidb-tools/pull/679#pullrequestreview-1117967883): >LGTM Instructions for interacting with me using PR comments are available [here](https://prow.tidb.net/command-help). If you have questions or suggestions related to my behavior, please file an issue against the [ti-community-infra/tichi](https://github.com/ti-community-infra/tichi/issues/new?title=Prow%20issue:) repository.

Leavrth commented 2 years ago

/merge

ti-chi-bot commented 2 years ago

This pull request has been accepted and is ready to merge.

Commit hash: ca8d758039a4d1373b3985beb01d1cccaefb4c84

pingcap / tidb-tools