A method and system for checking and repairing table structure consistency in GoldenDB database

Abstract

This invention discloses a method and system for checking and repairing table structure consistency in GoldenDB databases, relating to the field of database data processing technology. The method includes: extracting standard metadata of distributed tables from the metadata dictionary table of the GoldenDB management node (MDS) as a consistency check baseline, and obtaining the sharding mapping relationship between logical tables and data nodes (DNs) based on the metadata; connecting each shard master DN in parallel according to the sharding mapping relationship, collecting the actual physical table structure metadata of the corresponding logical tables, and performing standardized preprocessing on the collected metadata; comparing the standardized sharding metadata with the baseline metadata in a layered manner from coarse to fine granularity, identifying metadata differences and classifying risk levels, matching corresponding repair strategies according to the risk levels, generating and executing adapted DDL repair scripts; after repair, re-executing metadata collection and comparison to verify whether the differences have been eliminated, recording a repair success log based on the verification results, or performing a rollback and recording the cause of the exception.

Description

Technical Field

[0001] This invention relates to the field of database data processing technology, and in particular to a method and system for checking and repairing the consistency of table structure in a GoldenDB database. Background Technology

[0002] Against the backdrop of the accelerating pace of domestic database development, GoldenDB has gained widespread application in industries such as finance and telecommunications due to its superior performance and adaptability. GoldenDB provides high availability and scalability by sharding data across multiple physical nodes, with table structures typically distributed uniformly from the Management Node (MDS) to each Data Node (DN).

[0003] In production environment operations, scenarios such as momentary network outages, node failures, software defects, and partial node execution failures during DDL (Data Definition Language) execution can lead to inconsistencies between the metadata of each shard and the metadata stored in the global data dictionary. This inconsistency is often subtle and may not be immediately apparent from routine SQL operations, but it can cause errors such as data write failures, incorrect query results, replication interruptions, and even node crashes. Current common practices rely on the database's own strong consistency protocol or periodic manual inspections. The former is not necessarily entirely reliable, while the latter suffers from inefficiency, high latency, susceptibility to omissions, and high repair risks.

[0004] Therefore, an efficient and secure metadata consistency check and automated repair solution is needed to address the pain points of GoldenDB metadata consistency maintenance. Summary of the Invention

[0005] This invention provides a method and system for checking and repairing the table structure consistency of a GoldenDB database, in order to solve the technical problems of low reliability and efficiency in maintaining metadata consistency in existing GoldenDB databases.

[0006] On one hand, this invention provides a method for checking and repairing the table structure consistency of a GoldenDB database. The method includes the following steps: Step S1: Extract standard metadata of distributed tables from the metadata dictionary table of the GoldenDB management node MDS as a consistency check baseline, and obtain the sharding mapping relationship between logical tables and data nodes (DNs) based on the metadata; Step S2: Connect each shard master DN in parallel according to the sharding mapping relationship, collect the actual physical table structure metadata of the corresponding logical tables, and perform standardized preprocessing on the collected metadata; Step S3: Compare the standardized sharding metadata with the baseline metadata in layers from coarse to fine granularity, identify metadata differences and classify risk levels, match corresponding repair strategies according to risk levels, and generate and execute adapted DDL repair scripts; Step S4: After the repair is completed, re-execute metadata collection and comparison to verify whether the differences have been eliminated, record a repair success log according to the verification results, or perform a rollback and record the reason for the exception.

[0007] In one implementation of the present invention, the standard metadata in step S1 includes at least one of table name, table type, character set, storage engine, field information, index information, and partitioning rules.

[0008] In one implementation of the present invention, the standardization preprocessing in step S2 includes: uniformly converting field names and index names to lowercase, uniformly encoding character sets, and sorting according to the preset field definition order.

[0009] In one implementation of the present invention, the hierarchical comparison in step S3 includes four levels in sequence: table existence comparison, field consistency comparison, primary and foreign key consistency comparison, and index consistency comparison.

[0010] In one implementation of the present invention, the table existence comparison includes table definition existence verification and table comment consistency verification; the field consistency comparison includes field definition verification, field order verification, and field comment verification, and the fields include core fields and non-core fields; the primary and foreign key consistency comparison includes primary and foreign key existence verification and primary and foreign key field order verification; the index consistency comparison includes index existence verification and index field order verification, and the indexes include ordinary indexes and unique indexes.

[0011] In one implementation of the present invention, in step S3, the risk level is divided into three levels: low risk, medium risk, and high risk. The low-risk differences are differences in table annotation consistency and field annotation consistency; the medium-risk differences are differences in non-core field definitions, differences in non-core field order, differences in the existence of ordinary indexes, and differences in the order of ordinary index fields; the high-risk differences are differences in missing table definitions, differences in core field definitions and core field order, differences in the existence of primary and foreign keys and differences in the order of primary and foreign key fields, differences in the existence of unique indexes and differences in the order of unique index fields.

[0012] In one implementation of the present invention, the repair strategy corresponding to the risk level is as follows: for low-risk differences, the table structure is automatically backed up and then directly connected to the main DN to execute the repair script; for medium-risk differences, the repair script is executed after being pushed to the operation and maintenance platform and manually reviewed; for high-risk differences, automatic repair is prohibited and an alarm is triggered, and the repair is performed manually according to the repair plan.

[0013] In one implementation of the present invention, step S2 involves collecting the actual physical table structure metadata of each master DN by executing a preset metadata query statement, and using a parallel connection method to improve the metadata collection efficiency.

[0014] In one implementation of the present invention, if the verification fails in step S4, the metadata and data status before the repair are automatically rolled back, and the reason for the failure is recorded for troubleshooting.

[0015] On the other hand, the present invention also provides a table structure consistency check and repair system for GoldenDB database. The system includes: a metadata extraction module, used to extract standard metadata of distributed tables from the metadata dictionary table of the GoldenDB management node MDS as a consistency check baseline, and obtain the sharding mapping relationship between logical tables and data nodes (DNs) based on the metadata; and used to connect each shard master DN in parallel according to the sharding mapping relationship, collect the actual physical table structure metadata of the corresponding logical tables, and perform standardized preprocessing on the collected metadata; a data comparison module, used to perform hierarchical comparison of the standardized sharding metadata and baseline metadata from coarse to fine granularity, identify metadata differences and classify risk levels; a data repair module, used to match the corresponding repair strategy according to the risk level, generate and execute the adapted DDL repair script; and used to re-execute metadata collection and comparison after the repair is completed to verify whether the differences have been eliminated; and a log recording module, used to record the repair success log according to the verification result, or to perform rollback and record the reason for the exception.

[0016] The present invention provides a method and system for checking and repairing the table structure consistency of a GoldenDB database, which has the following beneficial effects: 1. This invention automates the entire process of metadata collection, difference detection, and hierarchical repair, replacing manual inspections, significantly improving operation and maintenance efficiency, and reducing labor costs.

[0017] 2. This invention does not modify the GoldenDB kernel code. It collects metadata and performs repair operations through an external protocol, which has no impact on business read and write operations and ensures business continuity.

[0018] 3. This invention employs a hierarchical comparison and semantic analysis algorithm, which can distinguish between substantive differences and format differences, avoid misjudgments, and cover all dimensions of metadata such as tables, fields, indexes, and constraints.

[0019] 4. This invention adopts a differentiated repair strategy based on the level of difference. Minor differences are automatically repaired to improve efficiency, medium-risk differences are repaired after review to reduce risk, and high-risk differences are manually intervened to ensure controllability. At the same time, it has backup and rollback mechanisms to avoid secondary failures caused by repair failures.

[0020] 5. This invention provides full-process traceability, establishing a complete log for recording, collecting, comparing, repairing, and alarming, supporting the generation of audit reports, and meeting the compliance requirements of the financial industry. Attached Figure Description

[0021] The accompanying drawings, which are included to provide a further understanding of the invention and form part of this invention, illustrate exemplary embodiments of the invention and are used to explain the invention, but do not constitute an undue limitation of the invention. In the drawings: Figure 1 A flowchart of a table structure consistency check and repair method for a GoldenDB database provided in this embodiment of the invention; Figure 2 This is a diagram illustrating the composition of a table structure consistency check and repair system for a GoldenDB database, provided as an embodiment of the present invention. Detailed Implementation

[0022] To make the objectives, technical solutions, and advantages of this invention clearer, the technical solutions of this invention will be clearly and completely described below in conjunction with specific embodiments and corresponding drawings. Obviously, the described embodiments are only a part of the embodiments of this invention, and not all of them. Based on the embodiments of this invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this invention.

[0023] This invention provides a method and system for checking and repairing table structure consistency in a GoldenDB database. It obtains all table structure information of distributed tenants as a baseline by querying the metadata information of the management node, and obtains the correspondence between logical tables and domain names (DNs) based on the metadata. It logs into the primary DN of each shard in parallel to obtain the table structure information of each shard. Then, it performs a deep comparison between the table structure information of each shard and the baseline, generates a difference report according to different risk levels based on the differences, and finally generates repair statements based on the differences. It identifies the risk level according to predetermined risk rules and selects a specific repair strategy. After the repair is completed, it re-obtains the table structure information of each shard and compares it with the baseline for verification to confirm that the differences have been repaired. The overall flowchart is as follows. Figure 1 As shown, the specific steps include: Step S1: Extract standard metadata of distributed tables from the metadata dictionary table of the GoldenDB management node MDS as a consistency check baseline, and obtain the sharding mapping relationship between logical tables and data nodes DN based on the metadata; Step S2: Connect each shard master DN in parallel according to the sharding mapping relationship, collect the actual physical table structure metadata of the corresponding logical table, and perform standardized preprocessing on the collected metadata; Step S3: Compare the standardized fragment metadata with the baseline metadata in layers from coarse to fine granularity, identify metadata differences and classify risk levels, match corresponding remediation strategies according to risk levels, and generate and execute the appropriate DDL remediation script. Step S4: After the repair is completed, re-execute the metadata collection and comparison to verify whether the differences have been eliminated. Record the repair success log according to the verification results, or perform a rollback and record the reason for the exception.

[0024] In this invention, the standard metadata mentioned in step S1 includes at least one of table name, table type, character set, storage engine, field information, index information, and partitioning rules. Connecting to the GoldenDB management node MDS, the system executes a metadata query statement to extract core metadata such as table name, table type, character set, storage engine, field information, index information, and partitioning rules of the distributed tables from the global data dictionary table. After format standardization and compliance verification, this metadata serves as the standard baseline for consistency checks. Then, based on the distributed routing table and sharding configuration table in the management node MDS metadata, the correspondence between logical tables and data node DNs is parsed to obtain the shard number, master DN node address, and access port of each logical table. This completes the baseline construction and accurate acquisition of the sharding mapping relationship, providing a directional basis for subsequent sharding metadata collection.

[0025] In this invention, the standardization preprocessing in step S2 includes: converting field names and index names to lowercase, standardizing character set encoding, and sorting according to a preset field definition order. The collected metadata of each DN node physical table is parsed and processed table by table: using a string standardization function, all field names and index names are converted to lowercase to avoid comparison discrepancies caused by database case sensitivity; a character set conversion component is called to normalize the metadata text and field encoding to UTF-8 standard encoding to eliminate heterogeneous encoding format conflicts; finally, using the field definitions of the baseline metadata as the standard, the field information and index information are reordered according to a preset order, redundant temporary attributes are filtered, and the metadata structure format is standardized to complete the standardization preprocessing of all metadata, ensuring the consistency of the baseline for subsequent layered comparisons.

[0026] In this invention, the hierarchical comparison in step S3 includes four levels: table existence comparison, field consistency comparison, primary and foreign key consistency comparison, and index consistency comparison. Using standard baseline metadata as a reference, the comparison is performed layer by layer with granularity from coarse to fine: First, a table existence comparison is performed to verify the existence of physical tables on each DN node and whether the basic table attributes are consistent with the baseline; second, a field consistency comparison is performed, verifying the name, data type, length, NOT NULL constraints, and field order of each field; then, a primary and foreign key consistency comparison is performed to verify the existence of primary and foreign keys, related fields, and constraint rules; finally, an index consistency comparison is completed to verify the index name, type, field combination, and sorting rules. The results of each comparison are marked in real time, progressively identifying differences and accurately locating table structure inconsistencies, providing data support for subsequent risk assessment and remediation.

[0027] In this invention, the table existence comparison includes table definition existence verification and table comment consistency verification; the field consistency comparison includes field definition verification, field order verification, and field comment verification, and the fields include core fields and non-core fields; the primary and foreign key consistency comparison includes primary and foreign key existence verification and primary and foreign key field order verification; the index consistency comparison includes index existence verification and index field order verification, and the indexes include ordinary indexes and unique indexes.

[0028] In this invention, in step S3, the risk level is divided into three levels: low risk, medium risk, and high risk. The low risk differences are differences in table annotation consistency and field annotation consistency; the medium risk differences are differences in non-core field definitions, differences in non-core field order, differences in the existence of ordinary indexes, and differences in the order of ordinary index fields; the high risk differences are differences in missing table definitions, differences in core field definitions and core field order, differences in the existence of primary and foreign keys and differences in the order of primary and foreign key fields, differences in the existence of unique indexes and differences in the order of unique index fields.

[0029] In this invention, the repair strategy corresponding to the risk level is as follows: for low-risk differences, the table structure is automatically backed up and then directly connected to the main DN to execute the repair script; for medium-risk differences, the repair script is executed after being pushed to the operation and maintenance platform and manually reviewed; for high-risk differences, automatic repair is prohibited and an alarm is triggered, and the repair is performed manually according to the repair plan.

[0030] In this invention, step S2 involves collecting the actual physical table structure metadata of each master DN by executing a preset metadata query statement, and employing a parallel connection method to improve metadata collection efficiency. Based on the parsed sharding mapping relationship, connection configurations for each master DN node are pre-generated, and preset GoldenDB metadata query standard statements (used to query table fields, indexes, constraints, and other structural information) are loaded. A thread pool / coroutine pool is used to establish parallel connection channels, synchronously initiating database connections to all master DN nodes, and executing preset query statements in parallel to collect actual physical table metadata; node timeout and reconnection mechanisms are set to avoid single-node abnormalities blocking the overall process. The collected raw metadata is written to a temporary cache queue in real time, and after completing the data collection of all sharded master DNs, it is uniformly summarized and output. Parallel processing eliminates the time bottleneck of serial collection, significantly improving the metadata collection efficiency in a distributed environment.

[0031] In this invention, if the verification fails in step S4, the metadata and data status before the repair are automatically rolled back, and the reason for the failure is recorded for troubleshooting.

[0032] In this invention, the method for checking and repairing the consistency of table structure metadata in GoldenDB database mainly includes the following four parts: metadata collection, consistency comparison and analysis, intelligent repair, and post-repair verification.

[0033] The metadata collection section specifically includes the following: 1. Obtain standard metadata of the distributed table from the metadata dictionary table of GoldenDB, including table name, table type, character set, storage engine, field information, index information, partitioning rules, etc., as a benchmark for consistency checks.

[0034] 2. Based on the mapping relationship between logical tables and data nodes obtained from the GoldenDB metadata, determine which specific shards each logical table's data is stored on.

[0035] 3. Log in to the main DN of each shard and obtain the actual physical table structure of the logical table on the current DN by executing a specific metadata query statement.

[0036] The consistency comparison analysis section specifically includes the following: 1. Standardize the collected table structure metadata information of each master DN: convert field names and index names to lowercase, convert character sets, and sort them according to the defined field order.

[0037] 2. The metadata of each master DN is compared with the baseline metadata of the management node in parallel using a coarse-to-fine granularity and hierarchical comparison strategy. The comparison is mainly carried out through the following levels: (1) Table existence comparison: Does the table definition exist? Are the comments consistent? (2) Field consistency comparison: Are the field definitions consistent? Are the order consistent? Are the comments consistent? (3) Primary and foreign key consistency comparison: Does the primary and foreign key of the table exist? Are the field order of the primary and foreign key consistent? (4) Index consistency comparison: Does the index of the table exist? Are the field order of the index consistent?

[0038] 3. Categorize and summarize the differences in the comparison, and generate a detailed difference report. The report will show the inconsistent table names, the DNs involved, and the content of the differences. Based on the content of the differences, the risk will be identified into three levels: low risk, medium risk, and high risk.

[0039] The intelligent repair section mainly includes the following: 1. Based on the type of discrepancy, select the specific repair strategy, generate an appropriate DDL script, and ensure the syntax correctness and compatibility of the statements.

[0040] 2. Based on the risk level assessment results, the system adopts different remediation strategies: (1) For low-risk differences, the system backs up the relevant table structure by directly connecting to the main DN and then automatically executes the relevant repair script.

[0041] (2) For differences in medium-risk levels, the system will push the repair script and the DN with the difference to the operation and maintenance management platform. After the operation and maintenance personnel review and confirm, it will be distributed to the target segment for repair.

[0042] (3) For differences in high-risk levels, the system prohibits automatic repair and immediately triggers an alarm, outputs a detailed repair plan, and the operation and maintenance personnel manually execute the plan to ensure that the repair process is controllable.

[0043] The post-repair verification process includes the following: After the repair operation is completed, the system automatically triggers a metadata collection and comparison to verify whether the differences have been completely eliminated. If the verification passes, a successful repair log is recorded; if the verification fails, a rollback operation is automatically performed to restore the metadata and data state to the state before the repair, and the reason for the failure is recorded for maintenance personnel to troubleshoot.

[0044] The above describes a method for checking and repairing table structure consistency in a GoldenDB database according to an embodiment of the present invention. Based on the same inventive concept, this invention also provides a system for checking and repairing table structure consistency in a GoldenDB database. Figure 2 A diagram illustrating the composition of a table structure consistency check and repair system for a GoldenDB database, provided as an embodiment of the present invention, is shown below. Figure 2 As shown, the system mainly includes: a metadata extraction module 201, used to extract standard metadata of distributed tables from the metadata dictionary table of the GoldenDB management node MDS as a consistency check baseline, and obtain the sharding mapping relationship between logical tables and data nodes (DNs) based on the metadata; and used to connect each shard master DN in parallel according to the sharding mapping relationship, collect the actual physical table structure metadata of the corresponding logical tables, and perform standardized preprocessing on the collected metadata; a data comparison module 202, used to perform hierarchical comparison of the standardized sharding metadata and baseline metadata from coarse to fine granularity, identify metadata differences and classify risk levels; a data repair module 203, used to match the corresponding repair strategy according to the risk level, generate and execute the adapted DDL repair script; and used to re-execute metadata collection and comparison after the repair is completed to verify whether the differences have been eliminated; and a log recording module 204, used to record the repair success log according to the verification result, or to perform rollback and record the reason for the exception.

[0045] The following section uses a GoldenDB database cluster within a bank's core business system as an example to illustrate the implementation process and effectiveness of this solution. This business system deploys two GoldenDB database clusters, each using a two-site, three-center architecture. Each cluster includes 5 management nodes (2 local, 2 within the same city, and 1 remote), 40 compute nodes (CNs) (16 local, 16 within the same city, and 8 remote), and 15 shards. Each shard contains 5 replicas, i.e., 5 data nodes (DNs) (2 local, 2 within the same city, and 1 remote). Based on business requirements, daily DDL operations such as adding fields, optimizing indexes, and modifying comments are performed. This presents a risk of table structure inconsistencies due to network fluctuations and DDL statement execution failures on some DN nodes. By deploying a GoldenDB database metadata consistency check and repair system, automated management of metadata consistency is achieved.

[0046] Scenario 1: Automatic repair of low-risk metadata differences.

[0047] (1) On August 18, 2025, the operations and maintenance personnel added the comment "user email" to the email field of the user information table hx_yhxx. Due to network jitter, the execution of the DN nodes of shard 5 and shard 8 failed. In the scheduled task, the consistency check system identified that the email field comment of these two shards was inconsistent with the baseline ("user email" recorded in MDS) through field consistency comparison, and was judged as a low-risk difference. The system automatically generated a script, directly connected to the corresponding master DN for execution, and verified that it passed the repair. No manual intervention was required throughout the process.

[0048] (2) On August 19, 2025, during a metadata consistency check of the product catalog table t_product_catalog, the system discovered, through the table existence comparison layer, that the physical table annotation on the primary DN node of shard 4 was inconsistent with the baseline metadata in the MDS. In the baseline, the table annotation was "Product Category Catalog Table, used to manage product categories," while on shard 4, the annotation was "Product Category Table." The system determined this difference to be a low-risk "Table Annotation Inconsistency Difference." Subsequently, the intelligent repair module automatically generated a repair script and successfully executed it via a direct JDBC connection to the primary DN of shard 4. After the repair was completed, the system immediately triggered a verification process to confirm that the table annotation was consistent with the baseline and recorded a successful repair log.

[0049] Scenario 2: Semi-automatic repair process for data discrepancies in medium-risk data.

[0050] (1) In the early morning of July 20, 2025, when the system was checking the core transaction log table hx_lsxx, it found through index consistency comparison that a regular index named idx_trade_date was missing on shard 7 and shard 9. This difference was judged to be of medium risk. The system generated a corresponding script. Given the large amount of data in the table, creating the index may cause table locking. Therefore, the system did not execute it automatically, but pushed the repair script and difference details to the unified operation and maintenance platform and initiated an approval process. After being reviewed and approved by the DBA during off-peak business hours, the script was issued and executed, and the difference was finally repaired.

[0051] (2) During a routine check on November 12, 2025, the system discovered several medium-risk differences in the field consistency comparison and index consistency comparison layers when verifying the customer extended information table t_customer_ext. On shard 6, the data type of the non-core field preferred_contact was VARCHAR(50) on the baseline, but it was actually VARCHAR(30), which is a "non-core field definition difference". At the same time, the definition order of a set of non-core fields (company, title, location) in the baseline was (location, company, title), which was inconsistent with the actual order, which is a "non-core field order difference". In addition, in the index consistency comparison, the system found that the ordinary index idx_customer_info used to speed up queries contained the fields (location, create_year) on the baseline, but its field order on shard 6 was (create_year, location), which constitutes an "ordinary index field order difference". Given that these differences involve non-core fields and ordinary indexes, and that modifications may cause temporary table locking, the system determined them to be medium-risk. The repair module generated a compound script containing multiple ALTER TABLE statements to sequentially modify field lengths, adjust field order, and rebuild indexes. This script was pushed to the unified operations platform, and after the DBA reviewed and confirmed the execution plan during the early morning off-peak business hours, it was manually triggered and successfully eliminated all discrepancies.

[0052] Scenario 3: Manual repair process for high-risk metadata discrepancies.

[0053] (1) On October 15, 2025, during the index consistency comparison, the system discovered that the field order of the unique index uk_account_card in the core private account table hx_dszhxx on shards 2, 7, and 9 did not match the baseline. The baseline was defined as (account_id, card_no), but on these shards it was actually (card_no, account_id). This difference in the field order of the unique index was judged as a high-risk issue because it changed the query optimization characteristics of the index and could cause serious performance problems. The system immediately disabled automatic repair and issued a high-risk alert to the operations and maintenance personnel via SMS and the work order system, along with a detailed difference report and a recommended repair script (first delete the old index, then create it in the correct order). After receiving the alert, the operations and maintenance personnel manually performed the repair operation within the contingency plan time window.

[0054] (2) On October 16, 2025, the system triggered a high-risk alert while checking the core transaction log table t_txn_log. At the table existence comparison layer, the system found that the physical table definition of the table did not exist on shard 10, i.e., "table definition missing difference". When further collecting metadata from other shards for field consistency comparison, it was found that the core field txn_amount (transaction amount) of the table on shard 7 was defined as DECIMAL(18,2) in the baseline, while it was actually DECIMAL(16,2), which is "core field definition difference". At the same time, on shard 7, the order of another set of core fields (account_id, txn_seq) was reversed from the baseline (txn_seq, account_id), constituting "core field order difference". These differences will directly lead to data write failure, application logic errors or abnormal query results, which is extremely risky. The system immediately disabled any automatic repair operations and issued a high-risk alert via SMS and the monitoring dashboard. Simultaneously, it generated a detailed diagnostic report indicating that shard 10 required table rebuilding, and shard 7 required modification of core field types and order. Upon receiving the alert, the operations personnel, following the repair plan in the report, first executed a complete CREATE TABLE statement on shard 10 to rebuild the table structure and synchronize data. Then, in the business maintenance window, they performed a complex ALTER TABLE operation on shard 7, involving changes to the data types and order of core fields, ultimately manually resolving all issues.

[0055] (3) On October 18, 2025, the system detected a high-risk difference in the primary and foreign key consistency comparison layer when comparing order-related tables. For the main table t_order, its primary key constraint PRIMARY KEY (order_id) was completely missing on shard 3, i.e., "primary and foreign key existence difference". For the sub-table t_order_detail, although its foreign key fk_order_id (related to t_order.order_id) existed on shard 5, the order of the foreign key fields did not match the baseline. In the baseline, the foreign key was defined as FOREIGNKEY (order_id, item_no), while it was actually FOREIGN KEY (item_no, order_id), which is "primary and foreign key field order difference". The missing primary key will destroy the uniqueness of the data, while the incorrect order of the foreign key fields may cause referential integrity checks to fail or the performance of join queries to decrease. The system judged it as high-risk, triggered an alarm and prevented automatic repair. After the operations and maintenance personnel intervened, they first manually added a primary key constraint to the t_order table on shard 3 (it was necessary to check and clean up any duplicate order_id data first). Then, they deleted the incorrect foreign key constraint on shard 5 and recreated it in the correct order, thus manually fixing the two critical structural inconsistencies.

[0056] Since the deployment of the GoldenDB metadata consistency check and repair system, the traditional manual inspection method has been completely eliminated. The metadata inconsistency check cycle has been adjusted from weekly manual execution to minute-level automatic checks, improving metadata repair efficiency by more than 90%. Since its deployment, it has handled 10 low-risk cases, 4 medium-risk cases, and 2 high-risk cases, without any business interruptions or performance slowdowns caused by metadata inconsistencies. At the same time, the full-process log recording meets the security and compliance requirements of the financial industry, realizing automated, intelligent, and secure management of GoldenDB database metadata.

[0057] The various embodiments in this invention are described in a progressive manner. Similar or identical parts between embodiments can be referred to mutually. Each embodiment focuses on describing the differences from other embodiments. In particular, the device embodiments are basically similar to the method embodiments, so the description is relatively simple; relevant parts can be referred to the descriptions of the method embodiments.

[0058] It should also be noted that 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 a process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes said element.

[0059] The above description is merely an embodiment of the present invention and is not intended to limit the invention. Various modifications and variations can be made to the present invention by those skilled in the art. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principle of the present invention should be included within the scope of the claims of the present invention.

Claims

1. A method for checking and repairing table structure consistency in a GoldenDB database, characterized in that, The method includes the following steps: Step S1: Extract standard metadata of distributed tables from the metadata dictionary table of the GoldenDB management node MDS as a consistency check baseline, and obtain the sharding mapping relationship between logical tables and data nodes DN based on the metadata; Step S2: Connect each shard master DN in parallel according to the sharding mapping relationship, collect the actual physical table structure metadata of the corresponding logical table, and perform standardized preprocessing on the collected metadata; Step S3: Compare the standardized fragment metadata with the baseline metadata in layers from coarse to fine granularity, identify metadata differences and classify risk levels, match corresponding remediation strategies according to risk levels, and generate and execute the appropriate DDL remediation script. Step S4: After the repair is completed, re-execute the metadata collection and comparison to verify whether the differences have been eliminated. Record the repair success log according to the verification results, or perform a rollback and record the reason for the exception.

2. The method for checking and repairing table structure consistency in a GoldenDB database according to claim 1, characterized in that, The standard metadata mentioned in step S1 includes at least one of the following: table name, table type, character set, storage engine, field information, index information, and partitioning rules.

3. The method for checking and repairing table structure consistency in a GoldenDB database according to claim 1, characterized in that, The standardization preprocessing described in step S2 includes: converting field names and index names to lowercase, using a unified character set encoding, and sorting them according to the preset field definition order.

4. The method for checking and repairing table structure consistency in a GoldenDB database according to claim 1, characterized in that, The hierarchical comparison in step S3 includes four levels: table existence comparison, field consistency comparison, primary and foreign key consistency comparison, and index consistency comparison.

5. The method for checking and repairing table structure consistency in a GoldenDB database according to claim 4, characterized in that, The table existence comparison includes table definition existence verification and table comment consistency verification; the field consistency comparison includes field definition verification, field order verification, and field comment verification, and the fields include core fields and non-core fields; the primary and foreign key consistency comparison includes primary and foreign key existence verification and primary and foreign key field order verification; the index consistency comparison includes index existence verification and index field order verification, and the indexes include ordinary indexes and unique indexes.

6. The method for checking and repairing table structure consistency in a GoldenDB database according to claim 1, characterized in that, In step S3, the risk level is divided into three levels: low risk, medium risk, and high risk. Low-risk differences include differences in table comment consistency and field comment consistency; medium-risk differences include differences in non-core field definitions, differences in the order of non-core fields, differences in the existence of ordinary indexes, and differences in the order of ordinary index fields; high-risk differences include differences in missing table definitions, differences in core field definitions and the order of core fields, differences in the existence of primary and foreign keys and the order of primary and foreign key fields, differences in the existence of unique indexes and the order of unique index fields.

7. The method for checking and repairing table structure consistency in a GoldenDB database according to claim 6, characterized in that, The remediation strategy corresponding to the risk level is as follows: After automatically backing up the low-risk differential table structure, directly connect to the primary DN to execute the repair script; The medium-risk differences are pushed to the operation and maintenance platform, where they are manually reviewed before the repair script is executed. High-risk differences should not be automatically repaired and should trigger alarms; instead, manual repair should be performed according to the repair plan.

8. The method for checking and repairing table structure consistency in a GoldenDB database according to claim 1, characterized in that, In step S2, the actual physical table structure metadata of each master DN is collected by executing a preset metadata query statement, and the efficiency of metadata collection is improved by using a parallel connection method.

9. The method for checking and repairing table structure consistency in a GoldenDB database according to claim 1, characterized in that, If the verification fails in step S4, the metadata and data status before the repair will be automatically rolled back, and the reason for the failure will be recorded for troubleshooting.

10. A table structure consistency check and repair system for GoldenDB database, characterized in that, The system includes: The metadata extraction module is used to extract standard metadata of distributed tables from the metadata dictionary table of the GoldenDB management node MDS as a consistency check baseline, and obtain the sharding mapping relationship between logical tables and data nodes (DNs) based on the metadata; and to connect each shard master DN in parallel according to the sharding mapping relationship, collect the actual physical table structure metadata of the corresponding logical tables, and perform standardized preprocessing on the collected metadata. The data comparison module is used to perform hierarchical comparison of standardized fragmented metadata and baseline metadata from coarse to fine granularity, identify metadata differences and classify risk levels. The data repair module is used to match the corresponding repair strategy according to the risk level, generate and execute the appropriate DDL repair script; and to re-execute metadata collection and comparison after the repair is completed to verify whether the differences have been eliminated. The logging module is used to record successful repair logs based on the verification results, or to perform a rollback and record the reason for the exception.

Images