julien-duponchelle / julien-duponchelle/python-mysql-replication

Duplicate event when using `auto_position` and losing mysql connection

Open
#367 1 comment 2 reactions 0 assignees View on GitHub
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.