Can MySQL start/fire an external process? - JDBC JAVA

This is a discussion on Can MySQL start/fire an external process? - JDBC JAVA ; Hi, I have an application that dumps data into a database. The data is time-sensitive. Meaning that I need a client to know (within a few milliseconds) when a new record has been added to a table. What's the best ...

+ Reply to Thread
Results 1 to 10 of 10

Can MySQL start/fire an external process?

  1. Default Can MySQL start/fire an external process?

    Hi,

    I have an application that dumps data into a database. The data is
    time-sensitive. Meaning that I need a client to know (within a few
    milliseconds) when a new record has been added to a table.

    What's the best way to do this? Can I set something up in MySQL that
    will start a process (run an executable) any time a new record is added
    to a table?

    Or, if that won't work. What if I have a process running and connected
    to the database (I would write the program in C or in Java). Is there
    some way to have a callback called any time a new record is added to a
    database?

    THANK YOU!
    John


  2. Default Re: Can MySQL start/fire an external process?

    johnfofawn@hotmail.com writes:

    > Hi,
    >
    >
    > Or, if that won't work. What if I have a process running and connected
    > to the database (I would write the program in C or in Java). Is there
    > some way to have a callback called any time a new record is added to a
    > database?
    >


    I think I saw that version 5.0 and up have triggers, so you could set a
    trigger that would write a value to a table when a record in some other table
    is added, then your app could pole that record and see if it indicates that
    something has happened.

    Sounds like a database based semaphore, no?

    Dave in Largo, FL

  3. Default Re: Can MySQL start/fire an external process?

    david@adboyd.com (J. David Boyd) writes:

    > is added, then your app could pole that record and see if it indicates that


    Oops. poll, not pole!

  4. Default Re: Can MySQL start/fire an external process?

    Thanks for your reply. I don't see how the trigger would help in this
    case. If my app still has to poll, it could poll the modified table
    just as easy as it could poll the table that is modified by a trigger.
    Right?

    Is there a way to make a trigger fire some sort of external event?

    Thanks,
    John


  5. Default Re: Can MySQL start/fire an external process?

    johnfofawn@hotmail.com wrote:
    > I have an application that dumps data into a database. The data is
    > time-sensitive. Meaning that I need a client to know (within a few
    > milliseconds) when a new record has been added to a table.
    >
    > What's the best way to do this?


    My recommendation would be that the application that posts the record to
    the database also notify other clients that new data has been posted.
    Then they can go fetch the new data from the database on their own. But
    at least they can sleep in the meantime.

    You can use a variety of publish/subscribe messaging technologies to do
    this in close to real time. Which one is best may depend on the clients
    involved. For instance, if your applications are written in Java, JMS
    may be a solution: http://java.sun.com/products/jms/tutorial/

    Making the other clients poll the database is a costly and inefficient
    way to do it.

    Regards,
    Bill K.

  6. Default Re: Can MySQL start/fire an external process?

    johnfofawn@hotmail.com writes:

    > Thanks for your reply. I don't see how the trigger would help in this
    > case. If my app still has to poll, it could poll the modified table
    > just as easy as it could poll the table that is modified by a trigger.
    > Right?
    >
    > Is there a way to make a trigger fire some sort of external event?
    >
    > Thanks,
    > John



    Look at http://dev.mysql.com/doc/mysql/en/using-triggers.html for the up to
    date info on MySQL triggers. I see no way to call an external event.

    Dave

  7. Default Re: Can MySQL start/fire an external process?

    J. David Boyd wrote:
    > johnfofawn@hotmail.com writes:
    >
    >
    >>Thanks for your reply. I don't see how the trigger would help in this
    >>case. If my app still has to poll, it could poll the modified table
    >>just as easy as it could poll the table that is modified by a trigger.
    >>Right?
    >>
    >>Is there a way to make a trigger fire some sort of external event?
    >>
    >>Thanks,
    >>John

    >
    >
    >
    > Look at http://dev.mysql.com/doc/mysql/en/using-triggers.html for the up to
    > date info on MySQL triggers. I see no way to call an external event.
    >
    > Dave


    Dave,

    You can do pretty much anything you want in a UDF (make a socket call,
    run some Java code using jUDF, exec() an external process, etc), which
    you can call from a trigger:

    http://dev.mysql.com/doc/mysql/en/adding-udf.html

    -Mark

    --
    Mark Matthews
    MySQL AB, Software Development Manager - Connectivity
    www.mysql.com

  8. Default Re: Can MySQL start/fire an external process?

    Mark Matthews wrote:
    > You can do pretty much anything you want in a UDF (make a socket call,
    > run some Java code using jUDF, exec() an external process, etc), which
    > you can call from a trigger:


    I would not recommend doing anything in a UDF that could execute in
    unbounded time, perform I/O, access other libraries, etc.
    Doing so can result in:
    - security vulnerabilities, because the UDF runs as the user id of the
    DBMS server
    - DBMS server hanging if your UDF takes a long time to return
    - DBMS server crashing if your UDF has a bug, or fails to handle
    exception cases

    UDFs are intended for very short-duration context-free operations,
    taking input parameters and giving output based solely on that input.
    I would not recommend designing a UDF to read or write any state outside
    the scope of the UDF.

    Regards,
    Bill K.

  9. Default Re: Can MySQL start/fire an external process?

    Bill Karwin wrote:
    > Mark Matthews wrote:
    >
    >>You can do pretty much anything you want in a UDF (make a socket call,
    >>run some Java code using jUDF, exec() an external process, etc), which
    >>you can call from a trigger:

    >
    >
    > I would not recommend doing anything in a UDF that could execute in
    > unbounded time, perform I/O, access other libraries, etc.
    > Doing so can result in:
    > - security vulnerabilities, because the UDF runs as the user id of the
    > DBMS server
    > - DBMS server hanging if your UDF takes a long time to return
    > - DBMS server crashing if your UDF has a bug, or fails to handle
    > exception cases


    Bill,

    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.

    > UDFs are intended for very short-duration context-free operations,
    > taking input parameters and giving output based solely on that input.
    > I would not recommend designing a UDF to read or write any state outside
    > the scope of the UDF.


    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.

    -Mark
    --
    Mark Matthews
    MySQL AB, Software Development Manager - Connectivity
    www.mysql.com

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

+ Reply to Thread

Similar Threads

  1. Replies: 2
    Last Post: 08-30-2007, 08:11 PM
  2. Replies: 1
    Last Post: 04-17-2006, 04:11 PM
  3. Start process and kill process in Windows
    By Application Development in forum REXX
    Replies: 13
    Last Post: 11-01-2005, 08:50 AM
  4. Process.Exited event does not fire for foreign processes
    By Application Development in forum DOTNET
    Replies: 8
    Last Post: 05-03-2005, 02:22 PM
  5. Replies: 0
    Last Post: 08-01-2004, 04:49 PM