Re: [sqlite] Sqlite3 command shell dump possible bug

2012-03-03 Thread Steffen Mangold 
Hi Simon,


thanks for your help. All worked now :) 
I only lost some data at the end of the table, not so bad.

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

Re: [sqlite] Sqlite3 command shell dump possible bug

2012-03-02 Thread Simon Slavin 

On 2 Mar 2012, at 10:45pm, Steffen Mangold  wrote:

> I now delete the malform message and the rollback command from the *.sql file 
> and run ".read".

Okay ...

> Sqlite shell runs complete and the shell ask me for new command "> " (DB file 
> seems to have the right size.
> I'm happy now and enter command ".exit" and bam Db file has 0kb?!?!?
> What have i done wrong? Any Commit command or something?

By all means enter 'COMMIT;' as a command after your '.read' has finished.  
Worst it can do is issue an error message.  Hmm.  Yes, if the file you read 
contains a BEGIN and then a ROLLBACK then the shell tool may not have the logic 
to realise that the end of the file means there should probably be a COMMIT 
somewhere.

Then, before your '.exit' command, try a '.tables' command and see if the 
database now has tables in.

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

Re: [sqlite] Sqlite3 command shell dump possible bug

2012-03-02 Thread Roger Binns 
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/03/12 15:32, Steffen Mangold wrote:
> how to "replace" this?

Change the abort to commit as others pointed out.  The dump code does the
following:

print BEGIN
foreach table in the database:
   foreach row in the table:
 print the row
print COMMIT

However if there is an error returned (SQLITE_CORRUPT) while iterating
over each row then it may start from the end and iterate backwards.
Instead of commit, it prints ABORT on failing to completely iterate over
the table.

> But sometime my DB corrupted and .dump repairs it for me without data
> loose. (was only some index errors).

How do you know there is no data loss?  You could only know that if you
had a good clean copy of the entire database, in which case you can just
use that.

The "index errors" are not the only errors.  They are only the ones
reported.  There can be silent errors, undetected corruption, stale pages
and who knows what else.

While doing a dump SQLite does not look at the contents of indices.  If
corruption was only inside an index then it would not be detected during
.dump and you could repair by dropping and recreating the index.

> But this time a part of the database goes really corrupt, so i now this
> data is lost.

The errors reported are not all the errors that exist, only a subset of them,

> The damage has come with a server blackout because USV failure. I now
> from docu that this can heavily damage a SQLite DB.

A UPS failure will not corrupt a SQLite database, nor will other forms of
power failure.

You can get corruption if the drive or controller lie about content being
synced during a power failure, but you should fix the drive/controller.

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

iEYEARECAAYFAk9RXQYACgkQmOOfHg372QQOFQCgighDzftxJVxe0RGNANZMzoln
Jw4AoLz+Q237aPK6aoQ8/nwKRjnxbnyt
=WohS
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite3 command shell dump possible bug

2012-03-02 Thread Steffen Mangold 
>
> You should be able to, yes. Just type in "END;" (without quotes, but with 
> semicolon).
>

Ok thank you i will try :) (in a few hours because DB is so big. :) 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite3 command shell dump possible bug

2012-03-02 Thread Roger Binns 
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/03/12 14:45, Steffen Mangold wrote:
> I'm happy now and enter command ".exit" and bam Db file has 0kb?!?!? 
> What have i done wrong? Any Commit command or something?

What you don't seem to understand is that your original database is
corrupt.  The data that is in it is not exactly what SQLite (and hence
your program) put in it.  While doing the dump, the corruption was
discovered and the dump aborted.  You just replaced the abort command with
"save what we have so far".

There is no way for SQLite to recover what is changed/missing/corrupted.
You'll need to work out what the damage has been.  The integrity check
only examines the top level structure - it does not detect data changes
(mostly).  For example if every 'a' had been changed to a 'b' it would not
detect that.

You should also work out how the corruption happened since you really
don't want it to happen again.  Here is how to corrupt a SQLite database:

  http://www.sqlite.org/howtocorrupt.html

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

iEYEARECAAYFAk9RVc8ACgkQmOOfHg372QQQ3gCfZBxh6oxaZ2OXhYDB9xsK7+BT
38IAnj7aiKugfj1w6/L1GbWfvkAsAz9/
=wWdx
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite3 command shell dump possible bug

2012-03-02 Thread Igor Tandetnik 

On 3/2/2012 5:57 PM, Steffen Mangold wrote:


If you have a BEGIN command in your script, then you should also have END or 
COMMIT at the end (the two are synonyms).



can i do this by shell command after ".read" if my SQL script has miss that?


You should be able to, yes. Just type in "END;" (without quotes, but 
with semicolon).

--
Igor Tandetnik

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

Re: [sqlite] Sqlite3 command shell dump possible bug

2012-03-02 Thread Steffen Mangold 
>
> If you have a BEGIN command in your script, then you should also have END or 
> COMMIT at the end (the two are synonyms).
>

can i do this by shell command after ".read" if my SQL script has miss that?

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

Re: [sqlite] Sqlite3 command shell dump possible bug

2012-03-02 Thread Igor Tandetnik 

On 3/2/2012 5:45 PM, Steffen Mangold wrote:

WHAT THE  !

I now delete the malform message and the rollback command from the *.sql file and run 
".read".
Sqlite shell runs complete and the shell ask me for new command ">  " (DB file 
seems to have the right size.
I'm happy now and enter command ".exit" and bam Db file has 0kb?!?!?
What have i done wrong? Any Commit command or something?


If you have a BEGIN command in your script, then you should also have 
END or COMMIT at the end (the two are synonyms).

--
Igor Tandetnik

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

Re: [sqlite] Sqlite3 command shell dump possible bug

2012-03-02 Thread Steffen Mangold 
WHAT THE  !

I now delete the malform message and the rollback command from the *.sql file 
and run ".read".
Sqlite shell runs complete and the shell ask me for new command "> " (DB file 
seems to have the right size.
I'm happy now and enter command ".exit" and bam Db file has 0kb?!?!?
What have i done wrong? Any Commit command or something?

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

Re: [sqlite] Sqlite3 command shell dump possible bug

2012-03-02 Thread Simon Slavin 

On 2 Mar 2012, at 7:08pm, Steffen Mangold  wrote:

> Ok maybe i found it in the sql file is written (file end):
> 
> [...]
> INSERT INTO "InverterData" VALUES(2478,'2012-02-28 
> 15:00:00',1435.73,429173.78,170.28,170.75,169.38,397.56,397.38,396.69,NULL,210976,31,NULL,NULL,1,304,NULL,NULL,NULL,694,NULL,NULL,NULL);
> / ERROR: (11) database disk image is malformed */
> / ERROR: (11) database disk image is malformed */
> CREATE TRIGGER SensorData_InsertUpdate
> [...]
> ROLLBACK; -- due to errors
> 
> So sqlite shell can not understand "/ ERROR: (11) database disk image is 
> malformed */" I think.
> and make a rollback?
> 
> Do you think that's it?

Yes, that's probably what's causing the errors.  If you remove those lines (or 
perhaps just the ROLLBACK) from the text file then you might get a successful 
'.read' session.

But what it means is that the original database ... the one you did a '.dump' 
of, was so corrupt that the shell tool couldn't dump it to a text file 
successfully.  So your .sql file may not be any use since it is missing at 
least some of the data, perhaps an entire table or some other structure.  If 
you need absolutely complete data from it you may need expert help in trying to 
rescue data from the original database file.  On the other hand if you 
understand the data structure yourself you might be able to patch it up to a 
state good enough to let you continue.

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

Re: [sqlite] Sqlite3 command shell dump possible bug

2012-03-02 Thread Steffen Mangold 
Ok maybe i found it in the sql file is written (file end):

[...]
INSERT INTO "InverterData" VALUES(2478,'2012-02-28 
15:00:00',1435.73,429173.78,170.28,170.75,169.38,397.56,397.38,396.69,NULL,210976,31,NULL,NULL,1,304,NULL,NULL,NULL,694,NULL,NULL,NULL);
/ ERROR: (11) database disk image is malformed */
/ ERROR: (11) database disk image is malformed */
CREATE TRIGGER SensorData_InsertUpdate
[...]
ROLLBACK; -- due to errors

So sqlite shell can not understand "/ ERROR: (11) database disk image is 
malformed */" I think.
and make a rollback?

Do you think that's it?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite3 command shell dump possible bug

2012-03-02 Thread Steffen Mangold 
>
> Is it very long ?  Can you read it with a dump utility or a text editor 
> (don't try it with a word processor) and see the SQL commands in it ?
>

Yes 14 GB. 4 Table, roundabout 200.000.000 inserts.

I opened it with a textviewer for large files. Sql seams well formed and 
readable till the end.

I now make a complet integrity_check.
Only four error types:

On tree page xxx cell xx: invalid page number
On tree page xxx cell xx: child page depth differs
On page xxx at right child: child page depth differs
On page xxx at right child: invalid page number xxx

But this error round about 100 times.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite3 command shell dump possible bug

2012-03-02 Thread Simon Slavin 

On 2 Mar 2012, at 6:30pm, Steffen Mangold  wrote:

>> Are you saying it creates a database file but doesn't put anything into it 
>> (zero filesize) or that it doesn't even create a blank file ?
> 
> With dump its write the complete DB File new but nearly at the end (new DD 
> file size compared to the malformed) Sqlite shell breaks and set the file 
> size of the new DB to 0kb.
> No error is written. :(

Okay.  Take that long text file (db.sql ?) and use a text editor or some other 
tool to split it into two.  Perhaps you can find a break point where it stops 
creating one table and starts creating another one.  While you're doing that, 
you can look at it by eye and see if the CREATE TABLE commands look right to 
you.

Then you can '.read' the first half, see if that worked, then '.read' the 
second half separately.  Perhaps one half will work and not the other.

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

Re: [sqlite] Sqlite3 command shell dump possible bug

2012-03-02 Thread Steffen Mangold 
>
> Are you saying it creates a database file but doesn't put anything into it 
> (zero filesize) or that it doesn't even create a blank file ?
>
With dump its write the complete DB File new but nearly at the end (new DD file 
size compared to the malformed) Sqlite shell breaks and set the file size of 
the new DB to 0kb.
No error is written. :(
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite3 command shell dump possible bug

2012-03-02 Thread Simon Slavin 

On 2 Mar 2012, at 6:16pm, Steffen Mangold  wrote:

>> There's no magic tool for repairing damaged database files.  But by using 
>> the .dump command (if necessary on each individual table and view) then 
>> creating a new database file and using the .read command you can often 
>> rescue some or all of the data in the original > > database.
>> 
> 
> Ok, with .dumb i now created a "db.sql" file successfully. 

Is it very long ?  Can you read it with a dump utility or a text editor (don't 
try it with a word processor) and see the SQL commands in it ?

> but I don't get the read command!? How create a new DB file with that command?
> With "sqlite> .read db.sql" it does much reading but no file is created.

Start the shell tool supplying it with the name for a new blank database file 
(I think you were using fred.temp in your earlier example).  Then do a '.read 
db.sql'.

prompt$ sqlite3 db.temp
SQLite version 3.7.7 2011-10-10 22:11:44
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .read db.sql

Then do something like a '.schema' and see if it has created the tables you 
expected.
Then quit the shell tool using '.quit' and see if the file you created exists.

Are you saying it creates a database file but doesn't put anything into it 
(zero filesize) or that it doesn't even create a blank file ?

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

Re: [sqlite] Sqlite3 command shell dump possible bug

2012-03-02 Thread Steffen Mangold 
>
> Ok, with .dumb i now created a "db.sql" file successfully. 
> but I don't get the read command!? How create a new DB file with that command?
> With "sqlite> .read db.sql" it does much reading but no file is created.
>

Ok I get it, must attach a DB first.
now sqlite writes the data to the DB, hopes this helps.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite3 command shell dump possible bug

2012-03-02 Thread Steffen Mangold 
>
> There's no magic tool for repairing damaged database files.  But by using the 
> .dump command (if necessary on each individual table and view) then creating 
> a new database file and using the .read command you can often rescue some or 
> all of the data in the original > > database.
>

Ok, with .dumb i now created a "db.sql" file successfully. 
but I don't get the read command!? How create a new DB file with that command?
With "sqlite> .read db.sql" it does much reading but no file is created.

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

Re: [sqlite] Sqlite3 command shell dump possible bug

2012-03-02 Thread Simon Slavin 

On 2 Mar 2012, at 2:36pm, Steffen Mangold  wrote:

> I read in some forums that .dumb is the best way to repair "malformed" DBs. 
> Do you have an other way?

It doesn't repair anything.  And the .dump command may, or may not, work on a 
malformed database file.  It depends on what'sw wrong with the database file.  
Might help if you understood what .dump does.

http://www.sqlite.org/sqlite.html

It reads a SQLite database and produces the SQL instructions it would take to 
reproduce that database.  So you start off with a database file in SQLite 
format and end up with a huge file of text containing SQL instructions.  Since 
a human can understand that file you can then read that text file yourself, or 
use a text editor to make changes to it before using the .read command to 
execute the instructions and make another database from them.

There's no magic tool for repairing damaged database files.  But by using the 
.dump command (if necessary on each individual table and view) then creating a 
new database file and using the .read command you can often rescue some or all 
of the data in the original database.

One thing you might like to try is "PRAGMA integrity_check;"



which will give you a good idea about what's wrong with the original database 
file.  It might tell you which table is defective, or whether the problem is 
with data (which may lead to problems reconstructing the file) or an index 
(which will probably be ignored when reconstructing the file).

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

Re: [sqlite] Sqlite3 command shell dump possible bug

2012-03-02 Thread Steffen Mangold 
>
> First, get all the other databases done, so you're worried only about the one 
> which doesn't work.
>
> Then do the .dump part for that database, putting the output into a file on 
> disk, which should leave you with a huge file of SQL commands which should 
> rebuild it.
>
> It's likely that the .dump stage will fail because your original database is 
> corrupt.  That's your problem.
>
> If it doesn't fail, split it up into parts, and rebuild your database using 
> '.read' by reading the parts in one at a time.  One of those parts should 
> cause an error message or a crash.  That's your problem.
>
> Either way, you should be able to narrow down the possible scope for the 
> crash.

Thank you simon i try this,

I read in some forums that .dumb is the best way to repair "malformed" DBs. Do 
you have an other way?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite3 command shell dump possible bug

2012-03-02 Thread Simon Slavin 

On 2 Mar 2012, at 1:20pm, Steffen Mangold  wrote:

> i have a problem with the sqlite2.exe under windows.
> Ok, I have here 20 corrupted DBs and want to repair they all.
> I do this with CMD and the command ".dump | sqlite3 rebuild.db3 | sqlite3 
> rebuild.temp"
> 
> This works perfect for all DBs except one.
> The DB where it is not working has a size of 15GB.
> During processing I can see the "rebuild.temp" is going bigger and bigger.
> But at the end the hole file is set empty!! It has a size of 0kb after 
> sqlite3.exe is finished?!?!

First, get all the other databases done, so you're worried only about the one 
which doesn't work.

Then do the .dump part for that database, putting the output into a file on 
disk, which should leave you with a huge file of SQL commands which should 
rebuild it.

It's likely that the .dump stage will fail because your original database is 
corrupt.  That's your problem.

If it doesn't fail, split it up into parts, and rebuild your database using 
'.read' by reading the parts in one at a time.  One of those parts should cause 
an error message or a crash.  That's your problem.

Either way, you should be able to narrow down the possible scope for the crash.

Simon.

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