Re: [Motion-user] Duplicate SQL entries for every event

[David P. <da...@de...>]

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...@de...> 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 mot...@fr... 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
>>>>> Mot...@li...
>>>>> 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
>>>> Mot...@li...
>>>> 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
>>> Mot...@li...
>>> 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
>> Mot...@li...
>> https://lists.sourceforge.net/lists/listinfo/motion-user
>> https://motion-project.github.io/
>>
>> Unsubscribe: https://lists.sourceforge.net/lists/options/motion-user