GetFeatureInfo using MySQL join doesn't work

[dgambin]

Trying to do GetFeatureInfo? request with layer joined to MySQL table returns segmentation fault. Works normally with PostgreSQL database.

All the details are described in mailing list posts "GetFeatureInfo? using MySQL join"

[dgambin]

It would be useful to know when this is planned to be solved. Otherwise, I have to migrate from MySQL to PostgreSQL

[dmorissette]

Not sure why this was moved to OGR component since it has to do with native MySQL vs Postgresql join support.

A complete test case to reproduce the issue would help (mapfile, data layers, sample request, etc.)

[sdlime]

The examples I saw used OGR connections, perhaps I missed something. Getting all info in the ticket rather than referring to a mailing list thread would indeed help. Steve

[dgambin]

The only examples I saw using MySQL in JOIN clause were using CONNECTIONTYPE OGR. Here is the part of my mapfile:

LAYER ...

NAME "mylayer" DATA "my" ... ... DUMP TRUE TEMPLATE "template.html" HEADER "template_header.html" FOOTER "template_footer.html" JOIN

NAME "test" CONNECTION "MySQL:gis,host=localhost,user=gis,password=gis"

# CONNECTION "PG:host=localhost user=postgres password=postgres dbname=gis port=5432"

CONNECTIONTYPE ogr TABLE "mytable" FROM "KO_KC" TO "broj_ko_kc" TYPE ONE-TO-MANY TEMPLATE "template_join.html"

END

where KO_KC is the attribute from my.shp and broj_ko_kc from MySQL table (both strings).

Sample request which generates the segmentation fault is:

 http://www.mysite.hr/cgi-bin/mywms61?SERVICE=WMS&VERSION=1.1.1&REQUEST=GetFeatureInfo&BBOX=5403800,4981350,5403980,4981460&SRS=EPSG:31275&WIDTH=400&HEIGHT=300&LAYERS=mylayer&STYLES=&FORMAT=image/png&TRANSPARENT=true&QUERY_LAYERS=mylayer&INFO_FORMAT=gml&X=200&Y=100

All this works if I comment the MySQL part and uncomment the PostgreSQL part. I have also tried many other combinations but each time I get segmentation fault.

Also, when I do: shp2img -m my.map -o image.png -l "mylayer" -all_debug 3, I get:

[Tue Sep 13 08:58:04 2011].271467 msLoadMap(): 0.029s [Tue Sep 13 08:58:04 2011].356888 msDrawMap(): Layer 0 (katastar_vodnjan_poligon), 0.083s [Tue Sep 13 08:58:04 2011].356943 msDrawMap(): Drawing Label Cache, 0.000s [Tue Sep 13 08:58:04 2011].356956 msDrawMap() total time: 0.085s [Tue Sep 13 08:58:04 2011].399386 msSaveImage() total time: 0.042s [Tue Sep 13 08:58:04 2011].402773 msPOSTGRESQLJoinClose() already close or never opened. [Tue Sep 13 08:58:04 2011].402821 shp2img total time: 0.161s

Trying to debug with gdb (on Linux Debian) gave me this:

Program received signal SIGTRAP, Trace/breakpoint trap. 0x00007f8b8d76c8b7 in ?? () from /lib64/ld-linux-x86-64.so.2

and with backtrace (bt):

#0 0x00007f8b8d76c8b7 in ?? () from /lib64/ld-linux-x86-64.so.2 #1 0x00007f8b8d75b1bc in ?? () from /lib64/ld-linux-x86-64.so.2 #2 0x00007f8b8d76bb35 in ?? () from /lib64/ld-linux-x86-64.so.2 #3 0x00007f8b8d75931b in ?? () from /lib64/ld-linux-x86-64.so.2 #4 0x00007f8b8d758a68 in ?? () from /lib64/ld-linux-x86-64.so.2 #5 0x0000000000000002 in ?? () #6 0x00007fff531bc8e2 in ?? () #7 0x0000000000000000 in ?? ()

Another try to debug the same on another machine (Mac OS) gave me this:

Program received signal EXC_BAD_ACCESS, Could not access memory. Reason: KERN_INVALID_ADDRESS at address: 0x0000000000000000 0x00007fff88337c00 in strlen () (gdb) bt #0 0x00007fff88337c00 in strlen () #1 0x00000001cf77aa67 in msPOSTGRESQLJoinConnect () #2 0x00000001cf6b578b in msReturnNestedTemplateQuery () #3 0x00000001cf70bbd9 in msWMSFeatureInfo () #4 0x00000001cf710584 in msWMSDispatch () #5 0x00000001cf7389e3 in msOWSDispatch () #6 0x00000001cf67728d in main ()

[dgambin]

Apologies for bad formatting above. I made copy/paste and submitted without previewing first :-(

[dmorissette]

Re-submitting comment:5 with formatting:

The only examples I saw using MySQL in JOIN clause were using CONNECTIONTYPE OGR. Here is the part of my mapfile:

 LAYER
 ...
   NAME "mylayer"
   DATA "my"
   ...
   ...
   DUMP TRUE
   TEMPLATE "template.html"
   HEADER "template_header.html"
   FOOTER "template_footer.html"
   JOIN
     NAME "test"
     CONNECTION "MySQL:gis,host=localhost,user=gis,password=gis"
 #    CONNECTION "PG:host=localhost user=postgres password=postgres
 dbname=gis port=5432"
     CONNECTIONTYPE ogr
     TABLE "mytable"
     FROM "KO_KC"
     TO "broj_ko_kc"
     TYPE ONE-TO-MANY
     TEMPLATE "template_join.html"
   END

where KO_KC is the attribute from my.shp and broj_ko_kc from MySQL table (both strings).

Sample request which generates the segmentation fault is:

 http://www.mysite.hr/cgi-bin/mywms61?SERVICE=WMS&VERSION=1.1.1&REQUEST=GetFeatureInfo&BBOX=5403800,4981350,5403980,4981460&SRS=EPSG:31275&WIDTH=400&HEIGHT=300&LAYERS=mylayer&STYLES=&FORMAT=image/png&TRANSPARENT=true&QUERY_LAYERS=mylayer&INFO_FORMAT=gml&X=200&Y=100

All this works if I comment the MySQL part and uncomment the PostgreSQL part. I have also tried many other combinations but each time I get segmentation fault.

Also, when I do: shp2img -m my.map -o image.png -l "mylayer" -all_debug 3, I get:

 [Tue Sep 13 08:58:04 2011].271467 msLoadMap(): 0.029s
 [Tue Sep 13 08:58:04 2011].356888 msDrawMap(): Layer 0
 (katastar_vodnjan_poligon), 0.083s
 [Tue Sep 13 08:58:04 2011].356943 msDrawMap(): Drawing Label Cache, 0.000s
 [Tue Sep 13 08:58:04 2011].356956 msDrawMap() total time: 0.085s
 [Tue Sep 13 08:58:04 2011].399386 msSaveImage() total time: 0.042s
 [Tue Sep 13 08:58:04 2011].402773 msPOSTGRESQLJoinClose() already close or
 never opened.
 [Tue Sep 13 08:58:04 2011].402821 shp2img total time: 0.161s

Trying to debug with gdb (on Linux Debian) gave me this:

 Program received signal SIGTRAP, Trace/breakpoint trap.
 0x00007f8b8d76c8b7 in ?? () from /lib64/ld-linux-x86-64.so.2

and with backtrace (bt):

 #0  0x00007f8b8d76c8b7 in ?? () from /lib64/ld-linux-x86-64.so.2
 #1  0x00007f8b8d75b1bc in ?? () from /lib64/ld-linux-x86-64.so.2
 #2  0x00007f8b8d76bb35 in ?? () from /lib64/ld-linux-x86-64.so.2
 #3  0x00007f8b8d75931b in ?? () from /lib64/ld-linux-x86-64.so.2
 #4  0x00007f8b8d758a68 in ?? () from /lib64/ld-linux-x86-64.so.2
 #5  0x0000000000000002 in ?? ()
 #6  0x00007fff531bc8e2 in ?? ()
 #7  0x0000000000000000 in ?? ()

Another try to debug the same on another machine (Mac OS) gave me this:

 Program received signal EXC_BAD_ACCESS, Could not access memory.
 Reason: KERN_INVALID_ADDRESS at address: 0x0000000000000000
 0x00007fff88337c00 in strlen ()
 (gdb) bt
 #0  0x00007fff88337c00 in strlen ()
 #1  0x00000001cf77aa67 in msPOSTGRESQLJoinConnect ()
 #2  0x00000001cf6b578b in msReturnNestedTemplateQuery ()
 #3  0x00000001cf70bbd9 in msWMSFeatureInfo ()
 #4  0x00000001cf710584 in msWMSDispatch ()
 #5  0x00000001cf7389e3 in msOWSDispatch ()
 #6  0x00000001cf67728d in main ()

[sdlime]

What data type is the column being joined to in MySQL?

Steve

[dgambin]

It is string column

[sdlime]

Ok, I *finally* got a bit of time to look at this. I'm not sure how the examples initially posted can even work since OGR is not a valid connection type in a JOIN object- I guess that's why I assigned to OGR support initially. I suspect the string OGR is being mapped to a constant that is somehow valid in the context of a join but even that doesn't make sense.

I did get MySQL to work using the following syntax:

JOIN

NAME 'test-join' CONNECTIONTYPE MYSQL CONNECTION 'host:username:password:table' TABLE 'table' FROM 'from-key' TO 'to-key'

END

The docs look to be inadequate (no reference to MySQL) but they don't even hint at using an OGR connection string. Where did that come from?

Anyway, can you try the above syntax?

Steve

[dgambin]

It doesn't work for me but maybe I wrote something wrong. Did you mean "dbname" instead of "table" in:

CONNECTION 'host:username:password:table'

I tried with CONNECTION "localhost:myusername:mypassword:mydbname", but it doesn't work. It works with CONNECTIONTYPE POSTGRESQL and CONNECTION "host=localhost user=myusername password=mypassword dbname=mydbname port=5432"

Regarding connection type - It works with either OGR or POSTGRESQL if CONNECTION is to postgresql. OGR conection type is written in mapfile example here: http://mapserver.org/es/mapfile/join.html and in several inetrnet posts too

[sdlime]

Whoops, sorry, yes it should be dbname instead of table. I can't get to my test box to post the actual mapfile snippet but will do so ASAP. The docs are definitely wrong with respect to OGR. It's a coincidence that the constants for the OGR connection type and the Postgres database connection type have the same value. I'll file tickets for:

• no MySQL in the docs
• change OGR to POSTGRES in the docs
• make the constants for MS_CONNECTION_TYPE and MS_JOIN_CONNECTION_TYPE mutually exclusive

Steve

[sdlime]

Replying to sdlime:

See tickets #4077 and #4078...