[sqlite] Corrupted database: On tree page 76852 cell 303: Rowid 18741471 out of order

[Ryan Schmidt]

Hello,

I have a corrupt SQLite database about which I'd appreciate your advice. The data is not critical but I'd like to fix it if it's possible and not too time-consuming. Even just knowing why the problem occurred or how to prevent it in the future would be helpful. If there's something the application should be doing differently in its use of the SQLite library to ensure the database doesn't get corrupted that would be good to know.

The application that is using the SQLite library (MacPorts) is experiencing this error:

sqlite error: library routine called out of sequence (21)

I ran an integrity check on the database, and the output began like this:

$ sqlite3 /opt/local/var/macports/registry/registry.db
SQLite version 3.25.2 2018-09-25 19:08:10
Enter ".help" for usage hints.
sqlite> .load /tmp/macports.sqlext
sqlite> pragma integrity_check;
*** in database main ***
On tree page 76852 cell 303: Rowid 18741471 out of order
On tree page 76852 cell 301: Rowid 18741430 out of order
On tree page 76852 cell 299: Rowid 18741387 out of order
On tree page 76852 cell 296: Rowid 18741324 out of order

Many similar lines follow. The full output is here:

https://trac.macports.org/ticket/57570

This was on macOS Sierra 10.12.6 on a Mac OS Extended (case-sensitive, journaled) filesystem.

macports.sqlext provides a custom collation for the version number column.

Some background: I run the MacPorts build farm. MacPorts uses SQLite to store its "registry", which keeps track of what ports MacPorts has installed and what files each port provides. In the build farm we keep the latest version of each port installed, which amounts to about 17,000 ports providing over 3 million total files, so the registry gets rather large, about 1.5GB, and operations that change the registry take a little time to complete.

Recently, I manually uninstalled a few ports from one of the builders. That command would have rewritten the registry to remove the entries for those ports. MacPorts also occasionally vacuums the registry, when it determines that doing so would be worthwhile, but it doesn't print a message if it does so, so it's possible that was happening but I'm not certain. While MacPorts was dealing with the registry, the VMware virtual machine the builder runs on froze and had to be manually powered off. Upon restarting the VM, registry operations began failing with the above error.

I've been running this build farm for two years, currently with 11 different builders, and I haven't seen this or any other registry corruption there before. The VMware host servers have ECC memory and the builders' virtual disks are stored on SSDs.

I've seen the document "How To Corrupt An SQLite Database File". There certainly are a lot of possibilities.

Does this particular failure stand out to anyone as an obvious example of a particular problem, ideally with a particular solution? If not, I can nuke the MacPorts installation and its SQLite registry and start over.

Thanks!

[Simon Slavin]

Post by Ryan Schmidt
$ sqlite3 /opt/local/var/macports/registry/registry.db
SQLite version 3.25.2 2018-09-25 19:08:10
Enter ".help" for usage hints.
sqlite> .load /tmp/macports.sqlext
sqlite> pragma integrity_check;
*** in database main ***
On tree page 76852 cell 303: Rowid 18741471 out of order
On tree page 76852 cell 301: Rowid 18741430 out of order
On tree page 76852 cell 299: Rowid 18741387 out of order
On tree page 76852 cell 296: Rowid 18741324 out of order

It is possible that the rows reported as missing are actually deleted rows, and that tree page 76852 just hasn't been moved from "part of TABLE ports" to "freed pages".

Try using ".dump" to dump that database to a text file. Can you read it and tell whether anything is missing ? You can either read the SQL commands by eye, or use command-line-tool to ".read" the .sql file to create another database, and use database tools to interrogate that one.

Does your database have any relations, either explicitly declared using FOREIGN KEY or implicit in how your software handles the data ? Presumably every row in TABLE file should be part of a row in TABLE port, or something like that. Can you use your understanding of the relation to prove that certain rows are missing from certain tables ? Does it give you any idea how much data is missing ?

Post by Ryan Schmidt
the VMware virtual machine the builder runs on froze and had to be manually powered off. Upon restarting the VM, registry operations began failing with the above error.

I'm 95% certain that your VMware software was caching changes made to the database file rather than flushing them to disk when the software told it to. Under default configuration they all do this, and this does not mean that VMWare is worse than any competing product.

In other words, the cause of corruption was that you were using a virtual machine and not real hardware, and that the virtual machine violates ACID for speed. It's possible that WMware has settings which correct this problem. If it does, using them will, of course, slow it down.

Simon.

[Ryan Schmidt]

Post by Simon Slavin

Post by Ryan Schmidt
$ sqlite3 /opt/local/var/macports/registry/registry.db
SQLite version 3.25.2 2018-09-25 19:08:10
Enter ".help" for usage hints.
sqlite> .load /tmp/macports.sqlext
sqlite> pragma integrity_check;
*** in database main ***
On tree page 76852 cell 303: Rowid 18741471 out of order
On tree page 76852 cell 301: Rowid 18741430 out of order
On tree page 76852 cell 299: Rowid 18741387 out of order
On tree page 76852 cell 296: Rowid 18741324 out of order

It is possible that the rows reported as missing are actually deleted rows, and that tree page 76852 just hasn't been moved from "part of TABLE ports" to "freed pages".
Try using ".dump" to dump that database to a text file. Can you read it and tell whether anything is missing ? You can either read the SQL commands by eye, or use command-line-tool to ".read" the .sql file to create another database, and use database tools to interrogate that one.
Does your database have any relations, either explicitly declared using FOREIGN KEY or implicit in how your software handles the data ? Presumably every row in TABLE file should be part of a row in TABLE port, or something like that. Can you use your understanding of the relation to prove that certain rows are missing from certain tables ? Does it give you any idea how much data is missing ?

Thanks very much for taking the time to respond. I'll look into what you suggested.

Post by Simon Slavin

Post by Ryan Schmidt
the VMware virtual machine the builder runs on froze and had to be manually powered off. Upon restarting the VM, registry operations began failing with the above error.

I'm 95% certain that your VMware software was caching changes made to the database file rather than flushing them to disk when the software told it to. Under default configuration they all do this, and this does not mean that VMWare is worse than any competing product.
In other words, the cause of corruption was that you were using a virtual machine and not real hardware, and that the virtual machine violates ACID for speed. It's possible that WMware has settings which correct this problem. If it does, using them will, of course, slow it down.

That occurred to me as well. But from what I can tell initially, writes should not be cached:

https://kb.vmware.com/s/article/1008542

"VMware ESX acknowledges a write or read to a guest operating system only after that write or read is acknowledged by the hardware controller to ESX. Applications running inside virtual machines on ESX are afforded the same crash consistency guarantees as applications running on physical machines or physical disk controllers."

[Simon Slavin]

Post by Ryan Schmidt
https://kb.vmware.com/s/article/1008542
"VMware ESX acknowledges a write or read to a guest operating system only after that write or read is acknowledged by the hardware controller to ESX. Applications running inside virtual machines on ESX are afforded the same crash consistency guarantees as applications running on physical machines or physical disk controllers."

Interesting. That paragraph is a well-written piece of text explaining the opposite of what I thought. Maybe things have changed in the past decade.

I suppose the lie "Your changes have been made on physical hardware, you can now proceed." may be somewhere else in your system (e.g. the hardware controller). Or maybe I'm barking up the wrong tree.

Simon.

[James K. Lowden]

On Wed, 5 Dec 2018 05:21:30 +0000

Post by Simon Slavin

Post by Ryan Schmidt
https://kb.vmware.com/s/article/1008542
"VMware ESX acknowledges a write or read to a guest operating
system only after that write or read is acknowledged by the
hardware controller to ESX. Applications running inside virtual
machines on ESX are afforded the same crash consistency guarantees
as applications running on physical machines or physical disk
controllers."

Interesting. That paragraph is a well-written piece of text
explaining the opposite of what I thought. Maybe things have changed
in the past decade.

VMware may well be doing the best it can on unreliable hardware. I
believe it's common knowledge that consumer-grade hard drives lie when
acknowledging writes: the acknowlegement is sent when the data are
received into the device's write buffer, not after being written to
disk. It's good for benchmarks. No one benchmarks data corruptions.

'Twas ever thus: If you want a reliable database, use a reliable disk.

--jkl

[Ryan Schmidt]

Post by Simon Slavin

Post by Ryan Schmidt
$ sqlite3 /opt/local/var/macports/registry/registry.db
SQLite version 3.25.2 2018-09-25 19:08:10
Enter ".help" for usage hints.
sqlite> .load /tmp/macports.sqlext
sqlite> pragma integrity_check;
*** in database main ***
On tree page 76852 cell 303: Rowid 18741471 out of order
On tree page 76852 cell 301: Rowid 18741430 out of order
On tree page 76852 cell 299: Rowid 18741387 out of order
On tree page 76852 cell 296: Rowid 18741324 out of order

It is possible that the rows reported as missing are actually deleted rows, and that tree page 76852 just hasn't been moved from "part of TABLE ports" to "freed pages".
Try using ".dump" to dump that database to a text file. Can you read it and tell whether anything is missing ? You can either read the SQL commands by eye, or use command-line-tool to ".read" the .sql file to create another database, and use database tools to interrogate that one.
Does your database have any relations, either explicitly declared using FOREIGN KEY or implicit in how your software handles the data ? Presumably every row in TABLE file should be part of a row in TABLE port, or something like that. Can you use your understanding of the relation to prove that certain rows are missing from certain tables ? Does it give you any idea how much data is missing ?

I was able to .dump the data from the corrupt database and .read it into a new database (1.4GB, slightly smaller than the original 1.5GB database, which could be plausible if the original had not been vacuumed?). pragma integrity_check then found no problems in the new database.

The tables do have some very rudimentary FOREIGN KEY relations. The "ports" table has an "id" INTEGER primary key, and the "files" table and a couple others have an "id" column that references it.

Verifying the correctness of the data just by looking at it seemed infeasible, so, feeling adventurous, I put the new database in place and let MacPorts build a few things, but problems soon became apparent. The "files" table which records the association of files with a port has over 31,000 files associated with a port two or three times each -- duplicate entries. MacPorts didn't expect this condition to exist and didn't react well to it. This table does not have any unique constraints. We probably should have had a unique constraint over the combination of id and path to prevent this condition from existing. After the corruption first occurred, several automated builds went by, activating and deactivating various ports, which would have affected the registry, before I noticed the problem and stopped the automated builds. It's possible that because MacPorts was not able to access the registry properly, it got the wrong idea about what to do, and created those duplicate entries.

Since I don't know what else may have gone wrong with the contents of the registry by this point, it seems safer to erase the MacPorts installation and start fresh. This will take a bit longer as every port has to be re-fetched and re-installed but at least I'll have confidence in the integrity of the registry.

[Simon Slavin]

Post by Ryan Schmidt
Since I don't know what else may have gone wrong with the contents of the registry by this point, it seems safer to erase the MacPorts installation and start fresh. This will take a bit longer as every port has to be re-fetched and re-installed but at least I'll have confidence in the integrity of the registry.

I agree with your diagnosis. Your software has used the corrupted database and made incorrect changes to it because it trusted corrupt information. Unfortunate. Since you do have the ability to build a new dataset from scratch, I think that's the best way.

I still have no better idea of the cause of the corruption. I'd imagine you're still considering what you found in "howtocorrupt".

Simon.

[R Smith]

Post by Ryan Schmidt
Hello,
I have a corrupt SQLite database about which I'd appreciate your advice. The data is not critical but I'd like to fix it if it's possible and not too time-consuming. Even just knowing why the problem occurred or how to prevent it in the future would be helpful. If there's something the application should be doing differently in its use of the SQLite library to ensure the database doesn't get corrupted that would be good to know.
The application that is using the SQLite library (MacPorts) is experiencing this error://....

Hi Ryan, the question has two parts:
1 - Can it be fixed?
Very probably - by simply dumping it to text and re-importing again. It
may need a scan by eye to make sure there are no serious broken text,
but usually the Index errors as per your error dump will not be too
complicated and easily/automatically corrected.
See the .dump command in the sqlite CLI documentation:
https://sqlite.org/cli.html#converting_an_entire_database_to_an_ascii_text_file


2 - How did it happen?
Hard to say, almost certainly a write that somehow didn't make it to the
physical layer.
Here is a list of why that can happen (which you may have already
encountered in your research):
https://sqlite.org/howtocorrupt.html


Good luck!
Ryan