MySQL Server Forum - trigger to run an external program

Author

trigger to run an external program

My SQL

2005-11-13, 7:23 am

Hi

Can I trigger an external program to run when a new record is added to
the mysql table?

The external program is in C and instead of scanning the table
continuously for new insertions, it will be better if an external
program could be triggered.

Any suggestions?

Bill Karwin

2005-11-13, 8:23 pm

My SQL wrote:
> Hi
>
> Can I trigger an external program to run when a new record is added to
> the mysql table?

In theory yes, you can write a UDF to do something like this, and call
that UDF from a trigger, but that's not an advisable solution, IMO.

UDF's that try to change state outside the DBMS (e.g. writing files,
starting processes, sending notifications, etc.) are risky. They may
take an unbounded amount of time to execute. Or they may be a security
vulnerability. Or they may have a bug that makes them crash, which
would bring down your MySQL server.

> The external program is in C and instead of scanning the table
> continuously for new insertions, it will be better if an external
> program could be triggered.

I think it's better to have another process monitor the data for
changes. If you really have a requirement that this be close to
real-time, you need to do continual scanning.

You might consider defining a special table to store the "flag" data
that a change has occurred, and on which record, etc. Then the external
process monitors only that one table. Once the external process has
accounted for a given change, it deletes the flag from that table.

Perhaps you could use the CSV storage engine to make it easier for that
external process to perform the monitoring without burdening the MySQL
engine.
See http://dev.mysql.com/doc/refman/5.0...age-engine.html

Regards,
Bill K.

Gordon Burditt

2005-11-13, 8:23 pm

>Can I trigger an external program to run when a new record is added to
>the mysql table?

This is awkward, and tends to have problems if the external program
can fail.

>The external program is in C and instead of scanning the table
>continuously for new insertions, it will be better if an external
>program could be triggered.
>
>Any suggestions?

One approach is to have a new table: work_to_do. A trigger inserts
a record into this table with a date stamp and identification of
the record that changed (and perhaps WHAT changed or the old value
of the field) along with a "job number". You have a process look
at work_to_do occasionally and process the changes. Since there's
a date in there, you can do the changes in order.

If the external program succeeds, delete the record from work_to_do
(by "job number"). Note that you could have several changes for
the same record before the external program gets to it, or perhaps
while the external program is handling some of the changes, another
one comes in. If the external program *FAILS*, leave the record
in work_to_do. Whether you proceed with independent changes after
a failed change is up to you.

This sort of thing is common when you've got a central billing
system and a distributed mail system, so when a mail account is
created, the mailbox has to be created on one of the mail servers.
If the mail server is down for maintenance at the moment, it's
important that the mailbox creation be done eventually.

Gordon L. Burditt

My SQL

2005-11-14, 1:23 pm

Thanks Bill

I got to know the risks involved. Will this continuous reading cause
any serious delays to the other queries being executed?

Thanks again

Bill Karwin

2005-11-14, 1:23 pm

My SQL wrote:
> I got to know the risks involved. Will this continuous reading cause
> any serious delays to the other queries being executed?

Probably not serious delays. I can't answer for certain, because I
don't know your system. The answer depends on many things, including
your database structure, the queries used on it by both readers and
writers, and also your system hardware, etc.

The only way to answer for certain is for you to implement it and test
with the monitoring application on and off.

Regards,
Bill K.

Sponsored Links