I have setup a write-forward replication from Aurora serverless v2 instance in Singapore to an instance in Ireland, and while running various tests, I discovered a problem when there is a query with large (20Mb+) blob from Singapore; the write forward to Ireland master DB fails! Querys with small blob work fine, but when the blob size increases, I'm starting to see an exception happening with message "mysql.connector.errors.OperationalError: 1159 (08S01): Failed to send write forwarding request to writer". This error happens with both our web SW (Java/Spring/Hibernate) and the simple demo python app I created to demonstrate the problem (code below), with different connectors (mysqlconnector/Java and python3-mysql.connector), so this is clearly a problem between the replicated DBs.
I have tried to alter various Aurora parameters via DB Cluster Parameter Groups, for example:
aurora_fwd_writer_idle_timeout -> 360
aurora_fwd_writer_max_connections_pct -> 50
max_allowed_packet -> 1073741824
net_read_timeout -> 300
net_write_timeout -> 300
None helped. Would anyone have any advice how to make write-forwards work with "larger" blobs?
Steps to reproduce:
- create a Aurora Mysql cluster and writer instance eg. in Ireland: engine version 3.04.1, Serverless v2, 0.5-6 ACUs
- Add AWS Region to create a Global Aurora Mysql DB and reader instance eg. in Singapore: Serverless v2, 0.5-6 ACUs. Turn on global write forwarding to Ireland instance.
- create a table to the writer (Ireland) DB:
CREATE TABLE `PROJECTKEY_BYTES2` (
`id` bigint NOT NULL,
`bytes` longblob
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
- Configure the minimal demoapp below to talk to the Singapore DB endpoint (change also other connection params like user, passwd etc)
import mysql.connector
import os
if __name__ == "__main__":
# generate blob
blob = bytearray(os.urandom(15000000))
# Initialize database connection
mydb = mysql.connector.connect(
host="production-cluster-****-ro-****.ap-southeast-1.rds.amazonaws.com",
port=3306,
user="****",
password='****',
database="****",
ssl_disabled=True,
init_command='SET aurora_replica_read_consistency=SESSION'
)
# It does not matter what aurora_replica_read_consistency is used, session, global, or eventual all cause the error...
mycursor = mydb.cursor()
insertQuery = ("INSERT INTO PROJECTKEY_BYTES2 (id, bytes) VALUES (%s, %s)")
insertData = (1, blob)
mycursor.execute(insertQuery, insertData)
mydb.commit()
mydb.close()
- Run this app in an EC2 instance that has access to the reader instance with write-forwarding (Singapore)
----> Expected result would be that INSERT clause with a BLOB would success
----> I'm seeing:
ubuntu@:~$ python3 replicationFailureTester.py
Traceback (most recent call last):
File "/usr/lib/python3/dist-packages/mysql/connector/connection_cext.py", line 661, in cmd_query
self._cmysql.query(
_mysql_connector.MySQLInterfaceError: Failed to send write forwarding request to writer
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/home/ubuntu/replicationFailureTester.py", line 34, in <module>
mycursor.execute(insertQuery, insertData)
File "/usr/lib/python3/dist-packages/mysql/connector/cursor_cext.py", line 374, in execute
result = self._cnx.cmd_query(
^^^^^^^^^^^^^^^^^^^^
File "/usr/lib/python3/dist-packages/mysql/connector/opentelemetry/context_propagation.py", line 74, in wrapper
return method(cnx, *args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/lib/python3/dist-packages/mysql/connector/connection_cext.py", line 669, in cmd_query
raise get_mysql_exception(
mysql.connector.errors.OperationalError: 1159 (08S01): Failed to send write forwarding request to writer
ubuntu@:~$
Any help highly appreciated...
Thanks for you answer! Comments inlined below...:
Note that our current production system is 2x Mysql 5.7 instances with two-way replication set up manually (ie. master-master) and there has NOT been any problems with UPDATEs or INSERTs with larger BLOBs. So I figure this is an issue with Aurora. Obviously, the main reason that I would like to use the Aurora is the easiness of write-forwarding from app perspective, just as it is advertized by AWS, as we would be able to have more reader (with write-forwarding) without the complexity on the app end.