Online ddl synchronization method and system based on temporary table interception and conversion

Abstract

The present application relates to the technical field of database data synchronization, and discloses an online DDL synchronization method and system based on temporary table interception and conversion. The method comprises capturing source database DDL logs in a data synchronization channel, identifying temporary table creation, data writing and renaming switching operation sequences in online DDL operations. DDL modification statements executed for the temporary table are extracted and stored persistently, and then converted into target DDL statements for the source table according to table mapping relationships. By listening to the symbolic event representing the completion of the renaming switching in the logs, the target DDL statements are sent to the target database for execution only when the event is triggered. The method can realize accurate and reliable synchronization of online DDL changes, and avoid data structure inconsistency and synchronization interruption problems caused by directly analyzing intermediate state logs.

Description

An Online DDL Synchronization Method and System Based on Temporary Table Interception and Conversion Technical Field

[0001] This invention relates to the field of database data synchronization technology, specifically to an online DDL synchronization method and system based on temporary table interception and conversion. Background Technology

[0002] In database synchronization scenarios, real-time capture and application of structural changes in the source database are crucial for maintaining data consistency between the two ends. Conventional synchronization techniques typically involve directly parsing and forwarding Data Definition Language (DDL) logs. This approach works effectively when the source database performs standard, offline DDL operations.

[0003] Existing synchronization solutions face challenges when dealing with online data definition language (ODL) operations. To minimize business impact, ODLs often employ a process of creating temporary tables, modifying the structure on these temporary tables, and then replacing the original tables with renames. Mainstream ODL log parsing technologies tend to focus directly on statements targeting the original table names. This can lead to synchronization tools prematurely attempting to apply structural changes that haven't yet taken effect on the target database, or losing crucial intermediate information in complex operation sequences. Consequently, the target database might attempt to modify an incomplete table structure in an intermediate state, easily triggering synchronization errors, causing inconsistencies in data structures between the two ends, or even leading to synchronization interruption.

[0004] The core problem that this invention needs to solve is how to accurately capture a series of intermediate modifications to temporary tables in online data definition language operations, and apply them to the target end as the final changes to the source table at the right time, so as to avoid the risks and defects brought about by directly parsing the original logs. Summary of the Invention

[0005] The purpose of this invention is to provide an online DDL synchronization method and system based on temporary table interception and conversion, so as to solve the problems mentioned in the background art.

[0006] To achieve the above objectives, this invention provides an online DDL synchronization method based on temporary table interception and conversion, the method comprising:

[0007] Capture Data Definition Language logs generated by the source database in the data synchronization channel;

[0008] Identify temporary table operations associated with online data definition language operations from captured data definition language logs. These temporary table operations include a sequence of operations such as creating a temporary table corresponding to the source table structure, writing data to the temporary table, and renaming and switching the temporary table to the source table.

[0009] Based on the identified temporary table operations, the data definition language modification statements executed on the temporary table are extracted and persistently stored in the synchronous metadata storage area.

[0010] Based on the mapping relationship between the source table and the temporary table, the data definition language modification statements executed on the temporary table that are persistently stored are converted into target data definition language statements executed on the source table;

[0011] The system monitors the data definition language log for marker events indicating that the temporary table and the source table have completed the renaming switch. When the marker event is detected, the system sends the target data definition language statement generated for the source table to the target database for execution, thereby synchronizing the online data definition language operations on the source database.

[0012] Preferably, capturing the Data Definition Language logs generated by the source database in the data synchronization channel includes:

[0013] The Data Definition Language log contains all Structured Query Language operations performed on the data tables in the source database;

[0014] Establish a connection to the source database's binary log file or transaction log file and continuously read newly added log entries;

[0015] The read log entries are parsed, and operation records with the operation type of Data Definition Language are filtered out to form the Data Definition Language log containing the operation timestamp, database name, table name, operation statement, and execution thread identifier.

[0016] Preferably, identifying temporary table operations associated with online Data Definition Language operations from captured Data Definition Language logs includes:

[0017] In the parsed data definition language operation records, it is checked whether the table name conforms to the preset online data definition language temporary table naming pattern. The online data definition language temporary table naming pattern includes a specific prefix, suffix, or a combination of the source table name and hash value.

[0018] When a table creation operation that conforms to the online data definition language temporary table naming pattern is detected, the temporary table name is recorded, and subsequent data definition language operations and data manipulation language operations targeting the temporary table name are marked as pending operation sequences associated with the online data definition language operation, until a renaming operation involving the temporary table name and the source table name is detected.

[0019] Preferably, the step of extracting the data definition language modification statements executed on the temporary table based on the identified temporary table operations, and persistently storing the extracted data definition language modification statements executed on the temporary table in the synchronous metadata storage area, includes:

[0020] In the marked sequence of pending operations, data definition language operations with the operation type of modifying table structure are selected. These data definition language operations include adding columns, deleting columns, modifying column definitions, adding indexes, or deleting indexes.

[0021] Extract the complete Structured Query Language statement, the temporary table name, and the execution location information of the corresponding operation in the source database from the selected Data Definition Language operations, and write the extracted complete Structured Query Language statement, temporary table name, and execution location information as a persistent record into the synchronous metadata storage area.

[0022] Preferably, the step of converting persistently stored Data Definition Language (DDL) modification statements executed on the temporary table into Target Data Definition Language (DDL) statements executed on the source table based on the mapping relationship between the source table and the temporary table includes:

[0023] Each persistent record is read from the synchronous metadata storage area. The persistent record contains the data definition language modification statement executed on the temporary table and the name of the temporary table.

[0024] Based on the correspondence between temporary tables and source tables recorded during the online data definition language operation, find the original source table name corresponding to the temporary table name;

[0025] Replace the temporary table name in the data definition language modification statement executed on the temporary table with the found original source table name to generate the semantically equivalent target data definition language statement executed on the source table, but with the source table as the operation object.

[0026] Preferably, the marker event in the monitoring data definition language log that indicates the completion of the renaming switch between the temporary table and the source table includes:

[0027] Continuously monitor the data definition language logs to identify log records where the operation type is table renaming and the table names involved include both the recorded temporary table name and the original source table name.

[0028] When an operation log is detected that renames a temporary table to the source table name and simultaneously renames the source table to another name, it is determined that an online data definition language operation has been executed to the final switching stage, and the execution position point or timestamp corresponding to the renaming operation log is recorded as the occurrence point of the landmark event.

[0029] Preferably, when the marker event is detected, sending the converted target data definition language statement for execution against the source table to the target database for execution includes:

[0030] After the landmark event is detected, all converted target data definition language statements executed against the source table are retrieved from the synchronous metadata storage area in the order of execution location.

[0031] All target data definition language statements obtained are treated as an atomic transaction, or sent sequentially to the target database connection session for execution according to the order of execution points.

[0032] Preferably, establishing a connection with the source database binary log file or transaction log file and continuously reading newly added log entries includes:

[0033] Configure the connection parameters for the source database, including the database address, port number, authentication information, and log file path;

[0034] A long connection is established with the source database log system based on the connection parameters, and the size changes of binary log files or transaction log files are monitored in real time through a log polling mechanism.

[0035] When new data is detected in the log file, the log parser is started to split the log content according to the transaction boundary and extract the complete transaction log record.

[0036] Each transaction log record is validated and verified to ensure its integrity and accuracy. Validated transaction log records are then added to the pending log queue.

[0037] Preferably, when an operation log is detected that renames a temporary table to the source table name and simultaneously renames the source table to another name, it is determined that an online data definition language operation has reached the final switching stage. The execution location point or timestamp corresponding to the renaming operation log is recorded as the occurrence point of the landmark event, including:

[0038] Parse the detailed contents of the renaming operation log to extract the original name of the source table, the name of the temporary table, and the name of the new table after renaming;

[0039] Verify that the renaming operation conforms to the standard switching mode of the online data definition language, that is, whether the temporary table is renamed to the original name of the source table, and whether the source table is renamed to a new name that includes the archive identifier.

[0040] When the verification meets the standard switching mode, the global transaction identifier and log sequence number of the renaming operation in the database log are extracted as the execution location point, and the system timestamp of the database server is recorded at the same time.

[0041] The global transaction identifier, log sequence number, and timestamp are combined to generate a unique event identifier, which is then stored in the signature event registry.

[0042] Preferably, the present invention also includes an online DDL synchronization system based on temporary table interception and conversion, the system including a memory, a processor, and a computer program stored in the memory and running on the processor, wherein when the processor executes the computer program, it implements the steps of the online DDL synchronization method based on temporary table interception and conversion as described above.

[0043] Compared with the prior art, the beneficial effects of the present invention are:

[0044] From the captured Data Definition Language (DDL) logs, DDL modification statements executed on temporary tables during online operations are identified and extracted, and these statements are persistently stored independently of the business data operation logs. This explicitly transforms the structural change intentions for intermediate temporary objects, which were originally hidden within the business data stream, into manageable and traceable synchronous metadata. This allows subsequent processing flows to clearly understand the specific content of the source-end structural changes, rather than relying on real-time speculation or filtering of mixed logs.

[0045] Instead of relying on real-time parsing triggers or fixed time windows, this mechanism proactively listens for marker events in the Data Definition Language (DDL) log indicating that the temporary table and the source table have completed a renaming switch, and uses this event as the sole condition for triggering structural synchronization on the target end. This mechanism ensures that the initiation of synchronization actions strictly depends on the objective moment when the source end structural changes actually take effect. It eliminates the risk of structural state misalignment caused by inconsistencies between the synchronization process and the online operation progress on the source end, achieving atomic alignment between change activation and synchronization execution.

[0046] Based on the modification statements for temporary tables stored in persistent memory, and combined with the mapping relationship between the source table and the temporary table identified from the operation sequence, the statements are transformed to generate target data definition language statements for the source table. The intermediate operation semantics based on the temporary table context are accurately restored to the final structural change semantics applicable to the original table object on the target side. This ensures that even if online operations undergo multiple complex intermediate transformations on the source side, the final effect can be completely and accurately transmitted to the target side. Attached Figure Description

[0047] Figure 1 is a schematic diagram illustrating the working principle of the online DDL synchronization method based on temporary table interception and conversion described in this invention.

[0048] Figure 2 is a flowchart of capturing data to define the language log;

[0049] Figure 3 is a flowchart for identifying temporary table operations;

[0050] Figure 4 shows the dual-axis data diagram of each processing stage of DDL synchronization.

[0051] Figure 5 is a pie chart showing the distribution of DDL operation types. Detailed Implementation

[0052] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0053] Please refer to Figure 1. This invention provides an online DDL synchronization method based on temporary table interception and conversion. The method includes: capturing data definition language logs generated by the source database; identifying temporary table operations associated with online data definition language operations from the captured data definition language logs; temporary table operations including creating a temporary table corresponding to the source table structure, writing data to the temporary table, and performing a renaming switch between the temporary table and the source table; extracting data definition language modification statements for the temporary table based on the identified temporary table operations; persistently storing the extracted data definition language modification statements for the temporary table in the synchronization metadata storage area; converting the persistently stored data definition language modification statements for the temporary table into target data definition language statements for the source table according to the mapping relationship between the source table and the temporary table; listening for a marker event in the data definition language log indicating that the temporary table and the source table have completed the renaming switch; and sending the converted target data definition language statements for the source table to the target database for execution when the marker event is detected, thereby completing the synchronization of online data definition language operations in the source database.

[0054] In one embodiment of the present invention, referring to Figure 2, the Data Definition Language (DDL) log contains all Structured Query Language (SCL) operations performed on data tables in the source database. A connection is established with the source database's binary log file or transaction log file. Newly added log entries are continuously read, parsed, and DDL operation records are filtered out, forming a DDL log containing operation timestamps, database names, table names, operation statements, and execution thread identifiers. Connection parameters for the source database are configured, including the database address, port number, authentication information, and log file path. A long-lived connection to the source database's log system is established based on these parameters. A log polling mechanism monitors the size changes of the binary log file or transaction log file in real time. When new data is detected in the log file, a log parser is activated to segment the log content according to transaction boundaries, extracting complete transaction log records. Each transaction log record is verified to ensure its integrity and accuracy. Verified transaction log records are added to the pending log queue.

[0055] In practical implementation, the Data Definition Language (DDL) log contains all Structured Query Language (SCL) operations performed on the data tables in the source database. It establishes a connection with the source database's binary log file or transaction log file and continuously reads newly added log entries. Connection parameters for the source database are configured, including the database address, port number, authentication information, and log file path. A long-lived connection to the source database's log system is established based on these parameters. In some embodiments, a log polling mechanism monitors the size changes of the binary log file or transaction log file in real time. This log polling mechanism checks the current size of the log file at a fixed frequency and compares it with the size of the previous record.

[0056] In practical implementation, when new data is detected in the log file, a log parser is activated to segment the log content according to transaction boundaries and extract complete transaction log records. The log parser identifies the transaction start marker and transaction commit marker in the log entries, and aggregates all operation records between these two markers into a single independent transaction log record. In some embodiments, each transaction log record is checked and verified to ensure its integrity and accuracy. The check and verification process calculates the hash value of the transaction log record and compares it with the checksum attached to the log file. Optionally, verified transaction log records are added to a pending log queue, which is a memory-based buffer used to temporarily store parsed operation records that have not yet been processed by subsequent steps. It can be understood that parsing the read log entries filters out operation records of the Data Definition Language (DDL) type, forming a DDL log containing an operation timestamp, database name, table name, operation statement, and execution thread identifier.

[0057] In practice, the filtering operation is based on the judgment of the operation command type in the transaction log records. Records with operation command types of creating, modifying, or deleting tables are identified as Data Definition Language (DDL) operation records. Optionally, the parsing of DDL operation records extracts fields such as operation timestamp, database name, table name, operation statement, and execution thread identifier. The operation timestamp comes from the precise time information generated by the database server when recording the log. It can be understood that, to ensure the continuity of log reading, when the binary log file is switched, the log polling mechanism can automatically locate the new log file and continue monitoring. The path information of the new log file is obtained from the database server's system table. The verification process introduces a verification mechanism. For a transaction log record, its integrity verification formula can be expressed as:

[0058] ;

[0059] Where: symbol Represents the verification result, symbol Represents the selected cryptographic hash function, symbol Represents all bytes of data in the transaction log record to be verified, symbol This represents the record read from the log file. The original checksum, when When the value is true, the transaction log record is considered complete and accurate.

[0060] In one embodiment of the present invention, referring to FIG3, the table name in the parsed data definition language operation record is checked to see if it conforms to a preset online data definition language temporary table naming pattern. The online data definition language temporary table naming pattern includes a combination of a specific prefix, suffix, or source table name and hash value. When a table creation operation that conforms to the online data definition language temporary table naming pattern is detected, the temporary table name is recorded, and subsequent data definition language operations and data manipulation language operations on the temporary table name are marked as a sequence of pending operations associated with the online data definition language operation, until a renaming operation involving the temporary table name and the source table name is detected.

[0061] In specific implementation, the table names in the parsed Data Definition Language (DDL) operation records are checked to see if they conform to a preset online DDL temporary table naming pattern. The online DDL temporary table naming pattern includes a specific prefix, suffix, or a combination of the source table name and a hash value. For each parsed DDL operation record, the detection process extracts the "table name" field and compares it with pre-configured pattern rules. When a table creation operation conforming to the online DDL temporary table naming pattern is detected, the temporary table name is recorded, and subsequent DDL operations and Data Manipulation Language (DML) operations targeting the temporary table name are marked as part of a pending operation sequence associated with the online DDL operation. Recording the temporary table name involves creating a new entry in an in-memory status tracking table, storing the temporary table name, the original source table name corresponding to the temporary table creation, and the timestamp of the first detection of the temporary table. In some embodiments, marking the operation involves adding a processing tag to all subsequent log records. The processing tag indicates that if the table name in the record matches an already recorded temporary table name, the operation to which the record belongs is classified as part of a pending operation sequence. It is understandable that the scope of the pending operation sequence covers both data definition language operations and data manipulation language operations targeting the temporary table name, up to the point where a renaming operation involving the temporary table name and the source table name is detected.

[0062] In practice, the pre-configured online data definition language temporary table naming pattern supports dynamic updates, and the pattern matching logic can be formally expressed. The formula for determining whether a table name conforms to the naming pattern can be expressed as:

[0063] ;

[0064] Where: symbol The boolean result representing pattern matching, symbol Represents a pattern matching function, symbol Represents the table name string extracted from the Data Definition Language operation record, symbol A set of rules representing a predefined online data definition language temporary table naming pattern, when When the value is true, the decision table name is determined. It conforms to the naming pattern of temporary tables in the Online Data Definition Language.

[0065] Optionally, the process of recording the correspondence between temporary table names and original source table names maintains a temporary table mapping table. This mapping table is stored in the memory of the synchronization process, with the temporary table name as the key and the original source table name as the value. It can be understood that the logic for marking the sequence of operations to be processed is based on a continuous state machine. The state of the state machine changes according to whether a valid temporary table creation event has been identified and whether the final rename / switch event has been received. Optionally, during the marking of the sequence of operations to be processed, all operation logs for the marked temporary table names, regardless of whether the operation type is Data Definition Language (DDL) or Data Manipulation Language (DML), are collected and temporarily stored in a buffer bound to the temporary table name.

[0066] In one embodiment of the present invention, data definition language (DML) operations that modify table structures are selected from a marked sequence of pending operations. These DML operations include adding columns, deleting columns, modifying column definitions, adding indexes, or deleting indexes. Complete structured query language (SCL) statements, temporary table names, and the execution location information of the corresponding operations in the source database are extracted from the selected DML operations. The extracted complete SCL statements, temporary table names, and execution location information are written as a persistent record to the synchronous metadata storage area. Each persistent record is read from the synchronous metadata storage area. Each persistent record contains a DML modification statement executed on a temporary table and the temporary table name. Based on the correspondence between temporary tables and source tables recorded during online DML operations, the original source table name corresponding to the temporary table name is found. The temporary table name in the DML modification statement executed on the temporary table is replaced with the found original source table name, generating a semantically equivalent target DML statement executed on the source table, but with the operation object being the source table.

[0067] In specific implementation, data definition language (DML) operations that modify table structures are selected from the marked sequence of pending operations. These DML operations include adding columns, deleting columns, modifying column definitions, adding indexes, or deleting indexes. The selection process iterates through each record in the sequence of pending operations, judging based on the syntax keywords of the operation commands in the records. These syntax keywords include "ALTERTABLE", "ADDCOLUMN", "DROPCOLUMN", "CHANGECOLUMN", "ADDINDEX", and "DROPINDEX". In some embodiments, the complete structured query language (SCL) statement, temporary table name, and execution position information of the corresponding operation in the source database are extracted from the selected DML operations. The execution position information is the log sequence number in the database binary log file that identifies the global order of the operation records. Optionally, the extracted complete SCL statement, temporary table name, and execution position information are written as a persistent record to the synchronous metadata storage area. The synchronous metadata storage area is a relational database table independent of the business database, and its table structure includes a statement content field, a temporary table name field, a position field, and a record generation timestamp field.

[0068] In specific implementation, each persistent record is read from the synchronous metadata storage area. The persistent record contains the Data Definition Language (DDL) modification statement executed on the temporary table and the temporary table name. The read operation is performed according to the numerical order of the position field in the persistent record. Based on the correspondence between the temporary table and the source table recorded during the online DDL operation, the original source table name corresponding to the temporary table name is found. The lookup operation queries the temporary table mapping dictionary maintained in memory, retrieving the corresponding original source table name using the temporary table name in the persistent record as the key. In some embodiments, the temporary table name in the DDL modification statement executed on the temporary table is replaced with the found original source table name, generating a semantically equivalent target DDL statement executed on the source table but with the operation object being the source table. It can be understood that the name replacement process performs string processing on the complete Structured Query Language (SCL) statement, identifying all tokens containing the temporary table name and replacing them with the original source table name. Token identification needs to consider the referencing rules of database identifiers, such as backticks. Optionally, the transformation logic of name replacement can be formally expressed. For a DDL modification statement executed on a temporary table, its transformation process formula can be expressed as:

[0069] ;

[0070] Where: symbol The target data to be generated represents the language statement string, symbols. Represents a string replacement function, symbol Represents the raw string of a Data Definition Language (DDL) modification statement executed against a temporary table, read from a persistent record; symbols Represents the temporary table name string read from the same persistent record, symbol This represents the string representing the original source table name obtained by finding the correspondence between the temporary table and the source table. string All of the above Replace identical substrings with Thus obtain It is understandable that the generated target data definition language statements executed against the source table are syntactically identical to the data definition language modification statements executed against the temporary table captured from the source database logs, differing only in the names of the tables being operated on.

[0071] Referring to Figure 4, this is a dual-axis composite chart used to display the "success rate" and "number of retries" data for each processing stage of DDL synchronization. It belongs to the data visualization category. The "persistent storage stage" has the highest success rate (approximately 96%), while also reaching a peak in the number of retries (10 times). The "statement transformation stage" has the lowest success rate (85%), but the fewest retries (1 time). Success rate and number of retries show a clear negative correlation. This type of chart is typically used for system performance analysis or process optimization to help technicians pinpoint the less efficient or less stable stages in the DDL synchronization process.

[0072] In one embodiment of the present invention, the Data Definition Language (DDL) log is continuously monitored to identify log records where the operation type is "rename table" and the table names involved include both the recorded temporary table name and the original source table name. When an operation log is identified that renames a temporary table to the source table name and simultaneously renames the source table to another name, it is determined that an online DDL operation has reached the final switching stage. The execution position point or timestamp corresponding to the renaming operation log is recorded as the occurrence point of a landmark event. The detailed content of the renaming operation log is parsed to extract the original name of the source table, the temporary table name, and the new table name after renaming. It is verified whether the renaming operation conforms to the standard switching mode of the online DDL, i.e., whether the temporary table is renamed to the original name of the source table and whether the source table is renamed to a new name containing an archive identifier. When it is verified that the standard switching mode is met, the global transaction identifier and log sequence number of the renaming operation in the database log are extracted as the execution position point. At the same time, the system timestamp of the database server is recorded. The global transaction identifier, log sequence number, and timestamp are combined to generate a unique event identifier, which is stored in the landmark event registry.

[0073] In practice, the Data Definition Language (DDL) logs are continuously monitored to identify log records where the operation type is "rename table" and the table names involved include both the recorded temporary table name and the original source table name. The monitoring process parses each record in the DDL logs, checks if the operation command is "RENAMETABLE," and extracts all table name pairs involved in the command. In some embodiments, when an operation log is identified that renames a temporary table to the source table name and simultaneously renames the source table to another name, it is determined that an online DDL operation has reached the final switch phase. The determination logic is based on comparing the extracted table name pairs with the recorded mapping relationship between the temporary table and the source table. Optionally, the execution location point or timestamp corresponding to the renaming operation log is recorded as the occurrence point of a landmark event. The execution location point can be the database log sequence number, and the timestamp is the time the operation occurred as recorded by the database server.

[0074] In practical implementation, the detailed content of the renaming operation log is parsed to extract the original name of the source table, the name of the temporary table, and the name of the new table after renaming. The parsing work includes syntactic analysis of the "RENAMETABLE" statement to break it down into multiple renaming clauses. The renaming operation is verified to conform to the standard switching pattern of the online data definition language, i.e., whether the temporary table is renamed to the original name of the source table, and whether the source table is renamed to a new name including an archive identifier. The verification process compares the extracted names with known patterns. In some embodiments, when verification confirms conformity to the standard switching pattern, the global transaction identifier and log sequence number of the renaming operation in the database log are extracted as the execution location point, and the system timestamp of the database server is recorded. The global transaction identifier is a unique number identifying a distributed transaction. It can be understood that combining the global transaction identifier, log sequence number, and timestamp generates a unique event identifier, which is stored in a signature event registry. The event identifier generation function can be expressed as:

[0075] ;

[0076] Where: symbol Represents the generated event identifier string, symbol This represents a string concatenation function, symbol Represents the extracted global transaction identifier string, symbol Represents the extracted log sequence number value, symbol The system timestamp string representing the record, function Concatenate the three input values ​​into a single string using a predetermined separator. Referring to Table 1, the landmark event registry is a relational table stored in the synchronization metadata store, used to record all identified switching events.

[0077] Table 1: Relationship Table of Synchronous Metadata Storage Area

[0078]

[0079] Optionally, the operation of storing data in the marker event registry is performed within a database transaction to ensure the atomicity and consistency of event recording. It can be understood that listening to and recording marker events is a key control point that triggers the subsequent synchronization of the transformed Data Definition Language statements to the target database.

[0080] In one embodiment of the present invention, after a landmark event is detected, all converted Target Data Definition Language (RDL) statements for execution against the source table are retrieved from the synchronous metadata storage area in the order of execution position points. All retrieved RDL statements are then processed as an atomic transaction, or sequentially sent to the connection session of the target database for execution in the order of execution position points. In a specific implementation, after a landmark event is detected, all converted RDL statements for execution against the source table are retrieved from the synchronous metadata storage area in the order of execution position points. The retrieval operation is completed by executing a database query statement. The query statement uses the source table name and temporary table name associated with the landmark event as filtering conditions and sorts the data in ascending order by the execution position point field. In some embodiments, the record set read from the synchronous metadata storage area is constructed into an ordered list. Each element in the list contains a RDL statement for execution against the source table and its original execution position point information. The order of the execution position point information ensures that the logical execution order of the data definition language operations is maintained. Optionally, the sorting logic for retrieving all converted RDL statements for execution against the source table can be formally expressed, and its sorting process formula can be expressed as:

[0081] ;

[0082] Where: symbol The final ordered target data represents a list of language statements and symbols. Represents a sorting function, symbol Represents the set of all records related to a specific online Data Definition Language operation read from the synchronized metadata store, symbol The key used for sorting is the execution point, and the function is... Based on the record set Each record Sort the values ​​in ascending order and output an ordered list. .

[0083] In practice, all the target data definition language statements acquired are sent as an atomic transaction to the target database connection session for execution. The execution method of the atomic transaction is to explicitly start a database transaction on the connection established with the target database, and then list... All statements within the atomic transaction are executed sequentially within the transaction context, and the transaction is then committed collectively. It's understood that if any statement fails during execution, the entire atomic transaction will be rolled back, all executed operations will be undone, and the failure will be recorded and an alert mechanism will be triggered. Alternatively, another implementation method is to send statements sequentially to the target database connection session according to their execution order. This method does not explicitly open a single database transaction encompassing all statements, but rather uses a list... Each Target Data Definition Language (RDL) statement in the process is executed independently, and the operations in the current connection session are committed immediately after each statement is executed. In some embodiments, when executed sequentially, each successfully executed RDL statement targeting the source table updates the status flag of the corresponding original persistent record in the synchronization metadata store, marking it as synchronized. This ensures that even if the process is interrupted, it can resume execution from the last successfully executed point after recovery. It can be understood that the strategy of executing all acquired RDL statements as an atomic transaction emphasizes the atomicity of operations and data consistency, while the strategy of executing them sequentially according to their execution points emphasizes the gradual nature and recoverability of operations.

[0084] Referring to Figure 5, this is a pie chart showing the distribution of different table structure modification types in DDL (Data Definition Language) operations. "Adding a column" has the highest percentage; "Deleting an index" has the lowest percentage; and the percentages of "Adding an index," "Modifying column definitions," and "Deleting a column" are at a moderate level, indicating a relatively balanced distribution. This type of pie chart is typically used in database operations and development scenarios to help relevant personnel quickly understand the main types of table structure changes, providing data references for resource allocation (such as index optimization and column management) or process optimization.

[0085] It should be noted that, in this document, relational terms such as "first" and "second" are used only to distinguish one entity or operation from another, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Furthermore, the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such process, method, article, or apparatus.

[0086] Although embodiments of the invention have been shown and described, it will be understood by those skilled in the art that various changes, modifications, substitutions and alterations can be made to these embodiments without departing from the principles and spirit of the invention, the scope of which is defined by the appended claims and their equivalents.

Claims

1. An online DDL synchronization method based on temporary table interception and conversion, characterized in that, The method includes: capturing data definition language (DDL) logs generated by the source database in a data synchronization channel; identifying temporary table operations associated with online DDL operations from the captured DDL logs, wherein the temporary table operations include an operation sequence of creating a temporary table corresponding to the source table structure, writing data to the temporary table, and performing a renaming switch between the temporary table and the source table; extracting DDL modification statements executed on the temporary table based on the identified temporary table operations, and persistently storing the extracted DDL modification statements executed on the temporary table in a synchronization metadata storage area; converting the persistently stored DDL modification statements executed on the temporary table into target DDL statements executed on the source table according to the mapping relationship between the source table and the temporary table; listening for a marker event in the DDL logs indicating that the temporary table and the source table have completed the renaming switch, and when the marker event is detected, sending the converted target DDL statements executed on the source table to the target database for execution to complete the synchronization of online DDL operations in the source database.

2. The online DDL synchronization method based on temporary table interception and conversion according to claim 1, characterized in that, The process of capturing the Data Definition Language (DCL) logs generated by the source database in the data synchronization channel includes: the DCL logs contain all Structured Query Language (SCL) operations performed on the data tables in the source database; establishing a connection with the binary log file or transaction log file of the source database and continuously reading newly added log entries; parsing the read log entries, filtering out operation records with the operation type being DCL, and forming the DCL logs containing operation timestamps, database names, table names, operation statements, and execution thread identifiers.

3. The online DDL synchronization method based on temporary table interception and conversion according to claim 2, characterized in that, The step of identifying temporary table operations associated with online data definition language (ODL) operations from captured data definition language (DCL) logs includes: detecting whether the table name conforms to a preset DCL temporary table naming pattern in the parsed DCL operation records. The DCL temporary table naming pattern includes a specific prefix, suffix, or a combination of the source table name and a hash value. When a table creation operation conforming to the DCL temporary table naming pattern is detected, the temporary table name is recorded, and subsequent DCL operations and data manipulation language (DML) operations targeting the temporary table name are marked as a sequence of pending operations associated with the online DCL operation, until a renaming operation involving the temporary table name and the source table name is detected.

4. The online DDL synchronization method based on temporary table interception and conversion according to claim 3, characterized in that, The step of extracting data definition language (DML) modification statements for the temporary table based on the identified temporary table operations and persistently storing the extracted DML modification statements for the temporary table in the synchronous metadata storage area includes: filtering DML operations of table structure modification type from the marked sequence of pending operations, including adding columns, deleting columns, modifying column definitions, adding indexes, or deleting indexes; extracting complete structured query language (SCL) statements, the temporary table name, and the execution location information of the corresponding operation in the source database from the selected DML operations; and writing the extracted complete SCL statements, temporary table name, and execution location information as a persistent record into the synchronous metadata storage area.

5. The online DDL synchronization method based on temporary table interception and conversion according to claim 4, characterized in that, The step of converting persistently stored Data Definition Language (DDL) modification statements for the temporary table into target DDL statements for the source table, based on the mapping relationship between the source table and the temporary table, includes: reading each persistent record from the synchronous metadata storage area, wherein the persistent record contains the DDL modification statement for the temporary table and the temporary table name; finding the original source table name corresponding to the temporary table name according to the correspondence between the temporary table and the source table recorded during the online DDL operation; replacing the temporary table name in the DDL modification statement for the temporary table with the found original source table name, thereby generating a semantically equivalent target DDL statement for the source table that operates on the source table.

6. The online DDL synchronization method based on temporary table interception and conversion according to claim 5, characterized in that, The key events in the monitored Data Definition Language (DDL) logs that signify the completion of the renaming switch between the temporary table and the source table include: continuously monitoring the DDL logs and identifying log records where the operation type is table renaming and the table names involved include both the recorded temporary table name and the original source table name; when an operation log is identified that renames the temporary table to the source table name and simultaneously renames the source table to another name, it is determined that an online DDL operation has reached the final switching stage, and the execution position point or timestamp corresponding to the renaming operation log is recorded as the occurrence point of the key event.

7. The online DDL synchronization method based on temporary table interception and conversion according to claim 1, characterized in that, The step of sending the converted Target Data Definition Language (RDL) statements for execution against the source table to the target database for execution upon detecting the marker event includes: after detecting the marker event, retrieving all converted RDL statements for execution against the source table from the synchronous metadata storage area in the order of execution location points; and sending all the retrieved RDL statements as an atomic transaction, or sequentially in the order of execution location points, to the connection session of the target database for execution.

8. The online DDL synchronization method based on temporary table interception and conversion according to claim 2, characterized in that, The process of establishing a connection with the source database's binary log file or transaction log file and continuously reading newly added log entries includes: configuring connection parameters for the source database, including the database address, port number, authentication information, and log file path; establishing a long-lived connection with the source database's log system based on the connection parameters, and monitoring changes in the size of the binary log file or transaction log file in real time through a log polling mechanism; when new data is detected in the log file, starting the log parser to segment the log content according to transaction boundaries and extracting complete transaction log records; verifying and validating each transaction log record to ensure the integrity and accuracy of the log records, and adding verified transaction log records to the pending log queue.

9. The online DDL synchronization method based on temporary table interception and conversion according to claim 6, characterized in that, When an operation log is detected that renames a temporary table to the source table name and simultaneously renames the source table to another name, it is determined that an Online Data Definition Language (ODL) operation has reached the final switching stage. The execution location or timestamp corresponding to the renaming operation log is recorded as the occurrence point of the landmark event. This includes: parsing the detailed content of the renaming operation log to extract the original name of the source table, the name of the temporary table, and the new name of the renamed table; verifying whether the renaming operation conforms to the standard switching mode of ODL, i.e., whether the temporary table is renamed to the original name of the source table, and whether the source table is renamed to a new name containing an archive identifier; when the standard switching mode is verified, extracting the global transaction identifier and log sequence number of the renaming operation in the database log as the execution location point, and simultaneously recording the system timestamp of the database server; combining the global transaction identifier, log sequence number, and timestamp to generate a unique event identifier, and storing it in the landmark event registry.

10. An online DDL synchronization system based on temporary table interception and conversion, comprising a memory, a processor, and a computer program stored in the memory and running on the processor, characterized in that, When the processor executes the computer program, it implements the steps of the online DDL synchronization method based on temporary table interception and conversion as described in any one of claims 1 to 9.

Images