Feature Request: allow DDL statements to run for unlimited time

[shlomi-noach]

Feature Description

The vttablet variable --queryserver-config-query-timeout sets a timeout on all statements served in TabletServer/QueryExecutor. It makes sense for most queries to have some reasonable timeouts.

However, for DDL statements this is problematic, and even wrong. Consider the following arguments:

• Some DDLs like ALTER TABLE or OPTIMIZE TABLE can runs for hours and days, well beyond any reasonable queryserver-config-query-timeout setting.
• Say we do run such a long running DDL. The way the timeout is implemented is like so:

  1. Using a context.WithTimeout

  2. Check for timeout here:
     

     vitess/go/vt/vttablet/tabletserver/connpool/dbconn.go

     Lines 188 to 194 in 1ed18d0

     	case <-ctx.Done():
     	dbc.terminate(ctx, insideTxn, now)
     	if !insideTxn {
     	// wait for the execute method to finish to make connection reusable.
     	<-ch
     	}
     	return nil, dbc.Err()

  3. Fast forward a few function calls, issue a kill query statement here:
     

     vitess/go/vt/vttablet/tabletserver/connpool/dbconn.go

     Lines 520 to 525 in 1ed18d0

     	sql := fmt.Sprintf("kill query %d", dbc.conn.ID())
     	go func() {
     	_, err := killConn.Conn.ExecuteFetch(sql, -1, false)
     	ch <- err
     	close(ch)
     	}()

The problem is that KILL QUERY does not work reliably with long running DDLs. The good scenario is that MySQL does rollback the ALTER TABLE statement, but it can and will take a proportionate time to the timeframe the statement ran before beign killed. So from the moment of KILL QUERY it may yet take hours for the original query to return/complete, which leaves the killing code waiting still.

To summarize:

• DDLs have very different timeout requirements compared with DML
• Timing out a DDL may not work as expected.

In light of that, I'm suggesting DDL should run without timeout at all.

It should go without saying that we advocate the use of Online DDL in vitess.

Use Case(s)

Being able to protect against normal queries unreasonable runtime, while still allowing long running DDLs.