[mapserver-users] ogr connect to mysql

[Sven Burbeck]

Hi group,
I have a question about ogr-connect to MySql database.

Yesterday I encountered that my MySQL-DB logged several SQL-Requests for 
a single (simple) MapServer request.
My mapfile stores a SQL-Statement like that:
##
SELECT centroid(b.geom), b.id, a.name, date(b.measure_date) as date, 
b.value as wert
FROM locality a RIGHT JOIN result b ON a.id = b.id
##

However, my MySQL-DB logs the following:
##
111007  7:24:22    19 Connect   user_xy at localhost on myDB
                    19 Query     SHOW TABLES
                    19 Query     DESCRIBE `table_1`
                    19 Query     SELECT srid FROM geometry_columns WHERE 
f_table_name = 'table_1'
                    19 Query     DESCRIBE `table_2`
                    19 Query     SELECT srid FROM geometry_columns WHERE 
f_table_name = 'table_2'
                    19 Query     DESCRIBE `table_3`
                    19 Query     SELECT srid FROM geometry_columns WHERE 
f_table_name = 'table_3'
                    19 Query     DESCRIBE `table_4`
                    19 Query     SELECT srid FROM geometry_columns WHERE 
f_table_name = 'table_4'
            ...
            ...
                    19 Query     SELECT centroid(b.geom), b.id, a.name, 
date(b.measure_date) as date, b.value as wert FROM locality a RIGHT JOIN 
result b ON a.id = b.id
                    19 Quit
##

I wondered where all these "SHOW TABLES", "DESCRIBE table..." came from, 
since the results are completely unnecessary in order to retrieve the 
final "SELECT centroid(b.geom)..." resultset, aren't they?

So I did a simple OGR check.
First for the table I would like to retrieve the "geom" column from:
##
ogrinfo MYSQL:myDB,user=user_xy,password=pwd_xy,port=3306 result -summary
INFO: Open of `MYSQL:myDB,user=user_xy,password=pwd_xy,port=3306'
       using driver `MySQL' successful.

Layer name: result
Geometry: Polygon
Feature Count: 1680
Extent: (6.034624, 47.396320) - (15.028760, 55.023074)
Layer SRS WKT:
GEOGCS["GCS_WGS_1984",
     DATUM["WGS_1984",
         SPHEROID["WGS_1984",6378137,298.257223563]],
     PRIMEM["Greenwich",0],
     UNIT["Degree",0.017453292519943295]]
FID Column = some_column_with_integers
Geometry Column = geom
...
##

Next check was just for an ogrinfo-connect with the sql-statement 
attached directly:
##
ogrinfo MYSQL:myDB,user=user_xy,password=pwd_xy,port=3306 result -sql 
'SELECT centroid(b.geom), b.id, a.name, date(b.measure_date) as date, 
b.value as wert FROM locality a RIGHT JOIN result b ON a.id = b.id'
##

T my surprise all these ogrinfo requests invoked the described bulk of 
SQL-statements. Apparently it doesn't matter if I use table-join or not.

So my question is:
Does Mapserver connect to MySQL using the "ogrinfo" request and is there 
any way to suppress all these (in my opinion) unnecessary "Describe 
table ... / SELECT srid..." queries?

These are the versions I use:
MapServer version 5.6.5
GDAL 1.8.0, released 2011/01/12
mysql  Ver 14.14 Distrib 5.5.10, for Linux (x86_64) using readline 5.1


Any help would be appreciated!

thanks in advance,
Sven

-- 
____________________________
Bundesamt für Strahlenschutz (BfS)
SW 2.1
Rosastr. 9
D-79098 Freiburg
phone: ++49 (0)3018 333 6741
fax: ++49 (0)3018 10 333 6741
____________________________

-- 
____________________________
Bundesamt für Strahlenschutz (BfS)
SW 2.1
Rosastr. 9
D-79098 Freiburg
phone: ++49 (0)3018 333 6741
fax: ++49 (0)3018 10 333 6741
____________________________