Creating Mysql Scheduler monitor changes in a table and notify Java server.
Things required
- Installing lib_mysqludf_sys UDF funtion Mysql
- Creating Event and Schedule it at an interval
- create a procedure that will be called by the scheduler and it will intern call the Java Engine.
The aim of this project is to notify the Java engine that there are some users that should be notified by email/any other means when the theshold time that is allocated to them is over.
Here we are installing lib_mysqludf_sys that will basically enable to communicate with the OS in this case Ubuntu.
Download the repos from lib_mysqludf_sys:
$ ls
install.sh lib_mysqludf_sys.html lib_mysqludf_sys.sql
lib_mysqludf_sys.c lib_mysqludf_sys.so Makefile$ make installLIBDIR=/usr/lib/mysql/plugin
install:
gcc -DMYSQL_DYNAMIC_PLUGIN -fPIC -Wall -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c -o $(LIBDIR)/lib_mysqludf_sys.soThe plugin directory will be found using the following commend inside Mysql.
mysql> SHOW VARIABLES LIKE 'plugin_dir';To know more plugin dir in mysql
CREATE FUNCTION sys_eval RETURNS INT SONAME 'lib_mysqludf_sys.so';Also check the Application Armor protection
$ sudo aa-status
apparmor module is loaded.
20 profiles are loaded.
20 profiles are in enforce mode.
/sbin/dhclient
/usr/bin/evince
/usr/bin/evince-previewer
/usr/bin/evince-previewer//sanitized_helper
/usr/bin/evince-thumbnailer
/usr/bin/evince-thumbnailer//sanitized_helper
/usr/bin/evince//sanitized_helper
/usr/lib/NetworkManager/nm-dhcp-client.action
/usr/lib/connman/scripts/dhclient-script
/usr/lib/cups/backend/cups-pdf
/usr/lib/lightdm/lightdm-guest-session
/usr/lib/lightdm/lightdm-guest-session//chromium
/usr/lib/telepathy/mission-control-5
/usr/lib/telepathy/telepathy-*
/usr/lib/telepathy/telepathy-*//pxgsettings
/usr/lib/telepathy/telepathy-*//sanitized_helper
/usr/lib/telepathy/telepathy-ofono
/usr/sbin/cups-browsed
/usr/sbin/cupsd
/usr/sbin/tcpdump
0 profiles are in complain mode.
4 processes have profiles defined.
4 processes are in enforce mode.
/sbin/dhclient (1081)
/usr/lib/telepathy/mission-control-5 (2221)
/usr/sbin/cups-browsed (973)
/usr/sbin/cupsd (2245)
0 processes are in complain mode.
0 processes are unconfined but have a profile defined.see whether msqld is having an enforcement from the OS if yes then disable it.
sudo ln -s /etc/apparmor.d/usr.sbin.mysqld /etc/apparmor.d/disable/
sudo apparmor_parser -R /etc/apparmor.d/usr.sbin.mysqldDetails refer: apparmor link
If Curl command doesn't have permission then change the SElinux variable in the /etc/selinux/config file.
sudo vi /etc/selinux/config
SELINUX=disabledThen reboot your system
Details refer: SElinux link
You could enable/disable the event_scheduler in Mysql
SET GLOBAL event_scheduler = ON;
SET GLOBAL event_scheduler = OFF;Then start CREATING.EVENT.sql
The Event can be schedulable to a time interval and will call a procedure
notify_user_by_email(). This will intern call the Application server URL/URI where the listener for this event is resided.