Обсуждение: [ODBC] ODBC connections go dead after a failed query?
Hi,
We have some legacy SQL queries that works on SQL Server but not on PostgreSQL due to SQL Server-specific syntax, which we are working to migrate. The problem is sometimes psqlodbc just cannot seem to reset itself after a bad query and simply go dead. All subsequent queries on the same connection, legitimate or not, would fail.
I've verified this can happen with the latest PostgreSQL 9.6.2 and psqlodbc 09_06_0100, on a Windows 7 x64 host.We have some legacy SQL queries that works on SQL Server but not on PostgreSQL due to SQL Server-specific syntax, which we are working to migrate. The problem is sometimes psqlodbc just cannot seem to reset itself after a bad query and simply go dead. All subsequent queries on the same connection, legitimate or not, would fail.
PsqlOdbcBug f68-e10 EXIT SQLPrepareW with return code 0 (SQL_SUCCESS)
HSTMT 0x00033EE8
WCHAR * 0x010F0F28 [ -3] "UPDATE site SET image=image_old;\ 0"
SDWORD -3
PsqlOdbcBug f68-e10 ENTER SQLExecute
HSTMT 0x00033EE8
PsqlOdbcBug f68-e10 EXIT SQLExecute with return code -1 (SQL_ERROR)
HSTMT 0x00033EE8
DIAG [42804] ERROR: column "image" is of type bytea but expression is of type text;
Error while preparing parameters (1)
PsqlOdbcBug f68-e10 ENTER SQLFreeHandle
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x00033EE8
PsqlOdbcBug f68-e10 EXIT SQLFreeHandle with return code 0 (SQL_SUCCESS)
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x00033EE8
PsqlOdbcBug f68-e10 ENTER SQLAllocHandle
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x000338D8
SQLHANDLE * 0x0035EB90
PsqlOdbcBug f68-e10 EXIT SQLAllocHandle with return code 0 (SQL_SUCCESS)
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x000338D8
SQLHANDLE * 0x0035EB90 ( 0x00D51C98)
PsqlOdbcBug f68-e10 ENTER SQLPrepareW
HSTMT 0x00D51C98
WCHAR * 0x010F0DE0 [ -3] "SELECT id FROM site;\ 0"
SDWORD -3
PsqlOdbcBug f68-e10 EXIT SQLPrepareW with return code 0 (SQL_SUCCESS)
HSTMT 0x00D51C98
WCHAR * 0x010F0DE0 [ -3] "SELECT id FROM site;\ 0"
SDWORD -3
PsqlOdbcBug f68-e10 ENTER SQLExecute
HSTMT 0x00D51C98
PsqlOdbcBug f68-e10 EXIT SQLExecute with return code -1 (SQL_ERROR)
HSTMT 0x00D51C98
DIAG [08S01] Could not send Query(connection dead);
Could not send Query(connection dead) (26)
Вложения
On 03/03/2017 12:14 PM, Jack C. Wei wrote: > Hi, > > We have some legacy SQL queries that works on SQL Server but not on > PostgreSQL due to SQL Server-specific syntax, which we are working to > migrate. The problem is sometimes psqlodbc just cannot seem to reset > itself after a bad query and simply go dead. All subsequent queries on > the same connection, legitimate or not, would fail. > > I've verified this can happen with the latest PostgreSQL 9.6.2 and > psqlodbc 09_06_0100, on a Windows 7 x64 host. > > The offending SQL query is something like "UPDATE site SET > image=image_old;" where [image] is BYTEA (used to be MEDIUMBLOB on SQL > Server) and [image_old] is TEXT. PostgreSQL does not allow this, which > is understandable. But after this query the connection goes dead, > failing all subsequent queries with ODBC error 08S01 or HY000. While it > is possible to detect this and reconnect as a workaround, it would be > cumbersome and lead to unwarranted overhead. See the following excerpt > from my ODBC trace log: > > > PsqlOdbcBug f68-e10 EXIT SQLPrepareW with return code 0 > (SQL_SUCCESS) > HSTMT 0x00033EE8 > WCHAR * 0x010F0F28 [ -3] "UPDATE site SET > image=image_old;\ 0" > SDWORD -3 > > PsqlOdbcBug f68-e10 ENTER SQLExecute > HSTMT 0x00033EE8 > > PsqlOdbcBug f68-e10 EXIT SQLExecute with return code -1 (SQL_ERROR) > HSTMT 0x00033EE8 > > DIAG [42804] ERROR: column "image" is of type bytea but > expression is of type text; > Error while preparing parameters (1) > > PsqlOdbcBug f68-e10 ENTER SQLFreeHandle > SQLSMALLINT 3 <SQL_HANDLE_STMT> > SQLHANDLE 0x00033EE8 > > PsqlOdbcBug f68-e10 EXIT SQLFreeHandle with return code 0 > (SQL_SUCCESS) > SQLSMALLINT 3 <SQL_HANDLE_STMT> > SQLHANDLE 0x00033EE8 > > PsqlOdbcBug f68-e10 ENTER SQLAllocHandle > SQLSMALLINT 3 <SQL_HANDLE_STMT> > SQLHANDLE 0x000338D8 > SQLHANDLE * 0x0035EB90 > > PsqlOdbcBug f68-e10 EXIT SQLAllocHandle with return code 0 > (SQL_SUCCESS) > SQLSMALLINT 3 <SQL_HANDLE_STMT> > SQLHANDLE 0x000338D8 > SQLHANDLE * 0x0035EB90 ( 0x00D51C98) > > PsqlOdbcBug f68-e10 ENTER SQLPrepareW > HSTMT 0x00D51C98 > WCHAR * 0x010F0DE0 [ -3] "SELECT id FROM site;\ 0" > SDWORD -3 > > PsqlOdbcBug f68-e10 EXIT SQLPrepareW with return code 0 > (SQL_SUCCESS) > HSTMT 0x00D51C98 > WCHAR * 0x010F0DE0 [ -3] "SELECT id FROM site;\ 0" > SDWORD -3 > > PsqlOdbcBug f68-e10 ENTER SQLExecute > HSTMT 0x00D51C98 > > PsqlOdbcBug f68-e10 EXIT SQLExecute with return code -1 (SQL_ERROR) > HSTMT 0x00D51C98 > > DIAG [08S01] Could not send Query(connection dead); > Could not send Query(connection dead) (26) > > > I've attached a minimal working sample code (we develop in Visual Studio > 2013) and also the ODBC trace log. Hopefully they make it through. Any > feedback is welcome. I am betting you dealing with something like this: test=> begin ; BEGIN test=> select 1/0; ERROR: division by zero test=> select 1; ERROR: current transaction is aborted, commands ignored until end of transaction block test=> rollback ; ROLLBACK test=> select 1; ?column? ---------- 1 (1 row) Basically an error in a transaction stops everything until you rollback. Here: https://odbc.postgresql.org/docs/config.html there is : Level of rollback on errors: Specifies what to rollback should an error occur. Nop(0): Don't rollback anything and let the application handle the error. Transaction(1): Rollback the entire transaction. Statement(2): Rollback the statement. Notes in a setup: This specification is set up with a PROTOCOL option parameter. PROTOCOL=[6.2|6.3|6.4|7.4][-(0|1|2)] default value is a sentence unit (it is a transaction unit before 8.0). I have never used that, so I am not sure exactly how to invoke it and what the consequences are. > > > Thank you, > Jack Wei > > > > > -- Adrian Klaver adrian.klaver@aklaver.com
Adrian, thanks for the reply.
Unfortunately I don't think it's about transactions. We do not use transactions in our queries, which means neither BEGIN/ROLLBACK/COMMIT nor ODBC's SQLEndTran().On Fri, Mar 3, 2017 at 12:27 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
I am betting you dealing with something like this:On 03/03/2017 12:14 PM, Jack C. Wei wrote:Hi,
We have some legacy SQL queries that works on SQL Server but not on
PostgreSQL due to SQL Server-specific syntax, which we are working to
migrate. The problem is sometimes psqlodbc just cannot seem to reset
itself after a bad query and simply go dead. All subsequent queries on
the same connection, legitimate or not, would fail.
I've verified this can happen with the latest PostgreSQL 9.6.2 and
psqlodbc 09_06_0100, on a Windows 7 x64 host.
The offending SQL query is something like "UPDATE site SET
image=image_old;" where [image] is BYTEA (used to be MEDIUMBLOB on SQL
Server) and [image_old] is TEXT. PostgreSQL does not allow this, which
is understandable. But after this query the connection goes dead,
failing all subsequent queries with ODBC error 08S01 or HY000. While it
is possible to detect this and reconnect as a workaround, it would be
cumbersome and lead to unwarranted overhead. See the following excerpt
from my ODBC trace log:
PsqlOdbcBug f68-e10 EXIT SQLPrepareW with return code 0
(SQL_SUCCESS)
HSTMT 0x00033EE8
WCHAR * 0x010F0F28 [ -3] "UPDATE site SET
image=image_old;\ 0"
SDWORD -3
PsqlOdbcBug f68-e10 ENTER SQLExecute
HSTMT 0x00033EE8
PsqlOdbcBug f68-e10 EXIT SQLExecute with return code -1 (SQL_ERROR)
HSTMT 0x00033EE8
DIAG [42804] ERROR: column "image" is of type bytea but
expression is of type text;
Error while preparing parameters (1)
PsqlOdbcBug f68-e10 ENTER SQLFreeHandle
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x00033EE8
PsqlOdbcBug f68-e10 EXIT SQLFreeHandle with return code 0
(SQL_SUCCESS)
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x00033EE8
PsqlOdbcBug f68-e10 ENTER SQLAllocHandle
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x000338D8
SQLHANDLE * 0x0035EB90
PsqlOdbcBug f68-e10 EXIT SQLAllocHandle with return code 0
(SQL_SUCCESS)
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x000338D8
SQLHANDLE * 0x0035EB90 ( 0x00D51C98)
PsqlOdbcBug f68-e10 ENTER SQLPrepareW
HSTMT 0x00D51C98
WCHAR * 0x010F0DE0 [ -3] "SELECT id FROM site;\ 0"
SDWORD -3
PsqlOdbcBug f68-e10 EXIT SQLPrepareW with return code 0
(SQL_SUCCESS)
HSTMT 0x00D51C98
WCHAR * 0x010F0DE0 [ -3] "SELECT id FROM site;\ 0"
SDWORD -3
PsqlOdbcBug f68-e10 ENTER SQLExecute
HSTMT 0x00D51C98
PsqlOdbcBug f68-e10 EXIT SQLExecute with return code -1 (SQL_ERROR)
HSTMT 0x00D51C98
DIAG [08S01] Could not send Query(connection dead);
Could not send Query(connection dead) (26)
I've attached a minimal working sample code (we develop in Visual Studio
2013) and also the ODBC trace log. Hopefully they make it through. Any
feedback is welcome.
test=> begin ;
BEGIN
test=> select 1/0;
ERROR: division by zero
test=> select 1;
ERROR: current transaction is aborted, commands ignored until end of transaction block
test=> rollback ;
ROLLBACK
test=> select 1;
?column?
----------
1
(1 row)
Basically an error in a transaction stops everything until you rollback.
Here:
https://odbc.postgresql.org/docs/config.html
there is :
Level of rollback on errors: Specifies what to rollback should an error occur.
Nop(0): Don't rollback anything and let the application handle the error.
Transaction(1): Rollback the entire transaction.
Statement(2): Rollback the statement.
Notes in a setup: This specification is set up with a PROTOCOL option parameter.
PROTOCOL=[6.2|6.3|6.4|7.4][-(0|1|2)]
default value is a sentence unit (it is a transaction unit before 8.0).
I have never used that, so I am not sure exactly how to invoke it and what the consequences are.
Thank you,
Jack Wei
--
Adrian Klaver
adrian.klaver@aklaver.com
Hi Jack, Probably I found the cause. Could you try the test drivers 9.6.0102 at http://www.ne.jp/asahi/inocchichichi/entrance/psqlodbc/ ? regards, Hiroshi Inoue On 2017/03/04 5:14, Jack C. Wei wrote: > Hi, > > We have some legacy SQL queries that works on SQL Server but not on > PostgreSQL due to SQL Server-specific syntax, which we are working to > migrate. The problem is sometimes psqlodbc just cannot seem to reset > itself after a bad query and simply go dead. All subsequent queries on > the same connection, legitimate or not, would fail. > > I've verified this can happen with the latest PostgreSQL 9.6.2 and > psqlodbc 09_06_0100, on a Windows 7 x64 host. > > The offending SQL query is something like "UPDATE site SET > image=image_old;" where [image] is BYTEA (used to be MEDIUMBLOB on SQL > Server) and [image_old] is TEXT. PostgreSQL does not allow this, which > is understandable. But after this query the connection goes dead, > failing all subsequent queries with ODBC error 08S01 or HY000. While > it is possible to detect this and reconnect as a workaround, it would > be cumbersome and lead to unwarranted overhead. See the following > excerpt from my ODBC trace log: > > > PsqlOdbcBug f68-e10 EXIT SQLPrepareW with return code 0 > (SQL_SUCCESS) > HSTMT 0x00033EE8 > WCHAR * 0x010F0F28 [ -3] "UPDATE site SET > image=image_old > ;\ 0" > SDWORD -3 > > PsqlOdbcBug f68-e10 ENTER SQLExecute > HSTMT 0x00033EE8 > > PsqlOdbcBug f68-e10 EXIT SQLExecute with return code -1 > (SQL_ERROR) > HSTMT 0x00033EE8 > > DIAG [42804] ERROR: column "image" is of type bytea but > expression is of type text; > Error while preparing parameters (1) > > PsqlOdbcBug f68-e10 ENTER SQLFreeHandle > SQLSMALLINT 3 <SQL_HANDLE_STMT> > SQLHANDLE 0x00033EE8 > > PsqlOdbcBug f68-e10 EXIT SQLFreeHandle with return code 0 > (SQL_SUCCESS) > SQLSMALLINT 3 <SQL_HANDLE_STMT> > SQLHANDLE 0x00033EE8 > > PsqlOdbcBug f68-e10 ENTER SQLAllocHandle > SQLSMALLINT 3 <SQL_HANDLE_STMT> > SQLHANDLE 0x000338D8 > SQLHANDLE * 0x0035EB90 > > PsqlOdbcBug f68-e10 EXIT SQLAllocHandle with return code 0 > (SQL_SUCCESS) > SQLSMALLINT 3 <SQL_HANDLE_STMT> > SQLHANDLE 0x000338D8 > SQLHANDLE * 0x0035EB90 ( 0x00D51C98) > > PsqlOdbcBug f68-e10 ENTER SQLPrepareW > HSTMT 0x00D51C98 > WCHAR * 0x010F0DE0 [ -3] "SELECT id FROM > site;\ 0" > SDWORD -3 > > PsqlOdbcBug f68-e10 EXIT SQLPrepareW with return code 0 > (SQL_SUCCESS) > HSTMT 0x00D51C98 > WCHAR * 0x010F0DE0 [ -3] "SELECT id FROM > site;\ 0" > SDWORD -3 > > PsqlOdbcBug f68-e10 ENTER SQLExecute > HSTMT 0x00D51C98 > > PsqlOdbcBug f68-e10 EXIT SQLExecute with return code -1 > (SQL_ERROR) > HSTMT 0x00D51C98 > > DIAG [08S01] Could not send Query(connection dead); > Could not send Query(connection dead) (26) > > > I've attached a minimal working sample code (we develop in Visual > Studio 2013) and also the ODBC trace log. Hopefully they make it > through. Any feedback is welcome. > > > Thank you, > Jack Wei >
Hi Hiroshi,
I just tried the test driver 9.6.0102 and it seems to have fixed the bug. Thank you for the good work.On Sat, Mar 4, 2017 at 5:48 AM, Inoue, Hiroshi <h-inoue@dream.email.ne.jp> wrote:
Hi Jack,
Probably I found the cause.
Could you try the test drivers 9.6.0102 at
http://www.ne.jp/asahi/inocchichichi/entrance/psqlodbc/
?
regards,
Hiroshi Inoue
On 2017/03/04 5:14, Jack C. Wei wrote:Hi,
We have some legacy SQL queries that works on SQL Server but not on PostgreSQL due to SQL Server-specific syntax, which we are working to migrate. The problem is sometimes psqlodbc just cannot seem to reset itself after a bad query and simply go dead. All subsequent queries on the same connection, legitimate or not, would fail.
I've verified this can happen with the latest PostgreSQL 9.6.2 and psqlodbc 09_06_0100, on a Windows 7 x64 host.
The offending SQL query is something like "UPDATE site SET image=image_old;" where [image] is BYTEA (used to be MEDIUMBLOB on SQL Server) and [image_old] is TEXT. PostgreSQL does not allow this, which is understandable. But after this query the connection goes dead, failing all subsequent queries with ODBC error 08S01 or HY000. While it is possible to detect this and reconnect as a workaround, it would be cumbersome and lead to unwarranted overhead. See the following excerpt from my ODBC trace log:
PsqlOdbcBug f68-e10 EXIT SQLPrepareW with return code 0 (SQL_SUCCESS)
HSTMT 0x00033EE8
WCHAR * 0x010F0F28 [ -3] "UPDATE site SET image=image_old;\ 0"
SDWORD -3
PsqlOdbcBug f68-e10 ENTER SQLExecute
HSTMT 0x00033EE8
PsqlOdbcBug f68-e10 EXIT SQLExecute with return code -1 (SQL_ERROR)
HSTMT 0x00033EE8
DIAG [42804] ERROR: column "image" is of type bytea but expression is of type text;
Error while preparing parameters (1)
PsqlOdbcBug f68-e10 ENTER SQLFreeHandle
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x00033EE8
PsqlOdbcBug f68-e10 EXIT SQLFreeHandle with return code 0 (SQL_SUCCESS)
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x00033EE8
PsqlOdbcBug f68-e10 ENTER SQLAllocHandle
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x000338D8
SQLHANDLE * 0x0035EB90
PsqlOdbcBug f68-e10 EXIT SQLAllocHandle with return code 0 (SQL_SUCCESS)
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x000338D8
SQLHANDLE * 0x0035EB90 ( 0x00D51C98)
PsqlOdbcBug f68-e10 ENTER SQLPrepareW
HSTMT 0x00D51C98
WCHAR * 0x010F0DE0 [ -3] "SELECT id FROM site;\ 0"
SDWORD -3
PsqlOdbcBug f68-e10 EXIT SQLPrepareW with return code 0 (SQL_SUCCESS)
HSTMT 0x00D51C98
WCHAR * 0x010F0DE0 [ -3] "SELECT id FROM site;\ 0"
SDWORD -3
PsqlOdbcBug f68-e10 ENTER SQLExecute
HSTMT 0x00D51C98
PsqlOdbcBug f68-e10 EXIT SQLExecute with return code -1 (SQL_ERROR)
HSTMT 0x00D51C98
DIAG [08S01] Could not send Query(connection dead);
Could not send Query(connection dead) (26)
I've attached a minimal working sample code (we develop in Visual Studio 2013) and also the ODBC trace log. Hopefully they make it through. Any feedback is welcome.
Thank you,
Jack Wei
Hi Jack,
Probably we would make a new release in 10 days.
regards,
Hiroshi Inoue
On 2017/03/08 7:10, Jack C. Wei wrote:
When can we expect an official release of the new driver? We're looking to deploy PostgreSQL in six weeks. Currently I'm using 9.3.0400 which is the last official version that does not have this issue. 9.5.0100 starts having this problem. So is 9.5.0400 and 9.6.0100 that I tried.Hi Hiroshi,I just tried the test driver 9.6.0102 and it seems to have fixed the bug. Thank you for the good work.
Probably we would make a new release in 10 days.
regards,
Hiroshi Inoue
JackRegards,On Sat, Mar 4, 2017 at 5:48 AM, Inoue, Hiroshi <h-inoue@dream.email.ne.jp> wrote:Hi Jack,
Probably I found the cause.
Could you try the test drivers 9.6.0102 at
http://www.ne.jp/asahi/inocchichichi/entrance/psqlodbc/
?
regards,
Hiroshi Inoue
On 2017/03/04 5:14, Jack C. Wei wrote:Hi,
We have some legacy SQL queries that works on SQL Server but not on PostgreSQL due to SQL Server-specific syntax, which we are working to migrate. The problem is sometimes psqlodbc just cannot seem to reset itself after a bad query and simply go dead. All subsequent queries on the same connection, legitimate or not, would fail.
I've verified this can happen with the latest PostgreSQL 9.6.2 and psqlodbc 09_06_0100, on a Windows 7 x64 host.
The offending SQL query is something like "UPDATE site SET image=image_old;" where [image] is BYTEA (used to be MEDIUMBLOB on SQL Server) and [image_old] is TEXT. PostgreSQL does not allow this, which is understandable. But after this query the connection goes dead, failing all subsequent queries with ODBC error 08S01 or HY000. While it is possible to detect this and reconnect as a workaround, it would be cumbersome and lead to unwarranted overhead. See the following excerpt from my ODBC trace log:
PsqlOdbcBug f68-e10 EXIT SQLPrepareW with return code 0 (SQL_SUCCESS)
HSTMT 0x00033EE8
WCHAR * 0x010F0F28 [ -3] "UPDATE site SET image=image_old;\ 0"
SDWORD -3
PsqlOdbcBug f68-e10 ENTER SQLExecute
HSTMT 0x00033EE8
PsqlOdbcBug f68-e10 EXIT SQLExecute with return code -1 (SQL_ERROR)
HSTMT 0x00033EE8
DIAG [42804] ERROR: column "image" is of type bytea but expression is of type text;
Error while preparing parameters (1)
PsqlOdbcBug f68-e10 ENTER SQLFreeHandle
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x00033EE8
PsqlOdbcBug f68-e10 EXIT SQLFreeHandle with return code 0 (SQL_SUCCESS)
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x00033EE8
PsqlOdbcBug f68-e10 ENTER SQLAllocHandle
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x000338D8
SQLHANDLE * 0x0035EB90
PsqlOdbcBug f68-e10 EXIT SQLAllocHandle with return code 0 (SQL_SUCCESS)
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x000338D8
SQLHANDLE * 0x0035EB90 ( 0x00D51C98)
PsqlOdbcBug f68-e10 ENTER SQLPrepareW
HSTMT 0x00D51C98
WCHAR * 0x010F0DE0 [ -3] "SELECT id FROM site;\ 0"
SDWORD -3
PsqlOdbcBug f68-e10 EXIT SQLPrepareW with return code 0 (SQL_SUCCESS)
HSTMT 0x00D51C98
WCHAR * 0x010F0DE0 [ -3] "SELECT id FROM site;\ 0"
SDWORD -3
PsqlOdbcBug f68-e10 ENTER SQLExecute
HSTMT 0x00D51C98
PsqlOdbcBug f68-e10 EXIT SQLExecute with return code -1 (SQL_ERROR)
HSTMT 0x00D51C98
DIAG [08S01] Could not send Query(connection dead);
Could not send Query(connection dead) (26)
I've attached a minimal working sample code (we develop in Visual Studio 2013) and also the ODBC trace log. Hopefully they make it through. Any feedback is welcome.
Thank you,
Jack Wei