Tag: mariadb

Building LIB_MYSQLUDF_SYS On Fedora 31

I moved my MariaDB server to a new host and could not follow my previously working instructions to build lib_mysqludf_sys. The error indicated that my_atomic.h was not found.

[lisa@server03 lib_mysqludf_sys]# make
gcc -fPIC -Wall -I/usr/include/mysql/server -I. -shared lib_mysqludf_sys.c -o /usr/lib64/mariadb/plugin//lib_mysqludf_sys.so
In file included from /usr/include/mysql/server/my_sys.h:34,
from lib_mysqludf_sys.c:41:
/usr/include/mysql/server/my_pthread.h:26:10: fatal error: my_atomic.h: No such file or directory
26 | #include <my_atomic.h>
| ^~~~~~~~~~~~~
compilation terminated.
make: *** [Makefile:4: install] Error 1

The missing file is located in /usr/include/mysql/server/private … so I had to include that file in the gcc command as well. My new Makefile reads as follows:

[lisa@server03 lib_mysqludf_sys]# cat Makefile
LIBDIR=/usr/lib64/mariadb/plugin/

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

I was then able to make and use install.sh to load it into MariaDB.

MariaDB Strangeness

I had to remove and reinstall MariaDB to upgrade from Fedora 29. I assumed Fedora 31 would have a more recent version, so I just installed the database and attempted to start it. Nope.

In journalctl

Dec 28 23:37:49 fedora123 mysql-prepare-db-dir[3977]: Database MariaDB is probably initialized in /var/lib/mysql already, nothing is done.
Dec 28 23:37:49 fedora123 mysql-prepare-db-dir[3977]: If this is not the case, make sure the /var/lib/mysql is empty before running mysql-prepare-db-dir.
Dec 28 23:37:49 fedora123 mysqld[4013]: 2019-12-28 23:37:49 0 [Note] /usr/libexec/mysqld (mysqld 10.3.20-MariaDB) starting as process 4013 …
Dec 28 23:37:49 fedora123 systemd[1]: mariadb.service: Main process exited, code=exited, status=1/FAILURE

In mariadb.log

2019-12-28 23:23:53 0 [ERROR] Fatal error: Can’t open and lock privilege tables: ‘mysql.user’ is not of type ‘TABLE’

Turns out the Fedora repo has 10.3, but the MariaDB repo that I’d used with Fedora 29 had MariaDB 10.4. After enabling the MariaDB repo and installing 10.4, the service without error.

openHAB – Motion Detection With Zoneminder Via SQL Triggers

We had used ZoneMinder filters to run a script which turned a “motion detected” switch on and off in openHAB. We had turned that off in favor of an openHAB/ZoneMinder binding; but the binding polled ZoneMinder for motion events, and this added significant load to our system. We tried re-enabling the filters we’d used previously, but they didn’t work. There are a lot of caveats around using filters (tl;dr: filtering can be delayed by several minutes, which renders ‘now’ filters ineffective) and more recent versions of ZoneMinder don’t have a number of alarm frames until after the event (which means filtering on alarm frames > 1 only detects motion after the fact). All of this means that the filters which worked pretty well a year or two ago no longer work reliably. Architecturally, the ZoneMinder filter process seemed ill suited for our needs. Actions that are not time sensitive, like file cleanup or roll-up reporting, could be done through a filter. But it’s not a good solution for identifying the FexEx guy in the driveway.

ZoneMinder uses a database to maintain system and alert data — I use MariaDB 10.3.18-1. MySQL introduced TRIGGER back in version 5. A trigger is essentially a bit of SQL automatically executed by the database when operations occur within a table — table activity triggers execution. When ZoneMinder first detects motion, an event is recorded in the database. When motion is no longer detected, the motion event is updated with event info (number of frames, event duration). Since both inserting a motion event and updating the event when motion ends are events within tables, a trigger can execute some SQL code almost immediately without much impact to system load.

The only problem is that SQL code does not, normally, POST data to a URI. Creating a trigger which can execute external binaries requires creating a UDF (user-defined function). I am using lib_mysqludf_sys which creates sys_get, sys_set, sys_exec, and sys_eval functions. The sys_get and sys_set functions are used for setting/getting environment variables. The sys_exec function returns the return code from execution, whereas sys_eval returns the output from execution.

Adding SYS UDF’s To MariaDB:

After cloning the lib_mysqludf_sys repo locally, edit Makefile to set LIBDIR to the appropriate directory for the MariaDB installation (/usr/lib64/mariadb/plugin/ in my case). I also needed to modify the compilation line to:

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

** 01 August 2020 update — I had to include an additional folder to build the latest version of this program on Fedora 31.

Run install.sh to install and register the user-defined functions in the MariaDB server. Because the output of command execution is unnecessary, the sys_exec is sufficient. Before registering a trigger, use the CLI SQL to verify sys_exec is working:

MariaDB [zm]> SELECT sys_exec('cat /etc/fedora-release');
+-------------------------------------+
| sys_exec('cat /etc/fedora-release') |
+-------------------------------------+
| 0 |
+-------------------------------------+
1 row in set (0.012 sec)

Creating the SQL Trigger:

To create a trigger for motion events, there needs to be a mapping between the monitorID used in ZoneMinder. You see the monitorID in the URL when you view a feed — “mid” in the GET query string:

Or use a SQL client to obtain a list of monitors from the ZoneMinder database:

MariaDB [zmdb]> select Id, Name from Monitors;
+----+-----------------------------------+
| Id | Name                              |
+----+-----------------------------------+
| 15 | IPCam01 - Area 123                |
| 16 | IPCam02 - Area 234                |
| 17 | IPCam03 - Area 345                |
| 18 | IPCam04 - Area 456                |
| 19 | IPCam05 - Area 567                |
+----+-----------------------------------+

Once you can correlate monitor ID values to OpenHAB items, update the IF/THEN section of the trigger. Update the strOpenHABHost variable to your server URL. There are two useful SQL commands commented out (– ) below. SHOW TRIGGERS does exactly that – it lists triggers that are registered in the database. DROP TRIGGER is used to remove the trigger. If you are using HTTPS to communicate with OpenHAB, you may need to add “–insecure” to the curl command to ignore certificate errors (or use –cacert to to establish a trust chain).

The sys_exec function in this trigger uses curl to post an item stage change to the OpenHAB REST API. Camera items are on when motion is detected.

To create the TriggerMotionOnNewEvent trigger, paste the following into your SQL client:

-- SHOW TRIGGERS
-- DROP TRIGGER zm.TriggerMotionOnNewEvent;
DELIMITER @@

CREATE TRIGGER TriggerMotionOnNewEvent
AFTER INSERT ON `Events`
FOR EACH ROW
BEGIN

DECLARE strCommand CHAR(255);
DECLARE strCameraName CHAR(64);
DECLARE iCameraID INT(10);
DECLARE iResult INT(10);
-- variables for local openHAB REST API hostname and port
DECLARE strOpenHABHost CHAR(64);
SET strOpenHABHost='http://openhabhost.example.com:8080';


-- Translate ZoneMinder IP camera ID with openHAB item name
SET iCameraID = NEW.monitorID;
IF(iCameraID = 10) THEN
SET strCameraName='IPCam05_Alarm';
ELSEIF(iCameraID = 11) THEN
SET strCameraName='IPCam03_Alarm';
ELSEIF(iCameraID = 12) THEN
SET strCameraName='IPCam04_Alarm';
ELSEIF(iCameraID = 13) THEN
SET strCameraName='IPCam01_Alarm';
ELSEIF(iCameraID = 14) THEN
SET strCameraName='IPCam02_Alarm';
END IF;

SET strCommand=CONCAT('/usr/bin/curl ', '-s --connect-timeout 10 -m 10 -X PUT --header "Content-Type: text/plain" --header "Accept: application/json" -d "ON" "',strOpenHABHost,'/rest/items/',strCameraName,'/state"');
SET iResult = sys_exec(strCommand);
END;
@@
DELIMITER ;

There is a second trigger to clear the motion event — set the camera item to off when there is no longer motion detected. ZoneMinder updates event records to record and EndTime for the event. This trigger executes any time an Event item is updated, but there is an IF statement that verifies that the EndTime is not null to avoid clearing the motion event too soon.

To create the ClearMotionOnEventEnd trigger, paste the following into your SQL client:

-- SHOW TRIGGERS
-- DROP TRIGGER zm.ClearMotionOnEventEnd;
DELIMITER @@

CREATE TRIGGER ClearMotionOnEventEnd
AFTER UPDATE ON `Events`
FOR EACH ROW
BEGIN

DECLARE strCommand CHAR(255);
DECLARE iResult int(10);
DECLARE strCameraName CHAR(25);
DECLARE iCameraID int(5);
-- variables for local openHAB REST API hostname and port
DECLARE strOpenHABHost CHAR(64);
SET strOpenHABHost='http://openhabhost.example.com:8080';

-- Translate ZoneMinder IP camera ID with openHAB item name
SET iCameraID = NEW.monitorID;
IF iCameraID = 10 THEN
SET strCameraName='IPCam05_Alarm';
ELSEIF iCameraID = 11 THEN
SET strCameraName='IPCam03_Alarm';
ELSEIF iCameraID = 12 THEN
SET strCameraName='IPCam04_Alarm';
ELSEIF iCameraID = 13 THEN
SET strCameraName='IPCam01_Alarm';
ELSEIF iCameraID = 14 THEN
SET strCameraName='IPCam02_Alarm';
END IF;

IF NEW.EndTime IS NOT NULL THEN
SET strCommand=CONCAT('/usr/bin/curl ', '-s --connect-timeout 10 -m 10 -X PUT --header "Content-Type: text/plain" --header "Accept: application/json" -d "OFF" "http://',strOpenHABHost,':',iOpenHABPort,'/rest/items/',strCameraName,'/state"');
SET iResult = sys_exec(strCommand);
END IF;

END;
@@
DELIMITER ;

Now when new motion detection events are inserted into the Events database table, the openHAB item corresponding to the camera will be turned on. When the event record is updated with an end timestamp, the openHAB item corresponding to the camera will be turned off.

Our implementation executes a second external command. Getting notified of motion when we’re home is great — pull up ZoneMinder, see the FedEx truck. But we don’t publish most of our infrastructure to the Internet — watching the video feed from ZoneMinder means VPN’ing into the network. I put together a quick shell script to pull the 25th image from the motion event (we retain a few seconds prior to motion being detected, and the number of frames recorded per second will vary … so there is trial-and-error involved in identifing an early-in-the-event frame that includes the triggering object). The sleep ensures enough time has elapsed for the motion images to be committed to disk.

#!/bin/bash
# parameter 1 is camera ID
# parameter 2 is camera name
# parameter 3 is event ID
sleep 5
strDate=$(date +%F)
strFile='/mnt/data/zoneminder/events/'$1'/'$strDate'/'$3'/00025-capture.jpg'
echo $strFile

echo "Image for event ID $2 on $strDate is attached to this message" | mailx -r "zoneminder@example.com" -s "$2 Motion Event" -a $strFile Us@example.com

TriggerMotionOnNewEvent includes the following two lines to trigger execution of the shell script when motion is detected.

SET strCommand=CONCAT('/path/to/shell/scripts/sendZoneminderEventImage.sh ',iCameraID,' "',strCameraName,'" ',NEW.Id,"&");
SET iResult=sys_exec(strCommand);

In doing so, we have an e-mail on our phones with a JPG from the motion event — I can quickly see the difference between a cat and a cat-burgler prowling around the patio when we’re away from home.