Mac: Users receive "database disk image is malformed" errors after restoring database from Time Machine backup

classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

Mac: Users receive "database disk image is malformed" errors after restoring database from Time Machine backup

Daniel Alm
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Mac: Users receive "database disk image is malformed" errors after restoring database from Time Machine backup

Richard Hipp-3
On 12/11/18, Daniel Alm <[hidden email]> wrote:
> 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.
>

Nothing about SQLite has changed that should make a difference here.

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?

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Mac: Users receive "database disk image is malformed" errors after restoring database from Time Machine backup

Olivier Mascia
In reply to this post by Daniel Alm
Dear Daniel,

I'm extracting two points out of your report:

> Le 11 déc. 2018 à 13:01, Daniel Alm <[hidden email]> a écrit :
>
> 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).
> PRAGMA journal_mode = WAL;

When TimeMachine makes copies of the files, the database file and -wal file will be copied at different points in time, albeit they should be copied from a filesystem snapshot, so should be consistent to each other. But this should be checked, and verified for different macOS versions.

a) If only the database file is restored (and not its -wal file) or if both files are restored (but were not in synch when backed up) this might, rightly, trigger the database is malformed message.

As said on page https://www.sqlite.org/wal.html:

"The WAL file is part of the persistent state of the database and should be kept with the database if the database is copied or moved. If a database file is separated from its WAL file, then transactions that were previously committed to the database might be lost, or the database file might become corrupted. The only safe way to remove a WAL file is to open the database file using one of the sqlite3_open() interfaces then immediately close the database using sqlite3_close()."


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.


--
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Mac: Users receive "database disk image is malformed" errors after restoring database from Time Machine backup

R Smith-2
In reply to this post by Richard Hipp-3

On 2018/12/12 4:48 PM, Richard Hipp wrote:

> On 12/11/18, Daniel Alm <[hidden email]> wrote:
>> 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.
>>
> Nothing about SQLite has changed that should make a difference here.
>
> 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?

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).

This theory however makes no sense unless either the journal mode in
SQLite has changed (either via update or pragma change by user), or
TimeMachine itself has changed its ways since when things used to work
correctly.



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Mac: Users receive "database disk image is malformed" errors after restoring database from Time Machine backup

Rob Willett
Also are they using Time Machine on a networked drive?

Whilst Time Machine was not supposed to work across networks, people
have made it work using 3rd party software. I know because we tried it
for a laugh and abandoned it (and Time Machine) as it was wholly
unreliable.

However I think the WAL commit (or uncommit) is a more likely scenario.

Rob

On 12 Dec 2018, at 15:00, R Smith wrote:

> On 2018/12/12 4:48 PM, Richard Hipp wrote:
>> On 12/11/18, Daniel Alm <[hidden email]> wrote:
>>> 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.
>>>
>> Nothing about SQLite has changed that should make a difference here.
>>
>> 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?
>
> 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).
>
> This theory however makes no sense unless either the journal mode in
> SQLite has changed (either via update or pragma change by user), or
> TimeMachine itself has changed its ways since when things used to work
> correctly.
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Mac: Users receive "database disk image is malformed" errors after restoring database from Time Machine backup

Keith Medcalf
In reply to this post by Daniel Alm

I know nothing about "Time Machine", but does it copy the entire filesystem in (at least) "crash consistent" state?  

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Daniel Alm
>Sent: Tuesday, 11 December, 2018 05:02
>To: [hidden email]
>Subject: [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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Mac: Users receive "database disk image is malformed" errors after restoring database from Time Machine backup

Peter da Silva-2
Apple uses Sqlite in a number of applications, including Apple Mail, so
they have to have some kind of accommodation for saving sqlite databases.

The Time Machine patent does not describe using file system snapshots:


*"An algorithm or other monitoring can be used to detect changes that occur
during the backup operation in order to maintain consistency between
related data in the backup. The back up can be performed again for related
data that was modified during prior backup operation. *

*"In general, in one aspect, a method is provided. A backup operation of
data including a plurality of related items is initiated. Modifications to
one or more items of the plurality of related items are monitored for
during the backup operation. The backup operation is completed. If a
modification occurred to one or more items, a second backup operation is
performed for the modified items."*

This does not seem to authoritatively state that multiple files will be
backed up consistently.

On Wed, Dec 12, 2018 at 9:06 AM Keith Medcalf <[hidden email]> wrote:

>
> I know nothing about "Time Machine", but does it copy the entire
> filesystem in (at least) "crash consistent" state?
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
> >-----Original Message-----
> >From: sqlite-users [mailto:sqlite-users-
> >[hidden email]] On Behalf Of Daniel Alm
> >Sent: Tuesday, 11 December, 2018 05:02
> >To: [hidden email]
> >Subject: [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
> >[hidden email]
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Mac: Users receive "database disk image is malformed" errors after restoring database from Time Machine backup

Rob Willett
Whilst Time Machine does not do snapshots how enterprise storage do
snapshots, literally a freeze and recovery point. Time Machine does make
backups suitable for booting from. Apple considers Time Machine suitable
for home use backups.

You can backup with TimeMachine and boot from it. My personal experience
of TM is that its flaky and unreliable and we use Carbon Copy Cloner AND
Chronosynd to get to backups that I trust to work. YMMV

However we are going off the point from the OP. I personally think TM is
to blame and would advice the users to check the TM setup and experiment
with delaying the WAL backup just to see what would happen.

Rob

On 12 Dec 2018, at 16:58, Peter da Silva wrote:

> Apple uses Sqlite in a number of applications, including Apple Mail,
> so
> they have to have some kind of accommodation for saving sqlite
> databases.
>
> The Time Machine patent does not describe using file system snapshots:
>
>
> *"An algorithm or other monitoring can be used to detect changes that
> occur
> during the backup operation in order to maintain consistency between
> related data in the backup. The back up can be performed again for
> related
> data that was modified during prior backup operation. *
>
> *"In general, in one aspect, a method is provided. A backup operation
> of
> data including a plurality of related items is initiated.
> Modifications to
> one or more items of the plurality of related items are monitored for
> during the backup operation. The backup operation is completed. If a
> modification occurred to one or more items, a second backup operation
> is
> performed for the modified items."*
>
> This does not seem to authoritatively state that multiple files will
> be
> backed up consistently.
>
> On Wed, Dec 12, 2018 at 9:06 AM Keith Medcalf <[hidden email]>
> wrote:
>
>>
>> I know nothing about "Time Machine", but does it copy the entire
>> filesystem in (at least) "crash consistent" state?
>>
>> ---
>> The fact that there's a Highway to Hell but only a Stairway to Heaven
>> says
>> a lot about anticipated traffic volume.
>>
>>
>>> -----Original Message-----
>>> From: sqlite-users [mailto:sqlite-users-
>>> [hidden email]] On Behalf Of Daniel Alm
>>> Sent: Tuesday, 11 December, 2018 05:02
>>> To: [hidden email]
>>> Subject: [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
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Mac: Users receive "database disk image is malformed" errors after restoring database from Time Machine backup

Simon Slavin-3
In reply to this post by Olivier Mascia
On 12 Dec 2018, at 2:59pm, Olivier Mascia <[hidden email]> wrote:

> When TimeMachine makes copies of the files, the database file and -wal file will be copied at different points in time, albeit they should be copied from a filesystem snapshot, so should be consistent to each other. But this should be checked, and verified for different macOS versions.

Yes.  My initial thought was that the journal file would be out-of-sync with the database file.  Though I could not guess which would be earlier and which later.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Mac: Users receive "database disk image is malformed" errors after restoring database from Time Machine backup

Scott Perry
In reply to this post by Daniel Alm
On Dec 11, 2018, at 04:01, Daniel Alm <[hidden email]> wrote:
>
> 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).

Time Machine achieves eventual consistency by restarting when it detects that a file has changed since the backup was started. It does not have special provisions for SQLite database files.

Even if the scheduled backup never runs, the conditions under which a database would be captured in an utterly inconsistent state should be vanishingly rare. It would be most useful if you could share a representative database with Richard for analysis.

> 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.

Out of curiosity, why aren't you using the SQLite that comes with the OS?

Scott

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Mac: Users receive "database disk image is malformed" errors after restoring database from Time Machine backup

Daniel Alm
In reply to this post by Daniel Alm
> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users