motion drops MySQL support, but you can get around it with a shell script

Akom's Tech Ruminations

motion drops MySQL support, but you can get around it with a shell script

My specific usage

Config

Still Photos

Movies

0 Trackbacks

5 Comments

Random Entry: Gradle Toolchains Support - different JVMs for compile and test
< Splunk high CPU utilization starting July 1, 2012 | amixer toggle mutes but does not unmute >

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:

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:

#!/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

and invoke like this:

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:

To answer some questions, I'm including specific examples.

# 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

(The lines wrap obviously)

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

What this does:

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

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

What this does:

Inserts event again
updates filesize again

Pretty basic. Now as far as a custom web-app, I did write several, but never got around to putting them up on sourceforge. Additionally, I have a cronjob that deletes items older than X days (I only have so much space and I record over 2GB per day from 8 cameras). It also updates the table and sets the "deleted" column to 1 for the deleted files. You could equivalently archive it to some other disk or to something like Amazon Glacier, and mark it archived.

Trackback specific URI for this entry

No Trackbacks

Display comments as (Linear | Threaded)

Gordon on 2012-10-28 04:47:
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
Admin on 2012-10-28 16:11:
Added some answers to your questions to the post.
Gordon on 2012-10-28 17:45:
Thanks for the quick reply. It kind of confirmed my suspicions regarding an application.

2GB in one day seems good going. You have lot's of images as a busy office has lots of motion triggers?

I have an IP cam streaming over the web, one of the cheap water proof wifi ebay jobbies which works really well based on the cost. Next up is for Motion to detect it's stream.

Do you have to use the following command to start Motion?

sudo LD_PRELOAD=/usr/lib/i386-linux-gnu/libv4l/v4l2convert.so motion

Have you tried Zone Minder? My friend at work uses it but he is not convinced by its system resource usage (memory and CPU).

Gordon
jchvvs on 2013-04-16 11:57:
Hello, Glad to see your post.

So if i understand correctly, you are using motion and mysql. Does it mean that you have a sort of php/sql/html websurveillance system?

Looking forward to reading you,

jchvvs
Admin on 2013-04-16 12:27:
I wrote serveral front-ends for content originated from motion. They are usually in-house custom to the needs of the business, thus I don't open source them. Basically, it's a MVC app that lets you query pre-recorded content by time/size/date/camera, and to play them back in the browser. It also lets you view the live feeds in real time. The app is not all that complex. With mysql, querying becomes trivial. There is also a cron job to drop old content and mark it deleted in DB.

Hope this helps.

November '24