Data Table Migration Checkpoints for Interrupted Schema Changes
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Performing table structure changes in large databases is time-consuming and prone to failures, leading to inefficient data migration and the need to re-migrate valid data due to rollback, thus reducing efficiency and accuracy.
Innovation Solution
A method and apparatus for data migration that involves obtaining source table structure information, creating a target data table, determining primary keys, successively migrating data after each successful migration, generating migration progress records, and resuming migration from the interruption point to avoid re-migration.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If table structure change is performed on a super large data table, then the table structure can be updated, but the operation becomes time-consuming and may fail due to software defects, hardware faults, or human interference
Solution Approach 1:
The patent segments the table structure change operation into multiple incremental steps rather than performing a single large-scale change. This is achieved by dividing the data migration process into batches, where each batch migrates a portion of the data from the source table to the target table with the new structure. This segmentation reduces the time and risk associated with each individual operation while maintaining overall reliability.
Solution Approach 2:
The patent creates a target table with the new structure in advance before performing the actual data migration. This preliminary action allows the table structure change to be prepared and validated beforehand, reducing the time required during the actual migration process and enabling a smoother transition without locking the source table for extended periods.
2Reliability
If table structure change execution fails, then all data must be rolled back, but this causes already migrated data to become invalid and requires re-migration, reducing efficiency
Solution Approach 1:
The patent implements a checkpoint mechanism that records the state of data migration at regular intervals. When a failure occurs, the system can identify the last successful checkpoint and resume migration from that point rather than rolling back all changes. This continuity principle ensures that already migrated data remains valid and can be picked up from where it left off, significantly improving migration efficiency while maintaining reliability.
Solution Approach 2:
The system incorporates feedback through checkpoint recording and status tracking during the migration process. By monitoring migration progress and storing intermediate states, the system can detect failures, determine the extent of successful migration, and provide feedback for resuming the operation. This feedback mechanism prevents complete rollback and enables efficient recovery from failures.
3Reliability
If data migration is performed from the beginning after a failure, then data consistency can be maintained, but migration speed and accuracy decrease due to redundant migration of already successful data
Solution Approach 1:
The patent extracts and identifies the portion of data that has already been successfully migrated using checkpoint records and comparison mechanisms. By separating the migrated portion from the total data set, the system can focus subsequent migration efforts only on the remaining unmigrated data. This extraction principle maintains data consistency while eliminating redundant operations, thereby improving both accuracy and speed.
Solution Approach 2:
Instead of performing complete re-migration of all data after a failure, the patent applies partial action by migrating only the necessary portion of data that was not successfully transferred before. The system determines the exact point of failure and performs migration only from that point forward, avoiding excessive action on already migrated data. This approach maintains accuracy while significantly improving migration speed.
Data Source
AI summary
The present disclosure provides methods, devices, apparatus, and storage medium for data migration. One method includes obtaining source table structure information of a source data table, changing the source table structure information, and creating a target data table based on changed table structure information; determining a plurality of source primary keys in the source data table; successively migrating the source primary keys and the source data in the source data table to the target data table, each migration starting after previous migration succeeds; generating a migration progress record of each migration; determining a migration progress record at migration interruption when the migration interruption occurs, and determining a source primary key at the migration interruption in the source data table; and continuing to successively migrate, starting from the source primary key at the migration interruption in the source data table, an unmigrated source primary key and unmigrated source data.