About connection problem of SQL Anywhere 17

[former_member420304]

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?

[chris_keating]

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>.

[former_member420304]

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?

[former_member420304]

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 サーバに接続できません。

[chris_keating]

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.

[former_member186998 · Accepted solution]

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,

[reimer_pods · Accepted solution]

Hi,

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

[former_member186998 · Accepted solution]

Hi,

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

Thanks,

[reimer_pods · Accepted solution]

Hi,

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

Reimer

[former_member420304 · Accepted solution]

Asano san.

We still fight against this problem.

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

[former_member186998 · Accepted solution]

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,

[chris_keating · Accepted solution]

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.

[former_member420304 · Accepted solution]

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.

[former_member186998 · Accepted solution]

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,

[former_member420304 · Accepted solution]

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

It might be fixed by new version.

[former_member186998 · Accepted solution]

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

[former_member420304 · Accepted solution]

Ok, we will continue the test for a while.

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

[former_member420304 · Accepted solution]

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.

[former_member186998 · Accepted solution]

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

[former_member420304 · Accepted solution]

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.)

[former_member186998 · Accepted solution]

"Upgrade Existing Install" is a new feature of version 17.
Therefore, you cannot use this function with version 12.

Changes to administration tools
Deployment Wizard for Windows enhancements
Deployment Wizard for Windows

Thanks,
Atsushi

[former_member420304 · Accepted solution]

Asano san.

We are sorry to take long time to respond.
Because it took a while to decide the treatment of this problem.

I close this thread.
Because we decided to suspend(possibly stop) the investigation.

The reasons are
・To test every version step by step needs a lot of time and costs.
・It is possible to explain to our customer with the information which we collected.

We appreciate for your support.