task_reminder.php not sending reminders?

[s4069b]

Has anyone else got this issue? It used to work here, but appears to have stopped. If I assign a note which 'requires action' the Jethro user does not receive an email notification. I have a cron job set-up to run every 5 minutes ( */5 * * * * /usr/local/bin/php /home/churchxx/public_html/jethro/scripts/task_reminder.php ) and it used to work... not sure when it stopped? Roster reminders and email reports still send, so the system mail is still working. If I run /usr/local/bin/php /home/churchxx/public_html/jethro/scripts/task_reminder.php from the terminal I don't see any errors.

[tbar0970]

I think this is caused by the new note-visibility feature ("notes assigned to me") which means the abstract_note table is now a view. Working on a fix now.

[tbar0970]

Same root cause as #652

[tbar0970]

This fix will be in the next release. You can fix it in the interim you can run the following SQL (which won't interfere with the fortcoming upgrade)

DROP VIEW abstract_note;
create view abstract_note as
select an.* from _abstract_note an
WHERE ((an.assignee = getCurrentUserID() AND an.status = 'pending')
OR (`getCurrentUserID`() = -(1))
OR (48 = (SELECT permissions & 48 FROM staff_member WHERE id = getCurrentUserID())));

[s4069b]

Thanks for the fix. I applied it the other day, and I think that is what has triggered errors when we edit. Wondering how to 'undo' the fix to see if that will remove the new errors.

This is a snippet of the error email I receive (multiple times.. ie 30 emails after one edit). The edit appears to be made tho? And It looks like a problem with all the custom fields associated with the person.

======================================================================
Trying to access array offset on value of type null
Line 505 of /home/churchj9/public_html/jethro/include/general.php

USER: 1
REFERER: https://xxx/jethro/?view=_edit_person&personid=974
USER_AGENT: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_5) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/13.1.1 Safari/605.1.15

REQUEST:
Array
(
[view] => _edit_person
[personid] => 974
[edit_object_submitted] => 1
[first_name] => xxx
[last_name] => xxx
[gender] => female
[age_bracketid] => 13
[congregationid] => 1
[status] => 0
[email] =>
[mobile_tel] =>
[work_tel] =>
[remarks] =>
[custom_12_d] => Array
(
[0] => 17
)

[custom_12_m] => Array
(
[0] => 2
)

[custom_12_y] => Array
(
[0] => 2009
)

[custom_42] => Array
(
[0] =>
[1] =>
)

[custom_35] => Array
(
[0] => nil
)

[custom_43] => Array
(
[0] =>
)

[custom_44] => Array
(
[0] =>
)

[custom_38] => Array
(
[0] => 40
[1] =>
)

[custom_38_other] => Array
(
[0] =>
[1] =>
)

[custom_45] => Array
(
[0] => 58
[1] =>
)

[custom_46] => Array
(
[0] => 59
[1] =>
)

[custom_41] => Array
(
[0] => 42
[1] =>
)

[custom_36] => Array
(
[0] => xx xx - Grandmother
)

[custom_47] => Array
(
[0] => xxx
)

[custom_48] => Array
(
[0] => xxx (4)
)

[custom_49_d] => Array
(
[0] => 26
)

[custom_49_m] => Array
(
[0] => 1
)

[custom_49_y] => Array
(
[0] => 2020
)

[custom_40] => Array
(
[0] =>
)

[custom_22] => Array
(
[0] => xx xx xxx
)

[custom_50] => Array
(
[0] => 63
)

[custom_50_other] => Array
(
[0] =>
)

[custom_52_d] => Array
(
[0] => 18
)

[custom_52_m] => Array
(
[0] => 2
)

[custom_52_y] => Array
(
[0] => 2020
)

[custom_52_note] => Array
(
[0] => xx xxx xxx
)

[custom_53] => Array
(
[0] =>
)

[custom_13] => Array
(
[0] =>
)

[custom_13_other] => Array
(
[0] =>
)

[custom_1] => Array
(
[0] =>
)

[custom_2_d] => Array
(
[0] =>
)

[custom_2_m] => Array
(
[0] =>
)

[custom_2_y] => Array
(
[0] =>
)

[custom_3_d] => Array
(
[0] =>
)

[custom_3_m] => Array
(
[0] =>
)

[custom_3_y] => Array
(
[0] =>
)

[custom_19] => Array
(
[0] =>
)

[custom_31_d] => Array
(
[0] =>
)

[custom_31_m] => Array
(
[0] =>
)

[custom_31_y] => Array
(
[0] =>
)

[custom_31_note] => Array
(
[0] =>
)

[custom_32] => Array
(
[0] =>
)

[custom_33] => Array
(
[0] =>
)

[custom_16] => Array
(
[0] =>
)

[custom_17] => Array
(
[0] =>
)

[custom_18] => Array
(
[0] =>
)

[custom_4] => Array
(
[0] =>
)

[custom_5_d] => Array
(
[0] =>
)

[custom_5_m] => Array
(
[0] =>
)

[custom_5_y] => Array
(
[0] =>
)

[custom_14] => Array
(
[0] =>
)

[custom_23] => Array
(
[0] =>
)

[custom_24] => Array
(
[0] =>
)

[custom_24_other] => Array
(
[0] =>
)

[custom_25] => Array
(
[0] =>
)

[custom_29_d] => Array
(
[0] =>
)

[custom_29_m] => Array
(
[0] =>
)

[custom_29_y] => Array
(
[0] =>
)

[custom_30_d] => Array
(
[0] =>
)

[custom_30_m] => Array
(
[0] =>
)

[custom_30_y] => Array
(
[0] =>
)

[custom_27] => Array
(
[0] =>
[1] =>
)

[custom_26] => Array
(
[0] =>
)

[custom_28_d] => Array
(
[0] =>
)

[custom_28_m] => Array
(
[0] =>
)

[custom_28_y] => Array
(
[0] =>
)

[custom_54_d] => Array
(
[0] =>
)

[custom_54_m] => Array
(
[0] =>
)

[custom_54_y] => Array
(
[0] =>
)

[custom_54_note] => Array
(
[0] =>
)

[custom_10] => Array
(
[0] =>
)

[custom_11_d] => Array
(
[0] =>
)

[custom_11_m] => Array
(
[0] =>
)

[custom_11_y] => Array
(
[0] =>
)

[custom_20_d] => Array
(
[0] =>
)

[custom_20_m] => Array
(
[0] =>
)

[custom_20_y] => Array
(
[0] =>
)

[custom_21_d] => Array
(
[0] =>
)

[custom_21_m] => Array
(
[0] =>
)

[custom_21_y] => Array
(
[0] =>
)

[custom_34] => Array
(
[0] =>
)

[JethroSession] => xxx
[wp-settings-3] => libraryContent=browse&advImgDetails=show&editor=tinymce&imgsize=large
[wp-settings-time-3] => 1568333048
[_wpfuuid] => xxx
)

BACKTRACE:
Array
(
[0] => Array
(
[file] => /home/churchj9/public_html/jethro/include/general.php
[line] => 509
[function] => process_widget
[args] => Array
(
[0] => custom_21
[1] => [Object/Array]
[2] => 0
)

   )

[1] => Array
(
[file] => /home/churchj9/public_html/jethro/db_objects/custom_field.class.php
[line] => 539
[function] => process_widget
[args] => Array
(
[0] => custom_21
[1] => [Object/Array]
[2] => [Object/Array]
[3] => 1
)

   )

[2] => Array
(
[file] => /home/churchj9/public_html/jethro/db_objects/person.class.php
[line] => 889
[function] => processWidget
[class] => Custom_Field
[type] => ->
[args] => Array
(
[0] =>
)

   )

[3] => Array
(
[file] => /home/churchj9/public_html/jethro/views/abstract_view_edit_object.class.php
[line] => 48
[function] => processForm
[class] => Person
[type] => ->
[args] => Array
(
)

   )

[4] => Array
(
[file] => /home/churchj9/public_html/jethro/views/abstract_view_edit_object.class.php
[line] => 33
[function] => _processObjectEditing
[class] => Abstract_View_Edit_Object
[type] => ->
[args] => Array
(
)

   )

[5] => Array
(
[file] => /home/churchj9/public_html/jethro/include/system_controller.class.php
[line] => 130
[function] => processView
[class] => Abstract_View_Edit_Object
[type] => ->
[args] => Array
(
)

   )

[6] => Array
(
[file] => /home/churchj9/public_html/jethro/index.php
[line] => 58
[function] => run
[class] => System_Controller
[type] => ->
[args] => Array
(
)

   )

)

[tbar0970]

Looking at the backtrace, I really doubt it's related to re-defining the abstract_note view.

Can you investigate and see if this is happening when you edit every person or just some people?

I guess you could go back to the last upgrade sql file and get the old abstract_note view definition, but as I said I'm pretty confident this is unrelated.

[s4069b]

Thanks for looking into it.
I can confirm that any edit to any person (either standard or custom fields) triggers the same error.
I rolled the database back to before applying the fix and the error has gone away.
I may have accidentally broken something else when originally applying the fix? Not sure. But I'll leave as is for now.

[tbar0970]

Yeah I've looked at it again and I'm really convinced that particular change can't have triggered this error. I've patched all my systems with this fix and havne't had this error. Maybe something else in your system changed (custom fields config?) in the meantime, and that's why your database rollback made the problem go away?

[s4069b]

Sorry to chew up your time chasing that. I just had an opportunity to look at it again. I rolled back to php 7.2 (from 7.4) and the error goes away. So it would appear to be something in the server's php settings which triggers the "trying to access array offset on value of type null" error.

[s4069b]

I have had a bit of time to try getting task reminders working again - because they are still not working even with crontab setup.
We're on Jethro 2.29.1, so I assume the fix mentioned here has been applied.

I've run task_reminder.php with --verbose and the script appears to run, picks up whether task notifications are enabled or not etc. All good. But it returns an empty array for this: "$reminders = Abstract_Note::getNotifications($minutes);"

So I booted into MYSQL and confirmed that the abstract_note view is defined.

MariaDB [jethro]> SHOW FULL TABLES WHERE table_type = 'VIEW'; +------------------+------------+ | Tables_in_jethro | Table_type | +------------------+------------+ | abstract_note | VIEW | | member | VIEW | | person | VIEW | | person_group | VIEW | +------------------+------------+ 4 rows in set (0.002 sec)

All good. BUT if I select everything in that view... it returns and Empty set.
MariaDB [jethro]> SELECT * FROM abstract_note; Empty set (0.013 sec)

So I took a look at how the abstract note is defined and I see this, which I think is OK.
MariaDB [jethro]> SHOW CREATE VIEW jethro.abstract_note; +---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ | View | Create View | character_set_client | collation_connection | +---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ | abstract_note | CREATE ALGORITHM=UNDEFINED DEFINER=root@localhostSQL SECURITY DEFINER VIEWabstract_noteAS selectan.idASid,an.subjectASsubject,an.detailsASdetails,an.statusASstatus,an.status_last_changedASstatus_last_changed,an.assigneeASassignee,an.assignee_last_changedASassignee_last_changed,an.action_dateASaction_date,an.creatorAScreator,an.createdAScreated,an.editorASeditor,an.editedASedited,an.historyAShistoryfrom_abstract_note anwherean.assignee=getCurrentUserID() and an.status= 'pending' orgetCurrentUserID() = -1 or 48 = (select staff_member.permissions& 48 fromstaff_memberwherestaff_member.id=getCurrentUserID()) | utf8mb4 | utf8mb4_general_ci | +---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ 1 row in set (0.004 sec)

So, I don't know where to look now.
Any ideas appreciated!
Thanks

[s4069b]

Thought I'd have a closer look at the database. Select * from _abstract_note returns all the notes that are stored (whereas Select * from abstract_note returns nothing).

MariaDB [jethro]> SHOW tables;
+----------------------------------+
| Tables_in_jethro |
+----------------------------------+
| _abstract_note |
| _abstract_note_old_backup |
| _disused_cong_headcount |
| _disused_group_headcount |
| _disused_roster_role_assn |
| _person |
| _person_group |
| abstract_note |
| account_congregation_restriction |
| account_group_restriction |
| action_plan |
| action_plan_age_bracket |
| age_bracket |
| attendance_record |
| congregation |
| congregation_headcount |
| congregation_service_component |
| custom_field |
| custom_field_option |
| custom_field_value |
| db_object_lock |
| family |
| family_note |
| family_photo |
| member |
| note_comment |
| note_template |
| note_template_field |
| person |
| person_group |
| person_group_category |
| person_group_headcount |
| person_group_membership |
| person_group_membership_status |
| person_note |
| person_photo |
| person_query |
| roster_role |
| roster_role_assignment |
| roster_view |
| roster_view_role_membership |
| roster_view_service_field |
| service |
| service_bible_reading |
| service_component |
| service_component_category |
| service_component_tag |
| service_component_tagging |
| service_item |
| setting |
| staff_member |
+----------------------------------+
51 rows in set (0.001 sec)

[tbar0970]

Yes, _abstract_note is the actual table. abstract_note is a view, filtered according to which notes the current user should actually see. It refers to the @current_user_id variable, which Jethro sets when it runs. That's why you don't see anything if you go into MySQL directly.

The task reminder script doesn't run "as" any particular user, which is what gave rise to the original issue #654 (comment)

Can I suggest you re-run the SQL in #654 (comment) (which was in the 2.30 upgrade) to make sure it's applied.

Then if you open mysql and run

SET @current_user_id = -1;
select count(*) from abstract_note;

you should see some results.

[s4069b]

Thanks. I realised (after my last comment) the difference b/w table/view. Silly of me.
Following your suggestion I do see results in mysql now.

MariaDB [jethro]> select count() from abstract_note;
+----------+
| count() |
+----------+
| 397 |
+----------+
1 row in set (0.014 sec)

So I'm back to the drawing-board with trouble-shooting task_reminder.php.

If I modify task_reminder.php by adding two lines:

$reminders = Abstract_Note::getNotifications($minutes);
$fromText = ifdef('TASK_NOTIFICATION_FROM_NAME', SYSTEM_NAME.' Jethro');
$subject = ifdef('TASK_NOTIFICATION_SUBJECT', 'New notes assigned to you');
print_r($reminders);
require_once JETHRO_ROOT.'/include/emailer.class.php';
echo "made it here";

Then (through Jethro front end) add a note to a user or family.
Then run task_reminder.php with --verbose (run as sudo which crontab would too?).
I see this result in the command line.
/scripts/task_reminder.php --verbose
Array
(
)
made it here

I can't help thinking there's something happening with mysql permissions?

[s4069b]

Embarrassing admission here - IT WAS NOT BROKEN - the task_reminder.php script does not send a reminder to new notes that you create for yourself

I discovered this as I read through the code for the task_reminder.php
this line $reminders = Abstract_Note::getNotifications($minutes); took me into this class - abstract_note.class.php
where the 'get_notifications' function has the comments
// get notes recently marked for action, notes recently assigned to a new person
// and notes which have just reached their action date in the last $minutes minutes
// We operate 10 seconds in the past to allow time for things to setting down
// We ignore notes that a user has assigned to themselves

That last line there. Oh well, I've learned my lesson now.
If you're going to test task_reminder.php - do so by assigning new notes to someone other than yourself!

[tbar0970]

Ah. Yes. It does that on the assumption that if you just assigned the note to yourself, you don't need a reminder!

It'd be good if that bit of business logic was more clearly explained... any thoughts on where to put the explanation?

[s4069b]

The assumption is embarrassingly sensible - and I don't know why it didn't dawn on me when I was trying to test the reminder script.
Perhaps the best place is in the 'header' of the task_reminder.php script?
Or maybe in the task notification section on the system configuration page?

[tbar0970]

TODO: add a note to the "task notification subject" config setting