1

Our customer is running a Single Master with multiple slaves(in a cluster).

Now, he wants us to write some triggers (only) that run on the slave(s) when new records are added, updated or deleted on the slave(s). This trigger should run an external java program.

Is this possible ? recommended ?

How do we run a java program from the trigger ? Is it recommended/safe ?

Any gotchas that need to be taken care of ?

anjanb
  • 12,999
  • 18
  • 77
  • 106
  • MySQL does not support Java as part of a trigger or to be run inside the server. –  Oct 21 '11 at 10:18
  • we want the trigger to run an external program on the Server running the MYSQL Slave server. We don't want to necessarily run the program inside the MYSQL server. – anjanb Oct 21 '11 at 10:23
  • Calling external programs from inside a database server is a security hole and not a great idea. – Johan Oct 21 '11 at 10:54
  • @Johan - When you know what you're doing, you own the server and are the only user of the server and if the specification requires it - it's a good idea. – N.B. Oct 21 '11 at 12:19

1 Answers1

1

Yes, it's possible. You need to create those triggers on slaves, they'll be triggered as binlog is read and executed.

You can execute an external program using sys_exec UDF available from MySQL Forge (or this link).

SELECT sys_exec('/path/to/program')

Just beware of what the program does, until the external program returns - query won't finish. If it's something that does further processing, I suggest creating some sort of a queueing system listening on a port so you can immediately exit(0), let the program do its stuff and release mysql thread so the query can finish.

N.B.
  • 13,688
  • 3
  • 45
  • 55