Re: [sqlite] Mac: Users receive "database disk image is malformed" errors after restoring database from Time Machine backup
> Richard Hipp wrote: > Do you know if the corruption is occurring when TimeMachine makes its > backup, or is occurring when the backed up database is restored? Can > you capture some unrestored TimeMachine backups to see if they are > corrupt? > > Can you send us one of your corrupted database files for analysis? Unfortunately, I do not know when exactly the corruption occurs. I will send you an example of a corrupted database in a separate email. > Olivier Mascia wrote: > b) Maybe using WAL is not really useful for your use-case, if really there > is mostly one very short write transaction per minute. The default > journal mode might be perfectly adequate. But surely you chose WAL mode > for some specific reason. I just don't instantly spot which one from your > report. The reasoning for using WAL is actually to reduce the number of changes to the main database. Given that our main DB can be ~100 MB in size, having a WAL reduces the number of times the main DB needs to change, which would cause a new copy to be made in the Time Machine backup, increasing its size. That's also why we allow the WAL to grow up to ~8 MB. > My best guess here is that TimeMachine somehow captures the sqlite DB > files a few milliseconds apart "sometimes" so that a journal file that > has just been committed is in the TimeMachine backup captured still in > its uncommitted state while the DB itself is in the committed state > already (or perhaps vice-versa). A corruption due to a race condition between updating the main DB and WAL _should_ be unlikely: We only do one save per minute, and the WAL only gets checkpointed into the main DB every few hours. So Time Machine would need to pass over those two files exactly when a checkpointing operation takes place, which is fairly unlikely. > Scott Perry wrote: > Out of curiosity, why aren't you using the SQLite that comes with the OS? Using a newer version of SQLite should give us slightly better performance, especially on older macOS versions (e.g. 10.11). Also, it might have been easier to link our Swift app with a custom copy of the library than with the OS-provided one, but I'm not sure about that. -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Mac: Users receive "database disk image is malformed" errors after restoring database from Time Machine backup
Hi, For the past half year we’ve been receiving reports from users who had restored their SQLite-based databases from a Time Machine backup. Afterwards, they would receive "database disk image is malformed” errors. The app also backs up the user’s data “manually” to a ZIP file every week; those backups seem to be working fine. We also haven’t received reports from other backup tools causing issues. I have also suspected a bug in Time Machine, but it is striking that the issues did seem to start occurring after an update to the app (luckily, in fact, with the same update that also introduced the “manual” backups). Changes that we made to our setup in the update that coincided with the errors occurring: - Upgraded SQLite from 3.21 to 3.24 (we have since reverted to 3.23.1 in another update; no improvement). - Used memory mapping for read accesses via “PRAGMA mmap_size = 1073741824;” (we have since reverted to “PRAGMA mmap_size = 0;” after reading http://sqlite.1065341.n5.nabble.com/Re-Database-corruption-and-PRAGMA-fullfsync-on-macOS-td95366.html <http://sqlite.1065341.n5.nabble.com/Re-Database-corruption-and-PRAGMA-fullfsync-on-macOS-td95366.html>; no improvement). - Using a secondary database via [ATTACH DATABASE](https://www.sqlite.org/lang_attach.html <https://www.sqlite.org/lang_attach.html>) (although this also seems to occur for users without such a database). At this point, I am at a loss, especially given that SQLite should be fairly robust against database corruption. While our app is running in the background all the time, it is not very write-heavy (~ one transaction per minute taking just a few milliseconds). Also, the app had been running fine before the update for a long time without any reports of this issue. I might be doing something wrong or have changed anything else, but I don’t know what; if you have any ideas, let me know. Any suggestions on what could be the culprit or what else I could try besides downgrading all the way to SQLite 3.21 would be appreciated. Thanks, Daniel Alm P.S.: Our database currently uses the following PRAGMAs: PRAGMA mmap_size = 0; PRAGMA page_size = 4096; PRAGMA cache_size = -10240; PRAGMA foreign_keys = ON; PRAGMA journal_size_limit = 8388608; PRAGMA checkpoint_fullfsync = 1; PRAGMA wal_autocheckpoint = 2048; PRAGMA journal_mode = WAL; Happy to provide any more details as needed. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compile 64bit version of SQLIte on Solaris?
use -m64 flag to compile and link > I just tried to see what ELF class is in after running the following > (added > -D_FILE_OFFSET_BITS=64): > > /usr/local/bin/gcc -D_FILE_OFFSET_BITS=64 -c -fPIC -DHAVE_USLEEP > sqlite3.c > > # file libsqlite3.so grep ELF > libsqlite3.so: ELF 32-bit LSB dynamic lib 80386 Version 1 [FPU], > dynamically linked, not stripped, no debugging information available > > How can I get the ELF class to reflect "ELF 64-bit"? > > Any ideas? > > Lynton > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Lynton Grice > Sent: 22 November 2010 09:33 AM > To: 'General Discussion of SQLite Database' > Subject: Re: [sqlite] Compile 64bit version of SQLIte on Solaris? > > Hi there, > > The reason I ask is that I have created a C shared lib using SQLite > functions etc and the custom shared lib I have created needs to be ELF > class > 64. > > At the moment I am getting the ELF class issues: > wrong ELF class: ELFCLASS32 > > That is why I am hoping I can compile the main SQLite shared lib in ELF > class 64.. > > This possible? > > Thanks > > Lynton > > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Lynton Grice > Sent: 22 November 2010 09:28 AM > To: sqlite-users@sqlite.org > Subject: [sqlite] Compile 64bit version of SQLIte on Solaris? > > Hi there, > > > > I am getting the following error when trying to compile SQLite on a > Solaris > 64 bit machine: > > > > /usr/local/bin/gcc -m64 -R/usr/sfw/lib/64 -c -fPIC -DHAVE_USLEEP > sqlite3.c > > sqlite3.c:1: sorry, unimplemented: 64-bit mode not compiled in > > > > Can someone please let me know how I can do this so I can use SQLite on a > 64 > bit Solaris machine? > > > > Thanks > > > > Lynton > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
