Smoke Detector Alerts Using MySQL

Objective

  • Set up a system to send an alert when a value greater than a specific threshold level of smoke is reported into SQL.
  • Use MySQL triggers to implement the detection.
  • Send an email to the admin to report the abnormal level of smoke.

Process Overview

  • Set up triggers in MySQL to call a procedure on insertion of data if certain conditions are met.
  • Create a procedure to write the data into an outfile.
  • Find out the location of the files written using outfile by mariadb.
  • Set up inotify to monitor the file for creation.
  • Call the mutt program to send an alert to the admins about abnormal smoke levels.

Creating triggers in MySQL

MySql Triggers Syntax - Source for MySQL triggers syntax.

Writing a MySQL triggers that does the following things:

  • Called after data insertion.
  • Checks if newly inserted value is over threshold.
  • If previous condition is true, calls a procedure.
delimiter $$
create trigger sensorDetectionTrigger
    after insert on sensorData for each row
    begin
        if NEW.type = "smoke" then
            if NEW.value>60 then
                call sensorDetectionCallback(NEW.value);
            end if;
        end if;
    end $$
delimiter ;

Creating a procedure in MySQL

Writing a MySQL procedure called when the values of smoke are over a particular threshold limit.

The procedure writes the data of the last inserted values of smoke into an outfile.

delimiter $$
create procedure sensorDetectionCallback (valueIn int)
begin
    select * from sensorData where value=valueIn
    and type="smoke" limit 1
    into outfile "/tmp/smokeDetected.txt" ;
end $$
delimiter ;

Looking for the outfile In the previous step we wrote the data that was to be reported into an outfile, we need to look for the location of this outfile so that we can monitor it and call a script whenever it is created.

To look for the outfile:

find /tmp/ -type f -name "*output.txt*"

The location of the file is in the /tmp directory, however, systemd create a specific directory for services with a dynamic directory name that changes on every reboot. So we have to work around this issue.

To solve the issue we just use regular expressions and find the directory with mariadb in the name.

find /tmp/ -type d -name "*mariadb*"

And then use the output of that command as the input for the inotiy command.

Using Inotify to monitor the directory

The outfile is created in a path that looks like: "/tmp/...mariadb.../tmp/output.txt" so we can monitor the tmp directory inside of mariadb's tmp directory. This way when a file with the name output.txt, we can send an email.

Inotify usage instructions

Inotify is an async program that monitor filesystem changes. inotifywait <path>" this commands output can be piped into a loop that can read the data that is output by inotifywait.

Using mutt to send automated emails

The mutt configuration file:

set from = "Test User"
set realname = "Test User"

set smtp_url = "smtps://@smtp.gmail.com"
set smtp_pass = 

set header_cache = "~/.mutt/cache/headers"
set message_cachedir = "~/.mutt/cache/bodies"
set certificate_file = "~/.mutt/certificates"

set abort_nosubject = no
set mail_check = 60
set sort = "reverse-date-received"
set signature = "~/.mutt/signature"
set copy = no
Using the above configuration file injunction with the following command:
echo "Body of the email" | mutt -s "The subject" "recepient@email.com"

The Complete Script

#!/bin/bash

path=`find /tmp/ -maxdepth 1 -type d -name '*mariadb*'`
file="/tmp/smokeDetected.txt"
if [ -f "$path/$file" ]; then
    rm $path/$file
fi
while true; do
inotifywait $path/tmp/ |
    while read path action file; do
        echo "Smoke levels above threshold level; sending alert.";
    done

output=`cat $path/$file`
warn="There was an abmornal level of smoke detected.
The raw output of the sensors:
Type    Value   RoomId
$output"

if [ ! -z "$output" ]; then
    echo "$warn" | mutt -s "Smoke Detected" suchithsridhar@gmail.com
fi
rm $path/$file
done

Summary:

Find the path of the mariadb tmp folder using the find command. Set the $file variable to the file to look for. Use inotifywait in a loop, set such that every time a file is created with that name in that directory, the $output variable is set to the contents of the file. Perform a final check to make sure that the warning isn't a mis-fire of the program, then use mutt to email the warning message to the admin. Then finally remove the file from the temporary directory so that MySQL is allowed to create it again.