search

tds-fdw / tds_fdw

A PostgreSQL foreign data wrapper to connect to TDS databases (Sybase and Microsoft SQL Server)
Other
380 stars 102 forks source link

query/connection is not stable when connection to sybase 11 #135

Open yong9900 opened 7 years ago

yong9900 commented 7 years ago

The problem: the sql I ran involve foreign table is not always successful (about 1/3 of total tries)

I tried to connect from my postgres 9.5 db to my sybase (version information) SQL Server/11.0.3.3 ESD#6/P-FREE/Linux Intel/Linux 2.2.14 i686/1/OPT/Fri Mar 17 15:45:30 CET 2000

I set up in my postgres 9.5 as following:

create server syb foreign data wrapper tds_fdw options( servername '127.0.0.1', port '7100', database 'MySybDB', tds_version '5.0', MyPostgres '1', language 'us_english', msg_handler 'notice');

create user mapping for abc server syb options (username 'abc', password 'abc');

CREATE FOREIGN TABLE test_on_sybase.table1 ( col1 integer, col2 real, col3 varchar) server syb options (query 'select col1,col2,col3 from syb_table');

with this setup, sometimes I got correct result, sometime I got errors. Occasionally, my sybase server crashed (it never crashs without my queries from postgres side).

Good ones

select count(*) from test_on_sybase.table1 ; NOTICE: DB-Library notice: Msg #: 5701, Msg state: 2, Msg: Changed database context to 'defaultdb'. , Server: [127.0.0.1:7100], Process: , Line: 1, Level: 0 NOTICE: DB-Library notice: Msg #: 5701, Msg state: 1, Msg: Changed database context to 'MySybDB'. , Server: , Process: , Line: 1, Level: 0 NOTICE: DB-Library notice: Msg #: 5701, Msg state: 2, Msg: Changed database context to 'defaultdb'. , Server: [127.0.0.1:7100], Process: , Line: 1, Level: 0 NOTICE: DB-Library notice: Msg #: 5701, Msg state: 1, Msg: Changed database context to 'MySybDB'. , Server: , Process: , Line: 1, Level: 0 count 2335 (1 row)

BAD ONES:

select count(*) from test_on_sybase.table1 ; NOTICE: DB-Library notice: Msg #: 5701, Msg state: 2, Msg: Changed database context to 'defaultdb'. , Server: [127.0.0.1:7100], Process: , Line: 1, Level: 0 NOTICE: DB-Library notice: Msg #: 5701, Msg state: 1, Msg: Changed database context to 'MySybDB'. , Server: , Process: , Line: 1, Level: 0 NOTICE: DB-Library notice: Msg #: 5702, Msg state: 1, Msg: The SQL Server is terminating this process. , Server: [127.0.0.1:7100], Process: , Line: 1, Level: 0 ERROR: DB-Library error: DB #: 20002, DB Msg: Adaptive Server connection failed, OS #: 0, OS Msg: Success, Level: 9 Time: 30.141 ms

select count(*) from test_on_sybase.table1 ; NOTICE: DB-Library notice: Msg #: 5701, Msg state: 2, Msg: Changed database context to 'defaultdb'. , Server: [127.0.0.1:7100], Process: , Line: 1, Level: 0 NOTICE: DB-Library notice: Msg #: 5701, Msg state: 1, Msg: Changed database context to 'MySybDB'. , Server: , Process: , Line: 1, Level: 0 ERROR: invalid byte sequence for encoding "UTF8": 0x86 Time: 6.051 ms

select count(*) from test_on_sybase.table1 ; NOTICE: DB-Library notice: Msg #: 5701, Msg state: 2, Msg: Changed database context to 'defaultdb'. , Server: [127.0.0.1:7100], Process: , Line: 1, Level: 0 NOTICE: DB-Library notice: Msg #: 5701, Msg state: 1, Msg: Changed database context to 'MySybDB'. , Server: , Process: , Line: 1, Level: 0 ERROR: invalid byte sequence for encoding "UTF8": 0xbd Time: 19.843 ms

select count(*) from test_on_sybase.table1 ; ERROR: invalid byte sequence for encoding "UTF8": 0xb5 Time: 1.269 ms

select count(*) from test_on_sybase.table1 ; NOTICE: DB-Library notice: Msg #: 5701, Msg state: 2, Msg: Changed database context to 'defaultdb'. , Server: [127.0.0.1:7100], Process: , Line: 1, Level: 0 NOTICE: DB-Library notice: Msg #: 5701, Msg state: 1, Msg: Changed database context to 'MySybDB'. , Server: , Process: , Line: 1, Level: 0 ERROR: invalid byte sequence for encoding "UTF8": 0xe5 0x5e 0x56 Time: 28.796 ms

select count(*) from test_on_sybase.table1 ; ERROR: DB-Library error: DB #: 20017, DB Msg: Unexpected EOF from the server, OS #: 115, OS Msg: Operation now in progress, Level: 9 Time: 4355.342 ms

select count(*) from test_on_sybase.table1 ; ERROR: DB-Library error: DB #: 20009, DB Msg: Unable to connect: Adaptive Server is unavailable or does not exist, OS #: 111, OS Msg: Connection refused, Level: 9 Time: 87.135 ms

On Sybase side, in the error logs, I got (this might not be directly related to the queries I ran from postgres side, as this db was accessed by multiple applications):

00:2017/07/25 10:00:24.01 kernel Cannot read, host process disconnected: qa4141mst1.intelerad.com 13050 spid: 79 00:2017/07/25 10:01:07.17 kernel Cannot read, host process disconnected: qa4141mst1.intelerad.com 13050 spid: 79 00:2017/07/25 10:51:24.31 kernel Cannot read, host process disconnected: qa4141mst1.intelerad.com 61789 spid: 104 00:2017/07/25 10:51:27.30 kernel Cannot read, host process disconnected: qa4141mst1.intelerad.com 61789 spid: 106 00:2017/07/25 10:51:30.60 kernel Cannot read, host process disconnected: qa4141mst1.intelerad.com 61789 spid: 108 00:2017/07/25 10:51:32.10 kernel nspacket: send, Connection reset by peer 00:2017/07/25 10:51:35.10 kernel nspacket: send, Connection reset by peer 00:2017/07/25 10:51:38.74 kernel Cannot send, host process disconnected: qa4141mst1.intelerad.com 61789 spid: 125 00:2017/07/25 10:51:42.10 kernel Cannot read, host process disconnected: qa4141mst1.intelerad.com 61789 spid: 122 00:2017/07/25 10:51:43.47 kernel Cannot read, host process disconnected: qa4141mst1.intelerad.com 61789 spid: 122 00:2017/07/25 10:52:10.72 kernel Cannot read, host process disconnected: qa4141mst1.intelerad.com 61789 spid: 125 00:2017/07/25 10:52:13.86 kernel nspacket: send, Connection reset by peer 00:2017/07/25 10:52:21.44 kernel nspacket: send, Connection reset by peer 00:2017/07/25 10:52:26.09 kernel Cannot read, host process disconnected: qa4141mst1.intelerad.com 61789 spid: 126 00:2017/07/25 10:52:44.66 kernel current process (0x736701de) infected with 11 00:2017/07/25 10:52:44.66 kernel **** 00:2017/07/25 10:52:44.66 kernel SQL causing error : 00:2017/07/25 10:52:44.66 kernel curdb = 6 pstat = 0x10000 lasterror = 0 00:2017/07/25 10:52:44.66 kernel preverror = 0 transtate = 1 00:2017/07/25 10:52:44.66 kernel curcmd = 193 program = 00:2017/07/25 10:52:44.68 kernel pc 0x82c94d5 ucbacktrace+0x61(0x0,0x1,0x83f2ad4,0xb,0x9b9b9d7b) 00:2017/07/25 10:52:44.68 kernel pc 0x8083e29 terminate_process+0x389(0x0,0xffffffff,0xb,0x83f2ad4,0xbc304d56) 00:2017/07/25 10:52:44.68 kernel pc 0x82d7d9b kisignal+0x83(0xb,0x63,0x0,0x2b,0x2b) 00:2017/07/25 10:52:44.68 kernel pc 0xf7fe0400 init_dummy+0xefccf79c(0x9b9b9b9b,0x9b9b9b9b,0xfc8caca4,0xf3f3f349,0xf2f2f3f3) 00:2017/07/25 10:52:44.68 kernel end of stack trace, spid 128, kpid 1936130526, suid 8 00:2017/07/25 10:52:49.65 kernel nspacket: send, Connection reset by peer 00:2017/07/25 10:54:46.14 kernel nspacket: send, Connection reset by peer 00:2017/07/25 10:54:47.92 kernel Cannot read, host process disconnected: qa4141mst1.intelerad.com 61789 spid: 130 00:2017/07/25 10:54:52.17 kernel Cannot read, host process disconnected: qa4141mst1.intelerad.com 61789 spid: 131 00:2017/07/25 10:54:59.60 kernel Cannot read, host process disconnected: qa4141mst1.intelerad.com 61789 spid: 129 00:2017/07/25 10:55:54.61 kernel Cannot read, host process disconnected: qa4141mst1.intelerad.com 61789 spid: 129 00:2017/07/25 10:56:11.42 kernel nspacket: send, Connection reset by peer 00:2017/07/25 10:56:24.77 kernel nspacket: send, Connection reset by peer 00:2017/07/25 10:56:32.05 kernel nspacket: send, Connection reset by peer 00:2017/07/25 10:56:39.56 kernel Cannot read, host process disconnected: qa4141mst1.intelerad.com 61789 spid: 136 .... 00:2017/07/25 11:44:32.29 kernel SQL causing error : 00:2017/07/25 11:44:32.29 kernel curdb = 6 pstat = 0x10000 lasterror = 0 00:2017/07/25 11:44:32.29 kernel preverror = 0 transtate = 1 00:2017/07/25 11:44:32.29 kernel curcmd = 193 program = 00:2017/07/25 11:44:32.31 kernel pc 0x82c94d5 ucbacktrace+0x61(0x0,0x1,0x83f2ad4,0xb,0x8f8f916f) 00:2017/07/25 11:44:32.31 kernel pc 0x8083e29 terminate_process+0x389(0x0,0xffffffff,0xb,0x83f2ad4,0xbc304d56) 00:2017/07/25 11:44:32.31 kernel pc 0x82d7d9b kisignal+0x83(0xb,0x63,0x0,0x2b,0x2b) 00:2017/07/25 11:44:32.31 kernel pc 0xf7fe0400 init_dummy+0xefccf79c(0x8f8f8f8f,0x6f8f8f8f,0x315f,0x0,0x0) 00:2017/07/25 11:44:32.31 kernel end of stack trace, spid 85, kpid 95551520, suid 8

Any idea why?

yong9900 commented 7 years ago

btw, I am using freetds-0.91-2.el6.i686 and tds_fdw 1.0.8