Database Replication Auto-Correction via Delete-Insert Pairs
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database replication processes face issues with discrepancies and corrupted data due to incomplete materialization, leading to reduced replication reliability and potential data loss during update or insert operations.
Innovation Solution
The system automatically corrects replicated data by replacing identified insert and update operations with pairs of delete and insert operations, ensuring that corrupted data is removed before new data is inserted, thereby maintaining data integrity and reliability.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If data replication uses bulk or non-atomic materialization methods to improve performance, then replication speed increases, but data discrepancies and corruption occur during materialization
Solution Approach 1:
The system performs preliminary actions by deleting corrupted data at the replicate database before inserting new data. The replication server intercepts log records, identifies corrupted rows, and executes delete operations on the replicate database prior to the insert operations, ensuring clean data state during bulk materialization
Solution Approach 2:
The system implements feedback mechanisms where the replication server monitors log records for materialization operations, detects discrepancies and corrupted data, and automatically generates correction commands. The server receives replication status information and adjusts operations accordingly to maintain data integrity during high-speed replication
2Reliability
If the system performs auto-correction by deleting corrupted data before insertion, then data integrity improves, but additional operations increase system complexity
Solution Approach 1:
The replication system performs self-service by automatically detecting and correcting its own errors. The replication server monitors its own replication operations, identifies corrupted data through log record analysis, and executes self-correction delete operations without external intervention, maintaining data integrity autonomously
Solution Approach 2:
The system merges the correction operation with the existing replication workflow. The delete operations for correcting corrupted data are combined with the insert operations in a coordinated sequence, where the replication server manages both operations through a unified process that integrates correction into the normal replication flow
3Reliability
If auto-correction deletes data at locations before insertion, then replication reliability increases, but processing time for each operation increases
Solution Approach 1:
The system performs delete operations as preliminary actions before insert operations. The replication server processes log records to identify corrupted data and executes delete operations on the replicate database before the corresponding insert operations, ensuring corrupted data is removed in advance
Solution Approach 2:
The system maintains continuity of useful action by executing correction operations within the same replication transaction flow. The delete and insert operations are performed continuously without breaking the replication stream, minimizing idle time while ensuring corrupted data is removed before new data is inserted
Data Source
AI summary
Systems, methods and computer program product embodiments for auto-correction in database replication are disclosed herein. An embodiment includes receiving a log record of one or more database row changes on a table marked for replication. From the received log record, one or more insert operations on the marked table are identified. Each identified insert operation is automatically replaced with a first pair of operations, where each first pair comprises a delete operation associated with a new insert operation. Each first pair, when executed at a replicate database, acts to delete data at a first location identified by each identified insert operation prior to inserting data of the new insert operation at the first location. The embodiment further includes identifying, from the log record, one or more update operations on the marked table, and automatically replacing each identified update operation with a second pair of operations, where each second pair comprises a new delete operation associated with a new insert operation.