Question Re: About connection problem of SQL Anywhere 17 in Technology Q&A

About connection problem of SQL Anywhere 17

Former Member — Fri, 06 Apr 2018 08:27:11 GMT

We have an problem about the connection of SQL Anywhere 17.

During running our system,
Suddenly the connection was closed.
And after this, the client could not connect to the server.

below is excerpt of the log(But Japanese.I'm sorry)
====== Excerpt of log ===========
:
I. 04/06 07:27:03. 接続 ID 132316: "DBA" は切断され、接続はプールにキャッシュされました
I. 04/06 07:27:03. 接続 ID 132317: "DBA" は接続され、プールされた接続 ID 132316 を再利用しました
I. 04/06 07:27:03. 接続 ID 132317: "DBA" は切断され、接続はプールにキャッシュされました
I. 04/06 07:27:03. 接続 ID 132318: "DBA" は接続され、プールされた接続 ID 132317 を再利用しました
I. 04/06 07:27:03. 接続 ID 132315: "DBA" は切断され、接続はプールにキャッシュされました
I. 04/06 07:27:03. 接続 ID 132319: "DBA" は接続され、プールされた接続 ID 132315 を再利用しました
I. 04/06 07:27:03. 接続 ID 132318: "DBA" は切断され、接続はプールにキャッシュされました
I. 04/06 07:27:03. 接続 ID 132320: "DBA" は接続され、プールされた接続 ID 132318 を再利用しました
I. 04/06 07:27:03. TCP/IP: 172.16.1.31:49380 からの接続要求を受信しました。
I. 04/06 07:27:03. 172.16.1.31:49380 からの接続によって接続 ID 132321 が割り当てられました。
I. 04/06 07:27:03. 接続 ID 132321: "DBA" は TCPIP によって SQL Anywhere 17.0.7 (3382) クライアントからデータベース "NSCLDB" に接続されました
I. 04/06 07:27:03. 接続 ID 132321: AppInfo は IP=172.16.1.31;HOST=aiphone-PC;OSUSER=aiphone;OS='Windows 7 Build 7601 Service Pack 1';EXE='C:\Program Files\Aiphone\Vi-nurse Client\PcncClient.exe';PID=0xcf8;THREAD=0xf50;VERSION=17.0.7.3382;API=ADO.NET;TIMEZONEADJUSTMENT=540 です。
I. 04/06 07:27:03. 接続 ID 132321: サーバの文字セット変換は有効ですが、不要です。
文字セット "Windows-31J" を使用
I. 04/06 07:27:03. 接続 ID 132321: 文字セットが "none" に変更されました
I. 04/06 07:27:03. 接続 ID 132321: "DBA" は切断され、接続はプールにキャッシュされました
I. 04/06 07:27:03. 接続 ID 132320: "DBA" は切断され、接続はプールにキャッシュされました
I. 04/06 07:27:03. 接続 ID 132322: "DBA" は接続され、プールされた接続 ID 132320 を再利用しました
:

It seems
the server suddenly stopped connection pooling to the cache
and also stopped reuse of the connection id(which had been already pooled).
Once the connection id was allocated, but it was soon closed.
But I'm not sure.

So please someone teach me what's happened and how to investigate.

We use SQL Anywhere 17.0.7 (3382).
Is it better to use new version?

Re: About connection problem of SQL Anywhere 17

chris_keating — Fri, 06 Apr 2018 14:20:08 GMT

I google translated this snipet. What I see is Connection 132321 is connected and disconnected. I do not see evidence that the disconnect was abnormal. It is noted that this connection is being cached. A second connection, 132320, is disconnected and cached. A new connection is then made and uses the connection 132320 from the pool.

Please refer to the Connection Pooling documentation for a full discussion of pooling by the SA server..

I typically use -z to troubleshoot issues related to connections on the server. If there are TDS connections, you will need to ensure that you suppress TDS debugging information. If you can reproduce this with a specific client, it would be ideal to have the client debug log paired with the server. A client debug log is enabled with the connection parameter LOG=<debug_log_file>.

Re: About connection problem of SQL Anywhere 17

Former Member — Mon, 09 Apr 2018 00:31:59 GMT

I appreciate for your advice.
I try -z option and get client debug log.

=============================

I am sorry.
I put a wrong place with the log(I noticed that from your comments).

This part is a place which we wonder.
:
I. 04/06 07:29:12. 接続 ID 132591: "DBA" は切断され、接続はプールにキャッシュされました
I. 04/06 07:29:12. 接続 ID 132592: "DBA" は接続され、プールされた接続 ID 132591 を再利用しました
I. 04/06 07:29:12. 接続 ID 132592: "DBA" は切断され、接続はプールにキャッシュされました
I. 04/06 07:29:12. 接続 ID 132593: "DBA" は接続され、プールされた接続 ID 132592 を再利用しました
I. 04/06 07:29:15. TCP/IP: 172.16.3.31:51696 からの接続要求を受信しました。
I. 04/06 07:29:15. 172.16.3.31:51696 からの接続によって接続 ID 132594 が割り当てられました。
I. 04/06 07:29:17. TCP/IP: 172.16.3.32:59700 からの接続要求を受信しました。
I. 04/06 07:29:17. 172.16.3.32:59700 からの接続によって接続 ID 132595 が割り当てられました。
I. 04/06 07:29:52. TCP/IP: 172.16.3.31:51721 からの接続要求を受信しました。
:
messages like last 2 rows above continue forever.

We wonder why the connection pooling cycle has stopped at 07:29:12 and never start again.
Before 07:29:12, conections have always pooled, but after 07:29:12 connections never pooled.
What's happend at 07:29:12?

Re: About connection problem of SQL Anywhere 17

Former Member — Tue, 10 Apr 2018 11:01:24 GMT

We got a client log.
But we could not find the answer.
Please someone teach us if this log has some hints or not.

:
20:38:39 UID=DBA;PWD=********;DBN=NSCLDB;ServerName=NSCLDB;CON='SQL Central 5';LOG=c:\nlx\sqlany17_connect_log.txt;LINKS='tcpip(host=localhost;port=2638)' を使用して接続を試みています。
20:38:39 動作中のサーバへの接続を試みています...
20:38:39 TCPIP 接続を試みています (アドレス 127.0.0.1:2638 が sasrv.ini キャッシュ内に見つかりました)。
20:38:39 nscldb という名前のサーバを探しています。
20:38:39 ブロードキャストせずに、キャッシュされたアドレス 127.0.0.1:2638 のサーバを見つけようとしています。
20:38:39 TCPIP リンクにデータベースサーバ nscldb がありました。
20:38:39 クライアントアドレス 127.0.0.1:49334 を使用して接続しました。
20:38:39 TCPIP によってサーバに接続しました。
20:39:10 通信関数 sconn::receive コード 4
20:39:10 接続応答の待機中にタイムアウトになりました。
20:39:10 クライアントを切断しました。
20:39:16 サーバに接続できません。

Re: About connection problem of SQL Anywhere 17

chris_keating — Tue, 10 Apr 2018 14:23:43 GMT

The server log (-z) shows connections from several IPs being received and assigned connections. Are you indicating that the same IPs are repeated reporting requests and connections being assigned? Those requests are coming from the clients. Is it possible that the clients have some sort of retry gear?

The client log shows an error sconn::receive 4 that is related to a timeout. We can see that the connection is established and then 30s later the connection reports a timeout error. That is the default timeout period for a connection. This tells me that this client was not able to establish a valid connection. But I do not have a server log for this connection so I cannot determine what happened at the server with this connection.

Re: About connection problem of SQL Anywhere 17

former_member186998 — Fri, 13 Apr 2018 04:53:20 GMT

Hi,

Did you solve this problem?

> We use SQL Anywhere 17.0.7 (3382).
> Is it better to use new version?

I recommend that you confirm this problem using the latest version.
The latest version of version 17 is 4793 now.

2381119 - About the latest version of SQL Anywhere

Thanks,

Re: About connection problem of SQL Anywhere 17

reimer_pods — Fri, 13 Apr 2018 08:03:38 GMT

Hi,

I could not open the link 2381119 in your answer. Could it be that it is only valid inside SAP?

Re: About connection problem of SQL Anywhere 17

former_member186998 — Mon, 16 Apr 2018 05:14:13 GMT

Hi,

Can you confirm the following link?
2381119 - About the latest version of SQL Anywhere

Thanks,

Re: About connection problem of SQL Anywhere 17

reimer_pods — Mon, 16 Apr 2018 08:19:54 GMT

Hi,

thanks for the modfied link, I could open it successfully (requires login with a valid S-ID).

Reimer

Re: About connection problem of SQL Anywhere 17

Former Member — Tue, 17 Apr 2018 11:09:58 GMT

Asano san.

We still fight against this problem.

Thank you for your suggestion.
We try the version 17(4793).

Re: About connection problem of SQL Anywhere 17

former_member186998 — Wed, 18 Apr 2018 01:08:41 GMT

Hi,

ok, I will wait for your reply.
So, I have some questions.
1.Does the other environment(client) have the same problem?
2.Did you confirm event log of the time when this problem occurred?
3.Can you be connected to the server from the client machine which a problem produced using SQL Central?

Thanks,

Re: About connection problem of SQL Anywhere 17

chris_keating — Wed, 18 Apr 2018 17:02:45 GMT

And get the paired logs (client and server communication debugging) that was requested in an earlier part of this topic. It looks like the connection is not completing in the timeout period but we only have an one-sided view of the behavior.

Re: About connection problem of SQL Anywhere 17

Former Member — Fri, 20 Apr 2018 06:22:40 GMT

Asano san.

From today to next Monday, we try the 4793 version.

>>1.Does the other environment(client) have the same problem?

Yes, it's happened to all clients

>>2.Did you confirm event log of the time when this problem occurred?

We're sorry.
We don't know how to get.
Could you teach us ?

>>3.Can you be connected to the server from the client machine which a problem produced using SQL Central?

No, we can't connect by using SQL Central too.

-----------------------------------
Dear Chris

The paired logs will be gotten by next Monday.
Please wait.

Re: About connection problem of SQL Anywhere 17

former_member186998 — Fri, 20 Apr 2018 07:33:32 GMT

Hi,

My explanation was insufficient.

Event log is log of the OS's. Go to Start > Control Panel > Administrative Tools > Event viewer. Also Event Viewer can be launched from Run menu (Win+R) by entering eventvwr.msc.
Application log and System log

I want to confirm log of the time when this problem occurred.

Thanks,

Re: About connection problem of SQL Anywhere 17

Former Member — Wed, 25 Apr 2018 01:25:14 GMT

After updating the version of SQL anywhere(3382->4793), the problem have not been occured yet.

It might be fixed by new version.

Re: About connection problem of SQL Anywhere 17

former_member186998 — Thu, 26 Apr 2018 01:33:34 GMT

Hi,

Thank you for explaining the situation. Let's keep an eye on things for a while.
And, please close this thread when the same phenomenon does not occur. Please go ahead and "accept" your own answer here, so the question will be marked as answered for others looking for this solution.

Thanks,
Atsushi

Re: About connection problem of SQL Anywhere 17

Former Member — Fri, 27 Apr 2018 06:57:01 GMT

Ok, we will continue the test for a while.

If this problem never happens again, I will close this thread.

Re: About connection problem of SQL Anywhere 17

Former Member — Wed, 09 May 2018 09:25:12 GMT

Asano san

The connection problem have not happened for around 3 weeks.
So I want to close this thread.

But before it, we want to ensure that the problem was fixed by versioning up.
We want to know which change of the SQL anywhere related to this problem.
So please advise us how to do it if there is.

We picked up the suspected portion of the change log of SQL anywhere.
We think one or some of these have an answer.

■SQL Anywhere 17
================(Build #4140 - Engineering Case #813094)================

Under very rare circumstances, the server may hang if a DDL statement, a
procedure call in a TDS based connection, and a select that other connections
properties queried run concurrently. This has been fixed.

================(Build #4085 - Engineering Case #811587)================

In rare cases a server may hang while trying to estimate a selectivity of
a particular predicate using an index. The hang is the result of a deadlock
between cleaner process and index based selectivity estimation. This has
been fixed.

================(Build #4042 - Engineering Case #810834)================

Poor performance may have happed on queries that involve index scans. The
performance hit was more visible when there were many concurrent connections
accessing keys that are of small proximity of each other in the index. Other
observed behaviors included server lookup and hangs. This has been fixed.

■SQL Anywhere 12 (We use SQL Anywhere 12 too and we have same problem on it.)

================(Build #4239 - Engineering Case #779711)================

When running an archive backup of a large database (greater than 5 GB), the
server would appear to be hung. Other connections would also hang and new
connections would not be allowed. The server would eventually continue and
the backup would complete but the server could be unavailable for several
minutes. This has been fixed.

================(Build #4166 - Engineering Case #770430)================

A server thread can go into an infinite loop attempting to update an index,
eventually resulting in a server hang. The index in question is not corrupt,
the server was just misinterpreting an index key. The is fixed so that the
key is now interpreted correctly.

================(Build #4122 - Engineering Case #765821)================

Server could have hang while executing a stored procedure that invoked other
stored procedures. This has been fixed.

Re: About connection problem of SQL Anywhere 17

former_member186998 — Thu, 10 May 2018 08:47:44 GMT

Thank you for your reply.
When you have a clear repro step, you apply it sequentially from old EBF.

For example:
3382 -> 3399 -> 4003 -> 4043 -> 4075 -> ...... -> 4793

And you confirm EBF which a problem did not produce. You may identify a rough problem by this method.

However, this method is not usable when you do not have a repro step.
I think that it is difficult to identify this problem by current information.

Thanks,
Atsushi

Re: About connection problem of SQL Anywhere 17

Former Member — Mon, 14 May 2018 07:55:20 GMT

Atsushi san.

Thank you for your reply.
Following your proposition, we are going to adapt each version step by step.

But we are now little bit confusing about the dialog of DeploymentWizard.exe.
In the dialog, we need to choose "Create New Install" or "Upgrade Existing Install".
Which is the right choice?

※We think that "Upgrade Existing Install" is must-be-selected,
but in SQL Anywhere 12 there is not a "Upgrade Existing Install" item.
(We use SQL Anywhere 12 and 17.)