db.connect: connection to remote database does not work

[mlennert]

In a freshly compiled 70release:

createdb -h myRemoteServer testgrass
grass70 -c /data/GRASS/DATA7/nc_spm_08/postgres_test
db.connect driver=pg database="host=myRemoteServer,dbname=testgrass"
> db.tables -p
DBMI-PostgreSQL erreur de pilote :
Connection failed.
FATAL:  la base de données « testgrass » n'existe pas


DBMI-PostgreSQL erreur de pilote :
Connection failed.
FATAL:  la base de données « testgrass » n'existe pas


ERREUR :Unable to open database
        <host=myRemoteServer,dbname=testgrass>

and

> g.copy vect=boundary_county,bc
Copy vector <boundary_county@PERMANENT> to current mapset as <bc>
DBMI-PostgreSQL driver error:
Connection failed.
FATAL:  la base de données « testgrass » n'existe pas


DBMI-PostgreSQL driver error:
Connection failed.
FATAL:  la base de données « testgrass » n'existe pas


WARNING: Unable to open database
         <host=myRemoteServer,dbname=testgrass> by driver
         <pg>
no database is open
no database is open
WARNING: Unable to copy table <bc>
WARNING: Unable to copy table <boundary_county> for layer 1 from
         <boundary_county@PERMANENT> to <bc>

but

> psql -h myRemoteServer -l | grep testgrass
 testgrass              | mlennert    | UTF8      | C       | C     | 

[mlennert]

g.gisenv set=DEBUG=3
db.tables -p
D1/3: G_set_program_name(): db.tables
D2/3: G_file_name(): path = /data/GRASS/DATA7/nc_spm_08/postgres_test
D2/3: dbDbmscap(): opendir [/data/home/mlennert/SRC/GRASS/grass70_release/dist.x86_64-pc-linux-gnu/driver/db/]
D2/3: dbDbmscap(): opendir [/data/home/mlennert/SRC/GRASS/grass70_release/dist.x86_64-pc-linux-gnu/driver/db/]
D1/3: db_d_init_error(): PostgreSQL
D3/3: db_get_login(): drv=[pg] db=[host=myRemoteServer,dbname=testgrass]
D3/3: read_file(): DB login file = </home/mlennert/.grass7/dblogin>
D3/3: login file does not exist
D3/3: db_driver_open_database(): driver=pg database definition = 'host=myRemoteServer,dbname=testgrass'
D3/3: parse_conn: 'host=myRemoteServer,dbname=testgrass'
D3/3: token 0 : host=myRemoteServer
D3/3: token 1 : dbname=testgrass
D3/3: db_get_login(): drv=[pg] db=[host=myRemoteServer,dbname=testgrass]
D3/3: read_file(): DB login file = </home/mlennert/.grass7/dblogin>
D3/3: login file does not exist
D3/3: db_driver_open_database(): host = (null), port = (null), options = (null), tty = (null), dbname = testgrass, user = (null), password = (null) schema = (null)

db.login driver=pg database=host=myRemoteServer,dbname=testgrass
db.tables -pD1/3: G_set_program_name(): db.tables
D2/3: G_file_name(): path = /data/GRASS/DATA7/nc_spm_08/postgres_test
D2/3: dbDbmscap(): opendir [/data/home/mlennert/SRC/GRASS/grass70_release/dist.x86_64-pc-linux-gnu/driver/db/]
D2/3: dbDbmscap(): opendir [/data/home/mlennert/SRC/GRASS/grass70_release/dist.x86_64-pc-linux-gnu/driver/db/]
D1/3: db_d_init_error(): PostgreSQL
D3/3: db_get_login(): drv=[pg] db=[host=myRemoteServer,dbname=testgrass]
D3/3: read_file(): DB login file = </home/mlennert/.grass7/dblogin>
D3/3: ret = 2 : drv=[pg] db=[host=myRemoteServer,dbname=testgrass] usr=[] pwd=[] host=[], port=[]
D3/3: db_driver_open_database(): driver=pg database definition = 'host=myRemoteServer,dbname=testgrass'
D3/3: parse_conn: 'host=myRemoteServer,dbname=testgrass'
D3/3: token 0 : host=myRemoteServer
D3/3: token 1 : dbname=testgrass
D3/3: db_get_login(): drv=[pg] db=[host=myRemoteServer,dbname=testgrass]
D3/3: read_file(): DB login file = </home/mlennert/.grass7/dblogin>
D3/3: ret = 2 : drv=[pg] db=[host=myRemoteServer,dbname=testgrass] usr=[] pwd=[] host=[], port=[]
D3/3: db_driver_open_database(): host = (null), port = (null), options = (null), tty = (null), dbname = testgrass, user = (null), password = (null) schema = (null)

So the host name gets lost somewhere on the way.

[martinl]

Replying to mlennert:

createdb -h myRemoteServer testgrass
grass70 -c /data/GRASS/DATA7/nc_spm_08/postgres_test
db.connect driver=pg database="host=myRemoteServer,dbname=testgrass"

did you tried

db.connect driver=pg database=testgrass
db.login driver=pg database=testgrass host=myRemoteServer

? Martin

[mlennert]

Replying to martinl:

Replying to mlennert:

createdb -h myRemoteServer testgrass
grass70 -c /data/GRASS/DATA7/nc_spm_08/postgres_test
db.connect driver=pg database="host=myRemoteServer,dbname=testgrass"

did you tried

db.connect driver=pg database=testgrass
db.login driver=pg database=testgrass host=myRemoteServer

Doesn't work:

rm .grass7/dblogin
db.connect driver=pg database=testgrass
db.login driver=pg database=testgrass host=myRemoteServer
db.tables -p
DBMI-PostgreSQL erreur de pilote :
Connection failed.
FATAL:  la base de données « testgrass » n'existe pas


DBMI-PostgreSQL erreur de pilote :
Connection failed.
FATAL:  la base de données « testgrass » n'existe pas


ERREUR :Unable to open database <testgrass>

and with DEBUG=3

D1/3: G_set_program_name(): db.tables
D2/3: G_file_name(): path = /data/GRASS/DATA7/nc_spm_08/postgres_test
D2/3: dbDbmscap(): opendir [/data/home/mlennert/SRC/GRASS/grass70_release/dist.x86_64-pc-linux-gnu/driver/db/]
D2/3: dbDbmscap(): opendir [/data/home/mlennert/SRC/GRASS/grass70_release/dist.x86_64-pc-linux-gnu/driver/db/]
D1/3: db_d_init_error(): PostgreSQL
D3/3: db_get_login(): drv=[pg] db=[testgrass]
D3/3: read_file(): DB login file = </home/mlennert/.grass7/dblogin>
D3/3: ret = 2 : drv=[pg] db=[testgrass] usr=[] pwd=[] host=[], port=[]
D3/3: db_driver_open_database(): driver=pg database definition = 'testgrass'
D3/3: parse_conn: 'testgrass'
D3/3: db_get_login(): drv=[pg] db=[testgrass]
D3/3: read_file(): DB login file = </home/mlennert/.grass7/dblogin>
D3/3: ret = 2 : drv=[pg] db=[testgrass] usr=[] pwd=[] host=[], port=[]
D3/3: db_driver_open_database(): host = (null), port = (null), options = (null), tty = (null), dbname = testgrass, user = (null), password = (null) schema = (null)

[mlennert]

Replying to martinl:

Replying to mlennert:

createdb -h myRemoteServer testgrass
grass70 -c /data/GRASS/DATA7/nc_spm_08/postgres_test
db.connect driver=pg database="host=myRemoteServer,dbname=testgrass"

did you tried

db.connect driver=pg database=testgrass
db.login driver=pg database=testgrass host=myRemoteServer

And if this is how it is supposed to be done, then we need to change the man page.

It just seems a bit redundant to have to indicate driver and database twice...

[mlennert]

The VAR file in the mapset contains this:

DB_DRIVER: pg
DB_DATABASE: testgrass

i.e. no info about the host. Is this normal ?

[mlennert]

Replying to mlennert:

The VAR file in the mapset contains this:

DB_DRIVER: pg
DB_DATABASE: testgrass

i.e. no info about the host. Is this normal ?

Replying to myself: this is when I use db.connect without 'host=' in the database string. When I use 'host=', I get:

DB_DRIVER: pg DB_DATABASE: host=myRemoteHost,dbname=testgrass

[mlennert]

Replying to mlennert:

Replying to martinl:

Replying to mlennert:

createdb -h myRemoteServer testgrass
grass70 -c /data/GRASS/DATA7/nc_spm_08/postgres_test
db.connect driver=pg database="host=myRemoteServer,dbname=testgrass"

did you tried

db.connect driver=pg database=testgrass
db.login driver=pg database=testgrass host=myRemoteServer

I just noticed that the first sentence with the most important information is missing:

Yes, I tried, but it didn't make a difference. Exact same error as before and DEBUG=3 gives me:

D3/3: db_driver_open_database(): host = (null), port = (null), options = (null), tty = (null), dbname = testgrass, user = (null), password = (null) schema = (null)

And if this is how it is supposed to be done, then we need to change the man page.

It just seems a bit redundant to have to indicate driver and database twice...

[mlennert]

BTW: I get the same errors in trunk.

[mlennert]

Getting closer:

The issue seems to be in the reading of the dblogin file.

My file contains;

pg|testgrass|||myRemoteHost|

But the debug message show:

D3/3: read_file(): DB login file = </home/mlennert/.grass7/dblogin>
D3/3: ret = 2 : drv=[pg] db=[testgrass] usr=[] pwd=[] host=[], port=[]

Looking at the sscanf call that reads the data from the file (in lib/db/dbmi_base/login.c, line 118, I see:

        ret = sscanf(buf, "%[^|]|%[^|]|%[^|]|%[^|]|%[^|]|%[^\n]",
                     dr, db, usr, pwd, host, port);

Somehow this doesn't seem to read the line as it should...

[mlennert]

The issue arises when some of the fields are empty, as for usr and pwd in my example. Anything from that point on is ignored. When I set a username and password with db.login, my db.tables example works like a charm.

See ​here for a discussion of the issue. Would using strtok be an alternative, here ? Or better yet: G_tokenize() ?

[mlennert]

very crude patch to show path to solution

[mlennert]

Replying to mlennert:

The issue arises when some of the fields are empty, as for usr and pwd in my example. Anything from that point on is ignored. When I set a username and password with db.login, my db.tables example works like a charm.

See ​here for a discussion of the issue. Would using strtok be an alternative, here ? Or better yet: G_tokenize() ?

I've attached a proof-of-concept patch that solves the problem for me, but it hardcodes the number of tokens. I don't know what forms login strings can potentially take, so this definitely needs to be revised to take into account different scenarios.

[mlennert]

Another issue linked to the wxGUI (reported by Stefan Lüdtke - I can confirm):

"If everything is in db.login, I can connect to the db (as reported earlier) and list tables. However I can neither link nor import them because they are not listed in the gui box."

I can see the database in the list, but once I select it, no tables are listed.

Using

v.in.ogr input="PG:dbname=testgrass host=myRemoteServer" layer=boundary_county out=bc

I can import a layer, but not using

v.in.ogr input="PG:dbname=testgrass" layer=boundary_county out=bc

I imagine v.in.ogr does not take into account the .grass7/dblogin file.

[mlennert]

One question about the new db.connect + db.login combination: if host information is stored in .grass7/dblogin file indexed by database, how does the system deal with two databases of the same name on two different hosts ?

[martinl]

Replying to mlennert:

I've attached a proof-of-concept patch that solves the problem for me, but it hardcodes the number of tokens. I don't know what forms login strings can potentially take, so this definitely needs to be revised to take into account different scenarios.

Please review attachment:dbmi_base_login2.diff​​

[mlennert]

Replying to martinl:

Replying to mlennert:

I've attached a proof-of-concept patch that solves the problem for me, but it hardcodes the number of tokens. I don't know what forms login strings can potentially take, so this definitely needs to be revised to take into account different scenarios.

Please review attachment:dbmi_base_login2.diff​​

Seems to work nicely, and much more elegant than my hack, thanks !

[martinl]

Replying to mlennert:

One question about the new db.connect + db.login combination: if host information is stored in .grass7/dblogin file indexed by database, how does the system deal with two databases of the same name on two different hosts ?

Currently it's not possible, items in the file are indexed by driver/database. Probably we could disable indexing at all. Please fill new ticket for that.

[mlennert]

Replying to mlennert:

Replying to martinl:

Replying to mlennert:

I've attached a proof-of-concept patch that solves the problem for me, but it hardcodes the number of tokens. I don't know what forms login strings can potentially take, so this definitely needs to be revised to take into account different scenarios.

Please review attachment:dbmi_base_login2.diff​​

Seems to work nicely, and much more elegant than my hack, thanks !

P.S. Someone should probably check for mysql or other db backends, or ?

[martinl]

In 69597:

db.connect: connection to remote database does not work (see #3167)

[martinl]

In 69598:

db.connect: connection to remote database does not work (see #3167)

(relbr72: merge r69597 from trunk)

[mlennert]

Replying to martinl:

In 69597:

db.connect: connection to remote database does not work (see #3167)

FYI: I applied the patch to grass70_release and it seems to work perfectly there as well.

[mlennert]

Replying to mlennert:

Another issue linked to the wxGUI (reported by Stefan Lüdtke - I can confirm):

"If everything is in db.login, I can connect to the db (as reported earlier) and list tables. However I can neither link nor import them because they are not listed in the gui box."

I can see the database in the list, but once I select it, no tables are listed.

Using

v.in.ogr input="PG:dbname=testgrass host=myRemoteServer" layer=boundary_county out=bc

I can import a layer, but not using

v.in.ogr input="PG:dbname=testgrass" layer=boundary_county out=bc

I imagine v.in.ogr does not take into account the .grass7/dblogin file.

In grass70_release/vector/v.in.ogr/main.c, I see (line 359-60):

    if (driver_name && strcmp(driver_name, "pg") == 0 &&
        G_strcasecmp(param.dsn->answer, "PG:") == 0) {

Two questions:

• Why does the default driver have to be "pg", here ? We should import from postgres, whatever the default driver.
• Unless I misunderstand how G_strcasecmp works, G_strcasecmp(param.dsn->answer, "PG:") will never be 0 (it would be only in the case that param.dsn->answer = 'PG:', but that should never be the case). So, IIUC, the condition is never met and whatever we would like to add to the dsn in terms of username, passwd, host, etc will never be added.

If I skip the condition check (but obviously the 'PG:' check is needed), and I add something like this to main.c:

===================================================================
--- main.c	(révision 69596)
+++ main.c	(copie de travail)
@@ -382,6 +382,10 @@
                 strcat(dsn, " passwd=");
                 strcat(dsn, conn.password);
             }
+            if (conn.hostName) {
+                strcat(dsn, " host=");
+                strcat(dsn, conn.hostName);
+            }
             /* TODO: host/port... */
         }
         else {

v.in.ogr imports a layer from the database on a remote server if the login info is correct.

[martinl]

In 69599:

db.connect: connection to remote database does not work (see #3167)

(relbr72: merge r69597 from trunk)

[martinl]

Replying to mlennert:

FYI: I applied the patch to grass70_release and it seems to work perfectly there as well.

Thanks for testing, patch applied in all active branches (r69597-r69599)

[martinl]

Replying to mlennert:

If I skip the condition check (but obviously the 'PG:' check is needed), and I add something like this to main.c:

Make sense to me to skip default driver check.

===================================================================
--- main.c	(révision 69596)
+++ main.c	(copie de travail)
@@ -382,6 +382,10 @@
                 strcat(dsn, " passwd=");
                 strcat(dsn, conn.password);
             }
+            if (conn.hostName) {
+                strcat(dsn, " host=");
+                strcat(dsn, conn.hostName);
+            }
             /* TODO: host/port... */
         }
         else {

Then I would add also a port item.

[martinl]

Replying to martinl:

Thanks for testing, patch applied in all active branches (r69597-r69599)

What is missing to downgrade priority at least and don't block 7.0.5 release?

[mlennert]

Replying to martinl:

Replying to martinl:

Thanks for testing, patch applied in all active branches (r69597-r69599)

What is missing to downgrade priority at least and don't block 7.0.5 release?

There is a fundamental issue about importing data from remote server using v.in.ogr. See #comment:21 on part of the details. But looking into the code, I see even more weirdness: for some reason, v.in.ogr only looks at the default database of a pg db.connect-defined connection. I have no idea, why. v.in.ogr should import whatever data is available, even from other databases.

Example (after disabling the condition check mentioned above):

createdb -h myRemoteServer testgrass2
db.connect driver=pg database=testgrass2
db.login host=myRemoteHost
db.tables -p

Everything works, no tables shown. But then:

v.in.ogr input="PG:dbname=testgrass" layer=boundary_county out=bc

with testgrass defined in .grass7/dblogin as

pg|testgrass|||myRemoteServer|

I get (with DEBUG=1)

D1/1: G_set_program_name(): v.in.ogr
D1/1: Using dsn=PG:dbname=testgrass2 host=myRemoteServer
ERREUR :Layer <boundary_county> not available
D1/1: G_set_program_name(): g.gisenv

i.e. it automatically tries to get the table from testgrass2, not testgrass.

This comes from the fact that the name of the database is read as such:

dbname = db_get_default_database_name();

Instead of trying to read the database from the dsn string...

In other words, it seems that importing data from a remote database in v.in.ogr needs to be done using a complete dsn string, even if db.login was called. And that this will only work on the command line, not in the import wizard. Do you confirm ?

I guess we could call this a "known limitation" and just release as is, as it would need a substantial rewrite which I would like to avoid just before a release, but it is confusing to users. So maybe some hint about this in the import wizard ?

[martinl]

Replying to mlennert:

Example (after disabling the condition check mentioned above):

createdb -h myRemoteServer testgrass2
db.connect driver=pg database=testgrass2
db.login host=myRemoteHost
db.tables -p

Everything works, no tables shown. But then:

OK, so it means that DB is empty, right?

D1/1: G_set_program_name(): v.in.ogr
D1/1: Using dsn=PG:dbname=testgrass2 host=myRemoteServer
ERREUR :Layer <boundary_county> not available
D1/1: G_set_program_name(): g.gisenv

I do not understand - DB is empty, so why do you expect that v.in.ogr will not fail?

[martinl]

I tested settings bellow:

db.connect -p 
driver: sqlite
database: /opt/grassdata/pgtest/pg/sqlite/sqlite.db

db.login -p
pg|grassremote|||myserver|

The commands bellow works in GRASS 73/72/70:

db.tables dri=pg data=grassremote | grep staty
ruian.staty

v.in.ogr works in GRASS 73/72:

v.in.ogr in=PG:dbname=grassremote layer=ruian.staty out=staty --o

in GRASS 70 it fails with

ERROR 1: PQconnectdb failed.
FATAL:  database "grassremote" does not exist

ERROR: Unable to open data source <PG:dbname=grassremote>

See r67510, hard to backport. In GRASS70 you can still do

v.in.ogr in="PG:dbname=grassremote host=myserver" layer=ruian.staty out=staty --o

which works.

[mlennert]

Replying to martinl:

Replying to mlennert:

Example (after disabling the condition check mentioned above):

createdb -h myRemoteServer testgrass2
db.connect driver=pg database=testgrass2
db.login host=myRemoteHost
db.tables -p

Everything works, no tables shown. But then:

OK, so it means that DB is empty, right?

Yes, the database I want to use as attribute backend.

D1/1: G_set_program_name(): v.in.ogr
D1/1: Using dsn=PG:dbname=testgrass2 host=myRemoteServer
ERREUR :Layer <boundary_county> not available
D1/1: G_set_program_name(): g.gisenv

I do not understand - DB is empty, so why do you expect that v.in.ogr will not fail?

Because my call to v.in.ogr was

v.in.ogr input="PG:dbname=testgrass" layer=boundary_county out=bc

i.e. trying to get a layer in db 'testgrass' and import it into my GISDBASE, storing the attributes into my database testgrass2. I.e. (as you note yourself in your next post here), v.in.ogr without a complete dsn string (i.e. containing host, etc) will try to connect to the current default attribute backend database, not the one you actually provide on the command line.

[mlennert]

Replying to martinl:

v.in.ogr in=PG:dbname=grassremote layer=ruian.staty out=staty --o

in GRASS 70 it fails with

ERROR 1: PQconnectdb failed.
FATAL:  database "grassremote" does not exist

ERROR: Unable to open data source <PG:dbname=grassremote>

See r67510, hard to backport. In GRASS70 you can still do

v.in.ogr in="PG:dbname=grassremote host=myserver" layer=ruian.staty out=staty --o

which works.

Yes, and as I said before, I agree that trying to solve this issue is too heavy the day before a release. So, let's just call this a 'known issue' and tell people that they can use the complete dsn string in a v.in.ogr command line as a work-around.

This is especially annoying for those that depend on the GUI wizards for import, but let's hope that it will be one more motivating element to switch to 7.2 as soon as it comes out (although I haven't tested 7.2 with the GUI wizard, yet).

So, I'm closing this as fixed as the original issue is solved, and we'll just have to accept that 7.0 treatment of databases is imperfect.

Thanks for going through all this with me !