CiviCRM Core issueshttps://lab.civicrm.org/dev/core/-/issues2019-08-06T20:35:08Zhttps://lab.civicrm.org/dev/core/-/issues/949Export table field size is inadequate for State/Province data values2019-08-06T20:35:08ZorigamiusaExport table field size is inadequate for State/Province data valuesExporting a custom data field of type State/Province crashes with a db error "Data too long for column" if the State/Province has a long name (example: no crash with "Massachusetts", but crash with "District of Columbia"). The temporary ...Exporting a custom data field of type State/Province crashes with a db error "Data too long for column" if the State/Province has a long name (example: no crash with "Massachusetts", but crash with "District of Columbia"). The temporary table is created with a column size of varchar(16), presumably because the State/Province key is stored as a LONG; but the exported quantity is the value, which could be of arbitrary length (and certainly includes examples longer than 16 characters in normal usage).
This appears to be a similar issue to https://lab.civicrm.org/dev/core/issues/181 and https://lab.civicrm.org/dev/core/issues/877.5.17.0https://lab.civicrm.org/dev/core/-/issues/4191Export with a field that has a small DB size but long labels results in fatal2023-06-15T21:16:14ZbgmExport with a field that has a small DB size but long labels results in fatalTo reproduce:
- Create a new custom field of type=alphanum, widget=select list, size=2 (on dmaster, you can edit "Constituent Information > Marital Status" to have a width of 2)
- Enter some field options that are more than 2 characters...To reproduce:
- Create a new custom field of type=alphanum, widget=select list, size=2 (on dmaster, you can edit "Constituent Information > Marital Status" to have a width of 2)
- Enter some field options that are more than 2 characters
Then go to Advanced Search:
- click submit to search,
- export all results
- select fields to export: marital status (or the field you created)
It will cause CiviCRM to fatal because it creates a temp table with a `varchar(4)` but it tries to store the field labels.
It works on CiviCRM 5.55 but not on CiviCRM 5.60 RC.https://lab.civicrm.org/dev/core/-/issues/1293Blank screen with no errors if you choose "Exclude contacts with "do not mail...2019-10-09T09:49:13ZDaveDBlank screen with no errors if you choose "Exclude contacts with "do not mail" privacy" during export where that causes it to have zero contactsFirst, to even see this you have to first apply the fix in #1292.
This seems low priority and is more likely to come up on a testing site just it's kind of confusing and sends you hunting to find the error message when there really isn'...First, to even see this you have to first apply the fix in #1292.
This seems low priority and is more likely to come up on a testing site just it's kind of confusing and sends you hunting to find the error message when there really isn't any. To reproduce:
1. Do a contact search.
2. Select contacts so that none of them have a street address, the Do Not Mail choice, or deceased. E.g. pick a couple contacts where all they have is name and/or email.
3. Choose export from the dropdown.
4. Choose selected fields.
5. Check the box that says `Exclude contacts with "do not mail" privacy, no street address, or who are deceased`.
6. On the next page choose a field like Do Not Mail.
7. When you export you just get a blank screen and there's no errors anywhere. It's because there is no actual error, it's just that there are now zero contacts to export, but it also doesn't give you a zero-length file to download.
I thought it might be browser-specific but it's the same in firefox and chrome.5.18.2https://lab.civicrm.org/dev/core/-/issues/4819Export contacts action: Merge Households option can produce export of househo...2023-12-06T23:20:03ZAndreasandreas.howiller@civiservice.deExport contacts action: Merge Households option can produce export of households that should be excluded by privacy settingOverview
----------------------------------------
The good old "export contacts" action offers the nice features 1. to merge household members into their households and 2. to exclude "do not mail" / no address / deceased contacts. Howeve...Overview
----------------------------------------
The good old "export contacts" action offers the nice features 1. to merge household members into their households and 2. to exclude "do not mail" / no address / deceased contacts. However, mixing the two can lead to probably unwanted exports.
![grafik](/uploads/a7d7f6c48e7bc23ecd5ad2613fb1d5da/grafik.png)
Reproduction steps
----------------------------------------
1. Create a household No-Mail-Household with individual Mail-Me-Person and individual Deceased-Person as household members, make sure all contacts do have an address.
2. Set privacy setting "do not mail" for No-Mail-Household.
3. Set "deceased" for contact Deceased-Person.
4. Apply "export contacts" action to a search result with all three contacts and look at some possible combinations...
Current behaviour / Expected Behaviour?
----------------------------------------
| Contacts selected for export | Merge option | Exclude "do not mail" etc. | Current behaviour | Expected behaviour
| ------ | ------ | ------ | ------ | ------ |
| No-Mail-Household | Do not merge | checked | no contact in export | no contact in export |
| Deceased-Person | Do not merge | checked | no contact in export | no contact in export |
| Mail-Me-Person | Do not merge | checked | only Mail-Me-Person in export | only Mail-Me-Person in export |
| No-Mail-Household, Deceased-Person, Mail-Me-Person | Do not merge | checked | only Mail-Me-Person in export | only Mail-Me-Person in export |
| No-Mail-Household, Deceased-Person, Mail-Me-Person | Do not merge | not checked | all three in export | all three in export |
| **No-Mail-Household**, Deceased-Person, **Mail-Me-Person** | Merge Household Members | checked | **No-Mail-Household in export** | **?** |
| No-Mail-Household, Deceased-Person, Mail-Me-Person | Merge Household Members | not checked | No-Mail-Household in export | No-Mail-Household in export |
| No-Mail-Household, Deceased-Person | Merge Household Members | not checked | No-Mail-Household in export | No-Mail-Household in export |
| No-Mail-Household, Deceased-Person | Merge Household Members | checked | no contact in export | no contact in export |
| No-Mail-Household, Mail-Me-Person | Merge Household Members | not checked | No-Mail-Household in export | No-Mail-Household in export |
| **No-Mail-Household**, **Mail-Me-Person** | Merge Household Members | checked | **No-Mail-Household in export** | **?** |
Summary
----------------------------------------
I can create an export with a household that should explicitly not appear in an export according to the export setting - this is obviously not a good idea.
But what would be a better behaviour? No contact at all or Mail-Me-Person as an individual? Perhaps the latter makes more sense?
Environment information
----------------------------------------
* CiviCRM 5.69.alpha1 on D10 and CiviCRM 5.64.4 on WP6.4.1https://lab.civicrm.org/dev/core/-/issues/4400Export of contacts when filtering on custom field exports all contacts in dat...2023-07-06T06:37:41ZtjhellmannExport of contacts when filtering on custom field exports all contacts in databaseOverview
----------------------------------------
When I filter contacts from Advanced Search and select a custom field to filter on, in the next screen, the correct list and number of records comes up. From there if I select All [number...Overview
----------------------------------------
When I filter contacts from Advanced Search and select a custom field to filter on, in the next screen, the correct list and number of records comes up. From there if I select All [number of records] records and select Export contacts, the next screen also shows the correct number of contacts from the previous screen. But when I click 'continue' to export the records, the export contains all the records in the database. This does not happen when the list is filtered for non custom fields.
Reproduction steps
----------------------------------------
1. Visit Advanced Search
1. Select a custom field value as search criteria and click search
1. See expected search results. Now click All Records and Export Contacts.
2. On the next screen see that the expected number of contacts say they will be in the export. Click continue and all the contacts in the database are in the exported file.
Environment information
----------------------------------------
* __CiviCRM:__ 5.62.1
* __PHP:__ 8.0.29
* __CMS:__ Drupal 9.5.9
* __Database:__ MySQL 5.7.39https://lab.civicrm.org/dev/core/-/issues/3751Export fails for contact reference of type multi-select2024-03-17T09:15:14ZKurund JalmiExport fails for contact reference of type multi-selectFor a multi-select custom field when multiple contacts are selected with long names then the export fails.
This happens because temp export table is created with a varchar 255 column. I feel it should be changed to type `text`.For a multi-select custom field when multiple contacts are selected with long names then the export fails.
This happens because temp export table is created with a varchar 255 column. I feel it should be changed to type `text`.https://lab.civicrm.org/dev/core/-/issues/3195Exporting "Spouse of" or other relationship with matching labels only exports...2023-12-17T05:03:31ZtommyboboExporting "Spouse of" or other relationship with matching labels only exports the A>B relationshipWhen exporting a contact and their related contacts, the relationship type dropdown truncates all the "duplicate" relationship labels. So when you a user selects "Spouse Of" it only exports the A>B relationship.
If you were to export Jo...When exporting a contact and their related contacts, the relationship type dropdown truncates all the "duplicate" relationship labels. So when you a user selects "Spouse Of" it only exports the A>B relationship.
If you were to export John Doe(A) and James Doe(B), who are spouses, the export would be rendered as
| First | Last | Spouse of First | Spouse of Last |
| ------ | ------ | ------ | ------ |
| John | Doe | James | Doe |
| James | Doe | | |
This is an issue if you are trying to export a list of donors and spouses. If the primary donor is the B spouse you will end up with no spouse exported.
This appears to be an old issue.
https://civicrm.stackexchange.com/questions/27001/exporting-spouses-only-exports-one-side-of-relationshiphttps://lab.civicrm.org/dev/core/-/issues/3187Exporting Activity Contacts Field Mapping page display just shows Assignee2024-01-22T05:03:20ZBarijohnExporting Activity Contacts Field Mapping page display just shows AssigneeOverview
----------------------------------------
When using field mapping to display an activity export the only name shown is the Assignee no matter which option you choose of With, Assigned, Added by. However the actual export is corr...Overview
----------------------------------------
When using field mapping to display an activity export the only name shown is the Assignee no matter which option you choose of With, Assigned, Added by. However the actual export is correct. This means people think it is wrong and stop at this point.
Conversation in Stack Exchange here: https://civicrm.stackexchange.com/questions/41274/missing-data-when-exporting-activities-from-search-results/41275
PR That might be connected is https://github.com/civicrm/civicrm-core/pull/21595
Tested on DMaster Demo5.50.alpha1
Reproduction steps
----------------------------------------
1. Create an Activity with a target and assignee. (I used meeting)
2. Search for activities using With or Added By.
3. Select Export activities from search page
4. Choose Field Mapping then choose Display Name/First Name/Last Name.
5. Preview displays the Assignee name.
6. Export shows the correct result.
Current behaviour
----------------------------------------
![Screenshot_2022-04-21_at_13.29.13](/uploads/f12bb0441e1b35692d781a3d7656510b/Screenshot_2022-04-21_at_13.29.13.png)
![Screenshot_2022-04-21_at_13.29.59](/uploads/a82fafcf91dd0472b7d5ad7c9566f6c0/Screenshot_2022-04-21_at_13.29.59.png)
Expected behaviour
----------------------------------------
The Display Mapping should show Margaret Smith.
Environment information
----------------------------------------
DMaster Demo5.50.alpha1https://lab.civicrm.org/dev/core/-/issues/2873Exporting related contact's information on a contact that is a subtype fails2024-02-02T05:03:23Zfabian_SYSTOPIAExporting related contact's information on a contact that is a subtype failsOverview
----------------------------------------
There is an old bug described on the old issue tracker that prevents related contact information to be exported via the UI if one of the contacts is a subtype.
https://issues.civicrm.org/...Overview
----------------------------------------
There is an old bug described on the old issue tracker that prevents related contact information to be exported via the UI if one of the contacts is a subtype.
https://issues.civicrm.org/jira/browse/CRM-16693
Reproduction steps
----------------------------------------
1. Search for any contacts, e.g. a subtype
1. Select at least one contact and choose "export contacts"
1. choose select fields for export
1. select display name or anything else of the contact (will be exported correctly)
1. select a field of a related contact (relationship) e.g. display name
Current behaviour
----------------------------------------
If one of the contacts is a contact subtype or the relationship is defined to be only allowed among subtypes the exported columns won't contain any data (data of the "main contact" will be exported correctly).
Expected behaviour
----------------------------------------
Data of related contact should be exported as well.
Environment information
----------------------------------------
* Browser: all
* CiviCRM: Since 4.6.x still occuring in 5.35.x
Comments
----------------------------------------
We may have funding to fix this bug and would appreciate an estimate from the core team.Monish DebMonish Debhttps://lab.civicrm.org/dev/core/-/issues/2645Exporting in localized civicrm crashes due to long translation for preferred_...2021-07-02T01:45:06ZakosgaraiExporting in localized civicrm crashes due to long translation for preferred_mail_format varchar(8) field.Overview
----------------------------------------
The contact csv export fails on Hungarian crm language, if the preferred_mail_format of the contact is "Both". [Chat link](https://chat.civicrm.org/civicrm/pl/o3q1qo78r3ne5d8dunjse9sttr)
...Overview
----------------------------------------
The contact csv export fails on Hungarian crm language, if the preferred_mail_format of the contact is "Both". [Chat link](https://chat.civicrm.org/civicrm/pl/o3q1qo78r3ne5d8dunjse9sttr)
Reproduction steps
----------------------------------------
1. Have a contact with preferred_mail_format = Both.
1. On Administer > Localization > Languages, Currency, Locations form (admin/setting/localization) update the Default Language to Hungarian.
1. On the Search > Find Contacts form search for the contact with the Both preferred_mail_format.
1. Select the contact and choose the Export contacts action (Kapcsolatok exportálása).
1. Choose Export PRIMARY fields (Elsődleges mezők exportálása) and Do not merge (Ne egyesítse) on the Export options (Export opciók) screen. Click to continue (Tovább).
1. Got an error "**DB Error: unknown error**".
Current behaviour
----------------------------------------
Currently it produces an error page instead of the csv. The following lines appeared in the log:
```
Fatal Error Details = array:3 [
"message" => "DB Error: unknown error"
"code" => null
"exception" => PEAR_Exception {#1254
#cause: DB_Error {#1252
+error_message_prefix: ""
+mode: 16
+level: 1024
+code: -1
+message: "DB Error: unknown error"
+userinfo: """
\n
INSERT INTO civicrm_tmp_d_export_047c092bed1926927fd304aec370141b (`id`, `civicrm_primary_id`, `contact_type`, `contact_sub_type`, `do_not_email`, `do_not_phone`, `do_not_mail`, `do_not_sms`, `do_
not_trade`, `is_opt_out`, `legal_identifier`, `external_identifier`, `sort_name`, `display_name`, `nick_name`, `legal_name`, `image_url`, `preferred_communication_method`, `preferred_language`, `preferred
_mail_format`, `hash`, `contact_source`, `first_name`, `middle_name`, `last_name`, `prefix_id`, `suffix_id`, `formal_title`, `communication_style_id`, `email_greeting_id`, `postal_greeting_id`, `addressee
_id`, `job_title`, `gender_id`, `birth_date`, `is_deceased`, `deceased_date`, `household_name`, `organization_name`, `sic_code`, `user_unique_id`, `current_employer_id`, `contact_is_deleted`, `created_dat
e`, `modified_date`, `addressee`, `email_greeting`, `postal_greeting`, `current_employer`, `location_type`, `address_id`, `street_address`, `street_number`, `street_number_suffix`, `street_name`, `street_
unit`, `supplemental_address_1`, `supplemental_address_2`, `supplemental_address_3`, `city`, `postal_code_suffix`, `postal_code`, `geo_code_1`, `geo_code_2`, `manual_geo_code`, `address_name`, `master_id`
, `county`, `state_province`, `country`, `phone`, `phone_ext`, `phone_type_id`, `phone_type`, `email`, `on_hold`, `is_bulkmail`, `signature_text`, `signature_html`, `im_provider`, `im`, `openid`, `world_r
egion`, `url`, `groups`, `tags`, `notes`)\n
VALUES (1,'1','Organization','','','','','','','','','','Default Organization','Default Organization','','Default Organization','','','','Mindkettő','','','','','','','','','','','','','','','',''
,'','','Default Organization','','','','','','2021-06-09 11:57:52','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','fixme.domainemail@example.org','Nem','','','','',
'','','','','','','')\n
[nativecode=1406 ** Data too long for column 'preferred_mail_format' at row 1]
...
```
Expected behaviour
----------------------------------------
The csv has to be generated and has to be downloadable.
Environment information
----------------------------------------
* __CiviCRM:__ _5.37.1_
* __CMS:__ _Drupal 8_
Comments
----------------------------------------
As I see, it is the similar issue to this one https://lab.civicrm.org/dev/core/-/issues/2164 but with a different field. The preferred_mail_format is a varchar(8) field and it is translated before the insertion to the temp table. The translated "Both" is "Mindkettő" in Hungarian so that it doesn't fit to the varchar(8) field. I would like to create a patch for this issue, but i'm not sure how to fix. As far as i see, the column definitions are generated [here](https://github.com/civicrm/civicrm-core/blob/5db0bc3c1f54eaca4307f103a73bda596ae914d6/CRM/Export/BAO/ExportProcessor.php#L1439-L1509). An obvious solution could be to change the varchar(n) to text, but i'm not sure that it is the best one.5.40.0https://lab.civicrm.org/dev/core/-/issues/2505CiviReport does not localize custom fields of type Number2023-02-16T13:30:23ZjaapjansmaCiviReport does not localize custom fields of type Number**How to reproduce**
1. Set localization settings: **decimal separator** to `,` and **thousand separator** to `.`
2. Add a custom group for cases
3. Add a custom field of **Type** `Number` and **Field type** `Text`
4. Enter a value such...**How to reproduce**
1. Set localization settings: **decimal separator** to `,` and **thousand separator** to `.`
2. Add a custom group for cases
3. Add a custom field of **Type** `Number` and **Field type** `Text`
4. Enter a value such as `1,234.56` which is localized `1.234,56`
5. Create a new CiviCase detail report. Check the column to display the custom field.
**Expected result**
Value of the custom field displayed as `1.234,56`
**Actual result**
Value of custom field is displayed as `1234.56` (note the decimal separator and the missing thousand separator).
**Comment**
The display value of custom number fields is also broken on the manage case screen.
We have a function `CRM_Utils_Money::formatLocaleNumericRoundedForDefaultCurrency` to format money fields. However we do not have such a function for number fields with a decimal. So basically nowhere in CiviCRM a number field is formatted according to localization settings. Unless the smarty modifier `|crmNumberFormat` is used in the template.
A fix should probably happen in `CRM_Core_BAO_CustomField::formatDisplayValue`, see https://github.com/civicrm/civicrm-core/blob/master/CRM/Core/BAO/CustomField.php#L1187
However I am not exactly sure how to fix this as I could not find a number formatting function such as we have for the money field. Does such a function exists and if so where do I find it?5.38.0https://lab.civicrm.org/dev/core/-/issues/2504CiviReport does not localize custom date fields when exporting to CSV2021-04-09T13:32:59ZjaapjansmaCiviReport does not localize custom date fields when exporting to CSVCiviReport does not localize custom fields of type Date
**How to reproduce**
1. Set localization settings: **Date Format Complete date** to `%d-%m-%Y`
2. Add a custom group for cases
3. Add a custom field of Type Date and no time.
4. E...CiviReport does not localize custom fields of type Date
**How to reproduce**
1. Set localization settings: **Date Format Complete date** to `%d-%m-%Y`
2. Add a custom group for cases
3. Add a custom field of Type Date and no time.
4. Enter a value such as 31-04-2021 (1st of april 2021)
5. Create a new CiviCase detail report. Check the column to display the custom field.
6. Export the report to CSV
**Expected result**
Value of the custom field displayed as `01-04-2021` as it is shown in the report and according to the localization setting.
**Actual result**
Value of custom field is displayed as `2021-04-01` which is ISO format.
**Comment**
The format for iso format is hard coded in: https://github.com/civicrm/civicrm-core/blob/master/CRM/Report/Utils/Report.php#L270
I am not sure if that is the desired behavior. I dont think so as the custom fields of type money are localized into a CSV export. So I would argue that the date should also be formatted according to the localization settings.https://lab.civicrm.org/dev/core/-/issues/2469Export of custom multiple choice options to CSV files limited to 32 characters2023-07-18T16:44:17ZbkeevilExport of custom multiple choice options to CSV files limited to 32 charactersA user is trying to export data from an advanced query on custom fields to a CSV file using the "Export contacts" action. This fails with a backtrace which is attached below. The error message is:
```
#6 /var/www/libertarian/vendor/pear...A user is trying to export data from an advanced query on custom fields to a CSV file using the "Export contacts" action. This fails with a backtrace which is attached below. The error message is:
```
#6 /var/www/libertarian/vendor/pear/db/DB/mysqli.php(936): DB_common->raiseError(-1, NULL, NULL, "\nINSERT INTO civicrm_tmp_d_export_1d387c75256673080ad64346b3c095c7 (`id`, `l...", "1406 ** Data too long for column 'custom_49' at row 13")
```
The `custom_49` field referred to in the error is a select list with a set of multiple choice options, some of which are up to 55 characters long.
The bug is that the temporary table that is being constructed to service the export to CSV is constructing the column as VARCHAR(32) while the text value of the multiple choice options in civicrm_option_value is a VARCHAR(512)
### Details
If I select the schema of the table `civicrm_tmp_d_export_1d387c75256673080ad64346b3c095c7` it reports the table structure as:
```
CREATE TABLE `civicrm_tmp_d_export_c3a74c8fb66b720f55e2661dab58716b` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`last_name` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
`first_name` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
`nick_name` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
`street_address` varchar(96) COLLATE utf8_unicode_ci DEFAULT NULL,
`city` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
`email` varchar(254) COLLATE utf8_unicode_ci DEFAULT NULL,
`phone` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
`sort_name` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
`custom_61` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`custom_50` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
`custom_51` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
`custom_68` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
`custom_58` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`custom_49` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_street_address` (`street_address`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
```
Indeed it is trying to insert the text value of an option into a temporary table and several of those text options are over 32 characters in length.
```
SELECT label,CHAR_LENGTH(label) as length FROM civicrm.civicrm_option_value WHERE (option_group_id=103) AND (CHAR_LENGTH(label) > 32)
```
returns
```
| label | length |
|---------------------------------------------------------|--------|
| 003 - Aurora–Oak Ridges–Richmond Hill | 37 |
| 005 - Barrie–Springwater–Oro-Medonte | 36 |
| 035 - Haliburton–Kawartha Lakes–Brock | 37 |
| 039 - Hamilton West–Ancaster–Dundas | 35 |
| 040 - Hastings–Lennox and Addington | 35 |
| 052 - Leeds–Grenville–Thousand Islands and Rideau Lakes | 55 |
| 061 - Mississauga East–Cooksville | 33 |
| 073 - Northumberland–Peterborough South | 39 |
| 102 - Stormont–Dundas–South Glengarry | 37 |
```
The full backtrace may be relevant:
```
backTrace
#0 /var/www/libertarian/vendor/civicrm/civicrm-core/CRM/Core/Error.php(148): CRM_Core_Error::backtrace()
#1 /var/www/libertarian/vendor/pear/pear-core-minimal/src/PEAR.php(944): CRM_Core_Error::handle(Object(DB_Error))
#2 /var/www/libertarian/vendor/pear/db/DB.php(997): PEAR_Error->__construct("DB Error: unknown error", -1, 16, (Array:2), "\nINSERT INTO civicrm_tmp_d_export_1d387c75256673080ad64346b3c095c7 (`id`, `l...")
#3 /var/www/libertarian/vendor/pear/pear-core-minimal/src/PEAR.php(575): DB_Error->__construct(-1, 16, (Array:2), "\nINSERT INTO civicrm_tmp_d_export_1d387c75256673080ad64346b3c095c7 (`id`, `l...")
#4 /var/www/libertarian/vendor/pear/pear-core-minimal/src/PEAR.php(223): PEAR->_raiseError(Object(DB_mysqli), NULL, -1, 16, (Array:2), "\nINSERT INTO civicrm_tmp_d_export_1d387c75256673080ad64346b3c095c7 (`id`, `l...", "DB_Error", TRUE)
#5 /var/www/libertarian/vendor/pear/db/DB/common.php(1928): PEAR->__call("raiseError", (Array:7))
#6 /var/www/libertarian/vendor/pear/db/DB/mysqli.php(936): DB_common->raiseError(-1, NULL, NULL, "\nINSERT INTO civicrm_tmp_d_export_1d387c75256673080ad64346b3c095c7 (`id`, `l...", "1406 ** Data too long for column 'custom_49' at row 13")
#7 /var/www/libertarian/vendor/pear/db/DB/mysqli.php(406): DB_mysqli->mysqliRaiseError()
#8 /var/www/libertarian/vendor/pear/db/DB/common.php(1234): DB_mysqli->simpleQuery("\nINSERT INTO civicrm_tmp_d_export_1d387c75256673080ad64346b3c095c7 (`id`, `l...")
#9 /var/www/libertarian/vendor/civicrm/civicrm-packages/DB/DataObject.php(2696): DB_common->query("\nINSERT INTO civicrm_tmp_d_export_1d387c75256673080ad64346b3c095c7 (`id`, `l...")
#10 /var/www/libertarian/vendor/civicrm/civicrm-packages/DB/DataObject.php(1829): DB_DataObject->_query("\nINSERT INTO civicrm_tmp_d_export_1d387c75256673080ad64346b3c095c7 (`id`, `l...")
#11 /var/www/libertarian/vendor/civicrm/civicrm-core/CRM/Core/DAO.php(457): DB_DataObject->query("\nINSERT INTO civicrm_tmp_d_export_1d387c75256673080ad64346b3c095c7 (`id`, `l...")
#12 /var/www/libertarian/vendor/civicrm/civicrm-core/CRM/Core/DAO.php(1563): CRM_Core_DAO->query("\nINSERT INTO civicrm_tmp_d_export_1d387c75256673080ad64346b3c095c7 (`id`, `l...", TRUE)
#13 /var/www/libertarian/vendor/civicrm/civicrm-core/CRM/Export/BAO/Export.php(365): CRM_Core_DAO::executeQuery("\nINSERT INTO civicrm_tmp_d_export_1d387c75256673080ad64346b3c095c7 (`id`, `l...")
#14 /var/www/libertarian/vendor/civicrm/civicrm-core/CRM/Export/BAO/Export.php(199): CRM_Export_BAO_Export::writeDetailsToTable(Object(CRM_Export_BAO_ExportProcessor), (Array:100), (Array:14))
#15 /var/www/libertarian/vendor/civicrm/civicrm-core/CRM/Export/Form/Map.php(142): CRM_Export_BAO_Export::exportComponents(TRUE, (Array:118), (Array:8), "`sort_name` asc", (Array:14), NULL, 1, " contact_a.id IN ( 1546,1415,641,27,1688,1504,1701,42,650,626,56,1509,1537,15...", "civicrm_contact", 0, 0, (Array:11), "AND")
#16 /var/www/libertarian/vendor/civicrm/civicrm-core/CRM/Core/Form.php(513): CRM_Export_Form_Map->postProcess()
#17 /var/www/libertarian/vendor/civicrm/civicrm-core/CRM/Core/StateMachine.php(144): CRM_Core_Form->mainProcess()
#18 /var/www/libertarian/vendor/civicrm/civicrm-core/CRM/Core/QuickForm/Action/Next.php(43): CRM_Core_StateMachine->perform(Object(CRM_Contact_Export_Form_Map), "next", "Next")
#19 /var/www/libertarian/vendor/civicrm/civicrm-packages/HTML/QuickForm/Controller.php(203): CRM_Core_QuickForm_Action_Next->perform(Object(CRM_Contact_Export_Form_Map), "next")
#20 /var/www/libertarian/vendor/civicrm/civicrm-packages/HTML/QuickForm/Page.php(103): HTML_QuickForm_Controller->handle(Object(CRM_Contact_Export_Form_Map), "next")
#21 /var/www/libertarian/vendor/civicrm/civicrm-core/CRM/Core/Controller.php(347): HTML_QuickForm_Page->handle("next")
#22 /var/www/libertarian/vendor/civicrm/civicrm-core/CRM/Core/Invoke.php(312): CRM_Core_Controller->run((Array:4), (Array:0))
#23 /var/www/libertarian/vendor/civicrm/civicrm-core/CRM/Core/Invoke.php(68): CRM_Core_Invoke::runItem((Array:13))
#24 /var/www/libertarian/vendor/civicrm/civicrm-core/CRM/Core/Invoke.php(36): CRM_Core_Invoke::_invoke((Array:4))
#25 /var/www/libertarian/web/modules/contrib/civicrm/src/Civicrm.php(88): CRM_Core_Invoke::invoke((Array:4))
#26 /var/www/libertarian/web/modules/contrib/civicrm/src/Controller/CivicrmController.php(80): Drupal\civicrm\Civicrm->invoke((Array:4))
#27 [internal function](): Drupal\civicrm\Controller\CivicrmController->main((Array:4), "")
#28 /var/www/libertarian/web/core/lib/Drupal/Core/EventSubscriber/EarlyRenderingControllerWrapperSubscriber.php(123): call_user_func_array((Array:2), (Array:2))
#29 /var/www/libertarian/web/core/lib/Drupal/Core/Render/Renderer.php(573): Drupal\Core\EventSubscriber\EarlyRenderingControllerWrapperSubscriber->Drupal\Core\EventSubscriber\{closure}()
#30 /var/www/libertarian/web/core/lib/Drupal/Core/EventSubscriber/EarlyRenderingControllerWrapperSubscriber.php(124): Drupal\Core\Render\Renderer->executeInRenderContext(Object(Drupal\Core\Render\RenderContext), Object(Closure))
#31 /var/www/libertarian/web/core/lib/Drupal/Core/EventSubscriber/EarlyRenderingControllerWrapperSubscriber.php(97): Drupal\Core\EventSubscriber\EarlyRenderingControllerWrapperSubscriber->wrapControllerExecutionInRenderContext((Array:2), (Array:2))
#32 /var/www/libertarian/vendor/symfony/http-kernel/HttpKernel.php(151): Drupal\Core\EventSubscriber\EarlyRenderingControllerWrapperSubscriber->Drupal\Core\EventSubscriber\{closure}()
#33 /var/www/libertarian/vendor/symfony/http-kernel/HttpKernel.php(68): Symfony\Component\HttpKernel\HttpKernel->handleRaw(Object(Symfony\Component\HttpFoundation\Request), 1)
#34 /var/www/libertarian/web/core/lib/Drupal/Core/StackMiddleware/Session.php(57): Symfony\Component\HttpKernel\HttpKernel->handle(Object(Symfony\Component\HttpFoundation\Request), 1, TRUE)
#35 /var/www/libertarian/web/core/lib/Drupal/Core/StackMiddleware/KernelPreHandle.php(47): Drupal\Core\StackMiddleware\Session->handle(Object(Symfony\Component\HttpFoundation\Request), 1, TRUE)
#36 /var/www/libertarian/web/core/modules/page_cache/src/StackMiddleware/PageCache.php(106): Drupal\Core\StackMiddleware\KernelPreHandle->handle(Object(Symfony\Component\HttpFoundation\Request), 1, TRUE)
#37 /var/www/libertarian/web/core/modules/page_cache/src/StackMiddleware/PageCache.php(85): Drupal\page_cache\StackMiddleware\PageCache->pass(Object(Symfony\Component\HttpFoundation\Request), 1, TRUE)
#38 /var/www/libertarian/web/core/lib/Drupal/Core/StackMiddleware/ReverseProxyMiddleware.php(47): Drupal\page_cache\StackMiddleware\PageCache->handle(Object(Symfony\Component\HttpFoundation\Request), 1, TRUE)
#39 /var/www/libertarian/web/core/lib/Drupal/Core/StackMiddleware/NegotiationMiddleware.php(52): Drupal\Core\StackMiddleware\ReverseProxyMiddleware->handle(Object(Symfony\Component\HttpFoundation\Request), 1, TRUE)
#40 /var/www/libertarian/vendor/stack/builder/src/Stack/StackedHttpKernel.php(23): Drupal\Core\StackMiddleware\NegotiationMiddleware->handle(Object(Symfony\Component\HttpFoundation\Request), 1, TRUE)
#41 /var/www/libertarian/web/core/lib/Drupal/Core/DrupalKernel.php(708): Stack\StackedHttpKernel->handle(Object(Symfony\Component\HttpFoundation\Request), 1, TRUE)
#42 /var/www/libertarian/web/index.php(19): Drupal\Core\DrupalKernel->handle(Object(Symfony\Component\HttpFoundation\Request))
#43 {main}
```https://lab.civicrm.org/dev/core/-/issues/2466Can't export link custom fields2021-03-22T11:56:31ZtschuettlerCan't export link custom fieldsOverview
----------------------------------------
It is currently not possible to export custom fields of type link with length above around 110 chars. The added HTML markup pushes the length of the string above 256 chars in the export c...Overview
----------------------------------------
It is currently not possible to export custom fields of type link with length above around 110 chars. The added HTML markup pushes the length of the string above 256 chars in the export column for this field.
I'm not aware, that this is a recent regression.
Reproduction steps
----------------------------------------
1. Create a custom field `url` of type **Link**.
1. Add a reasonably long `url` to a contact (e.g. `https://stage.example.org/system/files/webform/way_too_long_url_that_still_fits_in_a_link_custom_field_but_fails_to_export.jpg`).
1. Export this Contact outputting the custom field `url`
1. Got an error "**Fatal error: DB error**".
Current behaviour
----------------------------------------
Export fails if it contains long links.
```
Mar 18 17:47:44 [error] $Fatal Error Details = Array
(
[callback] => Array
(
[0] => CRM_Core_Error
[1] => exceptionHandler
)
[code] => -1
[message] => DB Error: unknown error
[mode] => 16
[debug_info] =>
INSERT INTO civicrm_tmp_d_export_4cf7fb7cd8210f2fe06326f57a33263f (`id`, `custom_23`)
VALUES (1,'<a href=\"https://stage.example.org/system/files/webform/way_too_long_url_that_still_fits_in_a_link_custom_field_but_fails_to_export.jpg\" target=\"_blank\">https://stage.example.org/system/files/webform/way_too_long_url_that_still_fits_in_a_link_custom_field_but_fails_to_export.jpg</a>')
[nativecode=1406 ** Data too long for column 'custom_23' at row 1]
[type] => DB_Error
[user_info] =>
INSERT INTO civicrm_tmp_d_export_4cf7fb7cd8210f2fe06326f57a33263f (`id`, `custom_23`)
VALUES (1,'<a href=\"https://stage.example.org/system/files/webform/way_too_long_url_that_still_fits_in_a_link_custom_field_but_fails_to_export.jpg\" target=\"_blank\">https://stage.example.org/system/files/webform/way_too_long_url_that_still_fits_in_a_link_custom_field_but_fails_to_export.jpg</a>')
[nativecode=1406 ** Data too long for column 'custom_23' at row 1]
[to_string] => [db_error: message="DB Error: unknown error" code=-1 mode=callback callback=CRM_Core_Error::exceptionHandler prefix="" info="
INSERT INTO civicrm_tmp_d_export_4cf7fb7cd8210f2fe06326f57a33263f (`id`, `custom_23`)
VALUES (1,'<a href=\"https://stage.example.org/system/files/webform/way_too_long_url_that_still_fits_in_a_link_custom_field_but_fails_to_export.jpg\" target=\"_blank\">https://stage.example.org/system/files/webform/way_too_long_url_that_still_fits_in_a_link_custom_field_but_fails_to_export.jpg</a>')
[nativecode=1406 ** Data too long for column 'custom_23' at row 1]"]
)
Mar 18 17:47:44 [debug] $backTrace = #0 /opt/buildkit/build/dmaster/sites/all/modules/civicrm/CRM/Core/Error.php(942): CRM_Core_Error::backtrace("backTrace", TRUE)
#1 /opt/buildkit/build/dmaster/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(922): CRM_Core_Error::exceptionHandler(Object(DB_Error))
#2 /opt/buildkit/build/dmaster/sites/all/modules/civicrm/vendor/pear/db/DB.php(997): PEAR_Error->__construct("DB Error: unknown error", -1, 16, (Array:2), "\nINSERT INTO civicrm_tmp_d_export_4cf7fb7cd8210f2fe06326f57a33263f (`id`, `c...")
#3 /opt/buildkit/build/dmaster/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(575): DB_Error->__construct(-1, 16, (Array:2), "\nINSERT INTO civicrm_tmp_d_export_4cf7fb7cd8210f2fe06326f57a33263f (`id`, `c...")
#4 /opt/buildkit/build/dmaster/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(223): PEAR::_raiseError(Object(DB_mysqli), NULL, -1, 16, (Array:2), "\nINSERT INTO civicrm_tmp_d_export_4cf7fb7cd8210f2fe06326f57a33263f (`id`, `c...", "DB_Error", TRUE)
#5 /opt/buildkit/build/dmaster/sites/all/modules/civicrm/vendor/pear/db/DB/common.php(1928): PEAR->__call("raiseError", (Array:7))
#6 /opt/buildkit/build/dmaster/sites/all/modules/civicrm/vendor/pear/db/DB/mysqli.php(936): DB_common->raiseError(-1, NULL, NULL, "\nINSERT INTO civicrm_tmp_d_export_4cf7fb7cd8210f2fe06326f57a33263f (`id`, `c...", "1406 ** Data too long for column 'custom_23' at row 1")
#7 /opt/buildkit/build/dmaster/sites/all/modules/civicrm/vendor/pear/db/DB/mysqli.php(406): DB_mysqli->mysqliRaiseError()
#8 /opt/buildkit/build/dmaster/sites/all/modules/civicrm/vendor/pear/db/DB/common.php(1234): DB_mysqli->simpleQuery("\nINSERT INTO civicrm_tmp_d_export_4cf7fb7cd8210f2fe06326f57a33263f (`id`, `c...")
#9 /opt/buildkit/build/dmaster/sites/all/modules/civicrm/packages/DB/DataObject.php(2696): DB_common->query("\nINSERT INTO civicrm_tmp_d_export_4cf7fb7cd8210f2fe06326f57a33263f (`id`, `c...")
#10 /opt/buildkit/build/dmaster/sites/all/modules/civicrm/packages/DB/DataObject.php(1829): DB_DataObject->_query("\nINSERT INTO civicrm_tmp_d_export_4cf7fb7cd8210f2fe06326f57a33263f (`id`, `c...")
#11 /opt/buildkit/build/dmaster/sites/all/modules/civicrm/CRM/Core/DAO.php(454): DB_DataObject->query("\nINSERT INTO civicrm_tmp_d_export_4cf7fb7cd8210f2fe06326f57a33263f (`id`, `c...")
#12 /opt/buildkit/build/dmaster/sites/all/modules/civicrm/CRM/Core/DAO.php(1565): CRM_Core_DAO->query("\nINSERT INTO civicrm_tmp_d_export_4cf7fb7cd8210f2fe06326f57a33263f (`id`, `c...", TRUE)
#13 /opt/buildkit/build/dmaster/sites/all/modules/civicrm/CRM/Export/BAO/Export.php(365): CRM_Core_DAO::executeQuery("\nINSERT INTO civicrm_tmp_d_export_4cf7fb7cd8210f2fe06326f57a33263f (`id`, `c...")
#14 /opt/buildkit/build/dmaster/sites/all/modules/civicrm/CRM/Export/BAO/Export.php(199): CRM_Export_BAO_Export::writeDetailsToTable(Object(CRM_Export_BAO_ExportProcessor), (Array:1), (Array:1))
#15 /opt/buildkit/build/dmaster/sites/all/modules/civicrm/CRM/Export/Form/Map.php(142): CRM_Export_BAO_Export::exportComponents(TRUE, (Array:1), (Array:7), "`sort_name` asc", (Array:1), NULL, 1, " contact_a.id IN ( 202 ) ", "civicrm_contact", 0, 0, (Array:6), "AND")
#16 /opt/buildkit/build/dmaster/sites/all/modules/civicrm/CRM/Core/Form.php(526): CRM_Export_Form_Map->postProcess()
#17 /opt/buildkit/build/dmaster/sites/all/modules/civicrm/CRM/Core/StateMachine.php(144): CRM_Core_Form->mainProcess()
#18 /opt/buildkit/build/dmaster/sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Next.php(43): CRM_Core_StateMachine->perform(Object(CRM_Contact_Export_Form_Map), "next", "Next")
#19 /opt/buildkit/build/dmaster/sites/all/modules/civicrm/packages/HTML/QuickForm/Controller.php(203): CRM_Core_QuickForm_Action_Next->perform(Object(CRM_Contact_Export_Form_Map), "next")
#20 /opt/buildkit/build/dmaster/sites/all/modules/civicrm/packages/HTML/QuickForm/Page.php(103): HTML_QuickForm_Controller->handle(Object(CRM_Contact_Export_Form_Map), "next")
#21 /opt/buildkit/build/dmaster/sites/all/modules/civicrm/CRM/Core/Controller.php(347): HTML_QuickForm_Page->handle("next")
#22 /opt/buildkit/build/dmaster/sites/all/modules/civicrm/CRM/Core/Invoke.php(313): CRM_Core_Controller->run((Array:4), (Array:0))
#23 /opt/buildkit/build/dmaster/sites/all/modules/civicrm/CRM/Core/Invoke.php(69): CRM_Core_Invoke::runItem((Array:13))
#24 /opt/buildkit/build/dmaster/sites/all/modules/civicrm/CRM/Core/Invoke.php(36): CRM_Core_Invoke::_invoke((Array:4))
#25 /opt/buildkit/build/dmaster/sites/all/modules/civicrm/drupal/civicrm.module(458): CRM_Core_Invoke::invoke((Array:4))
#26 /opt/buildkit/build/dmaster/includes/menu.inc(527): civicrm_invoke("contact", "search", "advanced")
#27 /opt/buildkit/build/dmaster/index.php(21): menu_execute_active_handler()
#28 {main}
```
Expected behaviour
----------------------------------------
Successfull export of long links.
I can think of two options:
1. Keep exporting the URL with HTML markup: Enlarge the field for links in the temporary table for exports.
1. **Breaking change** Just export the URL without markup
* I currently can't think of a reason to have HTML markup in the CSV.
Environment information
----------------------------------------
<!-- Some of the items below may not be relevant for every bug - if in doubt please include more information than you think is neccessary. -->
Does not seem to be related:
* __Browser:__ _Firefox 86.0.1_
* __CiviCRM:__ _Master_
* __PHP:__ _7.4_
* __CMS:__ _Drupal 7.78_
* __Database:__ _MySQL 8.0.23_
* __Web Server:__ _Apache 2.4_
Comments
----------------------------------------
Problem seems to be identical to the one solved in dev/core#1787.5.37.0tschuettlertschuettlerhttps://lab.civicrm.org/dev/core/-/issues/2413Invalid ODT generated when tokens contain XML reserved symbols2023-07-16T05:03:23ZLunarInvalid ODT generated when tokens contain XML reserved symbolsOverview
----------------------------------------
The *Print/Merge* feature creates broken ODT file using an ODT template with tokens if the data contains an ampersand (&). The `&` character gets written directly to the `content.xml`, th...Overview
----------------------------------------
The *Print/Merge* feature creates broken ODT file using an ODT template with tokens if the data contains an ampersand (&). The `&` character gets written directly to the `content.xml`, therefore creating invalid XML, and therefore an invalid ODT.
Reproduction steps
----------------------------------------
1. Create a contact with `Test & Test` in *Adressee*.
1. Create an ODT template containing `{contact.addressee}`.
1. Open the *Print/Merge* activity on the previously created contact.
1. Select the previously created ODT as template.
1. Perform the merge.
Current behaviour
----------------------------------------
The resulting ODT file is broken.
Expected behaviour
----------------------------------------
A valid ODT file should be generated (eg. reserved XML symbols should be properly escaped).
Environment information
----------------------------------------
* __CiviCRM:__ 5.34.0
* __PHP:__ 7.3
* __CMS:__ Drupal 9.1Björn EndresBjörn Endreshttps://lab.civicrm.org/dev/core/-/issues/2242CiviCRM Export, Saved Export Field Mapping that contains custom fields which ...2021-02-01T22:03:53Zjustinfreeman (Agileware)CiviCRM Export, Saved Export Field Mapping that contains custom fields which have been disabled or deleted are still loaded as "clear" values and cause the export download to fail with "DB Error: no such field"CiviCRM Export, Saved Export Field Mapping that contains custom fields which have been disabled or deleted are still loaded as "clear" values and cause the export download to fail with "DB Error: no such field".
This is complicated furt...CiviCRM Export, Saved Export Field Mapping that contains custom fields which have been disabled or deleted are still loaded as "clear" values and cause the export download to fail with "DB Error: no such field".
This is complicated further because the **user cannot remove the "clear" fields from the field export list at all** and as a result, renders the Saved Export Field Mapping defunct.
The disabled or deleted fields are listed on the field export page as "clear", see screenshot below.
![Screenshot_20201210_165641](/uploads/099da3249d6eb3734fab60d6adaacf58/Screenshot_20201210_165641.png)
Agileware Ref: CIVICRM-16275.35.0https://lab.civicrm.org/dev/core/-/issues/2164Exporting in localized civicrm crashes due to long translations in columns T_...2021-06-09T13:57:48ZnieloExporting in localized civicrm crashes due to long translations in columns T_BooleanOverview
----------------------------------------
Export tmp tables are created with varchar(16) for T_BOOLEAN types. This may be sufficient for the short-worded language english but if translated we get longer export values. So this sho...Overview
----------------------------------------
Export tmp tables are created with varchar(16) for T_BOOLEAN types. This may be sufficient for the short-worded language english but if translated we get longer export values. So this should be 32 or more
To fix for example change ExportProcessor.php, L1454, to
return "`$fieldName` varchar(64)";5.37.0https://lab.civicrm.org/dev/core/-/issues/1835Export preview not showing data that exists2023-03-28T05:03:44ZStoobExport preview not showing data that existsIn both of these attached cases, the data exists in CiviCRM, and the data is contained in the CSV downloaded upon export. But it is blank in the preview.
![preview](/uploads/82c5b38bb88effd5bb67b6842470111c/preview.png)
![export](/upl...In both of these attached cases, the data exists in CiviCRM, and the data is contained in the CSV downloaded upon export. But it is blank in the preview.
![preview](/uploads/82c5b38bb88effd5bb67b6842470111c/preview.png)
![export](/uploads/a2e1ee8affbeaff3d1377572b080b7f2/export.png)https://lab.civicrm.org/dev/core/-/issues/1830Export not returning addresses properly by location type2023-04-18T05:03:18ZStoobExport not returning addresses properly by location type**Issue 1:**
Select Primary address and is returning the oldest address instead. This is what the underlying data is like:
```
MariaDB [civicrm_crm]> select id,is_primary,contact_id,street_address,location_type_id from civicrm_address w...**Issue 1:**
Select Primary address and is returning the oldest address instead. This is what the underlying data is like:
```
MariaDB [civicrm_crm]> select id,is_primary,contact_id,street_address,location_type_id from civicrm_address where contact_id=5205;
+-------+------------+------------+---------------------+------------------+
| id | is_primary | contact_id | street_address | location_type_id |
+-------+------------+------------+---------------------+------------------+
| 1470 | 0 | 5205 | 317 SW 6th Ave #400 | 2 |
| 13705 | 1 | 5205 | 9755 SW Barnes Road | 3 |
| 13706 | 0 | 5205 | 9755 SW Barnes Road | 6 |
+-------+------------+------------+---------------------+------------------+
3 rows in set (0.00 sec)
```
And this is what is returned.
![neen](/uploads/66248d7e4974b423fe806aaa4acb493a/neen.png)
**Issue 2:**
Select state by Location Main/Work and shows nothing, even though exists. Note: selecting State by Primary location does return results.
![state](/uploads/735e25cdfbc479715a88b32894a8d3dc/state.png)
![main](/uploads/dea0cbd07ecc6fda7687aa3d622b66f2/main.png)
Currently using CiviCRM 5.26.2https://lab.civicrm.org/dev/core/-/issues/1725Contact export is very slow, creates huge temporary tables2023-04-26T05:03:21ZBobSContact export is very slow, creates huge temporary tablesOverview
----------------------------------------
When exporting contacts with the default output fields, a query is performed which can generate huge temporary files, even for databases that are not very greatly populated. I was in fac...Overview
----------------------------------------
When exporting contacts with the default output fields, a query is performed which can generate huge temporary files, even for databases that are not very greatly populated. I was in fact alerted to this problem when an export of only 80 contacts was performed that maxed out our 40GB of spare disk space and crashed the entire server. The following is a study of this single query, which I'll call Query1, and how it can be optimized to eliminate the problem.
Exponential Growth of Temporary Tables
----------------------------------------
The issue is caused by the joins of multiple tables, each having multiple rows for each of the exported contacts, combined with the fact MySQL creates temporary tables if the queries contain GROUP_CONCAT (among other causes). Although the query returns only one row per exported contact, the temporary table that is built in the process has a row count which is a sum of products.
e.g. Assume a join of tables A, B and C and a WHERE clause which includes N contacts. The temporary table generated will contain
`(A1*B1*C1) + (A2*B2*C2) … + (AN*BN*CN)` rows, where A1, for example, is the number of rows in table A for contact 1. Each additional exported field having multiple rows per contact causes exponential growth in the temporary table size, while the number of exported contacts increases the table size arithmetically.
A Tale of Two Contacts
----------------------------------------
To illustrate a real-world case, the row counts for the tables of interest of two contacts in my organization are as follows:
| Table | Contact_2 | Contact_1016 |
| ------ | ------ | ------ |
| civicrm_group_contact| 91 | 39 |
| civicrm_group_contact_cache | 30 | 17 |
| civicrm_tag | 18 | 6 |
| civicrm_note | 6 | 11 |
| civicrm_email | 4 | 3 |
| civicrm_phone | 2 | 4 |
| civicrm_address | 2 | 3 |
| PRODUCT | 4,717,440 | 1,575,288 |
| SUM OF PRODUCTS | ... | 6,292,728 |
Query1, shown along with its slow query log in [Queries](#queries), was captured from an export operation with default output fields of these two contacts. That query joins each of these tables, among others. "SUM OF PRODUCTS" indicates the number of rows in the resulting temporary table.
An export of 2000 similar contacts rather than just these 2 would be expected to generate a temporary table 1000 times this size (6 billion rows)!
Query Optimization
----------------
Query1 was modified to create the functionally equivalent Query2 (also shown with its slow query log in [Queries](#queries)), employing three subqueries to eliminate the product terms due to the first three DB tables shown above. Performance was vastly improved as shown in the following table:
| Parameters from Slow Query Log | Query1 | Query2 |
| ------ | ------ | ------ |
| Execution Time (S) | 67 | 0.38 |
| Temp file size (MB) | 4,473 | 30 |
| Rows Examined | 29,579,813 | 138,786 |
Just to be clear, these measurements were made running the query for only the two contacts described above.
Recommended Action
-------------------------
When building queries for export operations, subqueries should be used in the join clause for any table which might return more that one row per contact to ensure that it contributes only one row per contact to the temporary table. At the very least, this should be done for those fields that might contain numerous rows per contact, such as groups (smart and non-smart), tags, and notes.
Additional Issues with Contact Export
--------------------------
I would like additionally to note the following secondary issues with contact export:
- Smart groups are not rebuilt upon an export with the 'groups' field. If civicrm_group_contact_cache has been cleared (e.g. due to adding a contact), before running an export, the export will not include any smart groups in the ‘groups’ column.
- The groups, tags and notes filed are each truncated to a length of 1024 characters due to the default limitation of GROUP_CONCAT. This limit could be increased by executing “SET SESSION group_concat_max_len = 100000;”, for example.
- With the default output fields, is_primary is not specified in the email/phone/address join clauses. The results returned are therefore indeterminate for each of these fields when more than one row exists per contact.
Test Environment
--------------------------
- Query1 was captured using CiviCRM 5.24.3.
- To eliminate tainted timing measurements due to possible throttling or over-allocation by my VPS service provider, the above measurements were made on a freshly built Ubuntu 18.04 virtual machine on my local PC where only MariaDB 10.4 was installed (no Apache, PHP, CMS), along with a copy of my site’s Drupal database. Increasing RAM to 12GB from 8GB and parameter tuning using MySQLTuner led to only modest improvements in the execution speed.
References
--------------------------
- [Internal Temporary Table Use in MySQL](https://dev.mysql.com/doc/refman/5.7/en/internal-temporary-tables.html)
- [Original report on civicrm.stackexchange.com](https://civicrm.stackexchange.com/questions/35255/contact-export-runs-very-slowly-creates-huge-temporary-db-files)
Queries
------------
<details>
<summary><b>Query1:</b> Slow query log and SQL</summary>
```sql
# Thread_id: 53 Schema: drupal QC_hit: No
# Query_time: 67.547173 Lock_time: 0.000418 Rows_sent: 2 Rows_examined: 29579813
# Rows_affected: 0 Bytes_sent: 12505
# Tmp_tables: 5 Tmp_disk_tables: 2 Tmp_table_sizes: 4472889344
# Full_scan: No Full_join: Yes Tmp_table: Yes Tmp_table_on_disk: Yes
# Filesort: Yes Filesort_on_disk: No Merge_passes: 0 Priority_queue: No
SELECT
contact_a.id AS contact_id,
contact_a.contact_type AS `contact_type`,
contact_a.contact_sub_type AS `contact_sub_type`,
contact_a.sort_name AS `sort_name`,
contact_a.display_name AS `display_name`,
contact_a.do_not_email AS `do_not_email`,
contact_a.do_not_phone AS `do_not_phone`,
contact_a.do_not_mail AS `do_not_mail`,
contact_a.do_not_sms AS `do_not_sms`,
contact_a.do_not_trade AS `do_not_trade`,
contact_a.is_opt_out AS `is_opt_out`,
contact_a.legal_identifier AS `legal_identifier`,
contact_a.external_identifier AS `external_identifier`,
contact_a.nick_name AS `nick_name`,
contact_a.legal_name AS `legal_name`,
contact_a.image_URL AS `image_URL`,
contact_a.preferred_communication_method AS `preferred_communication_method`,
contact_a.preferred_language AS `preferred_language`,
contact_a.preferred_mail_format AS `preferred_mail_format`,
contact_a.hash AS `hash`,
contact_a.source AS `contact_source`,
contact_a.first_name AS `first_name`,
contact_a.middle_name AS `middle_name`,
contact_a.last_name AS `last_name`,
contact_a.prefix_id AS `prefix_id`,
contact_a.suffix_id AS `suffix_id`,
contact_a.formal_title AS `formal_title`,
contact_a.communication_style_id AS `communication_style_id`,
contact_a.email_greeting_id AS email_greeting_id,
contact_a.postal_greeting_id AS postal_greeting_id,
contact_a.addressee_id AS addressee_id,
contact_a.job_title AS `job_title`,
contact_a.gender_id AS `gender_id`,
contact_a.birth_date AS `birth_date`,
contact_a.is_deceased AS `is_deceased`,
contact_a.deceased_date AS `deceased_date`,
contact_a.household_name AS `household_name`,
IF(contact_a.contact_type = 'Individual',
NULL,
contact_a.organization_name) AS organization_name,
contact_a.sic_code AS `sic_code`,
contact_a.user_unique_id AS `user_unique_id`,
contact_a.employer_id AS `current_employer_id`,
contact_a.is_deleted AS `contact_is_deleted`,
contact_a.created_date AS `created_date`,
contact_a.modified_date AS `modified_date`,
contact_a.addressee_display AS addressee_display,
contact_a.addressee_custom AS addressee_custom,
contact_a.email_greeting_display AS email_greeting_display,
contact_a.email_greeting_custom AS email_greeting_custom,
contact_a.postal_greeting_display AS postal_greeting_display,
contact_a.postal_greeting_custom AS postal_greeting_custom,
IF(contact_a.contact_type = 'Individual',
contact_a.organization_name,
NULL) AS current_employer,
civicrm_address.id AS address_id,
civicrm_location_type.id AS location_type_id,
civicrm_location_type.name AS `location_type`,
civicrm_address.street_address AS `street_address`,
civicrm_address.street_number AS `street_number`,
civicrm_address.street_number_suffix AS `street_number_suffix`,
civicrm_address.street_name AS `street_name`,
civicrm_address.street_unit AS `street_unit`,
civicrm_address.supplemental_address_1 AS `supplemental_address_1`,
civicrm_address.supplemental_address_2 AS `supplemental_address_2`,
civicrm_address.supplemental_address_3 AS `supplemental_address_3`,
civicrm_address.city AS `city`,
civicrm_address.postal_code_suffix AS `postal_code_suffix`,
civicrm_address.postal_code AS `postal_code`,
civicrm_address.geo_code_1 AS `geo_code_1`,
civicrm_address.geo_code_2 AS `geo_code_2`,
civicrm_address.manual_geo_code AS `manual_geo_code`,
civicrm_address.name AS `address_name`,
civicrm_address.master_id AS `master_id`,
civicrm_address.county_id AS county_id,
civicrm_address.state_province_id AS state_province_id,
civicrm_address.country_id AS country_id,
civicrm_phone.id AS phone_id,
civicrm_phone.phone_type_id AS `phone_type_id`,
civicrm_phone.phone AS `phone`,
civicrm_phone.phone_ext AS `phone_ext`,
civicrm_email.id AS email_id,
civicrm_email.email AS `email`,
civicrm_email.on_hold AS `on_hold`,
civicrm_email.is_bulkmail AS `is_bulkmail`,
civicrm_email.signature_text AS `signature_text`,
civicrm_email.signature_html AS `signature_html`,
civicrm_im.id AS im_id,
civicrm_im.provider_id AS `im_provider`,
civicrm_im.provider_id AS provider_id,
civicrm_im.name AS `im`,
civicrm_openid.id AS openid_id,
civicrm_openid.openid AS `openid`,
civicrm_worldregion.id AS worldregion_id,
civicrm_worldregion.name AS `world_region`,
civicrm_website.id AS website_id,
civicrm_website.url AS `url`,
CONCAT_WS(',',
GROUP_CONCAT(DISTINCT IF(civicrm_group_contact.status = 'Added',
civicrm_group_contact.group_id,
'')),
GROUP_CONCAT(DISTINCT civicrm_group_contact_cache.group_id)) AS `groups`,
GROUP_CONCAT(DISTINCT (civicrm_tag.name)) AS tags,
GROUP_CONCAT(DISTINCT (civicrm_note.note)) AS notes
FROM
civicrm_contact contact_a
LEFT JOIN
civicrm_address ON (contact_a.id = civicrm_address.contact_id)
LEFT JOIN
civicrm_country ON (civicrm_address.country_id = civicrm_country.id)
LEFT JOIN
civicrm_email ON (contact_a.id = civicrm_email.contact_id)
LEFT JOIN
civicrm_phone ON (contact_a.id = civicrm_phone.contact_id)
LEFT JOIN
civicrm_im ON (contact_a.id = civicrm_im.contact_id)
LEFT JOIN
civicrm_openid ON (civicrm_openid.contact_id = contact_a.id)
LEFT JOIN
civicrm_location_type ON civicrm_address.location_type_id = civicrm_location_type.id
LEFT JOIN
civicrm_group_contact ON contact_a.id = civicrm_group_contact.contact_id
LEFT JOIN
civicrm_group_contact_cache ON contact_a.id = civicrm_group_contact_cache.contact_id
LEFT JOIN
civicrm_entity_tag ON (civicrm_entity_tag.entity_table = 'civicrm_contact'
AND civicrm_entity_tag.entity_id = contact_a.id)
LEFT JOIN
civicrm_note ON (civicrm_note.entity_table = 'civicrm_contact'
AND contact_a.id = civicrm_note.entity_id)
LEFT JOIN
civicrm_worldregion ON civicrm_country.region_id = civicrm_worldregion.id
LEFT JOIN
civicrm_group_contact `civicrm_group_contact-5e9e3db3d6d9a` ON (contact_a.id = `civicrm_group_contact-5e9e3db3d6d9a`.contact_id
AND `civicrm_group_contact-5e9e3db3d6d9a`.status IN ('Added'))
LEFT JOIN
civicrm_tag ON civicrm_entity_tag.tag_id = civicrm_tag.id
LEFT JOIN
civicrm_website ON contact_a.id = civicrm_website.contact_id
WHERE
(((`civicrm_group_contact-5e9e3db3d6d9a`.group_id IN ('581'))))
AND (contact_a.is_deleted = 0)
AND contact_a.is_deleted != 1
AND contact_a.id IN (2,1016)
GROUP BY contact_a.id
LIMIT 0 , 100000;
```
</details>
<details>
<summary><b>Query2:</b> Slow query log and SQL</summary>
```sql
# Thread_id: 53 Schema: drupal QC_hit: No
# Query_time: 0.382351 Lock_time: 0.000376 Rows_sent: 2 Rows_examined: 138786
# Rows_affected: 0 Bytes_sent: 12525
# Tmp_tables: 8 Tmp_disk_tables: 5 Tmp_table_sizes: 30154752
# Full_scan: No Full_join: Yes Tmp_table: Yes Tmp_table_on_disk: Yes
# Filesort: Yes Filesort_on_disk: No Merge_passes: 0 Priority_queue: No
SELECT
contact_a.id AS contact_id,
contact_a.contact_type AS `contact_type`,
contact_a.contact_sub_type AS `contact_sub_type`,
contact_a.sort_name AS `sort_name`,
contact_a.display_name AS `display_name`,
contact_a.do_not_email AS `do_not_email`,
contact_a.do_not_phone AS `do_not_phone`,
contact_a.do_not_mail AS `do_not_mail`,
contact_a.do_not_sms AS `do_not_sms`,
contact_a.do_not_trade AS `do_not_trade`,
contact_a.is_opt_out AS `is_opt_out`,
contact_a.legal_identifier AS `legal_identifier`,
contact_a.external_identifier AS `external_identifier`,
contact_a.nick_name AS `nick_name`,
contact_a.legal_name AS `legal_name`,
contact_a.image_URL AS `image_URL`,
contact_a.preferred_communication_method AS `preferred_communication_method`,
contact_a.preferred_language AS `preferred_language`,
contact_a.preferred_mail_format AS `preferred_mail_format`,
contact_a.hash AS `hash`,
contact_a.source AS `contact_source`,
contact_a.first_name AS `first_name`,
contact_a.middle_name AS `middle_name`,
contact_a.last_name AS `last_name`,
contact_a.prefix_id AS `prefix_id`,
contact_a.suffix_id AS `suffix_id`,
contact_a.formal_title AS `formal_title`,
contact_a.communication_style_id AS `communication_style_id`,
contact_a.email_greeting_id AS email_greeting_id,
contact_a.postal_greeting_id AS postal_greeting_id,
contact_a.addressee_id AS addressee_id,
contact_a.job_title AS `job_title`,
contact_a.gender_id AS `gender_id`,
contact_a.birth_date AS `birth_date`,
contact_a.is_deceased AS `is_deceased`,
contact_a.deceased_date AS `deceased_date`,
contact_a.household_name AS `household_name`,
IF(contact_a.contact_type = 'Individual',
NULL,
contact_a.organization_name) AS organization_name,
contact_a.sic_code AS `sic_code`,
contact_a.user_unique_id AS `user_unique_id`,
contact_a.employer_id AS `current_employer_id`,
contact_a.is_deleted AS `contact_is_deleted`,
contact_a.created_date AS `created_date`,
contact_a.modified_date AS `modified_date`,
contact_a.addressee_display AS addressee_display,
contact_a.addressee_custom AS addressee_custom,
contact_a.email_greeting_display AS email_greeting_display,
contact_a.email_greeting_custom AS email_greeting_custom,
contact_a.postal_greeting_display AS postal_greeting_display,
contact_a.postal_greeting_custom AS postal_greeting_custom,
IF(contact_a.contact_type = 'Individual',
contact_a.organization_name,
NULL) AS current_employer,
civicrm_address.id AS address_id,
civicrm_location_type.id AS location_type_id,
civicrm_location_type.name AS `location_type`,
civicrm_address.street_address AS `street_address`,
civicrm_address.street_number AS `street_number`,
civicrm_address.street_number_suffix AS `street_number_suffix`,
civicrm_address.street_name AS `street_name`,
civicrm_address.street_unit AS `street_unit`,
civicrm_address.supplemental_address_1 AS `supplemental_address_1`,
civicrm_address.supplemental_address_2 AS `supplemental_address_2`,
civicrm_address.supplemental_address_3 AS `supplemental_address_3`,
civicrm_address.city AS `city`,
civicrm_address.postal_code_suffix AS `postal_code_suffix`,
civicrm_address.postal_code AS `postal_code`,
civicrm_address.geo_code_1 AS `geo_code_1`,
civicrm_address.geo_code_2 AS `geo_code_2`,
civicrm_address.manual_geo_code AS `manual_geo_code`,
civicrm_address.name AS `address_name`,
civicrm_address.master_id AS `master_id`,
civicrm_address.county_id AS county_id,
civicrm_address.state_province_id AS state_province_id,
civicrm_address.country_id AS country_id,
civicrm_phone.id AS phone_id,
civicrm_phone.phone_type_id AS `phone_type_id`,
civicrm_phone.phone AS `phone`,
civicrm_phone.phone_ext AS `phone_ext`,
civicrm_email.id AS email_id,
civicrm_email.email AS `email`,
civicrm_email.on_hold AS `on_hold`,
civicrm_email.is_bulkmail AS `is_bulkmail`,
civicrm_email.signature_text AS `signature_text`,
civicrm_email.signature_html AS `signature_html`,
civicrm_im.id AS im_id,
civicrm_im.provider_id AS `im_provider`,
civicrm_im.provider_id AS provider_id,
civicrm_im.name AS `im`,
civicrm_openid.id AS openid_id,
civicrm_openid.openid AS `openid`,
civicrm_worldregion.id AS worldregion_id,
civicrm_worldregion.name AS `world_region`,
civicrm_website.id AS website_id,
civicrm_website.url AS `url`,
/*
CONCAT_WS(',',
GROUP_CONCAT(DISTINCT IF(civicrm_group_contact.status = 'Added', civicrm_group_contact.group_id, '')),
GROUP_CONCAT(DISTINCT civicrm_group_contact_cache.group_id)) AS `groups`,
*/
CONCAT_WS(',', sel_group_list.group_list, sel_smartgroup_list.group_list) `groups`,
#GROUP_CONCAT(DISTINCT (civicrm_tag.name)) AS tags,
tags,
GROUP_CONCAT(DISTINCT (civicrm_note.note)) AS notes
FROM
civicrm_contact contact_a
LEFT JOIN
civicrm_address ON (contact_a.id = civicrm_address.contact_id)
LEFT JOIN
civicrm_country ON (civicrm_address.country_id = civicrm_country.id)
LEFT JOIN
civicrm_email ON (contact_a.id = civicrm_email.contact_id)
LEFT JOIN
civicrm_phone ON (contact_a.id = civicrm_phone.contact_id)
LEFT JOIN
civicrm_im ON (contact_a.id = civicrm_im.contact_id)
LEFT JOIN
civicrm_openid ON (civicrm_openid.contact_id = contact_a.id)
LEFT JOIN
civicrm_location_type ON civicrm_address.location_type_id = civicrm_location_type.id
LEFT JOIN
civicrm_group_contact ON contact_a.id = civicrm_group_contact.contact_id
#LEFT JOIN civicrm_group_contact_cache ON contact_a.id = civicrm_group_contact_cache.contact_id
#LEFT JOIN civicrm_entity_tag ON (civicrm_entity_tag.entity_table = 'civicrm_contact' AND civicrm_entity_tag.entity_id = contact_a.id)
LEFT JOIN civicrm_note ON (civicrm_note.entity_table = 'civicrm_contact' AND contact_a.id = civicrm_note.entity_id)
LEFT JOIN civicrm_worldregion ON civicrm_country.region_id = civicrm_worldregion.id
#LEFT JOIN civicrm_group_contact `civicrm_group_contact-5e9e3db3d6d9a` ON (contact_a.id = `civicrm_group_contact-5e9e3db3d6d9a`.contact_id AND `civicrm_group_contact-5e9e3db3d6d9a`.status IN ('Added'))
#LEFT JOIN civicrm_tag ON civicrm_entity_tag.tag_id = civicrm_tag.id
LEFT JOIN (
SELECT contact_id, CONCAT_WS(',', GROUP_CONCAT(cgc.group_id)) group_list FROM civicrm_group_contact cgc
LEFT JOIN civicrm_contact contact_a
ON contact_a.id = cgc.contact_id AND cgc.status = "ADDED"
WHERE contact_a.id in (2,1016)
GROUP BY contact_a.id
) AS sel_group_list ON contact_a.id = sel_group_list.contact_id
LEFT JOIN (
SELECT contact_id, CONCAT_WS(',', GROUP_CONCAT(cgcc.group_id)) group_list FROM civicrm_group_contact_cache cgcc
LEFT JOIN civicrm_contact contact_a
ON contact_a.id = cgcc.contact_id
WHERE contact_a.id in (2,1016)
GROUP BY contact_a.id
) AS sel_smartgroup_list ON contact_a.id = sel_smartgroup_list.contact_id
LEFT JOIN (
SELECT contact_a.id, cet.entity_id, GROUP_CONCAT(DISTINCT(ct.name)) tags FROM civicrm_contact contact_a
LEFT JOIN civicrm_entity_tag cet ON contact_a.id = cet.entity_id
LEFT JOIN civicrm_tag ct ON cet.tag_id = ct.id
WHERE contact_a.id in (2,1016)
GROUP BY contact_a.id
) AS sel_tag_list ON contact_a.id = sel_tag_list.entity_id
LEFT JOIN civicrm_website ON contact_a.id = civicrm_website.contact_id
WHERE
#(((`civicrm_group_contact-5e9e3db3d6d9a`.group_id IN ('581')))) AND
(contact_a.is_deleted = 0)
AND contact_a.is_deleted != 1
AND contact_a.id IN (2,1016)
GROUP BY contact_a.id
LIMIT 0 , 100000;
```
</details>