Mysql: `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: https://github.com/go-sql-driver/mysql/issues/554#issuecomment-322020532

Configuration

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

Driver version (or git SHA): 2e00b5cd70399450106cec6431c2e2ce3cae5034
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.

[methane]

Even no schema and tables, I didn't know many things without code:

• connect options
• actual parameter (1234 in your last example).
• Exec? Prepare? Query?

Best way to provide full information for reproduce is just write complete, executable code
even if there are no schema required.

[benguild]

That's all I have. If I swap the server between 5.6 and 5.7, the connection crash appears. I don't know how to produce the other things you're asking about.

[methane]

I tried this code
and I got MySQL crash.

I don't know it's same error to you. If not, I waste my time.

[benguild]

Seems the same. Should we submit the bug to MySQL then? I appreciate your confirmation on whether or not it's an issue with the driver.

[methane]

Should we submit the bug to MySQL then?

Yes, if there is no duplicate issue report.
It's MySQL crash. It must not driver's issue.

[benguild]

OK, so it's not specific to Go, correct? It might be better if you submitted it because you know the specifics of how this driver and implementation work, but I could submit it and reference this issue (which we can close after).

[methane]

I can reproduce it with mysql command line.

mysql> prepare x from "(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`)";
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> set @a=1234;
Query OK, 0 rows affected (0.00 sec)

mysql> execute x using @a;
ERROR 2013 (HY000): Lost connection to MySQL server during query

server crashes in same way.

[benguild]

OK, will report now. Thanks for your help + research!

[methane]

I reported it just now.
https://bugs.mysql.com/bug.php?id=87484

[benguild]

Great! I just commented :)

[xaionaro]

[methane]

You need to login to see the issue.
Last comment is:

[7 Feb 11:32] Jon Stephens
Documented fix in the MySQL 5.7.22 and 8.0.5 changelogs as follows:

    Prepared statements using nested sub-selects were not always 
    handled correctly.

Closed.

[xaionaro]

@methane I authed, but still You do not have access to bug #87484..