[Monetdb-developers] Data integrity bug

Carl Nobile carl.nobile at gmail.com
Mon Oct 6 20:50:04 CEST 2008


Yes, the same is true with my situation. It can work fine 3 or 4 times in a
row then select  from where equals just stops returning any data.

-Carl

On Mon, Oct 6, 2008 at 2:45 PM, Ross Bates <rbates at gmail.com> 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
>
> --------------------------------------
>
> 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
>>
>> -------------------------------------------------------------------------------
>>
>
>


-- 
-------------------------------------------------------------------------------
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/c82f8d78/attachment.html>


More information about the developers-list mailing list