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 (David Powell)
----------------------------------------------------------------------
Message: 1
Date: Thu, 29 Jul 2021 11:11:01 -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:
<17af3088a08.279e.ce3d8395e0ae66063e5e142696dac...@depowell.com>
Content-Type: text/plain; charset="us-ascii"; Format="flowed"
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 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
>> --
>>
>>
>> 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
> _______________________________________________
> 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 11:13:31 -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:
<17af30ad3f8.279e.ce3d8395e0ae66063e5e142696dac...@depowell.com>
Content-Type: text/plain; charset="us-ascii"; Format="flowed"
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> 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 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
>>> --
>>>
>>> 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
>> _______________________________________________
>> 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 13
********************************************
