support wait/nowait grammar on ddl operation

[Damon-Guo]

Feature Request

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

When adding index, it is inconsistent with MySQL's behavior after the terminal is killed or Ctrl+ C, causing confusion.And adding more than one index at a time usually requires open more than one session.
Some DDL statement also blocked for long periods by add index .
For example, an add column operation on a table that is being indexed will be blocked, and DDL operations on other tables, such as create table, drop table, etc., will also be blocked.You need a way to put it in the background, and now you're doing it by Ctrl+ C, which is a deviation from the normal understanding.

Describe the feature you'd like:

1. In the case of adding index in long time, the nowait option can be used to let the foreground session return success instead of waiting all the time, and the adding index operation will automatically run in the background.

2. After the DDL operation is killed or manually Ctrl+ C disconnects, cancel the DDL currently executed.

3. admin show ddl is difficult to distinguish the blocking relationship, the DDL operation in the wait state can intuitively see which job is blocked.

Describe alternatives you've considered:

Teachability, Documentation, Adoption, Migration Strategy:

[ghost]

MariaDB currently supports this syntax on DDL.

The use case is different though: MySQL/MariaDB will not permit schema changes while there is an open transaction that has modified data on the table. See #17796 for an example. I don't mind TiDB's behavior here, but it could be better documented.

w.r.t. the use case in TiDB. My hope is that #18396 is implemented instead. But if the scope makes it too large of a feature, this serves a useful intermediate use-case.

[bb7133]

Thanks for the tips @nullnotnil . considering the different semantic of WAIT/NOTWAIT between this proposal and MariaDB, I think we can use ASYNC keyword instead?

[ghost]

@bb7133 yes I agree, ASYNC better describes the semantic requested. Another sql-like word is deferred, borrowed from the feature deferrable constraints (not available in mysql)

There might be some hidden scope for reviewers to discuss:

The feature request says that admin show ddl has intuitiveness issues, but an async ddl job that has to be killed because it failed to convert a type without data loss could be harder to debug. There is the opportunity to support only a subset of 'safe' DDL statements, but that also could create issues for documentation to clarify what is supported for each type of DDL.