Virtual Spatial Data and MySQL client

[Bart van den Eijnden]

Frank,

I have tracked down the issue by looking at the source code.

There was a problem with my MYSQL connect statement, now I use the following which works, note the name of the schema in front of the connect string, this seems to make the big difference:

<SrcDataSource>MYSQL:westholland,user=root,password=psv9570,port=3306,tables=bedrijven</SrcDataSource> 

I think an example would help on the MySQL driver page :-) :

http://www.gdal.org/ogr/drv_mysql.html

D:\ms4w\apps\stragisweb\htdocs\sites\mdwh\data>ogrinfo bedrijven_mysqlclient.ovf

ERROR 4: Update access not supported for VRT datasources.
user=root, password=16254705
Had to open data source read-only.
INFO: Open of `bedrijven_mysqlclient.ovf'
using driver `VRT' successful.
1: bedrijven (Point)

Best regards,
Bart

Bart van den Eijnden
Syncera IT Solutions
Postbus 270
2600 AG  DELFT

tel.nr.: 015-7512436
email: BEN at Syncera-ITSolutions.nl

>>> Bart van den Eijnden <BEN at SYNCERA-ITSOLUTIONS.NL> 07/22/05 09:35am >>>
Hi Frank,

I added the debug statement and the user is empty. Btw, on another note, how can one specify the schema to be used, or does one just prepend it to the tablename?

D:\ms4w\apps\stragisweb\htdocs\sites\mdwh\data>ogrinfo bedrijven_mysqlclient.ovf

ERROR 4: Update access not supported for VRT datasources.
user=, password=16253249
ERROR 1: MySQL connect failed for: user=root,password=psv9570,port=3306
Access denied for user 'BEN'@'localhost' (using password: YES)
FAILURE:
Unable to open datasource `bedrijven_mysqlclient.ovf' with the following drivers
...
  -> ESRI Shapefile
  -> UK .NTF
  -> SDTS
  -> TIGER
  -> S57
  -> MapInfo File
  -> DGN
  -> VRT
  -> AVCBin
  -> REC
  -> Memory
  -> CSV
  -> GML
  -> ODBC
  -> MySQL
  -> OCI

Ovf file used:

<OGRVRTDataSource>
    <OGRVRTLayer name="bedrijven">
        <SrcDataSource>MYSQL:user=root,password=psv9570,port=3306</SrcDataSource> 
 	<SrcLayer>bedrijven</SrcLayer> 
	<GeometryType>wkbPoint</GeometryType> 
        <LayerSRS>epsg:28992</LayerSRS>
	<GeometryField encoding="PointFromColumns" x="LCO_XCOORDINAAT" y="LCO_YCOORDINAAT"/> 
    </OGRVRTLayer>
</OGRVRTDataSource>

Best regards,
Bart

Bart van den Eijnden
Syncera IT Solutions
Postbus 270
2600 AG  DELFT

tel.nr.: 015-7512436
email: BEN at Syncera-ITSolutions.nl 

>>> Frank Warmerdam <fwarmerdam at GMAIL.COM> 07/21/05 06:56pm >>>
On 7/21/05, Bart van den Eijnden <BEN at syncera-itsolutions.nl> wrote:
> Hi list,
> 
> I can't connect to a MySQL database using the MySQL client in OGR. I am using MySQL client 4.1 on a 4.1 localhost database. It works fine through ODBC.
> 
> It looks like GDAL/OGR is trying to log in with a different user than I specify, the Windows user logged on to the computer, how come?
> 
> I get the following:
> 
> D:\ms4w\apps\stragisweb\htdocs\sites\mdwh\data>ogrinfo bedrijven_mysqlclient.ovf
> 
> ERROR 4: Update access not supported for VRT datasources.
> Warning 1: 'database=westholland' in MYSQL datasource definition not recognised
> and ignored.
> ERROR 1: MySQL connect failed for: user=root,password=psv9570,port=3306,database
> =westholland
> Access denied for user 'BEN'@'localhost' (using password: YES)
> FAILURE:

Bart,

Sorry, I don't know why that isn't working.  As I read the code, it
should have passed in root as the userid.   In the module:

  gdal/ogr/ogrsf_frmts/mysql/ogrmysqldatasource.cpp 

there is a call like:

    if( hConn
        && mysql_real_connect( hConn, 
                               oHost.length() ? oHost.c_str() : NULL,
                               oUser.length() ? oUser.c_str() : NULL,
                               oPassword.length() ? oPassword.c_str() : NULL,
                               oDB.length() ? oDB.c_str() : NULL,
                               nPort, NULL, 0 ) == NULL )
    {
        CPLError( CE_Failure, CPLE_AppDefined,
                  "MySQL connect failed for: %s\n%s", 
                  pszNewName + 6, mysql_error( hConn ) );
        mysql_close( hConn );
        hConn = NULL;
    }

My suggestion is that you add a printf() to report the actual arguments
being passed to this functions for debugging purposes. 

eg. 
  printf( "user=%s, password=%d\n", oUser.c_str(), oPassword.c_str() );

If the user reported by this is root then it is something funky deep
inside the mysql API.  If it is "BEN" or an empty string then we know
that something is going wrong in the parsing logic for the datasource
name (which you can follow up with me). 

Best regards,
-- 
---------------------------------------+--------------------------------------
I set the clouds in motion - turn up   | Frank Warmerdam, warmerdam at pobox.com 
light and sound - activate the windows | http://pobox.com/~warmerdam 
and watch the world go round - Rush    | Geospatial Programmer for Rent