SEO

#1 (**permalink**) 02-28-2008, 07:35 AM

Is it possible to execute an perl script after a record is added to
mydatabase.mytable on mysql 5.

I want to be able to do some processing after database has been updated.

TIA
Kees.

#2 (**permalink**) 02-28-2008, 07:35 AM

kees hessels wrote:
> Is it possible to execute an perl script after a record is added to
> mydatabase.mytable on mysql 5.

This is highly inadvisable to run external processes from a trigger or
stored procedure.

- External processes may run in an indefinite amount of time, and the
server will wait for it, potentially locking up access to tables for
other users.
- External processes can crash or have other faults, which may endanger
the stability of mysqld.
- The processes run as the user id of the mysqld process, which may
represent a security breach.
- Work done by a trigger may be rolled back (if you use InnoDB tables
and explicit transactions), but any work done by the external process
won't be rolled back in that case. This can cause confusion and
erroneous results.
- It's more complicated to log and debug such convolutions in workflow.

> I want to be able to do some processing after database has been updated.

I would recommend doing that processing in the application that executed
the update. Test for successful completion of the update statement,
commit the transaction, and then do the additional work. You can even
spawn the Perl script process from your application. Don't do it from a
trigger.

Regards,
Bill K.

#3 (**permalink**) 02-28-2008, 07:35 AM

Thanks Bill for the explenation, i'll do it from the calling script as you
suggested, i thought to make an application real db driven one should be
able to fire external processes from triggers, but i stand corrected.
Thanks,
Kees.

"Bill Karwin" <bill@karwin.com> wrote in message
news:e1h16r02nv0@enews1.newsguy.com...
> kees hessels wrote:
>> Is it possible to execute an perl script after a record is added to
>> mydatabase.mytable on mysql 5.
>
> This is highly inadvisable to run external processes from a trigger or
> stored procedure.
>
> - External processes may run in an indefinite amount of time, and the
> server will wait for it, potentially locking up access to tables for other
> users.
> - External processes can crash or have other faults, which may endanger
> the stability of mysqld.
> - The processes run as the user id of the mysqld process, which may
> represent a security breach.
> - Work done by a trigger may be rolled back (if you use InnoDB tables and
> explicit transactions), but any work done by the external process won't be
> rolled back in that case. This can cause confusion and erroneous results.
> - It's more complicated to log and debug such convolutions in workflow.
>
>> I want to be able to do some processing after database has been updated.
>
> I would recommend doing that processing in the application that executed
> the update. Test for successful completion of the update statement,
> commit the transaction, and then do the additional work. You can even
> spawn the Perl script process from your application. Don't do it from a
> trigger.
>
> Regards,
> Bill K.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

calling perl script from mysql trigger