How to Address the Creation of Tables Without Effective Indexes to Resolve the Issue of TiCDC Being Unable to Synchronize Such Tables?

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 基于 TiCDC 不能同步无有效索引表的问题,如何从源头解决无有效索引表的创建呢?

| username: TiDBer_CQ

[TiDB Usage Environment] Production Environment / Testing / PoC
[TiDB Version] 7.1
Regarding the issue that TiCDC cannot synchronize tables without valid indexes, how can we prevent the creation of tables without valid indexes from the source?
For example: Is there a parameter to prohibit the creation of tables without valid indexes?

| username: 小龙虾爱大龙虾 | Original post link

Yes, 系统变量 | PingCAP 文档中心

| username: TiDBer_5cwU0ltE | Original post link

It feels like we should communicate with the leadership and establish a standard, linking it to assessments. Only then will it work. From a technical perspective, imposing restrictions might not be appreciated by others, who may think you are creating obstacles.

| username: 我是人间不清醒 | Original post link

Introducing SQL work order auditing, we also have extensive multi-permissions in R&D, and downstream synchronization often encounters issues. After implementing auditing, this problem has been effectively avoided. It’s similar to Yearning, with statement validation and configurable desired restrictions, which will be rejected in the first step.

| username: TiDBer_小阿飞 | Original post link

Standardize index creation!
Follow certain standards and usage rates for index creation.
Rules or audit mechanisms.

| username: 像风一样的男子 | Original post link

This is a development standard issue; normally, a table would definitely have a primary key.

| username: forever | Original post link

This parameter is correct. Modify the primary key to write it as delete and add in one statement.

| username: 源de爸 | Original post link

We previously used MySQL 8.0, and all environments were forced to enable sql_require_primary_key=ON. The same applies to TiDB.

| username: Daniel-W | Original post link

It is generally recommended to set SET GLOBAL sql_require_primary_key=on when using CDC. If a table is created or modified without a primary key, an error will be returned.

| username: DBAER | Original post link

Development standards, training. Working on parameter constraints.

| username: dba-kit | Original post link

Actually, this has a big pitfall. Many metadata tables on DM do not have primary keys, and enabling it will cause DM to fail.

| username: vcdog | Original post link

Database development standards are essential.

| username: 小于同学 | Original post link

Database development standards are essential :+1:

| username: 小龙虾爱大龙虾 | Original post link

:saluting_face: Got it, thanks.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.