same code produces a different database file on different computers

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|

same code produces a different database file on different computers

Karl Forner
Hello,

I encountered a weird behavior recently.
The exact same code (executed from a docker container using the same image)
produced a different database file on two computers, as verified by a MD5
or sha256 hash of the two files.
But using the* .sha3sum* of sqlite3.exe I could check that the contents of
the two databases are indeed identical.

To sum up, same content, but the files are somewhat different.

Is this something expected or known ?
If so, is there a way to prevent this ?

Our use case  if that we track the sha256 hashes of the files, that could
have been produced on different computers to know of the outputs are up to
date. Until now, since ~ 4 years it has always seem to work.

Thanks.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: same code produces a different database file on different computers

Paul Sanderson
SQLite stores the verison number of the librrary in the database header.
Different SQlite libraries on different computers would cause this error.

Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence

On 19 December 2017 at 12:32, Karl Forner <[hidden email]> wrote:

> Hello,
>
> I encountered a weird behavior recently.
> The exact same code (executed from a docker container using the same image)
> produced a different database file on two computers, as verified by a MD5
> or sha256 hash of the two files.
> But using the* .sha3sum* of sqlite3.exe I could check that the contents of
> the two databases are indeed identical.
>
> To sum up, same content, but the files are somewhat different.
>
> Is this something expected or known ?
> If so, is there a way to prevent this ?
>
> Our use case  if that we track the sha256 hashes of the files, that could
> have been produced on different computers to know of the outputs are up to
> date. Until now, since ~ 4 years it has always seem to work.
>
> Thanks.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: same code produces a different database file on different computers

Karl Forner
Thanks for your reply.
All the software in the docker container. so it is exactly the same version
of all software and libraries, except the linux kernel. Moreover the
differences are not in the header, rather interspersed with what looks like
CREATE statements.



On Tue, Dec 19, 2017 at 1:40 PM, Paul Sanderson <
[hidden email]> wrote:

> SQLite stores the verison number of the librrary in the database header.
> Different SQlite libraries on different computers would cause this error.
>
> Paul
> www.sandersonforensics.com
> skype: r3scue193
> twitter: @sandersonforens
> Tel +44 (0)1326 572786
> http://sandersonforensics.com/forum/content.php?195-SQLite-
> Forensic-Toolkit
> -Forensic Toolkit for SQLite
> email from a work address for a fully functional demo licence
>
> On 19 December 2017 at 12:32, Karl Forner <[hidden email]> wrote:
>
> > Hello,
> >
> > I encountered a weird behavior recently.
> > The exact same code (executed from a docker container using the same
> image)
> > produced a different database file on two computers, as verified by a MD5
> > or sha256 hash of the two files.
> > But using the* .sha3sum* of sqlite3.exe I could check that the contents
> of
> > the two databases are indeed identical.
> >
> > To sum up, same content, but the files are somewhat different.
> >
> > Is this something expected or known ?
> > If so, is there a way to prevent this ?
> >
> > Our use case  if that we track the sha256 hashes of the files, that could
> > have been produced on different computers to know of the outputs are up
> to
> > date. Until now, since ~ 4 years it has always seem to work.
> >
> > Thanks.
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: same code produces a different database file on different computers

Simon Slavin-3


On 19 Dec 2017, at 12:43pm, Karl Forner <[hidden email]> wrote:

> All the software in the docker container. so it is exactly the same version
> of all software and libraries, except the linux kernel. Moreover the
> differences are not in the header, rather interspersed with what looks like
> CREATE statements.

Using the sqlite3 shell tool please give the command

SELECT * FROM sqlite_master;

on one copy of the database.  Are the CREATE commands in the result the same as the CREATE command you just told us about ?

Issue the same SELECT command for the other copy of the database.  Do you get identical output, in the same order, as you got from the first copy of the database ?

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: same code produces a different database file on different computers

Hick Gunter
In reply to this post by Karl Forner
The internal table sqlite_master has a field named sql that contains the text of the create statements. This may be what you are looking at. Does the difference show up in the sqlite_master table contents?

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Karl Forner
Gesendet: Dienstag, 19. Dezember 2017 13:44
An: SQLite mailing list <[hidden email]>
Betreff: [EXTERNAL] Re: [sqlite] same code produces a different database file on different computers

Thanks for your reply.
All the software in the docker container. so it is exactly the same version of all software and libraries, except the linux kernel. Moreover the differences are not in the header, rather interspersed with what looks like CREATE statements.



On Tue, Dec 19, 2017 at 1:40 PM, Paul Sanderson < [hidden email]> wrote:

> SQLite stores the verison number of the librrary in the database header.
> Different SQlite libraries on different computers would cause this error.
>
> Paul
> www.sandersonforensics.com
> skype: r3scue193
> twitter: @sandersonforens
> Tel +44 (0)1326 572786
> http://sandersonforensics.com/forum/content.php?195-SQLite-
> Forensic-Toolkit
> -Forensic Toolkit for SQLite
> email from a work address for a fully functional demo licence
>
> On 19 December 2017 at 12:32, Karl Forner <[hidden email]> wrote:
>
> > Hello,
> >
> > I encountered a weird behavior recently.
> > The exact same code (executed from a docker container using the same
> image)
> > produced a different database file on two computers, as verified by
> > a MD5 or sha256 hash of the two files.
> > But using the* .sha3sum* of sqlite3.exe I could check that the
> > contents
> of
> > the two databases are indeed identical.
> >
> > To sum up, same content, but the files are somewhat different.
> >
> > Is this something expected or known ?
> > If so, is there a way to prevent this ?
> >
> > Our use case  if that we track the sha256 hashes of the files, that
> > could have been produced on different computers to know of the
> > outputs are up
> to
> > date. Until now, since ~ 4 years it has always seem to work.
> >
> > Thanks.
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: same code produces a different database file on different computers

Karl Forner
In reply to this post by Simon Slavin-3
> Using the sqlite3 shell tool please give the command
> SELECT * FROM sqlite_master;

the results are identical

table|_meta|_meta|2|CREATE TABLE "_meta"(    "TABLENAME" TEXT NOT NULL  ,
"COLNAME" TEXT NOT NULL  , "VARNAMES" TEXT  , "LABELS" TEXT  , "PREFS"
TEXT   , PRIMARY KEY(TABLENAME,COLNAME))
index|sqlite_autoindex__meta_1|_meta|3|
table|pk_model|pk_model|39020|CREATE TABLE "pk_model"(    "MODEL" TEXT   ,
PRIMARY KEY(MODEL))
index|sqlite_autoindex_pk_model_1|pk_model|39021|
table|tumor_data|tumor_data|39027|CREATE TABLE "tumor_data"(    "CRO" TEXT
, "MODEL" TEXT  , "SUBTYPE" TEXT  , "QB_SUBTYPE" TEXT  ,
"SUBTYPE_DEFINED_FOR_QUARTZBIO_INTERNAL_HISTO_SUBTYPING_CRO_SUBTYPING_
CONSIDERED_IN_CASE_IT_IS_MORE_SUBTYPE_SPECIFIC" TEXT  , "TREATMENT_ARM_NO"
INTEGER  , "TREATMENT" TEXT  , "DAY" TEXT  , "MEASURE" TEXT  , "LBSTRESC"
TEXT , FOREIGN KEY(MODEL) REFERENCES pk_model(MODEL) ON UPDATE NO ACTION ON
DELETE NO ACTION)
table|sequencing_metadata|sequencing_metadata|7|CREATE TABLE
"sequencing_metadata"(    "ANALYSIS_ANALYSIS_NAME" TEXT  ,
"ANALYSIS_DATASET_NAME" TEXT  , "ANALYSIS_PROJECT_NAME" TEXT  ,
"METADATA_NAME" TEXT  , "METADATA_NUM" TEXT  , "METADATA_STR" TEXT  ,
"SAMPLE_BATCH" TEXT  , "SAMPLE_NAME" TEXT  , "MODEL" TEXT , FOREIGN
KEY(MODEL) REFERENCES pk_model(MODEL) ON UPDATE NO ACTION ON DELETE NO
ACTION)
table|excluded_mice|excluded_mice|1319|CREATE TABLE "excluded_mice"(
"MODEL" TEXT  , "TREATMENT" TEXT  , "COMMENT" TEXT  , "X" TEXT , FOREIGN
KEY(MODEL) REFERENCES pk_model(MODEL) ON UPDATE NO ACTION ON DELETE NO
ACTION)
table|excluded_visits|excluded_visits|1320|CREATE TABLE
"excluded_visits"(    "MODEL" TEXT  , "TREATMENT" TEXT  , "VISIT" TEXT  ,
"COMMENT" TEXT , FOREIGN KEY(MODEL) REFERENCES pk_model(MODEL) ON UPDATE NO
ACTION ON DELETE NO ACTION)
table|tumor_lungsubtypes|tumor_lungsubtypes|1321|CREATE TABLE
"tumor_lungsubtypes"(    "MODEL" TEXT  , "SUBTYPE_COMBINED" TEXT , FOREIGN
KEY(MODEL) REFERENCES pk_model(MODEL) ON UPDATE NO ACTION ON DELETE NO
ACTION)



Here's a screenshot of some of the diffs using vbindiff

https://ibb.co/kNm0X6

On Tue, Dec 19, 2017 at 1:48 PM, Simon Slavin <[hidden email]> wrote:

>
>
> On 19 Dec 2017, at 12:43pm, Karl Forner <[hidden email]> wrote:
>
> > All the software in the docker container. so it is exactly the same
> version
> > of all software and libraries, except the linux kernel. Moreover the
> > differences are not in the header, rather interspersed with what looks
> like
> > CREATE statements.
>
> Using the sqlite3 shell tool please give the command
>
> SELECT * FROM sqlite_master;
>
> on one copy of the database.  Are the CREATE commands in the result the
> same as the CREATE command you just told us about ?
>
> Issue the same SELECT command for the other copy of the database.  Do you
> get identical output, in the same order, as you got from the first copy of
> the database ?
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: same code produces a different database file on different computers

Karl Forner
and the dumps usign the .dump sqlite3.exe command are also identical.

On Tue, Dec 19, 2017 at 2:19 PM, Karl Forner <[hidden email]> wrote:

> > Using the sqlite3 shell tool please give the command
> > SELECT * FROM sqlite_master;
>
> the results are identical
>
> table|_meta|_meta|2|CREATE TABLE "_meta"(    "TABLENAME" TEXT NOT NULL  ,
> "COLNAME" TEXT NOT NULL  , "VARNAMES" TEXT  , "LABELS" TEXT  , "PREFS"
> TEXT   , PRIMARY KEY(TABLENAME,COLNAME))
> index|sqlite_autoindex__meta_1|_meta|3|
> table|pk_model|pk_model|39020|CREATE TABLE "pk_model"(    "MODEL" TEXT
> , PRIMARY KEY(MODEL))
> index|sqlite_autoindex_pk_model_1|pk_model|39021|
> table|tumor_data|tumor_data|39027|CREATE TABLE "tumor_data"(    "CRO"
> TEXT  , "MODEL" TEXT  , "SUBTYPE" TEXT  , "QB_SUBTYPE" TEXT  ,
> "SUBTYPE_DEFINED_FOR_QUARTZBIO_INTERNAL_HISTO_SUBTYPING_CRO_
> SUBTYPING_CONSIDERED_IN_CASE_IT_IS_MORE_SUBTYPE_SPECIFIC" TEXT  ,
> "TREATMENT_ARM_NO" INTEGER  , "TREATMENT" TEXT  , "DAY" TEXT  , "MEASURE"
> TEXT  , "LBSTRESC" TEXT , FOREIGN KEY(MODEL) REFERENCES pk_model(MODEL) ON
> UPDATE NO ACTION ON DELETE NO ACTION)
> table|sequencing_metadata|sequencing_metadata|7|CREATE TABLE
> "sequencing_metadata"(    "ANALYSIS_ANALYSIS_NAME" TEXT  ,
> "ANALYSIS_DATASET_NAME" TEXT  , "ANALYSIS_PROJECT_NAME" TEXT  ,
> "METADATA_NAME" TEXT  , "METADATA_NUM" TEXT  , "METADATA_STR" TEXT  ,
> "SAMPLE_BATCH" TEXT  , "SAMPLE_NAME" TEXT  , "MODEL" TEXT , FOREIGN
> KEY(MODEL) REFERENCES pk_model(MODEL) ON UPDATE NO ACTION ON DELETE NO
> ACTION)
> table|excluded_mice|excluded_mice|1319|CREATE TABLE "excluded_mice"(
> "MODEL" TEXT  , "TREATMENT" TEXT  , "COMMENT" TEXT  , "X" TEXT , FOREIGN
> KEY(MODEL) REFERENCES pk_model(MODEL) ON UPDATE NO ACTION ON DELETE NO
> ACTION)
> table|excluded_visits|excluded_visits|1320|CREATE TABLE
> "excluded_visits"(    "MODEL" TEXT  , "TREATMENT" TEXT  , "VISIT" TEXT  ,
> "COMMENT" TEXT , FOREIGN KEY(MODEL) REFERENCES pk_model(MODEL) ON UPDATE NO
> ACTION ON DELETE NO ACTION)
> table|tumor_lungsubtypes|tumor_lungsubtypes|1321|CREATE TABLE
> "tumor_lungsubtypes"(    "MODEL" TEXT  , "SUBTYPE_COMBINED" TEXT , FOREIGN
> KEY(MODEL) REFERENCES pk_model(MODEL) ON UPDATE NO ACTION ON DELETE NO
> ACTION)
>
>
>
> Here's a screenshot of some of the diffs using vbindiff
>
> https://ibb.co/kNm0X6
>
> On Tue, Dec 19, 2017 at 1:48 PM, Simon Slavin <[hidden email]>
> wrote:
>
>>
>>
>> On 19 Dec 2017, at 12:43pm, Karl Forner <[hidden email]> wrote:
>>
>> > All the software in the docker container. so it is exactly the same
>> version
>> > of all software and libraries, except the linux kernel. Moreover the
>> > differences are not in the header, rather interspersed with what looks
>> like
>> > CREATE statements.
>>
>> Using the sqlite3 shell tool please give the command
>>
>> SELECT * FROM sqlite_master;
>>
>> on one copy of the database.  Are the CREATE commands in the result the
>> same as the CREATE command you just told us about ?
>>
>> Issue the same SELECT command for the other copy of the database.  Do you
>> get identical output, in the same order, as you got from the first copy of
>> the database ?
>>
>> Simon.
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: same code produces a different database file on different computers

Keith Medcalf

So the "used bytes" are the same, but the "unused bytes" are not.  That would seem to indicate the possibility that the "empty page" initialization is the difference.  Some OSes will return zero'd out "new" sectors while some are perfectly happy to return whatever data happened to reside in the sector prior to it being allocated.  If this is the case the "used contents" will be the same but the "unused bytes" will be different.

Is this what you are seeing?

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Karl Forner
>Sent: Tuesday, 19 December, 2017 06:30
>To: SQLite mailing list
>Subject: Re: [sqlite] same code produces a different database file on
>different computers
>
>and the dumps usign the .dump sqlite3.exe command are also identical.
>
>On Tue, Dec 19, 2017 at 2:19 PM, Karl Forner <[hidden email]>
>wrote:
>
>> > Using the sqlite3 shell tool please give the command
>> > SELECT * FROM sqlite_master;
>>
>> the results are identical
>>
>> table|_meta|_meta|2|CREATE TABLE "_meta"(    "TABLENAME" TEXT NOT
>NULL  ,
>> "COLNAME" TEXT NOT NULL  , "VARNAMES" TEXT  , "LABELS" TEXT  ,
>"PREFS"
>> TEXT   , PRIMARY KEY(TABLENAME,COLNAME))
>> index|sqlite_autoindex__meta_1|_meta|3|
>> table|pk_model|pk_model|39020|CREATE TABLE "pk_model"(    "MODEL"
>TEXT
>> , PRIMARY KEY(MODEL))
>> index|sqlite_autoindex_pk_model_1|pk_model|39021|
>> table|tumor_data|tumor_data|39027|CREATE TABLE "tumor_data"(
>"CRO"
>> TEXT  , "MODEL" TEXT  , "SUBTYPE" TEXT  , "QB_SUBTYPE" TEXT  ,
>> "SUBTYPE_DEFINED_FOR_QUARTZBIO_INTERNAL_HISTO_SUBTYPING_CRO_
>> SUBTYPING_CONSIDERED_IN_CASE_IT_IS_MORE_SUBTYPE_SPECIFIC" TEXT  ,
>> "TREATMENT_ARM_NO" INTEGER  , "TREATMENT" TEXT  , "DAY" TEXT  ,
>"MEASURE"
>> TEXT  , "LBSTRESC" TEXT , FOREIGN KEY(MODEL) REFERENCES
>pk_model(MODEL) ON
>> UPDATE NO ACTION ON DELETE NO ACTION)
>> table|sequencing_metadata|sequencing_metadata|7|CREATE TABLE
>> "sequencing_metadata"(    "ANALYSIS_ANALYSIS_NAME" TEXT  ,
>> "ANALYSIS_DATASET_NAME" TEXT  , "ANALYSIS_PROJECT_NAME" TEXT  ,
>> "METADATA_NAME" TEXT  , "METADATA_NUM" TEXT  , "METADATA_STR" TEXT
>,
>> "SAMPLE_BATCH" TEXT  , "SAMPLE_NAME" TEXT  , "MODEL" TEXT , FOREIGN
>> KEY(MODEL) REFERENCES pk_model(MODEL) ON UPDATE NO ACTION ON DELETE
>NO
>> ACTION)
>> table|excluded_mice|excluded_mice|1319|CREATE TABLE
>"excluded_mice"(
>> "MODEL" TEXT  , "TREATMENT" TEXT  , "COMMENT" TEXT  , "X" TEXT ,
>FOREIGN
>> KEY(MODEL) REFERENCES pk_model(MODEL) ON UPDATE NO ACTION ON DELETE
>NO
>> ACTION)
>> table|excluded_visits|excluded_visits|1320|CREATE TABLE
>> "excluded_visits"(    "MODEL" TEXT  , "TREATMENT" TEXT  , "VISIT"
>TEXT  ,
>> "COMMENT" TEXT , FOREIGN KEY(MODEL) REFERENCES pk_model(MODEL) ON
>UPDATE NO
>> ACTION ON DELETE NO ACTION)
>> table|tumor_lungsubtypes|tumor_lungsubtypes|1321|CREATE TABLE
>> "tumor_lungsubtypes"(    "MODEL" TEXT  , "SUBTYPE_COMBINED" TEXT ,
>FOREIGN
>> KEY(MODEL) REFERENCES pk_model(MODEL) ON UPDATE NO ACTION ON DELETE
>NO
>> ACTION)
>>
>>
>>
>> Here's a screenshot of some of the diffs using vbindiff
>>
>> https://ibb.co/kNm0X6
>>
>> On Tue, Dec 19, 2017 at 1:48 PM, Simon Slavin
><[hidden email]>
>> wrote:
>>
>>>
>>>
>>> On 19 Dec 2017, at 12:43pm, Karl Forner <[hidden email]>
>wrote:
>>>
>>> > All the software in the docker container. so it is exactly the
>same
>>> version
>>> > of all software and libraries, except the linux kernel. Moreover
>the
>>> > differences are not in the header, rather interspersed with what
>looks
>>> like
>>> > CREATE statements.
>>>
>>> Using the sqlite3 shell tool please give the command
>>>
>>> SELECT * FROM sqlite_master;
>>>
>>> on one copy of the database.  Are the CREATE commands in the
>result the
>>> same as the CREATE command you just told us about ?
>>>
>>> Issue the same SELECT command for the other copy of the database.
>Do you
>>> get identical output, in the same order, as you got from the first
>copy of
>>> the database ?
>>>
>>> Simon.
>>> _______________________________________________
>>> sqlite-users mailing list
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>>
>>
>>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: same code produces a different database file on different computers

Simon Slavin-3
In reply to this post by Karl Forner
On 19 Dec 2017, at 1:30pm, Karl Forner <[hidden email]> wrote:

> and the dumps usign the .dump sqlite3.exe command are also identical.

Paul has a better chance of understanding the hex dump than I do, but what I think I’m seeing is trivial differences in file organisation.  In other words some pieces of data are stored in a different order in the two databases.

I’m not sure why this could happen in a situation where you start with two identical Docker images.  Might they run out of caching space at different times because background processes ran differently ?  Having caches written to the database in a different order could cause the sort of thing you’re showing us.

Your tests — sha3sum checksum and using .dump — both say that the databases have identical contents.  As a last possible check you might want to run this in the shell tool on both copies:

PRAGMA integrity_check;

I’m betting that you see no errors on either copy.  Whatever the differences between the two copies are, they're harmless as far as SQLite is concerned.  But I have no idea why they might have happened.  I hope Mr Sanderson or one of the development team have some ideas.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: same code produces a different database file on different computers

Clemens Ladisch
In reply to this post by Karl Forner
Karl Forner wrote:
> Here's a screenshot of some of the diffs using vbindiff
>
> https://ibb.co/kNm0X6

SQLite uses hash tables for schema objects, so different names might
result in such differences.

Where does the hex number in "excluded_mice_temp2b5036f270" come from?
Is it random?


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: same code produces a different database file on different computers

Karl Forner
In reply to this post by Simon Slavin-3
>
> PRAGMA integrity_check;
>

all ok


>
>
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: same code produces a different database file on different computers

Karl Forner
In reply to this post by Clemens Ladisch
On Tue, Dec 19, 2017 at 3:12 PM, Clemens Ladisch <[hidden email]> wrote:

> Karl Forner wrote:
> > Here's a screenshot of some of the diffs using vbindiff
> >
> > https://ibb.co/kNm0X6
>
> SQLite uses hash tables for schema objects, so different names might
> result in such differences.
>
> Where does the hex number in "excluded_mice_temp2b5036f270" come from?
> Is it random?
>

Yes, thanks, I think I got it. In order to alter tables, we first move them
to a new table with a temp name, copy them, then drop them.
The temp name is random, so is different between the two computers.

After doing a "vacuum" on both files, they are now identical !

So it is our fault, we should do a vacuum after the drop table.

Thanks a lot for your help !




>
>
> Regards,
> Clemens
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: same code produces a different database file on different computers

Clemens Ladisch
Karl Forner wrote:

> On Tue, Dec 19, 2017 at 3:12 PM, Clemens Ladisch <[hidden email]> wrote:
>> Where does the hex number in "excluded_mice_temp2b5036f270" come from?
>> Is it random?
>
> Yes, thanks, I think I got it. In order to alter tables, we first move them
> to a new table with a temp name, copy them, then drop them.
> The temp name is random, so is different between the two computers.
>
> After doing a "vacuum" on both files, they are now identical !
>
> So it is our fault, we should do a vacuum after the drop table.

No.   If you care about repeatability, don't use random numbers.
Instead use a deterministic algorithm to generate the temp table name.


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users