Filter the DDL Job History based on DDL types

[sachin-japate]

Feature Request

Is your feature request related to a problem? Please describe: Currently, DDL job history returns all the DDL jobs either by start_job_id or with limit. API not filtering the jobs based on the DDL type.

Describe the feature you'd like: Extending DDL job history API to filter based on the DDL types. http://{TIDBIP}:10080/ddl/history?ddl_types="2,4,47" http://{TIDBIP}:10080/ddl/history?ddl_types="2,4,47"&limit={number}

Describe alternatives you've considered: Requirements raised due to issues with incremental backups in TiDB have some limitations, incremental backup after certain types of DDL executed can lead to data inconsistency issues. To solve this, we wanted to handle this situation with our backup scheduling platform, We will introduce a validation before triggering any incremental backup. If we encounter such DDLs, a full backup will be triggered instead of incremental.

Alternatively, we could have fetched all the jobs and iterated and checked for the existence of specific DDLs, in some cases if the number of jobs is more, it may lead to OOM issues. So we wanted to avoid such situations and filter only specific DDL jobs of the type we required.

[D3Hunter]

incremental backup after certain types of DDL executed can lead to data inconsistency issues.

do you mean the TiDB BR tools? cc @BornChanger

also quote from https://stackoverflow.com/a/2602127/1347716,

The standard way to pass a list of values as URL parameters is to repeat them:

so http://{TIDBIP}:10080/ddl/history?type=2&type=4&type=47 would be better

[wjhuang2016]

It's a little confusing to use hard-coding numbers. How about using SQL instead of the 'HTTP API'?

[sachin-japate]

incremental backup after certain types of DDL executed can lead to data inconsistency issues.

do you mean the TiDB BR tools? cc @BornChanger Yes, in the BR tools (https://docs.pingcap.com/tidb/stable/br-incremental-guide#limitations) also quote from https://stackoverflow.com/a/2602127/1347716,

The standard way to pass a list of values as URL parameters is to repeat them:

so http://{TIDBIP}:10080/ddl/history?type=2&type=4&type=47 would be better Will check this

[sachin-japate]

It's a little confusing to use hard-coding numbers. How about using SQL instead of the 'HTTP API'?

@wjhuang2016 For the SQL interface, we need to grant SQL users certain permissions, and the backup scheduling platform needs to store these credentials for future use. However, storing credentials can lead to compliance issues. Therefore, we prefer to use an HTTP API.

[sachin-japate]

@wjhuang2016 Let me know if there are any further concerns or suggestions. Happy to discuss.

[easonn7]

Hi @sachin-japate , this is kernel PM Eason.

While HTTP API interfaces are convenient, they can also introduce security vulnerabilities. Recently, the kernel product team and security team have been reviewing the design specifications for internal interfaces. One of the specifications is that in the future, HTTP APIs will only serve internal components and will require component authentication. Therefore, we apologize that we cannot accommodate this request.

For future requests related to management information such as job history and schema metadata, we are going to provide corresponding system tables to meet those needs. Of course, security and convenience are always in conflict, and product design aims to provide convenience while ensuring security.

Regards, Eason

[sachin-japate]

Hi @easonn7 Thank you for sharing the future view on the HTTP API interfaces. Balancing security and convenience is challenging, and I appreciate the efforts to ensure both are addressed.

We will rethink our approach, and let you know if we need help.