MySql OGR connection failure

[Karl Suiter]

Hi All,

  OK, I'm stumped.  I'm running Mapserver cgi on Fedora Core 5 with no
problems.  However, I am trying to map point data stored in a MySql
database.

  I've configured and compiled the cvs release of gdal/ogr sucessfully
for both MySql and ODBC connectivity.

      gdal/ogr
      ./configure \
      --with-geotiff=/usr/local/lib \
      --with-libtiff=/usr/lib \
      --with-xerces \
      --with-mysql \
      --with-odbc=yes \
      --with-geos \
      --without-libtool \
      --with-png \
      --with-jpeg \
      --with-gif

      ogrinfo --formats
      Supported Formats:
        -> "ESRI Shapefile" (read/write)
        -> "MapInfo File" (read/write)
        -> "UK .NTF" (readonly)
        -> "SDTS" (readonly)
        -> "TIGER" (read/write)
        -> "S57" (read/write)
        -> "DGN" (read/write)
        -> "VRT" (readonly)
        -> "AVCBin" (readonly)
        -> "REC" (readonly)
        -> "Memory" (read/write)
        -> "CSV" (read/write)
        -> "GML" (read/write)
        -> "KML" (read/write)
        -> "Interlis 1" (read/write)
        -> "Interlis 2" (read/write)
        -> "ODBC" (read/write)
        -> "PGeo" (readonly)
        -> "OGDI" (readonly)
        -> "MySQL" (read/write)

  Mapserver 4.8.4 has also been compiled with OGR support ...

      mapserver
       ./configure \
      --with-gif \
      --with-png \
      --with-jpg \
      --with-pdf \
      --with-wbmp \
      --with-svg \
      --with-ogr=/usr/local/bin/gdal-config \
      --with-gdal=/usr/local/bin/gdal-config \
      --with-proj=/usr/include \
      --with-freetype=/usr/bin/freetype-config \
      --with-gd=/usr/local/include \
      --with-curl-config=/usr/bin/curl-config \
      --with-wfsclient \
      --with-wmsclient \
      --with-threads \
      --with-wcs \
      --with-wfs \
      --with-mysql=/usr/bin/mysql_config \
      --with-odbc \
      --with-httpd=/usr/sbin/httpd \
      --with-libiconv=/usr \
      --with-geos= \
      --enable-debug

      ./mapserv -v
      MapServer version 4.8.4 OUTPUT=GIF OUTPUT=PNG OUTPUT=JPEG OUTPUT=WBMP
      OUTPUT=SVG SUPPORTS=PROJ SUPPORTS=FREETYPE SUPPORTS=WMS_SERVER
      SUPPORTS=WMS_CLIENT SUPPORTS=WFS_SERVER SUPPORTS=WFS_CLIENT
      SUPPORTS=WCS_SERVER SUPPORTS=THREADS INPUT=EPPL7 INPUT=OGR
      INPUT=GDAL INPUT=SHAPEFILE DEBUG=MSDEBUG

  I'm able to connect to the database using ogrinfo with no problem
  (I can also connect using ODBC).

      ogrinfo MySQL:xxxx01,user=xxxx,password=xxxx01,host=localhost,
              port=3306,tables=division_dist
      INFO: Open of `MySQL:xxxx01,user=xxxx,password=xxxx01,host=localhost,
                     port=3306,tables=division_dist'
            using driver `MySQL' successful.
      1: division_dist (None)

  Now comes the part that is not working.  I can create both a .ovf connection
file and also embed the connection string in my .map file like so ...

     LAYER
       NAME MAP_POINTS
       STATUS OFF    TYPE POINT
       CONNECTIONTYPE OGR
       CONNECTION
    
        "<OGRVRTDataSource>
          <OGRVRTLayer name='ADMIN_REGIONS'>
           <SrcDataSource>MYSQL:xxxx01,user=xxxx,password=xxxx01,host=localhost,
             port=3306,tables=division_dist</SrcDataSource>
           <SrcSQL>SELECT latitude_deg, longitude_deg from division_dist where
             pest_id = %pestid% AND (latitude_deg &lt; &gt; 0 AND longitude_deg
             &lt; &gt; 0)</SrcSQL>
           <GeometryType>wkbPoint</GeometryType>
           <LayerSRS>WGS84</LayerSRS>
           <GeometryField encoding='PointFromColumns' x='latitude_deg'
            y='longitude_deg'/>
          </OGRVRTLayer>
         </OGRVRTDataSource>"

       DATA "MAP_POINTS"
        STATUS default
         CLASS
          STYLE
           SYMBOL 'circle'
            SIZE 5
            COLOR 255 0 0
            OUTLINECOLOR 0 0 0
           END
         END
     END

  However, calling this layer in a browser gives the following error

      msDrawMap(): Image handling error. Failed to draw layer named
                   'MAP_POINTS'.
      msOGRFileOpen(): OGR error. Open failed for OGR connection
      ` MySQL:xxxx01,user=xxxx,password=xxxx01,host=localhost,
       tables=division_dist '.
      Failed to open datasource `MySQL:xxxx01,user=xxxx,password=xxxx01,
      host=localhost,tables=division_dist'.

  The same error occurs when I use the ODBC driver.  FYI - when I run the .ovf
file usign ogrinfo, I can connect and return the data from the MySql database.

  The only thing I can determine is that mapserver is not picking up the
LD_LIBRARY_PATH is not being set.  I've added LD_LIBRARY_PATH to httpd.conf and
/etc/ld.so.conf and restarted the server.  Still no go.  I even attempted to
create a wrapper around the mapserv cgi (exporting LD_LIBRARY_PATH and then
calling mapserv) but this did not work correctly.

  Lastly, this doesn't appear to be a permissions issue.  When I use the MySql
root login and password, the conection still fails.

  If anyone has solved this problem and be willing to share the secret?

Cheers,

Karl