Re: [sqlite] Corrupted database repairing
It's very dificult decision. I'm prefer to use some callback function for logging all queries. But I don't know how to realise this callback - trace and profile callbacks can't show query string with variables values. 2008/8/11, Dennis Cote <[EMAIL PROTECTED]>: > Alexey Pechnikov wrote: >> >> Can I get full log of sql statements for to sent it other network or store >> to >> outher device? >> > > Alexey, > > You may want to read http://www.sqlite.org/cvstrac/wiki?p=UndoRedo for > an example of using triggers to generate SQL to modify a database. This > example is used for undo/redo, but the principals would be the same if > you want to generate an SQL log of changes that have been made to a > database. > > HTH > Dennis Cote > > > ___ > 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] Corrupted database repairing
Alexey Pechnikov wrote: > > Can I get full log of sql statements for to sent it other network or store to > outher device? > Alexey, You may want to read http://www.sqlite.org/cvstrac/wiki?p=UndoRedo for an example of using triggers to generate SQL to modify a database. This example is used for undo/redo, but the principals would be the same if you want to generate an SQL log of changes that have been made to a database. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Corrupted database repairing
В сообщении от Saturday 26 July 2008 10:23:57 Roger Binns написал(а): > Alexey Pechnikov wrote: > >> Maybe sqlite3_trace() or sqlite3_profile() can help with what you're > >> looking for here. > > Unfortunately sqlite3_trace isn't that useful as it only tells you the > text of the sql statement but not any bound parameters. The way to get > the bound parameters is to note them in your own functions that prepare > statements and bind them. You may also find this functionality is > already present in various wrappers. For example the Python wrappers do > this. All wrappers (tcl, python etc.) prepare statements and bind them self? I want to get all sql queries log only from scripts. On C I'm only writing extensions. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Corrupted database repairing
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Alexey Pechnikov wrote: >> Maybe sqlite3_trace() or sqlite3_profile() can help with what you're >> looking for here. Unfortunately sqlite3_trace isn't that useful as it only tells you the text of the sql statement but not any bound parameters. The way to get the bound parameters is to note them in your own functions that prepare statements and bind them. You may also find this functionality is already present in various wrappers. For example the Python wrappers do this. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFIisL9mOOfHg372QQRAoE0AJ4yvJoYue7v1ZmwRJjEgUy6zqlk2QCfeojA LgmuBsvg/o/lfrhBEj+CeuA= =habD -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Corrupted database repairing
В сообщении от Friday 25 July 2008 16:32:26 Derrell Lipman написал(а): > > Can I get full log of sql statements for to sent it other network or > > store to > > outher device? > > Maybe sqlite3_trace() or sqlite3_profile() can help with what you're > looking for here. Thanks! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Corrupted database repairing
On Fri, Jul 25, 2008 at 5:23 AM, Alexey Pechnikov <[EMAIL PROTECTED]> wrote: > > > Database servers such as Oracle or PostgreSQL have transactions log and > > > restore log. > > > > Those logs are effectively duplicates of the data or ways of > > reconstructing the data. SQLite has a transaction log for the last > > transaction only and only while it is progress. > > Can I get full log of sql statements for to sent it other network or store > to > outher device? Maybe sqlite3_trace() or sqlite3_profile() can help with what you're looking for here. Derrell ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Corrupted database repairing
В сообщении от Friday 25 July 2008 12:16:39 Roger Binns написал(а):
> Alexey Pechnikov wrote:
> > May be on FS layer?
>
> I specifically said VFS which is SQLite functionality - see
> http://www.sqlite.org/c3ref/vfs.html and
> http://www.sqlite.org/c3ref/io_methods.html
Thanks, last link may be helpful for me.
> > Yes, I'm using SQLite on servers.
>
> Just because you call it a server doesn't make it "server" hardware :-)
> When you pay the big bucks you get memory that can detect and correct
> errors, cpus run in lockstep with failure detection, multiple paths to
> storage devices, raid and similar technologies on the storage subsystem
> etc. Standard PCs have none of this.
I have server hardware with mirror raid, ECC RAM etc. But hardware and OS is
not ideal.
> > Database servers such as Oracle or PostgreSQL have transactions log and
> > restore log.
>
> Those logs are effectively duplicates of the data or ways of
> reconstructing the data. SQLite has a transaction log for the last
> transaction only and only while it is progress.
Can I get full log of sql statements for to sent it other network or store to
outher device? I may to get a part of this info by using "authorizer" method:
SQLITE_DELETE sqlite_master {} main {}
SQLITE_DROP_TABLE events {} main {}
SQLITE_DELETE events {} main {}
SQLITE_DELETE sqlite_master {} main {}
SQLITE_READ sqlite_master tbl_name main {}
SQLITE_READ sqlite_master type main {}
SQLITE_UPDATE sqlite_master rootpage main {}
SQLITE_READ sqlite_master rootpage main {}
SQLITE_UPDATE sqlite_master rootpage main {}
SQLITE_READ sqlite_master rootpage main {}
SQLITE_INSERT sqlite_master {} main {}
SQLITE_CREATE_TABLE events {} main {}
SQLITE_UPDATE sqlite_master type main {}
SQLITE_UPDATE sqlite_master name main {}
SQLITE_UPDATE sqlite_master tbl_name main {}
...
May be I must reimplement authorizer function? Now this best for security, of
course.
> > How can I provide restore mechanisms for SQLite database?
>
> You need to sit down and work out how much your data is worth, what you
> want to protect against, how often bad things happen and how much you
> are prepared to pay. Food for thought:
>
> http://www.acmqueue.org/modules.php?name=Content=showpage=504
> http://lwn.net/Articles/290141/
> http://www.newscientist.com/blog/technology/2008/03/do-we-need-cosmic-ray-a
>lerts-for.html
> http://en.wikipedia.org/wiki/Transmission_Control_Protocol#Error-free_data_
>transfer
Thanks, I'll read the links.
> You can't have 100% reliability as well as free (hardware and software).
> You can aim for 99 point some number of nines, but the more nines you
> have the more expensive it gets exponentially.
Yes, but I'm prefer thinking about data integrity in good time.
> In the real world, you will find that SQLite is suitably reliable for a
> large number of people and projects, taking
> http://www.sqlite.org/lockingv3.html#how_to_corrupt into account.
I'm successfully using SQLite from 2005 year and now I want to know how to use
SQLite in mission-critical applications.
> If you are more paranoid than that then write a SQLite VFS module that
> effectively does custom file level 'raid'. Duplicate data as many times
> as you want (don't forget to send it over the network as well for other
> machines to check). You'll also want to check multiple copies on reads
> in case one is corrupt.
Well, I think it may be good idea.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Corrupted database repairing
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Alexey Pechnikov wrote: > May be on FS layer? I specifically said VFS which is SQLite functionality - see http://www.sqlite.org/c3ref/vfs.html and http://www.sqlite.org/c3ref/io_methods.html > Which FS can help me for this? I'm using ext3 FS now on my > debian box. Filesystems don't help since they don't store redundant copies of data. At the block layer things like RAID do. > May be rsync or like software can restore corruption blocks from > full or incremental backups? How? The backup would have to correspond exactly to the current file otherwise you could be restoring stale blocks. RAID is a far better approach. > Yes, I'm using SQLite on servers. Just because you call it a server doesn't make it "server" hardware :-) When you pay the big bucks you get memory that can detect and correct errors, cpus run in lockstep with failure detection, multiple paths to storage devices, raid and similar technologies on the storage subsystem etc. Standard PCs have none of this. > Database servers such as Oracle or PostgreSQL have transactions log and > restore log. Those logs are effectively duplicates of the data or ways of reconstructing the data. SQLite has a transaction log for the last transaction only and only while it is progress. > How can I provide restore mechanisms for SQLite database? You need to sit down and work out how much your data is worth, what you want to protect against, how often bad things happen and how much you are prepared to pay. Food for thought: http://www.acmqueue.org/modules.php?name=Content=showpage=504 http://lwn.net/Articles/290141/ http://www.newscientist.com/blog/technology/2008/03/do-we-need-cosmic-ray-alerts-for.html http://en.wikipedia.org/wiki/Transmission_Control_Protocol#Error-free_data_transfer > May be > any fuse VFS module can do incremental delta of changes? May be I can do > incremental copy of SQLIte database after every writing transaction "on the > fly" (without database blocking) by rsync/... ? You can write a SQLite VFS module (*not a fuse one*) that does data duplication. You only need to worry about the duplicated data when xSync is called. > P.S. Database servers not good for me because I need for free, fast and > reliable embedded database in my multi-thread application server. You can't have 100% reliability as well as free (hardware and software). You can aim for 99 point some number of nines, but the more nines you have the more expensive it gets exponentially. In the real world, you will find that SQLite is suitably reliable for a large number of people and projects, taking http://www.sqlite.org/lockingv3.html#how_to_corrupt into account. If you are more paranoid than that then write a SQLite VFS module that effectively does custom file level 'raid'. Duplicate data as many times as you want (don't forget to send it over the network as well for other machines to check). You'll also want to check multiple copies on reads in case one is corrupt. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFIiYvnmOOfHg372QQRAvvzAKCWC33+kPicfrqltkHKTrB64LwV1gCghmKk z0uTsHRi39IvLEd0mE/qWIU= =ESiE -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Corrupted database repairing
В сообщении от Friday 25 July 2008 03:40:22 Roger Binns написал(а): > Alexey Pechnikov wrote: > > Is any way to repair corrupted database? > > From a theoretical point of view the only way to repair a corrupted > database is if there are multiple redundant copies of data or of > generating that data. Since SQLite doesn't do that (exception: indices > can be regenerated from uncorrupted data) you are mainly out of luck. > Instead SQLite takes the approach of trying to prevent corruption in the > first place. > > You can address this problem yourself. Write your own custom VFS layer > where you can store multiple redundant copies, checksums or whatever > else you are trying to defend against. May be on FS layer? Which FS can help me for this? I'm using ext3 FS now on my debian box. May be rsync or like software can restore corruption blocks from full or incremental backups? > It is also worth noting that unless you are running on mainframes or > "server" hardware, other corruption will be ignored. Yes, I'm using SQLite on servers. On winmobile PDA/smartphones I have no problems with SQLite and if database corrupted on this environment than winmobile must be reinstalled and FS reformatted. But on server I must make provision for data restoring in any cause. > For example > commodity machines don't have error checking or correcting RAM, checking > CPUs, checking hard disk controllers. The good news is that it is > slowly coming such as end to end checksums in ZFS, checksums in the SATA > spec etc. Database servers such as Oracle or PostgreSQL have transactions log and restore log. How can I provide restore mechanisms for SQLite database? May be any fuse VFS module can do incremental delta of changes? May be I can do incremental copy of SQLIte database after every writing transaction "on the fly" (without database blocking) by rsync/... ? P.S. Database servers not good for me because I need for free, fast and reliable embedded database in my multi-thread application server. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Corrupted database repairing
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Alexey Pechnikov wrote: > Is any way to repair corrupted database? - From a theoretical point of view the only way to repair a corrupted database is if there are multiple redundant copies of data or of generating that data. Since SQLite doesn't do that (exception: indices can be regenerated from uncorrupted data) you are mainly out of luck. Instead SQLite takes the approach of trying to prevent corruption in the first place. You can address this problem yourself. Write your own custom VFS layer where you can store multiple redundant copies, checksums or whatever else you are trying to defend against. You can also use it to verify that SQLite handles situations well, in addition to your own code. (For example make a write routine emulate disk full). [BTW the SQLite test suite is full of tests like this anyway] It is also worth noting that unless you are running on mainframes or "server" hardware, other corruption will be ignored. For example commodity machines don't have error checking or correcting RAM, checking CPUs, checking hard disk controllers. The good news is that it is slowly coming such as end to end checksums in ZFS, checksums in the SATA spec etc. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFIiRLmmOOfHg372QQRAvh+AKCfOBIFCNDFt+3pPjR0dMAm+nMcggCgwrkb Z3HWu8qk90LKDD5rgVO9kZs= =ikGn -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Corrupted database repairing
В сообщении от Thursday 24 July 2008 20:48:08 Alexey Pechnikov написал(а): > Hello! > > Is any way to repair corrupted database? May be I have archive copy of > database and corrupted this pages - can I get correct pages and merge their > with archive database? > > P.S. I have no corrupted database now but this question is important for > me. If page allocation data is correct than exists chance to repair non-corrupted pages. But how do it? And can I disable database schema reading and get access to non-corrupted pages? Can I manually set database schema (may be in memory only for current session) for get access to tables? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Corrupted database repairing
Hello! Is any way to repair corrupted database? May be I have archive copy of database and corrupted this pages - can I get correct pages and merge their with archive database? P.S. I have no corrupted database now but this question is important for me. Best regards, Alexey. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
