Thread: Re: Unknown connect option (Get), Postgres 7.4.2]

Re: Unknown connect option (Get), Postgres 7.4.2]

From
Albin Blaschka
Date:
Hello all,

Thank you for your replies!

but everything proposed, I have already tried and checked before I wrote
to the list...

To summarize:
When I want to connect from Computer A as user B to Database C via
PGAdminIII everything works smoothely -
When I want to connect from Computer A as user B to Database C via
Access or PGAdminII it fails... :-(

So I came to the conclusion that ODBC is the first to suspect...
and pg_hba.conf (which I edited by hand for the new setup) can't
be the problem...

But here (a little boiled down) is my pg_hba.conf:

# TYPE  DATABASE       USER            IP-ADDRESS   IP-MASK     METHOD
  local all            postgres                                 trust
  local all            +dba                                     md5
  local zuecht         zuecht_odbc                              md5
  local gruenland      gruen_odbc                               md5
  local basisdaten     versuch                                  md5

  host  all            postgres        127.0.0.1    255.255.0.0 md5
  host  all            postgres        192.168.0.0  255.255.0.0 md5

  host  all            albin           192.168.0.0  255.255.0.0 md5

  host  all            +dba            192.168.0.0  255.255.0.0 md5


Remark:
user "albin" (It's me;-) is a member of the group dba, as you see below, I
tried to connect in this way ...


PostgreSQL loggs the following during I try to connect via Access
(via Syslog):

May 25 10:44:58 postgres[4528]: [2-1] LOG:  00000: Verbindung empfangen:
                                            Host=192.168.0.110 Port=1086
May 25 10:44:58 postgres[4528]: [2-2] ORT:  BackendFork, postmaster.c:2395
May 25 10:44:58 postgres[4528]: [3-1] LOG:  00000: Verbindung authorisiert:
                                            Benutzer=albin Datenbank=zuecht
May 25 10:44:58 postgres[4528]: [3-2] ORT:  BackendFork, postmaster.c:2457
May 25 10:44:58 postgres[4528]: [4-1] LOG:  00000: Anweisung:
May 25 10:44:58 postgres[4528]: [4-2] ORT:  pg_parse_query, postgres.c:464
May 25 10:44:58 postgres[4528]: [5-1] LOG:  00000: Dauer: 0.297 ms
May 25 10:44:58 postgres[4528]: [5-2] ORT:  exec_simple_query,
postgres.c:960

I am working in Austria, so the output is partly in German, a short
(rough) translation:

Verbindung empfangen = connection received
Verbindung authorisiert = connection authorized
Benutzer = user
Datenbank = database

Thanks for all!

Albin

Database: PostgreSQL 7.4.2, self-compiled
Clients:  W2K, Access from Office XP
ODBC:     7.03.02.00

Here is the output of the psqlodbc-log:
CONN ERROR: func=PGAPI_GetConnectOption, desc='fOption=30002', errnum=205,
errmsg='Unknown connect option (Get)'
            ------------------------------------------------------------
            henv=136060816, conn=136066856, status=0, num_stmts=16
            sock=136060872, stmts=136060944, lobj_type=-999
            ---------------- Socket Info -------------------------------
            socket=-1, reverse=0, errornumber=0, errormsg='(NULL)'
            buffer_in=136077856, buffer_out=136081960
            buffer_filled_in=0, buffer_filled_out=0, buffer_read_in=0
conn=136066856, PGAPI_DriverConnect( in)='DSN=testing7.4.2;',
fDriverCompletion=1
DSN info:
DSN='testing7.4.2',server='balappserver',port='5432',dbase='zuecht',user='al
bin',passwd='xxxxx'

onlyread='0',protocol='6.4',showoid='0',fakeoidindex='0',showsystable='0'
          conn_settings='',conn_encoding='OTHER'
          translation_dll='',translation_option=''
Global Options: Version='07.03.0200', fetch=100, socket=4096,
unknown_sizes=0,
                max_varchar_size=254, max_longvarchar_size=8190
                disable_optimizer=1, ksqo=1, unique_index=1,
use_declarefetch=0
                text_as_longvarchar=1, unknowns_as_longvarchar=0,
bools_as_char=1 NAMEDATALEN=64
                extra_systable_prefixes='pg_;', conn_settings=''
conn_encoding='OTHER'
                conn=136066856, query=' '
NOTICE from backend during send_query: 'LOG:  Anweisung:
'
NOTICE from backend during send_query: 'LOG:  Dauer: 0.246 ms
'
CONN ERROR: func=PGAPI_DriverConnect, desc='Error from CC_Connect',
errnum=105, errmsg='The database does not exist on the server
or user authentication failed.'
            ------------------------------------------------------------
            henv=136060816, conn=136066856, status=0, num_stmts=16
            sock=136060872, stmts=136060944, lobj_type=-999
            ---------------- Socket Info -------------------------------
            socket=964, reverse=0, errornumber=0, errormsg='(NULL)'
            buffer_in=136077856, buffer_out=136081960
            buffer_filled_in=48, buffer_filled_out=0, buffer_read_in=48

And here from the mylog-file:

[1244]globals.extra_systable_prefixes = 'dd_;'
[1244][[SQLAllocHandle]][1244]**** in PGAPI_AllocEnv **
[1244]** exit PGAPI_AllocEnv: phenv = 136060816 **
[1244][[SQLSetEnvAttr]] att=200,2
[1244][[SQLAllocHandle]][1244]PGAPI_AllocConnect: entering...
[1244]**** PGAPI_AllocConnect: henv = 136060816, conn = 136066856
[1244]EN_add_connection: self = 136060816, conn = 136066856
[1244]       added at i =0, conn->henv = 136060816, conns[i]->henv =
136060816
[1244][SQLGetInfo(30)][1244]PGAPI_GetInfo: entering...fInfoType=77
[1244]PGAPI_GetInfo: p='03.00', len=0, value=0, cbMax=12
[1244][[SQLSetConnectAttr]] 103
[1244]PGAPI_SetConnectAttr 103
[1244]PGAPI_SetConnectOption: entering fOption = 103 vParam = 20
[1244][[SQLGetConnectAttr]] 30002
[1244]PGAPI_GetConnectAttr 30002
[1244]PGAPI_GetConnectOption: entering...
[1244]CONN ERROR: func=PGAPI_GetConnectOption, desc='fOption=30002',
errnum=205, errmsg='Unknown connect option (Get)'
[1244][[SQLSetConnectAttr]] 30002
[1244]PGAPI_SetConnectAttr 30002
[1244]PGAPI_SetConnectOption: entering fOption = 30002 vParam = 122428760
[1244]Microsoft Jet !!!!
[1244][SQLDriverConnect][1244]PGAPI_DriverConnect: entering...
[1244]**** PGAPI_DriverConnect: fDriverCompletion=1,
connStrIn='DSN=testing7.4.2;'
[1244]our_connect_string = 'DSN=testing7.4.2;'
[1244]attribute = 'DSN', value = 'testing7.4.2'
[1244]copyAttributes:
DSN='testing7.4.2',server='',dbase='',user='',passwd='xxxxx',port='',onlyrea
d='',protocol='',conn_settings='',disallow_premature=-1)
[1244]globals.extra_systable_prefixes = 'pg_;'
[1244]our_connect_string = 'DSN=testing7.4.2;'
[1244]attribute = 'DSN', value = 'testing7.4.2'
[1244]CopyCommonAttributes:
A7=100;A8=4096;A9=0;B0=254;B1=8190;B2=1;B3=1;B4=1;B5=1;B6=0;B7=1;B8=0;B9=1;C
0=1;C1=0;C2=pg_;[1244]CC_connect: entering...
[1244]CC_connect(): DSN = 'testing7.4.2', server = 'balappserver', port =
'5432', database = 'zuecht', username = 'albin', password='xxxxx'
[1244]connecting to the server socket...
[1244]connection to the server socket succeeded.
[1244]sizeof startup packet = 292
[1244]sent the authentication block.
[1244]sent the authentication block successfully.
[1244]gonna do authentication
[1244]read 9, global_socket_buffersize=4096
[1244]auth got 'R'
[1244]areq = 5
[1244]in AUTH_REQ_MD5
[1244]read 15, global_socket_buffersize=4096
[1244]auth got 'R'
[1244]areq = 0
[1244]auth got 'K'
[1244]auth got 'Z'
[1244]sending an empty query...
[1244]send_query(): conn=136066856, query=' '
[1244]send_query: done sending query
[1244]in QR_Constructor
[1244]exit QR_Constructor
[1244]read 48, global_socket_buffersize=4096
[1244]send_query: got id = 'N'
[1244]~~~ NOTICE: 'LOG:  Anweisung:
'
[1244]send_query: got id = 'I'
[1244]send_query: got id = 'N'
[1244]~~~ NOTICE: 'LOG:  Dauer: 0.246 ms
'
[1244]send_query: got id = 'Z'
[1244]got no result from the empty query.  (probably database does not
exist)
[1244]QResult: in DESTRUCTOR
[1244]QResult: free memory in, fcount=0
[1244]QResult: free memory out
[1244]QResult: exit DESTRUCTOR
[1244]CONN ERROR: func=PGAPI_DriverConnect, desc='Error from CC_Connect',
errnum=105, errmsg='The database does not exist on the server
or user authentication failed.'
[1244][[SQLGetDiagRec]]
[1244]PGAPI_GetDiagRec entering rec=1[1244]**** PGAPI_ConnectError:
hdbc=136066856 <0>
[1244]enter CC_get_error
[1244]enter CC_create_errormsg
[1244]msg = 'The database does not exist on the server
or user authentication failed.'
[1244]exit CC_create_errormsg
[1244]exit CC_get_error
[1244]CC_get_error: status = 105, msg = #The database does not exist on the
server
or user authentication failed.#
[1244]         szSqlState = 'S1000',len=72, szError='(null)'
[1244]PGAPI_GetDiagRec exiting 1
[1244][[SQLGetDiagRec]]
[1244]PGAPI_GetDiagRec entering rec=1[1244]**** PGAPI_ConnectError:
hdbc=136066856 <74>
[1244]enter CC_get_error
[1244]exit CC_get_error
[1244]CC_get_error: status = 105, msg = #The database does not exist on the
server
or user authentication failed.#
[1244]         szSqlState = 'S1000',len=72, szError='The database does not
exist on the server
or user authentication failed.'
[1244]PGAPI_GetDiagRec exiting 0
[1244][[SQLGetDiagField]] Handle=(2,81c3728) Rec=1 Id=8
[1244]PGAPI_GetDiagField entering rec=1[1244]PGAPI_GetDiagField exiting 0
[1244][[SQLGetDiagField]] Handle=(2,81c3728) Rec=1 Id=9
[1244]PGAPI_GetDiagField entering rec=1[1244]PGAPI_GetDiagField exiting 0
[1244][[SQLGetDiagField]] Handle=(2,81c3728) Rec=1 Id=10
[1244]PGAPI_GetDiagField entering rec=1[1244]PGAPI_GetDiagField exiting 0
[1244][[SQLGetDiagField]] Handle=(2,81c3728) Rec=1 Id=11
[1244]PGAPI_GetDiagField entering rec=1[1244]PGAPI_GetDiagField exiting 0
[1244][[SQLGetDiagRec]]
[1244]PGAPI_GetDiagRec entering rec=2[1244]**** PGAPI_ConnectError:
hdbc=136066856 <0>
[1244]PGAPI_GetDiagRec exiting 100
[1244][[SQLFreeHandle]][1244]PGAPI_FreeConnect: entering...
[1244]**** in PGAPI_FreeConnect: hdbc=136066856
[1244]enter CC_Destructor, self=136066856
[1244]in CC_Cleanup, self=136066856
[1244]after CC_abort
[1244]SOCK_Destructor
[1244]after SOCK destructor
[1244]exit CC_Cleanup
[1244]after CC_Cleanup
[1244]after free statement holders
[1244]exit CC_Destructor
[1244]PGAPI_FreeConnect: returning...
[1244][[SQLFreeHandle]][1244]**** in PGAPI_FreeEnv: env = 136060816 **
[1244]in EN_Destructor, self=136060816
[1244]exit EN_Destructor: rv = 1
[1244]   ok

Re: Unknown connect option (Get), Postgres 7.4.2]

From
Jeff Eckermann
Date:
--- Albin Blaschka <Albin.Blaschka@bal.bmlfuw.gv.at>
wrote:
>
> Hello all,
>
> Thank you for your replies!
>
> but everything proposed, I have already tried and
> checked before I wrote
> to the list...

I'm not going to say that the ODBC driver could not be
the problem.  All I will say is, every case similar to
yours that I have seen, turned out to be a problem
with connecting or authenticating.

You can easily test the authentication question, just
by temporarily setting up user "albin" to use "trust"
authentication.

I don't want to assume anything based on the fact that
PgAdminIII can connect, because I don't know how it
handles that.

If all else fails, try setting up psql on your
workstation (there are instructions in the docs on how
to do that), and try connecting manually.  If you get
a failure then, you may at least get a more helpful
error message.

>
> To summarize:
> When I want to connect from Computer A as user B to
> Database C via
> PGAdminIII everything works smoothely -
> When I want to connect from Computer A as user B to
> Database C via
> Access or PGAdminII it fails... :-(
>
> So I came to the conclusion that ODBC is the first
> to suspect...
> and pg_hba.conf (which I edited by hand for the new
> setup) can't
> be the problem...
>
> But here (a little boiled down) is my pg_hba.conf:
>
> # TYPE  DATABASE       USER            IP-ADDRESS
> IP-MASK     METHOD
>   local all            postgres
>            trust
>   local all            +dba
>            md5
>   local zuecht         zuecht_odbc
>            md5
>   local gruenland      gruen_odbc
>            md5
>   local basisdaten     versuch
>            md5
>
>   host  all            postgres        127.0.0.1
> 255.255.0.0 md5
>   host  all            postgres        192.168.0.0
> 255.255.0.0 md5
>
>   host  all            albin           192.168.0.0
> 255.255.0.0 md5
>
>   host  all            +dba            192.168.0.0
> 255.255.0.0 md5
>
>
> Remark:
> user "albin" (It's me;-) is a member of the group
> dba, as you see below, I
> tried to connect in this way ...
>
>
> PostgreSQL loggs the following during I try to
> connect via Access
> (via Syslog):
>
> May 25 10:44:58 postgres[4528]: [2-1] LOG:  00000:
> Verbindung empfangen:
>
> Host=192.168.0.110 Port=1086
> May 25 10:44:58 postgres[4528]: [2-2] ORT:
> BackendFork, postmaster.c:2395
> May 25 10:44:58 postgres[4528]: [3-1] LOG:  00000:
> Verbindung authorisiert:
>
> Benutzer=albin Datenbank=zuecht
> May 25 10:44:58 postgres[4528]: [3-2] ORT:
> BackendFork, postmaster.c:2457
> May 25 10:44:58 postgres[4528]: [4-1] LOG:  00000:
> Anweisung:
> May 25 10:44:58 postgres[4528]: [4-2] ORT:
> pg_parse_query, postgres.c:464
> May 25 10:44:58 postgres[4528]: [5-1] LOG:  00000:
> Dauer: 0.297 ms
> May 25 10:44:58 postgres[4528]: [5-2] ORT:
> exec_simple_query,
> postgres.c:960
>
> I am working in Austria, so the output is partly in
> German, a short
> (rough) translation:
>
> Verbindung empfangen = connection received
> Verbindung authorisiert = connection authorized
> Benutzer = user
> Datenbank = database
>
> Thanks for all!
>
> Albin
>
> Database: PostgreSQL 7.4.2, self-compiled
> Clients:  W2K, Access from Office XP
> ODBC:     7.03.02.00
>
> Here is the output of the psqlodbc-log:
> CONN ERROR: func=PGAPI_GetConnectOption,
> desc='fOption=30002', errnum=205,
> errmsg='Unknown connect option (Get)'
>
>
------------------------------------------------------------
>             henv=136060816, conn=136066856,
> status=0, num_stmts=16
>             sock=136060872, stmts=136060944,
> lobj_type=-999
>             ---------------- Socket Info
> -------------------------------
>             socket=-1, reverse=0, errornumber=0,
> errormsg='(NULL)'
>             buffer_in=136077856,
> buffer_out=136081960
>             buffer_filled_in=0, buffer_filled_out=0,
> buffer_read_in=0
> conn=136066856, PGAPI_DriverConnect(
> in)='DSN=testing7.4.2;',
> fDriverCompletion=1
> DSN info:
>
DSN='testing7.4.2',server='balappserver',port='5432',dbase='zuecht',user='al
> bin',passwd='xxxxx'
>
>
onlyread='0',protocol='6.4',showoid='0',fakeoidindex='0',showsystable='0'
>           conn_settings='',conn_encoding='OTHER'
>           translation_dll='',translation_option=''
> Global Options: Version='07.03.0200', fetch=100,
> socket=4096,
> unknown_sizes=0,
>                 max_varchar_size=254,
> max_longvarchar_size=8190
>                 disable_optimizer=1, ksqo=1,
> unique_index=1,
> use_declarefetch=0
>                 text_as_longvarchar=1,
> unknowns_as_longvarchar=0,
> bools_as_char=1 NAMEDATALEN=64
>                 extra_systable_prefixes='pg_;',
> conn_settings=''
> conn_encoding='OTHER'
>                 conn=136066856, query=' '
> NOTICE from backend during send_query: 'LOG:
> Anweisung:
> '
> NOTICE from backend during send_query: 'LOG:  Dauer:
> 0.246 ms
> '
> CONN ERROR: func=PGAPI_DriverConnect, desc='Error
> from CC_Connect',
> errnum=105, errmsg='The database does not exist on
> the server
> or user authentication failed.'
>
>
------------------------------------------------------------
>             henv=136060816, conn=136066856,
> status=0, num_stmts=16
>             sock=136060872, stmts=136060944,
> lobj_type=-999
>             ---------------- Socket Info
> -------------------------------
>             socket=964, reverse=0, errornumber=0,
> errormsg='(NULL)'
>             buffer_in=136077856,
> buffer_out=136081960
>             buffer_filled_in=48,
> buffer_filled_out=0, buffer_read_in=48
>
> And here from the mylog-file:
>
> [1244]globals.extra_systable_prefixes = 'dd_;'
> [1244][[SQLAllocHandle]][1244]**** in PGAPI_AllocEnv
> **
> [1244]** exit PGAPI_AllocEnv: phenv = 136060816 **
> [1244][[SQLSetEnvAttr]] att=200,2
> [1244][[SQLAllocHandle]][1244]PGAPI_AllocConnect:
> entering...
> [1244]**** PGAPI_AllocConnect: henv = 136060816,
> conn = 136066856
> [1244]EN_add_connection: self = 136060816, conn =
> 136066856
> [1244]       added at i =0, conn->henv = 136060816,
> conns[i]->henv =
> 136060816
> [1244][SQLGetInfo(30)][1244]PGAPI_GetInfo:
> entering...fInfoType=77
> [1244]PGAPI_GetInfo: p='03.00', len=0, value=0,
> cbMax=12
> [1244][[SQLSetConnectAttr]] 103
> [1244]PGAPI_SetConnectAttr 103
> [1244]PGAPI_SetConnectOption: entering fOption = 103
> vParam = 20
> [1244][[SQLGetConnectAttr]] 30002
> [1244]PGAPI_GetConnectAttr 30002
> [1244]PGAPI_GetConnectOption: entering...
> [1244]CONN ERROR: func=PGAPI_GetConnectOption,
> desc='fOption=30002',
> errnum=205, errmsg='Unknown connect option (Get)'
> [1244][[SQLSetConnectAttr]] 30002
> [1244]PGAPI_SetConnectAttr 30002
> [1244]PGAPI_SetConnectOption: entering fOption =
> 30002
=== message truncated ===





__________________________________
Do you Yahoo!?
Friends.  Fun.  Try the all-new Yahoo! Messenger.
http://messenger.yahoo.com/