Re: [sqlite] Problems (maybe a bug) when creating a backup using the command line interface '.backup' command

[Marco ten Thije]

On 12/21/2012 05:18 PM, Dan Kennedy wrote:


Thanks. I think it's this:

  http://www.sqlite.org/src/info/0cfd98ee20

Dan.
You are right. I have been re-reading our mail thread and this is 
exactly what happens. I have build a SQLite
version from the latest version in the archive (3.7.16) and this version 
creates a correct backup.


Thanks for the fix.

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

Re: [sqlite] Problems (maybe a bug) when creating a backup using the command line interface '.backup' command

[Marco ten Thije]

On 01/02/2013 11:03 AM, Marco ten Thije wrote:

On 01/02/2013 10:04 AM, Dan Kennedy wrote:

What series of commands did you pass to 3.7.5 to create
this file?
This file was copied from an existing product which uses QT 4.7 
libraries to populate the database. I cannot
reproduce the exact commands which resulted in this file, because it 
is populated dynamically by the

software.

I found out that QT 4.7 is using the 3.6.19 SQLite library. The 
databases were created using the 3.7.5 version,
so you were right about this. The database is created with a 3.7.5 
version and populated with 3.6.19


I did some more testing and found out that the backup database which 
SQLite 3.7.5 reports to be corrupted, is
reported OK by SQLite 3.6.19, although this backup was created by the 
3.7.5 SQLite version.


I created a ASCII dump (using the .dump command) of the backup database 
using 3.6.19 and this dump is
identical (except for the 'PRAGMA foreign_keys=OFF') to a dump of the 
original database.


So it looks like SQLITE 3.7.5 and 3.7.15.1 (and probably all versions in 
between) have a problem reading the backup file,

while 3.6.19 can read it.

I have stored a copy of the original database on 
http://www.cbbio.nl/sqlite/energy.sqlite for further analysing


Regards,

Marco ten Thije


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

Re: [sqlite] Problems (maybe a bug) when creating a backup using the command line interface '.backup' command

[Marco ten Thije]

On 01/02/2013 10:04 AM, Dan Kennedy wrote:

What series of commands did you pass to 3.7.5 to create
this file?
This file was copied from an existing product which uses QT 4.7 
libraries to populate the database. I cannot
reproduce the exact commands which resulted in this file, because it is 
populated dynamically by the

software.

I found out that QT 4.7 is using the 3.6.19 SQLite library. The 
databases were created using the 3.7.5 version,
so you were right about this. The database is created with a 3.7.5 
version and populated with 3.6.19



Perhaps there is a bug in 3.7.5 causing this. What happens
if you use 3.7.15 to create the original and do the backup?
I have recreated the database using a dump from the original database. I 
have created database using
this dump with both 3.7.5 (on a ARM9 platform) and 3.7.15.1 (on Intel). 
To my surprise this resulted in quite
different files. The one created with 3.7.5 is 125952 bytes in size, the 
3.7.15.1 is 116736 bytes in size.


These are the first 100 bytes in hex of both files:

3.7.5:
000 5153 694c 6574 6620 726f 616d 2074 0033
010 0004 0101 4000 2020  0100  7b00
020      0300  0100
030      0100  
040        
050        0100
060 2d00 1de2 000d  0004 00e3 7202 2002
070 7301 e300      
080        

3.7.15.1:
000 5153 694c 6574 6620 726f 616d 2074 0033
010 0004 0101 4000 2020  0100  7200
020      0300  0400
030      0100  
040        
050        0100
060 2d00 27e2 000d  0004 00e3 7202 2002
070 7301 e300      
080        

The created database files are both different from the one created with 
the .backup command. Both are reporting
'ok' with 'pragma integrity_check;'. The one created with .backup report 
errors:


*** in database main ***
Main freelist: invalid page number 151
On tree page 10 cell 10: invalid page number 154
On tree page 10 cell 10: Child page depth differs
On tree page 10 cell 11: invalid page number 145
On tree page 10 cell 12: Child page depth differs
On tree page 10 cell 13: invalid page number 120
On tree page 10 cell 13: Child page depth differs
On tree page 10 cell 14: invalid page number 121
On tree page 10 cell 15: invalid page number 152
On tree page 10 cell 16: Child page depth differs
On tree page 10 cell 18: invalid page number 157
On tree page 10 cell 18: Child page depth differs
On tree page 10 cell 19: invalid page number 153
On tree page 10 cell 20: Child page depth differs
On tree page 10 cell 21: invalid page number 141
On tree page 10 cell 21: Child page depth differs
On tree page 10 cell 22: Child page depth differs
On tree page 10 cell 53: invalid page number 132
On tree page 10 cell 53: Child page depth differs
On tree page 10 cell 54: Child page depth differs
On tree page 11 cell 0: invalid page number 143
On tree page 11 cell 1: invalid page number 129
On tree page 11 cell 2: Child page depth differs
On tree page 11 cell 4: invalid page number 125
On tree page 11 cell 4: Child page depth differs
On tree page 11 cell 5: Child page depth differs
On tree page 11 cell 9: invalid page number 122
On tree page 11 cell 9: Child page depth differs
On tree page 11 cell 10: Child page depth differs
On tree page 11 cell 11: invalid page number 119
On tree page 11 cell 11: Child page depth differs
On tree page 11 cell 12: Child page depth differs
On tree page 11 cell 18: invalid page number 128
On tree page 11 cell 18: Child page depth differs
On tree page 11 cell 19: invalid page number 149
On tree page 11 cell 20: Child page depth differs
On tree page 11 cell 24: invalid page number 123
On tree page 11 cell 24: Child page depth differs
On tree page 11 cell 25: Child page depth differs
On tree page 11 cell 28: invalid page number 139
On tree page 11 cell 28: Child page depth differs
On tree page 11 cell 29: Child page depth differs
On tree page 11 cell 36: invalid page number 137
On tree page 11 cell 36: Child page depth differs
On tree page 11 cell 37: invalid page number 156
On tree page 11 cell 38: invalid page number 147
On tree page 11 cell 39: invalid page number 146
On tree page 11 cell 40: Child page depth differs
On tree page 11 cell 44: invalid page number 155
On tree page 11 cell 44: Child page depth differs
On tree page 11 cell 45: Child page depth differs
On tree page 11 cell 53: invalid page number 136
On tree page 11 cell 53: Child page depth differs
On tree page 11 cell 54: Child page depth differs
On page 11 at right child: invalid page number 138
Page 32 is never used
Page 41 is never used
Page 46 is never used
Page 48 is never used
Page 51 is never used
Page 53 is never used
Page 

Re: [sqlite] Problems (maybe a bug) when creating a backup using the command line interface '.backup' command

[Dan Kennedy]

On 01/02/2013 03:27 PM, Marco ten Thije wrote:

On 12/21/2012 05:18 PM, Dan Kennedy wrote:

Thanks. I think it's this:

http://www.sqlite.org/src/info/0cfd98ee20

Dan.

Thanks. I have looked into ticket, but we also see this problem when the
backup is written and read by the
same SQLite version (3.7.5).

I have recreated a backup using the 3.7.5 version. These are the
hexdumps of both the original file and
the backup created with SQLite 3.7.5:

The original file:

000 5153 694c 6574 6620 726f 616d 2074 0033
010 0004 0101 4000 2020  f1d0  7600
020  9700  2100  0300  0100
030      0100  
040        
050        0200
060 2d00 1de2 000d  0004 00e3 7202 2002
070 7301 e300      
080        


What series of commands did you pass to 3.7.5 to create
this file?

That the change counters at byte offsets 24 and 92 are
different makes this file look like it was created using
3.7.5 and then populated using an older version.

Perhaps there is a bug in 3.7.5 causing this. What happens
if you use 3.7.15 to create the original and do the backup?

Dan.

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

Re: [sqlite] Problems (maybe a bug) when creating a backup using the command line interface '.backup' command

[Marco ten Thije]

On 12/21/2012 05:18 PM, Dan Kennedy wrote:

Thanks. I think it's this:

  http://www.sqlite.org/src/info/0cfd98ee20

Dan.
Thanks. I have looked into ticket, but we also see this problem when the 
backup is written and read by the

same SQLite version (3.7.5).

I have recreated a backup using the 3.7.5 version. These are the 
hexdumps of both the original file and

the backup created with SQLite 3.7.5:

The original file:

000 5153 694c 6574 6620 726f 616d 2074 0033
010 0004 0101 4000 2020  f1d0  7600
020  9700  2100  0300  0100
030      0100  
040        
050        0200
060 2d00 1de2 000d  0004 00e3 7202 2002
070 7301 e300      
080        

The backup created with SQLite 3.7.5:

000 5153 694c 6574 6620 726f 616d 2074 0033
010 0004 0101 4000 2020  0100  7600
020  9700  2100  0100  0100
030      0100  
040        
050        0100
060 2d00 1de2 000d  0004 00e3 7202 2002
070 7301 e300      
080        

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

Re: [sqlite] Problems (maybe a bug) when creating a backup using the command line interface '.backup' command

[Dan Kennedy]

On 12/21/2012 10:54 PM, Marco ten Thije wrote:



What is the size of the two database files?

The size of both files is 160768 bytes.


Also, can we have the first 6 lines of each hex dump
(i.e. enough to see the first 100 bytes)?

The first bytes of the original database:

000 5153 694c 6574 6620 726f 616d 2074 0033
010 0004 0101 4000 2020  f1d0  7600
020  9700  2100  0300  0100
030      0100  
040        
050        0200
060 2d00 1de2 000d  0004 00e3 7202 2002
070 7301 e300      
080        

The first bytes of the backup database:

000 5153 694c 6574 6620 726f 616d 2074 0033
010 0004 0101 4000 2020  0100  7600
020  9700  2100  0100  0100
030      0100  
040        
050        0100
060 2d00 27e2 000d  0004 00e3 7202 2002
070 7301 e300      
080        


Thanks. I think it's this:

  http://www.sqlite.org/src/info/0cfd98ee20

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

Re: [sqlite] Problems (maybe a bug) when creating a backup using the command line interface '.backup' command

[Marco ten Thije]

What is the size of the two database files?

The size of both files is 160768 bytes.


Also, can we have the first 6 lines of each hex dump
(i.e. enough to see the first 100 bytes)?

The first bytes of the original database:

000 5153 694c 6574 6620 726f 616d 2074 0033
010 0004 0101 4000 2020  f1d0  7600
020  9700  2100  0300  0100
030      0100  
040        
050        0200
060 2d00 1de2 000d  0004 00e3 7202 2002
070 7301 e300      
080        

The first bytes of the backup database:

000 5153 694c 6574 6620 726f 616d 2074 0033
010 0004 0101 4000 2020  0100  7600
020  9700  2100  0100  0100
030      0100  
040        
050        0100
060 2d00 27e2 000d  0004 00e3 7202 2002
070 7301 e300      
080        

I can send the binary database, but the mailinglist strips the attachments.

Best regards,

Marco ten Thije



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

Re: [sqlite] Problems (maybe a bug) when creating a backup using the command line interface '.backup' command

[Dan Kennedy]

On 12/21/2012 08:46 PM, Marco ten Thije wrote:

It returns 'ok':

./sqlite3 energy.sqlite
SQLite version 3.7.15.1 2012-12-19 20:39:10
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> pragma integrity_check;
ok
sqlite>

So, the original database looks ok.

I have compared the two databases (original and backup) and they
only differ in a few bytes in the header. The sizes match.

This is a diff between the hexdumps of both databases:

diff energy.hex backup.hex


What is the size of the two database files?

Also, can we have the first 6 lines of each hex dump
(i.e. enough to see the first 100 bytes)?

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

Re: [sqlite] Problems (maybe a bug) when creating a backup using the command line interface '.backup' command

[Marco ten Thije]

It returns 'ok':

./sqlite3 energy.sqlite
SQLite version 3.7.15.1 2012-12-19 20:39:10
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> pragma integrity_check;
ok
sqlite>

So, the original database looks ok.

I have compared the two databases (original and backup) and they
only differ in a few bytes in the header. The sizes match.

This is a diff between the hexdumps of both databases:

 diff energy.hex backup.hex
2,3c2,3
< 010 0004 0101 4000 2020  f1d0  7600
< 020  9700  2100  0300  0100
---
> 010 0004 0101 4000 2020  0100  7600
> 020  9700  2100  0100  0100
6,7c6,7
< 050        0200
< 060 2d00 1de2 000d  0004 00e3 7202 2002
---
> 050        0100
> 060 2d00 27e2 000d  0004 00e3 7202 2002



On 12/21/2012 02:34 PM, Eric Minbiole wrote:

Can you please try running "pragma integrity_check;" on the original
database?  That will give an indication of whether the original database
has any data corruption.


On Fri, Dec 21, 2012 at 4:13 AM, Marco ten Thije<
marco.tenth...@usetechnology.nl>  wrote:


Hello,

We are facing a problem with the '.backup' command using the SQLite
command line interface. The resulting backup-database seems to be corrupt.

We ran into this problem on a ARM9 platform using SQLite 3.7.5, but it can
also be reproduced on the latest 3.7.15.1 version on Intel.

I have attached our database to this email.  These are the steps to
reproduce it:

./sqlite3 energy.sqlite
SQLite version 3.7.15.1 2012-12-19 20:39:10
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>  select count(*) from EnergyLogData;
3306
sqlite>  .backup backup.sqlite
sqlite>  .exit

The above shows the number of records in one of the tables and creates a
backup to a new 'backup.sqlite' database file. But this new file
seems to be corrupt:

./sqlite3 backup.sqlite
SQLite version 3.7.15.1 2012-12-19 20:39:10
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>  select count(*) from EnergyLogData;
Error: database disk image is malformed
sqlite>  .exit

I have look into the Bug database and found ticket 3858, but that one
should already be fixed and we are not writing to the database during
the backup.

Is this a bug or some other problem?

--
Regards,
Marco ten Thije
--**--**


*Use system engineering bv*
Loc: Industriestraat 77 - 7482 EW HAAKSBERGEN - The Netherlands  Mail
Address: Postbus 236 - 7480 AE HAAKSBERGEN - The Netherlands
T: +31 53 5741456  F: +31 53 5741458   E: marco.tenthije@usetechnology.**
nl  >I:
www.usetechnology.nl
Chamber of Commerce nr: Veluwe en Twente 528 98210  VAT nr.: NL
8506.54.713B01
--**--**

/This message (including any attachments) is confidential and may be
privileged. If you have received it by mistake please notify the sender by
return e-mail and delete this message from your system. Any unauthorised
use or dissemination of this message in whole or in part is strictly
prohibited. Please note that e-mails are susceptible to change. Use system
engineering bv shall not be liable for the improper or incomplete
transmission of the information contained in this communication nor for any
delay in its receipt or damage to your system. Use system engineering bv
does not guarantee that the integrity of this communication has been
maintained nor that this communication is free of viruses, interceptions or
interference. /

___
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



--
Met een vriendelijke groet,
Marco ten Thije


*Use system engineering bv*
Bezoekadres: Industriestraat 77 - 7482 EW HAAKSBERGEN - The 
Netherlands  Postadres: Postbus 236 - 7480 AE HAAKSBERGEN - The Netherlands
T: 053 5741456  F: 053 5741458   E: marco.tenth...@usetechnology.nl 
   I: www.usetechnology.nl 


KvK nr: Veluwe en Twente 528 98210  BTW nr.: NL 8506.54.713B01

/Dit bericht (inclusief de eventuele bijlagen) is vertrouwelijk. Wanneer 
u dit bericht ten onrechte heeft ontvangen, dient u de afzender hiervan 
onmiddellijk per kerende e-mail op de hoogte te brengen en dit bericht 
te verwijderen uit uw systeem. Elk onbevoegd gebruik en/of 

Re: [sqlite] Problems (maybe a bug) when creating a backup using the command line interface '.backup' command

[Eric Minbiole]

Can you please try running "pragma integrity_check;" on the original
database?  That will give an indication of whether the original database
has any data corruption.


On Fri, Dec 21, 2012 at 4:13 AM, Marco ten Thije <
marco.tenth...@usetechnology.nl> wrote:

> Hello,
>
> We are facing a problem with the '.backup' command using the SQLite
> command line interface. The resulting backup-database seems to be corrupt.
>
> We ran into this problem on a ARM9 platform using SQLite 3.7.5, but it can
> also be reproduced on the latest 3.7.15.1 version on Intel.
>
> I have attached our database to this email.  These are the steps to
> reproduce it:
>
> ./sqlite3 energy.sqlite
> SQLite version 3.7.15.1 2012-12-19 20:39:10
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> select count(*) from EnergyLogData;
> 3306
> sqlite> .backup backup.sqlite
> sqlite> .exit
>
> The above shows the number of records in one of the tables and creates a
> backup to a new 'backup.sqlite' database file. But this new file
> seems to be corrupt:
>
> ./sqlite3 backup.sqlite
> SQLite version 3.7.15.1 2012-12-19 20:39:10
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> select count(*) from EnergyLogData;
> Error: database disk image is malformed
> sqlite> .exit
>
> I have look into the Bug database and found ticket 3858, but that one
> should already be fixed and we are not writing to the database during
> the backup.
>
> Is this a bug or some other problem?
>
> --
> Regards,
> Marco ten Thije
> --**--**
> 
>
> *Use system engineering bv*
> Loc: Industriestraat 77 - 7482 EW HAAKSBERGEN - The Netherlands  Mail
> Address: Postbus 236 - 7480 AE HAAKSBERGEN - The Netherlands
> T: +31 53 5741456  F: +31 53 5741458   E: marco.tenthije@usetechnology.**
> nl   usetechnology.nl >   I:
> www.usetechnology.nl 
> Chamber of Commerce nr: Veluwe en Twente 528 98210  VAT nr.: NL
> 8506.54.713B01
> --**--**
> 
> /This message (including any attachments) is confidential and may be
> privileged. If you have received it by mistake please notify the sender by
> return e-mail and delete this message from your system. Any unauthorised
> use or dissemination of this message in whole or in part is strictly
> prohibited. Please note that e-mails are susceptible to change. Use system
> engineering bv shall not be liable for the improper or incomplete
> transmission of the information contained in this communication nor for any
> delay in its receipt or damage to your system. Use system engineering bv
> does not guarantee that the integrity of this communication has been
> maintained nor that this communication is free of viruses, interceptions or
> interference. /
>
> ___
> 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] Problems (maybe a bug) when creating a backup using the command line interface '.backup' command

[Marco ten Thije]

Hello,

We are facing a problem with the '.backup' command using the SQLite 
command line interface. The resulting backup-database seems to be corrupt.


We ran into this problem on a ARM9 platform using SQLite 3.7.5, but it 
can also be reproduced on the latest 3.7.15.1 version on Intel.


I have attached our database to this email.  These are the steps to 
reproduce it:


./sqlite3 energy.sqlite
SQLite version 3.7.15.1 2012-12-19 20:39:10
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select count(*) from EnergyLogData;
3306
sqlite> .backup backup.sqlite
sqlite> .exit

The above shows the number of records in one of the tables and creates a 
backup to a new 'backup.sqlite' database file. But this new file

seems to be corrupt:

./sqlite3 backup.sqlite
SQLite version 3.7.15.1 2012-12-19 20:39:10
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select count(*) from EnergyLogData;
Error: database disk image is malformed
sqlite> .exit

I have look into the Bug database and found ticket 3858, but that one 
should already be fixed and we are not writing to the database during

the backup.

Is this a bug or some other problem?

--
Regards,
Marco ten Thije


*Use system engineering bv*
Loc: Industriestraat 77 - 7482 EW HAAKSBERGEN - The Netherlands  Mail 
Address: Postbus 236 - 7480 AE HAAKSBERGEN - The Netherlands
T: +31 53 5741456  F: +31 53 5741458   E: 
marco.tenth...@usetechnology.nl  
  I: www.usetechnology.nl 
Chamber of Commerce nr: Veluwe en Twente 528 98210  VAT nr.: NL 
8506.54.713B01


/This message (including any attachments) is confidential and may be 
privileged. If you have received it by mistake please notify the sender 
by return e-mail and delete this message from your system. Any 
unauthorised use or dissemination of this message in whole or in part is 
strictly prohibited. Please note that e-mails are susceptible to change. 
Use system engineering bv shall not be liable for the improper or 
incomplete transmission of the information contained in this 
communication nor for any delay in its receipt or damage to your system. 
Use system engineering bv does not guarantee that the integrity of this 
communication has been maintained nor that this communication is free of 
viruses, interceptions or interference. /
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users