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 (David Powell)
2. Re: Duplicate SQL entries for every event (John L. Poole)
3. Re: Duplicate SQL entries for every event
(motionl...@frontdesign.com)
----------------------------------------------------------------------
Message: 1
Date: Thu, 29 Jul 2021 09:22:30 -0500
From: David Powell <da...@depowell.com>
To: Motion discussion list <motion-user@lists.sourceforge.net>
Subject: Re: [Motion-user] Duplicate SQL entries for every event
Message-ID: <0ec9eedb-357f-2894-3df8-adefc1b79...@depowell.com>
Content-Type: text/plain; charset="utf-8"; Format="flowed"
Motion 4.3.1+git20210613-11320aa
It was the most recent at the time :-)
It has been a couple months since I did the upgrade.
David
On 7/29/21 8:29 AM, MrDave wrote:
>
> By latest Motion version do you mean 4.3.2 or git master?
>
>
> On 7/29/2021 4:58 AM, David Powell wrote:
>>
>> My /Motion/ setup inserts a row for each event (with the /sql_query/
>> config parameter) into a MySQL database.
>>
>> I recently upgraded my Motion server from a RPi 3B+ to a RPi 4B,
>> along with the latest versions of Motion, MySQL, and Ubuntu.? Now I
>> get an error entry in my Motion log for every Motion event. This is a
>> sample entry:
>>
>> *[1:ml1:patio] [ERR] [DBL] [Jul 29 05:27:40] dbse_exec_mysql: MySQL
>> query failed Duplicate entry '1-1-737-2021-07-29 02:57:41' for key
>> 'events.PRIMARY' error code 1062: No such file or directory*
>>
>> The primary key for the /events/ table is
>> /server_number,event_number,event_date/, and /camera_number/.
>>
>> 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. All seems well, except I sometimes get a Motion-generated
>> snapshot that doesn't match the Motion-generated movie, and sometimes
>> I get a movie from which I can't extract the length.? These are
>> fairly rare and may or may not be related to this error.? The log
>> entry, however, appears on each event.
>>
>> I'm not really a database kind of guy.? Anyone else have a similar
>> problem? Would this be caused by Motion, MySQL, or something else?
>>
>> Thanks, David
>>
>>
>>
>> _______________________________________________
>> Motion-user mailing list
>> Motion-user@lists.sourceforge.net
>> https://lists.sourceforge.net/lists/listinfo/motion-user
>> https://motion-project.github.io/
>>
>> Unsubscribe:https://lists.sourceforge.net/lists/options/motion-user
>
>
> _______________________________________________
> Motion-user mailing list
> Motion-user@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/motion-user
> https://motion-project.github.io/
>
> Unsubscribe: https://lists.sourceforge.net/lists/options/motion-user
-------------- next part --------------
An HTML attachment was scrubbed...
------------------------------
Message: 2
Date: Thu, 29 Jul 2021 07:59:59 -0700
From: "John L. Poole" <jlpool...@gmail.com>
To: motion-user@lists.sourceforge.net
Subject: Re: [Motion-user] Duplicate SQL entries for every event
Message-ID: <b95b1d4f-17a3-3715-ce59-d35e43907...@gmail.com>
Content-Type: text/plain; charset="utf-8"; Format="flowed"
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 list
>> Motion-user@lists.sourceforge.net
>> https://lists.sourceforge.net/lists/listinfo/motion-user
>> https://motion-project.github.io/
>>
>> Unsubscribe: https://lists.sourceforge.net/lists/options/motion-user
>
>
> _______________________________________________
> Motion-user mailing list
> Motion-user@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/motion-user
> https://motion-project.github.io/
>
> Unsubscribe: https://lists.sourceforge.net/lists/options/motion-user
--
Email Rider
John Laurence Poole
1566 Court ST NE
Salem OR 97301-4241
707-812-1323 office
-------------- next part --------------
An HTML attachment was scrubbed...
------------------------------
Message: 3
Date: Thu, 29 Jul 2021 17:27:37 +0200
From: motionl...@frontdesign.com
To: motion-user@lists.sourceforge.net
Subject: Re: [Motion-user] Duplicate SQL entries for every event
Message-ID: <4ddcf57e-c46a-bcdb-8d24-e4d97fdf4...@frontdesign.com>
Content-Type: text/plain; charset="utf-8"; Format="flowed"
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 list
>>> Motion-user@lists.sourceforge.net
>>> https://lists.sourceforge.net/lists/listinfo/motion-user
>>> https://motion-project.github.io/
>>>
>>> Unsubscribe: https://lists.sourceforge.net/lists/options/motion-user
>>
>>
>> _______________________________________________
>> Motion-user mailing list
>> Motion-user@lists.sourceforge.net
>> https://lists.sourceforge.net/lists/listinfo/motion-user
>> https://motion-project.github.io/
>>
>> Unsubscribe:https://lists.sourceforge.net/lists/options/motion-user
> --
> Email Rider
>
> John Laurence Poole
> 1566 Court ST NE
> Salem OR 97301-4241
> 707-812-1323 office
>
>
>
> _______________________________________________
> Motion-user mailing list
> Motion-user@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/motion-user
> https://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 181, Issue 12
********************************************
