MySQL Bugs: #84639: Query caching with two different clients causes errors

Bug #84639	Query caching with two different clients causes errors
Submitted:	24 Jan 2017 12:57	Modified:	7 Jul 2017 9:09
Reporter:	Алекснадр Гнатына	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Query Cache	Severity:	S3 (Non-critical)
Version:	5.7.14,5.7.16,5.7.17	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	malformed packet, query_cache_type

Description:
With query_cache enabled newer client(5.7) can't get cached by older client results and vice versa.

Older clients show "ERROR 2027 (HY000): Malformed packet" error, newer: "Empty set, nnnnn warnings" and disconnects.

tcpdump shows answer recieved in both cases.

I've tried:
 FreeBSD 10.1, 
 Linux 4.8.7-2.el7.centos.x86_64,
 mysql server 5.7.14, 5.7.16, 5.7.17,
 5.7.14-7 Percona Server, 
 10.1.21-MariaDB - same error.

MariaDB client(mysql  Ver 15.1 Distrib 10.0.28-MariaDB, for Linux (x86_64) using readline 5.1) doesn't produce error for other clients and doesn't show errors from other client(seems server caches mariadb-clients' queries separately).

How to repeat:
Install mysql 5.17.*:
/usr/local/libexec/mysqld -V
/usr/local/libexec/mysqld  Ver 5.7.17 for FreeBSD10.1 on amd64 (Source distribution)
FreeBSD 10.1-STABLE-r273058

Check query_cache is on:
show variables like "query_cache_%";
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 33554432 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+

Create test table:
create table t1 (id int);
insert into t1 set id=2;

newer client first:
mysql -V
mysql  Ver 14.14 Distrib 5.7.17, for FreeBSD10.1 (amd64) using  EditLine wrapper

 echo "select  * from t1 where id=2;" | mysql -vvv test
--------------
select  * from t1 where id=2
--------------

+------+
| id   |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

Bye

Query cached:
show status like "Qcache_queries_in_cache";
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_queries_in_cache | 1     |
+-------------------------+-------+

Older client second:
mysql -V
mysql  Ver 14.14 Distrib 5.6.24, for FreeBSD10.1 (amd64) using  5.2
mysql> select  * from t1 where id=2;
ERROR 2027 (HY000): Malformed packet

cache status:
root@localhost [(none)]> show status like "Qcache_queries_in_cache";
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_queries_in_cache | 1     |
+-------------------------+-------+

Seems there is no new query in qcache.

vice versa:
reset query cache first:
reset query cache;
Query OK, 0 rows affected (0.00 sec)

older(5.6) first:
mysql> select  * from t1 where id=2;
+------+
| id   |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

newer (5.7) second:
root@localhost [test]> select  * from t1 where id=2;
Empty set, 25856 warnings (0.00 sec)

root@localhost [test]> show warnings;
ERROR 2013 (HY000): Lost connection to MySQL server during query
root@localhost [test]>

Also i have mariaDB client which doesn't produce error for other clients and doesn't give error after other clients:
mysql -V
mysql  Ver 15.1 Distrib 10.0.28-MariaDB, for Linux (x86_64) using readline 5.1

Qcache_queries_in_cache increases after mariaDB:
> show status like "Qcache_queries_in_cache";
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_queries_in_cache | 2     |
+-------------------------+-------+

Suggested fix:
Use mariadb client or all clients must have same version.

typo in tags.

Category changed to Query Cache, several versions affected.

Hi!

Thank you for your bug report. I must inform you that I was not able to repeat your problem. There are two possible reasons.

First and first of all, I used only Oracle-built binaries, which are essential in order to evade build problems.

Second, never use database  with a name starting with "test", as it had special meaning in the previous versions.

>First and first of all, I used only Oracle-built binaries, which are essential in order to evade build problems.

I will try Oracle-build binary soon.

>Second, never use database  with a name starting with "test", as it had special meaning in the previous versions.
Database name doesn't matter in this case, i've tried other name with same effect:
mysql> select * from myshop.t1 where id=2;
ERROR 2027 (HY000): Malformed packet
mysql>

I've tried Oracle-build binaries, with same effect :(
Log below:
root@dev:~ # mysql-5.6.35-freebsd10-x86_64/bin/mysql -V
mysql-5.6.35-freebsd10-x86_64/bin/mysql  Ver 14.14 Distrib 5.6.35, for freebsd10 (amd64) using  EditLine wrapper
root@dev:~ # mysql-5.6.35-freebsd10-x86_64/bin/mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> reset query cache;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from myshop.t1 where id=2;
+------+
| id   |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

mysql> ^DBye
root@dev:~ # mysql-5.7.17-freebsd10-x86_64/bin/mysql -V
mysql-5.7.17-freebsd10-x86_64/bin/mysql  Ver 14.14 Distrib 5.7.17, for freebsd10 (amd64) using  EditLine wrapper
root@dev:~ # mysql-5.7.17-freebsd10-x86_64/bin/mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from myshop.t1 where id=2;
Empty set, 30976 warnings (0.00 sec)

mysql> status;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> status;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    13
Current database: *** NONE ***

--------------
mysql-5.7.17-freebsd10-x86_64/bin/mysql  Ver 14.14 Distrib 5.7.17, for freebsd10 (amd64) using  EditLine wrapper

Connection id:          13
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          more
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.17-log MySQL Community Server (GPL)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /tmp/mysql.sock
Uptime:                 31 min 21 sec

Threads: 1  Questions: 62  Slow queries: 0  Opens: 117  Flush tables: 1  Open tables: 110  Queries per second avg: 0.032
--------------

mysql>

Could you check same binaries on FreeBSD?

Hi,

Thanks for your report. Verified as described (on FreeBSD only, Oracle build)
 # uname -a
FreeBSD freebsd2 11.0-RELEASE-p1 FreeBSD 11.0-RELEASE-p1 #0 r306420: Thu Sep 29 01:43:23 UTC 2016     root@releng2.nyi.freebsd.org:/usr/obj/usr/src/sys/GENERIC  amd64

root@freebsd1:/usr/local # wget --no-check-certificate https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.17-freebsd11-x86_64.tar.gz

...

root@freebsd1:/usr/local/mysql # bin/mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like "query_cache_%";
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 33554432 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
5 rows in set (0.00 sec)

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.7.17    |
+-----------+
1 row in set (0.00 sec)

mysql> create database x;
Query OK, 1 row affected (0.00 sec)

mysql> use x;
Database changed
mysql> create table t1 (id int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 set id=2;
Query OK, 1 row affected (0.01 sec)

mysql> select  * from t1 where id=2;
+------+
| id   |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

mysql> show status like "Qcache_queries_in_cache";
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_queries_in_cache | 1     |
+-------------------------+-------+
1 row in set (0.00 sec)

mysql> grant all privileges on x.* to 'luzer'@'192.168.89.224' identified by '95uwekt42t4!A';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql>

root@freebsd2:/usr/local/mysql-5.6.35-freebsd11-x86_64/bin # ./mysql -h192.168.89.223 -uluzer -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select  * from t1 where id=2;
ERROR 1046 (3D000): No database selected
mysql> use x;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select  * from t1 where id=2;
ERROR 2027 (HY000): Malformed packet
mysql> show status like "Qcache_queries_in_cache";
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_queries_in_cache | 1     |
+-------------------------+-------+
1 row in set (0.00 sec)

mysql> \q

You might want to optimize your query cache, here's a good online tuner: 
https://dom.as/tech/query-cache-tuner/

Seriously, think twice if you want to use query cache or not, while this really is a bug, I doubt it will get any priority due to the fact it's related only to query cache.

take care
Bogdan

Same bug on linux:

gnatyna@gnatyna-HP ~/tmp/mysql $ uname -a
Linux gnatyna-HP 4.6.7-nrj-desktop-1rosa-x86_64 #1 SMP PREEMPT Wed Aug 17 12:10:18 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux
gnatyna@gnatyna-HP ~/tmp/mysql $ cat /etc/redhat-release
ROSA Desktop Fresh R8 release 2014.1 for x86_64

bin/mysql -uroot --socket=/tmp/mysql.sock -p123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.17 MySQL Community Server (GPL)                                                                           Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.                                                                       Oracle is a registered trademark of Oracle Corporation and/or its            
affiliates. Other names may be trademarks of their respective 
owners.                                                                         Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.                                                                      

mysql> create database qct;
Query OK, 1 row affected (0,00 sec)                                                                            mysql> create table qct.t1 (id int);
Query OK, 0 rows affected (0,05 sec)

mysql> insert into qct.t1 set id=2;
Query OK, 1 row affected (0,01 sec)

mysql> show variables like "query_cache%";
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 33554432 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
5 rows in set (0,00 sec)

mysql> select * from qct.t1 where id=2;
+------+
| id   |
+------+
|    2 |
+------+
1 row in set (0,00 sec)

mysql> show status like "qc%";
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 33535784 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 1        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 1        |
| Qcache_queries_in_cache | 1        |
| Qcache_total_blocks     | 4        |
+-------------------------+----------+
8 rows in set (0,00 sec)

mysql> ^DBye
gnatyna@gnatyna-HP ~/tmp/mysql $ mysql-5.6.35-linux-glibc2.5-x86_64/bin/mysql -uroot --socket=/tmp/mysql.sock -p123
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from qct.t1 where id=2;
ERROR 2027 (HY000): Malformed packet
mysql>
mysql> reset query cache;
Query OK, 0 rows affected (0,00 sec)

mysql> select * from qct.t1 where id=2;
+------+
| id   |
+------+
|    2 |
+------+
1 row in set (0,00 sec)

mysql> ^DBye
gnatyna@gnatyna-HP ~/tmp/mysql $ mysql-5.7.17-linux-glibc2.5-x86_64/bin/mysql -uroot --socket=/tmp/mysql.sock -p123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from qct.t1 where id=2;
Empty set, 25344 warnings (0,01 sec)

mysql> show warnings;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> status;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    9
Current database: *** NONE ***

--------------
mysql-5.7.17-linux-glibc2.5-x86_64/bin/mysql  Ver 14.14 Distrib 5.7.17, for linux-glibc2.5 (x86_64) using  EditLine wrapper

Connection id:          9
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.17 MySQL Community Server (GPL)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /tmp/mysql.sock
Uptime:                 4 min 54 sec

Threads: 1  Questions: 29  Slow queries: 0  Opens: 118  Flush tables: 1  Open tables: 37  Queries per second avg: 0.098
--------------

mysql>

MySQL will no longer invest in the query cache, see:

http://mysqlserverteam.com/mysql-8-0-retiring-support-for-the-query-cache/