MySQL Bugs: #93613: Mysql driver loads empty ResultSet from some table on some server.

Bug #93613	Mysql driver loads empty ResultSet from some table on some server.
Submitted:	14 Dec 2018 10:24	Modified:	16 Dec 2018 21:58
Reporter:	Konstantin Pavelko	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	5.1.39	OS:	Any
Assigned to:	Filipe Silva	CPU Architecture:	Any

Description:
I discovered strange behavior when upgraded mysql-connector-java: simple SQL query reads zero rows from some table on some server.
This behavior appears in mysql driver starting from version 5.1.39 and later, even on the latest version 8.0.13. Version 5.1.38 works ok.

There is the query:
SELECT 
  `browser_version_new`.`id`, 
  `browser_version_new`.`browser`, 
  `browser_version_new`.`version`
FROM `browser_version_new`

But if I change some symbol in the query (add another space or change SELECT → sELECT),  mysql driver reads all rows. 
This issue disappears on the same table with the same data on another mysql server.

I tried to debug this issue, it seems the driver thinks that it read a warning answer from the server, but the server actually returns all rows.

You can find results of my investigation in the attached files.

MysqlTestLoad.java – test application. There are several test functions: sqlStatement, sqlDirect, sqlDirect2. All of them produce the same result.

dump.5.1.38.txt – dump with result produced by driver 5.1.38
dump.5.1.39.txt – dump without result produced by driver 5.1.39
dump.5.1.39s.txt – dump with result produced by driver 5.1.39. The query was changed: SELECT → sELECT

staging.5.1.38.pcap, staging.5.1.39.pcap, staging.5.1.39s.pcap – corresponding pcap files

streams.txt – contains direct read from mysql InputStream after query was sent. See commented function call read1K in MysqlTestLoad.java

mysql server:
version: 5.7.17-11-log
version_comment: Percona Server (GPL), Release 11, Revision f60191c

How to repeat:
Run MysqlTestLoad.java on specific case.

test application

Attachment: MysqlTestLoad.java (application/octet-stream, text), 2.95 KiB.

dump with result produced by driver 5.1.38

Attachment: dump.5.1.38.txt (text/plain), 17.29 KiB.

dump without result produced by driver 5.1.39

Attachment: dump.5.1.39.txt (text/plain), 16.94 KiB.

dump with result produced by driver 5.1.39. The query was changed: SELECT → sELECT

Attachment: dump.5.1.39s.txt (text/plain), 16.75 KiB.

dump with result produced by driver 5.1.38

Attachment: staging.5.1.38.pcap (application/octet-stream, text), 8.42 KiB.

dump without result produced by driver 5.1.39

Attachment: staging.5.1.39.pcap (application/octet-stream, text), 8.15 KiB.

dump with result produced by driver 5.1.39. The query was changed: SELECT → sELECT

Attachment: staging.5.1.39s.pcap (application/octet-stream, text), 8.14 KiB.

direct reads from mysql InputStream after query was sent

Attachment: streams.txt (text/plain), 15.48 KiB.

Hi Konstantin,

Thank you very much for your interest in Connector/J and for this detailed report.

This bug is a duplicate of Bug#83304, which is related to the MySQL Server Bug#83346.

This happens only when you turn query cache on in the server and combine multiple clients with different support for the DEPRECATE_EOF protocol capability and make them run the exact same query. As a result, the cached query ends up being returned with data structured in a way that some of the clients don't understand it. Please consult the above mentioned bugs for all details.

You have a couple of options to avoid this problem:
1. Don't use query cache. Note that MySQL will no longer invest on it (
http://mysqlserverteam.com/mysql-8-0-retiring-support-for-the-query-cache/)
2. If you absolutely need to use query cache, then don't mix clients that support the capability DEPRECATE_EOF with clients that don't support it.

I'm setting this report as a duplicate of Bug#83304 but mind that it will never be fixed.

Please reopen or file another bug if you find something else not covered by the described behavior.