Thread: DIAG [HY000] server closed the connection unexpectedly

DIAG [HY000] server closed the connection unexpectedly

From
"Jon Raiford"
Date:
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
LabWare LIMS Solutions - Results Count

Re: DIAG [HY000] server closed the connection unexpectedly

From
"Tsunakawa, Takayuki"
Date:
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

Attachment

Re: DIAG [HY000] server closed the connection unexpectedly

From
"Jon Raiford"
Date:
Hi Tsunakawa-san,

Thank you.  I can confirm that this does resolve my issue.  I am now seeing an 08001 SQLSTATE when the disconnect first occurs.  I have other issues with my reconnecting code, but at least now I will be able to make it work.

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

Thanks again,

Jon

pgsql-odbc-owner@postgresql.org wrote on 10/19/2016 03:44:09 AM:

> From: "Tsunakawa, Takayuki" <tsunakawa.takay@jp.fujitsu.com>

> To: "'Jon Raiford'" <Raiford@labware.com>, "pgsql-
> odbc@postgresql.org" <pgsql-odbc@postgresql.org>

> Date: 10/19/2016 03:44 AM
> Subject: Re: [ODBC] DIAG [HY000] server closed the connection unexpectedly
> Sent by: pgsql-odbc-owner@postgresql.org
>
> 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 function completed
> 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, butcan 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
> [attachment "sqlstate_on_disconnection.patch" deleted by Jon
> Raiford/Employee/LW-US]
> --
> Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
> To make changes to your subscription:
>
http://www.postgresql.org/mailpref/pgsql-odbc

Re: DIAG [HY000] server closed the connection unexpectedly

From
"Jon Raiford"
Date:
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.

Jon

pgsql-odbc-owner@postgresql.org wrote on 10/19/2016 11:45:57 AM:

> From: "Jon Raiford" <Raiford@labware.com>

> To: "Tsunakawa, Takayuki" <tsunakawa.takay@jp.fujitsu.com>
> Cc: "pgsql-odbc@postgresql.org" <pgsql-odbc@postgresql.org>
> Date: 10/19/2016 11:46 AM
> Subject: Re: [ODBC] DIAG [HY000] server closed the connection unexpectedly
> Sent by: pgsql-odbc-owner@postgresql.org
>
> Hi Tsunakawa-san,
>
> Thank you.  I can confirm that this does resolve my issue.  I am now
> seeing an 08001 SQLSTATE when the disconnect first occurs.  I have
> other issues with my reconnecting code, but at least now I will be
> able to make it work.
>
> Assuming this is accepted and committed, is there any idea when an
> official updated installer may be posted?
>
> Thanks again,
>
> Jon
>
> pgsql-odbc-owner@postgresql.org wrote on 10/19/2016 03:44:09 AM:
>
> > From: "Tsunakawa, Takayuki" <tsunakawa.takay@jp.fujitsu.com>
> > To: "'Jon Raiford'" <Raiford@labware.com>, "pgsql-
> > odbc@postgresql.org" <pgsql-odbc@postgresql.org>
> > Date: 10/19/2016 03:44 AM
> > Subject: Re: [ODBC] DIAG [HY000] server closed the connection unexpectedly
> > Sent by: pgsql-odbc-owner@postgresql.org
> >
> > 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 function completed
> > 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,
> butcan 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
> > [attachment "sqlstate_on_disconnection.patch" deleted by Jon
> > Raiford/Employee/LW-US]
> > --
> > Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
> > To make changes to your subscription:
> >
http://www.postgresql.org/mailpref/pgsql-odbc

Re: DIAG [HY000] server closed the connection unexpectedly

From
"Tsunakawa, Takayuki"
Date:
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




Re: DIAG [HY000] server closed the connection unexpectedly

From
"Jon Raiford"
Date:

> 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 becomesavailable
> > and report back.
>
> First, the SQLSTATE is 08S01, not 08001.


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?

> 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. If
> SQL_CD_FALSE, the connection is still active.


Thank you!  I did not know about this option.  I will certainly investigate this as an alternative going forward.

Jon

Re: DIAG [HY000] server closed the connection unexpectedly

From
"Tsunakawa, Takayuki"
Date:
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



Attachment

Re: DIAG [HY000] server closed the connection unexpectedly

From
"Jon Raiford"
Date:
Hi Tsunakawa-san,

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        ENTER SQLFetch
                HSTMT               0x0026DBC0

v               a14-135c        EXIT  SQLFetch  with return code 100 (SQL_NO_DATA_FOUND)
                HSTMT               0x0026DBC0

v               a14-135c        ENTER SQLFreeHandle
                SQLSMALLINT                  3 <SQL_HANDLE_STMT>
                SQLHANDLE           0x0026DBC0

v               a14-135c        EXIT  SQLFreeHandle  with return code 0 (SQL_SUCCESS)
                SQLSMALLINT                  3 <SQL_HANDLE_STMT>
                SQLHANDLE           0x0026DBC0

v               a14-135c        ENTER SQLEndTran
                SQLSMALLINT                  2 <SQL_HANDLE_DBC>
                SQLHANDLE           0x0026CCC8
                SQLSMALLINT                  0

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)

Re: DIAG [HY000] server closed the connection unexpectedly

From
"Tsunakawa, Takayuki"
Date:
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


Attachment