Re: [sqlite] table backup

[dd]

I am using SQLite version 3.7.14.1.


On Wed, Dec 12, 2012 at 2:56 PM, dd <durga.d...@gmail.com> wrote:

> Hi Michael
>
> I am using journal mode truncate. Explicitly, I am not running any pragma
> for synchronous. (i think, it's default value is 2 - Full).
>
> Regards,
> d
>
>
> On Tue, Dec 11, 2012 at 5:08 PM, Michael Black <mdblac...@yahoo.com>wrote:
>
>> I don't see in the thread where you say what journal mode you're running
>> in
>> or your synchronous setting.
>> That may explain your problem and be easily fixable.
>>
>> The other thing is to compile your system with stack protection and see if
>> that can trap the problem.
>>
>> If it's just stack corruption you should be able to trigger the error
>> locally much more quickly with stack protection on then in a random
>> system.
>>
>>
>>
>>
>>
>> -Original Message-
>> From: sqlite-users-boun...@sqlite.org
>> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of dd
>> Sent: Tuesday, December 11, 2012 1:00 AM
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] table backup
>>
>> Hi Roger,
>>
>>   I don't have any clue. Two databases are corrupted. First one, while
>> inserting 20,000 records suddenly sqlite thrown disk io error at 4,000
>> record. No clue.
>>
>>   Second database corrupted when my application crashed. But that time,
>> second database was not opened.
>>
>>   So, I am planning to choose backup solution instead of investigating
>> corruption.
>>
>>   I discussed this issue in my previous post.
>>
>>   Any sample application for virtual table option.
>>
>> Best Regards,
>> d
>>
>>
>> On Mon, Dec 10, 2012 at 9:09 PM, Roger Binns <rog...@rogerbinns.com>
>> wrote:
>>
>> > -BEGIN PGP SIGNED MESSAGE-
>> > Hash: SHA1
>> >
>> > On 09/12/12 21:44, dd wrote:
>> > > Sometimes, sqlite databse corrupts.
>> >
>> > That is the problem you need to fix.  If you have a system that is
>> > unreliable then it will also corrupt your backups.
>> >
>> > http://www.sqlite.org/lockingv3.html#how_to_corrupt
>> > http://www.sqlite.org/howtocorrupt.html
>> >
>> > > So, I want to take online backup of specific table. Not entire
>> > > database.
>> >
>> > Do you need to take a backup on every change, as part of the change or
>> is
>> > it acceptable to make backups periodically and possibly lose
>> intermediate
>> > versions of the data?
>> >
>> > For a periodic backup you can iterate over the table contents and output
>> > them in a convenient format for you, such as CSV or SQL statements.
>> >
>> > For saving all data you can use triggers to save historical values in a
>> > second table and then do a periodic backup.
>> >
>> > If it must be immediate then the only choice available is to use a
>> virtual
>> > table and do the backup during writes/sync.
>> >
>> > This is all considerably more work than figuring out why you are getting
>> > corruption in the first place.
>> >
>> > Roger
>> > -BEGIN PGP SIGNATURE-
>> > Version: GnuPG v1.4.11 (GNU/Linux)
>> >
>> > iEYEARECAAYFAlDGF2IACgkQmOOfHg372QTo9gCfSQQwreSvsa9lrV/wj0YC2Fvj
>> > LT0AmwdZSaNvVJJuic3gLYmQfn9YX6x3
>> > =Gx1r
>> > -END PGP SIGNATURE-
>> > ___
>> > 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

Re: [sqlite] table backup

[dd]

Hi Michael

I am using journal mode truncate. Explicitly, I am not running any pragma
for synchronous. (i think, it's default value is 2 - Full).

Regards,
d


On Tue, Dec 11, 2012 at 5:08 PM, Michael Black <mdblac...@yahoo.com> wrote:

> I don't see in the thread where you say what journal mode you're running in
> or your synchronous setting.
> That may explain your problem and be easily fixable.
>
> The other thing is to compile your system with stack protection and see if
> that can trap the problem.
>
> If it's just stack corruption you should be able to trigger the error
> locally much more quickly with stack protection on then in a random system.
>
>
>
>
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of dd
> Sent: Tuesday, December 11, 2012 1:00 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] table backup
>
> Hi Roger,
>
>   I don't have any clue. Two databases are corrupted. First one, while
> inserting 20,000 records suddenly sqlite thrown disk io error at 4,000
> record. No clue.
>
>   Second database corrupted when my application crashed. But that time,
> second database was not opened.
>
>   So, I am planning to choose backup solution instead of investigating
> corruption.
>
>   I discussed this issue in my previous post.
>
>   Any sample application for virtual table option.
>
> Best Regards,
> d
>
>
> On Mon, Dec 10, 2012 at 9:09 PM, Roger Binns <rog...@rogerbinns.com>
> wrote:
>
> > -BEGIN PGP SIGNED MESSAGE-
> > Hash: SHA1
> >
> > On 09/12/12 21:44, dd wrote:
> > > Sometimes, sqlite databse corrupts.
> >
> > That is the problem you need to fix.  If you have a system that is
> > unreliable then it will also corrupt your backups.
> >
> > http://www.sqlite.org/lockingv3.html#how_to_corrupt
> > http://www.sqlite.org/howtocorrupt.html
> >
> > > So, I want to take online backup of specific table. Not entire
> > > database.
> >
> > Do you need to take a backup on every change, as part of the change or is
> > it acceptable to make backups periodically and possibly lose intermediate
> > versions of the data?
> >
> > For a periodic backup you can iterate over the table contents and output
> > them in a convenient format for you, such as CSV or SQL statements.
> >
> > For saving all data you can use triggers to save historical values in a
> > second table and then do a periodic backup.
> >
> > If it must be immediate then the only choice available is to use a
> virtual
> > table and do the backup during writes/sync.
> >
> > This is all considerably more work than figuring out why you are getting
> > corruption in the first place.
> >
> > Roger
> > -BEGIN PGP SIGNATURE-
> > Version: GnuPG v1.4.11 (GNU/Linux)
> >
> > iEYEARECAAYFAlDGF2IACgkQmOOfHg372QTo9gCfSQQwreSvsa9lrV/wj0YC2Fvj
> > LT0AmwdZSaNvVJJuic3gLYmQfn9YX6x3
> > =Gx1r
> > -END PGP SIGNATURE-
> > ___
> > 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

Re: [sqlite] table backup

[Simon Slavin]

On 11 Dec 2012, at 6:00pm, Roger Binns  wrote:

> Chances are that whatever corrupts your databases would also corrupt the
> backups.  Here is something the Windows team found:
> 
>  http://blogs.msdn.com/b/oldnewthing/archive/2005/04/12/407562.aspx

Right.  I don't have experience with motherboards and CPUs but I have seen 
similar things with storage subsystems.  Take hard disks intended for laptop or 
desktop use, and spin them 24/7 in a server and you get overheating, uneven 
bearing wear, and faulty operation.  Take a cheap Orient-made hard disk apart 
and find a surface rated for 500Gig used with a stepper motor which turns it 
into a 750Gig drive.  Or a sprayed gold databus rated for 11ms switching which 
should be an electroplated databus rated for 6ms switching.

That sort of skimping is fine on a laptop you use for browsing and email.  Then 
you find some cheapo business organisation is using one as a server.  
Diagnosing the problem takes a thousand dollars of expert time that could have 
paid for a proper server.  And a professional software developer resentful 
because he had to learn hardware and physics.

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

Re: [sqlite] table backup

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 11/12/12 03:46, dd wrote:
> But in customers place, I am getting corruptions.

Chances are that whatever corrupts your databases would also corrupt the
backups.  Here is something the Windows team found:

  http://blogs.msdn.com/b/oldnewthing/archive/2005/04/12/407562.aspx

> But, critical data is there with corrupted database. How can I recover
> it without backup.

You can't without a backup unless the corruption happened in duplicate
information (eg an index).

> Any sample application for virtual table option.

You'll probably find that quite hard.  A simpler alternative is to
temporarily attach another database and copy the table into it.

  attach 'backup.db' as backup;
  drop table if exists backup.important;
  create table backup.important as select * from important;
  detach backup;

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlDHdLIACgkQmOOfHg372QTEjgCdGcoBgu9u5d55NuSoIsl57WWr
pqYAoMWjFW0UhM0i8tsRCqRznV8Irh5u
=03Xw
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] table backup

[Michael Black]

I don't see in the thread where you say what journal mode you're running in
or your synchronous setting.
That may explain your problem and be easily fixable.

The other thing is to compile your system with stack protection and see if
that can trap the problem.

If it's just stack corruption you should be able to trigger the error
locally much more quickly with stack protection on then in a random system.





-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of dd
Sent: Tuesday, December 11, 2012 1:00 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] table backup

Hi Roger,

  I don't have any clue. Two databases are corrupted. First one, while
inserting 20,000 records suddenly sqlite thrown disk io error at 4,000
record. No clue.

  Second database corrupted when my application crashed. But that time,
second database was not opened.

  So, I am planning to choose backup solution instead of investigating
corruption.

  I discussed this issue in my previous post.

  Any sample application for virtual table option.

Best Regards,
d


On Mon, Dec 10, 2012 at 9:09 PM, Roger Binns <rog...@rogerbinns.com> wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 09/12/12 21:44, dd wrote:
> > Sometimes, sqlite databse corrupts.
>
> That is the problem you need to fix.  If you have a system that is
> unreliable then it will also corrupt your backups.
>
> http://www.sqlite.org/lockingv3.html#how_to_corrupt
> http://www.sqlite.org/howtocorrupt.html
>
> > So, I want to take online backup of specific table. Not entire
> > database.
>
> Do you need to take a backup on every change, as part of the change or is
> it acceptable to make backups periodically and possibly lose intermediate
> versions of the data?
>
> For a periodic backup you can iterate over the table contents and output
> them in a convenient format for you, such as CSV or SQL statements.
>
> For saving all data you can use triggers to save historical values in a
> second table and then do a periodic backup.
>
> If it must be immediate then the only choice available is to use a virtual
> table and do the backup during writes/sync.
>
> This is all considerably more work than figuring out why you are getting
> corruption in the first place.
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.11 (GNU/Linux)
>
> iEYEARECAAYFAlDGF2IACgkQmOOfHg372QTo9gCfSQQwreSvsa9lrV/wj0YC2Fvj
> LT0AmwdZSaNvVJJuic3gLYmQfn9YX6x3
> =Gx1r
> -END PGP SIGNATURE-
> ___
> 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

Re: [sqlite] table backup

[Simon Slavin]

On 11 Dec 2012, at 11:46am, dd  wrote:

> I tested with millions of records. Sqlite is stable and reliable. It is
> suitable for my application. So, I have chosen.
> But in customers place, I am getting corruptions. Within 6 months I got 4
> corruptions.

That is a good test.  My conclusion is that your computer is fine and your 
customer's computer is faulty.  This is perfectly possible.  Tell them to use a 
different computer and the corruption will probably stop.

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

Re: [sqlite] table backup

[dd]

I tested with millions of records. Sqlite is stable and reliable. It is
suitable for my application. So, I have chosen.
But in customers place, I am getting corruptions. Within 6 months I got 4
corruptions. I do integrity check when app launch. But, critical data is
there with corrupted database. How can I recover it without backup.

bit more details:

1. Application installing in users machine.
2. Configuration maintains in database in local computer.
3. Application related data stored in database when running in local
computer.
4. Users related data(critical) stored in database in local computer.

Database integrity is very very fine when app launches. In runtime, due to
hardware or my logic error(using smart pointers boost/std), sqlite database
got corrupted. Now, backup is mandatory for me.

this is integiry_check result for corrupted db:

sqlite> PRAGMA integrity_check;
*** in database main ***
Page 255: btreeInitPage() returns error code 11
On tree page 970 cell 5: Child page depth differs
Page 252: unable to get the page. error code=1
Page 249: unable to get the page. error code=1
Page 246: unable to get the page. error code=1
Page 244: unable to get the page. error code=1
Page 239: unable to get the page. error code=1
Page 236: unable to get the page. error code=1
Page 233: unable to get the page. error code=1
Page 180: unable to get the page. error code=1
Page 229: unable to get the page. error code=1
Page 226: unable to get the page. error code=1
Page 223: unable to get the page. error code=1
Page 220: unable to get the page. error code=1
Page 218: unable to get the page. error code=1
Page 214: unable to get the page. error code=1
Page 211: unable to get the page. error code=1
Page 208: unable to get the page. error code=1
Page 205: unable to get the page. error code=1
Page 203: unable to get the page. error code=1
Page 194: unable to get the page. error code=1
Page 197: unable to get the page. error code=1
Page 200: unable to get the page. error code=1
Page 112: unable to get the page. error code=1
On tree page 1832 cell 11: Child page depth differs
Page 1101: unable to get the page. error code=1
On tree page 1832 cell 12: Child page depth differs
Page 1181: unable to get the page. error code=1
Page 1331: unable to get the page. error code=1
Page 1377: unable to get the page. error code=1
Page 1426: unable to get the page. error code=1
Page 2475: unable to get the page. error code=1
Page 2759: unable to get the page. error code=1
Page 2890: unable to get the page. error code=1
Page 2927: unable to get the page. error code=1
Page 2709: unable to get the page. error code=1
Page 2375: unable to get the page. error code=1
Page 2129: unable to get the page. error code=1
Page 1199: unable to get the page. error code=1
Page 1833: unable to get the page. error code=1
Page 7: unable to get the page. error code=1
Page 8: unable to get the page. error code=1
Page 5: unable to get the page. error code=1
Page 6: unable to get the page. error code=1
Page 4: unable to get the page. error code=1
Page 2: unable to get the page. error code=1
Page 3: unable to get the page. error code=1
Page 1: unable to get the page. error code=1
Page 9 is never used
Page 10 is never used
Page 16 is never used
Page 21 is never used
Page 22 is never used
Page 23 is never used
Page 24 is never used
Page 25 is never used
Page 26 is never used
Page 28 is never used
Page 30 is never used
Page 31 is never used
Page 33 is never used
Page 34 is never used
Page 35 is never used
Page 36 is never used
Page 37 is never used
Page 38 is never used
Page 39 is never used
Page 40 is never used
Page 41 is never used
Page 42 is never used
Page 43 is never used
Page 44 is never used
Page 46 is never used
Page 47 is never used
Page 49 is never used
Page 51 is never used
Page 54 is never used
Page 55 is never used
Page 56 is never used
Page 57 is never used
Page 58 is never used
Page 59 is never used
Page 60 is never used
Page 61 is never used
Page 62 is never used
Page 63 is never used
Page 64 is never used
Page 65 is never used
Page 66 is never used
Page 67 is never used
Page 68 is never used
Page 69 is never used
Page 70 is never used
Page 71 is never used
Page 72 is never used
Page 73 is never used
Page 74 is never used
Page 75 is never used
Page 76 is never used
Page 77 is never used

Any inputs?


On Tue, Dec 11, 2012 at 2:16 PM, Simon Slavin  wrote:

>
> On 11 Dec 2012, at 7:00am, dd  wrote:
>
> >  I don't have any clue. Two databases are corrupted. First one, while
> > inserting 20,000 records suddenly sqlite thrown disk io error at 4,000
> > record. No clue.
> >
> >  Second database corrupted when my application crashed. But that time,
> > second database was not opened.
> >
> >  So, I am planning to choose backup solution instead of investigating
> > corruption.
>
> That seems silly.  Unless you run an integrity-check just before you do a
> backup you will be backing up corrupted databases.  You 

Re: [sqlite] table backup

[Simon Slavin]

On 11 Dec 2012, at 7:00am, dd  wrote:

>  I don't have any clue. Two databases are corrupted. First one, while
> inserting 20,000 records suddenly sqlite thrown disk io error at 4,000
> record. No clue.
> 
>  Second database corrupted when my application crashed. But that time,
> second database was not opened.
> 
>  So, I am planning to choose backup solution instead of investigating
> corruption.

That seems silly.  Unless you run an integrity-check just before you do a 
backup you will be backing up corrupted databases.  You will restore a copy 
from your backup and find that it too is corrupt.  And as you put more and more 
data into a database both integrity-check and backup get slower and slower.  
You really don't want to set up a working system that depends on them.

There are literally hundreds of millions of installations of SQLite.  Perhaps 
half a billion.  Every released version undergoes three kinds of testing of the 
very strictest kind.  The source code is public which means there are thousands 
of experienced programmers who look through it and might notice and bad 
programming.  SQLite does not routinely corrupt databases.  I'm not saying that 
it never happens but on this mailing list it's unusual to find reports of 
corruption that aren't the result of hardware faults of bad programming in the 
code that /calls/ the SQLite API.  If you have either of these problems then 
you need to know about it.  For all you know you have a faulty hard disk drive 
or even just a loose connection somewhere in your computer.

That process where you insert 20,000 records ?  That looks ideal.  Do it a 
hundred times.  See if you can stimulate another fault.

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

Re: [sqlite] table backup

[Hick Gunter]

Sorry, but the virtual table extensions I have written are all proprietary.

Gunter

-Ursprüngliche Nachricht-
Von: dd [mailto:durga.d...@gmail.com]
Gesendet: Dienstag, 11. Dezember 2012 08:00
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] table backup

Hi Roger,

  I don't have any clue. Two databases are corrupted. First one, while
inserting 20,000 records suddenly sqlite thrown disk io error at 4,000
record. No clue.

  Second database corrupted when my application crashed. But that time,
second database was not opened.

  So, I am planning to choose backup solution instead of investigating
corruption.

  I discussed this issue in my previous post.

  Any sample application for virtual table option.

Best Regards,
d


On Mon, Dec 10, 2012 at 9:09 PM, Roger Binns <rog...@rogerbinns.com> wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 09/12/12 21:44, dd wrote:
> > Sometimes, sqlite databse corrupts.
>
> That is the problem you need to fix.  If you have a system that is
> unreliable then it will also corrupt your backups.
>
> http://www.sqlite.org/lockingv3.html#how_to_corrupt
> http://www.sqlite.org/howtocorrupt.html
>
> > So, I want to take online backup of specific table. Not entire
> > database.
>
> Do you need to take a backup on every change, as part of the change or is
> it acceptable to make backups periodically and possibly lose intermediate
> versions of the data?
>
> For a periodic backup you can iterate over the table contents and output
> them in a convenient format for you, such as CSV or SQL statements.
>
> For saving all data you can use triggers to save historical values in a
> second table and then do a periodic backup.
>
> If it must be immediate then the only choice available is to use a virtual
> table and do the backup during writes/sync.
>
> This is all considerably more work than figuring out why you are getting
> corruption in the first place.
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.11 (GNU/Linux)
>
> iEYEARECAAYFAlDGF2IACgkQmOOfHg372QTo9gCfSQQwreSvsa9lrV/wj0YC2Fvj
> LT0AmwdZSaNvVJJuic3gLYmQfn9YX6x3
> =Gx1r
> -END PGP SIGNATURE-
> ___
> 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


--
 Gunter Hick
Software Engineer
Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna, Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then delete this message from 
your system. Please do not copy it or use it for any purposes, or disclose its 
contents to any person as to do so could be a breach of confidence. Thank you 
for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] table backup

[dd]

Hi Roger,

  I don't have any clue. Two databases are corrupted. First one, while
inserting 20,000 records suddenly sqlite thrown disk io error at 4,000
record. No clue.

  Second database corrupted when my application crashed. But that time,
second database was not opened.

  So, I am planning to choose backup solution instead of investigating
corruption.

  I discussed this issue in my previous post.

  Any sample application for virtual table option.

Best Regards,
d


On Mon, Dec 10, 2012 at 9:09 PM, Roger Binns  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 09/12/12 21:44, dd wrote:
> > Sometimes, sqlite databse corrupts.
>
> That is the problem you need to fix.  If you have a system that is
> unreliable then it will also corrupt your backups.
>
> http://www.sqlite.org/lockingv3.html#how_to_corrupt
> http://www.sqlite.org/howtocorrupt.html
>
> > So, I want to take online backup of specific table. Not entire
> > database.
>
> Do you need to take a backup on every change, as part of the change or is
> it acceptable to make backups periodically and possibly lose intermediate
> versions of the data?
>
> For a periodic backup you can iterate over the table contents and output
> them in a convenient format for you, such as CSV or SQL statements.
>
> For saving all data you can use triggers to save historical values in a
> second table and then do a periodic backup.
>
> If it must be immediate then the only choice available is to use a virtual
> table and do the backup during writes/sync.
>
> This is all considerably more work than figuring out why you are getting
> corruption in the first place.
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.11 (GNU/Linux)
>
> iEYEARECAAYFAlDGF2IACgkQmOOfHg372QTo9gCfSQQwreSvsa9lrV/wj0YC2Fvj
> LT0AmwdZSaNvVJJuic3gLYmQfn9YX6x3
> =Gx1r
> -END PGP SIGNATURE-
> ___
> 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] table backup

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 09/12/12 21:44, dd wrote:
> Sometimes, sqlite databse corrupts.

That is the problem you need to fix.  If you have a system that is
unreliable then it will also corrupt your backups.

http://www.sqlite.org/lockingv3.html#how_to_corrupt
http://www.sqlite.org/howtocorrupt.html

> So, I want to take online backup of specific table. Not entire
> database.

Do you need to take a backup on every change, as part of the change or is
it acceptable to make backups periodically and possibly lose intermediate
versions of the data?

For a periodic backup you can iterate over the table contents and output
them in a convenient format for you, such as CSV or SQL statements.

For saving all data you can use triggers to save historical values in a
second table and then do a periodic backup.

If it must be immediate then the only choice available is to use a virtual
table and do the backup during writes/sync.

This is all considerably more work than figuring out why you are getting
corruption in the first place.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlDGF2IACgkQmOOfHg372QTo9gCfSQQwreSvsa9lrV/wj0YC2Fvj
LT0AmwdZSaNvVJJuic3gLYmQfn9YX6x3
=Gx1r
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] table backup

[Hick Gunter]

You could write a virtual table extension that performs pre-image (in the 
xUpdate method) or after-image (in the xCommit method) logging of write 
transactions to a CSV file or even an SQL Logfile.

The biggest challenge would be writing the xBestIndex function to properly 
return an optimal execution plan. Parsing the result of "explain query plan" 
may or may not be an option.

It also depends on the SQL statements used for updates. If you only use "UPDATE 
 SET  WHERE " then it would be relatively 
simple.

Gunter

-Ursprüngliche Nachricht-
Von: dd [mailto:durga.d...@gmail.com]
Gesendet: Montag, 10. Dezember 2012 06:45
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] table backup

Sometimes, sqlite databse corrupts. So, I want to take online backup of
specific table. Not entire database. Any best solution?


On Sun, Dec 9, 2012 at 11:29 PM, Roger Binns <rog...@rogerbinns.com> wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 09/12/12 10:35, dd wrote:
> > i mean, for every write operation in a table.
>
> You should specify some of your other constraints and exactly what it is
> you are trying to achieve (*not* how you are trying to achieve it).
>
> The solutions range from implementing undo by using triggers to save old
> values, through using a virtual table that backs up data somewhere else.
>
> The SQLite backup API only works on the whole database.
>
> Roger
>
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.11 (GNU/Linux)
>
> iEYEARECAAYFAlDE5poACgkQmOOfHg372QQI8gCeP6GfezJUWQ7M3pdfvvgc2FCp
> J1QAoMSM/tbpxlicrrDpTbgjHAsWln1d
> =mPMA
> -END PGP SIGNATURE-
> ___
> 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


--
 Gunter Hick
Software Engineer
Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna, Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then delete this message from 
your system. Please do not copy it or use it for any purposes, or disclose its 
contents to any person as to do so could be a breach of confidence. Thank you 
for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] table backup

[dd]

Sometimes, sqlite databse corrupts. So, I want to take online backup of
specific table. Not entire database. Any best solution?


On Sun, Dec 9, 2012 at 11:29 PM, Roger Binns  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 09/12/12 10:35, dd wrote:
> > i mean, for every write operation in a table.
>
> You should specify some of your other constraints and exactly what it is
> you are trying to achieve (*not* how you are trying to achieve it).
>
> The solutions range from implementing undo by using triggers to save old
> values, through using a virtual table that backs up data somewhere else.
>
> The SQLite backup API only works on the whole database.
>
> Roger
>
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.11 (GNU/Linux)
>
> iEYEARECAAYFAlDE5poACgkQmOOfHg372QQI8gCeP6GfezJUWQ7M3pdfvvgc2FCp
> J1QAoMSM/tbpxlicrrDpTbgjHAsWln1d
> =mPMA
> -END PGP SIGNATURE-
> ___
> 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] table backup

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 09/12/12 10:35, dd wrote:
> i mean, for every write operation in a table.

You should specify some of your other constraints and exactly what it is
you are trying to achieve (*not* how you are trying to achieve it).

The solutions range from implementing undo by using triggers to save old
values, through using a virtual table that backs up data somewhere else.

The SQLite backup API only works on the whole database.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlDE5poACgkQmOOfHg372QQI8gCeP6GfezJUWQ7M3pdfvvgc2FCp
J1QAoMSM/tbpxlicrrDpTbgjHAsWln1d
=mPMA
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] table backup

[dd]

i mean, for every write operation in a table.


On Sun, Dec 9, 2012 at 11:55 PM, dd  wrote:

> Hi All,
>
>   What is the best way to do online backup for a particular table for
> every write operation in a sqlite db. Not entire database.
>
> Best Regards,
> d
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] table backup

[dd]

Hi All,

  What is the best way to do online backup for a particular table for every
write operation in a sqlite db. Not entire database.

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