Weird behaviour with query cancellation using context

[pjebs]

Server: MySQL 5.6

Stored Proc:

DELIMITER //
CREATE PROCEDURE slowInsert(IN t int)
BEGIN       
       SELECT SLEEP(t);
       INSERT INTO `table_x` (message) VALUES (UUID());
END //
DELIMITER ;

Go Code:

package main

import (
    "context"
    "database/sql"
    "time"

    _ "github.com/go-sql-driver/mysql"
)

func main() {
    db, err := sql.Open("mysql", "<url>")
    if err != nil {
        panic(err)
    }
    db.SetConnMaxLifetime(9 * time.Second)
    db.SetMaxIdleConns(12)
    db.SetMaxOpenConns(12)

    ctx := context.Background()
    ctx, cancel := context.WithTimeout(ctx, time.Duration(3)*time.Second)
    defer cancel()

    _, err = db.ExecContext(ctx, "call slowInsert( 10 )") // context will cancel before insert occurs
    if err != nil {
        panic(err)
    }
}

[pjebs]

We are encountering weird behaviour.
When we run slowInsert( 10 ) on a mysql client (eg. sequel pro), the 10 second wait is respected before insertion. We have no reason to believe that the stored proc is wrong or invalid.

When we run the Go code above, the context is cancelling after 3 seconds -- all good.
... But immediately the insertion occurs. (It doesn't wait 10 seconds).

[pjebs]

Now, if we change the stored proc so that it's:

DELIMITER //
CREATE PROCEDURE slowInsert(IN t int)
BEGIN       
       SELECT SLEEP(t/2); // <---- The change
       SELECT SLEEP(t/2); // <---- The change
       INSERT INTO `table_x` (message) VALUES (UUID());
END //
DELIMITER ;

The Go code works perfectly and cancels the query (i.e. no insertion happens).
In both cases, our mysql client (sequel pro) respects the wait period hence both stored procs are equivalent.

But in our Go code, the second stored proc works but the first does not - leading to our belief that there is some weird bug in the driver.

[pjebs]

@alessio-palumbo

[methane]

It's not a bug. We don't support cancelling query yet.
And SLEEP() is very special query. It checks connection is alive or not.
In general case, MySQL doesn't check connection until query is finished.

[pjebs]

The Readme: https://github.com/go-sql-driver/mysql#contextcontext-support

Go 1.8 added database/sql support for context.Context. This driver supports query timeouts and cancellation via contexts. See context support in the database/sql package for more details.

[methane]

OK, README is misleading. It just cancelling "waiting" query result, not query execution.

MySQL protocol doesn't provide safe way to cancel query execution.
Some CLI supports cancelling, but it's not safe / stable on some environment.

[pjebs]

thanks

[methane]

What you should do is using transaction. If you don't commit, it's rollbacked.
So timeout while transaction is safe.

[pjebs]

@andizzle @alexclifford @edwardhutchison

[pjebs]

Why does first storedProc insert after timeout but second storedProc does not insert. I'd assume that based on your statement, it either inserts or it doesn't. If the driver doesn't support cancelling query execution, why does it seem to cancel the execution?

[methane]

It's MySQL behavior. I don't know much. But SLEEP() has very strange behavior when connection is closed.