CN109446022B

CN109446022B - Method and device for detecting abnormal overflow page of database and storage medium

Info

Publication number: CN109446022B
Application number: CN201811186772.3A
Authority: CN
Inventors: 陈明辉; 张辉极; 黄加红; 连洲红
Original assignee: Xiamen Meiya Pico Information Co Ltd
Current assignee: Xiamen Meiya Pico Information Co Ltd; China Electronics Engineering Design Institute Co Ltd
Priority date: 2018-10-12
Filing date: 2018-10-12
Publication date: 2022-08-12
Anticipated expiration: 2038-10-12
Also published as: CN109446022A

Abstract

The invention provides a method, a device and a storage medium for detecting abnormal database overflow pages, wherein the method comprises the steps of obtaining a database overflow page unit, then reading out a node of a linked list of the database overflow page unit, reading the page number RN of the next node from the node, obtaining the total length TL of the linked list of the data overflow page unit, calculating the length RL from the head of the linked list of the database overflow page unit to the current node, and detecting whether the database overflow page unit is abnormal according to the RN, the TL and the RL, so that the linked list node of the overflow page unit in a B + tree is automatically read, and whether the linked list node of the overflow page unit is abnormal is automatically judged, a foundation is laid for subsequent abnormal data repair, and the method has strong universality.

Description

Method and device for detecting abnormal overflow page of database and storage medium

Technical Field

The invention relates to the technical field of databases, in particular to a method and a device for detecting abnormal overflow pages of a database and a storage medium.

Background

SQLite, a lightweight database, is an ACID compliant relational database management system that is contained in a relatively small C library. It is a public domain item established by d.richardhipp. The design target is embedded, and the embedded type memory is used in a plurality of embedded products at present, the occupied resource is very low, and only a few hundred K of memories are enough in the embedded type equipment. The system can support a mainstream operating system such as Windows/Linux/Unix and the like, can be combined with a plurality of programming languages such as Tcl, C #, PHP, Java and the like, has an ODBC interface, and has a processing speed higher than that of a world-known database management system with two types of sources such as Mysql and PostgreSQL. Consequently, the use of SQLite databases is becoming more widespread, particularly in portable devices.

However, because the stability of the portable device is relatively poor, for example, when data writing is performed, power is suddenly turned off, so that data abnormality of SQLite is caused, currently, detection of data abnormality of SQLite is performed, few studies are conducted at home and abroad, in the prior art, the SQLite is generally queried and written into a new database one by one, but the method has the disadvantages that the abnormal type of the database cannot be judged, abnormal points of the database cannot be accurately found, and when abnormal data is encountered, the query process may be interrupted, so that the database repair process is incomplete, and the situation that records of the repaired database have great difference often occurs, so that the accuracy and the comprehensiveness of database repair are affected. Therefore, how to accurately detect the data abnormality of the SQLite is a technical problem in the prior art.

Disclosure of Invention

The present invention provides the following technical solutions to overcome the above-mentioned drawbacks in the prior art.

A method for detecting a database overflow page exception, the method comprising:

an acquisition step, acquiring a database overflow page unit;

and a detection step, namely reading out a node of a linked list of the database overflow page unit, reading the page number RN of the next node from the node, acquiring the total length TL of the linked list of the data overflow page unit, calculating the length RL from the head of the linked list of the database overflow page unit to the current node, and detecting whether the database overflow page unit is abnormal or not according to the RN, the TL and the RL.

Still further, the method further comprises: and recording error information of the abnormal database overflow page, wherein the error information comprises a page number, a unit starting address, a next unit starting address of a first node of a linked list of a database overflow page unit, and a page number and a node sequence number of a node of the abnormal database overflow page.

Furthermore, the database is an SQLite database, the database comprises a plurality of SQLite pages, the SQLite pages are managed by adopting a B + tree, and the SQLite pages storing data in the B + tree are called leaf pages.

Further, the acquiring step operates as: reading a leaf page from the B + tree, searching a data record, if the search result is empty, indicating that the current page is searched, continuing to search the next page, and if the search result is not empty, recording the offset of the data record in the leaf page as r _ offset; judging whether r _ size is less than or equal to usable _ size-r _ offset, if so, indicating that the data record is not a database overflow page unit, then continuously inquiring the next data record, and if not, indicating that the data record is the database overflow page unit; wherein r _ size is the size of the data record, and usable _ size is the available size of the SQLite page.

Further, the operation of detecting whether the database overflow page unit is abnormal according to the RN, TL and RL is as follows: judging whether RN is larger than the maximum page number MaxN of the database, if so, determining that the data of the node corresponding to RN is abnormal data, if not, determining whether RN is 0 and RL is less than TL, or RN is not equal to 0 and RL is not less than TL, if so, determining that the data of the node corresponding to RN is abnormal data, if not, counting the times N of the node corresponding to RN at B + tree and the node of the idle page linked list, if N is not less than 2, determining that the data of the node corresponding to RN is abnormal data, and if not, indicating that the data of the overflow page of the database is normal.

The invention also provides a device for detecting the abnormal overflow page of the database, which comprises:

the acquisition unit is used for acquiring a database overflow page unit;

the detection unit is used for reading out one node of the linked list of the database overflow page unit, reading the page number RN of the next node from the node, acquiring the total length TL of the linked list of the data overflow page unit, calculating the length RL from the head of the linked list of the database overflow page unit to the current node, and detecting whether the database overflow page unit is abnormal or not according to the RN, the TL and the RL.

Still further, the apparatus further comprises: and the recording unit is used for recording error information of the abnormal database overflow page, and the error information comprises the page number of the first node of the linked list of the database overflow page unit, the unit starting address, the next unit starting address, the page number of the node with the abnormal database overflow page and the node sequence number.

Furthermore, the database is an SQLite database, the database comprises a plurality of SQLite pages, the SQLite pages are associated by adopting a B + tree, and the SQLite pages storing data in the B + tree are called leaf pages.

Further, the obtaining unit performs the following operations: reading a leaf page from the B + tree, searching a data record, if the search result is empty, continuing to search the next page, and if the search result is not empty, recording the offset of the data record in the leaf page as r _ offset; judging whether r _ size is less than or equal to usable _ size-r _ offset, if so, indicating that the data record is not a database overflow page unit, then continuously inquiring the next data record, and if not, indicating that the data record is the database overflow page unit; wherein r _ size is the size of the data record, and usable _ size is the available size of the SQLite page.

The invention also proposes a computer-readable storage medium having stored thereon computer program code which, when executed by a computer, performs any of the methods described above.

The invention has the technical effects that: the invention reads the page number RN of the next node from the node by reading out one node of the linked list of the overflow page unit of the database, obtains the total length TL of the linked list of the overflow page unit of the data, calculates the length RL from the head of the linked list of the overflow page unit of the database to the current node, and detects whether the overflow page unit of the database is abnormal or not according to the RN, the TL and the RL, thereby realizing the automatic reading of the linked list node of the overflow page unit in the B + tree, automatically judging whether the linked list node of the overflow page unit is abnormal or not, laying a foundation for the subsequent data abnormal restoration, and having strong universality.

Drawings

Other features, objects and advantages of the present application will become more apparent upon reading of the following detailed description of non-limiting embodiments thereof, made with reference to the accompanying drawings.

FIG. 1 is a flow chart of a method for detecting a database overflow page exception according to an embodiment of the present invention.

FIG. 2 is a page structure diagram of a database according to an embodiment of the invention.

FIG. 3 is a block diagram of a database overflow page according to an embodiment of the present invention.

Fig. 4 is a block diagram of an apparatus for detecting an overflow page abnormality of a database according to an embodiment of the present invention.

Detailed Description

The present application will be described in further detail with reference to the following drawings and examples. It is to be understood that the specific embodiments described herein are merely illustrative of the relevant invention and not restrictive of the invention. It should be noted that, for convenience of description, only the portions related to the related invention are shown in the drawings.

It should be noted that the embodiments and features of the embodiments in the present application may be combined with each other without conflict. The present application will be described in detail below with reference to the embodiments with reference to the attached drawings.

FIG. 1 shows a method for detecting an exception of a database overflow page according to the present invention, which comprises:

in the acquiring step S101, a database overflow page unit is acquired.

And a detection step S102, reading out a node of the linked list of the database overflow page unit, reading the page number RN of the next node from the node, acquiring the total length TL of the linked list of the data overflow page unit, calculating the length RL from the head of the linked list of the database overflow page unit to the current node, and detecting whether the database overflow page unit is abnormal or not according to the RN, the TL and the RL.

In one embodiment, the database adopted by the invention is an SQLite database, the database comprises a plurality of SQLite pages, the SQLite pages are managed by adopting a B + tree, and the SQLite pages storing data in the B + tree are called leaf pages. The method is not limited to a certain database, is suitable for detecting and analyzing the overflow page abnormality of all SQLite databases, and is also suitable for detecting the overflow page abnormality of other SQLite-like databases.

The SQLite database file is composed of a series of data tables, each data table is composed of a series of pages, and the page organization is managed by a B + tree, each B + tree node corresponds to a SQLite page, the size of the page, page _ size, is the power of n of 2, and is commonly 1024, 2048, 4096 bytes, etc., the reserved part of the tail of the page, page _ size, which is deducted, is the available size of the page, usable _ size (in general, page _ size and usable _ size are equal). The leaf node where the data is stored is called a leaf page, and the internal node where the index is stored is called an internal page, each page is composed of four parts, as shown in fig. 2, where the 0 th byte of the header is a page type including the internal page (OXO5) and the leaf page (xod), the 1 st to 2 nd bytes indicate the start address of the first free block, the 3 rd to 4 th bytes indicate the number of cells of the page, the 5 th to 6 th bytes indicate the start address of the cell content area, and the 7 th byte indicates the number of fragmentation bytes.

The B + tree leaf pages internally organize data in units of cells (cells), one cell containing one (or part, when an overflow page is used) payload (also referred to as B + tree record data). Cells (cells) are of variable size and the page size is fixed, which makes it possible for a cell to be larger than a full page, and such a cell overflows onto a linked list of overflowing pages, as shown in fig. 3. The total length TL of the linked list of the data overflow page unit may be obtained by reading the unit header of the overflow page unit shown in fig. 3, and the length RL from the link table header of the database overflow page unit to the current node may be calculated by accessing the linked list of fig. 3.

In FIG. 3, the last cell of the B + tree page is oversized, requiring the use of overflow pages. At this time, the last 4 bytes of the cell are the page number of the 1 st overflow page in the overflow page chain table. For each overflow page, the first 4 bytes are the page number of the next overflow page, and when the value is 0, the page is the tail page of the table of the overflow page linked list. Each overflow page is filled with data (except for the first 4 bytes) in its entirety except for the last overflow page, and the last overflow page may have little data, even only one byte of data, but an overflow page will not store data from both cells.

In one embodiment, the operation of the acquiring step S101 is: reading a leaf page from the B + tree, searching a data record, if the search result is empty, indicating that the current page is searched, continuing to search the next page, and if the search result is not empty, recording the offset of the data record in the leaf page as r _ offset; judging whether r _ size is less than or equal to usable _ size-r _ offset, if so, indicating that the data record is not a database overflow page unit, then continuously inquiring the next data record, and if not, indicating that the data record is the database overflow page unit; wherein r _ size is the size of the data record, and usable _ size is the available size of the SQLite page. By the operation, which SQLite data records are the overflow page units of the database can be determined, which is one of the key points for realizing the invention and one of the important invention points of the invention.

Another important point of the present invention is to perform detection of abnormal operation, and to perform recovery of the abnormality only if the position of the abnormality is accurately found. In one embodiment, the operation of detecting whether the database overflow page unit is abnormal according to the RN, the TL and the RL is as follows: judging whether RN is larger than the maximum page number MaxN of the database, if so, determining that the data of the node corresponding to RN is abnormal data, if not, determining whether RN is 0 and RL is less than TL, or RN is not equal to 0 and RL is not less than TL, if so, determining that the data of the node corresponding to RN is abnormal data, if not, counting the times N of the node corresponding to RN at B + tree and the node of the idle page linked list, if N is not less than 2, determining that the data of the node corresponding to RN is abnormal data, and if not, indicating that the data of the overflow page of the database is normal. The detection principle is that the page number of a node cannot exceed the maximum page number MaxN of the database, and cannot appear in all nodes 2 times, and when RN is equal to 0, RL is necessarily greater than or equal to TL, and when RN is equal to 0, RL is necessarily less than TL.

In order to record the detected abnormality and facilitate the repair of subsequent data, as shown in fig. 1, the method further includes: and a recording step S103, recording error information of the abnormal database overflow page, wherein the error information comprises a page number, a unit starting address and a next unit starting address of a first node of a linked list of a database overflow page unit, and a page number and a node sequence number of a node of the abnormal database overflow page. These page numbers, addresses, etc. can be recorded when accessing the overflow page unit of the database shown in fig. 3, and the error information can be recorded in the form of [ start _ pgno, start _ offset, next _ offset, abrormal _ pgno, abrormal _ index ], where if the first node is the last unit of the SQLite page, the next _ offset is equal to usable _ size, which is one of the important invention points of the present invention and can be used for repairing the following data exception.

With further reference to fig. 4, as an implementation of the method shown in fig. 1, the present application provides an embodiment of an apparatus for detecting an overflow page exception of a database, where the embodiment of the apparatus corresponds to the embodiment of the method shown in fig. 1, and the apparatus may be specifically included in various electronic devices.

FIG. 4 shows an apparatus for detecting an overflow page exception of a database according to the present invention, which comprises:

an obtaining unit 401 is configured to obtain a database overflow page unit.

The detection unit 402 is configured to read out a node of a linked list of a database overflow page unit, read a page number RN of a next node from the node, obtain a total length TL of the linked list of the data overflow page unit, calculate a length RL from a linked list header of the database overflow page unit to a current node, and detect whether the database overflow page unit is abnormal according to the RN, the TL, and the RL.

In an embodiment of the present invention, the database adopted in the present invention is an SQLite database, the database includes a plurality of SQLite pages, the SQLite pages are managed by a B + tree, and the SQLite pages storing data in the B + tree are called leaf pages. The method is not limited to a certain database, is suitable for detecting and analyzing the overflow page abnormality of all SQLite databases, and is also suitable for detecting the overflow page abnormality of other SQLite-like databases.

In an embodiment of the present apparatus, the obtaining unit 401 performs the following operations: reading a leaf page from the B + tree, searching a data record, if the search result is empty, indicating that the current page is searched, continuing to search the next page, and if the search result is not empty, recording the offset of the data record in the leaf page as r _ offset; judging whether r _ size is less than or equal to usable _ size-r _ offset, if so, indicating that the data record is not a database overflow page unit, then continuously inquiring the next data record, and if not, indicating that the data record is the database overflow page unit; wherein r _ size is the size of the data record, and usable _ size is the available size of the SQLite page. By the operation, which SQLite data recording units are database overflow page units can be determined, which is one of the key points for realizing the invention and one of the important invention points of the invention.

Another important point of the present invention is to perform detection of abnormal operation, and to perform recovery of the abnormality only if the position of the abnormality is accurately found. In one embodiment, the operation of detecting whether the database overflow page unit is abnormal according to the RN, the TL and the RL is as follows: judging whether RN is greater than the maximum page number MaxN of the database, if so, determining that the data of the node corresponding to RN is abnormal data, if not, determining whether RN is equal to 0 and RL is less than TL, or RN is not equal to 0 and RL is not less than TL, if so, determining that the data of the node corresponding to RN is abnormal data, if not, counting the times N of the node corresponding to RN at B + tree and the idle page linked list node, if N is not less than 2, determining that the data of the node corresponding to RN is abnormal data, and if not, indicating that the data of the overflow page of the database is normal. The detection principle is that the page number of a node cannot exceed the maximum page number MaxN of the database, and cannot appear in all nodes 2 times, and when RN is equal to 0, RL is necessarily greater than or equal to TL, and when RN is equal to 0, RL is necessarily less than TL.

In order to record the detected abnormality and facilitate the repair of the subsequent data, as shown in fig. 4, the apparatus further includes: the recording unit 403 records error information of the database overflow page exception, where the error information includes a page number of a first node of a linked list of the database overflow page unit, a unit start address, a next unit start address, and a page number and a node sequence number of a node where the database overflow page exception occurs. These page numbers, addresses, etc. can be recorded when accessing the overflow page unit of the database shown in fig. 3, and the error information can be recorded in the form of [ start _ pgno, start _ offset, next _ offset, abrormal _ pgno, abrormal _ index ], where if the first node is the last unit of the SQLite page, the next _ offset is equal to usable _ size, which is one of the important invention points of the present invention and can be used for repairing the following data exception.

For convenience of description, the above devices are described as being divided into various units by function, and are described separately. Of course, the functionality of the units may be implemented in one or more software and/or hardware when implementing the present application.

From the above description of the embodiments, it is clear to those skilled in the art that the present application can be implemented by software plus necessary general hardware platform. Based on such understanding, the technical solutions of the present application may be essentially or partially implemented in the form of a software product, which may be stored in a storage medium, such as a ROM/RAM, a magnetic disk, an optical disk, etc., and includes several instructions for enabling a computer device (which may be a personal computer, a server, or a network device, etc.) to execute the method according to the embodiments or some parts of the embodiments of the present application.

Finally, it should be noted that: although the present invention has been described in detail with reference to the above embodiments, it should be understood by those skilled in the art that: modifications and equivalents may be made thereto without departing from the spirit and scope of the invention and it is intended to cover in the claims the invention as defined in the appended claims.

Claims

1. A method for detecting an exception of a database overflow page, the method comprising:

an acquisition step, acquiring a database overflow page unit: traversing the B + tree leaf page, and reading data records stored in a cross-page manner;

a detection step, namely reading out a node of a linked list of a database overflow page unit, reading a page number RN of a next node from the node, acquiring the total length TL of the linked list of the database overflow page unit, calculating the length RL from a linked list head of the database overflow page unit to a current node, and detecting whether the database overflow page unit is abnormal or not according to the RN, the TL and the RL;

the detecting whether the database overflow page unit is abnormal according to the RN, the TL, and the RL specifically includes: judging whether the RN is larger than the maximum page number MaxN of the database, if so, judging that the data of the node corresponding to the RN is abnormal data, if not, judging whether RL < TL > and RL < 0 are met or RN ≠ 0 and RL is larger than or equal to TL, if so, judging that the data of the node corresponding to the RN is abnormal data, if not, counting the times N of the node of the RN at the B + tree and the idle page linked list node, if N is larger than or equal to 2, judging that the data of the node corresponding to the RN is abnormal data, and if not, indicating that the data of the overflow page of the database is normal.

2. The method of claim 1, further comprising:

and recording error information of the abnormal database overflow page, wherein the error information comprises a page number, a unit starting address, a next unit starting address of a first node of a linked list of a database overflow page unit, and a page number and a node sequence number of a node of the abnormal database overflow page.

3. The method according to claim 2, wherein the database is an SQLite database, the database comprises a plurality of SQLite pages, the SQLite pages are managed by adopting a B + tree, and the SQLite pages storing data in the B + tree are called leaf pages.

4. The method according to claim 3, wherein the operation of the acquiring step is specifically:

reading a leaf page from the B + tree, searching a data record, if the search result is empty, indicating that the current page is searched, continuing to search the next page, and if the search result is not empty, recording the offset of the data record in the leaf page as r _ offset; judging whether r _ size is less than or equal to usable _ size-r _ offset, if so, indicating that the data record is not the data record in the database overflow page unit, then continuously inquiring the next data record, and if not, indicating that the data record is the data record in the database overflow page unit; wherein r _ size is the size of the data record, and usable _ size is the available size of the SQLite page.

5. An apparatus for detecting an exception to an overflow page in a database, the apparatus comprising:

an obtaining unit, configured to obtain a database overflow page unit: traversing the B + tree leaf page, and reading data records stored in a cross-page manner;

the detection unit is used for reading out one node of a linked list of the database overflow page unit, reading the page number RN of the next node from the node, acquiring the total length TL of the linked list of the database overflow page unit, calculating the length RL from the head of the linked list of the database overflow page unit to the current node, and detecting whether the database overflow page unit is abnormal or not according to the RN, the TL and the RL; the detecting whether the database overflow page unit is abnormal according to the RN, the TL, and the RL specifically includes: judging whether the RN is larger than the maximum page number MaxN of the database, if so, judging that the data of the node corresponding to the RN is abnormal data, if not, judging whether RL < TL > and RL < 0 are met or RN ≠ 0 and RL is larger than or equal to TL, if so, judging that the data of the node corresponding to the RN is abnormal data, if not, counting the times N of the node of the RN at the B + tree and the idle page linked list node, if N is larger than or equal to 2, judging that the data of the node corresponding to the RN is abnormal data, and if not, indicating that the data of the overflow page of the database is normal.

6. The apparatus of claim 5, further comprising:

and the recording unit is used for recording error information of the abnormal database overflow page, and the error information comprises the page number of the first node of the linked list of the database overflow page unit, the unit starting address, the next unit starting address, the page number of the node with the abnormal database overflow page and the node sequence number.

7. The device according to claim 6, wherein the database is an SQLite database, the database comprises a plurality of SQLite pages, the SQLite pages are managed by adopting a B + tree, and the SQLite pages storing data in the B + tree are called leaf pages.

8. The apparatus according to claim 7, wherein the obtaining unit performs operations specifically as:

9. A computer-readable storage medium, characterized in that the storage medium has stored thereon computer program code which, when executed by a computer, performs the method of any of claims 1-4.