Post a Comment On: Sergi Adamchuk's blog

I have the following task: I have installed a GPS tracker in car. While not moving the tracker sends to server a ping every 3 minutes. On server I am using traccar I have configured traccar to store its data in mysql Now each time when I receive a ping or location info from the tracker I have to notify OpenHab If there is not ping for approximately 20 minutes I have to send and alarm notification So, here is what I have to do
(Useful article for reference) 1. Configure items and rules on OpenHab Items
Group Car  (All)
DateTime Car_Last_ping   "Last Update [%1$td.%1$tm %1$tH:%1$tM]" (Car) Switch Car_ping "Ping from car" (Car) Rules val Number NO_PING_DELAY = 20 // Minutes
  rule "Set last ping date"   when     Item Car_ping received update   then         postUpdate(Car_Last_ping, new DateTimeType())
  rule "Notify on car ping delay"   when     Time cron "0 0/10 * * * ?" // Every 10 minutes   then     var DateTimeType lastPingTime = Car_Last_ping.state as DateTimeType     var Number delay = (now.millis - lastPingTime.getCalendar.getTimeInMillis) / 60000
    // logInfo("Solvek", "Lasted time "+delay)     if delay>NO_PING_DELAY {       sendTelegram("bot1", "No ping from car for a long time")     }   end 2. Install lib_mysqludf_sys library In order to execute system commands from MySQL triggers we need UDF "sys_exec". It is defined in this library.
2.1. Install dependent library: sudo apt-get install libmysqlclient15-dev 2.2. Grab sys library sources: git clone https://github.com/mysqludf/lib_mysqludf_sys.git 2.3. cd lib_mysqludf_sys 2.4. Install using sudo ./install.sh
2.5. If you are getting errors during compilation modify Makefile as described here and goto 2.4. 2.6. If no errors happens then goto 2.9. However if you get error: "ERROR 1126 (HY000) at line 29: Can't open shared library 'lib_mysqludf_sys.so' (errno: 0 /usr/lib/mysql/plugin/lib_mysqludf_sys.so: cannot open shared object file: No such file or directory) ERROR: unable to install the UDF" do the following steps.
2.7. Copy lib_mysqludf_sys.so from usr/lib to usr/lib/mysql/plugins 2.8. Return back to lib_mysqludf_sys sources directory and manually install UDF by running command: mysql -u root -p mysql < lib_mysqludf_sys.sql
You may test UDF function by quiring SELECT sys_exec('curl  http://localhost:8080/CMD?Car_ping=ON')
2.9. If you get 0 in result then it is ok.
On Ubuntu apparmor can prevent executing of UDF (and you get 32512 instead of 0). In this case you have to disable apparmor it for mysql: sudo ln -s /etc/apparmor.d/usr.sbin.mysqld /etc/apparmor.d/disable/ sudo apparmor_parser -R /etc/apparmor.d/usr.sbin.mysqld
More details here. 3. Create a trigger on positions table in traccar database DROP TRIGGER IF EXISTS `insert_positions`; CREATE DEFINER=`root`@`localhost` TRIGGER `insert_positions`  AFTER INSERT ON `positions`  FOR EACH ROW  IF NEW.device_id = 2 THEN  SET @exec_var = sys_exec('curl http://localhost:8080/CMD?Car_ping=ON');  END IF
Note here NEW.device_id =2. "2" is the id of your device to monitor, you have to replace it with actual value. Also shell command curl http://localhost:8080/CMD?Car_ping=ON allows to update an item on OpenHAB.