Postgres: missing sql insert query! #1692
Replies: 1 comment
-
|
to add some more information here is what motion. log showed: |
Beta Was this translation helpful? Give feedback.
{{ message }}
sosconsult
started this conversation in
General
0 replies
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
-
According to the Poestgresql log entries motion never accessed to its respective postgres table.
Here is the relevant part of the motion.conf:
;#################################################################
;# Database #
;#################################################################
;# Motion can record many details of its images, movies, and #
;# events in a database. Support for one or more of the #
;# Database Management Systems (DBMS) MySQL, MariaDB, #
;# PostgreSQL, and SQLite3 may be incorporated when Motion #
;# is built from source. Many Linux distributions provide #
;# packages to selectively incorporate most or all of #
;# Motion's DBMS support. Configuration option database_type #
;# specifies which DBMS, if any, is used in a given Motion #
;# run. #
;# When a Motion installation includes DBMS support, SQL #
;# statements to insert or update database records may be #
;# specified using Motion's database configuration options #
;# sql_query, sql_query_start, and sql_query_stop. These SQL #
;# statements may record dynamic image, movie, and event #
;# data in database tables using Conversion Specifiers for #
;# data elements like filename, time, number of changed #
;# pixels detected etc. Motion does not record images or #
;# movies themselves in a database. #
;# Motion can insert or update database records when it #
;# creates image or movie files, when it recognizes the #
;# beginning of a motion event, and when it closes movie #
;# files at the end of a motion event. sql_query and #
;# sql_query_stop are executed only for the image and movie #
;# file types enabled by their respective sql_log_* #
;# configuration options. The sql_log_* options do not #
;# impact sql_query_start, which, if specified, is always #
;# executed at event start. #
;# Motion does not provide for deletion of the database #
;# records it creates nor for the images and movies they #
;# catalog. Other user-provided scripts or software may #
;# analyze, search, display, and purge database records and #
;# related images and movies. #
;# For further details please refer to #
;# https://motion-project.github.io/motion_config.html #
;#---------------------------------------------------------------#
;# database_type #
;# Type: String #
;# Range / Valid values: mysql, mariadb, postgresql, sqlite3 #
;# Default: Not defined #
;# This option specifies the database type. #
;#---------------------------------------------------------------#
database_type postgresql;
;#---------------------------------------------------------------#
;# database_dbname #
;# Type: String #
;# Range / Valid values: Max 4095 characters #
;# Default: Not defined #
;# Name of a database (SQLite3: full path to the database #
;# file) containing preexisting table(s) created with #
;# schema(s) consistent with any SQL statements specified by #
;# sql_query* configuration options. #
;#---------------------------------------------------------------#
database_dbname camserv
;#---------------------------------------------------------------#
;# database_host #
;# Type: String #
;# Range / Valid values: Max 4095 characters #
;# Default: localhost #
;# The host on which the database is located. Hostname, #
;# domain name, or IP address. Optionally for PostgreSQL, #
;# full path of the local directory containing its Unix #
;# domain pipe; e.g., /var/run/postgresql. Ignored for #
;# SQLite3. #
;#---------------------------------------------------------------#
database_host localhost
;#---------------------------------------------------------------#
;# database_port #
;# Type: Integer #
;# Range / Valid values: 0 – 65535 #
;# Default: 0 #
;# The port number that is used for the database. Ignored #
;# for SQLite3. Typical values are: mysql=3306 and #
;# postgresql=5432 #
;#---------------------------------------------------------------#
database_port 5432
;#---------------------------------------------------------------#
;# database_user #
;# Type: String #
;# Range / Valid values: Max 4095 characters #
;# Default: Not defined #
;# The user account name for database. Ignored for SQLite3. #
;#---------------------------------------------------------------#
database_user motion
;#---------------------------------------------------------------#
;# database_password #
;# Type: String #
;# Range / Valid values: Max 4095 characters #
;#---------------------------------------------------------------#
;#---------------------------------------------------------------#
;# database_busy_timeout #
;# Type: Integer #
;# Range / Valid values: 0 or more, millisec. #
;# Default: 0, immediate timeout #
;# SQLite3 only. If a required SQLite3 table is locked when #
;# a Motion thread issues a SQL statement, the maximum time #
;# it may block the camera's Motion thread before that SQL #
;# statement is abandoned. #
;#---------------------------------------------------------------#
;#---------------------------------------------------------------#
;# sql_log_picture #
;# Type: Boolean #
;# Range / Valid values: on, off #
;# Default: off #
;# Log to the database when Motion triggers an image file #
;# to be saved. #
;#---------------------------------------------------------------#
sql_log_picture on
;#---------------------------------------------------------------#
;# sql_log_snapshot #
;# Type: Boolean #
;# Range / Valid values: on, off #
;# Default: off #
;# Log to the database when creating a snapshot image file. #
;#---------------------------------------------------------------#
;#---------------------------------------------------------------#
;# sql_log_movie #
;# Type: Boolean #
;# Range / Valid values: on, off #
;# Default: off #
;# Log to the database when creating a motion triggered #
;# movie file. #
;#---------------------------------------------------------------#
sql_log_movie on
;#---------------------------------------------------------------#
;# sql_log_timelapse #
;# Type: Boolean #
;# Range / Valid values: on, off #
;# Default: off #
;# Log to the database when creating a timelapse movie file #
;#---------------------------------------------------------------#
;#---------------------------------------------------------------#
;# sql_query #
;# Type: String #
;# Range / Valid values: Max 4095 characters #
;# Default: Not defined #
;# SQL statement (typically INSERT) to a database table when #
;# a sql_log_* image file is recorded or movie file #
;# recording begins. #
;# You can use Conversion Specifiers within the query. #
;# Sample Queries (depend upon the table provided): #
;# INSERT INTO security(camera, filename, frame, file_type, #
;# time_stamp, text_event) VALUES(%t, '%f', %q, %n, #
;# '%Y-%m-%d %T', '%C') #
;# INSERT IGNORE INTO security(camera, file_name, year, #
;# month, day, hour, minute) VALUES (8, '%f', %Y, %m,%d, %H, #
;# %M) #
;#---------------------------------------------------------------#
sql_query insert into security(camera, filename, frame, file_type, time_stamp, event_time_stamp) values('%t', '%f', '%q', '%n', '%Y-%m-%d %T', '%C')
;#---------------------------------------------------------------#
;# sql_query_start #
;# Type: String #
;# Range / Valid values: Max 4095 characters #
;# Default: Not defined #
;# SQL statement (typically INSERT) executed when a motion #
;# event is recognized. This can be used, for example, to #
;# record event start times and generate unique event Ids. #
;# You can use Conversion Specifiers in this statement. #
;#---------------------------------------------------------------#
;#---------------------------------------------------------------#
;# sql_query_stop #
;# Type: String #
;# Range / Valid values: Max 4095 characters #
;# Default: Not defined #
;# SQL statement (typically INSERT or UPDATE) executed when #
;# Motion stops recording a motion-triggered or timelapse #
;# movie. For example, this can update an events table with #
;# an event end timestamp, provided that movie_output and #
;# sql_log_movie are enabled. #
;# You can use Conversion Specifiers in this statement. File #
;# type (conversion specifier %n) allows distinction between #
;# motion movie end and timelapse movie end. #
;# Sample Queries: #
;# UPDATE security SET movie_end='%Y-%m-%d %T' WHERE #
;# filename='%f' #
;#---------------------------------------------------------------#
what do I miss?
Any advise is welcome. Please help
Beta Was this translation helpful? Give feedback.
All reactions