Flyway-based database migration method

Abstract

A database migration method based on Flyway comprises the following steps that an unexecuted migration script is obtained, and the migration script is a migration script created based on Flyway; acquiring script characteristics and an execution plan of the migration script; obtaining a risk value of migration script execution according to the script characteristics of the migration script and the execution plan; obtaining a risk level of the migration script according to the risk value executed by the migration script and a preset risk grading rule; dynamically executing the migration script according to the risk level of the migration script; by adopting the technical characteristics, automatic risk assessment is realized, the workload of manual analysis and judgment is greatly reduced, and the operation and maintenance efficiency is remarkably improved; and a risk level dynamic adjustment execution strategy of the migration script is obtained by means of automatic risk assessment, so that database migration work better meets actual business requirements, the migration process is remarkably optimized, and the overall migration efficiency, safety and reliability are improved.

Description

Technical Field

[0001] This application relates to the technical field of database management, and particularly to a database migration method based on Flyway. Background Art

[0002] In the process of database management, as software systems are continuously iterated and upgraded, the database architecture also needs to be changed accordingly. The traditional database change management method lacks standardization, resulting in errors easily occurring during the change process and making it difficult to ensure reliability. As an open-source database migration tool, Flyway provides strong support for developers and operators to manage database architecture changes. It performs version control on the changes to the database architecture in the form of scripts, making database change management develop in the direction of standardization, reliability, and traceability. At the same time, Flyway has extensive compatibility and supports various common relational databases such as MySQL, Oracle, PostgreSQL, and SQL Server, which greatly improves the flexibility of developers in selecting databases for different projects. The core mechanism for Flyway to implement database architecture version tracking is to create a special table named flyway_schema_history in the database. When Flyway starts running, it will automatically compare the executed migration scripts recorded in this table with the migration scripts defined in the file system or class path. If it detects that new migration scripts have not been executed in the database, Flyway will execute these migration scripts in sequence according to the version numbers of the migration scripts, thereby updating the database architecture to the latest version.

[0003] Although Flyway has significant advantages in database migration, in the complex scenarios of database migration management, there are problems such as high manual dependence and lack of flexibility in execution strategies when dealing with migration risks. There is an urgent need for a more perfect database migration solution to meet the increasingly complex database management requirements. Summary of the Invention

[0004] In view of the above problems, this application provides a database migration method based on Flyway, which solves the problems of high manual dependence and insufficient flexibility in execution strategies during the process of dealing with migration risks.

[0005] To achieve the above object, the inventor provides a database migration method based on Flyway, which includes the following steps:

[0006] Obtain unexecuted migration scripts, where the migration scripts are created based on Flyway;

[0007] Obtain the script features and execution plans of the migration scripts;

[0008] Obtain the risk value of the migration script execution according to the script characteristics and execution plan of the migration script;

[0009] Obtain the risk level of the migration script according to the risk value of the migration script execution and the preset risk classification rules;

[0010] Dynamically execute the migration script according to the risk level of the migration script.

[0011] Furthermore, the risk value of the migration script execution is obtained by matching historical execution data; or

[0012] Obtained by inputting a cost calculation model pre-trained based on historical execution data; or

[0013] Calculated based on a preset execution cost algorithm.

[0014] Furthermore, the risk value is obtained by comprehensively calculating the lock table risk assessment value, execution time prediction value, and business impact assessment value.

[0015] Furthermore, the risk levels of the risk classification rules at least include high level and low level;

[0016] The steps of dynamically executing the migration script according to the risk level of the migration script include:

[0017] Judge whether the risk level of the migration script is a low risk level;

[0018] If so, automatically execute the migration script;

[0019] Otherwise, split the migration script into several new migration scripts and execute them in sequence.

[0020] Furthermore, the risk levels of the risk classification rules at least include high risk level and low risk level;

[0021] The steps of dynamically executing the migration script according to the risk level of the migration script include:

[0022] Judge whether the risk level of the migration script is lower than the high risk level. If so, execute the migration script, otherwise transfer to manual operation.

[0023] Furthermore, in the process of dynamically executing the migration script according to the risk level of the migration script, it also includes judging whether the migration script meets the suspension condition; if it meets, suspend the execution, otherwise continue to execute;

[0024] So the suspension conditions include any one or more of the following:

[0025] The execution duration of the migration script exceeds the preset execution duration;

[0026] The migration script execution encounters an error;

[0027] The risk level of the migration script is a high - risk level.

[0028] Further, when the pause condition is eliminated, the execution continues at the position where it was paused.

[0029] Further, during the process of dynamically executing the migration script according to the risk level of the migration script, if the pause condition is met, while pausing the execution, an alarm mechanism will also be synchronously started.

[0030] Further, after the step of dynamically executing the migration script according to the risk level of the migration script, it also includes using the execution data of the migration script to update the historical execution data, the cost calculation model, or the preset execution cost algorithm.

[0031] Further, when dynamically executing the migration script according to the risk level of the migration script, it also includes judging whether the rollback condition is met. If it is met, the migration script rollback is executed;

[0032] The way to execute the migration script rollback is to roll back based on the reverse script of the executed migration script; or

[0033] Roll back based on the database transaction mechanism; or

[0034] Roll back based on database backup and recovery; or

[0035] Perform reverse processing based on the log to generate a rollback script for rollback.

[0036] Different from the prior art, the above - mentioned technical solution realizes automated risk assessment by extracting the script features and execution plans of the migration script, greatly reducing the workload of manual analysis and judgment, and significantly improving the operation and maintenance efficiency; and based on the risk level obtained from the automated risk assessment, dynamically adjusts the execution strategy, making the database migration work more in line with the actual business needs in the database migration scenario, significantly optimizing the migration process, and enhancing the efficiency, security, and reliability of the overall migration.

[0037] The relevant records in the above - mentioned invention content are only an overview of the technical solution of this application. In order to enable those of ordinary skill in the art to more clearly understand the technical solution of this application, and then can be implemented according to the content recorded in the description and the drawings, and in order to make the above - mentioned purpose, other purposes, features, and advantages of this application more easily understood, the following is described in conjunction with the specific implementation manners and drawings of this application. Brief Description of the Drawings

[0038] The accompanying drawings are only used to illustrate the principles, implementation methods, applications, features, and effects of the specific embodiments of the present invention and other related contents, and should not be considered as a limitation to this application.

[0039] In the accompanying drawings of the specification:

[0040] Figure 1 It is a schematic flowchart of the database migration method based on Flyway described in the specific embodiment. Specific Embodiment

[0041] To describe in detail the possible application scenarios, technical principles, specific implementable solutions, achievable purposes and effects of this application, etc., the following will be described in detail in combination with the listed specific embodiments and with reference to the accompanying drawings. The embodiments described herein are only used to more clearly illustrate the technical solutions of this application, so they are only used as examples and cannot be used to limit the protection scope of this application.

[0042] Referring to "embodiment" in this article means that the specific features, structures or characteristics described in combination with the embodiment can be included in at least one embodiment of this application. The term "embodiment" appearing in various positions in the specification does not necessarily refer to the same embodiment, nor does it particularly limit its independence or relevance to other embodiments. In principle, in this application, as long as there is no technical contradiction or conflict, the technical features mentioned in each embodiment can be combined in any way to form corresponding implementable technical solutions.

[0043] Unless otherwise defined, the meanings of the technical terms used in this article are the same as those generally understood by those skilled in the technical field to which this application belongs; the use of the relevant terms in this article is only to describe specific embodiments and is not intended to limit this application.

[0044] In the description of this application, the term "and / or" is an expression used to describe the logical relationship between objects, indicating that there can be three relationships. For example, A and / or B means: there is A, there is B, and there is both A and B at the same time. In addition, the character " / " in this article generally represents an "or" logical relationship between the associated objects before and after.

[0045] In this application, terms such as "first" and "second" are only used to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply any actual quantity, primary-secondary or order relationship between these entities or operations.

[0046] Without further limitations, in this application, the use of "including", "comprising", "having" or other similar open-ended expressions in a statement is intended to cover non-exclusive inclusion. These expressions do not exclude the possibility that there may be additional elements in the process, method or product including the said elements. Thus, in a process, method or product including a series of elements, it may include not only those defined elements, but also other elements not explicitly listed, or elements inherent to such process, method or product.

[0047] Similar to the understanding in the Examination Guidelines, in this application, expressions such as "greater than", "less than", "exceeding" are understood as not including the specified number; expressions such as "above", "below", "within" are understood as including the specified number. In addition, in the description of the embodiments of this application, the meaning of "a plurality of" is two or more (including two). Similar expressions related to "many", such as "multiple groups", "multiple times", etc., are understood in the same way, unless otherwise specifically defined.

[0048] In the description of the embodiments of this application, the spatially related expressions used, such as "center", "longitudinal", "transverse", "length", "width", "thickness", "upper", "lower", "front", "rear", "left", "right", "vertical", "horizontal", "perpendicular", "top", "bottom", "inner", "outer", "clockwise", "counterclockwise", "axial", "radial", "circumferential", etc., indicate the orientation or positional relationship based on the specific embodiment or the orientation or positional relationship shown in the drawings. It is only for the convenience of describing the specific embodiments of this application or for the reader's understanding, rather than indicating or implying that the device or component referred to must have a specific position, a specific orientation, or be constructed or operated in a specific orientation. Therefore, it should not be construed as a limitation to the embodiments of this application.

[0049] The processor described in the embodiments of the present application can be implemented by hardware, firmware, software, or a combination thereof. It can use circuits, one or more application specific integrated circuits (ASICs), digital signal processors (DSPs), digital signal processing devices (DSPDs), programmable logic devices (PLDs), field programmable gate arrays (FPGAs), central processing units (CPUs), controllers, microcontrollers, microprocessors, or at least one of them. It also includes other physical, biological, or chemical structures that can achieve functions similar to or equivalent to those of the above-listed processors, such as biological neurons, quantum computing units, DNA computing units, etc., so that the processor can execute some steps, all steps, or any combination of the steps mentioned in the computer programs or methods involved in the various embodiments of the present application.

[0050] The computer programs involved in the embodiments can be stored in a computer device-readable storage medium, which includes but is not limited to magnetic disks, magnetic tapes, magnetic cards, floppy disks, flash memories, optical discs, optical cards, read-only memories (ROMs), random access memories (RAMs), erasable programmable ROMs (EPROMs), and electrically erasable programmable ROMs (EEPROMs), etc. It also includes other biological, physical, or chemical structures that can achieve the same or equivalent functions as the above-listed storage media, such as units with information storage capabilities like DNA, RNA, proteins, etc. In a specific embodiment, the storage medium involved can be one of the above medium types or a combination of the above medium types. In different embodiments, the computer programs involved in the embodiments can be stored centrally in a single medium or distributedly in multiple media. The memory containing the computer device-readable storage medium can be a non-volatile memory or a random access memory. These computer device-readable storage media can be built into the device or connected to the device involved in the embodiment as an external device or a part of an external device. In some embodiments, the memory with the computer device-readable storage medium is deployed locally; in other embodiments, a scheme of deploying the memory away from the processor can also be adopted, such as a network-attached memory accessed via an RF circuit or an external port and a communication network, where the communication network can be the Internet, one or more internal networks, local area networks (LANs), wide area wireless networks (WLANs), storage area networks (SANs), etc., or a suitable combination thereof, as long as the computer device can access the memory. In addition, the computer programs involved in the embodiments can be stored in plaintext / ciphertext form or designed as training data and integrated and reorganized implicitly and saved in the parameter states of a deep neural network or other machine learning models through model training.

[0051] Flyway is a database migration tool that makes database change management more standardized, reliable, and traceable by version controlling changes to the database schema in the form of scripts. It mainly realizes the recording and management of database changes by creating a table named flyway_schema_history in the database. When Flyway starts running, it will automatically compare the executed migration scripts recorded in this table with the migration scripts defined under the file, system, or classpath. If it detects unexecuted migration scripts in the file system or classpath and these unexecuted migration scripts have not been executed in the database, Flyway will execute these unexecuted migration scripts in sequence and then update the database schema to the latest version.

[0052] In the actual database migration scenario, the Flyway tool has significant shortcomings in coping with migration risks, mainly reflected in two aspects: excessive manual dependence and lack of flexibility in execution strategies. When dealing with specific database change operations, Flyway cannot estimate the potential risks during the execution of migration scripts. Take the operation of adding a field with a default value to a large table as an example. Since the amount of data in the large table is extremely large, when adding a field and assigning a default value, the database needs to update each piece of data. This process not only consumes a large amount of system resources but also is very likely to cause a long table lock. However, the Flyway tool lacks the function of estimating the table lock time for such operations, cannot know in advance the time required for the operation, and is also difficult to judge its impact on the business system, resulting in the inability to formulate a reasonable operation and maintenance plan. Moreover, when using Flyway, the naming of migration scripts needs to follow specific specifications. The general format is V<version number>_<description>.sql, where the version number is used to identify the execution order of the migration scripts. When executing the migration scripts, they are executed in ascending order according to the order carried in the file name of the migration scripts. This fixed execution mode makes it impossible to flexibly adjust the execution order according to the execution risks of the migration scripts. For example, in a common database migration scenario, there are migration script A and migration script B. Migration script A is used to add a field to a certain core business table. This operation involves modifying the table structure, not only has a long execution time but also may cause the table lock time to be extended due to various reasons during the execution process, thus affecting other business operations and having a high execution risk. While migration script B is to insert data into a general data record table, which has relatively little impact on real-time business and a low risk level. From the perspective of risk control, to minimize the impact on the business system, script B with less impact on the business should be executed first, and then script A. However, since Flyway only executes the scripts in ascending order of file names, if the file name of script A is before that of script B, the system will first execute the high-risk migration script A. This is very likely to cause the business system to respond slowly or even experience execution interruptions and anomalies during the execution of script A (especially when performing such operations during the business peak period), thus affecting the normal operation of the entire business system.

[0053] Therefore, this application relies on the content contained in the migration script itself to obtain the script characteristics and execution plan of the migration script; then, the obtained script characteristics and execution plan are used to calculate the risk value of the current migration script through a specific algorithm; then, referring to the pre-set mapping rule between the risk value and the risk level, the risk level of the migration script is determined to provide support for the formulation of subsequent execution strategies. In the execution stage, a differentiated execution strategy is formulated according to the risk level.

[0054] Therefore, the present application proposes a database migration method based on Flyway, which has good scalability and can efficiently handle various migration scenarios. By extracting the script features and execution plans of the migration scripts, automated risk assessment is achieved, significantly reducing the workload of manual analysis and judgment, and remarkably improving the operation and maintenance efficiency; and based on the risk level obtained from the automated risk assessment, the execution strategy is dynamically adjusted, making the database migration work more in line with the actual business needs in the database migration scenario, significantly optimizing the migration process, and enhancing the efficiency, security, and reliability of the overall migration.

[0055] See Figure 1 As shown, the following provides an implementation manner of a database migration method based on Flyway, which includes the following steps:

[0056] S10. Obtain unexecuted migration scripts, where the migration scripts are created based on Flyway;

[0057] S20. Obtain the script features and execution plans of the migration scripts;

[0058] S30. Obtain the risk value of the execution of the migration script according to the script features and execution plans of the migration script;

[0059] S40. Obtain the risk level of the migration script according to the risk value of the execution of the migration script and the preset risk classification rules;

[0060] S50. Dynamically execute the migration script according to the risk level of the migration script.

[0061] The above migration script refers to a script file for database migration. The migration script usually contains a series of SQL statements or other instructions specific to the database management system, which are used to convert the database from one version or state to another version or state. Such as performing database structure changes (such as creating new tables, modifying table structures (such as adding or deleting columns, modifying the data types of columns, etc.), creating indexes, adding foreign key constraints, etc.), data migration, data initialization, etc. The above unexecuted migration scripts can be identified by reading the records in the flyway_schema_history table and traversing the migration scripts defined under the file system or class path at the same time, and by comparing. Specifically, when traversing the migration scripts under the file system or class path, it is necessary to check the version number and file name of each script one by one to see if they are recorded in the flyway_schema_history table. If the relevant information of a migration script is not in this table, this script can be determined as an unexecuted migration script.

[0062] The script features of the above migration script can be obtained by parsing the migration script. Generally, key information can be extracted from different levels of the migration script by using techniques such as lexical analysis, syntactic analysis, and semantic analysis, so as to obtain the script features. Specifically, through lexical analysis, the migration script can be split into individual lexical units (tokens) based on regular expressions according to predefined rules. Through this process, the SQL statements in the script are initially disassembled, laying a foundation for subsequent analysis. Then, based on the results of lexical analysis, with the help of syntactic analysis based on context-free grammar, a syntax tree is constructed using a top-down or bottom-up parsing algorithm to reflect the syntax structure of the SQL statements in the migration script. Then, semantic analysis is carried out in combination with the syntax tree and the metadata information of the database to extract the script features. The script features may include table size, number of indexes, table names, and / or lock type encodings, etc. The above execution plan can be obtained with the help of the query optimizer of the database system. The query optimizer does not actually execute the SQL statements in the migration script, but asks the database to return the execution plan of the SQL statements. The execution plan is presented in the form of a result set, which details the specific steps and strategies when the database executes the SQL statements, including the indexes used, the table join order, the data reading method, the estimated number of rows to be scanned, the estimated execution time, etc. Taking the MySQL database as an example, if you want to add a new column shipping_fee to a table named order, the code example for obtaining the execution plan is as follows:

[0063] EXPLAIN ALTER TABLE `order` ADD COLUMN shipping_fee decimal(20,2) default 0.00;

[0064] After executing the above EXPLAIN statement, the database will return a result set showing the execution plan of the ALTER TABLE operation. Note that the result formats returned by different database systems may vary.

[0065] The above risk value for the execution of the migration script obtained based on the script features and execution plan of the migration script is to evaluate the potential risk level by analyzing the characteristics of the script itself and its expected execution process, aiming to discover in advance problems that may affect the stability, performance, and data integrity of the database migration process, etc.

[0066] In some embodiments, risk assessment can be performed based on historical execution data. The historical execution data is the relevant data when similar or identical migration scripts were executed in the past, covering various risk factors during the execution process, providing strong support for calculating the risk value of the execution of the migration script. The risk factors include risks of the migration script itself (such as syntax errors, logical errors, version compatibility, etc.), database system risks (such as table locking risks, performance bottlenecks, data loss or corruption, transaction processing problems, etc.), business impact risks (business interruption, inconsistent business data), etc.

[0067] In some embodiments, it is obtained by comparing with historical execution data. That is, the obtained script features and execution plan are compared with the historical execution data, and the similarity between the historical execution data and the currently unexecuted migration script is calculated comprehensively through the script features and execution plan. According to the execution situation of the historical script with the highest similarity, the risk value of the migration script to be evaluated is inferred. For example, according to the script features and execution plan, the script with the highest similarity is found in the historical execution data. This script was executed successfully but took a long time, resulting in a slight delay in the response of the business system, and the risk value is 8. Therefore, it is speculated that the risk value of the unexecuted migration script to be evaluated is also about 8. This means that when the unexecuted migration script is executed, it may take a long time and have a certain impact on the response performance of the business system. Operations and maintenance personnel can take countermeasures such as resource allocation and business notification in advance.

[0068] In some embodiments, a risk assessment rule library is established based on historical execution data. The obtained script features and execution plan are matched with the risk assessment rule library, and each risk factor is scored according to the matching result. Finally, the risk value of the execution of the migration script is obtained by comprehensively considering the scores of each risk factor. For example, in the established risk assessment rule library, the risk of large table structure change is 8, the risk of index optimization is 6, and the risk of data operation is 3; the script features of the unexecuted migration script include ALTER TABLE and INSERT INTO, and the execution plan includes an ALTER TABLE operation on the customer information table that will cause table locking and is a full-table operation; the INSERT INTO operation on the transaction record table is executed efficiently because there is a suitable index on the table. Then, for the ALTER TABLE operation, the "risk of large table structure change" rule is matched and 8 points are obtained. For the INSERT INTO operation, the "risk of data operation" rule is matched and 3 points are obtained. By comprehensively considering the scores of each risk factor, the risk value of this migration script is 8 + 3 = 11 points.

[0069] In some embodiments, the input is obtained by pre-training a cost calculation model based on historical execution data, that is, the cost calculation model is pre-trained based on historical execution data, and the script features and execution plan are input into the cost calculation model to obtain a risk value. Specifically, a deep learning model can be used as the basic framework of the cost calculation model, and common models include decision trees, random forests, neural networks, etc. The collected historical execution data is divided into a training set and a test set, and the training set is used to train the model. During the training process, the script features and execution plan are used as the input of the model, and the risk value during the script execution is used as the output. By continuously adjusting the parameters of the model, the model can accurately learn the mapping relationship between the script features, execution plan, and risk value. After training, the test set is used to evaluate the model to ensure that the model has good generalization ability and accuracy. When it is necessary to evaluate the risk value of an unexecuted migration script, its script features and execution plan are used as input data and passed into the pre-trained cost calculation model. The cost calculation model uses the mapping relationship learned during the training process to calculate the risk value of the execution of this migration script.

[0070] In some embodiments, it is calculated based on a preset execution cost algorithm. For example, the risk value is quantitatively calculated from three risk factors: lock table risk, execution time, and business impact. Specifically, the quantitative risk values of lock table risk assessment, execution time prediction, and business impact assessment are multiplied by their corresponding weights, and then all the results are added together to obtain the risk value of the execution of the migration script. The weights here reflect the importance of each factor in the overall risk assessment, and their assignment can be based on experience, historical execution data statistics, or expert judgment.

[0071] In terms of lock table risk assessment, lock table operations will affect the concurrency performance of the database and increase the risk of business blocking. The basic risk value can be calculated based on the number of rows of table metadata in the script features, and then combined with the lock granularity analyzed from the execution plan. Different weights are assigned to the basic risk value according to different lock types, so as to form a benchmark lock table risk assessment algorithm to calculate the lock table risk value. For example, it is set that the risk value increases by 0.5 for every million rows. The read lock (MDL_READ) has a relatively low risk, and the basic risk value is multiplied by 0.3; the write lock (MDL_WRITE) has a higher risk, multiplied by 1.2; the table lock (TABLE_LOCK) has an even higher risk, multiplied by 2.0.

[0072] In terms of execution time prediction, the execution time can be analyzed based on the execution plan, or the execution time can be predicted according to the script features of the migration script in cooperation with the gradient boosting tree regression model. When training the model, a data set containing features such as table size, number of indexes, and lock type encoding and the corresponding execution time labels are used as the input. After training, the model can predict the execution time of the migration script.

[0073] In terms of business impact assessment, since it is a general convention to make the database table names self-explanatory when creating database tables, the tables can be determined whether they are critical tables based on the table names in the script features of the migration scripts. The isCritical method receives the table name as a parameter and determines whether the table is a critical table by judging whether the table name is "orders" or whether it ends with "_config". If the above conditions are met, it indicates that the table is a critical table and the risk of business impact assessment is relatively high.

[0074] Obtaining the risk level of the migration script according to the risk value of the executed migration script and the preset risk classification rules helps to understand the possible risk levels during the migration process, so as to formulate corresponding coping strategies to ensure the smooth execution of the migration script. The above-mentioned preset risk classification rules divide the risk levels into at least high-risk levels and low-risk levels. To achieve the precise matching of the risk value and the risk level, the risk value is usually divided into several numerical intervals, and then the mapping rules between each interval and the risk level are established. In this way, the risk value can be directly corresponding to the corresponding risk level, providing a clear quantitative standard for risk assessment. For example, when the risk value range is set to 0-100, it is divided into three intervals: 0-30 corresponds to the low-risk level, and the possibility of causing serious impacts on the database and business systems during the execution of the migration scripts in this interval is relatively low; 31-60 is the medium-risk level, and certain risks may occur when executing such scripts and need to be closely monitored; 61-100 is the high-risk level, which means that the execution of the script may bring greater impacts to the stability of the database and the continuity of the business and needs to be treated with caution.

[0075] Dynamically executing the migration script according to the risk level of the migration script as described above makes the database migration work more in line with the actual business needs, not only ensuring the stable operation of the system, but also effectively improving the resource utilization rate and reducing the operation and maintenance costs.

[0076] In some embodiments, the steps of dynamically executing the migration script according to the risk level of the migration script include:

[0077] Judging whether the risk level of the migration script is a low-risk level;

[0078] If so, automatically execute the migration script;

[0079] Otherwise, split the migration script into several new migration scripts and execute them in sequence.

[0080] Migration scripts with a low risk level usually have a relatively small impact on the database, with low execution risks. During the automatic execution process, the probability of failure is also relatively low. Therefore, automatically executing such scripts can ensure the stable operation of the database without interfering with normal business operations. In contrast, migration scripts with a high risk level often involve complex database structure changes or a large amount of data migration operations. If executed all at once, it is very likely to cause problems such as a decline in database performance and lock conflicts. After splitting it into several new migration scripts, the scope of operation of each script is reduced, the complexity is lowered, and correspondingly, the impact on the database is also significantly reduced. By sequentially executing these new migration scripts, the migration task can be gradually completed while maintaining the stability of the database, avoiding long waits caused by high-risk operations, and reducing the cost of error handling. In addition, the execution time of the split new migration scripts is short. Once a problem occurs, it can be quickly located and solved, thereby accelerating the progress of the entire migration process. For example, if a migration script is to add a freight field to an order table with a default value of 0, when the risk level of the migration script is not low, the instructions in the migration script are split into two new migration scripts: adding the field and assigning a value to the field; when executing, first execute the migration script for adding the field, and then assign a value to the field in sequence.

[0081] In some embodiments, the step of dynamically executing a migration script according to the risk level of the migration script includes: determining whether the risk level of the migration script is lower than the high risk level. If so, execute the migration script; otherwise, transfer it to manual operation. That is, when the risk level of the migration script is lower than the high risk level, it indicates that its potential risk is within an acceptable range. When the risk level of the migration script reaches or is higher than the high level, to avoid a serious impact on the database and business system caused by high-risk operations, the migration script is transferred to the manual process. At this stage, experienced database administrators and business experts can conduct a comprehensive and in-depth evaluation of the operation content, execution plan, potential impact, etc. of the migration script, re-determine the risk level of the migration script, thereby reducing the occurrence of high risk levels caused by insufficient evaluation, and can also analyze and optimize the migration script, such as adjusting the script logic and optimizing the database configuration to reduce the risk level of the migration script; at the same time, by preventing the blind execution of high-risk level migration scripts, the possibility of business interruption caused by migration operations is reduced, ensuring business continuity.

[0082] In some embodiments, during the process of dynamically executing a migration script according to the risk level of the migration script, it further includes determining whether the migration script meets the suspension condition; if it meets, the execution is suspended, otherwise, it continues to execute. By setting the suspension condition, the execution status of the migration script is monitored in real time or at regular intervals, so as to better control the database migration process. When the suspension condition is triggered, the further execution of the migration script is stopped, and the migration script can be analyzed in time. According to the existing situation of the migration script, the migration strategy can be optimized in time, such as adjusting the script logic, optimizing the database configuration, etc., and then the execution is resumed, so as to improve the migration efficiency and reduce the overall time consumption of the migration task.

[0083] In some embodiments, the suspension condition is that the execution duration of the migration script exceeds the preset execution duration; that is, during the execution of the migration script, the execution duration of the migration script is monitored. When the execution duration exceeds the preset execution duration, the suspension condition is triggered. For example, the preset execution duration is 30 minutes. When the script executes to 31 minutes, the suspension condition is triggered. Once the suspension condition is triggered, the further execution of the migration script is stopped, avoiding that the migration script consumes too many system resources due to too long execution time, resulting in a decline in database performance and affecting the normal progress of other business operations. For example, preventing a long full-table scan operation from occupying a large amount of CPU and memory resources, ensuring the stable operation of the database system, and timely suspending the execution of the timed-out migration script.

[0084] In some embodiments, the suspension condition is that the migration script executes with an error. That is, when an error occurs during the execution of the migration script is monitored, the suspension condition is immediately triggered. The above errors can be syntax errors in the migration script (such as misspelling of SQL statements), runtime errors (such as data type mismatch, violation of database constraints), or errors caused by environmental factors such as database connection interruption and resource shortage. Taking an SQL script as an example, when executing an INSERT INTO statement, if it is monitored that the data value does not match the data type defined by the table structure, the suspension condition is triggered, and the further execution of the migration script is immediately stopped. By interacting with the database execution engine, the currently executing SQL statement or other operations are interrupted to prevent the error from expanding further.

[0085] In some embodiments, the suspension condition is that the risk level of the migration script is a high-risk level. That is, when the migration script is executed, if it is monitored that the migration script is at a high-risk level, the suspension condition is immediately triggered. When the suspension condition is triggered, the execution of the migration script is immediately stopped, avoiding that the execution of the high-risk level migration script causes a serious impact on the database and the business system and has an adverse effect on the database.

[0086] In practical applications, one or more pause conditions in the above embodiments can be set according to specific application scenarios and requirements. When multiple pause conditions are set, they can be combined in a logical "OR" or "AND" manner. For example, as long as any one of the pause conditions is met, the execution of the migration script is stopped; or multiple pause conditions need to be met simultaneously to stop the execution of the migration script. By reasonably setting the pause conditions, the migration efficiency can be effectively improved and the overall time consumption of the migration task can be reduced.

[0087] During the execution of the migration script, once the pause condition is met, the execution of the migration script will be immediately stopped. Taking a script containing table field modification operations as an example, suppose a migration script contains two statements for modifying table fields. When the first statement is successfully executed and the second statement meets the pause condition during execution, the execution will be immediately stopped. In this case, if you want to restart after optimizing the migration script, you need to manually delete the execution record of the first statement and restore the fields that have been successfully modified by the first statement to their initial state before you can successfully restart. This operation method not only increases the complexity of the overall operation, but also greatly increases the risk of human error, which is very likely to have a negative impact on data integrity and system stability.

[0088] Therefore, in some embodiments, when the pause condition is eliminated, the execution continues at the position where the execution was paused. Specifically, the execution state of the migration script at the time of pause can be recorded, including information such as the operations that have been completed, the progress of the current operation, and the specific position where the pause condition was triggered. At the same time, the intermediate state and relevant data of the migration script execution are saved, including the modified database structure, the inserted or updated data, etc. When it is detected that the pause condition is eliminated, the execution state of the migration script at the time of pause is automatically read, and based on the recorded specific position where the pause condition was triggered, the statement in the migration script that needs to continue execution is accurately located. During the execution process, the execution state of the migration script is monitored in real time to ensure that each operation is successfully completed. If the pause condition is met again, the above pause and resume processes will be repeated. Only by paying attention to the elimination of the pause condition, there is no need to manually delete the execution record and restore the field state, and the execution automatically continues from the pause position, greatly simplifying the operation process and reducing the operation difficulty and error risk. For example, if the migration script is to insert 100,000 pieces of data and the pause condition is triggered when it reaches 9,900 pieces, when the pause condition is eliminated and the execution is resumed, it can automatically continue from 9,901. Another example is that there are two SQL execution statements in the migration script, which modify the fields of two tables respectively. The first execution statement is successfully executed, and the second execution statement fails, triggering the pause condition. When the failure problem of the second execution statement is fixed and the execution is resumed, directly start the project, and the first execution statement that has been executed will be automatically skipped, and the second statement will continue to be executed.

[0089] When the pause condition is triggered, the further execution of the migration script is stopped. In order to analyze the migration script in a timely manner and optimize the migration strategy according to the existing situation of the migration script. In some embodiments, if the pause condition is met, while pausing the execution of the migration, an alarm mechanism will also be started synchronously. The alarm methods can include but are not limited to sound, light, message push, etc. Intuitive methods such as sound and light are used to quickly attract the attention of on-site operation and maintenance personnel. With the help of message push, alarm information can be sent remotely. The ways of message push include but are not limited to email, text message, instant messaging tools, etc. When using message push for alarm, the content of the message can be the satisfied pause condition, pause time, script location, and related error descriptions, so as to quickly understand the severity of the problem, locate the position where the error occurred, analyze the general cause of the problem, and then take targeted measures to solve the problems that occur during the migration process and ensure the smooth progress of the database migration.

[0090] As time goes by and the database environment changes, the operating conditions faced by the migration script are constantly changing, and the risk value will also fluctuate accordingly. In this case, relying on the original historical execution data may not be able to accurately calculate the risk value of the current migration script. Therefore, it is crucial to update the historical execution data in a timely manner to ensure the accuracy of risk assessment. In some embodiments, an update to the historical execution data is also provided, that is, after dynamically executing the steps of the migration script according to the risk level of the migration script, it further includes using the execution data of the migration script to update the historical execution data, cost calculation model, or preset execution cost algorithm. By continuously updating and supplementing the historical execution data based on the actual execution situation, it can ensure that the historical execution data always conforms to the current actual situation, thereby more accurately evaluating the risk value of the migration script. The accuracy of risk assessment is the key to ensuring the smooth progress of database migration. The following takes the cost calculation model as an example. By setting up an incremental learning mechanism, its accuracy and adaptability can be effectively improved. The incremental learning mechanism can automatically write the results after each execution of the migration script into the training set for the model to perform incremental learning. After each execution, the model will analyze the new data, continuously adjust its internal parameters, and optimize the estimation of the execution cost of the migration script. Code example:

[0091]

[0092] During the connection stage between project development and testing, when releasing from the development branch to the testing branch, problems caused by migration scripts often occur. Once testers start the project and find problems with the migration scripts, to avoid interference with other functional tests, it is necessary to roll back the push of the current development branch and restore the code and database status of the testing branch to the initial state before the push. However, even after the code rollback is completed, the execution status of the scripts has been recorded in the flyway_schema_history table during the execution of the migration scripts before, and the actual changes to the database schema have also taken effect. This leads to when restarting the project again, based on its recording mechanism, it will determine that these scripts have been executed and will not execute again. But at this time, the code of the testing branch may have been rolled back to a version that does not include these database changes, which results in an inconsistency between the actual schema version of the testing database and the database version expected by the code. Such an inconsistency is likely to cause problems such as non-existence of database tables or fields accessed by the code, seriously hindering the orderly progress of the testing work. Therefore, in some embodiments, when dynamically executing migration scripts according to the risk level of the migration scripts, the system also needs to introduce a rollback judgment link, that is, to judge whether the preset rollback conditions are met. When the rollback conditions are met, the rollback operation of the migration script is triggered to ensure the consistency between the database status and the code version and maintain the smooth progress of project testing.

[0093] In some embodiments, the way to roll back the execution of the migration script is to roll back based on the reverse script of the executed migration script; specifically, when writing the migration script, the corresponding reverse script will be written synchronously, and this reverse script is used to undo the changes brought by the migration script. When the rollback conditions are met and a rollback operation needs to be performed, the corresponding reverse script will be executed. For example, if the operation of the migration script is to add a new table, the operation of the corresponding reverse script is to delete this table; if the operation of the migration script is to insert a record into the database, the operation of the corresponding reverse script is to delete this record.

[0094] In some embodiments, the way to roll back the execution of the migration script is to roll back based on the database transaction mechanism. Before executing the previous migration script, a rollback point mark at the transaction level is made, and subsequent rollback is performed according to the rollback point mark; the database transaction has the atomicity feature, that is, all operations within the transaction show the characteristic of "either all are successfully executed or all fail and roll back". During the execution of the migration script, the operations included in the script are encapsulated within a transaction. When the preset rollback conditions are met and a rollback is required, the transaction rollback function of the database itself can be used to undo the executed operations, thereby realizing the rollback of the migration script.

[0095] In some embodiments, the migration script execution rollback method is a rollback based on database backup and recovery; before executing the migration script, a built-in tool or third-party backup software is used to perform a full backup of the database, and the complete state of the database at that point in time is stored. The backup file contains all the data in the database and the configuration information of the database, etc. When the preset rollback conditions are met and a rollback is required, the previously created backup file can be used to restore the database to the state before the migration operation is executed.

[0096] In some embodiments, reverse processing is performed based on the log, and a rollback script is generated for rollback; the log file of the database (such as the binary log, i.e., Binlog) records in detail all data change operations that occur in the database. By parsing the log file, the specific changes caused during the execution of the migration script can be accurately located, and the corresponding reverse operation is generated based on this, thereby achieving rollback.

[0097] The above rollback conditions can be functional test exceptions (such as data integrity exceptions, table structure or field access failures, etc.), performance issues (such as serious response time timeouts, excessive resource utilization, etc.) or environment-related issues (such as test environment compatibility issues, configuration conflicts, etc.). In some cases, the rollback condition can be the same as the pause condition. When the two conditions are the same, different operations will be performed based on different strategies. If only the pause condition is met, the execution of the migration script will be suspended and an alarm will be issued so that the migration script can be analyzed in time and the migration strategy can be optimized in time according to the current situation of the migration script. When the rollback condition is met, not only will the execution of the migration script be terminated, but the rollback operation will also be quickly started according to the preset rollback mechanism to restore the database status and code version to the state before the migration.

[0098] It is worth noting that although some rollback conditions overlap with pause conditions, they are essentially different in terms of response mechanisms. The rollback mechanism aims to eliminate the adverse effects of the executed migration script on the database and return the database version to a stable initial state, thereby ensuring the smooth progress of subsequent operations. The pause mechanism focuses more on stopping the further expansion of potential risks in a timely manner, in order to gain time for analysis and decision-making, so as to flexibly choose to continue execution, adjust the migration strategy, or perform a rollback operation.

[0099] Finally, it should be noted that although the above embodiments have been described in the specification and drawings of this application, this does not limit the scope of patent protection of this application. All technical solutions generated by replacing or modifying equivalent structures or equivalent processes based on the essential concept of this application using the contents recorded in the specification and drawings of this application, as well as directly or indirectly implementing the technical solutions of the above embodiments in other related technical fields, are included in the scope of patent protection of this application.

Claims

1. A database migration method based on Flyway, characterized in that, It includes the following steps: Obtain unexecuted migration scripts, where the migration scripts are created based on Flyway; Obtain the script features and execution plans of the migration scripts; Obtain the risk value of the execution of the migration script according to the script features and execution plans of the migration script; Obtain the risk level of the migration script according to the risk value of the execution of the migration script and the preset risk classification rules; Dynamically execute the migration script according to the risk level of the migration script.

2. The database migration method based on Flyway according to claim 1, wherein The risk value of the execution of the migration script is obtained by matching historical execution data; or Obtained by inputting a cost calculation model pre-trained based on historical execution data; or Calculated based on a preset execution cost algorithm.

3. The database migration method based on Flyway according to claim 1, characterized in that, The risk value is obtained through comprehensive calculation of the lock table risk assessment value, the predicted execution time, and the business impact assessment value.

4. The database migration method based on Flyway according to claim 1, characterized in that The risk levels of the risk classification rules include at least high level and low level; The step of dynamically executing the migration script according to the risk level of the migration script includes: Judge whether the risk level of the migration script is a low risk level; If so, automatically execute the migration script; Otherwise, split the migration script into several new migration scripts and execute them in sequence.

5. The database migration method based on Flyway according to claim 1, characterized in that The risk levels of the risk classification rules include at least high risk level and low risk level; The step of dynamically executing the migration script according to the risk level of the migration script includes: Judge whether the risk level of the migration script is lower than the high risk level. If so, execute the migration script, otherwise transfer to manual operation.

6. The database migration method based on Flyway according to claim 1, wherein, During the process of dynamically executing the migration script according to the risk level of the migration script, it also includes judging whether the migration script meets the suspension condition; if it meets, suspend the execution, otherwise continue to execute; So the suspension conditions include any one or more of the following: The execution duration of the migration script exceeds the preset execution duration; An error occurs during the execution of the migration script; The risk level of the migration script is a high risk level.

7. The database migration method based on Flyway according to claim 6, wherein When the suspension condition is eliminated, continue to execute at the position where the execution was suspended.

8. The database migration method based on Flyway according to claim 6, characterized in that, During the process of dynamically executing the migration script according to the risk level of the migration script, if the suspension condition is met, while suspending the execution, an alarm mechanism will also be started synchronously.

9. The database migration method based on Flyway according to claim 1, wherein After the step of dynamically executing the migration script according to the risk level of the migration script, it also includes updating the historical execution data, the cost calculation model, or the preset execution cost algorithm with the execution data of the migration script.

10. The database migration method based on Flyway according to claim 1, wherein When dynamically executing the migration script according to the risk level of the migration script, it also includes judging whether the rollback condition is met. If it is met, perform a rollback of the migration script; The method of performing a rollback of the migration script is to perform a rollback based on the reverse script of the executed migration script; Or Perform a rollback based on the database transaction mechanism; Or Perform a rollback based on database backup and recovery; Or Perform reverse processing based on the log to generate a rollback script for rollback.

Images