Re: [sqlite] sqlite3 datbase disk image malformed

[John LeSueur]

Your rsync command might be ignoring any journal files that may be
outstanding.

John

On Fri, Mar 6, 2009 at 10:33 AM, Derrell Lipman wrote:

> On Thu, Mar 5, 2009 at 7:39 AM, Peter van Dijk 
> wrote:
>
> > On Thu, Mar 05, 2009 at 04:30:50AM -0800, donnied wrote:
> > >
> > > rsync was corrupting the database.  I'll have to exclude the database
> > from
> > > rsync backup.
> >
> > I feel a need to point out that it is not, technically, rsync that was
> > corrupting
> > the database. The issue is that rsync does not take -snapshots- of files;
> > for
> > that matter, very few backup tools do. One fix/workaround is to have a
> > cronjob
> > for making textual dumps.
> >
>
> If it's possible that some other application is modifying the database
> while
> you're backing it up, you'll likely end up with a corrupted database when
> you rsync it. rsync only copies changed regions of files to save bandwidth.
> If you make it a standard practice to always issue a "BEGIN EXCLUSIVE"
> transaction on the database before you do your rsync, then the database
> will
> be in a consistent state on both the destination side where rsync compares
> and the source side where rsync is looking for changes. This way, the
> database is effectively a static (unchanging) file and rsync should have no
> problem backing it up without corruption. After you complete the rsync then
> ROLLBACK the transaction.
>
> Derrell
> ___
> 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

Re: [sqlite] sqlite3 datbase disk image malformed

[Peter van Dijk]

On Thu, Mar 05, 2009 at 04:30:50AM -0800, donnied wrote:
> 
> rsync was corrupting the database.  I'll have to exclude the database from
> rsync backup. 

I feel a need to point out that it is not, technically, rsync that was 
corrupting
the database. The issue is that rsync does not take -snapshots- of files; for
that matter, very few backup tools do. One fix/workaround is to have a cronjob
for making textual dumps.

Cheers, Peter
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 datbase disk image malformed

[donnied]

rsync was corrupting the database.  I'll have to exclude the database from
rsync backup. 


D. Richard Hipp wrote:
> 
> 
> See http://www.sqlite.org/atomiccommit.html and especially section 9.0  
> "Things That Can Go Wrong"
> 
> 

-- 
View this message in context: 
http://www.nabble.com/sqlite3-datbase-disk-image-malformed-tp22329261p22350695.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 datbase disk image malformed

[Tito Ciuro]

Hi Dan,

On Mar 4, 2009, at 9:21 AM, Dan wrote:

>
> On Mar 5, 2009, at 12:10 AM, Tito Ciuro wrote:
>
>> Hello,
>>
>> On Mar 4, 2009, at 6:06 AM, D. Richard Hipp wrote:
>>
>>> See http://www.sqlite.org/atomiccommit.html and especially section
>>> 9.0
>>> "Things That Can Go Wrong"
>>
>> Reading the above link, I'm curious about a specific case: 4.2 Hot
>> Rollback Journals. It states that:
>>
>> [...]
>> The first time that any SQLite process attempts to access the  
>> database
>> file, it obtains a shared lock as described in section 3.2 above. But
>> then it notices that there is a rollback journal file present. SQLite
>> then checks to see if the rollback journal is a "hot journal".
>> [...]
>>
>> SQLite's documentation in http://www.sqlite.org/lockingv3.html states
>> the following about a shared lock:
>>
>> [...]
>> The database may be read but not written. Any number of processes can
>> hold SHARED locks at the same time, hence there can be many
>> simultaneous readers. But no other thread or process is allowed to
>> write to the database file while one or more SHARED locks are active.
>> [...]
>>
>> So, if when SQLite attempts to access the data file for the first  
>> time
>> (thread T1) and obtains a shared lock, it seems that there's a window
>> of opportunity for secondary thread (T2) to obtain another shared
>> lock. In this case, T1 would not obtain an exclusive lock until T2  
>> has
>> completed reading. This would potentially leave T2 with damaged/
>> inconsistent data. Once T2's shared lock was relinquished, T1 would
>> proceed to rollback the hot journal.
>>
>> Shouldn't the first connection obtain an exclusive lock right away
>> instead and then perform the testing for the existence of a hot
>> journal? I'm probably mistaken, but this is what I gather from the
>> documentation mentioned above.
>
> After obtaining a shared-lock, SQLite tests for the existence of
> a hot-journal file. The test for a hot-journal file is that the
> journal file exists and that no other connection holds a RESERVED
> or PENDING lock on the database file. We know no other process
> is holding an EXCLUSIVE lock on the database file, since we are
> holding a SHARED lock.
>
> If it determines that there is a hot-journal file in the file system,
> SQLite obtains an EXCLUSIVE lock on the database file. It does
> not obtain a RESERVED or PENDING lock first like it does normally,
> but jumps straight to EXCLUSIVE.
>
> If the EXCLUSIVE lock is obtained Ok, roll back the journal file.
> If not, then release all locks and return SQLITE_BUSY. If the
> EXCLUSIVE lock cannot be obtained, then some other process must
> have obtained a SHARED lock. The other process will also try to
> roll back the hot-journal. By releasing all locks, hopefully
> we can get out of the other processes way fast enough to allow it
> to obtain the EXCLUSIVE lock and roll back the journal file.
>
> The key is that at no point is it possible for a second process
> to conclude that the database is valid when there is really a
> hot-journal file that requires rollback in the file-system. If
> there are multiple clients all trying to access the database at
> once then a few operations might return SQLITE_BUSY, but eventually
> one of the clients will successfully obtain the EXCLUSIVE lock
> and roll back the hot-journal.
>
> Dan.

Makes perfect sense (especially the last paragraph). Thanks for taking  
the time to write a detailed explanation.

Regards,

-- Tito

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 datbase disk image malformed

[Dan]

On Mar 5, 2009, at 12:10 AM, Tito Ciuro wrote:

> Hello,
>
> On Mar 4, 2009, at 6:06 AM, D. Richard Hipp wrote:
>
>> See http://www.sqlite.org/atomiccommit.html and especially section  
>> 9.0
>> "Things That Can Go Wrong"
>
> Reading the above link, I'm curious about a specific case: 4.2 Hot
> Rollback Journals. It states that:
>
> [...]
> The first time that any SQLite process attempts to access the database
> file, it obtains a shared lock as described in section 3.2 above. But
> then it notices that there is a rollback journal file present. SQLite
> then checks to see if the rollback journal is a "hot journal".
> [...]
>
> SQLite's documentation in http://www.sqlite.org/lockingv3.html states
> the following about a shared lock:
>
> [...]
> The database may be read but not written. Any number of processes can
> hold SHARED locks at the same time, hence there can be many
> simultaneous readers. But no other thread or process is allowed to
> write to the database file while one or more SHARED locks are active.
> [...]
>
> So, if when SQLite attempts to access the data file for the first time
> (thread T1) and obtains a shared lock, it seems that there's a window
> of opportunity for secondary thread (T2) to obtain another shared
> lock. In this case, T1 would not obtain an exclusive lock until T2 has
> completed reading. This would potentially leave T2 with damaged/
> inconsistent data. Once T2's shared lock was relinquished, T1 would
> proceed to rollback the hot journal.
>
> Shouldn't the first connection obtain an exclusive lock right away
> instead and then perform the testing for the existence of a hot
> journal? I'm probably mistaken, but this is what I gather from the
> documentation mentioned above.

After obtaining a shared-lock, SQLite tests for the existence of
a hot-journal file. The test for a hot-journal file is that the
journal file exists and that no other connection holds a RESERVED
or PENDING lock on the database file. We know no other process
is holding an EXCLUSIVE lock on the database file, since we are
holding a SHARED lock.

If it determines that there is a hot-journal file in the file system,
SQLite obtains an EXCLUSIVE lock on the database file. It does
not obtain a RESERVED or PENDING lock first like it does normally,
but jumps straight to EXCLUSIVE.

If the EXCLUSIVE lock is obtained Ok, roll back the journal file.
If not, then release all locks and return SQLITE_BUSY. If the
EXCLUSIVE lock cannot be obtained, then some other process must
have obtained a SHARED lock. The other process will also try to
roll back the hot-journal. By releasing all locks, hopefully
we can get out of the other processes way fast enough to allow it
to obtain the EXCLUSIVE lock and roll back the journal file.

The key is that at no point is it possible for a second process
to conclude that the database is valid when there is really a
hot-journal file that requires rollback in the file-system. If
there are multiple clients all trying to access the database at
once then a few operations might return SQLITE_BUSY, but eventually
one of the clients will successfully obtain the EXCLUSIVE lock
and roll back the hot-journal.

Dan.







> Regards,
>
> -- Tito
> ___
> 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

Re: [sqlite] sqlite3 datbase disk image malformed

[Tito Ciuro]

Hello,

On Mar 4, 2009, at 6:06 AM, D. Richard Hipp wrote:

> See http://www.sqlite.org/atomiccommit.html and especially section 9.0
> "Things That Can Go Wrong"

Reading the above link, I'm curious about a specific case: 4.2 Hot  
Rollback Journals. It states that:

[...]
The first time that any SQLite process attempts to access the database  
file, it obtains a shared lock as described in section 3.2 above. But  
then it notices that there is a rollback journal file present. SQLite  
then checks to see if the rollback journal is a "hot journal".
[...]

SQLite's documentation in http://www.sqlite.org/lockingv3.html states  
the following about a shared lock:

[...]
The database may be read but not written. Any number of processes can  
hold SHARED locks at the same time, hence there can be many  
simultaneous readers. But no other thread or process is allowed to  
write to the database file while one or more SHARED locks are active.
[...]

So, if when SQLite attempts to access the data file for the first time  
(thread T1) and obtains a shared lock, it seems that there's a window  
of opportunity for secondary thread (T2) to obtain another shared  
lock. In this case, T1 would not obtain an exclusive lock until T2 has  
completed reading. This would potentially leave T2 with damaged/ 
inconsistent data. Once T2's shared lock was relinquished, T1 would  
proceed to rollback the hot journal.

Shouldn't the first connection obtain an exclusive lock right away  
instead and then perform the testing for the existence of a hot  
journal? I'm probably mistaken, but this is what I gather from the  
documentation mentioned above.

Regards,

-- Tito
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 datbase disk image malformed

[D. Richard Hipp]

On Mar 4, 2009, at 7:41 AM, donnied wrote:

>
> I have a very small sql database (132K).  I will often get error  
> messages
> that the Disk image is malformed.  I've looked over possible causes  
> cited
> elsewhere and didn't see anything pertinent.  I'm using an ext3 file  
> system
> with 64 bit Debian.  The files are created with Python (first one  
> script for
> three tables and then two more scripts one additional table each).
> Everything seems fine and then when I come back to the database  
> later I get
> a message that the disk image is malformed. I'll create a cron job to
> monitor the database with "pragma integrity_check" but I'm not sure  
> what
> else to check.

See http://www.sqlite.org/atomiccommit.html and especially section 9.0  
"Things That Can Go Wrong"

D. Richard Hipp
d...@hwaci.com



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users