https://www.percona.com/blog/wp-content/uploads/2022/06/Screenshot-2022-06-27-at-7.01.31-PM-300×166.png
Though I am writing this post being a PostgreSQL DBA, this page can be read by anyone because the concept of corruption is the same in every database.
After reading this blog post, one should understand what database corruption is and how it may occur.
Being DBAs, corruption is something we do not want to ever see in our system; however, there are a number of systems where corruption revisits very frequently. Whenever it occurs in big databases, it becomes challenging for us to detect and repair it as we may see no sign of it. In my 15 years of experience as a DBA, I saw corruption as the toughest nut to crack because ostensible reasons for any corruption are not actually predictable. In other words, we may not know the actual cause of the issue; hence, it is quite difficult to get the RCA.
In this series of blogs, I am going to cover various types of corruption and methods to find and fix them.
This blog post will throw some light on the basics of database corruption.
Concept and analogy
To explain it in a simple manner, I will take an example of the Spanish language. There is text in the image below.
Here, the above text is in Spanish. For anyone who does not understand Spanish, is it possible for them to read it? The straightforward answer is “No”.
Anyone would ask “How may a person without having knowledge of Spanish read it?”. To end the curiosity, the image reads “Goodbye” in English.
The same thing happens with software products as well. All software is programmed to read and write in its own pre-defined format, and it may not be able to do so in any other format that is supported by any other software product. For example, Python code can not be compiled or read, or executed, in C, Java, or Perl.
In the case of databases, it is about the format of data being stored on a disk. When a database product, such as PostgreSQL, MySQL, or MongoDB, writes on a disk, it performs the operation by using some format.
While reading from a disk, the database product expects the same format there; any portion of data on disk that is not in an appropriate format is CORRUPTION.
To summarize this, corruption is nothing but an improper format or a sequence of data.
How does it happen?
As mentioned in the last section, corruption is a result of an unreadable data format. As we know, data is stored in the form of bits on a disk. Now, in the case of integer or numeric, the conversion is simple. But for characters, every machine is designed to convert data in the form of bytes, which is a group of eight bits, in such a way that every byte represents a character. There are 256 different combinations of every byte, from 00000000(0) to 11111111(255).
To read bytes in the form of characters, some formats are designed, such as ASCII, EBCDIC, BCD, and so on. They are also known as encoding schemes. Out of all these schemes, ASCII (American Standard Code for Information Interchange) is more popular. In this format, every byte (all 256 combinations) is assigned a particular character.
Like,
01000001(65) – A
00101100(44) – ,
Below is the link to view all the ASCII codes.
https://www.rapidtables.com/code/text/ascii-table.html
Here, if any byte is stored with an unexpected sequence of bits, the machine will read a different character.
For example,
Let’s say character A(65) is stored as 11000001(193) instead of 01000001(65), which is “Á“(not the English letter A).
Now, in these mentioned encoding schemes, some characters are human-readable and the rest are not. But, another point to note is that all the software products are not built to decipher all the characters. So, in any case, if a byte at any position gets changed, it is possible that the database may not be able to read the character, and hence data. Those unreadable or non-parsable data are deemed as corrupted data.
For example,
In case How are you? is stored as How are you¿, character “¿” is not available in English, hence those character sets that can only parse English may not be able to recognize that character. So, it will not be able to read that text and throws an error by marking it unreadable. Here, only one character is unrecognizable, but the whole text will be marked as corrupted data.
Causes
It is truly mysterious because we may never know the actual reason for any kind of corruption. As I mentioned above, the corruption is attributed to changes of bits/bytes, but it is really difficult to make it certain what process/thread leads to that change. This is why any test case related to corruption is not actually reproducible. The only thing we can do is explore possible causes.
Some of the possible causes are as below.
Hardware issue:
When RAID disks are not designed properly or controllers are faulty, it may not be able to write data correctly on disks. In non-RAID disks, mechanical devices should work properly because bits are not stored properly due to faulty disks as well.
Corruption may also occur due to heavy I/Os.
Bug in the operating system:
On occasions, due to a buggy kernel or code, OS encodes data wrongly, and it is later written to the disk. On occasions, OS produces corrupted data while it is inefficient to stem I/Os.
Bug in the database product:
In many cases, the product itself sometimes stores wrong data on the disk, or due to inefficient algorithms, it saves data in the wrong format.
Types of corruption
Every database comprises different types of files, such as data files, WAL files, commit logs, and so on. These files contain data for various database objects e.g. tables, indexes, materialized views, WAL records, etc. When these database files go corrupt, some queries retrieve wrong data or return errors, or some operations(e.g. recovery, replay) may not work as expected. As a DBA, one needs to identify what particular objects are affected due to that corruption. For ease of understanding, corruption is categorized into different types; some of them are as below.
Index corruption:
In general, an index keeps a pointer(s) for a particular value(or a value set) in a column. Whenever an index is asked to return pointers (ctid in PostgreSQL, rowid in Oracle), it fetches those pointers and returns them to the requestor.
In the case of corruption, a wrong pointer to any value is saved on the disk due to faulty bits on the disk. Consequently, it returns a wrong record.
Data corruption:
When data/toast pages store faulty data(in terms of format), it may become unrecognizable while reading the same data. Hence, they get errored out by the database.
WAL corruption:
WAL/Redo/Transaction log files store data in a particular format, and while reading them, WAL entries are parsed and applied. In the case of WAL corruption, WAL entries are not parsable, which affects the WAL reply operation.
Page header corruption:
The lowest unit of storage in databases is block/page, which actually stores the actual records. To maintain data integrity, some information is stored in a separate section that is called the page header. Any improper information in a page header is header corruption. This affects the data integrity.
Summary
Corruption results from changes in bits/bytes while storing data on the disk. When a database product (e.g. MySQL, PostgreSQL) does not get the data in an expected format, it is corruption.
The data in the database may get corrupted due to various reasons, such as faulty hardware and buggy OS/kernel/database products. Owing to this, the data is accidentally changed before it is stored on the disk. While it is wrongly stored on a disk, and hence files, it affects various functions of the software product; to easily understand what particular areas are affected, the corruption is classified into various types, such as index corruption, data corruption, and so on.
This is the first post in the series of database corruption blogs; other blogs will be posted soon. Stay tuned!
Percona Database Performance Blog