Re: Can MySQL start/fire an external process?

Mark Matthews wrote: 
Yes those are limitations one has to be aware of. Some can be mitigated
depending on the language you write your UDF in. For example, you can
mitigate most security concerns by writing your UDF in a "managed"
language such as Java with the correct permissions set.

On the other hand, a Java exception that isn't caught and handled properly could cause mysqld to exit.

If the OP needs non-polling solutions, there's really no other way to
make database state changes immediately available outside of the MySQL
DBMS (and many others) without resorting to something such as a UDF
called from a trigger.

Then I respectfully suggest that one shouldn't use the DBMS to notify other clients.

I thought of another risk of using UDF's in triggers to notify clients: what if the event-raising UDF is invoked within a transaction? This could occur before the transaction is committed. The clients will receive notification, but if they query for the data, they won't find it (unless they're doing dirty-reads). Also, the transaction could be rolled back, so the new data will never appear. But the client has still been notified of the new data. In short, events do not respect transaction isolation.

IMHO, the safe and correct way to do this is for the app that posts the data to notify other apps _without_ using the DBMS as the event channel. Use JMS, for instance. Make sure the data has been posted successfully, and the transaction is committed before sending the message notifying other clients.

Anyway, there's more than one way to do it, and these are my opinions about how I'd do it if the task were given to me.

Regards,
Bill K.
.

Relevant Pages

  • Re: Does rolling a cluster create down time?
    ... What the clients see or how it affects their transactions depends solely on ... It must be cluster aware. ... be able to resubmit the current transaction as it will be rolled back on the ... When I roll the cluster from one node to ...
    (microsoft.public.sqlserver.clustering)
  • Re: Application logic and Business logic
    ... > and then export it to the database through occasional batches of SQL. ... the read-trip was specific to the transaction being attempted so that ... realized in all the posting programs, online and batch. ... You can't do that if all your logic is sprinkled across your clients. ...
    (comp.object)
  • Build data external datalink based on user
    ... to different external data sources. ... In short I have a transaction database with one transaction table tbltrans ... holding some 10 million records split between 15 clients. ...
    (microsoft.public.access.externaldata)
  • Re: SQL Code that is submitted to server changes when run on different clients
    ... both clients are running the same MDAC version.... ... > that in an external reference of some kind ?? ... there may be problems with references and TRIM ... Nevertheless you may as well remove the spaces in you UDF on the ...
    (microsoft.public.access.adp.sqlserver)
  • Job
    ... The Duties of the financial manager consists of reception of money resources from clients of our company and transferring them to us. ... For every transaction, we pay 8 % from the total sum. ...
    (soc.politics.marxism)