How to create MySQL triggers to execute external script/php on Ubuntu server 14

How to create MySQL triggers to execute external script/php on Ubuntu server 14

 

 

Ok let's start my assuming that you have Mysql installed and working and have now run into a situation where you need to execute and external command based on a database trigger. 

For example delete a user accounts files when the username is added to the clean table.

 

First you cannot do this with mysql alone. You need to install a plugin libmysqludf_sys which you will have to compile yourself. 

I think the instructions are all a little dated so i've decided to document how I got it working. 

Start by downloading the required files 

 

I always use sudo -s so I don't have to type the password a thousand times...so i don't put sudo infront of everything. Just assume it's run as root.

 

download

<a href="https://github.com/mysqludf/lib_mysqludf_sys/archive/master.zip" rel="nofollow noopener">https://github.com/mysqludf/lib_mysqludf_sys/archive/master.zip</a>

 

more info about it here:

<a href="https://github.com/mysqludf/lib_mysqludf_sys#readme" rel="nofollow noopener">https://github.com/mysqludf/lib_mysqludf_sys#readme</a>

 

Unzip them and copy to the server or download with server and unzip it's up to you. 

 

Here is where it starts to fall apart a bit, default installs are missing a lot of what you need so first lets apt-get the prereqs. There could be more read the error messages. 

 

apt-get install make gcc  libmysqlclient-dev

 

cd into the lib_mysqludf_sys-master folder

 

vi Makefile

 

edit the LIBDIR variable to match below for Ubuntu and add -fPIC to the gcc command then (:x) save close

 

---------------

LIBDIR=/usr/lib/mysql/plugin

 

install:

        gcc -Wall -fPIC -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c -o $(LIBDIR)/lib_mysqludf_sys.so

 

--------------

 

The install.sh script does some checks that fail due to dated references, we will just run the make command first from the command line to skip those checks. 

 

make

 

If it worked then you should have a  lib_mysqludf_sys.so file in your /usr/lib/mysql/plugin folder now. If so then we are golden. If not keep troubleshooting you need this file. It might be possible to use the provided file, but I doubt it for some reason. 

 

Now that make is complete the install.sh script can be used to setup the database entries for this library it should run fine now. 

 

./install.sh

 

From here the plugin is installed but will be blocked by apparmour so we will disable it for mysqld

 

ln -s /etc/apparmor.d/usr.sbin.mysqld /etc/apparmor.d/disable/

apparmor_parser -R /etc/apparmor.d/usr.sbin.mysqld

 

to check before and after results if desired. 

aa-status

 

Now let's test it. Start mysql and run a few test commands.

mysql -p

..password..

SELECT sys_eval('id');

Should give you something like this:

mysql&gt; SELECT sys_eval('id');

+--------------------------------------------------+

| sys_eval('id')                                   |

+--------------------------------------------------+

| uid=105(mysql) gid=115(mysql) groups=115(mysql)

 |

+--------------------------------------------------+

1 row in set (0.08 sec)

 

 

Next we will setup a stored procedure to call when the trigger executes

 

CREATE DEFINER=`root`@`localhost` PROCEDURE `deletemailbox_sp`()

BEGIN

 DECLARE cmd VARCHAR(100);

 DECLARE result VARCHAR(100);

 DECLARE vuser VARCHAR(100);

 SELECT email FROM clean  into vuser  ; 

 SET cmd = CONCAT('/etc/cleanmailbox.sh '  ,vuser );

 SET result = sys_eval(cmd);

INSERT INTO deleted(email) VALUES( vuser);

END

 

Then add a trigger on insert to call the procedure which calls the external function. In this case because a trigger cannot delete from the triggering table I delete the record with PHP from the external script. This will prevent the select returning more than one row which won't wor with select into.

 

CREATE TRIGGER `udfwrapper_trigger` AFTER INSERT ON `clean`

 FOR EACH ROW CALL deletemailbox_sp()

 

 

Since I wanted to use doveadm to delete all message from a users mailbox I also needed to query the names of all the users mailboxes in my external script. It's still a little clunky but works. 

 

Here is the script i'm using at the moment

 

#!/bin/bash

#remove old file

rm /etc/mailboxes

#list users mailboxes into file

 doveadm mailbox list -u $1 &gt; /etc/mailboxes

 

#process file

while read box

do

doveadm expunge -u $1 mailbox $box larger 1B

done &lt; /etc/mailboxes

#call php script to delete record from clean table

/etc/cleanmailbox.php $1

 

 

To tie this all together I have 2 events that run once an hour  with a slight pause between them. One to add record to the clean table and the other deletes the user record.

 

 

CREATE DEFINER=`root`@`localhost` EVENT `clean` ON SCHEDULE EVERY 1 HOUR STARTS '2015-02-14 00:00:00' ENDS '2019-03-31 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO INSERT INTO `clean` (`email`) select `email` from `virtual_users` WHERE   `last_login`    &lt; DATE_SUB(NOW(), INTERVAL 30 DAY) AND `account_type` = 1

 

and 

 

CREATE DEFINER=`root`@`localhost` EVENT `clean users` ON SCHEDULE EVERY 1 HOUR STARTS '2015-02-11 00:05:00' ENDS '2025-02-11 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO DELETE FROM `virtual_users` WHERE   `last_login`    &lt; DATE_SUB(NOW(), INTERVAL 30 DAY) AND `account_type` = 1

 

 

Now that's a whole lot of fun with databases

 

Tags: