Forwarded connection on Writer terminated; try restarting transaction

0

Aws Aurora reader instance with write forwarding randomly throws an exception with message "Forwarded connection on Writer terminated; try restarting transaction" What does it mean, and how I should handle it?

asked 7 months ago303 views
1 Answer
0

I have verified documentation and error “Forwarded connection on Writer terminated; try restarting transaction” occurs when "Session on master/primary gets timed out or terminated (e.g. killed)”

[+] https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/aurora-global-database-write-forwarding.html#aurora-global-database-write-forwarding-isolation

Please check your application transactions workload/concurrency (or) try changing the values of the aurora_fbd_master_idle_timeout (or) aurora_fwd_writer_idle_timeout parameter to make it a little larger, for example: from 60 to 600.

[+] https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/aurora-global-database-write-forwarding.html#aurora-global-database-write-forwarding-params

Please test these parameters in the Cluster Parameter Group of your testing environment before modifying it on production. Since it is a dynamic parameter, there is no need to restart the DB instance.

I was able to reproduce the issue in my lab environment well.

++++++++++
On Replica:
++++++++++

mysql> use testnew;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

mysql> set aurora_replica_read_consistency = 'eventual';
Query OK, 0 rows affected (0.00 sec)

mysql> begin; insert into t1 values(1); select sleep(20);

====> Now, on master, kill the corresponding forwarded session. 

Query OK, 0 rows affected (0.01 sec)
Query OK, 1 row affected (0.02 sec)
+-----------+
| sleep(20) |
+-----------+
|         0 |
+-----------+
1 row in set (20.00 sec)

mysql> insert into t1 values(1);
ERROR 63982 (HY000): Forwarded connection on Writer terminated; try restarting transaction
mysql> 

++++++++++
On Master:
++++++++++

mysql> show full processlist;
+------+-----------------+--------------------+------+---------+-------+------------------------+-----------------------+
| Id   | User            | Host               | db   | Command | Time  | State                  | Info                  |
+------+-----------------+--------------------+------+---------+-------+------------------------+-----------------------+
|    5 | event_scheduler | localhost          | NULL | Daemon  | 15908 | Waiting on empty queue | NULL                  |
|   11 | rdsadmin        | localhost          | NULL | Sleep   |     0 |                        | NULL                  |
|   12 | rdsadmin        | localhost          | NULL | Sleep   |     0 |                        | NULL                  |
|   19 | rdsadmin        | localhost          | NULL | Sleep   |     0 |                        | NULL                  |
|   24 | rdsadmin        | localhost          | NULL | Sleep   |   182 |                        | NULL                  |
| 1387 | rdsadmin        | localhost          | NULL | Sleep   |     6 |                        | NULL                  |
| 1399 | admin           | 172.oo.oo.45:3oo36 | NULL | Query   |     0 | init                   | show full processlist |
+------+-----------------+--------------------+------+---------+-------+------------------------+-----------------------+
7 rows in set (0.00 sec)

mysql> show full processlist;
+------+-----------------+---------------------+---------+---------+-------+------------------------+-----------------------+
| Id   | User            | Host                | db      | Command | Time  | State                  | Info                  |
+------+-----------------+---------------------+---------+---------+-------+------------------------+-----------------------+
|    5 | event_scheduler | localhost           | NULL    | Daemon  | 15923 | Waiting on empty queue | NULL                  |
|   11 | rdsadmin        | localhost           | NULL    | Sleep   |     1 |                        | NULL                  |
|   12 | rdsadmin        | localhost           | NULL    | Sleep   |     0 |                        | NULL                  |
|   19 | rdsadmin        | localhost           | NULL    | Sleep   |     5 |                        | NULL                  |
|   24 | rdsadmin        | localhost           | NULL    | Sleep   |   197 |                        | NULL                  |
| 1387 | rdsadmin        | localhost           | NULL    | Sleep   |    12 |                        | NULL                  |
| 1399 | admin           | 172.oo.oo.45:3oo36  | NULL    | Query   |     0 | init                   | show full processlist |
| 1407 | admin           | 172.oo.oo.183:5oo70 | testnew | Sleep   |     4 | NULL                   | NULL                  |
+------+-----------------+---------------------+---------+---------+-------+------------------------+-----------------------+
8 rows in set (0.00 sec)

mysql> show full processlist;
+------+-----------------+---------------------+---------+---------+-------+------------------------+-----------------------+
| Id   | User            | Host                | db      | Command | Time  | State                  | Info                  |
+------+-----------------+---------------------+---------+---------+-------+------------------------+-----------------------+
|    5 | event_scheduler | localhost           | NULL    | Daemon  | 15927 | Waiting on empty queue | NULL                  |
|   11 | rdsadmin        | localhost           | NULL    | Sleep   |     1 |                        | NULL                  |
|   12 | rdsadmin        | localhost           | NULL    | Sleep   |     0 |                        | NULL                  |
|   19 | rdsadmin        | localhost           | NULL    | Sleep   |     9 |                        | NULL                  |
|   24 | rdsadmin        | localhost           | NULL    | Sleep   |   201 |                        | NULL                  |
| 1387 | rdsadmin        | localhost           | NULL    | Sleep   |     1 |                        | NULL                  |
| 1399 | admin           | 172.oo.oo.45:3oo36  | NULL    | Query   |     0 | init                   | show full processlist |
| 1407 | admin           | 172.oo.oo.183:5oo70 | testnew | Sleep   |     8 | NULL                   | NULL                  |
+------+-----------------+---------------------+---------+---------+-------+------------------------+-----------------------+
8 rows in set (0.00 sec)

mysql> show full processlist;
+------+-----------------+---------------------+---------+---------+-------+------------------------+-----------------------+
| Id   | User            | Host                | db      | Command | Time  | State                  | Info                  |
+------+-----------------+---------------------+---------+---------+-------+------------------------+-----------------------+
|    5 | event_scheduler | localhost           | NULL    | Daemon  | 15929 | Waiting on empty queue | NULL                  |
|   11 | rdsadmin        | localhost           | NULL    | Sleep   |     1 |                        | NULL                  |
|   12 | rdsadmin        | localhost           | NULL    | Sleep   |     0 |                        | NULL                  |
|   19 | rdsadmin        | localhost           | NULL    | Sleep   |     1 |                        | NULL                  |
|   24 | rdsadmin        | localhost           | NULL    | Sleep   |   203 |                        | NULL                  |
| 1387 | rdsadmin        | localhost           | NULL    | Sleep   |     3 |                        | NULL                  |
| 1399 | admin           | 172.oo.oo.45:3oo36  | NULL    | Query   |     0 | init                   | show full processlist |
| 1407 | admin           | 172.oo.oo.183:5oo70 | testnew | Sleep   |    10 | NULL                   | NULL                  |
+------+-----------------+---------------------+---------+---------+-------+------------------------+-----------------------+
8 rows in set (0.00 sec)

mysql> CALL mysql.rds_kill(1407);
Query OK, 0 rows affected (0.00 sec)

mysql> 
AWS
SUPPORT ENGINEER
answered 7 months ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions