Bug #101595 Query with router :Error Code: 2013.Lost connection to MySQL server during query

Submitted: 13 Nov 2020 7:41 Modified: 19 Jan 2021 16:45
Reporter: lionel mazeyrat Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Router Severity:S1 (Critical)
Version:8.0.22 OS:Windows (windows server 2016)
Assigned to: CPU Architecture:x86

[13 Nov 2020 7:41] lionel mazeyrat
Description:
I've upgraded a 3 node Group Replication InnoDB cluster from 8.0.20.
The 3 nodes are ONLINE.
1 * R/W and 2 * R/O

But when I query (SELECT) with the workbench to the local routeur (127.0.0.1:6446) most of the request fail with the error code :
Error Code: 2013. Lost connection to MySQL server during query	0.015 sec

Example of request in error :

SELECT E.*,T.systeme,T.designation,T.udtModel from eqt E LEFT JOIN type_eqt T ON E.typeEqt=T.typeEqt WHERE T.systeme like "%" and T.typeEqt like "%" order by E.pathEqt asc

SELECT * FROM bdref.tag_surcharge;

These request doesnt return many lines.
If I execute many times the request, sometimes 1 over 10 is OK.

In fact when the request is OK, the Duration displayed in the workbench is 0.000sec

If I use a direct connection (127.0.0.1:3306) with the workbench to the local mysql DB, all the request are OK !

I suspect some timing issue in the routeur....
My routeur file doesn't change from 8.0.20 to 8.0.22 upgrade.

# File automatically generated during MySQL Router bootstrap
[DEFAULT]
logging_folder=C:/Program Files/MySQL/MySQL Router 8.0/conf/log
runtime_folder=C:/Program Files/MySQL/MySQL Router 8.0/conf/run
data_folder=C:/Program Files/MySQL/MySQL Router 8.0/conf/data
keyring_path=C:/Program Files/MySQL/MySQL Router 8.0/conf/data/keyring
master_key_path=C:/Program Files/MySQL/MySQL Router 8.0/conf/mysqlrouter.key
connect_timeout=15
read_timeout=30
dynamic_state=C:/Program Files/MySQL/MySQL Router 8.0/conf/data/state.json

[logger]
level = INFO

[metadata_cache:gtcCluster]
router_id=2
user=mysql_router2_2h02mgtzxjk2
metadata_cluster=gtcCluster
ttl=0.5

[routing:gtcCluster_default_rw]
bind_address=0.0.0.0
bind_port=6446
destinations=metadata-cache://gtcCluster/default?role=PRIMARY
routing_strategy=first-available
protocol=classic

[routing:gtcCluster_default_ro]
bind_address=0.0.0.0
bind_port=6447
destinations=metadata-cache://gtcCluster/default?role=SECONDARY
routing_strategy=round-robin-with-fallback
protocol=classic

[routing:gtcCluster_default_x_rw]
bind_address=0.0.0.0
bind_port=6448
destinations=metadata-cache://gtcCluster/default?role=PRIMARY
routing_strategy=first-available
protocol=x

[routing:gtcCluster_default_x_ro]
bind_address=0.0.0.0
bind_port=6449
destinations=metadata-cache://gtcCluster/default?role=SECONDARY
routing_strategy=round-robin-with-fallback
protocol=x

How to repeat:
I've upgraded a 3 node Group Replication InnoDB cluster from 8.0.20 to 8.0.22

issue request during my than 1sec with the router connection
[13 Nov 2020 8:38] lionel mazeyrat
I make additionnal test.

If I downgrade mysql router 8.0.22 by replacing bin and lib directory with bin and lib directory from mysql-router-8.0.20-winx64.zip
the same sql request are OK with the old router.
[17 Nov 2020 9:05] MySQL Verification Team
Hi,

If you connect directly to the mysql server (avoid router) do you have similar problem or everything works ok?

Thanks
Bogdan
[17 Nov 2020 9:23] lionel mazeyrat
If I connect directly to the mysql serveur (without the router), I don't have similar problem, and all is working fine.

(As a temporary workaround, I've downgraded the router to 8.0.20 and all is working fine with mysql 8.0.22 and router 8.0.20)
[17 Nov 2020 14:42] MySQL Verification Team
Thanks,

I'll redo the test. There is a big network layer change in the 8.0.22 so it is possible we introduced a problem, I just need to figure out how to easily reproduce

thanks
Bogdan
[17 Nov 2020 14:48] MySQL Verification Team
Hi,

I think we already have this reported and actually verified already by me. It happens if the result is "too big". 

Thanks for the report
Bogdan
[11 Dec 2020 8:51] Gary Yuang
MySQL Router use in centos meet the save problem
[11 Dec 2020 8:52] Gary Yuang
MySQL Router use in centos meet the save problem
[12 Dec 2020 8:19] Hrvoje Matijakovic
I'm seeing similar issue on Ubuntu:

3-node setup + 1 router

Running the following command on router:
ysbench --report-interval=1 --tables=2 --table-size=100000 --time=0 --events=0 --threads=1 --rand-type=pareto --db-driver=mysql --mysql-user=sysbench --mysql-password=Test1234# --mysql-host=mysql-router --mysql-port=6446 --mysql-db=sbtest --mysql-storage-engine=innodb /usr/share/sysbench/oltp_read_write.lua prepare 

creates the sbtest table but errors out with the same error:

WARNING: Both event and time limits are disabled, running an endless test
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Creating table 'sbtest1'...
Inserting 100000 records into 'sbtest1'
FATAL: mysql_drv_query() returned error 2013 (Lost connection to MySQL server during query) for query 'INSERT INTO sbtest1(k, c, pad) VALUES(28, '62566691066-77196085394-06513946709-74114332143-54584439988-61406381461-17577001759-87376225380-83143968575-19471108462', '55697510211-17962519444-05841532439-70216809702-95089282630'),(3954, '81358898272-55318303110 ...

Node config:
        [mysqld]
        plugin_load_add='group_replication.so'
        server_id={{ 10000 | random }}
        binlog_checksum=NONE
        enforce_gtid_consistency=ON
        gtid_mode=ON
        group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
        group_replication_start_on_boot=OFF
        relay_log={{ ansible_hostname }}-relay-bin
        innodb_dedicated_server=ON
        group_replication_ip_allowlist='192.168.80.71/24, 192.168.80.72/24, 192.168.80.73/24, 192.168.80.74/24'
[12 Dec 2020 8:33] Hrvoje Matijakovic
Running the same sysbench command on the node is finishing without errors:
root@ps-node1:~# sysbench --report-interval=1 --tables=2 --table-size=100000 --time=0 --events=0 --threads=1 --rand-type=pareto --db-driver=mysql --mysql-user=sysbench --mysql-password=Test1234# --mysql-host=ps-node1 --mysql-port=3306 --mysql-db=sbtest --mysql-storage-engine=innodb /usr/share/sysbench/oltp_read_write.lua prepare
WARNING: Both event and time limits are disabled, running an endless test
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Creating table 'sbtest1'...
Inserting 100000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...
Creating table 'sbtest2'...
Inserting 100000 records into 'sbtest2'
Creating a secondary index on 'sbtest2'...
[15 Dec 2020 5:24] Toshinori SUGITA
> https://bugs.mysql.com/?id=101285
[29 Dec 2020 10:52] Wallace Ko
Same problem on Ubuntu 20.04. You may consider rolling back to 8.0.21 (pay attention to the difference: https://dev.mysql.com/doc/relnotes/mysql-router/en/news-8-0-22.html)

@Bogdan I wonder if this issue is a duplicate of https://bugs.mysql.com/bug.php?id=101406
[30 Dec 2020 4:27] MySQL Verification Team
@hrvoje, it is because sysbench does not do it with big packets, only with big packets / big resultsets or big inserts you reproduce this

Thanks
Bogdan
[30 Dec 2020 4:29] MySQL Verification Team
Dupliate of Bug#101406
[30 Dec 2020 4:32] MySQL Verification Team
@Wallace, yes that's the base one in public bugs database

@Toshinori, same root cause, base bug is 101406

all best
Bogdan
[19 Jan 2021 16:45] lionel mazeyrat
Do you know if this bug is fixed in mysql router 8.0.23 ?
[4 Jun 2021 6:24] peng gao
is fixed  8.0.24 or 8.0.25 ?

Network transfers larger than the socket send buffer, such as large result sets or queries, could terminate the connection. (Bug #32543049)

this bug? thanks.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.069 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.