Mysql: Return useful error when attempting to reuse a blocked connection

[ycybaby]

Issue description

the latest version: 1.3.

Prepare one statement and execute it on one transaction, keep rows open. Error happens when trying to prepare another statement:
[mysql] 2016/12/08 15:45:57 packets.go:431: busy buffer

I see issue#314 which was similiar with the problem and closed. Looks like this problem not fixed in 1.3.

Example code

func query(db *sql.DB) {
    var (
        tx    *sql.Tx
        stmt1 *sql.Stmt
        stmt2 *sql.Stmt
        rs1   *sql.Rows
        rs2   *sql.Rows
    )

    tx, _ = db.Begin()
    stmt1, _ = tx.Prepare("select f1, f2 from test where f2 = 11")
    rs1, _ = stmt1.Query()
    rs1.Next()

    stmt2, _ = tx.Prepare("select f2 from test where f2 = 111")  // error here
    rs2, _ = stmt2.Query()
    rs2.Next()

}

Error log

[mysql] 2016/12/08 15:45:57 packets.go:431: busy buffer

Configuration

*Driver version:1.3

Go version: go version go1.7.1 windows/amd64,go version go1.7.4 linux/amd64

[methane]

314 and this is completely different.

MySQL protocol is not async. Previous request must be finished before sending next request.
It means you must Close previous ResultSet before Prepare (or any other request like Exec or Query).

[julienschmidt]

This should however return a different error than "busy buffer". I'm surprised database/sql even allows this.

[ycybaby]

I am not sure whether it depends on the setting of STMT_ATTR_CURSOR_TYPE.

In packets.go:915, it is set to CURSOR_TYPE_NO_CURSOR.
// flags (0: CURSOR_TYPE_NO_CURSOR) [1 byte]
data[9] = 0x00

We also use mysql c driver in other project. It is set to CURSOR_TYPE_READ_ONLY so that more than one resultset can be opened on one connection at the same time.

[methane]

http://dev.mysql.com/doc/refman/5.7/en/mysql-stmt-attr-set.html

If you use the STMT_ATTR_CURSOR_TYPE option with CURSOR_TYPE_READ_ONLY, a cursor is opened for the statement when you invoke mysql_stmt_execute(). If there is already an open cursor from a previous mysql_stmt_execute() call, it closes the cursor before opening a new one.

So you cannot keep old resultset opened even when using STMT_ATTR_CURSOR_TYPE.

[ycybaby]

This note just says the open cursor for the stmt to be re-executed will be closed, not cursors for all stmts on the connection, i.e. only one cursor can be opened for one stmt at any time.
But more than one stmt can be open on one connection.

[methane]

Hmm. You may right.
But I'm not interested in supporting server side cursor.

[ycybaby]

I think in many scenarios this feature is needed, such as nest-loop search, ...

Another problem is, without no cursor all dataset will be fetched to client-side. Maybe one performance issue when dataset is huge.

Hope it can be added in the future version :)

[methane]

I don't understand this yet.
I can't find any protocol for concurrent resultset fetching.

Could you provide complete example written in C, using MySQL Connector/C?
Or could you get packet capture with tcpdump?

[methane]

Ah, I found the document. It is in "Stored Procedures" section, not "Prepared Statements" section.
https://dev.mysql.com/doc/internals/en/com-stmt-fetch-response.html

[julienschmidt]

I created issue #1053 regarding cursor support. Please continue the discussion about that over there.

The original issue still returns the same confusing error:

=== RUN   TestIssue526
[mysql] 2020/01/07 16:51:31 packets.go:446: busy buffer
[mysql] 2020/01/07 16:51:31 connection.go:158: bad connection
--- FAIL: TestIssue526 (0.02s)
    driver_test.go:1802: driver: bad connection
FAIL

With the following adopted test code, which still has to modified to check for the correct error type:

func TestIssue526(t *testing.T) {
    runTests(t, dsn, func(dbt *DBTest) {
        tx, err := dbt.db.Begin()
        if err != nil {
            dbt.Fatal(err)
        }
        stmt1, err := tx.Prepare("SELECT 1")
        if err != nil {
            dbt.Fatal(err)
        }

        rs1, err := stmt1.Query()
        if err != nil {
            dbt.Fatal(err)
        }
        rs1.Next()
        err = rs1.Err()
        if err != nil {
            dbt.Fatal(err)
        }

        stmt2, err := tx.Prepare("SELECT 2")
        if err != nil {
            dbt.Fatal(err)
        }
        rs2, err := stmt2.Query()
        if err != nil {
            dbt.Fatal(err)
        }
        rs2.Next()
        err = rs2.Err()
        if err != nil {
            dbt.Fatal(err)
        }
    })
}