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! _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
