A method, device and system for correcting ROWID mapping changes during data synchronization

Abstract

The present invention relates to a correction method, device and system for ROWID mapping changes during data synchronization. The method part mainly includes: the target-side data synchronization service adds an auxiliary column to the table synchronized from the source to the target, which is used to store the ROWID of the corresponding row data on the source; after the target-side data synchronization service receives the MOVE operation start command sent by the source, it creates a mapping container; the mapping of the new ROWID and MD5 value received from the source is inserted into the mapping container; after the target-side data synchronization service receives the source-side ROWID range of the cleaned data page sent by the source, it obtains the old data ROWID range from the auxiliary column; constructs a query statement for the corresponding table according to the two ROWID ranges; after the target-side data synchronization service executes the query statement and extracts the result set, it calculates the MD5 value of each row according to the result set and obtains the corresponding new ROWID value in the mapping container, and updates the new ROWID value to the auxiliary column. The present invention can effectively improve the repair performance of the mapping relationship between the source-side ROWID and the target-side data.

Description

Technical Field

[0001] The present invention relates to the field of database technology, and in particular to a method, device and system for correcting ROWID mapping changes during data synchronization. Background Art

[0002] In the database real-time synchronization service (supporting heterogeneity) based on the log parsing architecture, the source-side data synchronization service is responsible for capturing the operation log of the source-side database, parsing the log and restoring the corresponding database operations, and then sending them to the target-side data synchronization service. The target-side data synchronization service is responsible for executing these operations in the target-side database, thereby completing the data synchronization process.

[0003] When the target-side data synchronization service synchronizes operations on the target-side database, some synchronized tables do not have primary keys, resulting in low synchronization efficiency. In this scenario, the ROWID (pseudo-column used to uniquely mark rows in a table) mapping method can effectively improve the synchronization efficiency. ROWID mapping refers to establishing a mapping relationship between the ROWID of the table data in the source database and the data in the target-side database. When operating the source data, the ROWID of the source database is used to quickly locate the data in the target database. This method can effectively improve the synchronization efficiency of tables without primary keys. However, after the ROWID mapping relationship is established, some source databases, such as Oracle databases, will use the MOVE command to move data to other table spaces for business needs. This operation will change the ROWID value of the moved data row, resulting in the inability to use the new ROWID to operate the corresponding data in the target database when operating these data later, resulting in the inability to synchronize data normally.

[0004] In view of this, how to overcome the defects of the existing technology and how to correct the relationship between ROWID mapping data after the ROWID of the source data changes to ensure the accuracy of the relationship between the source ROWID mapping and the target data has become an important technical problem that needs to be solved urgently in the industry. Summary of the invention

[0005] In view of the defects or improvement requirements in the existing technology: how to correct the relationship between ROWID mapping data after the ROWID of the source data changes, so as to ensure the accuracy of the relationship between the source ROWID mapping the target data. The present invention provides a correction method, device and system for ROWID mapping change during data synchronization. The method adopts a comparison and repair mode to update the mapping relationship between the source-side ROWID and the target-side data. First, the ROWID of each row of the new data is collected by parsing the full-column log of the new data and its corresponding MD5 value is calculated (MD5 is a widely used cryptographic hash function that can generate a 128-bit hash value to ensure complete and consistent information transmission). Then, the ROWID range of the source-side database migration data is determined by collecting the cleanup log of the old data page. The ROWID range is used to extract data and its ROWID in the target-side database in the target-side database, and the MD5 is calculated for each extracted row. Finally, the corresponding relationship between the source-side data row and the target-side data row is found by comparing the MD5 values. The one-to-one corresponding relationship of the data MD5 is used to correct the ROWID mapping relationship. This method can effectively improve the repair performance of the mapping relationship between the source-side ROWID and the target-side data.

[0006] The present invention adopts the following technical solution:

[0007] In a first aspect, the present invention provides a method for correcting ROWID mapping changes during data synchronization, comprising:

[0008] The target-side data synchronization service adds an auxiliary column to the table synchronized from the source side to the target side to store the ROWID of the corresponding row data on the source side.

[0009] After receiving the MOVE operation start command sent by the source end, the target end data synchronization service creates a mapping container; inserts the mapping of the new ROWID and MD5 value sent by the source end into the mapping container;

[0010] After receiving the source ROWID range of the cleaned data page sent by the source, the target data synchronization service obtains the old data ROWID range from the auxiliary column; constructs a query statement for the corresponding table based on the two ROWID ranges;

[0011] After the target-side data synchronization service executes the query statement and extracts the result set, it calculates the MD5 value of each row based on the result set and obtains the corresponding new ROWID value in the mapping container, and updates the new ROWID value to the auxiliary column to modify the mapping relationship.

[0012] In some embodiments, the acquisition of the MOVE operation start command includes: after the source-side data synchronization service identifies the MOVE method operation log, sending a MOVE operation start command to the target-side data synchronization service.

[0013] In some embodiments, the acquisition of the mapping between the new ROWID and the MD5 value includes: the source-side data synchronization service parses the insertion log of the new data to obtain the new ROWID value of the new data; the column value of each column of the new data is converted into a string in a unified format, and then sorted by row by column name, and then the MD5 value of the corresponding row after all column values ​​​​except the ROWID column are calculated; the new ROWID of each inserted row is sent to the target-side data synchronization service in combination with the MD5 value of the corresponding row.

[0014] In some embodiments, the query statement for constructing a corresponding table according to two ROWID ranges specifically includes:

[0015] If the old data ROWID range is within the source ROWID range, construct a full table query statement for the corresponding table on the target side; if the old data ROWID range is outside the source ROWID range, use the source ROWID range to construct an interval query statement based on the auxiliary column in the corresponding table on the target side.

[0016] In some embodiments, the query items of the full-table query statement and the interval query statement both include the ROWID column of the target database, but do not include the auxiliary column A, and except for the ROWID column, the column name positions are sorted by column name; wherein, the full-table query statement does not carry a filtering condition, and the interval query statement carries a filtering condition based on the ROWID range of the auxiliary column A.

[0017] In some embodiments, the step of calculating the MD5 value of each row according to the result set and obtaining the corresponding new ROWID value in the mapping container, and updating the new ROWID value to the auxiliary column to modify the mapping relationship specifically includes:

[0018] Convert the column values ​​of each row of data in each column of the extracted result set into strings in a unified format, and then calculate the combined MD5 value of all column values ​​except the ROWID column;

[0019] Use the MD5 value obtained by the target data synchronization service as the KEY to obtain the corresponding new ROWID value of the source data in the mapping container. Use the ROWID of the current row of the target data to locate the new ROWID value of the source data and update the auxiliary column of the table to modify the mapping relationship.

[0020] In some embodiments, the target-end data synchronization service and the source-end data synchronization service use a unified format to convert data and calculate MD5 values.

[0021] In a second aspect, the present invention further provides a device for correcting ROWID mapping changes during data synchronization, the device comprising:

[0022] At least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores instructions executable by the at least one processor, the instructions being executed by the processor to execute the method for correcting ROWID mapping changes during data synchronization as described in the first aspect.

[0023] In a third aspect, the present invention further provides a system for correcting ROWID mapping changes during data synchronization, applying the method for correcting ROWID mapping changes during data synchronization as described in the first aspect, the system comprising an auxiliary column module, a mapping container module, a query statement construction module and a mapping correction module, wherein:

[0024] The auxiliary column module is used to enable the target-side data synchronization service to add an auxiliary column to the table synchronized from the source side to the target side, which is used to store the ROWID of the corresponding row data on the source side;

[0025] The mapping container module is used to enable the target end data synchronization service to create a mapping container after receiving the MOVE operation start command sent by the source end; insert the mapping of the new ROWID and MD5 value sent by the source end into the mapping container;

[0026] The query statement construction module is used to enable the target end data synchronization service to obtain the old data ROWID range from the auxiliary column after receiving the source end ROWID range of the cleaned data page sent by the source end; and construct a query statement for the corresponding table according to the two ROWID ranges;

[0027] The mapping correction module is used to enable the target end data synchronization service to execute the query statement and extract the result set, calculate the MD5 value of each row according to the result set and obtain the corresponding new ROWID value in the mapping container, and update the new ROWID value to the auxiliary column to realize the correction of the mapping relationship.

[0028] In a fourth aspect, the present invention further provides a non-volatile computer storage medium storing computer executable instructions, which are executed by one or more processors to complete the method for correcting ROWID mapping changes during data synchronization as described in the first aspect.

[0029] Compared with the prior art, the present invention provides a correction method, device and system for ROWID mapping changes during data synchronization, and its beneficial effects are: a comparison and repair mode is adopted to update the mapping relationship between the source-side ROWID and the target-side data, firstly, the ROWID of each row of the new data is collected by parsing the full-column log of the new data and its corresponding MD5 value is calculated, and then the ROWID range of the source-side database migration data is determined by collecting the cleanup log of the old data page, and the ROWID range is used to extract the data and its ROWID in the target-side database in the target-side database, and the MD5 is calculated for each extracted row, and finally the corresponding relationship between the source-side data row and the target-side data row is found by comparing the MD5 values, and the ROWID mapping relationship is corrected by adopting this one-to-one correspondence of the data MD5, which can effectively improve the repair performance of the mapping relationship between the source-side ROWID and the target-side data. BRIEF DESCRIPTION OF THE DRAWINGS

[0030] In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the following briefly introduces the drawings required to be used in the embodiments of the present invention. Obviously, the drawings described below are only some embodiments of the present invention, and for ordinary technicians in this field, other drawings can be obtained based on these drawings without creative work.

[0031] Figure 1 A flowchart of a method for correcting ROWID mapping changes during data synchronization provided by Embodiment 1 of the present invention;

[0032] Figure 2 A flow chart of the source data synchronization service operation provided by Embodiment 1 of the present invention;

[0033] Figure 3 A flow chart of target-side data synchronization service operation provided by Embodiment 1 of the present invention;

[0034] Figure 4 A schematic diagram of a module of a system for correcting ROWID mapping changes during data synchronization provided by Embodiment 1 of the present invention;

[0035] Figure 5 A schematic diagram of the structure of a device for correcting ROWID mapping changes during data synchronization provided by Embodiment 3 of the present invention. DETAILED DESCRIPTION

[0036] The present invention is described in detail below in conjunction with specific embodiments. The following embodiments will help those skilled in the art to further understand the present invention, but do not limit the present invention in any form. It should be noted that, for those of ordinary skill in the art, without departing from the concept of the present invention, several variations and improvements can also be made. These all belong to the protection scope of the present invention. It should be noted that, if there is no conflict, the various features in the embodiments of the present invention can be combined with each other, all within the protection scope of this application. In addition, although the functional module division may be performed in the device schematic diagram, and the logical order may be shown in the flow chart, in some cases, it may be different from the module division in the device, or the steps shown or described in the order in the flow chart may be performed.

[0037] Unless otherwise defined, all technical and scientific terms used in this specification have the same meaning as those commonly understood by those skilled in the art of the present invention. The terms used in this specification and in the specification of the present invention are only for the purpose of describing specific embodiments and are not intended to limit the present invention. In addition, the technical features involved in each embodiment of the present invention described below can be combined with each other as long as they do not conflict with each other.

[0038] Unless the context requires otherwise, throughout the specification and claims, the term "including" is to be interpreted as open inclusion, that is, "including, but not limited to". In the description of the specification, the terms "one embodiment", "some embodiments", "exemplary embodiments", "examples", "specific examples" or "some examples" and the like are intended to indicate that specific features, structures, materials or characteristics associated with the embodiment or example are included in at least one embodiment or example of the present disclosure. The schematic representation of the above terms does not necessarily refer to the same embodiment or example. In addition, the specific features, structures, materials or characteristics may be included in any one or more embodiments or examples in any appropriate manner, that is, although they may be carried in the embodiments or examples of the above terms due to reasons such as the order and position of appearance, it is not limited to that they can be carried in combination by one embodiment or example.

[0039] In order to make the purpose, technical scheme and advantages of the present application more clearly understood, the present application is further described in detail below in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present application and are not intended to limit the present application. The present invention is described in detail below with reference to the accompanying drawings and embodiments.

[0040] Embodiment 1:

[0041] like Figure 1 As shown, an embodiment of the present invention provides a method for correcting ROWID mapping changes during data synchronization, and the method includes the following steps.

[0042] Step 101: The target-side data synchronization service adds an auxiliary column to the table synchronized from the source side to the target side, which is used to store the ROWID of the corresponding row data on the source side.

[0043] Step 102: After the target-side data synchronization service receives the MOVE operation start command sent by the source, it creates a mapping container; and inserts the mapping of the new ROWID and MD5 value received from the source into the mapping container. Wherein, the acquisition of the MOVE operation start command includes: after the source-side data synchronization service identifies the MOVE method operation log, it sends a MOVE operation start command to the target-side data synchronization service. The acquisition of the mapping of the new ROWID and MD5 value includes: the source-side data synchronization service parses the insertion log of the new data to obtain the new ROWID value of the new data; converts the column value of each column of the new data into a string in a unified format, and then sorts the columns by row and column name, and then calculates the MD5 value of all column values ​​of the corresponding row except the ROWID column; sends the new ROWID of each inserted row combined with the MD5 value of the corresponding row to the target-side data synchronization service.

[0044] Step 103: After the target-side data synchronization service receives the source-side ROWID range of the cleaned data page sent by the source side, it obtains the old data ROWID range from the auxiliary column; and constructs a query statement for the corresponding table based on the two ROWID ranges. Specifically, if the old data ROWID range is within the source-side ROWID range, a full-table query statement for the corresponding table on the target side is constructed; if the old data ROWID range is outside the source-side ROWID range, an interval query statement based on the auxiliary column is constructed in the target-side corresponding table using the source-side ROWID range. The query items of the full-table query statement and the interval query statement both include the ROWID column of the target-side database, but do not include the auxiliary column A, and except for the ROWID column, the column name positions are sorted by column name; the full-table query statement does not carry a WHERE filter condition, and the interval query statement carries a filter condition based on the ROWID range of the auxiliary column A.

[0045] In addition, this can also be achieved by using auxiliary column A to replace the ROWID in the query item, and the old ROWID value in auxiliary column A can be used as a condition to update to the new ROWID value.

[0046] Step 104: After the target-side data synchronization service executes the query statement and extracts the result set, it calculates the MD5 value of each row based on the result set and obtains the corresponding new ROWID value in the mapping container, and updates the new ROWID value to the auxiliary column to modify the mapping relationship. Specifically, the column value of each column of each row of data in the extracted result set is converted into a string in a unified format, and then the MD5 value of all column values ​​except the ROWID column is calculated after the combination; the MD5 value obtained by the target-side data synchronization service is used as the KEY to obtain the corresponding new ROWID value of the source data in the mapping container, and the new ROWID value of the source data is updated to the auxiliary column of the table through the ROWID positioning of the current row of the target-side data to modify the mapping relationship. It should be noted that the target-side data synchronization service and the source-side data synchronization service use a unified format to convert data and calculate MD5 values.

[0047] It should be noted that using the source ROWID to map the target data in data synchronization is a common solution to improve the synchronization performance of tables without primary keys. When the table in the source ORACLE database executes the MOVE method, the data moves between different tablespaces, and the ROWID value of the data changes. ORACLE's MOVE method generates a full-column log of the new data after migration and a log for cleaning up old data pages. The existing method is to parse the full-column log of the new data into full-column data, and use full-column matching as a condition to update the ROWID mapping relationship between the data in the target database and the data in the source database. Since the table on the target side does not have a primary key, the update will be very slow when the data scale is large.

[0048] Through the above steps, this embodiment adopts a comparative repair mode to update the mapping relationship between the source-side ROWID and the target-side data. First, the ROWID of each row of the new data is collected and its corresponding MD5 value is calculated by parsing the full-column log of the new data. Then, the ROWID range of the source-side database migration data is determined by collecting the cleanup log of the old data page. The ROWID range is used to extract the data and its ROWID in the target-side database in the target-side database, and the MD5 is calculated for each extracted row. Finally, the corresponding relationship between the source-side data row and the target-side data row is found by comparing the MD5 values. The one-to-one correspondence of the data MD5 is used to correct the ROWID mapping relationship. This method can effectively improve the repair performance of the mapping relationship between the source-side ROWID and the target-side data.

[0049] Based on the above method, the technical content of the embodiment of the present invention is further described by describing in detail the operations on the source and destination ends respectively. First, a synchronization service is deployed on the source database and the destination database. The source database synchronization service reads logs from the source database, while the target database synchronization service is responsible for storing the synchronization operations sent by the source end in transactions. The table synchronized from the source to the target database needs to add an auxiliary column A on the target side to store the ROWID of the row data in the source database, and a unique index needs to be created on the column A. When the source performs a synchronous insert operation, the ROWID value of the data on the source side needs to be filled in the auxiliary column A. When synchronizing the update and delete operations on the source side, the ROWID in the operation can be used as a condition to match the auxiliary column A to quickly locate the data.

[0050] refer to Figure 2 As shown in the figure, the source data synchronization service captures the ORACLE MOVE method operation log and parses it according to the following steps. It should be noted that when ORACLE's MOVE method moves data to a new tablespace, it will first generate the insertion log of the new data and then the cleanup log of the old data page. Based on this feature, the following strategy is used to parse the log.

[0051] Step 201: Identify the MOVE method operation log and send a MOVE operation start command to the target data synchronization service.

[0052] Step 202: Parse the insertion log of new data to obtain the ROWID value of the new data.

[0053] Step 203: Convert the column value of each column of the new data into a string in a unified format, then sort by row by column name, and then calculate the MD5 value of all column values ​​in the row. The source and target data synchronization services use a unified format to convert data and calculate MD5 values. The order of columns must be consistent to ensure that the MD5 values ​​generated by the same data on the source and target are consistent.

[0054] Step 204: Send the ROWID of each inserted row combined with the MD5 value of the row to the target data synchronization service.

[0055] Step 205: parse the cleanup log of old data pages, calculate the minimum and maximum ROWID values ​​of each data page, and count the minimum and maximum ROWID values ​​of all cleaned data pages, and deliver the minimum and maximum ROWID values ​​to the target end data synchronization service.

[0056] refer to Figure 3 As shown, after receiving the MOVE operation start command sent by the source end, the target end data synchronization service performs the following actions.

[0057] Step 301: Create a mapping container P (eg, HASH or B-tree), receive the mapping of ROWID and MD5 value sent by the source, and insert it into the mapping container P using the MD5 value as the KEY.

[0058] Step 302: Receive the minimum and maximum ROWIDs of the cleanup data page sent by the source end, and combine them into a condition range [R1, R2].

[0059] Step 303: Get the minimum ROWID and maximum ROWID of the old data from the auxiliary column A of the synchronization table of the target database, and determine whether these two ROWIDs are in the source ROWID range [R1, R2]. If yes, jump to step 304 for execution; otherwise: jump to step 305 for execution.

[0060] Step 304: The minimum ROWID and the maximum ROWID of the old data in auxiliary column A are in the source-side ROWID range [R1, R2], indicating that the source-side ORACLE database has performed a MOVE operation on the entire table. It is necessary to construct a full-table query statement Q for the corresponding table of the target-side database, in which the query items include the target-side database ROWID column but do not include auxiliary column A, and except for the ROWID column, the column name positions need to be sorted by column name.

[0061] Step 305: The minimum ROWID and maximum ROWID of the old data in auxiliary column A are outside the source ROWID range [R1, R2], indicating that the source ORACLE database has performed a MOVE operation on a partition of the table. Here, it is only necessary to construct an interval query statement Q based on auxiliary column A using the source ROWID range [R1, R2] in the corresponding table of the target database, where the query item includes the target database ROWID column but does not include auxiliary column A, and except for the ROWID column, the column name positions need to be sorted by column name.

[0062] Step 306: Execute the query statement Q in the target database and extract the result set. Convert the column value of each row of data in each column of the extracted result set into a string in a unified format, and then calculate the MD5 value of all column values ​​except the ROWID column after the combination.

[0063] Step 307: Use the MD5 value obtained on the target side as the KEY to obtain the new ROWID value of the corresponding source data in the mapping container P, delete the located mapping relationship from the container P, and update the new ROWID value of the source data to the auxiliary column A of the table through the ROWID of the current row of the target side data to repair the mapping relationship.

[0064] Deleting the located mapping relationship from the mapping container P can effectively solve the problem of duplicate rows.

[0065] The above solution is mainly used when there is a ROWID relationship mapping between the source data and the target data in the data synchronization environment. When the ROWID of the source data changes, the MD5 value of the new data is parsed and compared with the MD5 value of the target data to find the association between the new data on the source side and the old data on the target side, and to update the ROWID in the previous auxiliary column A, thereby ensuring the correctness of the subsequent synchronization update and deletion operations using the source ROWID.

[0066] The basic steps of the above embodiment can be explained as follows:

[0067] First, when the source ORACLE database performs a MOVE operation, it will generate a log of the new data. By sending the ROWID of the new data row and calculating the MD5 of the new data, the network bandwidth usage can be effectively reduced. In addition, the source and target data synchronization services use a unified format to convert data and the order of columns when calculating the MD5 value is consistent to ensure that the MD5 values ​​generated by the same data on the source and target are consistent.

[0068] Secondly, when parsing the data page cleanup log generated by the MOVE operation, the source side only needs to count the minimum and maximum ROWIDs of all cleanup data pages to form a ROWID range and send this range to the target side data synchronization service. It only needs to know which ranges of the data source ROWID in the auxiliary column A have changed, and does not need to know every row of data where the ROWID has changed.

[0069] Finally, the target-side data synchronization first uses the ROWID sent by the source side in combination with MD5 to build a mapping container P, and then uses the ROWID range sent by the source side to determine whether it is a MOVE operation of the entire table or a MOVE operation of a sub-partition, thereby determining the data query statement Q of the target-side database. By executing the query and calculating the MD5 value of each row of data, the corresponding relationship between the target-side data ROWID and the source-side new data ROWID is found in the mapping container P through the MD5 value to update the source-side ROWID in the auxiliary column A, thereby achieving the maintenance of the mapping between the source-side ROWID and the target-side data.

[0070] The embodiment of the present invention uses a mapping container P to quickly build a corresponding relationship between the data with ROWID changes on the source side and the data on the target side, avoiding the problem of synchronization performance loss caused by executing an update statement on a table without a primary key and an index on the target side to update the ROWID mapping relationship in the traditional solution. The embodiment of the present invention uses the fast mapping feature of the mapping container to improve the overall synchronization performance.

[0071] Based on the above method, this embodiment also provides a correction system for ROWID mapping changes during data synchronization, referring to Figure 4 As shown, the system includes an auxiliary column module, a mapping container module, a query statement construction module, and a mapping correction module, wherein: the auxiliary column module is used to enable the target-side data synchronization service to add an auxiliary column to the table synchronized from the source end to the target end, which is used to store the ROWID of the corresponding row data on the source end; the mapping container module is used to enable the target-side data synchronization service to create a mapping container after receiving the MOVE operation start command sent by the source end; insert the mapping of the new ROWID and MD5 value received from the source end into the mapping container; the query statement construction module is used to enable the target-side data synchronization service to obtain the old data ROWID range from the auxiliary column after receiving the source-side ROWID range of the cleaned data page sent by the source end; construct the query statement of the corresponding table according to the two ROWID ranges; the mapping correction module is used to enable the target-side data synchronization service to execute the query statement and extract the result set, calculate the MD5 value of each row according to the result set and obtain the corresponding new ROWID value in the mapping container, and update the new ROWID value to the auxiliary column to realize the correction of the mapping relationship. The specific implementation process of each module can refer to the above method steps, which will not be repeated here.

[0072] Embodiment 2:

[0073] Based on the correction method for ROWID mapping changes during data synchronization provided in the above-mentioned embodiment 1, embodiment 2 of the present invention provides a specific example for illustration.

[0074] Examples of the above scheme are as follows:

[0075] 1. Execute the table creation statement in the source database:

[0076] CREATE TABLE T(ID INT);

[0077] After synchronization to the target end, an auxiliary column A will be added to the T table to store the ROWID of the source data. The table creation statement on the target end becomes:

[0078] CREATE TABLE T(ID INT, A VARCHAR(20)UNIQUE).

[0079] 2. Execute the insert statement in the source database:

[0080] INSERT INTO T(ID)VALUES(11);

[0081] INSERT INTO T(ID)VALUES(22);

[0082] INSERT INTO T(ID)VALUES(33);

[0083] COMMIT;

[0084] The above operation will form the following table in the source table T data:

[0085] ROWID ID AAAcOpAAEAANaODAAA 11 AAAcOpAAEAANaODAAB 22 AAAcOpAAEAANaODAAC 33

[0086] After the above operations are synchronized to the target end, the data in table T on the target end will form the following table:

[0087] ROWID ID A 1 11 AAAcOpAAEAANaODAAA 2 22 AAAcOpAAEAANaODAAB 3 33 AAAcOpAAEAANaODAAC

[0088] From the above table, we can see that auxiliary column A stores the ROWID of the T table data in the source database.

[0089] 3. Execute the delete statement in the source database:

[0090] DELETE FROM T WHERE ID=22;

[0091] COMMIT;

[0092] After the above deletion is executed, the data in the source table T will form the following table:

[0093] ROWID ID AAAcOpAAEAANaODAAA 11 AAAcOpAAEAANaODAAC 33

[0094] Since table T has ROWID mapping on the source and target ends, the deletion operation can be quickly performed on the target end with the help of the ROWID mapping function: DELETE FROM T WHERE A='AAAcOpAAEAANaODAAB'.

[0095] After the above operations are synchronized to the target end, the data in table T on the target end will form the following table:

[0096] ROWID ID A 1 11 AAAcOpAAEAANaODAAA 3 33 AAAcOpAAEAANaODAAC

[0097] 4. Perform the MOVE operation on the source database, and the source data synchronization service will capture and parse it:

[0098] ALTER TABLE T MOVE.

[0099] (1) After capturing the MOVE operation, send a MOVE start operation to the target end.

[0100] (2) Analyze the insertion log of new data after migration and restore the insertion operation of new data:

[0101] INSERT INTO T(ID)VALUES(11);--ROWID:AAAcOpAAEBBNaODAAA

[0102] INSERT INTO T(ID)VALUES(33);--ROWID:AAAcOpAAEBBBNaODAAB

[0103] Extract the ROWID and generate the MD5 value for each row of data according to the rules:

[0104] ROWID MD5 (for the convenience of presentation, column values ​​are used instead) AAAcOpAAEBBNaODAAA 11 AAAcOpAAEBBNaODAAB 33

[0105] The ROWID and MD5 of the two rows of new data are delivered to the target data synchronization service.

[0106] (3) Parse the log page cleanup log and count the minimum and maximum ROWIDs of all cleanup pages. If this table has only one data page, the ROWID range collected by the cleanup is [AAAcOpAAEAANaODAAA, AAAcOpAAEAANaOD / / / ], and send it to the target data synchronization service.

[0107] 5. The target data synchronization service performs the MOVE operation.

[0108] (1) After receiving the MOVE operation, the operation starts and a mapping container P of a HASH structure is created.

[0109] (2) Receive the data row sent by the source end with ROWID combined with MD5, and insert it into the mapping container P using MD5 as the KEY.

[0110] (3) After receiving the log cleanup ROWID range [AAAcOpAAEAANaODAAA, AAAcOpAAEAANaOD / / / ] sent by the source end, the minimum value AAAcOpAAEAANaODAAA and the maximum value AAAcOpAAEAANaODAAC of the auxiliary column A of the target database table T are extracted. It is found that it is within the cleanup ROWID range of the source end. The full table query statement Q is constructed according to the above rules:

[0111] SELECT ROWID, ID FROM T;

[0112] Note: If it is not within the cleanup range on the source side, use the ROWID range on the source side to construct an interval query statement Q based on the auxiliary column A:

[0113] SELECT ROWID, ID FROM T WHERE A>='AAAcOpAAEAANaODAAA' AND A<='AAAcOpAAEAANaODAAC'.

[0114] (4) Query the result set and calculate the MD5 value for each column except the ROWID column.

[0115] (5) Extract the first row, where ROWID is 1 and the MD5 value calculated from the ID column is 11. The ROWID of the new data on the source side is obtained in container P through the MD5 value, which is AAAcOpAAEBBNaODAAA. The mapped relationship is deleted from container P, and then the ROWID mapping relationship in the auxiliary column A of the T table in the target database is updated. Execute: UPDATE T SET A = 'AAAcOpAAEBBNaODAAA' WHERE ROWID = 1.

[0116] (6) The second row is extracted. The ROWID is 3. The MD5 value calculated by the ID column is 33. The ROWID of the new data on the source side is obtained in the container P through the MD5 value, which is AAAcOpAAEBBNaODAAB. The ROWID mapping relationship in the auxiliary column A of the T table in the target database is updated. Execute: UPDATE T SET A='AAAcOpAAEBBNaODAAB' WHERE ROWID=3.

[0117] (7) After the result set is extracted, the ROWID mapping relationship is also repaired, and the data in table T becomes:

[0118] ROWID ID A 1 11 AAAcOpAAEBBNaODAAA 3 33 AAAcOpAAEBBNaODAAB

[0119] It can be seen from the above process that a row of data is deleted from the source-side table T, resulting in a hole between the first and second rows of the table T. At this time, the data of the table T is reorganized by executing the MOVE method operation on the source-side database, resulting in the change of the ROWID of the first and second rows of the data in the source-side table T. At this time, the auxiliary column A of the target-side table T still stores the old ROWID from the source. If the traditional update method is used to correct the ROWID in the auxiliary column A, when the data scale of the target-side table T is large, the efficiency of the update statement will be very poor. The embodiment of the present invention uses a mapping container to find the mapping relationship between the reorganized data and the same row in the target-side table, and then performs a rapid positioning update through the ROWID of the target-side database table, thereby repairing the mapping relationship between the source-side database and the target-side database at a faster speed.

[0120] It should be noted that ROWID mapping can be implemented in two ways. The first is to create an auxiliary column in the target database table as exemplified in the above embodiment, and directly fill the ROWID of the source end into the auxiliary column of each row. The advantage of this method is high efficiency, because the source ROWID and the target data are in the same row. The disadvantage is that the table structure of the target table is destroyed because there is an additional auxiliary column. The second is to create an independent source ROWID and target ROWID mapping table. The ROWID mapping relationship between the source database and the target database is stored in an independent mapping table. When the source operates the data, it first finds the ROWID of the target database in this independent table through the ROWID of the source operation, and then uses the ROWID of the target to operate the target database. The advantage of this solution is that it will not destroy the table structure of the target end. The disadvantage is that there is an additional independent mapping, and the performance is worse than the first solution.

[0121] When the second ROWID mapping method is adopted, the method for correcting ROWID mapping changes during data synchronization provided by the present invention may include the following steps: creating an independent ROWID mapping table of the source ROWID and the target ROWID; after the target data synchronization service receives the MOVE operation start command sent by the source, it creates a mapping container; inserts the mapping of the new ROWID and MD5 value received from the source into the mapping container; after the target data synchronization service receives the source ROWID range of the cleaned data page sent by the source, it obtains the old data ROWID range from the ROWID mapping table, and constructs a query statement for the corresponding table based on the two ROWID ranges; after the target data synchronization service executes the query statement and extracts the result set, it calculates the MD5 value of each row according to the result set and obtains the corresponding new ROWID value in the mapping container, and updates the new ROWID value to the ROWID mapping table to realize the correction of the mapping relationship.

[0122] The above two methods are used to implement the correction of ROWID mapping changes during data synchronization. The general steps are the same. The only difference is the ROWID mapping. The first method is to directly map the source ROWID to the target data, and the second method is to map the source ROWID to the target ROWID. The data needs to be operated through the target ROWID. For the other steps of the second method, refer to the detailed description of the first method. You only need to modify the auxiliary column in the first method to the ROWID mapping table, which will not be repeated here.

[0123] Embodiment 3:

[0124] Based on the method for correcting the change of ROWID mapping during data synchronization provided in the above embodiment 1, the present invention also provides a device for correcting the change of ROWID mapping during data synchronization that can be used to implement the above method and system, such as Figure 5, which is a schematic diagram of the device architecture of an embodiment of the present invention. The device for correcting ROWID mapping changes during data synchronization in this embodiment includes one or more processors 21 and a memory 22. Figure 5 A processor 21 is taken as an example.

[0125] The processor 21 and the memory 22 may be connected via a bus or other means. Figure 5 The example of connecting through bus is taken in the following.

[0126] The memory 22, as a non-volatile computer-readable storage medium, can be used to store non-volatile software programs, non-volatile computer executable programs and modules, such as the correction method for ROWID mapping changes during data synchronization in Example 1. The processor 21 executes various functional applications and data processing of the correction device for ROWID mapping changes during data synchronization by running the non-volatile software programs, instructions and modules stored in the memory 22, that is, the correction method for ROWID mapping changes during data synchronization in Example 1 is implemented.

[0127] The memory 22 may include a high-speed random access memory, and may also include a non-volatile memory, such as at least one disk storage device, a flash memory device, or other non-volatile solid-state storage device. In some embodiments, the memory 22 may optionally include a memory remotely arranged relative to the processor 21, and these remote memories may be connected to the processor 21 via a network. Examples of the above-mentioned network include, but are not limited to, the Internet, an intranet, a local area network, a mobile communication network, and combinations thereof.

[0128] The program instructions / modules are stored in the memory 22, and when executed by one or more processors 21, the correction method for changing the ROWID mapping during data synchronization in the above embodiment 1 is executed, for example, the above described Figure 1-Figure 3 The steps shown.

[0129] The above-mentioned product can execute the method provided in the embodiment of the present application, and has the functional modules and beneficial effects corresponding to the execution method. For technical details not fully described in this embodiment, please refer to the method provided in the embodiment of the present application.

[0130] It should be noted that the device embodiments described above are merely illustrative, wherein the units described as separate components may or may not be physically separated, and the components displayed as units may or may not be physical units, that is, they may be located in one place, or may be distributed on multiple network units. Some or all of the modules may be selected according to actual needs to achieve the purpose of the solution of this embodiment.

[0131] Through the description of the above implementation methods, ordinary technicians in this field can clearly understand that each implementation method can be implemented by means of software plus a general hardware platform, and of course, it can also be implemented by hardware. Ordinary technicians in this field can understand that all or part of the processes in the above-mentioned embodiment method can be completed by instructing related hardware through a computer program, and the program can be stored in a computer-readable storage medium. When the program is executed, it can include the processes of the embodiments of the above-mentioned methods. Among them, the storage medium can be a disk, an optical disk, a read-only memory (ROM) or a random access memory (RAM), etc.

[0132] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit them. Under the concept of the present invention, the technical features in the above embodiments or different embodiments may also be combined, the steps may be implemented in any order, and there are many other changes in different aspects of the present invention as described above, which are not provided in detail for the sake of simplicity. Although the present invention has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that the technical solutions described in the aforementioned embodiments may still be modified, or some of the technical features may be replaced by equivalents. However, these modifications or replacements do not deviate the essence of the corresponding technical solutions from the scope of the technical solutions of the embodiments of the present invention.

Claims

1. A method for correcting ROWID mapping changes during data synchronization, characterized in that: include: The target-side data synchronization service adds an auxiliary column to the table synchronized from the source side to the target side to store the ROWID of the corresponding row data on the source side; After receiving the MOVE operation start command sent by the source end, the target end data synchronization service creates a mapping container; inserts the mapping of the new ROWID and MD5 value sent by the source end into the mapping container; After receiving the source ROWID range of the cleaned data page sent by the source, the target data synchronization service obtains the old data ROWID range from the auxiliary column; constructs a query statement for the corresponding table based on the two ROWID ranges; After the target-side data synchronization service executes the query statement and extracts the result set, it calculates the MD5 value of each row based on the result set and obtains the corresponding new ROWID value in the mapping container, and updates the new ROWID value to the auxiliary column to modify the mapping relationship.

2. The method for correcting ROWID mapping changes during data synchronization according to claim 1, characterized in that: The acquisition of the MOVE operation start command includes: after the source-side data synchronization service identifies the MOVE method operation log, sending a MOVE operation start command to the target-side data synchronization service.

3. The method for correcting ROWID mapping changes during data synchronization according to claim 1, characterized in that: The acquisition of the mapping between the new ROWID and the MD5 value includes: the source-side data synchronization service parses the insertion log of the new data to obtain the new ROWID value of the new data; the column value of each column of the new data is converted into a string in a unified format, and then sorted by row by column name, and then the MD5 value of all column values ​​of the corresponding row except the ROWID column is calculated; the new ROWID of each inserted row is sent to the target-side data synchronization service in combination with the MD5 value of the corresponding row.

4. The method for correcting ROWID mapping changes during data synchronization according to claim 1, characterized in that: The query statement for constructing a corresponding table according to two ROWID ranges specifically includes: If the old data ROWID range is within the source ROWID range, construct a full table query statement for the corresponding table on the target side; if the old data ROWID range is outside the source ROWID range, use the source ROWID range to construct an interval query statement based on the auxiliary column in the corresponding table on the target side.

5. The method for correcting ROWID mapping changes during data synchronization according to claim 4, characterized in that: The query items of the full-table query statement and the interval query statement both include the ROWID column of the target database, but do not include the auxiliary column A, and except for the ROWID column, the column name positions are sorted by column name; wherein, the full-table query statement does not carry a filtering condition, and the interval query statement carries a filtering condition based on the ROWID range of the auxiliary column A.

6. The method for correcting ROWID mapping changes during data synchronization according to claim 1, characterized in that: The step of calculating the MD5 value of each row according to the result set and obtaining the corresponding new ROWID value in the mapping container, and updating the new ROWID value to the auxiliary column to modify the mapping relationship specifically includes: Convert the column values ​​of each row of data in each column of the extracted result set into strings in a unified format, and then calculate the combined MD5 value of all column values ​​except the ROWID column; Use the MD5 value obtained by the target data synchronization service as the KEY to obtain the corresponding new ROWID value of the source data in the mapping container. Use the ROWID of the current row of the target data to locate the new ROWID value of the source data and update the auxiliary column of the table to modify the mapping relationship.

7. The method for correcting ROWID mapping changes during data synchronization according to any one of claims 1 to 6, characterized in that: The target-end data synchronization service and the source-end data synchronization service use a unified format to convert data and calculate MD5 values.

8. A device for correcting ROWID mapping changes during data synchronization, characterized in that: The device includes: at least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores instructions executable by the at least one processor, and the instructions are executed by the processor to execute the method for correcting ROWID mapping changes during data synchronization as described in any one of claims 1-7.

9. A system for correcting ROWID mapping changes during data synchronization, using the method for correcting ROWID mapping changes during data synchronization as claimed in any one of claims 1 to 7, characterized in that: The system includes an auxiliary column module, a mapping container module, a query statement construction module and a mapping correction module, wherein: The auxiliary column module is used to enable the target-side data synchronization service to add an auxiliary column to the table synchronized from the source side to the target side, which is used to store the ROWID of the corresponding row data on the source side; The mapping container module is used to enable the target end data synchronization service to create a mapping container after receiving the MOVE operation start command sent by the source end; insert the mapping of the new ROWID and MD5 value sent by the source end into the mapping container; The query statement construction module is used to enable the target end data synchronization service to obtain the old data ROWID range from the auxiliary column after receiving the source end ROWID range of the cleaned data page sent by the source end; and construct a query statement for the corresponding table according to the two ROWID ranges; The mapping correction module is used to enable the target end data synchronization service to execute the query statement and extract the result set, calculate the MD5 value of each row according to the result set and obtain the corresponding new ROWID value in the mapping container, and update the new ROWID value to the auxiliary column to realize the correction of the mapping relationship.

10. A non-volatile computer storage medium, characterized in that: The computer storage medium stores computer executable instructions, which are executed by one or more processors to complete the method for correcting ROWID mapping changes during data synchronization as described in any one of claims 1-7.

Images