Placeholders with multiple statements in binary protocol do not work

[CAFxX]

Issue description

It seems that using multiple statements and placeholders is not fully supported yet.

From a cursory look this seems to me very similar to #769. That ticket is closed with the reason that the binary protocol does not support multi resultsets, but as I wrote in #769 (comment) that should not be the case. It was suggested by @methane that #769 is a different issue, so I opened this issue instead.

Example code

The following code unexpectedly returns error during db.Query. If the commented-out db.Query line is used instead, or if interpolateParams=true is used in the DSN, then the query succeeds.

package main

import (
        "database/sql"
        "fmt"
        _ "github.com/go-sql-driver/mysql"
)

func main() {
    db, err := sql.Open("mysql", "root:root@tcp(127.0.0.1:3306)/test?multiStatements=true")
    if err != nil {
        panic(err)
    }
    defer db.Close()
    
    // rows, err := db.Query("SET @v = 1; SELECT @v; SELECT @v+1;") // this works
    rows, err := db.Query("SET @v = ?; SELECT @v; SELECT @v+1;", 1) // this does not work

    if err != nil {
        panic(err)
    }
    defer rows.Close()

    var r1, r2 int

    if !rows.Next() {
        panic("missing row")
    }
    rows.Scan(&r1)

    if !rows.NextResultSet() {
        panic("missing result set 2")
    }
    if !rows.Next() {
        panic("missing row")
    }
    rows.Scan(&r2)

    fmt.Println(r1, r2) // 1 2
}

Error log

panic: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT @v; SELECT @v+1' at line 1

Configuration

Driver version (or git SHA):
c45f530

Go version: run go version in your console
1.11.2

Server version: E.g. MySQL 5.6, MariaDB 10.0.20
MySQL 5.7

Server OS: E.g. Debian 8.1 (Jessie), Windows 10
Ubuntu 16.04

[peergynt]

@CAFxX would the query above even use the binary protocol? This looks like a straight text query.
I believe that the binary protocol would be used with a prepared statement.

[CAFxX]

@peergynt my understanding is that the binary protocol is used automatically by the driver when you use placeholders and you don't set interpolateParams=true (at least, that's my understanding from reading #769 (comment)):

You need to use interpolateParams=true to use text protocol, at your own risk.

[peergynt]

@CAFxX I believe that the issue here is that the driver uses a prepared statement under the hood when you use the placeholder but a prepared statement with multiple statements is not valid in MySQL:
https://dev.mysql.com/doc/refman/8.0/en/prepare.html

The text must represent a single statement, not multiple statements.

[methane]

    rows, err := db.Query("SET @v = ?; SELECT @v; SELECT @v+1;", 1) // this does not work

This is duplicate of #769.
Both of #769 and this are about "multi statement", not "multi resultset".

"multi statement" depends on "multi resultset". But "multi resultset" doesn't depend on "multi statement".

Note that there are CLIENT_MULTI_RESULTS, CLIENT_PS_MULTI_RESULTS, CLIENT_MULTI_STATEMENTS, but there isn't CLIENT_PS_MULTI_STATEMENT.
https://dev.mysql.com/doc/internals/en/capability-flags.html

[methane]

I think stored procedure is required to return "multi resultset" from "single statement".
But I never use stored procedure. I am not interested in it.

If you really need it, please try to use it.

[CAFxX]

@methane I think it would be less surprising if the driver fell back to the text protocol automatically when its choice of protocol fails (as in this case, where the driver's internal choice of the binary protocol causes a valid MySQL query to fail).

It is unfortunate that this use case is currently not working, as multiple statements are very effective at slashing (multi)query latency.

[methane]

It is unfortunate that this use case is currently not working, as multiple statements are very effective at slashing (multi)query latency.

You can use interpolateParams=true.

[CAFxX]

I would, but does this still apply?

You need to use interpolateParams=true to use text protocol, at your own risk.

If that configuration is insecure then I guess it shouldn't be used, right? If that's the case, it means the use case is not supported, correct?

---

On a completely unrelated note, I just realized we are just a few floors apart, as I'm also working in Roppongi. 🤣

[methane]

If that configuration is insecure then I guess it shouldn't be used, right?

I think it's secure as far as it is used correctly. If you find security hole, file an issue.
But we can not write down all difference between PS and interpolateParams.
For example, I never tried SELECT "foo ? bar" on PS. In case of interpolateParams, ? is treated as placeholder.

I don't say you should, or should not use it. You should think it by your brain.

If that's the case, it means the use case is not supported, correct?

If you determined to not use it, correct. MySQL doesn't support multi statement in prepared statement. We can't support what MySQL doesn't support.

BTW, new MySQL protocol (X protocol) support pipelining. It works like batching multi statements in one query. But there are no production ready Go driver for the protocol.

---

On a completely unrelated note, I just realized we are just a few floors apart, as I'm also working in Roppongi. 🤣

Ah, I use Mercari well. There are some ex-KLab in your company. Mercari is one of the best tech companies in Japan.
I believe there are some Go and MySQL experts in your office.

Additionally, there should be some PHP experts in your office too. The interpolateParams is very similar to "PDO::ATTR_EMULATE_PREPARES" in PHP. ref

You can discuss about interpolateParams with experts in your office.