Method and device for online DDL table structure modification of relational database

A database and relational technology, applied in the field of relational databases, can solve problems affecting the normal operation of online business, and achieve the effects of short locking time, integrity and consistency

Pending Publication Date: 2020-03-24
北京思特奇信息技术股份有限公司
View PDF2 Cites 1 Cited by
  • Summary
  • Abstract
  • Description
  • Claims
  • Application Information

AI Technical Summary

Problems solved by technology

[0003] For the MySQL database, the DDL operation is obviously a function that all MySQL DBAs criticize, because in MySQL, the table will be locked when DDL is performed on the table, causing a large number of threads on the database to be in the "Waiting for meta datalock" state, which seriously affects the line. The business is running normally

Method used

the structure of the environmentally friendly knitted fabric provided by the present invention; figure 2 Flow chart of the yarn wrapping machine for environmentally friendly knitted fabrics and storage devices; image 3 Is the parameter map of the yarn covering machine
View more

Image

Smart Image Click on the blue labels to locate them in the text.
Viewing Examples
Smart Image
  • Method and device for online DDL table structure modification of relational database
  • Method and device for online DDL table structure modification of relational database
  • Method and device for online DDL table structure modification of relational database

Examples

Experimental program
Comparison scheme
Effect test

Embodiment Construction

[0039] The principles and features of the present invention will be described below in conjunction with the accompanying drawings, and the examples given are only used to explain the present invention, and are not intended to limit the scope of the present invention.

[0040] Such as figure 1 As shown, a method for modifying the table structure of a relational database online DDL provided by the embodiment of the present invention, the method includes: S1, create a shadow table according to the DDL function that needs to be modified, and do not lock the original table at the same time, and the business system continues to maintain DML operation of the original table.

[0041] Online DDL supports functions, as shown in Table 1:

[0042]

[0043] Table 1

[0044] Connect to the master instance of the database through the Online DDL client designed without triggers. The original table receives a DDL request, and the DDL client creates a shadow table ghost_table according to ...

the structure of the environmentally friendly knitted fabric provided by the present invention; figure 2 Flow chart of the yarn wrapping machine for environmentally friendly knitted fabrics and storage devices; image 3 Is the parameter map of the yarn covering machine
Login to view more

PUM

No PUM Login to view more

Abstract

The invention discloses a method and device for online DDL table structure modification of a relational database, and relates to the field of relational databases (MySQL). The method comprises the steps: creating a shadow table according to a DDL function needing to be modified while an original table is not locked and a service system continuously maintains DML operation on the original table; after the shadow table is created, copying the data in the original table into the shadow table; checking the copy data in the shadow table and the data of the original table; and when the verificationresults of the shadow table and the original table are consistent, guaranteeing the consistency of the copied table data, copying and renaming the shadow table of the original table data, replacing the original table. The whole DDL table structure modification process does not affect normal operation of online services at all, and efficient processing of a large number of threads in the table structure modification process is guaranteed, and normal operation of a service system can be maintained, and target writing operation of a front-end application is not affected.

Description

technical field [0001] The invention relates to the field of relational databases (MySQL), in particular to a method and device for online DDL modification of table structures of relational databases. Background technique [0002] After the project uses the MySQL database to officially launch the production environment, due to business changes or database operation and maintenance, we will always encounter business scenarios where DDL changes are made to data tables, such as adding, modifying, and deleting fields or indexes for certain tables. [0003] For the MySQL database, the DDL operation is obviously a function that all MySQL DBAs criticize, because in MySQL, the table will be locked when DDL is performed on the table, causing a large number of threads on the database to be in the "Waiting for meta datalock" state, which seriously affects the line. Business is running normally. Contents of the invention [0004] The technical problem to be solved by the present inve...

Claims

the structure of the environmentally friendly knitted fabric provided by the present invention; figure 2 Flow chart of the yarn wrapping machine for environmentally friendly knitted fabrics and storage devices; image 3 Is the parameter map of the yarn covering machine
Login to view more

Application Information

Patent Timeline
no application Login to view more
Patent Type & Authority Applications(China)
IPC IPC(8): G06F16/28G06F16/22
CPCG06F16/284G06F16/2282
Inventor 武鹏飞
Owner 北京思特奇信息技术股份有限公司
Who we serve
  • R&D Engineer
  • R&D Manager
  • IP Professional
Why Eureka
  • Industry Leading Data Capabilities
  • Powerful AI technology
  • Patent DNA Extraction
Social media
Try Eureka
PatSnap group products

Browse by: Latest US Patents, China's latest patents, Technical Efficacy Thesaurus, Application Domain, Technology Topic.

© 2024 PatSnap. All rights reserved.Legal|Privacy policy|Modern Slavery Act Transparency Statement|Sitemap