Missing events from new databases (MySQL 8)

[raven-kg]

Hi there,
After upgrading my MySQL server from 5.7 to 8.0, I had a problem that events from newly created databases do not appear in the data read by the binlog reader. I used this simple code to test this and found that the data does not appear in the output if the database (table?) is created when this code is already running. And they does appear after if the code is restarted, but only the ones that occur after a restart.

import config as cfg
import logging
from pymysqlreplication import BinLogStreamReader
from pymysqlreplication.row_event import (
    DeleteRowsEvent,
    UpdateRowsEvent,
    WriteRowsEvent,
)

while True:
    try:
        stream = BinLogStreamReader(
            connection_settings={'host': cfg.client.host, 'user': cfg.client.user, 'passwd': cfg.client.password},
            server_id=11111, blocking=True, resume_stream=True, only_events=[DeleteRowsEvent, WriteRowsEvent, UpdateRowsEvent])
        for event in stream:
            if event.table != 'posts':
                continue
            for row in event.rows:
                print(row)
    except Exception as e:
        logging.exception(e)

MySQL server is AWS RDS instance, binlog/gtid settings are below:

log_bin = ON
binlog_format  = ROW
binlog_row_image  = FULL
sync_binlog  = 0
...
enforce_gtid_consistency = OFF
gtid_mode  = OFF_PERMISSIVE

mysql-replication is also upgraded to latest version

[dongwook-chan]

@raven-kg
To better understand and address this issue, kindly provide the following additional details:

1. The exact minor version of MySQL with which you are encountering this problem.
2. The Data Definition Language (DDL) script that you used for the creation of the problematic database.
3. The type of storage engine being used for the database in question.

In the meantime, I suggest adjusting your system variables as follows and testing once again:

enforce_gtid_consistency = ON
gtid_mode  = ON

This information and action should facilitate a more comprehensive and precise diagnosis of the issue by the maintainers.

[raven-kg]

@dongwook-chan

1. 8.0.32 MySQL Community Server
2. data is imported by mysqldump
3. InnoDB

And we were able to solve this issue by adding autocommit=True to connection_settings. i think this behavior should probably be documented somehow.

[dongwook-chan]

@raven-kg
Thank you for your feedback and for sharing the version details and your workaround.

However, as it stands, the information supplied doesn't provide a full picture for us to adequately address the issue. To reach a conclusive solution and update the documentation or code accordingly, we need a more detailed scenario to reproduce the problem.

Could you please furnish us with the following additional information:

1. The options you utilized with the mysqldump command.
2. The size of the dump file.
3. The session variable set on the session which loaded the dump file to MySQL.

With this extra detail, we will be better equipped to find an appropriate solution. For now, we will keep this issue open for further analysis and discussion.

[raven-kg]

@dongwook-chan

1. No extra options, just 'mysql $dbname < $dump_file'. Client version is older than server - 10.1.48-MariaDB
2. File size is 47Mb
3. I'm not sure I understand this. Do you mean the variables set at the beginning of the dump file?

[dongwook-chan]

@raven-kg

3. Yes, could you provide the variables and its values?

Did you run python-mysql-replication after the dump file was loaded or was python-mysql-replication connected to MySQL all along? It would be helpful if you answer this question for both cases where you had events missing and when you set autocommit=True to connection_settings.

[raven-kg]

@dongwook-chan

3. Here they are:
   /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!50503 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

The script utilizing python-mysql-replication runs as a daemon and is not being restarted every time a new database is created. It works with the newly created database after restart, but it is not possible to restart it every time a database is created because of the possibility of data loss (the script serves hundreds of databases).

[dongwook-chan]

@raven-kg
I'll run some tests using dump files and let you know if I find the source of the error.

[sean-k1]

@raven-kg maybe this issue is same with your issue

can u try like this and tell me know how it works?
cc. @dongwook-chan

[raven-kg]

@sean-k1 it doesn't help in my case

[sean-k1]

@raven-kg
did you do this on main branch? (not latset version)

[sean-k1]

@raven-kg we release new version (0.44.0)
Can you try this latest version?

[raven-kg]

@sean-k1 i tried to run on 0.42.0 with the changes backported from your commit

[raven-kg]

@sean-k1 same with 0.44.0

[sean-k1]

@raven-kg

@dongwook-chan

1. 8.0.32 MySQL Community Server
2. data is imported by mysqldump
3. InnoDB

And we were able to solve this issue by adding autocommit=True to connection_settings. i think this behavior should probably be documented somehow.

you said solve this issue by adding autocommit=True to connection_settings.
and we added this code on 0.44.0 but It does not working right?

so i can not understand this one..
Sorry I couldn't help you :(