motion drops MySQL support, but you can get around it with a shell script
Posted by Admin • Monday, September 24. 2012 • Category: Linux
I use motion as a surveillance system for a sizable office (9 cameras). Motion is good. But the current package of motion in Ubuntu 12.04 is no longer compiled with MySQL support (used to have it in 10.04). Compiling motion from source really didn't sound appealing... but I do want to add events to a database for my own in-house motion history browser. What to do?
Motion supports command execution on events, such as on_movie_end and on_picture_save... which makes it really easy to do a one-liner like this:
(same for on_picture_save, if needed). You can also daisy-chain commands with semicolon if you have multiple. Also, note that the query above is for my table, yours may have different columns. The rest are my specific examples of usage:
Motion supports command execution on events, such as on_movie_end and on_picture_save... which makes it really easy to do a one-liner like this:
on_movie_end bash -c "echo insert into security(camera, filename, frame, file_type, time_stamp, event_time_stamp, event_id) values('%t', '%f', '%q', '%n', '%Y-%m-%d %T', '%C', '%v') | mysql -uUSER -pPASS"if you'd like to do something fancier, you can break it out into a shell script:
and invoke like this:
#!/bin/bash
logger -t test "$0 executing with $*"
sql="insert into security(camera, filename, frame, file_type, time_stamp, event_time_stamp, event_id) values('$1', '$2', '$3', '$4', '$5', '$6', '$7')"
echo $sql | mysql -u$U -p$P motion
on_movie_end U=USER P=PASS bash /usr/local/sbin/motion-insert-event.sh '%t' '%f' '%q' '%n' '%Y-%m-%d %T' '%C' '%v
(same for on_picture_save, if needed). You can also daisy-chain commands with semicolon if you have multiple. Also, note that the query above is for my table, yours may have different columns. The rest are my specific examples of usage:
My specific usage
To answer some questions, I'm including specific examples.Config
# First, relevant configuration # Create per-day subdirectories so it's not a huge mess: jpeg_filename %Y-%m-%d/img-C%t/%H%M%S-%q movie_filename %Y-%m-%d/movie-C%t/%H%M%S snapshot_filename %Y-%m-%d/snap-C%t/%H%M%S-snapshot timelapse_filename %Y-%m-%d/timelapse-C%t output_normal best # I want to use these images as thumbnails in my webapp
Still Photos
(The lines wrap obviously)What this does:
on_picture_save bash /usr/local/sbin/motion-insert-event.sh '%t' '%f' '%q' '%n' '%Y-%m-%d %T' '%C' '%v'; mkdir -p `dirname %f`/thumb ; convert -resize 80 %f `dirname %f`/thumb/`basename %f` ; sh /usr/local/sbin/motion-update-filesize.sh %f
- Runs a tiny shell script to do a mysql insert (see top of post)
- Creates a thumbnail in the "thumb" subdirectory for use by my web-app
- Updates the value in the filesize column in my table (motion itself doesn't supply this value). This is a simple bash script I wrote, nothing fancy
Movies
What this does:
on_movie_end bash /usr/local/sbin/motion-insert-event.sh '%t' '%f' '%q' '%n' '%Y-%m-%d %T' '%C' '%v'; bash /usr/local/sbin/motion-update-filesize.sh %f
- Inserts event again
- updates filesize again
This is a great idea as I dont want to recompile either!!!
As motion triggers, what do you do with all the images? Is it a case of writing a web app to handle the images and see what is going on?
I was going to run a python script to do do the DB INSERT like you did, and then possibly move some images around as you would end up with millions of images in a single DIR? Then write some PHP to view the database entries and images.
I'm not very far forward with Motion...
thanks
Gordon