Re: [sqlite] same code produces a different database file on different computers
Karl Forner wrote: > On Tue, Dec 19, 2017 at 3:12 PM, Clemens Ladisch 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] same code produces a different database file on different computers
On Tue, Dec 19, 2017 at 3:12 PM, Clemens Ladisch 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 > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] same code produces a different database file on different computers
> > PRAGMA integrity_check; > all ok > > > > Simon. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] same code produces a different database file on different computers
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] same code produces a different database file on different computers
On 19 Dec 2017, at 1:30pm, Karl Forner 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] same code produces a different database file on different computers
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-
>boun...@mailinglists.sqlite.org] 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
>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
>
>> wrote:
>>
>>>
>>>
>>> On 19 Dec 2017, at 12:43pm, Karl Forner
>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
>>> sqlite-users@mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>>
>>
>>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 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
> wrote:
>
>>
>>
>> On 19 Dec 2017, at 12:43pm, Karl Forner 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
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] same code produces a different database file on different computers
> 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 wrote:
>
>
> On 19 Dec 2017, at 12:43pm, Karl Forner 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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] same code produces a different database file on different computers
On 19 Dec 2017, at 12:43pm, Karl Forner 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 < sandersonforens...@gmail.com> 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 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 > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] same code produces a different database file on different computers
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 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 > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] same code produces a different database file on different computers
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
