[Monetdb-developers] Data integrity bug

Ross Bates rbates at gmail.com
Mon Oct 6 20:45:35 CEST 2008


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

--------------------------------------

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 <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 <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 <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 - http://enigmail.mozdev.org
>>>
>>> iEYEAREKAAYFAkjpL2AACgkQYH1+F2Rqwn3k9gCeKg+p+n+nQE+dPTLDLnZe9OZ9
>>> SIAAnimb0zS4XvWtiKncWEwh3RU7DMJe
>>> =zHeG
>>> -----END PGP SIGNATURE-----
>>>
>>
>>
>
>
> --
>
> -------------------------------------------------------------------------------
> Carl J. Nobile (Software Engineer)
> carl.nobile at gmail.com
>
> -------------------------------------------------------------------------------
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.monetdb.org/pipermail/developers-list/attachments/20081006/5172cb9f/attachment.html>


More information about the developers-list mailing list