On Dec 4, 2018, at 22:42, Simon Slavin wrote:
> On 5 Dec 2018, at 3:20am, Ryan Schmidt wrote: > >> $ 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. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
