sql_query_stop will not be triggert

[zwotzie]

1. Version: 4.2.1
2. installed as deb: pi_stretch_motion_4.2.1-1_armhf.deb
3. motion as part of third party: MotionEyeOS

I run motion with MotionEye but it seems to be not an issue of the third party because:

I configured mysql to track the still images:

CREATE DATABASE motion;
CREATE USER 'motion'@'localhost' IDENTIFIED BY 'mypasswordformotion!';
GRANT ALL ON motion.* TO 'motion'@'localhost';
FLUSH PRIVILEDGES;

USE motion;

CREATE TABLE images (
  id int not null primary key auto_increment
, event_id int
, filename varchar(255) not null
, frame_number int
, file_type int
, image_width int
, image_height int
, motion_center_x int
, motion_center_y int
, changed_pixels int
, noise_level int
, motion_area_height int
, motion_area_width int
, threshold int
, camera int
, creation_time timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE motion_events (
  event_id int primary key auto_increment
, camera tinyint
, start_time timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
, end_time timestamp NULL DEFAULT NULL
);

motion configuration in "Extra Motion Options":

database_type mysql
database_dbname motion
database_host localhost
database_port 3306
database_user motion
database_password mypasswordformotion!

sql_log_picture on

sql_query_start insert into motion_events(camera, start_time) values ('%t', '%Y-%m-%d %T')
sql_query_stop update motion_events set end_time='%Y-%m-%d %T' where event_id=%{dbeventid}
sql_query insert into images (camera, event_id, filename, frame_number, file_type, image_width, image_height, motion_center_x, motion_center_y, changed_pixels, noise_level, motion_area_height, motion_area_width, threshold) values('%t', %{dbeventid}, '%f', %q, %n, %w, %h, %K, %L, %D, %N, %J, %i, %o)

and put the motion options to MotionEye (which all work korrekt - if you look at the debug log):

...
[0:motion] [INF] [ALL] sql_query_stop            update motion_events set end_time='%Y-%m-%d %T' where event_id=%{dbeventid}
...

EVENT_START

[1:ml1] [DBG] [DBL] do_sql_query: Executing mysql query
[1:ml1] [DBG] [EVT] exec_command: Executing external command '/usr/local/lib/python2.7/dist-packages/motioneye/scripts/relayevent.sh "/etc/motioneye/motioneye.conf" start 1; echo $(date --iso-8601=seconds)" gpio 14 = "$(gpio read 14) >> /home/pi/motioneye/gpio14/$(date --iso-8601).txt'
[1:ml1] [INF] [EVT] event_new_video: Source FPS 4
[1:ml1] [NTC] [ALL] motion_detected: Motion detected - starting event 1
[1:ml1] [NTC] [ALL] create_path: creating directory /home/pi/motioneye/pics/2018-12-27/34

SOME IMAGES

[1:ml1] [DBG] [DBL] do_sql_query: Executing mysql query
[1:ml1] [DBG] [EVT] exec_command: Executing external command '/usr/local/lib/python2.7/dist-packages/motioneye/scripts/relayevent.sh "/etc/motioneye/motioneye.conf" picture_save 1 /home/pi/motioneye/pics/2018-12-27/34/21-45-54.03.34.jpg'
[1:ml1] [NTC] [EVT] event_newfile: File of type 1 saved to: /home/pi/motioneye/pics/2018-12-27/34/21-45-54.03.34.jpg
...

END_EVENT

[1:ml1] [DBG] [EVT] exec_command: Executing external command '/usr/local/lib/python2.7/dist-packages/motioneye/scripts/relayevent.sh "/etc/motioneye/motioneye.conf" stop 1; echo $(date --iso-8601=seconds)" gpio 14 = "$(gpio read 14)" end_cmd" >> /home/pi/motioneye/gpio14/$(date --iso-8601).txt'
[1:ml1] [NTC] [ALL] mlp_actions: End of event 1

As you can see, the on_event_end is executed but not the sql_query_stop. Unfortunately the debug output show only "do_sql_query: Executing mysql query" but not the name "sql_xyz" or the sql itself.

All other sqls (sql_query_start, sql_query) are executed well.

[tosiara]

There is some kind of confusion here
sql_query_stop is used to log FILE CLOSE event, not the end of the event itself. Since you are only saving pictures, the query is not executed, because FILE CLOSE is only related to a movie. If you try to set sql_log_movie on you would notice that query is being executed as expected

Related code line: https://github.com/Motion-Project/motion/blob/master/event.c#L319

When only sql_log_picture on the comparison (sqltype & cnt->sql_mask) gives FALSE (8 & 5 == FALSE)

So we should either update documentation to make this clear and rename the confusing option to something like sql_query_movie_end. Or we can split the option into two, one (new) should always execute on event end, another (old, renamed) query - on movie end
Setting this so far as a bug and adding @Mr-Dave to judge and comment

[zwotzie]

I would very much welcome the splitting into the latter two variables (sql_query_movie_end and sql_query_event_end). And the (re)naming into speaking semantically correct terms is always a dream!

In the example sql in the documentation shows the ending of an event. So at least update this as soon as possible.

In my case it's not that problematic, i just use "on_event_end" for this and more... :)