[Monetdb-developers] Data integrity bug

Carl Nobile carl.nobile at gmail.com
Mon Oct 6 21:07:02 CEST 2008


Ross,

I forgot to answer one of your questions. Although my web service can delete
data, running a delete doesn't seen to be involved with this issue. To make
a long story short it seems everything is okay until I add a column during
an update.

-Carl

On Mon, Oct 6, 2008 at 2:50 PM, Carl Nobile <carl.nobile at gmail.com> wrote:

> 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
>
> -------------------------------------------------------------------------------
>



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


More information about the developers-list mailing list