Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client

Hi Tom

Thanks for replying.

>Hm, I just noticed this bit:

> Protocol=9.4

>Others with more ODBC experience can correct me, but I suspect that the
ODBC driver has no idea what to do with that value. The PG wire protocol
hasn't changed since 7.4, and I thought that "7.4" was generally the right
thing to use there.

Ok, I replaced the

Protocol=9.4

line with

Protocol=7.4

and then tried isql again, still receiving the same error.

---
[S1000][unixODBC]The database does not exist on the server
or user authentication failed.
---

>Also, have you tried looking into the resulting TraceFile and/or DebugFile?
You'd probably have to modify

>DebugFile=/var/log/postgresql_debug.log

>as I sure hope /var/log is not writable to you.

I changed these paths to point to

TraceFile=/tmp/odbc_trace.log

DebugFile=/tmp/odbc_debug.log

and then tried to run isql again, error remains the same, AND neither of
these log files are created.

I did notice though that there is a file /tmp/mylog_postgres23427.log that
was last written on the 17th (that's when I started to try to get Postgres
9.4 to communicate over ODBC) but hasn't been updated since.

That file contains:

---
[140298388883200]calling getDSNdefaults
[140298388883200]CC_connect: entering...
[140298388883200]sslmode=disable
[140298388883200]original_CC_connect: entering...
[140298388883200]Driver Version='09.03.0400,Apr 5 2015'
[140298388883200]original_CC_connect: DSN = 'pgdb-cdr', server =
'localhost', port = '5432', database = 'asteriskcdrdb', username =
'asteriskcdruser', password='xxxxx'
[140298388883200]connecting to the server socket...
[140298388883200](0)(null) ERRNO=115
[140298388883200]connection to the server socket succeeded.
[140298388883200]sizeof startup packet = 292
[140298388883200]sent the authentication block successfully.
[140298388883200]gonna do authentication
[140298388883200]read -1, global_socket_buffersize=4096
[140298388883200]Lasterror=11
[140298388883200]!!! poll ret=1 revents=1
[140298388883200]read 9, global_socket_buffersize=4096
[140298388883200]auth got 'R'
[140298388883200]areq = 5 salt=fcc6a94100
[140298388883200]in AUTH_REQ_MD5
[140298388883200]read -1, global_socket_buffersize=4096
[140298388883200]Lasterror=11
[140298388883200]!!! poll ret=1 revents=1
[140298388883200]read 15, global_socket_buffersize=4096
[140298388883200]auth got 'R'
[140298388883200]areq = 0 salt=0000000000
[140298388883200]sending an empty query...
[140298388883200]CC_send_query: conn=0x18aaf20, query=' '
[140298388883200]send_query: done sending query 3bytes flushed
[140298388883200]in QR_Constructor
[140298388883200]exit QR_Constructor
[140298388883200]send_query: got id = 'K'
[140298388883200]CC_error_statements: self=0x18aaf20
[140298388883200]CONN ERROR: func=CC_send_query, desc='', errnum=106,
errmsg='Unexpected protocol character from backend (send_query)'
[140298388883200]CC_on_abort in
[140298388883200]SOCK_Destructor
[140298388883200]send_query: error - Unexpected protocol character from
backend (send_query)
[140298388883200]CC_on_abort in
[140298388883200]QResult: enter DESTRUCTOR
[140298388883200]QResult: in QR_close_result
[140298388883200]QResult: free memory in, fcount=0
[140298388883200]QResult: free memory out
[140298388883200]QResult: exit close_result
[140298388883200]QResult: exit DESTRUCTOR
[140298388883200]CC_error_statements: self=0x18aaf20
[140298388883200]CONN ERROR: func=original_CC_connect, desc='', errnum=105,
errmsg='The database does not exist on the server
or user authentication failed.'
[140298388883200]QResult: enter DESTRUCTOR
[140298388883200]CONN ERROR: func=PGAPI_Connect, desc='Error on CC_connect',
errnum=105, errmsg='The database does not exist on the server
or user authentication failed.'
[140298388883200]PGAPI_Connect: returning..-1.
[140298388883200][[SQLGetDiagRec]]
[140298388883200]PGAPI_GetDiagRec entering type=2 rec=1
[140298388883200]**** PGAPI_ConnectError: hdbc=0x18aaf20 <513>
[140298388883200]enter CC_get_error
[140298388883200]enter CC_create_errormsg
[140298388883200]msg = 'The database does not exist on the server
or user authentication failed.'
[140298388883200]exit CC_create_errormsg
[140298388883200]exit CC_get_error
[140298388883200]CC_get_error: status = 105, msg = #The database does not
exist on the server
or user authentication failed.#
[140298388883200] szSqlState = 'S1000',len=72, szError='The
database does not exist on the server
or user authentication failed.'
[140298388883200]PGAPI_GetDiagRec exiting 0
[140298388883200][[SQLGetDiagRec]]
[140298388883200]PGAPI_GetDiagRec entering type=2 rec=2
[140298388883200]**** PGAPI_ConnectError: hdbc=0x18aaf20 <513>
[140298388883200]PGAPI_GetDiagRec exiting 100
[140298388883200][[SQLFreeHandle]][140298388883200]PGAPI_FreeConnect:
entering...
[140298388883200]**** in PGAPI_FreeConnect: hdbc=0x18aaf20
[140298388883200]enter CC_Destructor, self=0x18aaf20
[140298388883200]in CC_Cleanup, self=0x18aaf20
[140298388883200]after SOCK destructor
[140298388883200]CC_conninfo_init opt=1
[140298388883200]exit CC_Cleanup
[140298388883200]after CC_Cleanup
[140298388883200]after free statement holders
[140298388883200]exit CC_Destructor
[140298388883200]PGAPI_FreeConnect: returning...
[140298388883200][[SQLFreeHandle]][140298388883200]**** in PGAPI_FreeEnv:
env = 0x18a9a40 **
[140298388883200]in EN_Destructor, self=0x18a9a40
[140298388883200]clearing conns count=128
[140298388883200]exit EN_Destructor: rv = 1
[140298388883200] ok
---

This looks like pretty relevant (don't know why it does not have amore
recent date though?) but relevant errors appear to be:

---
[140298388883200]send_query: got id = 'K'
[140298388883200]CC_error_statements: self=0x18aaf20
[140298388883200]CONN ERROR: func=CC_send_query, desc='', errnum=106,
errmsg='Unexpected protocol character from backend (send_query)'
[140298388883200]CC_on_abort in
[140298388883200]SOCK_Destructor
[140298388883200]send_query: error - Unexpected protocol character from
backend (send_query)
[140298388883200]CC_on_abort in
---

and this clearly is happening on my connection attempts?

This line

[140298388883200]send_query: error - Unexpected protocol character from
backend (send_query)

does eem to indicated that somehow the ODBC driver Postgres provides is
incompatible on protocol level with unixODBC's isql application, at least in
my instance here...?

Thanks

Regards

Stefan