Re: [sqlite] corrupt database recovery

[Gene Allen]

It's not hard to update it...I'm just whining about that it's out of date
and I hate having to deal with matching everything up so I was hoping that I
could get past the Attach. 

I think your python script below might ease my maintenance whoas a bit.
I'll port it to C# and just build my insert on the fly.   

Thank you. 

Gene

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of John Machin
Sent: Saturday, April 25, 2009 9:30 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] corrupt database recovery

On 26/04/2009 11:28 AM, Gene wrote:
> You are exactly right John...that is indeed what the code looks
> like...except we have over 25 columns (it's a flat table).

I presume that you are referring to this:
"""
  Write a script that loops around doing 'select * from mytable where
  rowid = ?' on a connection to your corrupt database and doing 'insert
  into mytable values(?,?,?,?,? etc etc etc)' on a connection to your
  clean database.
"""

If that's correct, then surely the only maintenance you need to do to 
the above when an extra column is added to your table is to add an extra 
two characters ',?' to the insert statement ... you don't even have to 
do that e.g.

# Python
TABLE_NAME = "mytable"
NUMBER_OF_COLS = 25
question_marks = ",".join("?" * NUMBER_OF_COLS)
insert_sql = "insert into %s values (%s)" % (TABLE_NAME, question_marks)

AND the output from the select should be able to be pumped straight into 
the insert with no changes at all.

AND there might even be a pragma or suchlike that will enable you to 
easily find the number of columns on the fly in your script ...


> 
> We've already fixed the bad code, but there are some customers who have
old
> versions...it didn't break very often with the old code, but it does still
> did.
> 
> I haven't tried a select Min or max on the row id but a select count(*)
> returns an error...that's how I know I need to do the row by row recovery
> method.  Select * from mytable also returns an error.
> 
> The tables usually have tens of thousands of rows, sometimes over a couple
> hundred thousand but that's rare.
> 
> What seems to work is that I do a select * from myTable where rowId = 'X'
> incing X until I get an error.  After I get the error, every row higher
then
> X also returns an error. So as soon as I get an error, I stop trying to
> recover more rows.

Does this usually mean that you are able to recover almost all of the rows?

> The pull and bind code is just ugly and we don't update our 'recovery
> utility' as quickly as we make changes to the database so it tends to get
> out of data.  That's all.

I don't understand what is "the pull and bind" code and why you would 
need anything other that what I've outlined.

Cheers,
John

___
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] corrupt database recovery

[John Machin]

On 26/04/2009 11:28 AM, Gene wrote:
> You are exactly right John...that is indeed what the code looks
> like...except we have over 25 columns (it's a flat table).

I presume that you are referring to this:
"""
  Write a script that loops around doing 'select * from mytable where
  rowid = ?' on a connection to your corrupt database and doing 'insert
  into mytable values(?,?,?,?,? etc etc etc)' on a connection to your
  clean database.
"""

If that's correct, then surely the only maintenance you need to do to 
the above when an extra column is added to your table is to add an extra 
two characters ',?' to the insert statement ... you don't even have to 
do that e.g.

# Python
TABLE_NAME = "mytable"
NUMBER_OF_COLS = 25
question_marks = ",".join("?" * NUMBER_OF_COLS)
insert_sql = "insert into %s values (%s)" % (TABLE_NAME, question_marks)

AND the output from the select should be able to be pumped straight into 
the insert with no changes at all.

AND there might even be a pragma or suchlike that will enable you to 
easily find the number of columns on the fly in your script ...


> 
> We've already fixed the bad code, but there are some customers who have old
> versions...it didn't break very often with the old code, but it does still
> did.
> 
> I haven't tried a select Min or max on the row id but a select count(*)
> returns an error...that's how I know I need to do the row by row recovery
> method.  Select * from mytable also returns an error.
> 
> The tables usually have tens of thousands of rows, sometimes over a couple
> hundred thousand but that's rare.
> 
> What seems to work is that I do a select * from myTable where rowId = 'X'
> incing X until I get an error.  After I get the error, every row higher then
> X also returns an error. So as soon as I get an error, I stop trying to
> recover more rows.

Does this usually mean that you are able to recover almost all of the rows?

> The pull and bind code is just ugly and we don't update our 'recovery
> utility' as quickly as we make changes to the database so it tends to get
> out of data.  That's all.

I don't understand what is "the pull and bind" code and why you would 
need anything other that what I've outlined.

Cheers,
John

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

Re: [sqlite] corrupt database recovery

[Gene]

You are exactly right John...that is indeed what the code looks
like...except we have over 25 columns (it's a flat table).

We've already fixed the bad code, but there are some customers who have old
versions...it didn't break very often with the old code, but it does still
did.

I haven't tried a select Min or max on the row id but a select count(*)
returns an error...that's how I know I need to do the row by row recovery
method.  Select * from mytable also returns an error.

The tables usually have tens of thousands of rows, sometimes over a couple
hundred thousand but that's rare.

What seems to work is that I do a select * from myTable where rowId = 'X'
incing X until I get an error.  After I get the error, every row higher then
X also returns an error. So as soon as I get an error, I stop trying to
recover more rows.

The pull and bind code is just ugly and we don't update our 'recovery
utility' as quickly as we make changes to the database so it tends to get
out of data.  That's all.

I haven't tried a PRAGMA integrity_check; in a long time so I can't remember
what it tells me.  I'll run it again.

Thanks for you comments John!

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of John Machin
Sent: Saturday, April 25, 2009 8:15 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] corrupt database recovery

On 26/04/2009 5:47 AM, Gene wrote:
> Every now and again, we have a database that gets corrupt in the field
(bad
> coding on our end, not sqlite). 
> 

Hi Gene,

The obvious question: why not fix the bad code?

What does 'PRAGMA integrity_check;' say about these corrupt databases?

> When we get one of these corrupt databases, we recover what we can by get
> one row at a time by rowid, like 'select * from mytable where rowid = 1'
and
> we inc the row number every time.  Each row we successfully recover we
> insert into a clean database.

How do you know when to stop incrementing the row number?

Does 'select min(rowid), max(rowid) from mytable' give you believable 
answers?

What happens when you do 'select * from mytable' ?

Approximately how many rows are there?

How many 'select * from mytable where rowid = ' attempts 
fail, and for what reasons (previously deleted, some other result code(s))?

Are the failures restricted to a relatively small range of rowids?

> Works pretty well, except over time we've added more and more columns,
each
> one that has to be pulled and bound to get it into the new table.

What is causing this "pull and bind" problem, the fact that some columns 
  weren't present initially? or just the sheer number of columns i.e. 
you need to "pull and bind" all columns (not just the later additions)? 
In any case, please explain what you mean by "pulled" and "bound".

>  We tried
> to do an 'attach' so we could do a 'select into' the clean database
directly
> from the corrupt one.  But as soon as we attempt to 'attach' the corrupt
> database, we understandable get a 'database is malformed' error.

It's not quite so understandable why 'select * from mytable where rowid 
= 1' doesn't get an error.

> Is there an easier way to pull the good records out of a corrupt database
> and put them into a new one without binding each column by hand?

Can you give us an example of a row or two of (a) what you get from the 
'select * from mytable where rowid = ' (b) the insert 
statement that you need to do to insert that data into the clean 
database? Doesn't have to be real data -- e.g. assume 3 columns 
initially, now grown to 5.

What rules/procedure/recipe do you follow when producing (b) from (a) by 
hand?

Assuming that 'select * from mytable' doesn't work, and subject to 
understanding the pulling and binding by hand thing, I would have 
thought the solution would look something like this:

Write a script that loops around doing 'select * from mytable where 
rowid = ?' on a connection to your corrupt database and doing 'insert 
into mytable values(?,?,?,?,? etc etc etc)' on a connection to your 
clean database.

HTH,
John

___
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] corrupt database recovery

[John Machin]

On 26/04/2009 5:47 AM, Gene wrote:
> Every now and again, we have a database that gets corrupt in the field (bad
> coding on our end, not sqlite). 
> 

Hi Gene,

The obvious question: why not fix the bad code?

What does 'PRAGMA integrity_check;' say about these corrupt databases?

> When we get one of these corrupt databases, we recover what we can by get
> one row at a time by rowid, like 'select * from mytable where rowid = 1' and
> we inc the row number every time.  Each row we successfully recover we
> insert into a clean database.

How do you know when to stop incrementing the row number?

Does 'select min(rowid), max(rowid) from mytable' give you believable 
answers?

What happens when you do 'select * from mytable' ?

Approximately how many rows are there?

How many 'select * from mytable where rowid = ' attempts 
fail, and for what reasons (previously deleted, some other result code(s))?

Are the failures restricted to a relatively small range of rowids?

> Works pretty well, except over time we've added more and more columns, each
> one that has to be pulled and bound to get it into the new table.

What is causing this "pull and bind" problem, the fact that some columns 
  weren't present initially? or just the sheer number of columns i.e. 
you need to "pull and bind" all columns (not just the later additions)? 
In any case, please explain what you mean by "pulled" and "bound".

>  We tried
> to do an 'attach' so we could do a 'select into' the clean database directly
> from the corrupt one.  But as soon as we attempt to 'attach' the corrupt
> database, we understandable get a 'database is malformed' error.

It's not quite so understandable why 'select * from mytable where rowid 
= 1' doesn't get an error.

> Is there an easier way to pull the good records out of a corrupt database
> and put them into a new one without binding each column by hand?

Can you give us an example of a row or two of (a) what you get from the 
'select * from mytable where rowid = ' (b) the insert 
statement that you need to do to insert that data into the clean 
database? Doesn't have to be real data -- e.g. assume 3 columns 
initially, now grown to 5.

What rules/procedure/recipe do you follow when producing (b) from (a) by 
hand?

Assuming that 'select * from mytable' doesn't work, and subject to 
understanding the pulling and binding by hand thing, I would have 
thought the solution would look something like this:

Write a script that loops around doing 'select * from mytable where 
rowid = ?' on a connection to your corrupt database and doing 'insert 
into mytable values(?,?,?,?,? etc etc etc)' on a connection to your 
clean database.

HTH,
John

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

[sqlite] Sort order with umlauts

[Lukas Haase]

Hello,

For a database I also need a fulltext index. This is my table containing 
all the words:

CREATE TABLE db_fulltext.fulltext(
fulltextID INTEGER PRIMARY KEY,
word VARCHAR(100) COLLATE NOCASE
);

Now I have the problem that I have also words with umlauts. Now they are 
sorted this way:

ua
..
uz
..
zz
..
üa
..

But I need the umlauts treated as their respective vovels, i.e.:

ua
üa
..
uz
..
zz

Is this somehow possible?

Greets,
Luke

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

Re: [sqlite] PHP Sqlite2 - random update returns.. caching problem?

[vacuumir]

Hi all, 

Seems that this: 


vacuumir wrote:
> 
> $result = Db_query('PRAGMA synchronous = FULL'); print_r($result);
> 

in fact did work - except I wasn't using a statement that actually returns
the changed state.. To confirm, I use this: 

$ress = Db_query('PRAGMA synchronous = FULL;'); print_r($ress); // prints 1
$ress = fetchFromDb('PRAGMA synchronous;', true); print_r($ress); //prints 2
(for FULL)

and subsequent updates, so far, have been OK... I hope it lasts :) 



-- 
View this message in context: 
http://www.nabble.com/PHP-Sqlite2---random-update-returns..-caching-problem--tp23235937p23236271.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

[sqlite] PHP Sqlite2 - random update returns.. caching problem?

[vacuumir]

Hi all, 

I really don't know where else to post with this problem. I am using a PHP5
application, which uses the Sqlite2 built-in (2.8.16), and am using queries
via 

Class: PDO for PHP 4 
http://www.phpclasses.org/browse/package/2572.html

which simply call sqlite_fetch_array or sqlite_query.. Initially, all seemed
good, but now, I am noticing the following things. Say I have a database
file which has some data which behaves OK. I do an update on the server.
Then I reload/refresh the page. Randomly upon subsequent refreshes of the
page, sometimes the updated data is shown - and sometimes the data from the
previous, 'stable' state. 

Now, I download this sqlite file, and open it with SQLite Database Browser
(SDB), and I make a null edit in a field (i.e. open a field, press Space,
and press delete - so no changes are made, but the software still recognizes
a change and allows for saving) and then save the database file. If I now
upload to the server and overwrite the previous database, now this state
becomes the 'stable' one, and subsequent updates and page refreshes start
behaving randomly ??!!

Even worse is this: when after a refresh I get a non-updated state, and I
download the database, it does not show the updated data (in SDB). I hit
refresh in my browser a couple of times until I get the updated data, and I
download the same database file again - now the updated data is shown in
SDB??

But the worst must be, that when I delete the database file from the server
altogether, the php webpage sometimes *still* shows data from the last
'stable' state ???!!! (and sometimes it is empty, as expected).. 

I tried to read around, but cannot find the exact same problem - does anyone
have any idea why this is happening and how to fix it (note I have no
control over the server, and so I'm forced to use the built-in sqlite there)
?? I tried to put in something like:

$result = Db_query('PRAGMA synchronous = FULL'); print_r($result);

at the start of my php page - the return value is '1', but that doesn't seem
to help much.. 

Any suggestions will be greatly appreciated !!

Thanks in advance... 



-- 
View this message in context: 
http://www.nabble.com/PHP-Sqlite2---random-update-returns..-caching-problem--tp23235937p23235937.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

[sqlite] corrupt database recovery

[Gene]

Every now and again, we have a database that gets corrupt in the field (bad
coding on our end, not sqlite). 

 

When we get one of these corrupt databases, we recover what we can by get
one row at a time by rowid, like 'select * from mytable where rowid = 1' and
we inc the row number every time.  Each row we successfully recover we
insert into a clean database.

 

Works pretty well, except over time we've added more and more columns, each
one that has to be pulled and bound to get it into the new table.  We tried
to do an 'attach' so we could do a 'select into' the clean database directly
from the corrupt one.  But as soon as we attempt to 'attach' the corrupt
database, we understandable get a 'database is malformed' error.

 

Is there an easier way to pull the good records out of a corrupt database
and put them into a new one without binding each column by hand?

 

Many thanks,

 

Gene

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

Re: [sqlite] Indexing for sums?

[Igor Tandetnik]

"Nikolas Stevenson-Molnar"
 wrote in message
news:f45a26bc-1ee8-4a72-a90a-77f40eef6...@evergreen.edu
> If I have the following table:
>
> CREATE TABLE stem(sid integer primary key, x double, y double, dbh
> double);
>
> ... is there any way I can create an index for the following query?
>
> SELECT * FROM stem WHERE x + dbh > 20

No, not really. If you need this query often, you may want to add a 
column to store (x+dbh), and index that.

Igor Tandetnik 



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

[sqlite] Indexing for sums?

[Nikolas Stevenson-Molnar]

Hi all,

If I have the following table:

CREATE TABLE stem(sid integer primary key, x double, y double, dbh  
double);

... is there any way I can create an index for the following query?

SELECT * FROM stem WHERE x + dbh > 20

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

Re: [sqlite] Newbie question about using SQLite with Windows Forms application (VS 2005 C++)

[Vinnie]

> From: wiktor 
> Subject: [sqlite] Newbie question about using SQLite with
> Windows Forms application (VS 2005 C++)
> I'm trying to build a win form application that uses
> sqlite. I have problems with making it  work. I would like
> to have the sqlite source included in my project (as .h file
> or dll) - sth similar to (but done by a function)
> http://www.sqlite.org/quickstart.html. As I have read on
> internet sources it shall be possible. 

I have never used Windows Forms but from what I understand it is a user 
interface toolkit for .NET. So you will need to access SQLite from .NET. There 
are a few ways of going about this.

Here is one .NET wrapper for SQLite:
http://www.phpguru.org/static/SQLite.NET.html

The SQLite website has some instructions for building SQLite with Visual Studio 
.NET:
http://www.sqlite.org/cvstrac/wiki?p=HowToCompileWithVsNet

Hope this helps!

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

[sqlite] Newbie question about using SQLite with Window s Forms application (VS 2005 C++)

[wiktor]

Hi, 

I'm trying to build a win form application that uses sqlite. I have problems 
with making it  work. I would like to have the sqlite source included in my 
project (as .h file or dll) - sth similar to (but done by a function) 
http://www.sqlite.org/quickstart.html. As I have read on internet sources it 
shall be possible. 

Can anyone provide me with step by step guide on how to do it in Visual Studio 
C++ 2005?

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