`driver: bad connection` on query with MySQL 5.7... works fine on 5.6

[benguild]

Issue description

I haven't been able to get a particular style of query to work on MySQL 5.7, but it works fine against MySQL 5.6 on the same hardware. 100% failure rate in Go right now. — Crashes the connection or something with driver: bad connection every time... and it stays like that for a few requests thereafter.

Example code

See SQL query at: #554 (comment)

Configuration

I just pulled the latest master, doesn't work. :(

Driver version (or git SHA): 2e00b5c
Go version: go version go1.6.3 (appengine-1.9.46) darwin/amd64
Server version: 5.7.14-google-log

[methane]

Without reproducible example, nothing I can do.
What you can do is creating minimum, easy to reproduce example for us.

If you can't, tcpdump may capture useful information.

[benguild]

Sorry for the delay, I'm researching this now. I noticed that the query actually does work when the parameters are embedded into the query itself and not inserted using ? marks. Otherwise it fails with "bad connection" and crashes something somehow.

I'll see how much I can widdle this down to a concrete reproduction.

[benguild]

OK— I was able to simplify the query causing connection issues with MySQL 5.7 down to something that requires no schema/tables:

(SELECT ? AS `bugCauser` FROM (SELECT * FROM (SELECT NULL) AS `tableA` ORDER BY (SELECT NULL FROM (SELECT NULL) AS `tableB`)) AS `tableA` ORDER BY (SELECT NULL FROM (SELECT NULL) AS `tableB`))

As mentioned, this query crashes the connection on the latest master pull of this driver against a fresh Google Cloud SQL instance running MySQL 5.7. The issue does not appear with the same query pointed at a MySQL 5.6 instance, nor when I run this query directly on the server or via a vanilla SQL client.

---

If you remove the bound parameter (replace ? with NULL) ... the issue disappears. So, whatever is happening is triggered by the fact that there is at least one bound parameter present in the query.

[benguild]

I can confirm that a way to workaround this bug is to move the column fetch for the outer ORDER BY into SELECT ...

(SELECT ? AS `bugCauser`, (SELECT NULL FROM (SELECT NULL) AS `tableB`) AS `countSelected` FROM (SELECT * FROM (SELECT NULL) AS `tableA` ORDER BY (SELECT NULL FROM (SELECT NULL) AS `tableB`)) AS `tableA` ORDER BY `countSelected`)

… However, one reason why this doesn’t work in all situations is due to the ONLY_FULL_GROUP_BY restriction that comes present by default in MySQL 5.7 ... so, if we were using a GROUP BY in that outer query, we’d be in trouble since we’re not SELECTing or GROUPing the "countSelected" column in practice.

[methane]

If it's relying on MySQL server version, it's very likely server side issue, rather than
driver (connector) issue.
Driver can't do anything when server returned error.
Why don't you ask on MySQL's issue tracker?

[benguild]

Like I said, I can run this against the server from command line or from SQL client without issue. It's only an issue if I call it via Go, which crashes the connection.

The issue does not appear when talking to a MySQL 5.6 server, but if I call that query from Golang against MySQL 5.7 (Google Cloud SQL) the connection crashes in the pool.

[benguild]

Results:

• run via Go on MySQL 5.7 server — FAILS!
• run via SQL client on MySQL 5.7 server - OK
• run via Go on MySQL 5.6 server — OK
• run via SQL client on MySQL 5.6 server - OK

[methane]

Like I said, I can run this against the server from command line or from SQL client without issue.

Is it use prepared statement? I don't think so.

[benguild]

OK, so you're saying the bug is with MySQL prepared statements, not with this?

[methane]

I don't know it's a bug or spec.
Anyway, you don't provide "reproducible" example. Just a SQL is not "reproducible".

[benguild]

If you run that query in the environment I mentioned, it is reproducible 100% of the time.

[methane]

mysql> prepare xxx from 
    -> (SELECT ? AS `bugCauser` FROM (SELECT * FROM (SELECT NULL) AS `tableA` ORDER BY (SELECT NULL FROM (SELECT NULL) AS `tableB`)) AS `tableA` ORDER BY (SELECT NULL FROM (SELECT NULL) AS `tableB`));
ERROR 1064 (42000): 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 ? AS `bugCauser` FROM (SELECT * FROM (SELECT NULL) AS `tableA` ORDER BY ' at line 2

"prepare" statement is different from "prepared statement protocol" used by drivers.
But I think same error happens.

[methane]

If you run that query in the environment I mentioned, it is reproducible 100% of the time.

You don't provide no code.
I don't know which options do you used.

In most case, maintainers are volunteer. Please don't expect we write code from scratch.
If I do it and I can't reproduce, I waste my time. I have no time to waste.

[benguild]

Was tested via GORM:

db.Exec("(SELECT ? AS `bugCauser`, (SELECT NULL FROM (SELECT NULL) AS `tableB`) AS `countSelected` FROM (SELECT * FROM (SELECT NULL) AS `tableA` ORDER BY (SELECT NULL FROM (SELECT NULL) AS `tableB`)) AS `tableA` ORDER BY `countSelected`)", 1234);

I didn't think code was necessary because it's just query that requires no schema/tables.