Send Motion-user mailing list submissions to
motion-user@lists.sourceforge.net
To subscribe or unsubscribe via the World Wide Web, visit
https://lists.sourceforge.net/lists/listinfo/motion-user
or, via email, send a message with subject or body 'help' to
motion-user-requ...@lists.sourceforge.net
You can reach the person managing the list at
motion-user-ow...@lists.sourceforge.net
When replying, please edit your Subject line so it is more specific
than "Re: Contents of Motion-user digest..."
Today's Topics:
1. Re: Duplicate SQL entries for every event (tosiara)
----------------------------------------------------------------------
Message: 1
Date: Fri, 27 Aug 2021 11:29:18 +0300
From: tosiara <tosi...@gmail.com>
To: David Powell <da...@depowell.com>
Cc: Motion discussion list <motion-user@lists.sourceforge.net>
Subject: Re: [Motion-user] Duplicate SQL entries for every event
Message-ID:
<CACHTdwQWfBnXxciA_CZdQuxaVO27ASUvgxvaM3Tza3i=47h...@mail.gmail.com>
Content-Type: text/plain; charset="utf-8"
Hi,
I wanted to check your issue, but I don't see any database related
parameters in your provided configs
On Fri, Aug 13, 2021 at 6:23 PM David Powell <da...@depowell.com> wrote:
> I was going to open a bug report, but haven't quite gotten around to it
> yet. Sorry.
>
> Here's the output from *describe events;*
>
> *Field*
> *Type* *Null* *Key* *Default* *Extra*
> server_number int NO PRI <null>
> camera_number int NO PRI <null>
> event_number int NO PRI <null>
> event_date varchar(45) NO PRI
>
> video_length int YES
> <null>
> filename varchar(200) YES
> <null>
>
>
> This is my main motion.conf. I can supply the individual camera config
> files but they're pretty simple. I'll put one of them below the main
> config.
>
> # /usr/local/etc/motion/motion.conf
> #
> # This config file was generated by motion 4.1.1+git20181002-062a328
> # at 2021-06-08T17:01:40
>
>
> ############################################################
> # System control configuration parameters
> ############################################################
>
> # Start in daemon (background) mode and release terminal.
> daemon on
>
> # Start in Setup-Mode, daemon disabled.
> setup_mode off
>
> # File to store the process ID.
> ; pid_file value
>
> # File to write logs messages into. If not defined stderr and syslog is
> used.
> log_file /var/log/motion/motion.log
>
> # Level of log messages [1..9] (EMG, ALR, CRT, ERR, WRN, NTC, INF, DBG,
> ALL).
> log_level 4
>
> # Filter to log messages by type (COR, STR, ENC, NET, DBL, EVT, TRK, VID,
> ALL).
> log_type ALL
>
> # Do not sound beeps when detecting motion
> quiet on
>
> # Native language support.
> native_language on
>
> # User defined name for the camera.
> ; camera_name value
>
> # Numeric identifier for the camera.
> camera_id 0
>
> # Target directory for pictures, snapshots and movies
> ; target_dir value
>
> # Video device (e.g. /dev/video0) to be used for capturing.
> videodevice /dev/video0
>
> # Parameters to control video device. See motion_guide.html
> ; vid_control_params value
>
> # Preferred color palette to be used for the video device
> v4l2_palette 17
>
> # The input number to be used on the video device.
> input -1
>
> # The video norm to use for video capture and TV tuner cards.
> norm 0
>
> # The frequency to set the tuner to (kHz) for TV tuner cards
> frequency 0
>
> # The Motion method to use to change the brightness/exposure on video
> device.
> auto_brightness 0
>
> # Device name (e.g. /dev/tuner0) to be used for capturing when using tuner
> as source
> ; tunerdevice value
>
> # Number of frames to capture in each roundrobin step
> roundrobin_frames 1
>
> # Number of frames to skip before each roundrobin step
> roundrobin_skip 1
>
> # Try to filter out noise generated by roundrobin
> roundrobin_switchfilter off
>
> # The full URL of the network camera stream.
> ; netcam_url value
>
> # Optional high resolution URL for rtsp/rtmp cameras only.
> ; netcam_highres value
>
> # Username and password for network camera. Syntax username:password
> ; netcam_userpass value
>
> # The method for keep-alive of network socket for mjpeg streams.
> netcam_keepalive on
>
> # The URL to use for a netcam proxy server.
> ; netcam_proxy value
>
> # Use less strict jpeg checks for network cameras.
> netcam_tolerant_check on
>
> # Use TCP transport for RTSP/RTMP connections to camera.
> netcam_use_tcp on
>
> # Name of mmal camera (e.g. vc.ril.camera for pi camera).
> ; mmalcam_name value
>
> # Camera control parameters (see raspivid/raspistill tool documentation)
> ; mmalcam_control_params value
>
> ############################################################
> # Image Processing configuration parameters
> ############################################################
>
> # Image width in pixels.
> width 640
>
> # Image height in pixels.
> height 480
>
> # Maximum number of frames to be captured per second.
> framerate 15
>
> # Minimum time in seconds between capturing picture frames from the camera.
> minimum_frame_time 0
>
> # Number of degrees to rotate image.
> rotate 0
>
> # Flip image over a given axis
> flip_axis none
>
> # Draw a locate box around the moving object.
> locate_motion_mode preview
>
> # Set the look and style of the locate box.
> locate_motion_style redbox
>
> # Text to be overlayed in the lower left corner of images
> text_left CAMERA1
>
> # Text to be overlayed in the lower right corner of images.
> text_right %Y-%m-%d\n%T-%q
>
> # Overlay number of changed pixels in upper right corner of images.
> text_changes off
>
> # Scale factor for text overlayed on images.
> text_scale 1
>
> # The special event conversion specifier %C
> text_event %Y%m%d%H%M%S
>
> ############################################################
> # Motion detection configuration parameters
> ############################################################
>
> # Always save pictures and movies even if there was no motion.
> emulate_motion off
>
> # Threshold for number of changed pixels that triggers motion.
> threshold 2500
>
> # Enable tuning of the threshold down if possible.
> threshold_tune off
>
> # Noise threshold for the motion detection.
> noise_level 32
>
> # Automatically tune the noise threshold
> noise_tune on
>
> # Despeckle the image using (E/e)rode or (D/d)ilate or (l)abel.
> despeckle_filter EedDl
>
> # Area number used to trigger the on_area_detected script.
> ; area_detect value
>
> # Full path and file name for motion detection mask PGM file.
> ; mask_file value
>
> # Full path and file name for privacy mask PGM file.
> ; mask_privacy value
>
> # The value defining how slow or fast the smart motion mask created and
> used.
> smart_mask_speed 0
>
> # Percentage of image that triggers a lightswitch detected.
> lightswitch_percent 75
>
> # When lightswitch is detected, ignore this many frames
> lightswitch_frames 3
>
> # Number of images that must contain motion to trigger an event.
> minimum_motion_frames 1
>
> # Gap in seconds of no motion detected that triggers the end of an event.
> event_gap 60
>
> # The number of pre-captured (buffered) pictures from before motion.
> pre_capture 6
>
> # Number of frames to capture after motion is no longer detected.
> post_capture 3
>
> ############################################################
> # Script execution configuration parameters
> ############################################################
>
> # Command to be executed when an event starts.
> ; on_event_start value
>
> # Command to be executed when an event ends.
> ; on_event_end value
>
> # Command to be executed when a picture is saved.
> ; on_picture_save value
>
> # Command to be executed when motion in a predefined area is detected
> ; on_area_detected value
>
> # Command to be executed when motion is detected
> ; on_motion_detected value
>
> # Command to be executed when a movie file is created.
> ; on_movie_start value
>
> # Command to be executed when a movie file is closed.
> on_movie_end java -cp /usr/local/bin/CamSummary.jar
> david/camera/SpecUpdater %f
>
> # Command to be executed when a camera can't be opened or if it is lost
> ; on_camera_lost value
>
> # Command to be executed when a camera that was lost has been found.
> ; on_camera_found value
>
> ############################################################
> # Picture output configuration parameters
> ############################################################
>
> # Output pictures when motion is detected
> picture_output best
>
> # Output pictures with only the pixels moving object (ghost images)
> picture_output_motion off
>
> # Format for the output pictures.
> picture_type jpeg
>
> # The quality (in percent) to be used in the picture compression
> picture_quality 75
>
> # Text to include in a JPEG EXIF comment
> ; picture_exif value
>
> # File name(without extension) for pictures relative to target directory
> picture_filename %Y-%m-%d/%t-%v-%C
>
> ############################################################
> # Snapshot output configuration parameters
> ############################################################
>
> # Make automated snapshot every N seconds
> snapshot_interval 0
>
> # File name(without extension) for snapshots relative to target directory
> snapshot_filename %v-%Y%m%d%H%M%S-snapshot
>
> ############################################################
> # Movie output configuration parameters
> ############################################################
>
> # Create movies of motion events.
> movie_output on
>
> # Create movies of moving pixels of motion events.
> movie_output_motion off
>
> # Maximum length of movie in seconds.
> movie_max_time 120
>
> # The fixed bitrate to be used by the movie encoder. Ignore quality setting
> movie_bps 400000
>
> # The encoding quality of the movie. (0=use bitrate. 1=worst quality,
> 100=best)
> movie_quality 45
>
> # Container/Codec to used for the movie. See motion_guide.html
> movie_codec mp4
>
> # Duplicate frames to achieve "framerate" fps.
> movie_duplicate_frames off
>
> # Pass through from the camera to the movie without decode/encoding.
> movie_passthrough off
>
> # File name(without extension) for movies relative to target directory
> movie_filename %Y-%m-%d/%t-%v-%C
>
> # Use pipe and external encoder for creating movies.
> movie_extpipe_use off
>
> # Full path and options for external encoder of movies from raw images
> ; movie_extpipe value
>
> ############################################################
> # Timelapse output configuration parameters
> ############################################################
>
> # Interval in seconds between timelapse captures.
> timelapse_interval 0
>
> # Timelapse file rollover mode. See motion_guide.html for options and uses.
> timelapse_mode daily
>
> # Frame rate for timelapse playback
> timelapse_fps 30
>
> # Container/Codec for timelapse movie.
> timelapse_codec mpg
>
> # File name(without extension) for timelapse movies relative to target
> directory
> timelapse_filename %Y%m%d-timelapse
>
> ############################################################
> # Loopback pipe configuration parameters
> ############################################################
>
> # v4l2 loopback device to receive normal images
> ; video_pipe value
>
> # v4l2 loopback device to receive motion images
> ; video_pipe_motion value
>
> ############################################################
> # Webcontrol configuration parameters
> ############################################################
>
> # Port number used for the webcontrol.
> webcontrol_port 1800
>
> # Enable IPv6 addresses.
> webcontrol_ipv6 off
>
> # Restrict webcontrol connections to the localhost.
> webcontrol_localhost off
>
> # Type of configuration options to allow via the webcontrol.
> webcontrol_parms 2
>
> # Method that webcontrol should use for interface with user.
> webcontrol_interface 0
>
> # The authentication method for the webcontrol
> webcontrol_auth_method 0
>
> # Authentication string for the webcontrol. Syntax username:password
> ; webcontrol_authentication value
>
> # Use ssl / tls for the webcontrol
> webcontrol_tls off
>
> # Full path and file name of the certificate file for tls
> ; webcontrol_cert value
>
> # Full path and file name of the key file for tls
> ; webcontrol_key value
>
> # The cross-origin resource sharing (CORS) header for webcontrol
> ; webcontrol_cors_header value
>
> ############################################################
> # Live stream configuration parameters
> ############################################################
>
> # The port number for the live stream.
> stream_port 8081
>
> # The port number for the live substream
> substream_port 0
>
> # Restrict stream connections to the localhost.
> stream_localhost off
>
> # Authentication method for live stream.
> stream_auth_method 0
>
> # The authentication string for the stream. Syntax username:password
> ; stream_authentication value
>
> # Use ssl / tls for stream.
> stream_tls off
>
> # The cross-origin resource sharing (CORS) header for the stream
> ; stream_cors_header value
>
> # Percentage to scale the stream image on the webcontrol.
> stream_preview_scale 25
>
> # Have the stream image start on a new line of the webcontrol
> stream_preview_newline off
>
> # Method for showing stream on webcontrol.
> stream_preview_method 0
>
> # Quality of the jpeg images produced for stream.
> stream_quality 50
>
> # Provide the stream images in black and white
> stream_grey off
>
> # Output frames at 1 fps when no motion is detected.
> stream_motion off
>
> # Maximum framerate of images provided for stream
> stream_maxrate 1
>
> # Limit the number of images per connection
> stream_limit 0
>
> ############################################################
> # Database and SQL Configuration parameters
> ############################################################
>
> # The type of database being used if any.
> database_type mysql
>
> # Database name to use. For sqlite3, the full path and name.
> database_dbname cameras
>
> # The host on which the database is located
> database_host localhost
>
> # Port used by the database.
> database_port 3306
>
> # User account name for database.
> database_user <redacted>
>
> # User password for database.
> database_password <redacted>
>
> # Database wait for unlock time
> database_busy_timeout 5
>
> # Log to the database when creating motion triggered image file
> sql_log_picture off
>
> # Log to the database when creating a snapshot image file
> sql_log_snapshot off
>
> # Log to the database when creating motion triggered movie file
> sql_log_movie on
>
> # Log to the database when creating timelapse movie file
> sql_log_timelapse off
>
> # SQL query at event start. See motion_guide.html
> ; sql_query_start value
>
> # SQL query at event stop. See motion_guide.html
> sql_query_stop INSERT INTO events
> (server_number,camera_number,event_number,event_date,video_length,filename)
> VALUES (1,%t,%v,{ts '%C'},null,'%f');
>
> # SQL query string that is sent to the database. See motion_guide.html
> ; sql_query
>
> ############################################################
> # Tracking configuration parameters
> ############################################################
>
> # Method used by tracking camera. See motion_guide.html
> track_type 0
>
> # Enable auto tracking
> track_auto off
>
> # Serial port of motor
> ; track_port value
>
> # Motor number for x-axis
> track_motorx 0
>
> # Set motorx reverse
> track_motorx_reverse off
>
> # Motor number for y-axis
> track_motory 0
>
> # Set motory reverse
> track_motory_reverse off
>
> # Maximum value on x-axis
> track_maxx 0
>
> # Minimum value on x-axis
> track_minx 0
>
> # Maximum value on y-axis
> track_maxy 0
>
> # Minimum value on y-axis
> track_miny 0
>
> # Center value on x-axis
> track_homex 128
>
> # Center value on y-axis
> track_homey 128
>
> # ID of an iomojo camera if used
> track_iomojo_id 0
>
> # Angle in degrees the camera moves per step on the X-axis with auto-track
> track_step_angle_x 10
>
> # Angle in degrees the camera moves per step on the Y-axis with auto-track.
> track_step_angle_y 10
>
> # Delay to wait for after tracking movement as number of picture frames.
> track_move_wait 10
>
> # Speed to set the motor to (stepper motor option)
> track_speed 255
>
> # Number of steps to make (stepper motor option)
> track_stepsize 40
>
> # Command to execute to move a camera in generic tracking mode
> ; track_generic_move value
>
> ##############################################################
> # Camera config files - One for each camera.
> ##############################################################
> camera /usr/local/etc/motion/cameraPatio.conf
> camera /usr/local/etc/motion/cameraShop.conf
> camera /usr/local/etc/motion/cameraPumpHouse.conf
> camera /usr/local/etc/motion/cameraBackYard.conf
> camera /usr/local/etc/motion/cameraPumpYard.conf
> camera /usr/local/etc/motion/cameraShed.conf
> camera /usr/local/etc/motion/cameraGate.conf
>
> ##############################################################
> # Directory to read '.conf' files for cameras.
> ##############################################################
> ; camera_dir /etc/motion/conf.d
>
>
>
> This is the *cameraPatio.conf* file. The other files are very similar.
>
> # /usr/local/etc/motion/cameraPatio.conf
> #
> # This config file was generated by motion 4.1.1+git20181002-062a328
> # at 2021-06-08T17:01:40
>
>
> # User defined name for the camera.
> camera_name patio
>
> # Target directory for pictures, snapshots and movies
> target_dir /var/www/html/cams/patio
>
> # The full URL of the network camera stream.
> netcam_url http://10.10.6.181:8181/videostream.cgi?user=<redacted>&pwd=
>
> # The method for keep-alive of network socket for mjpeg streams.
> netcam_keepalive off
>
> # Text to be overlayed in the lower left corner of images
> text_left Patio
>
> # Threshold for number of changed pixels that triggers motion.
> threshold 1000
>
> # Enable tuning of the threshold down if possible.
> threshold_tune on
>
> # Full path and file name for motion detection mask PGM file.
> mask_file /usr/local/etc/motion/patioMask.pgm
>
> ############################################################
> # Webcontrol configuration parameters
> ############################################################
>
> # Port number used for the webcontrol.
> webcontrol_port 0
>
> ############################################################
> # Live stream configuration parameters
> ############################################################
>
> # The port number for the live stream.
> stream_port 1801
>
> Let me know if you need any other info.
>
> David
>
>
> On 8/13/21 6:31 AM, tosiara wrote:
>
> Could you send me your exact motion config and mysql command output:
> "describe events;"
> I will try to check
>
> On Thu, Jul 29, 2021 at 8:45 PM Shawn Ashe <dracutfo...@gmail.com>
> <dracutfo...@gmail.com> wrote:
>
> I would find a way to log it, at least it's a symptom you can give to the
> motion team, and maybe something will become obvious to you
>
> On Thu, Jul 29, 2021, 1:05 PM David Powell <da...@depowell.com>
> <da...@depowell.com> wrote:
>
> It's the %v replaceable parameter in the insert statement. Motion generates
> it internally.
> dp
>
> On July 29, 2021 11:26:25 AM Shawn Ashe <dracutfo...@gmail.com>
> <dracutfo...@gmail.com> wrote:
>
> is the event_number a counter,or based on time/etc?
>
> Could be the events are going off to fast and you are getting duplicate event
> numbers.
>
> On Thu, Jul 29, 2021 at 12:15 PM David Powell <da...@depowell.com>
> <da...@depowell.com> wrote:
>
> Oh, I understand your question better now. The event number is generated by
> Motion automatically.
>
> David
>
> On July 29, 2021 11:11:01 AM David Powell <da...@depowell.com>
> <da...@depowell.com> wrote:
>
> My understanding is that event_number is there for this very purpose - to
> make each event unique. I can't imagine another reason to have it.
>
> David
>
> On July 29, 2021 10:46:33 AM motionl...@frontdesign.com wrote:
>
> Hi.
>
>
> Also; "The primary key for the events table is
> server_number,event_number,event_date, and camera_number."
>
> So, can the server_number, event_number, event_date and camera_number be the
> same twice?
>
> I'm guessing server_number for sure, camera_number too. Event date? Yeah
> possible if it's two events in tight enough sequence and sloppy enough
> datetime datatype.
>
> event_number? ... doesn't sound likely but... what do i know - how does that
> get generated?
>
>
> Why not use an auto_intrement for primary key and have a composite index on
> those other columns instead?
>
>
> //Lars
>
>
>
> On 2021-07-29 16:59, John L. Poole wrote:
>
> The error message you shared, e.g. trigger of a primary key constraint,
> suggests an error in program design. If you have a table with unique
> constraints, e.g. every entry must be unique, you don't try to insert the
> same record twice.
>
> I recommend you file a bug so everything is documented well and then the
> developers can determine why they are attempting to insert something already
> present, or at least testing for presence before attempting to insert a
> record that may already exist.
>
> On 7/29/2021 7:27 AM, David Powell wrote:
>
> This is the whole SQL section of motion.conf:
>
> ############################################################
> # Database and SQL Configuration parameters
> ############################################################
>
> # The type of database being used if any.
> database_type mysql
>
> # Database name to use. For sqlite3, the full path and name.
> database_dbname cameras
>
> # The host on which the database is located
> database_host localhost
>
> # Port used by the database.
> database_port 3306
>
> # User account name for database.
> database_user <redacted>
>
> # User password for database.
> database_password <redacted>
>
> # Database wait for unlock time
> database_busy_timeout 5
>
> # Log to the database when creating motion triggered image file
> sql_log_picture off
>
> # Log to the database when creating a snapshot image file
> sql_log_snapshot off
>
> # Log to the database when creating motion triggered movie file
> sql_log_movie on
>
> # Log to the database when creating timelapse movie file
> sql_log_timelapse off
>
> # SQL query at event start. See motion_guide.html
> ; sql_query_start value
>
> # SQL query at event stop. See motion_guide.html
> sql_query_stop INSERT INTO events
> (server_number,camera_number,event_number,event_date,video_length,filename)
> VALUES (1,%t,%v,{ts '%C'},null,'%f');
>
> # SQL query string that is sent to the database. See motion_guide.html
> ; sql_query
>
> I had the query in sql_query but I moved it to sql_query_stop. That didn't
> help.
>
> David
>
>
> On 7/29/21 8:17 AM, Damian via Motion-user wrote:
>
> The SQL statement in motion.conf is
> INSERT INTO events
> (server_number,camera_number,event_number,event_date,video_length,filename)
> VALUES (1,%t,%v,{ts '%C'},null,'%f');
>
> The records /are/ there in the database, and I run a custom program to
> extract them for displaying the snapshots and movies in a web page
>
>
> Which sql_log_* statements are enabled?
>
>
> _______________________________________________
> Motion-user mailing
> listMotion-user@lists.sourceforge.nethttps://lists.sourceforge.net/lists/listinfo/motion-userhttps://motion-project.github.io/
>
> Unsubscribe: https://lists.sourceforge.net/lists/options/motion-user
>
>
>
> _______________________________________________
> Motion-user mailing
> listMotion-user@lists.sourceforge.nethttps://lists.sourceforge.net/lists/listinfo/motion-userhttps://motion-project.github.io/
>
> Unsubscribe: https://lists.sourceforge.net/lists/options/motion-user
>
> --
>
> John Laurence Poole
> 1566 Court ST NE
> Salem OR 97301-4241
> 707-812-1323 office
>
>
>
> _______________________________________________
> Motion-user mailing
> listMotion-user@lists.sourceforge.nethttps://lists.sourceforge.net/lists/listinfo/motion-userhttps://motion-project.github.io/
>
> Unsubscribe: https://lists.sourceforge.net/lists/options/motion-user
>
> _______________________________________________
> Motion-user mailing
> listMotion-user@lists.sourceforge.nethttps://lists.sourceforge.net/lists/listinfo/motion-userhttps://motion-project.github.io/
>
> Unsubscribe: https://lists.sourceforge.net/lists/options/motion-user
>
> _______________________________________________
> Motion-user mailing
> listMotion-user@lists.sourceforge.nethttps://lists.sourceforge.net/lists/listinfo/motion-userhttps://motion-project.github.io/
>
> Unsubscribe: https://lists.sourceforge.net/lists/options/motion-user
>
> _______________________________________________
> Motion-user mailing
> listMotion-user@lists.sourceforge.nethttps://lists.sourceforge.net/lists/listinfo/motion-userhttps://motion-project.github.io/
>
> Unsubscribe: https://lists.sourceforge.net/lists/options/motion-user
>
> _______________________________________________
> Motion-user mailing
> listMotion-user@lists.sourceforge.nethttps://lists.sourceforge.net/lists/listinfo/motion-userhttps://motion-project.github.io/
>
> Unsubscribe: https://lists.sourceforge.net/lists/options/motion-user
>
> _______________________________________________
> Motion-user mailing
> listMotion-user@lists.sourceforge.nethttps://lists.sourceforge.net/lists/listinfo/motion-userhttps://motion-project.github.io/
>
> Unsubscribe: https://lists.sourceforge.net/lists/options/motion-user
>
> _______________________________________________
> Motion-user mailing
> listMotion-user@lists.sourceforge.nethttps://lists.sourceforge.net/lists/listinfo/motion-userhttps://motion-project.github.io/
>
> Unsubscribe: https://lists.sourceforge.net/lists/options/motion-user
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
------------------------------
------------------------------
Subject: Digest Footer
_______________________________________________
Motion-user mailing list
Motion-user@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/motion-user
------------------------------
End of Motion-user Digest, Vol 182, Issue 18
********************************************
