Re: [sqlite] Corrupted database: On tree page 76852 cell 303: Rowid 18741471 out of order
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
Re: [sqlite] Corrupted database: On tree page 76852 cell 303: Rowid 18741471 out of order
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 ? Thanks very much for taking the time to respond. I'll look into what you suggested. >> 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." ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Corrupted database: On tree page 76852 cell 303: Rowid 18741471 out of order
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
Re: [sqlite] Suitability for Macintosh OS 9.2?
On Nov 19, 2018, at 07:58, Charles Hudson wrote: > I come from an ANSI SQL client / server background (Oracle, MS SQL) but am > interested in finding a SQL database to install on an old Macintosh G3 Power > PC that is running OS 9.2. I don't need network connectivity as this would > be limited to a single machine. > > I am pursuing this mostly out of curiosity; a learning experience for > investigating the capabilities of the Mac. > > Rather than sign up for your mailing list I thought I might ask this one > question: Which, if any, versions of SQLite might be suitable for this > task? You would probably have an easier time getting SQLite working on that hardware by installing a UNIX-like operating system. Classic Mac OS (Mac OS 9 and earlier) are not related to UNIX at all and SQLite is not designed for it, but Mac OS X (which was subsequently renamed to OS X and now macOS) is a BSD-derived UNIX operating system, and an old version of Mac OS X could be installed on your G3. Which version depends on which model of G3 you have. Blue & white Power Macintosh G3s can run up to Mac OS X 10.4 Tiger. Beige Power Macintosh G3s may need a RAM upgrade but can run up to Mac OS X 10.2 Jaguar officially, and 10.4 can be installed with XPostFacto. Those versions of Mac OS X already come with an old version of SQLite; you may be able to compile a newer version if needed. If you're running at least Mac OS X 10.4, you can use MacPorts to install a newer SQLite for you. (Mac OS X 10.5 and later do not run on PowerPC G3 processors.) You can keep your Mac OS 9 installation if you want, either on the same partition or on a separate partition or separate disk. You can run your Mac OS 9 programs within Mac OS X by using the "Classic" application, or you can reboot into Mac OS 9. Alternately, you might be able to install another UNIX-like operating system, such as a Linux distribution or one of the other BSD variants. Installing Mac OS X or any other UNIX-like system would also make it more likely that you could install other common SQL databases like MySQL/MariaDB and PostgreSQL. Or if you just need a database and don't need it to be SQL, FileMaker Pro is a database system that was available for Mac OS 9 and is still available for Mac OS X. Even HyperCard for Mac OS 9 and earlier can be used as a simple database and was pretty fun. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 3.7.10 build failure on OS X 10.4: ld: Undefined symbols: _OSAtomicCompareAndSwapPtrBarrier
On Jan 21, 2012, at 06:21, Richard Hipp wrote: > On Sat, Jan 21, 2012 at 3:29 AM, Ryan Schmidt wrote: > >> Hello, I'm writing on behalf of the MacPorts package management system. >> Our users are having trouble compiling sqlite 3.7.10 on OS X 10.4, both on >> PowerPC and on Intel. 3.7.9 compiled fine. The error is: >> >> ld: Undefined symbols: >> _OSAtomicCompareAndSwapPtrBarrier >> >> The complete log is attached to our bug report: >> >> https://trac.macports.org/ticket/32930 >> >> Thanks for any help you can provide. >> > > I think that if you add this patch: > >http://www.sqlite.org/src/info/238e35a441 > > and if you compile with -DSQLITE_WITHOUT_ZONEMALLOC that it might work. > Please give it a try and let me know one way or another. Thank you, that worked for me. https://trac.macports.org/changeset/89249 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] 3.7.10 build failure on OS X 10.4: ld: Undefined symbols: _OSAtomicCompareAndSwapPtrBarrier
Hello, I'm writing on behalf of the MacPorts package management system. Our users are having trouble compiling sqlite 3.7.10 on OS X 10.4, both on PowerPC and on Intel. 3.7.9 compiled fine. The error is: ld: Undefined symbols: _OSAtomicCompareAndSwapPtrBarrier The complete log is attached to our bug report: https://trac.macports.org/ticket/32930 Thanks for any help you can provide. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
