Duplicate event when using `auto_position` and losing mysql connection

[MikaYuoadas]

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:

            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.

[Samira-El]

I ran into the same issue but with MariaDB Gtid. I solved it by updating auto_position property of the BinlogStreamReader instance whenever a new Gtid event is encountered.