julien-duponchelle / julien-duponchelle/python-mysql-replication
Duplicate event when using `auto_position` and losing mysql connection
- Dominant language
- Python
- Stars
- 2.4k
- Forks
- 690
- PR merge metrics
- No merged PRs in 30d
Description
Hi,
We're using `auto_position` option and we notice some strange behavior when the MySQL connection drops.
Our setup is as follow:
One MySQL master with 2 read-only replicas.
We're doing the replication with `pymysql-replication` from a vip pointing to one of the two replica.
We get the issue when the vip switch from one replica to another (and thus the connection is lost).
Because the error code is in `MYSQL_EXPECTED_ERROR_CODES`, this snippet of code mark the `self.__connected_stream` as False:
```python
try:
if pymysql.__version__ < LooseVersion("0.6"):
pkt = self._stream_connection.read_packet()
else:
pkt = self._stream_connection._read_packet()
except pymysql.OperationalError as error:
code, message = error.args
if code in MYSQL_EXPECTED_ERROR_CODES:
self._stream_connection.close()
self.__connected_stream = False
continue
raise
```
But then on the next call to `__iter__()` the `__connect_to_stream()` function is called again with the same `auto_position` so we restart processing binlogs from this old GtidSet even though we might have already processed several events between the first start and the connection dropping.
So we end up processing the same events twice.
To prevent that we would need a way to keep track of already processed Gtid or be able to raise the error to the calling code so it can decide to retry the connection with a more up-to-date GtidSet.
Contributor guide
Assessment
This issue has not been assessed yet.