xp_startsmtp database not responding

[hansg]

Since upgrade from SQL Anywhere 10 to 16 (build 2043) we have problems with a database that does not response anymore. All clients that are connected are freezing. A new connection in ISQL freezes after a click on the "Connect" button. This happens with a few customers now.

With request log we saw that the problem is in the command xp_startsmtp(). I created a small test stored procedure that executed only the startsmpt. I could reproduce the problem with that test sp. But a month later I am not able to reproduce the error again with the same test sp. However, the customer was hang again.

It looks that the xp_startsmtp() is causing the problem but it seems more complicated than just calling xp_startsmtp(). It looks like a spam block or something...? That the problems occurs after 'somethings' happen..? The xp_startsmtp is called from a scanner (by ODBC). But the test sp was executed from the server.

Questions: - anybody how to fix this? - how is it possible that a xp_startsmtp can hold all clients?

[Former Member]

This does not sound like something I recognize.

Is it possible the database server process has asserted? Has the smtp protocol also changed during this upgrade? {encrypted?/secured?} Is this true of both the 64bit and 32bit servers? If running McAfee check your Host Intrustion Protection settings? If running a different Virus scanner, Spam blocker check those settings?

I cannot guarantee any of the above will lead to results or provide any better information about this but those are some things I could think of.

If nothing else shows up you may have to get a dump of the hung database servers, and open up a support incident so this can be investigated.

Another alternative would be to share your test script to see if someone else can mock this up.

And finally, maybe the use of process and/or network tracing may show something.

Best of luck.

[hansg]

I opened an incident. I could reproduce a look-a-like problem as follows: - create a login procedure as follows: begin SET OPTION WAIT_FOR_COMMIT = 'OFF' ; call sp_login_environment; end - Call start smtp with a wrong port from dbiqsl call xp_startsmtp('myemal@mycomp.com', 'mysmtpserver', 99)

This call will timeout in 20 seconds, in between click Window->New window in dbisql. A new dbisql will open but with a waiting cursor. As soon as the timeout is passed both dbisql are accessible again.

The same steps work fine in SA10.

When I remove the line 'SET OPTION WAIT_FOR_COMMIT = 'OFF' ;' in the login procedure the problem is over.

In SA16 i something changed that calling xp_startsmtp seems to block setting database options. Or something like that.

When I have more information from SAP I will update this post.

Kind Regards

[former_SQLA_member1694908]

I have the same problem, since i upgrade from version 9 to 16. Sometimes the xp_startsmtp() works fine, sometimes blocks and freezes the database server and all clients. Have you some news about this issue?

[Former Member]

This could be related to a recently fixed issue with versions 16 and 17 where a smtp server could fail to respond or respond correctly. The fix was identifed as 793866 and for version 16 the change was made in build# 2298. You may find some relief if you include a specific value for the "timeout" parameter.

FWIW, here's the CR note:

A call to the system procedures xp_startsmtp or xp_sendmail could have caused the database server to hang indefinitely if the SMTP server was not well-behaved. This has been fixed.

[VolkerBarth]

Hm, I'm puzzled: Yesterday you stated you were using xp_startsmtp()?

In case you are using xp_startmail() and therefore MAPI instead of SMTP, I would suggest to ask that as a new question.

[former_SQLA_member1694908]

I use build 2283 and i don't find the build 2298, the next is 2305. The xp_startsmtp() timeout parameter, if not provided, have the default value of 60secs, so in 60secs the sp should terminate. I my case the database server stop responding, and in some cases, if i stop the client application, starts responding again.

I add the dbsrv16 and dbeng16 to Symantec antivirus exceptions and now i looking for new incidents.

[VolkerBarth]

how can I solve this issue

So, now you've stated (on your other question) that you really do use xp_startsmtp() and are runnig version 16.0.0.2283, you might try the newest EBF which contains the fix Nick has mentioned...

[former_SQLA_member1694908]

i've tryed the #2305, but i had a bigger problem. The database server was go donw to many times (maybe in the same circumstances). So, i'm not cofortable to made that upgrade. I saw that are new builds, last one 2322. I will try this one

[VolkerBarth]

@Samuel: FYI: I turned your answers into comments, simply as they are not real answers. If you provide more information to your problem, you can use the "add new comment" button below Nick's answer - or for nested comments - use the "reply button" immediately below each comment.

[VolkerBarth]

From the other FAQ:

Could you show a snippet of your code to connect via MAPI SMTP and to send the mail, say, to show whether you check the return code for xp_startsmtp and the like?

Note that v16 has introduced the new xp_get_mail_error_text system procedure and xp_get_mail_error_code system procedure that could be of help here - in case you get a return code before the engine freezes...

Does the behaviour depend on the user/privileges trying to mail and/or the kind of recipient (part of your mail domain or not)?

[former_SQLA_member1694908]

today i upgraded to build 2322. after a few days, i will post about the behavior of the server

[former_SQLA_member1694908]

don't work!!! just now the server freezes (with version 16.0.0.2322). DNS problem??? I will change the exchange server for the ip address.

below the server log, the first line is before call the xp_startsmtp()

I. 09/23 15:10:58. before start mail - CRM_spNotificaPedidoCargaEmitente JMadureira
I. 09/23 15:12:05. Starting checkpoint of "SGInformatica" (SGInformatica.db) at Fri Sep 23 2016 15:12
I. 09/23 15:12:05. Finished checkpoint of "SGInformatica" (SGInformatica.db) at Fri Sep 23 2016 15:12
I. 09/23 15:13:17. Connection terminated abnormally; client socket shut down
I. 09/23 15:13:17. Disconnected TCPIP client's AppInfo: IP=192.168.78.27;HOST=PTCOM1001;OSUSER=PPinto;OS='Windows 8 Build 9200 ';EXE='\\\\ns-uf01\\app$\\Fapricela\\CRMSALES\\Versão 2\\FCore.exe';PID=0x1f94;THREAD=0x2bf0;VERSION=16.0.0.2283;API=OLEDB;TIMEZONEADJUSTMENT=60;APPINFO=FCORE
(...)
I. 09/23 15:20:29. Connection terminated abnormally; client socket shut down
I. 09/23 15:20:29. Disconnected TCPIP client's AppInfo: HOST=PCCOM713;OS=Windows Vista Build 7601 Service Pack 1;PID=0x1360;THREAD=0x1380;EXE=\\\\ns-uf01\\app$\\Fapricela\\CRMSALES\\Versão 2\\FCore.exe;VERSION=9.0.2.3951;API=OLEDB;TIMEZONEADJUSTMENT=60;APPINFO=FCORE
I. 09/23 15:20:29. Connection terminated abnormally; client socket shut down
I. 09/23 15:20:29. Disconnected TCPIP client's AppInfo: IP=192.168.69.77;HOST=PCCOM713;OSUSER=ECardoso;OS='Windows 7 Build 7601 Service Pack 1';EXE='\\\\ns-uf01\\app$\\Fapricela\\CRMSALES\\Versão 2\\FCore.exe';PID=0x1360;THREAD=0x1380;VERSION=16.0.0.2283;API=OLEDB;TIMEZONEADJUSTMENT=60;APPINFO=FCR_MSG_SRVC
I. 09/23 15:20:49. Database server shutdown requested via server console

[Former Member]

Sounds like something new that will need to be worked from wire traces and forced dumps. You should contact product support so that can be captured and analyzed in greater detail than can be identified from the current descriptions.

[former_SQLA_member1694908]

happened again, but this time I have changed de multiprogramming level to manual and activate the output info (the concurrency value don't stop grown until i stopp the server and the level reach the max value):

I. 09/27 17:09:17. Starting checkpoint of "Fapricela" (Fapricela.db) at Tue Sep 27 2016 17:09 
I. 09/27 17:09:18. Finished checkpoint of "Fapricela" (Fapricela.db) at Tue Sep 27 2016 17:09
I. 09/27 17:09:20. before start mail - CRM_spNotificaPedidoCargaEmitente JMadureira 
I. 09/27 17:10:26. MPL[curr=10 prev=10] concurrency[155] throughput[curr=0 prev=255] algorithm=0 
I. 09/27 17:10:26. Adjusting the multiprogramming level to 20 
I. 09/27 17:10:26. Starting checkpoint of "FapricelaUK" (FapricelaUK.db) at Tue Sep 27 2016 17:10 
I. 09/27 17:10:26. Finished checkpoint of "FapricelaUK" (FapricelaUK.db) at Tue Sep 27 2016 17:10 
I. 09/27 17:10:31. Connection terminated abnormally; client socket shut down 
I. 09/27 17:10:31. Disconnected TCPIP client's AppInfo: IP=192.168.69.106;HOST=PCCOM709;OSUSER=jmadureira;OS='Windows 7';EXE='FCore.exe';PID=0x11fc;THREAD=0x1468;VERSION=16.0.0.2283;API=OLEDB;TIMEZONEADJUSTMENT=60;APPINFO=FCORE
(...) 
I. 09/27 17:11:00. MPL[curr=20 prev=10] concurrency[178] throughput[curr=53 prev=0] algorithm=0 
I. 09/27 17:11:00. Adjusting the multiprogramming level to 26 
I. 09/27 17:11:19. Connection terminated abnormally; client socket shut down 
I. 09/27 17:11:19. Disconnected TCPIP client's AppInfo: IP=192.168.69.20;HOST=RDP-SRV02;OSUSER=CMaria;OS='Windows 2008R2';EXE='FCore.exe';PID=0x2484;THREAD=0x780;VERSION=16.0.0.2283;API=OLEDB;TIMEZONEADJUSTMENT=60;APPINFO=FCORE
(...) 
I. 09/27 17:11:34. MPL[curr=26 prev=20] concurrency[196] throughput[curr=13 prev=53] algorithm=0 
I. 09/27 17:11:34. Adjusting the multiprogramming level to 26 (...) 
I. 09/27 17:12:08. MPL[curr=26 prev=26] concurrency[211] throughput[curr=0 prev=13] algorithm=0 
I. 09/27 17:12:08. Adjusting the multiprogramming level to 36
(...) 
I. 09/27 17:13:16. MPL[curr=46 prev=36] concurrency[238] throughput[curr=12 prev=12] algorithm=1 
I. 09/27 17:13:16. Adjusting the multiprogramming level to 46 
I. 09/27 17:13:49. MPL[curr=46 prev=46] concurrency[248] throughput[curr=0 prev=12] algorithm=0 
I. 09/27 17:13:49. Adjusting the multiprogramming level to 56
(...) 
I. 09/27 17:14:23. MPL[curr=56 prev=46] concurrency[253] throughput[curr=15 prev=0] algorithm=0 
I. 09/27 17:14:23. Adjusting the multiprogramming level to 66 
I. 09/27 17:14:57. MPL[curr=66 prev=56] concurrency[250] throughput[curr=35 prev=15] algorithm=0 
I. 09/27 17:14:57. Adjusting the multiprogramming level to 87
I. 09/27 17:14:57. Starting checkpoint of "GDocFapUK" (GDocFapUK.db) at Tue Sep 27 2016 17:14
I. 09/27 17:14:57. Starting checkpoint of "GWIInvestments" (GWIInvestments.db) at Tue Sep 27 2016 17:14
I. 09/27 17:14:57. Starting checkpoint of "FapricelaFR" (FapricelaFR.db) at Tue Sep 27 2016 17:14
I. 09/27 17:14:58. Finished checkpoint of "GDocFapUK" (GDocFapUK.db) at Tue Sep 27 2016 17:14
(...)
I. 09/27 17:15:31. MPL[curr=87 prev=66] concurrency[238] throughput[curr=84 prev=35] algorithm=0
I. 09/27 17:15:31. Adjusting the multiprogramming level to 100
I. 09/27 17:15:31. Connection terminated abnormally
I. 09/27 17:15:31. Disconnected TCPIP client's AppInfo: IP=192.168.75.33;HOST=PCPROD700;OSUSER=POliveira;OS='Windows 7';EXE='FCore.exe';PID=0x1530;THREAD=0x10fc;VERSION=16.0.0.2283;API=OLEDB;TIMEZONEADJUSTMENT=60;APPINFO=FCR_MSG_SRVC
I. 09/27 17:16:05. MPL[curr=100 prev=87] concurrency[112] throughput[curr=1379 prev=84] algorithm=1
(...)
I. 09/27 17:16:39. MPL[curr=100 prev=100] concurrency[151] throughput[curr=0 prev=1379] algorithm=0
I. 09/27 17:17:13. MPL[curr=100 prev=100] concurrency[179] throughput[curr=0 prev=0] algorithm=0
I. 09/27 17:17:47. MPL[curr=100 prev=100] concurrency[185] throughput[curr=0 prev=0] algorithm=0
(...)
I. 09/27 17:18:21. MPL[curr=100 prev=100] concurrency[190] throughput[curr=0 prev=0] algorithm=0
I. 09/27 17:18:55. MPL[curr=100 prev=100] concurrency[193] throughput[curr=0 prev=0] algorithm=0

[Former Member]

It appears I was misinformed on final fixes for this. There will be another fix (identified as engineer 801195 and if you have a SAP customer id you can view the Note for this at https://launchpad.support.sap.com/#/notes/2356793 ).

This is actually another aspect of this problem and will be fixed in the next SP >= SQL Anywhere 16.0.0 build 2327.

In the meantime, you might want to drill into aspects of the SMTP server involved. To be exposed to these issues, you SMPT server needs to contributing in some way. You may benefit from someone wit expertise in network tracing to help drill into this more to see if there is something that can be done on that side.

[former_SQLA_member1694908]

when this upgrade will be available?