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

[<mot...@fr...>]

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
> -- 
> Email Rider
>
> 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