Using Perform Script On Server

[jjjjp]

I oversee a database at my university that I developed several years ago. Last year our university stopped permitting Send Mail via SMTP if the script was being invoked from a client.  I could now invoke SMTP only from a recognized server within the university. I was able to save the functionality for scheduled scripts, but I lost a lot of functionality on the client side. I can still send emails using the client's email app, but some of the database's client-side email tasks are much less cumbersome if they can be performed using Send via SMTP.

I am working right now on recovering that functionality through Perform Script on Server (PSOS). I can successfully send an email via SMTP using PSOS in a very simple test, but every time I try to test PSOS in my database using more complicated examples, the Server just freezes up as does the FM client. Sometimes force quitting the FM client has cause server to unfreeze, but sometimes FM Server remains indefinitely frozen. I then need to get my IT support to restart FM Server.

I have never had FM freeze up before and have never had to instruct IT support to restart FM Server.

Maybe I should give up on using PSOS. I have recently found posts on forums warning other users to stay away from PSOS. But I am wondering wether there is a safe way to make it work without the continual risk of freezing. My plan is to ensure that the script invoked by PSOS contains only one script step, Send Mail (Via SMTP Server).

Some questions for the forum:

1. Should I simply give up on PSOS?
2. If I keep trying, how do I prevent FileMaker Server from freezing?
   • Is there a way to put a time limit in Perform Script on Server when invoked through a client?
   • Can I configure the admin console to prevent the kind of freezing that I am seeing?
3. Is using Perform Script on Server too high risk? I will try to limit such scripts to just the email command using arguments supplied through script parameters rather than directly accessing fields in the database. 
4. If I get single command scripts to work for basic emails, can you confirm that adding attachments will be supported if the file is stored in a container?
5. Is gmail smtp a viable (free) option in FM with a regular gmail account?

Thanks in advance,

Jerry

Edited August 8, 2023 by jjjjp

[Ocean West]

For starters I would give up on SMTP as that protocol in a lot of places is being deprecated and use an API, I think Outlook and Google are on the forefront to push you this direction. 

In fact I would still use PSOS but use a provider such as Mandrill, SendGrid, SparkPost or MailGun, or AWS SES, to send emails thru their API or even thru their SMTP forwarding as these can still do so.

You can search for examples for these services and FileMaker. 

If the email has to come from that users email account that is more complex, and definitely should require api keys.

The latest version of FMP and FMS 2023 has the ability to use Google or MS via OAuth 2.0  

Quote

FileMaker now supports Open SSL 3.0, Send Mail with OAuth 2.0 authentication,

As far as the server goes it depends on its configuration and if the hardware is adequate for the number of users and the complexity of the system. Check the logs to see what it's causing it to hang, freezing seems, hard to diagnose with out delving into the logs and knowing more about the capacity and environment.

 

[jjjjp]

Sorry, but I don't see why I would continue to rely on the PSOS script step while changing my email platform. It is PSOS that appears to be causing me all of my current problems. The script that I invoke so far freezes at the drop of a hat. My problem isn't with my university's SMTP server performing unreliably. My overnight scheduled scripts all perform fine now that the SMTP settings in WCONLINE are configured properly. The limitation as of a year ago is that the SMTP functionality works only if the script is being sent from a university server. So I can't invoke the Send Mail via SMTP step from a script on the client side. PSOS seemed to offer a way around the limitation. 

Another issue with switching email platforms is cost. I am guessing most or all of these services are not free. Right now I have no budget allotted to this program except to cover the annual FM Server fee.

I have FM and FM Server 19. I could upgrade to 20. I have followed the PSOS link and see something about Perform Script On Server with Callback. The description says that "performs a script on the server that is hosting the current file without freezing." Should I be relying instead on this new script step? And how does it prevent freezing? Can one set a time limit as one can when manually running a script from FM Server?

Happy to have your further comments, Ocean West. Or anyone else with experience on PSOS, please feel free to weigh in.

[Ocean West]

The PSOS script on call back is no different then the normal PSOS without the "wait for completion" and you would specify another script to run that displays a dialog or some other message that the script completed. It just gives the user feedback the result of the task (if you pass back the results) The notification script is like an onWindowTimer trigger it will run when the server sends the signal. 

If I understand, the SMTP via client side doesn't work if the user is not on campus probably because they are not in the network and there is most likely relay restrictions. The Server it self is on campus so it can send smtp server. 

I use Sparkpost ( now MessageBird ) 50k emails for $20bucks a month. 

[jjjjp]

The SMTP client can't even use SMTP from the client side via the standard Send Mail script step. Only authorized IP address are accepted. My IT support person had to apply to get the server that hosts FM Server accepted onto the university's list of approved IP addresses. What this all means that without PSOS, I can only use Send Mail via SMTP from scheduled scripts.

PSOS would seem to offer a way around the restriction, if only it didn't freeze at almost every opportunity. The description of PSOS  with Callback refers to "performing a script on the server that is hosting the current file without freezing." How does it do so? Are you saying that if I use the normal PSOS and unselect "wait for completion," I will stop the admin console and FM Server itself from freezing? I'm not sure if this is my experience, but it is too costly right now in terms of time lost to keep running tests. I need to know how to stop the freezing from occurring. If I am unlikely to succeed, please let me know.

Paid email platforms are not really an option for me.

 

[comment]

27 minutes ago, jjjjp said:

The description of PSOS  with Callback refers to "performing a script on the server that is hosting the current file without freezing."

No, it refers to "performing a script on the server that is hosting the current file without freezing the client". And I believe that's not the same kind of "freezing" you describe.

I don't know why your server freezes. It shouldn't - and I am not aware of any inherent problems with PSOS.  I would disregard "posts on forums warning other users to stay away from PSOS". You will find posts on forums warning you to stay away from just about anything. It always starts with someone not knowing what they're doing, and ending up blaming some feature or other instead of their own incompetence.

The first thing I would check is the server's OS compatibility with your version of FMS. 

[jjjjp]

Thank you for this response. I believe it was you who helped me successfully navigate some (for me) very challenging design/coding issues when I was designing this database years ago. It is very useful, then, to hear from you that it is highly unlikely PSOS is the source of the freezing problems. I will proceed on that basis.

I'm sure you would agree, based on what I have just pulled from my console (see attached), that compatibility must in fact be an issue, if not the issue? My FM Server version is 19.3.2.203. The OS is Microsoft Windows Server 2012 R2 Standard, which is, I gather, very old. I'm not sure whether the current hardware will even support what Claris page appears to list as the minimum FM Server requirement: Windows Server 2019 Datacenter (with Desktop Experience). So it may end up being both a hardware and OS software issue.

I will raise the incompatibility issue with my university's IT support.

Note that to unfreeze the server today,  IT support said they doubled my memory and CPU. Is it possible that space is also part of the problems I have been having? I had issues in the spring that manifested differently, and IT bumped the specs up back then. So today is the second bump. From a May email from my IT support in response to problems connecting to my database:  "the server is running on 1 vCPU and 4 GB RAM which is too low in my opinion. I think the minimum should be 2 vCPU and 6 GB RAM.1 vCPU and 4 GB RAM which is too low in my opinion. I think the minimum should be 2 vCPU and 6 GB RAM." It appears now that I am at 8 GB. My FM database is 30.4 MB. Because I can no longer test SMTP-dependent scripts from the client but must do so from the server, I currently host two similarly sized versions of the database on FM Server at the same time. I don't know how much RAM I must have for each MB of database. FM Server itself (about 55 MB?) will of course eat up a lot of the CPU's memory.

Let me know if you can whether you think memory and CPU were and are still part of the issue, or whether I should should be focusing on compatibility. (I haven't yet had a chance to test whether the problem with PSOS has recurred with the doubling of memory and CPU.)

 

Edited August 9, 2023 by jjjjp

[Ocean West]

Good job getting upgraded I would say the bare minimum is 3 or 4 cpus and 4 - 8 gb on 19v. I have a few clients on this config, but have less then 6 users at the most on line and are doing widely different things. Depends on the nature of your solution and the construction of the scripts.

How many active users do you have - is your email PSOS a one off or is it looping?

 if you look at the error logs on the server are you seeing anything error like?

1501	SMTP authentication failed
1502	Connection refused by SMTP server
1503	Error with SSL
1504	SMTP server requires the connection to be encrypted
1505	Specified authentication is not supported by SMTP server
1506	Email message(s) could not be sent successfully
1507	Unable to log in to the SMTP server

I too have seen long delays on PSOS because the SMTP server takes a long time to respond. "feels hung" the script doesn't finish until the smtp connection closes and returns results to the script. 

If you the user doesn't need immediate notification you could  queue the messages and run them in batch later find all emails that need to be sent loop and send them and perhaps email yourself or an admin any failures. 

If you are planning on upgrading to v20 then perhaps you IT could deploy a Ubuntu box it may be more performant and not require windows license you could then use these funds for an email api service - ( linux depends on if your doing any server side PDF generations - fonts, and use of any plugins you are using )

I assume you're getting free hosting for the campus? Perhaps compare costs with a dedicated hosting company that would work and not rely on university infrastructure? 

 

There is a beautiful technique called publish and subscribe or a queue system, https://simpleq.proofgeist.com

in this method you could send a message to this db and then it would on schedule send the email script on server. 

[comment]

You can check your configuration against the official requirements:
https://support.claris.com/s/article/FileMaker-Server-operating-system-requirements-all-versions-1503692927810?language=en_US

I'm afraid that's the limit of my usefulness here; I know practically nothing about deployment.

 

[jjjjp]

Thanks, both of you. 

Ocean West, your advice re minimum requirements is very helpful to have. To answer your questions:

1. It is very rare that there is more than one active user at a time. Most of the time the database is idle. My colleagues and I would be hard-pressed to do without the database. It fulfills may key functions. But it is the opposite of user-intensive. Its function is primarily administrative, and I do the lion's share of the administering.

2. Re the error logs, if I am looking in the right place, I see nothing.

The viewer:

The settings:

Should I be adjusting the settings? (I am not that conversant with the FM server side.)

3. Re the cost of hosting, I think my college pays central IT something each year, and has been fine with the current fee, but it will be too complicated to think about hosting elsewhere. This is not to mention my university's vigilance about security. Running the database is a very small part of my job. Over the years, I have put a lot of work into doing the programming, but at this point I need to be focussed on getting it back to running smoothly in the background and as much as possible not to disturb the current setup. So I'd like to work with the support network that I now have. I hope my IT support will be able to upgrade as necessary. They seem willing to so far, but I don't know whether upgrading the OS will require relocating to a more up-date Windows server and if that will incur more cost still.

Are you at his point in a position, Ocean West, to weigh in on how big a contributor our current OS server software, Microsoft Windows Server 2012 R2 Standard, might be to the issues I am experiencing? Again, Claris lists Windows Server 2019 Datacenter (with Desktop Experience) at the very bottom in its list of compatible OSes. Windows Server 2012 is not bene close.

Very good idea to make sure I don't wait for completion in the calling PSOS script and leave error handling (diagnostic email sent out by email) to the the script that is being executed on the server. I was planning to wait for completion of the script, which returns an error code which the calling script then logs in the database.

But an important question: If I initiate something like 35 (typical number) of the same script in a loop on the server side, each iteration basically tasked with sending an email via SMTP, will the 35 tasks be executed on the server in parallel or in series?

If in parallel, then I guess I could be asking for trouble, which is probably why you are suggesting I subscribe to a queue system. But I am reluctant to start introducing new software. I'd like to stay if possible within the native FM universe, if only not to over-complicate the operation of the database.

If, as is already quite clear, the freezing issue has to do with the current system specs and/or the current OS, then it might not be such a problem, once these problems are addressed, to have the calling script wait for completion. Wouldn't the execution of a Send Mail via SMTP command ordinarily be fairly quick? 

I will pass on the information about the Ubuntu box. I would like to upgrade to FM Server and FM 20 at some point in the near future. But again, my ideal is to remain within the native FM system.

 

 

[Ocean West]

Click Select and choose a log to view and download you may need to view it in an editor or it is easiest to drag it on to FM icon to create a database so you can sort and search as needed. Typically the Event.log or ScriptErrors.log - see below

Also there is the function you can add to your script https://help.claris.com/en/pro-help/content/set-error-logging.html to log errors. 

Each PSOS is one "user session" that has to startup and run the logic. Do you have a startup / shutdown scripts that does a ton of stuff just to setup for the user experience?  Typically I have a custom function that isServer in my startup/shutdown script to do the bare minimum before the target script runs. You could be doing a lot of tasks that are slow and not necessary.  If the PSOS goes to layout does a find and loops and sends mail it is one session. 

Another approach is to flag the record "readyToSend" and you just mark records then a scheduled server script can do the fine loop and send if it doesn't find any records it just aborts. 

FYI the SimpleQ is all native FileMaker.

The freeze may not be FileMaker but may be the university email server, it could be slow to handshake or may throttle depending on how many connections it's getting from same user.

 

 

[jjjjp]

Am able to get back to this issue once again. Thanks for all your help so far.

Quote

Also there is the function you can add to your script https://help.claris.com/en/pro-help/content/set-error-logging.html to log errors. 

I added a Set Error Logging [ On ] step at the top of the first script that opens in my database.  Presumably the setting remains in place as long as the database is running, and so I will start to see log files.

Quote

Do you have a startup / shutdown scripts that does a ton of stuff just to setup for the user experience? 

For the PSOS that I'm thinking of using, I plan to reduce the command to the bare minimum: (1) separate out the script parameters; (2) invoke Send Mail via SMTP using those same script parameters; (3) pass the error, if any, back  to the calling PSOS script. I would include step (3) only if I decide to wait for completion. I will probably try that first and see if it takes too long. But I will do so only after asking my tech support to address any hardware/CPU/Windows Server OS issues.

Quote

The freeze may not be FileMaker but may be the university email server, it could be slow to handshake or may throttle depending on how many connections it's getting from same user.

Back before sending mail via SMTP from the client became impossible, I used to send in series up to 40 emails in a script one after another, with lots of book-keeping in between. Everything happened very fast. It's pretty clear to me that the problem is with current setup on the server side. It just can't handle the current load, maybe, at least in part, because I am now running two databases rather than one when I am doing testing that involves server-side operations: the current version of my database and a test database of the same size. I used to be able to do all of my testing off server on my development version of the database.

The key observation is not that the client FM slows to a standstill. The key observation is that the admin console is no longer even accessible. I can't log back in. I must get my support team to restart. It is the server that is doing the freezing.

Just one point I am not completely clear about that I am hoping you can answer to the best of your knowledge: Should I be pressing my support staff to update the Windows Server OS version from 2012 to at least 2019? How much of a contributing factor do you expect the Server OS to be?

[Ocean West]

as far as latest support of OS yes I am baffled that they would continue with 2012 support , be aware usually there're is an increase in license support

 

[comment]

I must correct something I said earlier:

On 8/9/2023 at 12:21 AM, comment said:

The first thing I would check is the server's OS compatibility with your version of FMS. 

I have now noticed that the version you are using is not fully updated. In fact, no less than 10(!) updates were released after your current version of 19.3.2.

So actually the first thing I would do is update the FMS to 19.6.3. And then I would ask for the server's OS to be upgraded to one of the versions that were tested by Claris and declared compatible with the running version of FMS. 

Interestingly enough, one of these updates release notes lists:

Quote

• Various corner-case issues caused FileMaker Server to become unresponsive.

as one of the issues it addressed. I don't know if your case falls into this category, but again, these are the most basic troubleshooting steps that should be taken before anything else.

[jjjjp]

Thanks for these clear recommendations. I will pass them on to my IT support person along with Ocean West's earlier recommendations for memory and CPU.