Opened 13 years ago
Last modified 12 years ago
#4021 new defect
GetFeatureInfo using MySQL join doesn't work
Reported by: | dgambin | Owned by: | dmorissette |
---|---|---|---|
Priority: | normal | Milestone: | |
Component: | OGR Support | Version: | 6.0 |
Severity: | normal | Keywords: | |
Cc: | sdlime, aboudreault |
Description
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"
Change History (13)
comment:1 by , 13 years ago
Component: | MapServer C Library → OGR Support |
---|---|
Owner: | changed from | to
comment:2 by , 13 years ago
comment:3 by , 13 years ago
Cc: | added |
---|
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.)
comment:4 by , 13 years ago
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
comment:5 by , 13 years ago
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:
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 ()
comment:6 by , 13 years ago
Apologies for bad formatting above. I made copy/paste and submitted without previewing first :-(
comment:7 by , 13 years ago
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:
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 ()
comment:10 by , 12 years ago
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
comment:11 by , 12 years ago
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
follow-up: 13 comment:12 by , 12 years ago
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
It would be useful to know when this is planned to be solved. Otherwise, I have to migrate from MySQL to PostgreSQL