MySQL Bugs: #121504: FEDERATED: ER_NET_ERROR_ON_WRITE (1160) from closing a dead remote connection is returned to an unrelated statement

Bug #121504	FEDERATED: ER_NET_ERROR_ON_WRITE (1160) from closing a dead remote connection is returned to an unrelated statement
Submitted:	14 Oct 12:30
Reporter:	Yunus Shaikh	Email Updates:
Status:	Open	Impact on me:	None
Category:	MySQL Server: Federated storage engine	Severity:	S3 (Non-critical)
Version:	8.4.11	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
A statement that never references a FEDERATED table fails with

ERROR 1160 (08S01): Got an error writing communication packets

when, while opening its own tables, it evicts an idle FEDERATED table from the table cache, and that table's remote connection has died.

How the remote connection dies in production: 
1. The remote server closes the idle FEDERATED connection after wait_timeout. Since 8.0.24 it first sends ER_CLIENT_INTERACTION_TIMEOUT, which the local side never reads. 
2. Later, a TCP keepalive probe from the local side gets an RST, because the remote socket no longer exists.

How to repeat:
1. Start the server and create the objects:

docker run -d --name fed84 \
  --sysctl net.ipv4.tcp_keepalive_time=2 --sysctl net.ipv4.tcp_keepalive_intvl=1 --sysctl net.ipv4.tcp_fin_timeout=1 \
  -e MYSQL_ALLOW_EMPTY_PASSWORD=yes mysql:8.4 \
  --federated --mysql-native-password=ON --table-open-cache=1 --table-open-cache-instances=1 --wait-timeout=3

# wait until the server accepts TCP connections (about 30 s)

until docker exec fed84 mysql -uroot -h127.0.0.1 -e "SELECT 1" >/dev/null 2>&1; do sleep 2; done

docker exec -i fed84 mysql -uroot -e "
CREATE DATABASE r;
CREATE TABLE r.t (id INT PRIMARY KEY); INSERT INTO r.t VALUES (1);
CREATE TABLE r.other (id INT PRIMARY KEY); INSERT INTO r.other VALUES (100);
CREATE USER fed@'%' IDENTIFIED WITH mysql_native_password BY 'fedpw'; GRANT SELECT ON r.* TO fed@'%';
CREATE SERVER s FOREIGN DATA WRAPPER mysql OPTIONS (HOST '127.0.0.1', PORT 3306, USER 'fed', PASSWORD 'fedpw', DATABASE 'r');
CREATE TABLE r.f (id INT PRIMARY KEY) ENGINE=FEDERATED CONNECTION='s/t';"

2. Run one statement at a time:

$ docker exec -i fed84 mysql -uroot -e "SELECT VERSION(), @@version_comment"
VERSION()   @@version_comment
8.4.11  MySQL Community Server - GPL

$ docker exec -i fed84 mysql -uroot -e "SELECT SUM_ERROR_RAISED FROM performance_schema.events_errors_summary_global_by_error WHERE ERROR_NUMBER=1160"
SUM_ERROR_RAISED
0

$ docker exec -i fed84 mysql -uroot -e "SELECT * FROM r.f"
id
1

# wait 10 seconds, run nothing else

$ docker exec -i fed84 mysql -uroot -e "SELECT * FROM r.other"
ERROR 1160 (08S01) at line 1: Got an error writing communication packets

$ docker exec -i fed84 mysql -uroot -e "SELECT * FROM r.other"
id
100

$ docker exec -i fed84 mysql -uroot -e "SELECT SUM_ERROR_RAISED FROM performance_schema.events_errors_summary_global_by_error WHERE ERROR_NUMBER=1160"
SUM_ERROR_RAISED
1

r.other is a plain InnoDB table; the statement never references r.f.

Expected: SELECT * FROM r.other returns the row. Any error from closing the cached FEDERATED connection should be discarded or logged, not returned to this statement.

Actual: ERROR 1160. The retry succeeds because the dead connection was removed during the failed attempt.

Additional evidence (same server, MySQL 8.4.11):

a) Querying the FEDERATED table itself over the same dead link succeeds, because real_query() hides the error (2 of 2 runs):

$ docker exec -i fed84 mysql -uroot -e "SELECT * FROM r.f"
id
1
# wait 10 seconds
$ docker exec -i fed84 mysql -uroot -e "SELECT * FROM r.f"
id
1

b) When the victim statement is an INSERT, it fails and the row is not written:

$ docker exec -i fed84 mysql -uroot -e "INSERT INTO r.other VALUES (5)"
ERROR 1160 (08S01) at line 1: Got an error writing communication packets
$ docker exec -i fed84 mysql -uroot -e "SELECT * FROM r.other"
id
100

c) FLUSH LOCAL TABLES r.f after the link dies, before the eviction: no error (line 1626):

$ docker exec -i fed84 mysql -uroot -e "FLUSH LOCAL TABLES r.f"
$ docker exec -i fed84 mysql -uroot -e "SELECT * FROM r.other"
id
100

d) Control: the same steps without the --sysctl options (kernel default tcp_keepalive_time=7200, so no RST within the test window): no error. A FIN-only close lets the COM_QUIT write succeed.

e) Not needed for the 8.4 report, but worth noting: on MySQL 8.0.46, test (a) also fails with 1160 (3 of 3 runs), so on 8.0 even statements on the FEDERATED table itself are affected.

Workaround: periodically run FLUSH LOCAL TABLES <federated tables> (the FLUSH path discards close errors), or retry statements that fail with 1160.