Execution of a Java program by a trigger.

04/02 19:09
阅读数 602

I want to make an update trigger execute a Java program.

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

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

展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部