MySQL Bugs: #101406: MySQL Router "resource temporarily unavailable" in log file

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

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.

Hi,

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

thanks
Bogdan

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.

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

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

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

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

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

Hi,

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

Bogdan

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

Hi,

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

all best
Bogdan

Bug #101826 is marked as duplicate of this one.

Bug #101536 is marked as a duplicate of this one

Bug #101285 is marked as duplicate of this one

Bug#101595 is marked as duplicate of this one

Still not working in 8.0.23.

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)

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

#103299 is marked as duplicate of this one

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.

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