March 23, 2016

Combining OpenHAB and Traccar for monitoring vehicle pings

I have the following task:

  1. I have installed a GPS tracker in car. While not moving the tracker sends to server a ping every 3 minutes.
  2. On server I am using traccar
  3. I have configured traccar to store its data in mysql
  4. Now each time when I receive a ping or location info from the tracker I have to notify OpenHab
  5. 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


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)


val Number NO_PING_DELAY = 20 // Minutes

  rule "Set last ping date"
    Item Car_ping received update
        postUpdate(Car_Last_ping, new DateTimeType())

  rule "Notify on car ping delay"
    Time cron "0 0/10 * * * ?" // Every 10 minutes
    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")

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
2.3. cd lib_mysqludf_sys
2.4. Install using sudo ./
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 '' (errno: 0 /usr/lib/mysql/plugin/ cannot open shared object file: No such file or directory) ERROR: unable to install the UDF" do the following steps.

2.7. Copy 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 

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` 
 IF NEW.device_id = 2 THEN
 SET @exec_var = sys_exec('curl http://localhost:8080/CMD?Car_ping=ON');

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.

No comments: