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)
(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.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')
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
CREATE DEFINER=`root`@`localhost` TRIGGER `insert_positions`
AFTER INSERT ON `positions`
FOR EACH ROW
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.
