[GRASS-user] Connecting GRASS to a MySQL database?

[Richard Chirgwin]

  On 23/09/10 5:13 PM, grass-user-request at lists.osgeo.org wrote:
> Message: 3
> Date: Wed, 22 Sep 2010 16:51:08 -0400 (EDT)
> From: PixelPusher<pixelpusher at cox.net>
> Subject: [GRASS-user] Connecting GRASS to a MySQL database?
> To:grass-user at lists.osgeo.org
> Message-ID:<cbcc5e.5f72.12b3b36ce33.Webtop.0 at cox.net>
> Content-Type: text/plain; charset=UTF-8; format=flowed; delsp=no
>
> Sorry for the n00bish question, but I'm confused by the GRASS on-line
> docs.
>
> I have a MySQL database running on my local computer. Let's say that the
> MySQL user name is TRD and he has access to a database called TRD where
> I want to store my StateBoundaries table.
>
> I can start the  GRASS (6.4.0) Python interface and select
> "Database->Manage Databases->Connect" from the pulldown menu. I am then
> faced with the choice of driver, where I select "mysql". But, then it
> needs the Database name. This is where I get lost. The string in the
> text field says, "$GISDBASE/$LOCATION_NAME/$MAPSET/TRD". I'm not sure
> where GRASS is getting the values that substitute for those things that
> look like script variables. I guess the "$LOCATION_NAME" and "$MAPSET"
> come from the location and mapset that I selected when GRASS started.
> But, where is "$GISDBASE" set?
>
> I also step through the log in process by setting the appropriate user
> name and password. I know that I can use this combination to access the
> MySQL database through its command line interface.
>
> The next thing I do is try to test the connection. However, this results
> in lots of errors like, "Cannot connect to MySQL, Access denied for
> user" and "ERROR: Unable to open database
> <$GISDBASE/$LOCATION_NAME/$MAPSET/TRD>". It looks like GRASS is not
> using the right username and password and not doing the variable
> substitution that I was expecting.
>
> Can anoyone give me some hints on how to get this thing started?
>
> Thanks!
> Thom
Thom,

Having taught a few people how to use Grass-GIS, I can say that database 
parameters confuse a great many of them!

The parameters you see are for the default dbf driver (Grass-GIS uses 
this driver as the default for any new mapset). For MySQL, you provide 
the database location, and also login.

So where you see the $GISDBASE/$LOCATION_NAME/$MAPSET/dbf/ string, you want:
database=host=<ip address>,dbname=TRD

But you'll also have to login. Eg (command line):

db.login user=TRD password="somepassword"

(the inverted commas are needed at the command line but not using the GUI)

Notes:

1. From now on, every map you make in this particular mapset will use 
the MySQL database as its default connection. Don't try to mix different 
database drivers within a mapset - you'll get a headache!

2. Database parameters are stored only within the mapset you're using. 
If you want to connect a different mapset to MySQL, you have to set its 
connection.

Richard Chirgwin