Oracle Spatial Data gets corrupt

[sumit321]

Bug is created using following configuration: Mapserver: 5.6.3 and 5.6.5 (C sharp mapscript) Oracle Spatial: 10.2.0.1.0 and 11g release 2 OS: Windows 7, vista and 2003 server.

Overview: If you call resultsGetShape it may update unique column when data source is Oracle Spatial. This behavior seems to be random and create duplicate value in unique column!

Following are steps to replicate bug: 1. Create one polygon layer in Oracle Spatial. 2. Add four features to it. 3. Execute following c sharp code

mapObj map = new mapObj("MyMap?"); layerObj TargetLayer? = map.getLayer(0); TargetLayer?.template = "dummy"; rectObj rect = TargetLayer?.getExtent(); if (TargetLayer?.queryByRect(TargetLayer?.map, rect) != (int)MS_RETURN_VALUE.MS_SUCCESS) {

return ;

} resultCacheObj resultTarget = TargetLayer?.getResults(); for (int resultCounterTarget = 0; resultCounterTarget < resultTarget.numresults; resultCounterTarget++) {

resultCacheMemberObj resultCacheMember = resultTarget.getResult(resultCounterTarget); shapeObj shapeTarget = new shapeObj((int)TargetLayer?.type);

//ERROR OCCURS AFTER FOLLOWING CODE LINE TargetLayer?.resultsGetShape(shapeTarget, resultCacheMember.shapeindex, resultCacheMember.tileindex); }

Further Analysis: When you call msOracleSpatialLayerResultGetShape, it tries to fetch item from oracle. When OCIAttrGet( (dvoid *)sthand->stmthp, (ub4)OCI_HTYPE_STMT,... (line 2289) is executed a record in Oracle Spatial is updated with duplicate entry. (Even if user don't have required privileges).

Attachments: Mapfile used to replicate bug. "Before" and "After" screen shots. Script to create table.

This ticket is with reference to following user forum thread.  http://osgeo-org.1803224.n2.nabble.com/Problems-with-MapServer-and-Oracle-Spatial-td5518303.html#a5533884

[bene]

I had the same problem:  http://osgeo-org.1803224.n2.nabble.com/msOracleSpatialLayerWhichShapes-OracleSpatial-error-td5048929.html#a5103572

[sumit321]

There are couple of things I would like to highlight...

-Column which is getting update with duplicate entry have primary key constraint!![[BR]]

-Cursor used to fetch data is read only!![[BR]]

Now that is really horrifying that you have read only cursor, no update rights and still you are able to update primary key with duplicate entry while reading data!!!

[bene]

Replying to sumit321:

There are couple of things I would like to highlight...
-Column which is getting update with duplicate entry have primary key constraint!![[BR]] -Cursor used to fetch data is read only!![[BR]] Now that is really horrifying that you have read only cursor, no update rights and still you are able to update primary key with duplicate entry while reading data!!!

Obviously we have a bug in Oracle. On the other hand: I guess, we have an additional bug in mapserver, which inadvertant "reveals"/"exploits" this Oracle-bug.

[aboudreault]

I just tried to import your sql file.... but I'm getting: ORA-02143: invalid STORAGE option. I'm not familiar with that storage option...if you have any hint.. let me know, Otherwise, will look at this monday.

[sumit321]

I tried to generate previous sql file from SQL Developer (11 g) and it seems to have lot of undesired statements.

To make life simpler I have created sql script manually and tested complete cycle to create table and generate bug. It is working fine. Please let me know if you need anything else as well.

-Sumit

Replying to aboudreault:

I just tried to import your sql file.... but I'm getting: ORA-02143: invalid STORAGE option. I'm not familiar with that storage option...if you have any hint.. let me know, Otherwise, will look at this monday.

[aboudreault]

Hi sumit321, I'm not able to reproduce the bug. My environment is Linux with Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 and MapServer 5.6.3. Unfortunately, I do not have any Windows machine. I wrote a script similar to your C# one in python and everything seems to work properly. Here's my log file:

[Mon Sep 20 10:20:44 2010].259780 msOracleSpatialLayerOpen called with: GEOM FROM UPDATEBUG USING UNIQUE BID SRID 4326 (Layer pointer 0x950be18)
[Mon Sep 20 10:21:14 2010].419824 msOracleSpatialLayerOpen. Shared connection not available. Creating one.
[Mon Sep 20 10:21:14 2010].419866 msConnPoolRegister(updatebug,user_testos/xxxxx@orthanc/testos,0x954e470)
[Mon Sep 20 10:21:14 2010].435603 msOracleSpatialLayerFreeItemInfo was called.
[Mon Sep 20 10:21:14 2010].435675 msOracleSpatialLayerFreeItemInfo was called.
[Mon Sep 20 10:21:14 2010].435698 msOracleSpatialLayerGetItems was called.
[Mon Sep 20 10:21:14 2010].436912 msOracleSpatialLayerInitItemInfo was called.
[Mon Sep 20 10:21:14 2010].436946 msOracleSpatialLayerInitItemInfo was called.
[Mon Sep 20 10:21:14 2010].436972 msOracleSpatialLayerWhichShapes was called.
[Mon Sep 20 10:21:14 2010].437040 msOracleSpatialLayerWhichShapes. Using this Sql to retrieve the data: SELECT BID, BID, GEOM FROM UPDATEBUG WHERE SDO_FILTER( GEOM, MDSYS.SDO_GEOMETRY(2003, :srid, NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),:ordinates ),'querytype=window') = 'TRUE'
[Mon Sep 20 10:21:14 2010].437062 msOracleSpatialLayerWhichShapes. Bind values: srid:4326   minx:-96.726322   miny:-25.243112  maxx:31.760882   maxy:56.977310 
[Mon Sep 20 10:21:14 2010].437166 msOracleSpatialLayerWhichShapes getting ordinate definition.
[Mon Sep 20 10:21:14 2010].441241 msOracleSpatialLayerWhichShapes converting to OCIColl.
[Mon Sep 20 10:21:14 2010].441425 msOracleSpatialLayerWhichShapes bind by name and object.
[Mon Sep 20 10:21:14 2010].441496 msOracleSpatialLayerWhichShapes name and object now bound.
[Mon Sep 20 10:21:14 2010].500266 msOracleSpatialLayerNextShape on layer 0x950be18, row_num: 0
[Mon Sep 20 10:21:14 2010].500443 msOracleSpatialLayerNextShape on layer 0x950be18, row_num: 1
[Mon Sep 20 10:21:14 2010].500491 msOracleSpatialLayerNextShape on layer 0x950be18, row_num: 2
[Mon Sep 20 10:21:14 2010].500529 msOracleSpatialLayerNextShape on layer 0x950be18, row_num: 3
[Mon Sep 20 10:21:14 2010].500566 msOracleSpatialLayerNextShape on layer 0x950be18, row_num: 4
[Mon Sep 20 10:21:14 2010].501157 msOracleSpatialLayerNextShape on layer 0x950be18, Fetched 0 more rows (4 total)
[Mon Sep 20 10:21:14 2010].501605 msOracleSpatialLayerResultGetShape was called. Using the record = 0 of 4. (shape: 2 should equal pkey: 2)
[Mon Sep 20 10:21:14 2010].501635 msOracleSpatialLayerResultGetShape: Fetching result from DB start: 0 end:-1 record: 0
[Mon Sep 20 10:21:14 2010].502924 msOracleSpatialLayerResultGetShape was called. Using the record = 1 of 4. (shape: 1 should equal pkey: 1)
[Mon Sep 20 10:21:14 2010].503079 msOracleSpatialLayerResultGetShape was called. Using the record = 2 of 4. (shape: 3 should equal pkey: 3)
[Mon Sep 20 10:21:14 2010].503208 msOracleSpatialLayerResultGetShape was called. Using the record = 3 of 4. (shape: 3 should equal pkey: 3)
[Mon Sep 20 10:21:14 2010].505885 msFreeMap(): freeing map at 0x94eff58.
[Mon Sep 20 10:21:14 2010].505913 msOracleSpatialLayerClose was called. Layer: 0x950be18, Layer name: updatebug. Layer connection: user_testos/xxxxxxxxxxx@orthanc/testos
[Mon Sep 20 10:21:14 2010].505983 msOracleSpatialLayerClose. Cleaning layerinfo handlers.
[Mon Sep 20 10:21:14 2010].506047 msOracleSpatialLayerClose. Release the Oracle Pool.
[Mon Sep 20 10:21:14 2010].506057 msConnPoolRelease(updatebug,user_testos/xxxxxxxxx@orthanc/testos,0x954e470)
[Mon Sep 20 10:21:14 2010].506066 msConnPoolClose(user_testos/xxxxxxxxx@orthanc/testos,0x954e470)
[Mon Sep 20 10:21:17 2010].183374 freeLayer(): freeing layer at 0x950be18.

I see that the unique value is duplicated in the sql query... but this doesn't crash anything.

[sumit321]

Hi Aboudreault,

As you may have notice with Python script, application may not crash upfront rather it does more serious damage by updating unique value (While reading record!). It raises few basic questions and problems.

1. How MapServer can update values while reading attributes.

2. How Oracle is allowing MapServer to update unique column with undesired value. 3. Updated column in unique column and if you have duplicate values in it (Conceptually you can never have duplicate values in unique column!), all your indexes created on this attribute are bound to go for a toss.
4. From application perspective, you are sitting on a time bomb which may anytime as your primary key is corrupt, your indexes are corrupt and referential integrity of your database is invalid.

I wish my unique bank a/c id Id was duplicated with Larry Ellison ;)

-Sumit

Replying to aboudreault:

Hi sumit321, I'm not able to reproduce the bug. My environment is Linux with Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 and MapServer 5.6.3. Unfortunately, I do not have any Windows machine. I wrote a script similar to your C# one in python and everything seems to work properly. Here's my log file: {{{ [Mon Sep 20 10:20:44 2010].259780 msOracleSpatialLayerOpen called with: GEOM FROM UPDATEBUG USING UNIQUE BID SRID 4326 (Layer pointer 0x950be18) [Mon Sep 20 10:21:14 2010].419824 msOracleSpatialLayerOpen. Shared connection not available. Creating one. [Mon Sep 20 10:21:14 2010].419866 msConnPoolRegister(updatebug,user_testos/xxxxx@orthanc/testos,0x954e470) [Mon Sep 20 10:21:14 2010].435603 msOracleSpatialLayerFreeItemInfo was called. [Mon Sep 20 10:21:14 2010].435675 msOracleSpatialLayerFreeItemInfo was called. [Mon Sep 20 10:21:14 2010].435698 msOracleSpatialLayerGetItems was called. [Mon Sep 20 10:21:14 2010].436912 msOracleSpatialLayerInitItemInfo was called. [Mon Sep 20 10:21:14 2010].436946 msOracleSpatialLayerInitItemInfo was called. [Mon Sep 20 10:21:14 2010].436972 msOracleSpatialLayerWhichShapes was called. [Mon Sep 20 10:21:14 2010].437040 msOracleSpatialLayerWhichShapes. Using this Sql to retrieve the data: SELECT BID, BID, GEOM FROM UPDATEBUG WHERE SDO_FILTER( GEOM, MDSYS.SDO_GEOMETRY(2003, :srid, NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),:ordinates ),'querytype=window') = 'TRUE' [Mon Sep 20 10:21:14 2010].437062 msOracleSpatialLayerWhichShapes. Bind values: srid:4326 minx:-96.726322 miny:-25.243112 maxx:31.760882 maxy:56.977310 [Mon Sep 20 10:21:14 2010].437166 msOracleSpatialLayerWhichShapes getting ordinate definition. [Mon Sep 20 10:21:14 2010].441241 msOracleSpatialLayerWhichShapes converting to OCIColl. [Mon Sep 20 10:21:14 2010].441425 msOracleSpatialLayerWhichShapes bind by name and object. [Mon Sep 20 10:21:14 2010].441496 msOracleSpatialLayerWhichShapes name and object now bound. [Mon Sep 20 10:21:14 2010].500266 msOracleSpatialLayerNextShape on layer 0x950be18, row_num: 0 [Mon Sep 20 10:21:14 2010].500443 msOracleSpatialLayerNextShape on layer 0x950be18, row_num: 1 [Mon Sep 20 10:21:14 2010].500491 msOracleSpatialLayerNextShape on layer 0x950be18, row_num: 2 [Mon Sep 20 10:21:14 2010].500529 msOracleSpatialLayerNextShape on layer 0x950be18, row_num: 3 [Mon Sep 20 10:21:14 2010].500566 msOracleSpatialLayerNextShape on layer 0x950be18, row_num: 4 [Mon Sep 20 10:21:14 2010].501157 msOracleSpatialLayerNextShape on layer 0x950be18, Fetched 0 more rows (4 total) [Mon Sep 20 10:21:14 2010].501605 msOracleSpatialLayerResultGetShape was called. Using the record = 0 of 4. (shape: 2 should equal pkey: 2) [Mon Sep 20 10:21:14 2010].501635 msOracleSpatialLayerResultGetShape: Fetching result from DB start: 0 end:-1 record: 0 [Mon Sep 20 10:21:14 2010].502924 msOracleSpatialLayerResultGetShape was called. Using the record = 1 of 4. (shape: 1 should equal pkey: 1) [Mon Sep 20 10:21:14 2010].503079 msOracleSpatialLayerResultGetShape was called. Using the record = 2 of 4. (shape: 3 should equal pkey: 3) [Mon Sep 20 10:21:14 2010].503208 msOracleSpatialLayerResultGetShape was called. Using the record = 3 of 4. (shape: 3 should equal pkey: 3) [Mon Sep 20 10:21:14 2010].505885 msFreeMap(): freeing map at 0x94eff58. [Mon Sep 20 10:21:14 2010].505913 msOracleSpatialLayerClose was called. Layer: 0x950be18, Layer name: updatebug. Layer connection: user_testos/xxxxxxxxxxx@orthanc/testos [Mon Sep 20 10:21:14 2010].505983 msOracleSpatialLayerClose. Cleaning layerinfo handlers. [Mon Sep 20 10:21:14 2010].506047 msOracleSpatialLayerClose. Release the Oracle Pool. [Mon Sep 20 10:21:14 2010].506057 msConnPoolRelease(updatebug,user_testos/xxxxxxxxx@orthanc/testos,0x954e470) [Mon Sep 20 10:21:14 2010].506066 msConnPoolClose(user_testos/xxxxxxxxx@orthanc/testos,0x954e470) [Mon Sep 20 10:21:17 2010].183374 freeLayer(): freeing layer at 0x950be18. }}} I see that the unique value is duplicated in the sql query... but this doesn't crash anything.

[aboudreault]

I'm not sure to follow you. My data do not change at all. There is no unique value updated. MapServer only executes SELECT statements. Have you been able to reproduce that bug with this test case on Unix?

[sumit321]

Hi Aboudreault, your log is suggesting that your values are getting updated. Observe following statements in your log..

[Mon Sep 20 10:21:14 2010].501605 msOracleSpatialLayerResultGetShape was called. Using the record = 0 of 4. (shape: 2 should equal pkey: 2)
[Mon Sep 20 10:21:14 2010].501635 msOracleSpatialLayerResultGetShape: Fetching result from DB start: 0 end:-1 record: 0
[Mon Sep 20 10:21:14 2010].502924 msOracleSpatialLayerResultGetShape was called. Using the record = 1 of 4. (shape: 1 should equal pkey: 1)
[Mon Sep 20 10:21:14 2010].503079 msOracleSpatialLayerResultGetShape was called. Using the record = 2 of 4. (shape: 3 should equal pkey: 3)
[Mon Sep 20 10:21:14 2010].503208 msOracleSpatialLayerResultGetShape was called. Using the record = 3 of 4. (shape: 3 should equal pkey: 3

If you execute "select * from updateBug", it should give you values 1, 2, 3, 3 for bid column. However if you execute "select bid from updateBug", it should give you 1, 2, 3, 4 for bid column. I think this could be because primary index is not allowing duplicate value ( which is correct). If you drop primary index and constraint, in that case both of above said queries will give you same result with duplicate values.

As of now I haven't tried this bug in unix box. Please let me know if you want me to replicate this bug in Unix box as well. However looking at your posted log, I am sure you will be able to verify this bug. - Sumit

Replying to aboudreault:

I'm not sure to follow you. My data do not change at all. There is no unique value updated. MapServer only executes SELECT statements. Have you been able to reproduce that bug with this test case on Unix?

Replying to aboudreault:

I'm not sure to follow you. My data do not change at all. There is no unique value updated. MapServer only executes SELECT statements. Have you been able to reproduce that bug with this test case on Unix?

[aboudreault]

ah. I effectively did a "select bid from updatebug;" to verify my data and didn't notice anything wrong. Now with a "select * from updatebug;" confirm that the data have changed.

[bene]

(just improved indentation for better understanding...)

mapObj map = new mapObj("MyMap"); 

layerObj TargetLayer = map.getLayer(0); 
TargetLayer.template = "dummy"; 
rectObj rect = TargetLayer.getExtent(); 

if (TargetLayer.queryByRect(TargetLayer.map, rect) != (int)MS_RETURN_VALUE.MS_SUCCESS) {
  return ; 
} 

resultCacheObj resultTarget = TargetLayer.getResults(); 
for (int resultCounterTarget = 0; resultCounterTarget < resultTarget.numresults; resultCounterTarget++) {
  resultCacheMemberObj resultCacheMember = resultTarget.getResult(resultCounterTarget); 
  shapeObj shapeTarget = new shapeObj((int)TargetLayer.type);

  //ERROR OCCURS AFTER FOLLOWING CODE LINE 
  TargetLayer.resultsGetShape(shapeTarget, resultCacheMember.shapeindex, resultCacheMember.tileindex); 
}

[aboudreault]

Hi, I've backported a patch in branch 5.6 in r10531. Can anyone test it and confirm me that it fixes the bug?

[bene]

Replying to aboudreault:

Hi, I've backported a patch in branch 5.6 in r10531. Can anyone test it and confirm me that it fixes the bug?

Wow!

I reproduced Sumit's testcase with a C-Program reliable.

After applying your patch the error didn't come anymore.

[sumit321]

Hi Aboudreault, It seems that MapServer is still crashing. In testing C sharp script, I added one more line in end; map.draw, it crashes the MapServer with memory error. Hope I am not missing anything. Good news is data is not replicating anymore. -Sumit

Replying to aboudreault:

Hi, I've backported a patch in branch 5.6 in r10531. Can anyone test it and confirm me that it fixes the bug?

[aboudreault]

Sumit, please verify if you had the same crash without my patch (with the map.draw line) and if so, create a new ticket. I'm going to investigate in it.

[sumit321]

Aboudreault,
Without your patch map.draw was working fine. It is after applying your patch that Mapserver is crashing.
-Sumit

Replying to aboudreault:

Sumit, please verify if you had the same crash without my patch (with the map.draw line) and if so, create a new ticket. I'm going to investigate in it.

[aboudreault]

I also got a "Aborted" memory error after applying the patch and adding a map.draw(). Here's the error I got without the patch with a map.draw():

Traceback (most recent call last):
  File "testOCI.py", line 20, in <module>
    map.draw()
  File "/home/aboudreault/opt/fgs/www/lib/python2.6/mapscript.py", line 1466, in draw
    def draw(*args): return _mapscript.mapObj_draw(*args)
_mapscript.MapServerError: msDrawMap(): Image handling error. Failed to draw layer named 'updatebug'.
msOracleSpatialLayerWhichShapes(): OracleSpatial error. Error: ORA-00600: internal error code, arguments: [17182], [0x24EAFCDA], [], [], [], [], [], []
 . Query statement: SELECT BID, GEOM FROM UPDATEBUG WHERE SDO_FILTER( GEOM, MDSYS.SDO_GEOMETRY(2003, :srid, NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),:ordinates ),'querytype=window') = 'TRUE' . Check your data statement.
aboudreault@ferocious:~/opt/fgs/www/htdocs/bugs/3541$ python testOCI.py

Let me dig into that.. :)

[aboudreault]

This error was not related to this bug. See ticket #3271. Patch has been backported. Please try again with this new patch: r10533.

[sumit321]

Hi Aboudreault,

Finally some good news! its working fine in my test script. Today I will test it in my actual application. But it seems that bug is fixed now.

-Sumit. Replying to aboudreault:

This error was not related to this bug. See ticket #3271. Patch has been backported. Please try again with this new patch: r10533.

[aboudreault]

Closing the ticket.