MySQL Bugs: #107982: router hangs for big SQL

Bug #107982	router hangs for big SQL
Submitted:	26 Jul 2022 13:50	Modified:	31 Aug 2022 17:10
Reporter:	Cristi Coteț	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Router	Severity:	S3 (Non-critical)
Version:	8.0.29 - 8.0.30	OS:	CentOS
Assigned to:		CPU Architecture:	x86

Description:
For big SQL queries ( > 16 Mb ) connection hangs.
Using strace to the php process I can see that router stops accepting new data on connection at some moment:

...
sendto(5, "[removed]"..., 16464686, MSG_DONTWAIT, NULL, 0) = 73088
sendto(5, "[remove]"..., 16355054, MSG_DONTWAIT, NULL, 0) = -1 EAGAIN (Resource temporarily unavailable)
poll([{fd=5, events=POLLOUT}], 1, 86400000

The proces freezes at that moment. 
On the MySQL Router API is shows the following figures for all attempts using the same data (test SQL has one binary column with 30Mb data)
"bytesFromServer": 267,
"bytesToServer": 16777772, - this is 556 bytes over 16Mb

On MySQL Router 8.0.28 the SQL works correctly.
If I lower the size of the binary column to 15 Mb (total SQL under 16Mb) it also works on MySQL Router 8.0.29+ 

Server max_allowed_packet: 67108864 
Working version 8.0.28 was tested against same MySQL Database.

How to repeat:
Try to execute a SQL query bigger than 16 Mb through MySQL Router 8.0.29 or 8.0.30

Suggested fix:
We should be able to increase this limit of 16Mb. 

It I try to put max_allowed_packet in router default section I got this warning:

option 'DEFAULT.max_allowed_packet' is not supported

Hi,

Thanks for your report. We are working on this issue already.

kind regards

Hi team!
We have the same problem using mysqldump with large tables. The records have databin fields that exceed 16M.
The process remains running on the server until timeout without any previous error.
Could you tell me when this problem will be solved?
We just upgraded to 8.0.30 from version 8.0.26 and are considering downgrading.
We also have additional problems with TLSv1 and TLSv1.1 connections.
Thank you very much. Regards

Posted by developer:
 
Added the following note to the MySQL Router 8.0.31 release notes:

Under certain circumstances, processing SQL statements of 16MB or larger, MySQL Router could become unresponsive.

Hi,

The expected resolution is for the problem to be fixed. And not only under certain circumstances but it always happens for SQL 's bigger than 16Mb.

Thank you

Posted by developer:
 
Updated release note by request:

Processing SQL statements of 16MB or larger caused MySQL Router to become unresponsive.

Hi Team!
To try to solve this problem, we have connected a router in 8.0.26 version with an innoDB Cluster on 8.0.30 version and it seems to solve the problem. 
We don't know if this will cause other problems, but we haven't detected them yet. Please, if you know of any known problem for making this type of mixed version, we appreciate your information.
Regards