Akom's Tech Ruminations

Various tech outbursts - code and solutions to practical problems

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:
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:


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:

My specific 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

Still Photos

(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:
  1. Runs a tiny shell script to do a mysql insert (see top of post)
  2. Creates a thumbnail in the "thumb" subdirectory for use by my web-app
  3. 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:
  1. Inserts event again
  2. 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.

0 Trackbacks

  1. No Trackbacks


Display comments as (Linear | Threaded)
  1. 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...



  2. Added some answers to your questions to the post.

  3. 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).


  4. 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,


  5. 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.

Add Comment

Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
You can use [geshi lang=lang_name [,ln={y|n}]][/geshi] tags to embed source code snippets.
Standard emoticons like :-) and ;-) are converted to images.
Markdown format allowed

Submitted comments will be subject to moderation before being displayed.