Replication

[mxAsad]

I changed the logs folder to a new folder and I gave the binlog file name and position myself, but despite the terminal saying that we are parsing the latest records. The replication itself is starting from the start again. Even though I gave the proper binlog file and it's position

[bakwc]

Could you please describe what are you trying to achieve?

[mxAsad]

So what happened was that the replication process was going on but during the replication a column was added to a table that caused the replication to fail, so i fixed the table structure on both sides and started the replication again and set it the start state manually to the required binlog file and position. But the replication start from the beginning again. Plus I have figured out the issue after i decoded the state file. What happens is that when the code starts it create the state file of the entire db and create the tables at the same time as well but when dealing with large databases where replication can go on for days. If a change happens in the table schema during replication. It will not match with state file created at the very beginning. Ideally, we should update the state file one table at a time. Create a table and insert into it and only then put meta date related to the next table in the state file. That way, we can handle the scenario where a change occurs during the initial replication. We are updating the state file in the initial replication only after the first replication is done. We should do it table by table. That way, we can solve the problem. It is not that your ddl statements handling is not working the problem is that when dealing with large databases it is not robust enough. Instead of a quick scan of the entire schema we should have a scan per table.

[bakwc]

Ah.. i see. So the issue is that schema has changed during running the initial replication. I am afraid updating table structure table by table may also not help, eg. in the situation when schema changed on the table we currently run initial replication.
I will think what could be done here, thank you for the report!

[mxAsad]

What is the difference in the last binlog file and pos in state.json and state pickle file. How and where they are used

[mxAsad]

Also if you don't mind can you tell me how you read binlogs

[mxAsad]

I modified the pickle file to according to my new schema. The pickle file has the start position at log file 173 but the json state file has the log position at 163. I need to know how they work

[bakwc]

What is the difference in the last binlog file and pos in state.json and state pickle file. How and where they are used

1. (binlog_file, binlog_position) is a transaction identifier. Mysql splits all binlogs into multiple files. Every file contains a lot of transactions. Eg:

(binlog_file_1, 1948)
(binlog_file_1, 1949)
(binlog_file_1, 1950)
(binlog_file_2, 1960)
(binlog_file_2, 1965)
...

2. state.json - this is a file with state of BinlogReplicator process. BinlogReplicator is responsible fore read data from mysql and store it inside binlog/DATABASE_NAME/*.bin. It only reads data from mysql and store it locally. After he read transaction from mysql and stored into a local file - he write it's state into state.json

3. state.pckl - this is a file with state of DbReplicator process. DbReplicator is responsible for initial replication, and after initial replication finished - it is reading local files that were previously created by BinlogReplicator, and writing them into ClickHouse. After data uploaded to clickhouse it updates his own state.pckl file. There are multiple DbReplicator processes, each process responsible for replication of a single database (for cases when you need to replicate multiple databases).

[mxAsad]

I have understood the pckl file and quite a bit of code. I know now how I can resume from where I left. Thank you fro your help so far. Can you also tell how to handle the situation where we make a temp data base and drop the original data base on the target. The thing is I don't want to make a temp database but start insertion in the database already available in target after restarting the process. I already have an idea on how to handle any problem that occur due to it but if the code continue to drop my data again and again I won't get anywhere. Looking forward to hearing from you.

[bakwc]

Temp database is created due initial replication only. The reason was to avoid dropping main database until data is fully replicated. Otherwise we will have a situation that customers will not see any data at all, that's not good.

To avoid using temporary database you need:

1. self.target_database_tmp = self.target_database + '_tmp' replace to self.target_database_tmp = self.target_database
2. Comment-out following block of code:

        logger.info(f'initial replication - swapping database')
        if self.target_database in self.clickhouse_api.get_databases():
            self.clickhouse_api.execute_command(
                f'RENAME DATABASE `{self.target_database}` TO `{self.target_database}_old`',
            )
            self.clickhouse_api.execute_command(
                f'RENAME DATABASE `{self.target_database_tmp}` TO `{self.target_database}`',
            )
            self.clickhouse_api.drop_database(f'{self.target_database}_old')
        else:
            self.clickhouse_api.execute_command(
                f'RENAME DATABASE `{self.target_database_tmp}` TO `{self.target_database}`',
            )

Still I don't recommend using this approach. If for some reason you will need to recreate replication from scratch - a previously replicated database will be dropped completely, and your clients will not be able to access data until the initial replication is fully finished.

[mxAsad]

Thanks
And lets say my replication stopped somewhere and i have GBs of data already loaded , and suddenly replication stopped due to any error or some reason.

The situation is i also lost my previous state logs , and in this case if i run the initial replication, i will drop all GBs of data already available from the clickhouse main DB and replication begins from the start with the temp DB.

Else , what i wanted in my scenarios , that if the replication stopped somewhere and the some data is loaded already and if the lose my Logs or they get corrupted …..

My initial replication should not drop any tables of database , just start from the maximum PK to onwards till the end and get ready for the binLogs rull_all Flag at the end….

[bakwc]

My initial replication should not drop any tables of database , just start from the maximum PK to onwards till the end and get ready for the binLogs rull_all Flag at the end….

This is not reliable approach. There could be delete / update operations while you running initial replication.
The binary log will be applied since the time you started the replication.
Eg.:
16:00 - initial replication started, binary log position is (logfile1, 100)
16:15 - delete operation was applied to some row in mysql (logfile1, 150)
16:30 - initial replication finished.

Currently we apply all binlog records starting from 16.00 (logfile1, 100), so the delete operation (logfile1, 150) also would be applied.
What you propose (just continue replication from largest PK and forget about logs / state / etc.) will lead to delete operation would be ignored and you will get data discrepancy.

[mxAsad]

I managed to overcome that scenario, but the thing happening now is that I am getting the error transaction not found and along with a binlog file name and position. That file by the way, is still present in the source db and when i try to use run all to start the process again instead of a save state it is starting the initial replication again

[bakwc]

We only store binlog for 5 hours locally, so if you have an issue for more than 5 hours you need to run replication from scratch.

[mxAsad]

In our server, the retention period for logs is 7 days and the log files are still present

[jaredmdobson]

@mxAsad are you using AWS RDS? because if so you are have to set a special setting called: 'binlog retention hours'
see here: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/mysql-stored-proc-configuring.html