Execution of a Java program by a trigger.

I have a table T1 with a column named Flag. Whenever Flag changes, I want to run a trigger that results in an execution of a Java program.

Is this possible?

 

•  答案

Yes, using the Mysql User-Defined Function (see mysql 5.5 FAQ: Triggers) and installing the lib_mysqludf_sys

Then, for example, you can write your own trigger calling the sys_exec like this:

delimiter |
    CREATE TRIGGER testtrigger BEFORE UPDATE ON T1
     FOR EACH ROW BEGIN
      DECLARE result int(10);
      IF NEW.Flag <> OLD.Flag THEN  
        SET result = sys_exec("/path/to/javabin -jar your.jar");
        -- other kind of works and checks...
      END IF;
    END;
|

The result contains the exit code of the external program

There are other useful functions in this library:

• sys_eval : executes an arbitrary command, and returns it"s output.
• sys_get : gets the value of an environment variable
• sys_set : create an environment variable, or update the value of an existing environment variable
• sys_exec : executes an arbitrary command, and returns it"s exit code

More info here

Try it on a dev env and...

Be very careful in deciding whether you need this function. UDFs are available to all database users - you cannot grant EXECUTE privileges for them. As the commandstring passed to sys_exec can do pretty much everything, exposing the function poses a very real security hazard.

But anyway, I agree with the proposal of Remus Rusanu

来源 分享

创建 09 4月. 13 Cristian Porta

how can i install the lib_mysqludf_sys utility ?? – Ankit Kapoor 09 4月. 13

Take a look into "installation" section here: https://GitHub.com/mysqludf/lib_mysqludf_sys/blob/master/lib_mysqludf_sys.html ... but in general read the mysql udf manual – Cristian Porta 09 4月. 13

getting this error -----CREATE FUNCTION sys_exec RETURNS INT SONAME "lib_mysqludf_sys.so"; ERROR 1126 (HY000): Can"t open shared library "lib_mysqludf_sys.so" (errno: 0 /usr/lib/mysql/plugin/lib_mysqludf_sys.so: wrong ELF class: ELFCLASS32) – Ankit Kapoor 10 4月. 13

do you have a 32bit or 64bit operating system? – Cristian Porta 10 4月. 13

its 64 bit ubuntu 0.12... – Ankit Kapoor 10 4月. 13

Try recompiling for 64bit... modify your Makefile "GCc -m64 -fPIC -Wall ...." and "sudo make" – Cristian Porta 10 4月. 13

can you please elaborate more ..i went through this over some sites but cannot figure out how should i proceed.. – Ankit Kapoor 10 4月. 13

About recompiling the mysqludf_sys for 64bit you can look at the answer on your post Http://dba.stackexchange.com/questions/39752/how-to-resolve-elfclass32-error-in-mysql-for-udf-lib-mysqludf-sys-so – Cristian Porta 07 10月. 13

no solution has been provided on this http://dba.stackexchange.com/questions/39752/how-to-resolve-elfclass32-error-in-mysql-for-udf-lib-mysqludf-sys-so – Ankit Kapoor 07 10月. 13

Ok, try this: "gcc -m64 -fPIC -Wall -I/path/to/your/mysql/basedir -I. -shared lib_mysqludf_sys.c -o /path/to/save/your/new/lib_mysqludf_sys.so" – Cristian Porta 07 10月. 13

i will reply in a day or two... – Ankit Kapoor 08 10月. 13

@user21546 any news? :) – Cristian Porta 11 11月. 13

not yet !! im out for some days..i need to review it again on this project !! – Ankit Kapoor 11 11月. 13

hi ..m here now..can you please help me here...i dnt know how to install lib_mysqludf_sys...once it done...i will Go further – Ankit Kapoor 06 12月. 13