Bug #101406 MySQL Router "resource temporarily unavailable" in log file - and client hanging
Submitted: 31 Oct 2020 10:01 Modified: 25 Aug 2021 22:44
Reporter: Rich Southward Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Router Severity:S2 (Serious)
Version:8.0.22 OS:Ubuntu (aws installation)
Assigned to: CPU Architecture:Any

[31 Oct 2020 10:01] Rich Southward
Description:
I have been using MySQL Router for the past few months without any issues, but I just ran apt-get upgrade on my Linux server, and mysql-router was upgraded from version 8.0.21 up to 8.0.22.

Since then, my connections are hanging when I try to pull back more than about 50 rows of data.

When I look in the mysqlrouter.log file, it show a series of DEBUG messages like this:

2020-10-30 12:55:14 routing DEBUG [7f259745a700] fd=12 write error: Resource temporarily unavailable

Incidentally, I have now uninstalled and re-installed the previous version and it's working as expected.

How to repeat:
Install MySQL Router 8.0.22, and set up a simple configuration (mine has 2 "routes") I've redacted the actual hostname.

One of the servers is on MySQL v. 5.7.28 and the other is on 8.0.17

[routing:serverone]
bind_address = 0.0.0.0
bind_port = 8989
destinations = ****:3306
routing_strategy = first-available

[routing:main]
bind_address = 0.0.0.0
bind_port = 8990
destinations = ****:3306
routing_strategy = first-available

Suggested fix:
Downgrading back to 8.0.21 prevents this behaviour.
[2 Nov 2020 14:13] MySQL Verification Team
Hi,

Are you using prepared statements or regular?
What client are you using?

thanks
Bogdan
[2 Nov 2020 16:26] Rich Southward
Originally when I first noticed it, I was calling directly from PHP (using a mixture of direct SQL and also calling stored procedures)

Once I had ascertained that it was an issue, I was then running with a simple command in MySQL Workbench.

select * from members limit n

If 'n' is 10, it works every time, but if I increase to more than 100, the connection then hangs, and there is the above 'DEBUG' message written in the log file.

I've rolled back to 8.0.21, but if you want to do any testing, let me know and I'll re-install 22.
[6 Nov 2020 5:01] MySQL Verification Team
Hi,

I think I missunderstood you the first time. What you are saying is that 

PHP/WB -> router 8.0.22 -> mysql 8.0.17

has this issue while if router is 8.0.21 you don't have a problem. 

We had a lot of changes in the .22 release on all sides, can you test please 

php/wb -> router 8.0.22 -> mysql 8.0.22

as this I'm failing to reproduce

thanks
Bogdan
[9 Nov 2020 10:03] Rich Southward
Hi,

One of the targets/routes is to a MySQL server 5.7.28, the other is to 8.0.17.

I could potentially upgrade the 8.0.17 server to 8.0.22, but I can't touch the other one (which is running 5.7.28) as it's a production server.

Should I not be able to have an instance of MySQL Router connecting to multiple MySQL servers of different versions?

Thanks
Rich
[9 Nov 2020 10:50] Stefan Kraam
Hi,
Same error appears when importing bigger db throw mysqlrouter 8.0.22, when using 8.0.21 everything works.
[9 Nov 2020 11:29] MySQL Verification Team
Hi,

> Should I not be able to have an instance of MySQL Router connecting to multiple MySQL servers of different versions?

You can, but since I'm having issues reproducing your problem, and since upgrading to .22 on the 8.0 server should happen anyhow doing that would tell us more about problem (if it works with 8.0.22 server or not).

I tested router 8.0.22 connecting to 8.0.22, 8.0.21 and 8.0.15 server and I did not reproduce the problem, but I'm doing this on normal lan and on Oracle cloud, not on AWS.

all best
Bogdan
[9 Nov 2020 11:36] MySQL Verification Team
Hi,

> Same error appears when importing bigger db throw mysqlrouter 8.0.22, when using 8.0.21 everything works

so with 8.0.21 router it all works ok, and with 8.0.22 router it does not.

I will redo the test but would appreciate if you can test in system where you can reproduce if 8.0.22 server with 8.0.22 router makes difference

thanks
[9 Nov 2020 11:47] MySQL Verification Team
Hi,

I managed to reproduce the problem. I think we know where the problem came from. Thanks for the report

Bogdan
[9 Nov 2020 13:34] Rich Southward
Great news, I'll keep my MySQL version at 8.0.17, so that I can confirm that it's fixed when the patch is released.

Do you think it will be a "hotfix" or will it have to wait until 8.0.23 ?

Thanks for looking into it.

Rich
[9 Nov 2020 13:35] MySQL Verification Team
Hi,

We do not release "hotfixes", you will have to wait for .23

all best
Bogdan
[3 Dec 2020 14:10] MySQL Verification Team
Bug #101826 is marked as duplicate of this one.
[9 Dec 2020 17:56] MySQL Verification Team
Bug #101536 is marked as a duplicate of this one
[30 Dec 2020 4:28] MySQL Verification Team
Bug #101285 is marked as duplicate of this one
[30 Dec 2020 4:29] MySQL Verification Team
Bug#101595 is marked as duplicate of this one
[22 Jan 2021 13:15] Fredrik Hemlig
Still not working in 8.0.23.
[5 Feb 2021 15:37] Keith Lammers
I can also confirm that this issue still exists in 8.0.23. I have a query that returns ~2500 rows. Sometimes it works, other times I still get the "Lost connection to MySQL Server during query" error, same as in 8.0.22.

If I connect directly to the cluster R/W instance, the query is successful every time. Rolling back to MySQL Router 8.0.21 also works fine.

One thing that's interesting is that in I get these log entries in the MySQL Router log with 8.0.23, I wasn't getting them in 8.0.22, not sure if it provides a clue:

2021-02-05 10:34:30 routing INFO [fa0] client::write() failed: A non-blocking socket operation could not be completed immediately. (system:10035)
[13 Apr 2021 9:17] Fredrik Hemlig
We're only having this issue when running MySQL router on Windows, the linux version works as expected. Tested both against a Percona's InnoDB cluster and Oracle's. Assuming this bug is in the Windows version of MySQL router.

Windows versions of MySQL router:
  8.0.20 - Not affected
  8.0 21 - Not tested
  8.0.22 - Affected
  8.0.23 - Affected
[16 Apr 2021 10:19] MySQL Verification Team
#103299 is marked as duplicate of this one
[25 Aug 2021 22:44] Rich Southward
Can anybody confirm whether this is working with MySQL Router 8.0.26 ?

I've currently got this package held-back in my Ubuntu installation, and would like to upgrade it if possible.
[30 Aug 2021 7:01] miu 123
Changes in MySQL Router 8.0.23
Large SQL statements that were larger than the send buffer would lead errors such as "write error: Resource temporarily unavailable" and drop the connection. (Bug #32081158)

This problem disappeared in version 8023