[mapserver-users] MySQL connection: so near, yet so far...

[Chris Green]

I am trying to establish a MySQL connection to a Mapserver 6.0 layer using
OGR. After much trial and error (mostly error) I have got to the point where
I have an ovf file which I can test using ogrinfo, but which I cannot make
work in a layer.

My ovf file (OGR_test.ovf) is  

<OGRVRTDataSource>
    <OGRVRTLayer name="OGR_test">
	
<SrcDataSource>mysql:testDB,user=root,password=mysql,port=3306,tables=testTa
ble</SrcDataSource>
        <SrcSQL>SELECT City, Latitude, Longitude FROM testTable</SrcSQL>
        <GeometryType>wkbPoint</GeometryType>
        <GeometryField encoding="PointFromColumns" x="Longitude"
y="Latitude"/>
    </OGRVRTLayer>
</OGRVRTDataSource>

I can successfully test this and see the data in testTable via:

~# ogrinfo var/www/vhosts/mysite/httpdocs/data/OGR_test.ovf


However when I create a test layer I cannot establish the OGR connection. I
am trying with:

LAYER
  NAME "testLayer"
  STATUS DEFAULT
  TYPE POINT
  CONNECTIONTYPE OGR
  CONNECTION "OGR_test.ovf"
  DATA "OGR_test"
  CLASS
    NAME "MyClass"
    STYLE
      SYMBOL 'circle'
      SIZE 15
      COLOR 0 255 0
    END
  END
END

This produces an "internal server error".

I have also tried using an absolute path to OGR_test.ovf, still no success.
It seems that I have not got the CONNECTION and DATA parameters right but
after reading the MySQL and the OGR sections in the Mapserver documentation
many times I still can't figure out what I'm doing wrong.

Can someone help?


Regards,



Chris


-----Original Message-----
From: mapserver-users-bounces at lists.osgeo.org
[mailto:mapserver-users-bounces at lists.osgeo.org] On Behalf Of Chris Green
Sent: 22 June 2011 10:09
To: 'Rahkonen Jukka'; mapserver-users at lists.osgeo.org
Subject: RE: [mapserver-users] Mapserver layer - MySQL connection

Jukka

It seems I celebrated too soon.....although this now succeeds:

~# ogrinfo mysql:testDB,user=root,password=mysql,port=3306 

...when I try to create an ovf file like so:

<OGRVRTDataSource>
    <OGRVRTLayer name="testLayer">
 
<SrcDataSource>mysql:testDB,user=root,password=mysql,port=3306,tables=testTa
ble</SrcDataSource>
        <SrcSQL>SELECT City, Latitude, Longitude FROM testTable</SrcSQL>
        <GeometryType>wkbPoint</GeometryType>
        <GeometryField encoding="PointFromColumns" x="Longitude"
y="Latitude"/>
    </OGRVRTLayer>
</OGRVRTDataSource>


I get the error "Unable to open datasource
'var/www/vhosts/mysite/httpdocs/OGR_test.ovf' with the following drivers."
This is followed by the list of drivers which includes MySQL.

Something wrong in my ovf file?



Chris

-----Original Message-----
From: mapserver-users-bounces at lists.osgeo.org
[mailto:mapserver-users-bounces at lists.osgeo.org] On Behalf Of Chris Green
Sent: 22 June 2011 08:29
To: 'Rahkonen Jukka'; mapserver-users at lists.osgeo.org
Subject: RE: [mapserver-users] Mapserver layer - MySQL connection

Jukka

You are a genius -it worked!


Thanks

-----Original Message-----
From: Rahkonen Jukka [mailto:Jukka.Rahkonen at mmmtike.fi] 
Sent: 22 June 2011 08:26
To: Chris Green; mapserver-users at lists.osgeo.org
Subject: Re: [mapserver-users] Mapserver layer - MySQL connection

Hi,

Try first by removing the space characters after separating commas.

-Jukka Rahkonen-


________________________________________
Lähettäjä: mapserver-users-bounces at lists.osgeo.org
[mapserver-users-bounces at lists.osgeo.org] k&#228;ytt&#228;j&#228;n Chris
Green [chris.green at ibstv.co.uk] puolesta
Lähetetty: 22. kesäkuuta 2011 10:12
Vastaanottaja: mapserver-users at lists.osgeo.org
Aihe: [mapserver-users] Mapserver layer - MySQL connection

Hi all

I’m using Mapserver 6.0 and I have GDAL  1.8.0 installed, running under
Ubuntu 10.04.  I am trying to make a connection from a layer to MySQL,
following the guidance given in the MySQL section of Mapserver
documentation. My problem is that when I try to connect using ogrinfo like:

~# ogrinfo mysql:testDB, user=root, password=mysql, port=3306

I get a mySQL connect failure message “ERROR 1: MySQL connect failed for:
testDB, Access denied for user’root’@’localhost’ (using password: NO)”

It seems that MySQL is refusing this connection request because the password
that I entered in the ogrinfo command is not being passed to MySQL and I
can’t find a way to overcome this. The same user name and password work fine
if I access MySQL directly rather than via ogrinfo.

Does anyone have an idea what my problem is?



Chris







_______________________________________________
mapserver-users mailing list
mapserver-users at lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users


_______________________________________________
mapserver-users mailing list
mapserver-users at lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users