[Monetdb-developers] Data integrity bug

Carl Nobile

5 Oct 2008 5 Oct '08

1:53 a.m.

I think I have found a data integrity bug. After working with the DB for a while I notice that the equals (=) operator no longer works, however, the LIKE operator will still retrieve data. The only way I have found to solve this issue is to rebuild the DB and import all the data. This is obviously not a very good solution. There is an application sitting on top of the DB that has been the cause of this, but as you can see below mclient also gives the wrong results. Bouncing the server does not fix it. You should know the only odd thing the application does is dynamically add and drop columns to one of the tables--the reason we are using MonetDB. This allows us to have an essential flat 2nd normal form table with 4th normal form capabilities. sql>SELECT "USERNAME","LAST_UPDT_CID","CREATE_DT" from "BUSINESS_USER_PROFILE" WHERE "USERNAME"='Carl'; sql>SELECT "USERNAME","LAST_UPDT_CID","CREATE_DT" from "BUSINESS_USER_PROFILE" WHERE "USERNAME" like 'Carl'; +-----------------+-------------------------+---------------------------------------+ | USERNAME |LAST_UPDT_CID |CREATE_DT | +==========+==============+======================+ | Carl |GVE |2008-10-04 20:47:54.275000 | +----------------- +------------------------+---------------------------------------+ We are still in development of this project, but need to release our code in a month or so. Any ideas what is going on here? ------------------------------------------------------------------------------- Carl J. Nobile (Software Engineer) carl.nobile@gmail.com -------------------------------------------------------------------------------

Attachments:

attachment.html (text/html — 2.2 KB)

Show replies by date

Stefan de Konink

5 Oct 5 Oct

3:29 a.m.

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA512 Carl Nobile schreef:

...

I think I have found a data integrity bug. After working with the DB for a while I notice that the equals (=) operator no longer works, however, the LIKE operator will still retrieve data.

Niels; could this be the alignment bug I had too? Stefan -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEAREKAAYFAkjoNKEACgkQYH1+F2Rqwn0EiwCgiGwJKdX+jCxTKRqFMxsoabvI rxYAn3hpTyYkRRVJIB+6jcTyyb//8GPg =4KRC -----END PGP SIGNATURE-----

Ross Bates

9:13 p.m.

I have run into this issue recently as well. Similar to what Carl described, it only happens on tables where I've added a column. Even if you drop and recreate the table from scratch with the new column and reload the data, the (=) operator still won't work. I don't recreate the database, but I do have to rename the table to something different. Stefan - what is occurring in the alignment bug you are referring to? -Ross On Sat, Oct 4, 2008 at 10:29 PM, Stefan de Konink wrote:

...

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA512

Carl Nobile schreef:

...
I think I have found a data integrity bug. After working with the DB for a while I notice that the equals (=) operator no longer works, however, the LIKE operator will still retrieve data.

Niels; could this be the alignment bug I had too?

Stefan -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEAREKAAYFAkjoNKEACgkQYH1+F2Rqwn0EiwCgiGwJKdX+jCxTKRqFMxsoabvI rxYAn3hpTyYkRRVJIB+6jcTyyb//8GPg =4KRC -----END PGP SIGNATURE-----

------------------------------------------------------------------------- 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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-developers

Stefan de Konink

9:19 p.m.

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

Ross Bates

6 Oct 6 Oct

5:23 p.m.

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

Ross Bates

6:08 p.m.

I'm not sure if one of the developers can make better sense of the trace files, but attached is the output of 2 traces where a table stopped returning rows for a simple (=) operator. The "broken" trace file shows how the query returns no rows for something that exists. One thing to note, the top of the trace says that there are 8 rows in the table but there are only 4 rows in the table. The second table (chnl2) was created and populated in the same way as the first table (chnl2). You can see that the same query is returning rows. I'm using a nightly build. Anything else I can do to help troubleshoot, please let me know. -Ross On Mon, Oct 6, 2008 at 12:23 PM, Ross Bates 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 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 Nobile

6:24 p.m.

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 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 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@gmail.com -------------------------------------------------------------------------------

Ross Bates

6:45 p.m.

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 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 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 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@gmail.com

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

Carl Nobile

6:50 p.m.

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 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 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 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 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@gmail.com

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

Carl Nobile

7:07 p.m.

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 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 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 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 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 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@gmail.com

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

--

------------------------------------------------------------------------------- Carl J. Nobile (Software Engineer) carl.nobile@gmail.com

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

Niels Nes

7 Oct 7 Oct

10:24 a.m.

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@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@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@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@gmail.com --------------------------------------------------------------- ----------------

References

1. mailto:carl.nobile@gmail.com 2. mailto:rbates@gmail.com 3. mailto:stefan@konink.de 4. http://enigmail.mozdev.org/ 5. mailto:carl.nobile@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@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@cwi.nl

5926

Age (days ago)

5928

Last active (days ago)

List overview

Download

10 comments

4 participants

participants (4)

Carl Nobile
Niels Nes
Ross Bates
Stefan de Konink