[Monetdb-developers] Data integrity bug

Carl Nobile carl.nobile at gmail.com
Mon Oct 6 20:24:16 CEST 2008


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


More information about the developers-list mailing list