Query caching with two different clients causes errors
Affects | Status | Importance | Assigned to | Milestone | ||
---|---|---|---|---|---|---|
MySQL Server |
Unknown
|
Unknown
|
||||
Percona Server moved to https://jira.percona.com/projects/PS | Status tracked in 5.7 | |||||
5.5 |
New
|
Undecided
|
Unassigned | |||
5.6 |
New
|
Undecided
|
Unassigned | |||
5.7 |
Triaged
|
Medium
|
Unassigned | |||
8.0 |
Invalid
|
Undecided
|
Unassigned |
Bug Description
We're seeing issues when running a SELECT SQL_CACHE query on one client, then running that same query (that has already been cached) on another client. There was a similar bug reported and fixed in 2004 https:/
The bug can be replicated as follows:
With query_cache_type being set to DEMAND
Run a query using PHP's PDO class and the MySQL PDO driver
SELECT SQL_CACHE count(`sys_id`) AS `count` FROM t_2130;
Now run the same query through the command line:
mysql> SELECT SQL_CACHE count(`sys_id`) AS 'count' FROM t_2130;
Empty set (0.00 sec)
The result is Empty set instead of the count, if you try to run that same query again via command line:
mysql> SELECT SQL_CACHE count(`sys_id`) AS 'count' FROM t_2130;
ERROR 2013 (HY000): Lost connection to MySQL server during query
There is also an issue if you do it the other way around
Run a query via the command line
mysql> SELECT SQL_CACHE count(`sys_id`) AS `count` FROM t_2169;
+-------+
| count |
+-------+
| 6 |
+-------+
1 row in set (0.00 sec)
Returns as it should.
Now run the same query using PHP's PDO class and the MySQL PDO driver
SELECT SQL_CACHE count(`sys_id`) AS `count` FROM t_2169;
You get the error:
SQLSTATE[HY000]: General error: 2027 Malformed packet
So far I've found that running "RESET QUERY CACHE;" does slightly solve the issue, as long as we stick to running it on one client
I've tested on Percona TokuDB 5.7.11-4, 5.7.13-6, and 5.7.14-7
I'm using php version 5.6.20
tags: | added: upstream |
Got same issue: 1.el7.elrepo. x86_64 #1 SMP Wed Sep 7 11:44:03 EDT 2016 x86_64 x86_64 x86_64 GNU/Linux
Server:
echo "status"|mysql|grep Server
Server version: 5.7.14-7 Percona Server (GPL), Release 7, Revision 083e298
uname -a
Linux 4.4.20-
mysql> create table test1 (id int);
inQuery OK, 0 rows affected (1.56 sec)
mysql> insert into test1 set id=2;
Query OK, 1 row affected (0.27 sec)
Newer client:
# mysql -V
mysql Ver 14.14 Distrib 5.7.17, for FreeBSD10.1 (amd64) using EditLine wrapper
mysql> select * from test1 where id=2;
--------------
select * from test1 where id=2
--------------
+------+
| id |
+------+
| 2 |
+------+
1 row in set (0.00 sec)
Older client:
mysql -V
mysql Ver 14.14 Distrib 5.6.24, for FreeBSD10.1 (amd64) using 5.2
mysql> select * from test1 where id=2;
--------------
select * from test1 where id=2
--------------
ERROR 2027 (HY000): Malformed packet
if run older client first:
mysql -V
mysql Ver 14.14 Distrib 5.6.24, for FreeBSD10.1 (amd64) using 5.2
mysql> select * from test1 where id='2';
--------------
select * from test1 where id='2'
--------------
+------+
| id |
+------+
| 2 |
+------+
1 row in set (0.01 sec)
Newer client got warnings message and disconnects:
mysql -V
mysql Ver 14.14 Distrib 5.7.17, for FreeBSD10.1 (amd64) using EditLine wrapper
mysql> select * from test1 where id='2';
--------------
select * from test1 where id='2'
--------------
Empty set, 28160 warnings (0.00 sec)
mysql> show warnigs;
--------------
show warnigs
--------------
ERROR 2013 (HY000): Lost connection to MySQL server during query
Server query_cache_ settings: ------- ------- ------- ---+--- ------+ ------- ------- ------- ---+--- ------+ min_res_ unit | 4096 | strip_comments | OFF | wlock_invalidat e | OFF | ------- ------- ------- ---+--- ------+
mysql> show variables like "query_cache_%";
+------
| Variable_name | Value |
+------
| query_cache_limit | 1048576 |
| query_cache_
| query_cache_size | 1048576 |
| query_cache_
| query_cache_type | ON |
| query_cache_
+------
6 rows in set (0.00 sec)
mysql>
How to reproduce: install percona server 5.7.15(or 5.7.16), turn query_cache on, select via 5.6 client,then same query via 5.7 client.
Config file:
cat /etc/my.cnf|egrep -v '^[[:space:]]*#'
[mysqld] buffer_ size = 2M /tmp/mysql. sock
join_buffer_size = 128M
sort_buffer_size = 2M
read_rnd_
query_cache_type=1
datadir=/data/mysql
socket=
symbolic-links=0
log-error= /spool/ logs/mysqld. log /var/run/ mysqld/ mysqld. pid
pid-file=
innodb_ lock_wait_ timeout = 15 max_dirty_ pages_pct = 90 stats_sample_ pages = 32
innodb_
innodb_
init-connect="SET NAMES cp1251" server= cp1251_ general_ ci set-server= cp1251
collation-
character-
[mysql] /tmp/mysql. sock character- set=cp1251
socket=
user=root
default-