EBF 4239 Cannot drop connections or shut down the database

[Former Member]

Hi,

This seems to be new behaviour since we have applied the EBF 4239 to 10.0.1 because we have not seen it before. We are seeing various connections that do not respond to either a DROP CONNECTION xxxxxx or a disconnect from Sybase Central.

If we try to shut the database down from Sybase Central it just hangs and the service never disappears from task manager but won't accept any more connections. Eventually we have to resort to ending the dbsrv10 task.

For the startup parameters we use: -x tcpip(dobroadcast=no;serverport=2638) -ti 600 -tl 0 -c 25P -ch 25P -zl From sa_conn_info for the problem connection: LastReqTime "2011-09-14 08:56:50.015" (a few weeks ago now!) and ReqType "Open" From sa_locks on that connection: lock_class "Schema", lock_duration "Transaction", lock_type "Shared" on vrious tables.

All I can think of is going back to our previous EBF because with 42 databases it's a problem when we want to alter a table that is blocked by the connection we cannot drop.

Any help appreciated. Regards, Adrian.

[jeff_albion]

From sa_conn_info for the problem connection: LastReqTime "2011-09-14 08:56:50.015" (a few weeks ago now!) and ReqType "Open" From sa_locks on that connection: lock_class "Schema", lock_duration "Transaction", lock_type "Shared" on vrious tables.

For the problem connection, what was the connection number provided? Was it a 'client' connection or an 'internal' server connection? (Internal connection numbers are much higher than normal connection numbers - somewhere in the 1000000s).

I mentioned in a previous question that there was a known issue with internal connections being left behind due to a server bug (that could no longer be dropped / cancelled), but these issues should be fixed by the latest patch that you have just installed and are now seeing issues with.

Can you reproduce this situation reliably? How often have you seen this situation now occur in your environment?

[Former Member]

Hi,

The problem connection number is 663480 on the particular server that I am looking at.

I can also see 3 other large numbers when I run sa_conn_info of 1019080042, 1019080041 and 1019080039 which have a ReqType of "unknown (0)" CommLink "NA" NodeAddr "NA". These numbers cannot be dropped either.

I haven't tried to re-create the problem but we have 42 databases and 23 of them are currently showing connections with an old LastReqTime that the -ti 600 normally cleared up after 10 hours inactivity and I can no longer drop.

If I kill the database then the problem may go away for a couple of days but then we get a new one on the database - we never get more than one 'client' connection number with an issue.

The database is running on a Windows 2003 R2 x64 Edition SP2. The client apps are developed in Delphi 7 and use NativeDB to connect and have been running ok for several years. We store the name of the executable within the "ConnectionName" so we can see that various apps are causing it along with various different "Last Statements" from various terminal servers so it doesn't look like one app or particular sql or a rogue client server.

I have noticed that some of the apps use proxy tables so I am wondering now if they are not disconnecting cleanly resulting in an issue on the new EBF. I will let you know if that lets me re-create the problem

Hope that helps and thanks for looking.

[VolkerBarth]

As to the "high connection numbers" - could they be related to events (cf. "CREATE EVENT ...") that are still running? - I guess they aren't influenced by the -ti setting.

For the proxy connections: Have you tried to use the following:

ALTER SERVER mySrv CONNECTION CLOSE myConnId

[Former Member]

Hi,

Having checked the source code for the apps the hanging sessions are not proxy connections and we don't use the CREATE EVENT command anywhere unless something internal to Sybase is generating those?

Thanks, Adrian.

[VolkerBarth]

AFAIK, the "maintenance plans" facility uses events to backup/validate the database, and you can create those plans with the help of Sybase Central. You might just query the following system table to find out whether there are any events defined:

select * from sysevent

[Former Member]

Various daemons that run in the server that perform maintenance tasks require a (temporary) connection, and these connections are assigned connection IDs with "high connection numbers". In version 10 software there isn't a way to tell what each temporary connection is for; this was improved in 11.0 so that it is much easier to know what's going on. So, in 10.x you see the "Other user" message with locking issues with these temporary connections.

Being unable to ALTER a table due to long-running temporary connections certainly sounds like a problem to me worthy of further investigation.

Do you have a support plan?

[former_SQLA_member1694874]

We are having a similar issue with the same EBF, are there any fixes due in the next EBF for v10 that might solve the problem without the need to raise a support case ? Obviously I understand you can't guarantee it's the same issue if there is but it might save some time if we can just try the next EBF.

Also do you have any idea when the next EBF is due for v10 ?

[Former Member]

It's unclear what's causing the problem; hence I hesitate to recommend trying the most recent 12.0.1 EBF. Do you have a repro?

[former_SQLA_member1694874]

Sorry but if we knew what was causing the problem we could at least try to work around it, it's only started since we applied the EBF mentioned above and only stopping the database clears the connections which is a real problem for us.

If you're unsure of the cause are there any issues fixed by the next EBF that at least have the same symptoms ?

Can you give an idea of when the next EBF will be released for v10 ?

[Former Member]

Hi,

It was happening every day across any number of our 40 databases, different programs, different scenarios. I've now rolled back to a previous EBF and everything has returned to normal. Something must have been introduced between EBF 3960 (the stable version we were back on) and 4239 where the problem appeared. I could go forward an EBF at a time to see where it starts but that will take time and not resolve anything so I'll wait to see if any subsequent EBF mentions a fix for it. The version we are on is stable and can stay there while a fix appears.

Regards, Adrian.

[Former Member]

Hi Glenn,

Sadly our support never got sorted when we upgraded to v10, the only time we have needed support was last year when we logged a call and our account manager managed to get it sorted for us but the support contract never got sorted out.

Normally the product is very reliable and of course requires almost zero effort to maintain so it has never been an issue for us because it just works out of the box - the main reason we like it so much because we are such a small team and don't want to spend hours doing pointless DB admin 🙂

I've posted a reply to Jeff saying that we have rolled back o 3960, the last stable version we were on and after a week I can easily confirm that the problem does not exist back on that EBF. On 4239 it was happening daily on any one of our 40 databases, seeminglyat random and via any program. I suspect it was down to our windows terminal server session setup terminating after 3 hours idle time (we have -ti 600 on the server to keep it connected 10 hours before releasing) which sometimes screwed it in the database requiring a kill of the database in order to close it down and get rid of the connection since a clean stop of the service just didn't respond after hours of waiting.

We are comfortable on 3960, the app is a tried and tested one that we keep ticking over and there's no plans to jump to v12/13/14 etc on this. However we will be looking at the whole system in the next 6 months with a view to implementing a whole new ERP system across the company and I still see ASA as a strong contender for it's price, performance, footprint, ease of use etc etc.

I'll keep checking the EBF's for a fix in the future but in the meantime I think we'll just stick where we are and not rock the boat again!

Thanks, Adrian.

[Breck_Carter]

Rolling back... been there, done that... FWIW, unless you see something in a later V10 EBF that you really need, you should stay where you are. The reason is, V10 is pretty old, and it would probably be a much better idea to upgrade to V12 than to wrestle with V10 EBFs. My two cents, anyway.

[Breck_Carter]

You should more than hesitate, since the OP's on V10 🙂

[jeff_albion]

There is a known issue (CR #702733 - 11.0.1.2786, 12.0.1.3713) which allows connections to be 'undroppable' in rare circumstances (usually involving a parallelized query that makes use of a HashFilter). Unfortunately, this fix has not been backported to the 10.0.1 codeline due to 10.0.1's "Archived" or "End-of-Life" status. Setting the connection option 'MAX_QUERY_TASKS' to '1' or restarting the database server is a workaround for the issue in 10.0.1.

For more information about this issue, see my response to this question here: http://sqlanywhere-forum.sap.com/questions/6164/connections-that-cannot-be-dropped/6197