Percona Server moved to https://jira.percona.com/projects/PS

Query caching with two different clients causes errors

Bug #1623040 reported by Kayla on 2016-09-13

This bug affects 2 people

	Status	Importance	Assigned to
MySQL Server	Unknown	Unknown	mysql-bugs #84639
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://bugs.mysql.com/bug.php?id=6511

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:

Revision history for this message

Alexander A. Gnatyna (gnatyna) wrote on 2017-01-20:

Got same issue:
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-1.el7.elrepo.x86_64 #1 SMP Wed Sep 7 11:44:03 EDT 2016 x86_64 x86_64 x86_64 GNU/Linux

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:
mysql> show variables like "query_cache_%";
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1048576 |
| query_cache_strip_comments | OFF |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+---------+
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]
join_buffer_size = 128M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M
query_cache_type=1
datadir=/data/mysql
socket=/tmp/mysql.sock

symbolic-links=0

log-error=/spool/logs/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

innodb_lock_wait_timeout = 15
innodb_max_dirty_pages_pct = 90
innodb_stats_sample_pages = 32

init-connect="SET NAMES cp1251"
collation-server=cp1251_general_ci
character-set-server=cp1251

[mysql]
socket=/tmp/mysql.sock
user=root
default-character-set=cp1251

Got same issue:
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-1.el7.elrepo.x86_64 #1 SMP Wed Sep 7 11:44:03 EDT 2016 x86_64 x86_64 x86_64 GNU/Linux

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:
mysql> show variables like "query_cache_%";
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 1048576 |
| query_cache_strip_comments   | OFF     |
| query_cache_type             | ON      |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+
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]
join_buffer_size = 128M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M
query_cache_type=1
datadir=/data/mysql
socket=/tmp/mysql.sock

symbolic-links=0

log-error=/spool/logs/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

innodb_lock_wait_timeout        = 15
innodb_max_dirty_pages_pct      = 90
innodb_stats_sample_pages       = 32

init-connect="SET NAMES cp1251"
collation-server=cp1251_general_ci
character-set-server=cp1251

[mysql]
socket=/tmp/mysql.sock
user=root
default-character-set=cp1251

Revision history for this message

Alexander A. Gnatyna (gnatyna) wrote on 2017-01-20:

Same issue on linux server:
Linux 4.8.7-2.el7.centos.x86_64 #1 SMP Fri Dec 2 17:22:11 MSK 2016
Server version: 5.7.16-10-log Percona Server (GPL), Release 10, Revision a0c7d0d

Revision history for this message

Alexander A. Gnatyna (gnatyna) wrote on 2017-01-26:

I've submitted this bug to upstream https://bugs.mysql.com/bug.php?id=84639

Laurynas Biveinis (laurynas-biveinis) on 2017-01-27

tags:

added: upstream

Revision history for this message

Shahriyar Rzayev (rzayev-sehriyar) wrote on 2018-01-25:

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1741