Thread: DIAG [HY000] server closed the connection unexpectedly

Hi, Inoue-san
cc: Jon

This is rather a bug of psqlODBC.  According to the SQLExecDirect reference page below, the SQLSTATE should be 08S01.

https://msdn.microsoft.com/en-us/library/ms713611(v=vs.85).aspx

08S01
Communication link failure
The communication link between the driver and the data source to which the driver was connected failed before the
functioncompleted processing. 



The attached patch will fix this.  Could you review and commit it?


Regards
Takayuki Tsunakawa

> -----Original Message-----
> From: pgsql-odbc-owner@postgresql.org
> [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Jon Raiford
> Sent: Wednesday, October 19, 2016 5:00 AM
> To: pgsql-odbc@postgresql.org
> Subject: [ODBC] DIAG [HY000] server closed the connection unexpectedly
>
> I am running the 32-bit v9.05.02.10 Unicode ODBC driver in one environment
> (dated 15-Apr-2016).  I know it may not be the latest version, but can anyone
> tell me if there has been a bug recently with the ODBC driver answering
> the wrong SQL_STATE during a database disconnect?
>
> Here is a small excerpt from my ODBC trace log.  This is the first error
> encountered during the connection.  Notice that instead of answering with
> the SQL STATE [08006] or whatever it is answering a generic [HY000].  This
> has required quite a lot of effort to hunt this down into a simple scenario
> so I'm hopefully someone will tell me it has already been addressed :)
>
>
> v               4348-18a0        ENTER SQLAllocHandle
>                 SQLSMALLINT                  3 <SQL_HANDLE_STMT>
>                 SQLHANDLE           0x02298080
>                 SQLHANDLE *         0x0018F0EC
>
> v               4348-18a0        EXIT  SQLAllocHandle  with return code
> 0 (SQL_SUCCESS)
>                 SQLSMALLINT                  3 <SQL_HANDLE_STMT>
>                 SQLHANDLE           0x02298080
>                 SQLHANDLE *         0x0018F0EC ( 0x0229C4E8)
>
> v               4348-18a0        ENTER SQLExecDirectW
>                 HSTMT               0x0229C4E8
>                 WCHAR *             0x0018F10C [       4] "asdf"
>                 SDWORD                     4
>
> v               4348-18a0        EXIT  SQLExecDirectW  with return code
> -1 (SQL_ERROR)
>                 HSTMT               0x0229C4E8
>                 WCHAR *             0x0018F10C [       4] "asdf"
>                 SDWORD                     4
>
>                 DIAG [HY000] server closed the connection unexpectedly
>         This probably means the server terminated abnormally
>         before or while processing the request.
> ;
> server closed the connection unexpectedly
>         This probably means the server terminated abnormally
>         before or while processing the request.
>  (1)
>
>
>
>
> Best Regards,
>
> Jon Raiford
> raiford@labware.com
> Tel: +1 (302) 658-8444
> http://www.labware.com <http://www.labware.com/> LabWare LIMS Solutions
> - Results Count

Hi, Jon

From: pgsql-odbc-owner@postgresql.org
> [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Jon Raiford
> It would seem that I was mistaken.  My simple test case did produce the
> expected 08001 SQLSTATE with the updated driver, but when I scaled up to
> multiple connections it was not consistent.  In the end I am now simply
> check HY000 errors and change them to 08001 if the text matches "*server
> closed the connection*".  A brute force method, but it allows me to move
> forward.
>
> Of course it may be that I made a mistake in applying the patch or my build
> environment.  I will happily test an updated build when it becomes available
> and report back.

First, the SQLSTATE is 08S01, not 08001.

Yes, I'd appreciate it if you could report back the details.  I have no idea about any problem which can happen with
simultaneousmultiple connections. 

BTW, you seem to want a method to judge that the connection is lost.  Then, SQLGetConnectAttr(hdbc,
SQL_ATTR_CONNECTION_DEAD)should be the right answer.  See the following page, and please try it. 

https://msdn.microsoft.com/en-us/library/ms713605(v=vs.85).aspx

SQL_ATTR_CONNECTION_DEAD
A read-only SQLUINTEGER value that indicates the state of the connection. If SQL_CD_TRUE, the connection has been lost.
IfSQL_CD_FALSE, the connection is still active. 

> Assuming this is accepted and committed, is there any idea when an official
> updated installer may be posted?

Sorry, I'm not in the position to decide it.  Inoue-san and Saito-san will do.

Regards
Takayuki Tsunakawa

Hi, Jon, Inoue-san,

Inoue-san,
The attached patch is rebased on HEAD.


Jon,

From: Jon Raiford [mailto:Raiford@labware.com]
> Yes, I can see that the code does answer 08S01, however it is being reported
> as 08001 to the application.  This can be seen even in the ODBC trace.  I'm
> not sure why it is changed.  Maybe this is a symptom of a bigger problem?

Didn't the 08001 get returned after you failed to reconnect?  As follows, I got 08S01 when I killed backend while it
wasrunning an SQL statement (e.g. SELECT pg_sleep(1000)). 

[isql command of unixODBC]
$ isql -v postgres
...
SQL> select pg_sleep(1000);
[ISQL]ERROR: Could not SQLExecute
[08S01]server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
;
The connection has been lost
SQL>

[my test app using SQLExecDirect]
08S01: server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
;
The connection has been lost

Regards
Takayuki Tsunakawa

Hi, Jon, Inoue-san

From: pgsql-odbc-owner@postgresql.org
> [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Jon Raiford
> Here is an excerpt from my ODBC tracing on Windows.  You can see that the
> error is being reported as 08001 instead of 08S01:
> v               a14-135c        EXIT  SQLEndTran  with return code -1
> (SQL_ERROR)
>                 SQLSMALLINT                  2 <SQL_HANDLE_DBC>
>                 SQLHANDLE           0x0026CCC8
>                 SQLSMALLINT                  0
>
>                 DIAG [08001] server closed the connection unexpectedly
>         This probably means the server terminated abnormally
>         before or while processing the request.
>  (101)

Thanks, Jon.  08001 was set when the driver failed to send the query (COMMIT in this case.)

Inoue-san, the attached is a revised patch.  I changed the error code to CONNECTION_COMMUNICATION_ERROR when
PQsendQuery()fails. 

Regards
Takayuki Tsunakawa