[Monetdb-developers] Data integrity bug

Niels Nes Niels.Nes at cwi.nl
Tue Oct 7 12:24:25 CEST 2008


On Mon, Oct 06, 2008 at 01:45:35PM -0500, Ross Bates wrote:
>    Something definitely going on with the table stats, I am seeing
>    the same strange behavior in that it doesn't occur on a
>    consistent basis.
>    I have finally been able to recreate it using the following
>    steps. (also see my notes that follow)
>    --------------------------------------
>    CREATE TABLE "sobi"."table1"
>    (
>       cid integer,
>        p varchar(100),
>       c varchar(100),
>       lf integer,
>       rt integer,
>       lvl integer
>    )
>    insert into sobi.table1 values (1,'foo','bar',9,12,1)
>    insert into sobi.table1 values (1,'boo','blah',13,22,1)
>    CREATE TABLE "sobi"."table2"
>    (
>        p varchar(100),
>       c varchar(100),
>       lf integer,
>       rt integer,
>       lvl integer
>    )
>    insert into sobi.table2(p,c) (select p,c from sobi.table1)
>    select * from sobi.table2
>    select * from sobi.table2 where p = 'foo'
>    delete from sobi.table2

I found a problem within the monetdb kernel code which caused this
problem, ie its fixed now in the head of cvs.

Ross thanks for the script it really helps solving such bugs. I have
just one more request (general ie not just for you). Please file bug
reports on the sourceforge bugtracker. It make keeping track on 
bugs so much easier.

Niels
>    --------------------------------------
>    It might happen right away - or it might take 5-10 times, but
>    if you iterate over the insert > select > delete statements
>    eventually the [p = 'foo'] select will stop returning rows.
>    On Mon, Oct 6, 2008 at 1:24 PM, Carl Nobile
>    <[1]carl.nobile at gmail.com> wrote:
> 
>    Ross,
>    I have written a web service in Python that sets on top of
>    MonetDB it is somewhat complicated, but I'll try to explain it
>    below. There are only three tables in the schema and one view
>    over sys.tables and sys.columns.
>    BUSINESS_UNIT
>    BUSINESS_USER_COLUMN_STATS
>    BUSINESS_USER_PROFILE
>    BUSINESS_USER_PROFILE_COLUMNS
>    1) START TRANSACTION
>    2) Check if record exists to determine if an UPDATE or an
>    INSERT needs to be done.
>    (SELECT count(*) FROM "BUSINESS_USER_PROFILE" WHERE
>    "USER_ID"=%(USER_ID)s AND "BUSINESS_ID"=%(BUSINESS_ID)s)
>    3) If an INSERT then check if the BUSINESS_UNIT is present.
>    SELECT count(*) FROM "BUSINESS_UNIT" WHERE
>    "BUSINESS_ID"=%(BUSINESS_ID)s
>    4) If UPDATE see item 13
>    5) If INSERT then: INSERT INTO "BUSINESS_UNIT" ("BUSINESS_ID",
>    "CREATE_DT", "CREATE_CID") VALUES (%(BUSINESS_ID)s,
>    %(CREATE_DT)s, %(CREATE_CID)s
>    6) Look to see if any new columns need to be added to
>    BUSINESS_USER_PROFILE by checking the view, if yes then: ALTER
>    TABLE "BUSINESS_USER_PROFILE" ADD COLUMN "%s" VARCHAR(240) NULL
>    7) Commit then START TRANSACTION
>    8) INSERT the record into the BUSINESS_USER_PROFILE table (the
>    two %s constructs could be any number of columns and values):
>    INSERT INTO "BUSINESS_USER_PROFILE" (%s) VALUES (%s)
>    9) Check to see if the column needs to be inserted or updated
>    to the stats table: SELECT "COLUMN_NAME" FROM
>    "BUSINESS_USER_COLUMN_STATS"
>    10) Check the view again to see if the column already exists.
>    11) If INSERT: INSERT INTO "BUSINESS_USER_COLUMN_STATS"
>    ("COLUMN_NAME", "BUSINESS_ID", "LAST_UPDT_DT", "LAST_UPDT_CID")
>    VALUES (%(COLUMN_NAME)s, %(BUSINESS_ID)s, %(LAST_UPDT_DT)s,
>    %(LAST_UPDT_CID)s)
>    12) If UPDATE: UPDATE "BUSINESS_USER_COLUMN_STATS" SET
>    "BUSINESS_ID"=%(BUSINESS_ID)s, "LAST_UPDT_DT"=%(LAST_UPDT_DT)s,
>    "LAST_UPDT_CID"=%(LAST_UPDT_CID)s WHERE
>    "COLUMN_NAME"=%(COLUMN_NAME)s
>    13) Do 6 and 7 above.
>    14) UPDATE the record in the BUSINESS_USER_PROFILE table (the
>    two %s constructs could be any number of columns or
>    conditions): 'UPDATE "BUSINESS_USER_PROFILE" SET %s WHERE %s
>    15) Do 9, 10, 11, and 12
>    OK this may be over kill, but I had to think through the
>    process myself before I could answer your question. Any select
>    after doing a sequence of six inserts and six updates would
>    usually cause the issue. The operative word is 'usually',
>    because sometimes it would work flawlessly.
>    -Carl
>    On Mon, Oct 6, 2008 at 1:23 PM, Ross Bates
>    <[2]rbates at gmail.com> wrote:
> 
>    Carl - this bug continues to bother me as well as I can't
>    reproduce the exact steps.
>    Does your application populate the table in question using sql
>    like this?
>        insert into foo(col1,col2) (select col1,col2 from bar)
>    Also, do you run any delete statements before populating the
>    data?
>    I was thinking that the bug was related to the
>    addition/deletion of columns, but it appears to show up more
>    often after a series of insert/delete statements which follow
>    my alter/create table statements.
>    On Sun, Oct 5, 2008 at 4:19 PM, Stefan de Konink
>    <[3]stefan at konink.de> wrote:
> 
>    -----BEGIN PGP SIGNED MESSAGE-----
>    Hash: SHA512
> 
>      Ross Bates schreef:
> 
>    > Stefan - what is occurring in the alignment bug you are
>    referring to?
> 
>      I had an issue before that occured after I had added an
>      index (that in
>      MonetDB terms shouldn't do anything), where I was able to
>      make mserver5
>      crash on a string comparison.
>      But I know that this was fixed even before I had reported
>      it.
> 
>    Stefan
>    -----BEGIN PGP SIGNATURE-----
>    Version: GnuPG v2.0.9 (GNU/Linux)
>    Comment: Using GnuPG with Mozilla -
>    [4]http://enigmail.mozdev.org
> 
>      iEYEAREKAAYFAkjpL2AACgkQYH1+F2Rqwn3k9gCeKg+p+n+nQE+dPTLDLnZe
>      9OZ9
>      SIAAnimb0zS4XvWtiKncWEwh3RU7DMJe
>      =zHeG
>      -----END PGP SIGNATURE-----
> 
>    --
>    ---------------------------------------------------------------
>    ----------------
>    Carl J. Nobile (Software Engineer)
>    [5]carl.nobile at gmail.com
>    ---------------------------------------------------------------
>    ----------------
> 
> References
> 
>    1. mailto:carl.nobile at gmail.com
>    2. mailto:rbates at gmail.com
>    3. mailto:stefan at konink.de
>    4. http://enigmail.mozdev.org/
>    5. mailto:carl.nobile at gmail.com

> -------------------------------------------------------------------------
> This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
> Build the coolest Linux based applications with Moblin SDK & win great prizes
> Grand prize is a trip for two to an Open Source event anywhere in the world
> http://moblin-contest.org/redirect.php?banner_id=100&url=/
> _______________________________________________
> Monetdb-developers mailing list
> Monetdb-developers at lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/monetdb-developers


-- 

Niels Nes, Centre for Mathematics and Computer Science (CWI)
Kruislaan 413, 1098 SJ Amsterdam, The Netherlands
room C0.02,  phone ++31 20 592-4098, fax ++31 20 592-4312
url: http://www.cwi.nl/~niels   e-mail: Niels.Nes at cwi.nl




More information about the developers-list mailing list