Development issueshttps://lab.civicrm.org/groups/dev/-/issues2023-09-12T05:08:13Zhttps://lab.civicrm.org/dev/core/-/issues/4539Very slow query when sending mailings via CiviMail2023-09-12T05:08:13ZwmortadaVery slow query when sending mailings via CiviMailOverview
----------------------------------------
We have come across an issue where the database connection was timing out due to slow, long-running queries when the Send Scheduled Mailings job was running. We became aware of this when...Overview
----------------------------------------
We have come across an issue where the database connection was timing out due to slow, long-running queries when the Send Scheduled Mailings job was running. We became aware of this when we set a maximum execution time limit of 3 minutes for MySQL queries. Mailings for one of our clients were not being sent because the database connection was timing out.
As a workaround, we have increased the maximum execution time to 10 minutes and reduced the Mailer Batch Limit and Mailer Job Size to 1,000. However, we'd like to fix the underlying issue that the queries are running so slowly.
When sending a mailing CiviCRM runs a query of the form:
```sql
SELECT r.contact_id, r.email_id, r.phone_id
FROM civicrm_mailing_recipients r
INNER JOIN civicrm_contact c on
(c.id = r.contact_id
AND c.is_deleted = 0
AND c.is_deceased = 0
AND c.do_not_email = 0
AND c.is_opt_out = 0
)
INNER JOIN civicrm_email e ON (r.email_id = e.id AND e.on_hold = 0)
WHERE r.mailing_id = 3271
LIMIT 18000, 1000
```
There are no indices for `is_deceased`, `do_not_email` or `is_opt_out` so this query is very slow. (There is an index for `is_deleted`. There should be an index for `is_deceased` but this appears to be missing from this site.)
We have tested adding these indices in a development environment to see what difference it would make and this increase the speed of the query by a factor of one thousand:
- 20,000 rows with no indices = 80 seconds
- 20,000 rows with indices = 0.085 seconds
Reproduction steps
----------------------------------------
This is likely to only affect sites with a large number of contacts and large number of rows in the `civicrm_mailing_recipients` table (in this case 46,000 contacts and 9 million rows respectively).
1. Set a maximum execution time for MySQL of 3 minutes (say)
2. Create a mailing to a large group of contacts (>20,000)
3. Schedule the mailing to send
4. Send Scheduled Mailings job fails with error: "Finished execution of Send Scheduled Mailings with result: Failure, Error message: DB Error: unknown error"
Current behaviour
----------------------------------------
No indices ~~`is_deceased`~~, `do_not_email` or `is_opt_out` in `civicrm_contact` so queries run very slowly.
Expected behaviour
----------------------------------------
Indices for the above fields so that the query runs quickly.
Environment information
----------------------------------------
* __CiviCRM:__ _5.62_
* __PHP:__ _8.1__
* __CMS:__ _N/A_
* __Database:__ _MySQL 5.7.43_
Comments
----------------------------------------
There are other speed improvements in #4045 but these relate to the speed of the user interface rather than the speed of sending emails.
Perhaps this can also help to [reduce our collective CO2 emissions](https://civicrm.org/blog/systopia/beginning-green-how-can-we-make-civicrm-more-sustainable)?https://lab.civicrm.org/dev/core/-/issues/4536Report improvements2023-09-23T05:02:21ZyashodhaReport improvements_Primary Membership_ filter option in _Membership Details_ report has options that don't make sense.
![dddsdsd](/uploads/0e74060c879cbaf416c85e8eb1067134/dddsdsd.png)
Remove options that don't make sense and add option to choose _All_..._Primary Membership_ filter option in _Membership Details_ report has options that don't make sense.
![dddsdsd](/uploads/0e74060c879cbaf416c85e8eb1067134/dddsdsd.png)
Remove options that don't make sense and add option to choose _All_ as well.yashodhayashodhahttps://lab.civicrm.org/dev/core/-/issues/4535View Membership lists template contributions along with actual contribution u...2023-08-29T10:36:47ZRichView Membership lists template contributions along with actual contribution under "Related Contributions and Recurring Contributions"This is confusing. The status column is blank, too, so it looks at a glance as though this is a real payment:
![image](/uploads/61a1120ef3c1f389c2dda90bb0bd38f5/image.png)
I propose that template contributions are NOT shown on that pag...This is confusing. The status column is blank, too, so it looks at a glance as though this is a real payment:
![image](/uploads/61a1120ef3c1f389c2dda90bb0bd38f5/image.png)
I propose that template contributions are NOT shown on that page. Agree?https://lab.civicrm.org/dev/core/-/issues/4534Price set discount by date back office issues2023-08-24T18:33:09ZlarsssandergreenPrice set discount by date back office issuesThese are probably not a priority for anyone, but I ran into these while testing @eileen's recent PR, so thought I might as well note them:
1. Discount set shows as a select on edit participant, but only in certain circumstances (haven'...These are probably not a priority for anyone, but I ran into these while testing @eileen's recent PR, so thought I might as well note them:
1. Discount set shows as a select on edit participant, but only in certain circumstances (haven't quite worked this out, but something to do with number of discount sets and/or whether the dates are current or in the past). It also seems to be wrong, showing the current discount set based on the current date, not the one for the participant. Regardless, it shouldn't be changeable here as that does nothing. It should either always show as frozen or never show at all.
2. On the other hand, discount set should always be selectable on Change Selections, with the default set to the discount set that the participant currently has (then if you change from adult to student ticket or something, they still get the discount based on date, but you also can change the discount if needed).
3. Discount set is selectable when registering a single participant from back office, but not on the Register search task for multiple participants. It should be.https://lab.civicrm.org/dev/core/-/issues/4533Searchkit: "Add new" button text2023-08-24T17:22:23Zmattwiremjw@mjwconsult.co.ukSearchkit: "Add new" button textWhen you enable the "Add New" button you cannot customise the text and it uses the entity name eg. "Add Activity". But if you want to link to a form that does something more specific eg. to create a signature on a petition it would be mu...When you enable the "Add New" button you cannot customise the text and it uses the entity name eg. "Add Activity". But if you want to link to a form that does something more specific eg. to create a signature on a petition it would be much clearer if this button could be customised to say eg. "Add Signature".https://lab.civicrm.org/dev/core/-/issues/4528Entity Reference custom fields: Configurable delete behavior2024-01-17T22:28:23ZjensschuppeEntity Reference custom fields: Configurable delete behavior## Overview
Currently, when entities referenced in a custom field of the type _Entity Reference_ are deleted, the entity reference field gets cleared (set to `NULL`). This is easy and prevents us from database inconsistencies. But this ...## Overview
Currently, when entities referenced in a custom field of the type _Entity Reference_ are deleted, the entity reference field gets cleared (set to `NULL`). This is easy and prevents us from database inconsistencies. But this can lead to problems:
* inconsistencies when the entity reference field is set to be required
* stale/orphaned entities the entity reference field is attached to
* `civicrm_value_*` records with all-empty columns (or are they being taken care of already)
## Example use-case
Imagine a custom entity named _Qualification_ with (among others) an entity reference custom field referencing a _Contact_ entity, which is set to be required; read: A _Contact_ has multiple _Qualifications_.
## Current behaviour
When deleting the contact, the _Qualification_ entity will not be deleted until done so manually. The reference is just being set to `NULL` instead. Those entities will be meaningless without a referenced contact, the required entity reference custom field will be empty, which breaks the semantic contract.
Question: Is the `SET NULL` behavior a FK constraint on the DB table or part of some logic in Core code?
## Proposed behaviour
When deleting the contact, the _Qualification_ entity should be deleted. This is what we'd have configured the entity reference field to behave, as in other scenarios, the current behavior will be totally fine.
So, we'll need a configuration option for _Entity Reference_ custom fields for selecting what should happen upon deletion of the referenced entity. Available options could be (basically MySQL foreign key constraints):
* Remove Reference (`SET NULL`) - current behavior, maybe also the default option
* Cascade Delete (`CASCADE`) - removes the _`civicrm_value_*`_ record and the entity the custom group is attached to
* Restrict Delete (`RESTRICT`) - just as you can't e. g. delete contacts with financial transactions
* Set Default (`SET DEFAULT`) - to be configured on the custom field
* ~~Keep Reference (`NO ACTION`)~~ - might not be a good idea
## Comments
@colemanw and @michaelmcandrew might be interested.
Currently, this can be partly achieved by implementing a `preDelete` event for retrieving and deleting referencing entities.https://lab.civicrm.org/dev/core/-/issues/4527End Date field in Relationship cannot filter on date in join clause APIv42023-08-24T21:58:32ZseamusleeEnd Date field in Relationship cannot filter on date in join clause APIv4In APIv4 when joining onto relationships for example from contact.get you cannot use a date value e.g. 2023-08-23 you get unknown field error. However if you put the string into quotes it works
https://dmaster.demo.civicrm.org/civicrm/a...In APIv4 when joining onto relationships for example from contact.get you cannot use a date value e.g. 2023-08-23 you get unknown field error. However if you put the string into quotes it works
https://dmaster.demo.civicrm.org/civicrm/api4#/explorer/Contact/get?join=%5B%5B%22Relationship%20AS%20relationship%22,%22LEFT%22,null,%5B%22id%22,%22%3D%22,%22relationship.contact_id_a%22%5D,%5B%22relationship.end_date%22,%22%3E%22,%222023-08-23%22%5D%5D%5Dhttps://lab.civicrm.org/dev/core/-/issues/4526Unselecting price set discount set for back office event registration disappe...2023-08-23T02:51:57ZlarsssandergreenUnselecting price set discount set for back office event registration disappears fee block1. Add a discount set to a quick config price set for an event
2. Add new event registration from back office
3. Unselect the default discount set (which you would do if you wanted to charge the person full price)
The fee block disappea...1. Add a discount set to a quick config price set for an event
2. Add new event registration from back office
3. Unselect the default discount set (which you would do if you wanted to charge the person full price)
The fee block disappears, you cannot select any price options. The discount set is automatically re-selected, but the price options do not re-appear.https://lab.civicrm.org/dev/core/-/issues/4525SearchKit: If using Group by Display Name, selecting one contact selects them...2023-08-22T23:47:57ZlarsssandergreenSearchKit: If using Group by Display Name, selecting one contact selects them all (sort of)See https://civicrm.stackexchange.com/questions/45404/searchkit-deselect-with-group-by-option-and-how-to-save-the-sorting
To reproduce:
1. Search Contacts in SK
2. Group by Display Name
3. Select one contact from the table
Observe tha...See https://civicrm.stackexchange.com/questions/45404/searchkit-deselect-with-group-by-option-and-how-to-save-the-sorting
To reproduce:
1. Search Contacts in SK
2. Group by Display Name
3. Select one contact from the table
Observe that all the contacts are selected, though it says only 1 of N.https://lab.civicrm.org/dev/core/-/issues/4522Membership Detail improvements2023-08-24T14:02:52ZyashodhaMembership Detail improvementsIn the _Membership Details_ report,
add in _Columns_ tab:
- Auto-renew (Yes/no)
- Auto-renew Status
add in _Sorting_ tab:
- Start Date (of membership)
- End Date (of membership)
- State
- Auto-renew
- Auto-renew StatusIn the _Membership Details_ report,
add in _Columns_ tab:
- Auto-renew (Yes/no)
- Auto-renew Status
add in _Sorting_ tab:
- Start Date (of membership)
- End Date (of membership)
- State
- Auto-renew
- Auto-renew Statusyashodhayashodhahttps://lab.civicrm.org/dev/core/-/issues/4520Simplify and improve offline event receipt template contribution section details2023-09-23T22:55:28ZlarsssandergreenSimplify and improve offline event receipt template contribution section detailsAn offline event receipt includes the following (in this case, for a pending (pay later) registration):
![image](/uploads/67eefa5dc465c6ee31b71d7523f2c20c/image.png)
Here's a non-pending one:
![image](/uploads/954f9780ed01de7e46519f6b...An offline event receipt includes the following (in this case, for a pending (pay later) registration):
![image](/uploads/67eefa5dc465c6ee31b71d7523f2c20c/image.png)
Here's a non-pending one:
![image](/uploads/954f9780ed01de7e46519f6b36f2dc2b/image.png)
1. I don't think we need to include Registration Date. I don't know when this would be useful information for the recipient. It is almost always going to be the date of the email and even if it isn't (which I think is only possible if you manually change the date while inputting the registration), what purpose does it serve for the recipient? Note that we have Transaction Date just below this and they are going to be the same almost all the time. If it is important to someone that we include both we could do so if they are different, but I don't really see why we need to include Registration Date in an email receipt.
1. Paid By: This should not be included if no payment was recorded, as it is misleading in that case.
1. Total Amount: This should be included and we should show a balance if no payment has been made and the status is pending (pay later) - but not if the status is otherwise, because then we can assume that we aren't charging the person. In this case, we shouldn't include a total at all.
1. Pay later text. This should be included if we register the person as pending (pay later) and this text exists. It looks like there is some attempt to include this in the template, but it doesn't seem to work - I think is_pay_later may not be set for offline registrations.
2. Financial Type: Do we need to include this? In general, I think it doesn't add anything, but I suppose it could be useful to differentiate donations from non-donations. Seems like that could also be misleading though, as only part of the contribution may be tax deductible. I'd lean on the side of removing this.https://lab.civicrm.org/dev/core/-/issues/4519Search kit - if id is not present then the 'select' is misleading2023-09-02T05:12:00ZeileenSearch kit - if id is not present then the 'select' is misleadingIf you create an email search with no `Email ID` field (eg this url civicrm/admin/search#/create/Email?label=emails&params=%7B"version":4,"select":%5B"email","contact_id"%5D,"orderBy":%7B%7D,"where":%5B%5D,"groupBy":%5B%5D,"join":%5B%5D,...If you create an email search with no `Email ID` field (eg this url civicrm/admin/search#/create/Email?label=emails¶ms=%7B"version":4,"select":%5B"email","contact_id"%5D,"orderBy":%7B%7D,"where":%5B%5D,"groupBy":%5B%5D,"join":%5B%5D,"having":%5B%5D%7D )
and then search & select a row they all display as selected - but note that the text says "1 selected of 202 results"
![image](/uploads/a288959b13e588c1cfa9cbfcb29367d9/image.png)
If you take an action the 1 appears
![image](/uploads/fe31fe6460047573d1c832fb89b0e6b7/image.png)
But, it actually acts on all of them for download & seemingly none for the other actions
@colemanw @larssg this is per chat - @larssg thought https://civicrm.stackexchange.com/questions/45404/searchkit-deselect-with-group-by-option-and-how-to-save-the-sorting might relatehttps://lab.civicrm.org/dev/core/-/issues/4518Indicate in additional participant confirmation email when their status is pe...2023-08-24T14:01:24ZlarsssandergreenIndicate in additional participant confirmation email when their status is pending (pay later)When an additional participant receives a confirmation email and their status is pending (pay later), there is nothing to indicate their status in the email, so it looks like they are fully registered. We should include something like:
...When an additional participant receives a confirmation email and their status is pending (pay later), there is nothing to indicate their status in the email, so it looks like they are fully registered. We should include something like:
"Your registration is pending payment."https://lab.civicrm.org/dev/core/-/issues/4517Show a message to let event registrants know that additional participants wil...2023-08-24T14:00:38ZlarsssandergreenShow a message to let event registrants know that additional participants will receive an email confirming their registrationWhen you register additional participants for an event and enter an email for those additional participants, an email confirmation is sent to the additional participants' emails. We should include text on the additional participants scre...When you register additional participants for an event and enter an email for those additional participants, an email confirmation is sent to the additional participants' emails. We should include text on the additional participants screen indicating that this confirmation email will be sent when there is an email field on the page, something like:
"An email confirming registration will be sent to the email address provided below."https://lab.civicrm.org/dev/core/-/issues/4516Rethinking 20 minutes QF session timeouts2023-10-05T15:27:38ZJonGoldRethinking 20 minutes QF session timeoutsThe 20 minute timeout has been a part of Civi for as long as I can remember. It's also caused lost donations, frustrated applicants, and so on. Reading an [article about why short session timeouts aren't helpful](https://www.sjoerdlang...The 20 minute timeout has been a part of Civi for as long as I can remember. It's also caused lost donations, frustrated applicants, and so on. Reading an [article about why short session timeouts aren't helpful](https://www.sjoerdlangkemper.nl/2023/08/16/session-timeout/) made me think about this for Civi.
* What is the original reason? Is it still valid?
* If we lengthen the timeout, do we need to make special arrangements around event registration when there's a maximum number of seats available?https://lab.civicrm.org/dev/core/-/issues/4515FormBuilder: Can't edit Submit button text in Firefox or Safari2023-08-20T16:39:28ZlarsssandergreenFormBuilder: Can't edit Submit button text in Firefox or SafariThis is a minor issue, but editing the Submit button text does not work (you cannot type any characters) in FormBuilder in Firefox (115 on macOS) or Safari. It does work in Chrome.This is a minor issue, but editing the Submit button text does not work (you cannot type any characters) in FormBuilder in Firefox (115 on macOS) or Safari. It does work in Chrome.https://lab.civicrm.org/dev/core/-/issues/4513AdminUI: In 5.64 new basic search shows group names to users who, according t...2024-02-05T21:58:15ZAndy ClarkAdminUI: In 5.64 new basic search shows group names to users who, according to ACLs, don't have access to those groupsThe new basic search ('Find Contacts') in 5.64 that uses SK correctly restricts visibility of contacts for users with access limited by ACLs, but still shows the group names that those users should not be able to see. If the user then se...The new basic search ('Find Contacts') in 5.64 that uses SK correctly restricts visibility of contacts for users with access limited by ACLs, but still shows the group names that those users should not be able to see. If the user then searches those groups, they appear to have zero contacts which is confusing. Users with access limited by ACLs should only be able to see those group names that they have access to. Disabling the AdminUI extension and restoring the old basic search solves the problem - those groups are not then displayed.colemanwcolemanwhttps://lab.civicrm.org/dev/drupal/-/issues/189Epic: Drupal 10 finalization2023-10-30T18:30:20ZtottenEpic: Drupal 10 finalizationThis is a follow-up to #176. Where #176 did the main work of achieving compatibility, this issue is for the final wrap-up to handle Drupal 10 as a publicly visible option.
* [x] Add build types for test/demo infra
* [x] Add public sandb...This is a follow-up to #176. Where #176 did the main work of achieving compatibility, this issue is for the final wrap-up to handle Drupal 10 as a publicly visible option.
* [x] Add build types for test/demo infra
* [x] Add public sandbox on https://civicrm.org/demo (https://d10-master.demo.civicrm.org/)
* [ ] Add to test matrices
* [x] CiviCRM-E2E-Matrix (master)
* [ ] CiviCRM-E2E-Matrix (stable/rc)
* [ ] Fix distinctive test failures on D10
* [x] Concurrent mail tests (https://github.com/civicrm/civicrm-core/pull/27072)
* [ ] Authx tests (*these may be non-blockers since D9 has similar issues -- but we should look again*)
* [ ] Add to `Installation Guide` ([Requirements](https://docs.civicrm.org/installation/en/latest/general/requirements/), [Install on Drupal](https://docs.civicrm.org/installation/en/latest/drupal/))
* [x] Add to https://civicrm.org/downloadhttps://lab.civicrm.org/dev/core/-/issues/4512Profile notify on submit for event registration additional participants only ...2023-08-18T21:15:34ZlarsssandergreenProfile notify on submit for event registration additional participants only works if profile is used for primary participantThis one is a little obscure, but thought I might as well note it.
If you set up an event registration using a profile that sends a notification email on submit and you use that profile for the primary participant and the additional par...This one is a little obscure, but thought I might as well note it.
If you set up an event registration using a profile that sends a notification email on submit and you use that profile for the primary participant and the additional participants, everything works as intended. But if the additional participant profile that has the notification is only used for the additional participant and not the primary participant, then no notification will be sent.
This is because we only build the profile fields and send the notification if [notify is TRUE for customPre or customPost here.](https://github.com/civicrm/civicrm-core/blob/e6b439e4eb032b269fc823a7782cc2d8bb9d8304/CRM/Event/BAO/Event.php#L1077). The notification being sent for the additional participant in the case where the notification profile is used for the primary participant is sort of an unintended side effect and it would be better to handle each profile on the registration form separately.https://lab.civicrm.org/dev/core/-/issues/4511Allow multiple 'Added By' for an activity2023-09-08T20:35:49ZyashodhaAllow multiple 'Added By' for an activityThe way some clients are using the CRM, the 'Added By' person(s) is/are really the person(s) responsible for making the activity happen, and the 'Assigned To' are people that participate in the activity but are not directly responsible f...The way some clients are using the CRM, the 'Added By' person(s) is/are really the person(s) responsible for making the activity happen, and the 'Assigned To' are people that participate in the activity but are not directly responsible for it.
The issues is that the CRM only allows ONE Added By, so they rely on some custom contact reference fields 'Partnered With' to supplement the Added By. But this is not a good solution and has many limitations e,g not being able to see the activity in Partner tab etc.
Therefore the best way to move forward is to allow multiple 'Added By' for an activity. The database schema already allows for that with the _activity_contact_ table.
From technical perspective, I reckon it was an oversight to not allow multiple contacts for Contact Source as we had moved civicrm_activity.source_contact_id (single), civicrm_activity_target.contact_id, civicrm_activity_assignee.contact_id (dropped civicrm_activity_target/civicrm_activity_assignee tables) in favor of civicrm_activity_contact table.
Screens that need to be modified:
- Activity tab in the Contact screen
- Activity View screen
- Activity Edit screen
- Activities reports
- Activities resultyashodhayashodha