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.