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/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/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/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/1541HTML entities in iCalendar export2022-06-21T23:27:09ZPhilipp MichaelHTML entities in iCalendar exportOverview
----------------------------------------
iCalendar (iCal) export does not decode html entities.
Reproduction steps
----------------------------------------
1. Create an event or activity with a html description containing speci...Overview
----------------------------------------
iCalendar (iCal) export does not decode html entities.
Reproduction steps
----------------------------------------
1. Create an event or activity with a html description containing special characters like German umlauts or non breaking spaces.
1. Export this entity as ical and open it in a mail client like Microsoft Outlook.
1. Note that text contains html entities like `ü` or `&npsp;`
Current behaviour
----------------------------------------
Example excerpt from iCalendar export:
![ical](/uploads/0ba7a9c6f4c71599601576947f31313d/ical_htmlentity.png)
```
DESCRIPTION:Ein Thema der Fachveranstaltung ist die Digitalisi
erung im Bereich der Agrarwirtschaft\, unter ander
em \;wie die Messung und Speicherung von Daten
 \;landwirtschaftlich genutzt werden kö\;n
nen.\n \n Die Teilnehmer kö\;nnen aschlie&szli
g\;end \;innerhalb von Workshops zum Thema Bio
gasanlage und Energieeffizienz ihr Wissen erweiter
n.
```
Expected behaviour
----------------------------------------
Decode all html entities for safe iCalendar use.
Environment information
----------------------------------------
* __CiviCRM:__ _Master_
* __PHP:__ _7.2_
* __CMS:__ _Drupal 7.30_
Comments
----------------------------------------
This issue was already reported in https://issues.civicrm.org/jira/browse/CRM-13645
Based on this I've created a patch, which fixes the issue for me: [ical_htmlentitydecode.patch](/uploads/730571dc9f2fcb05ad18148637aee58f/ical_htmlentitydecode.patch)
Note that html in iCalendar is fine if the "Alternate Text Representation" points to a "text/html" content portion (see https://tools.ietf.org/html/rfc5545#section-3.2.1 for details).5.52.0https://lab.civicrm.org/dev/core/-/issues/1512Address ID field should be exportable2020-01-10T23:28:27ZJonGoldAddress ID field should be exportableOverview
----------------------------------------
Address ID is currently not set as exportable.
Example use-case
----------------------------------------
The use case for exporting is to ship address data to a third party for cleanup (...Overview
----------------------------------------
Address ID is currently not set as exportable.
Example use-case
----------------------------------------
The use case for exporting is to ship address data to a third party for cleanup (vendors who will return addresses with more accurate postal codes, changes of address, etc.). It's necessary to export an address ID to allow a reimport on the same address.
Current behaviour
----------------------------------------
Address ID not exportable.
Proposed behaviour
----------------------------------------
Address ID exportable.5.23.0JonGoldJonGoldhttps://lab.civicrm.org/dev/core/-/issues/1424Export doesn't work in Excel with diacritic chars2020-07-12T03:19:04ZeileenExport doesn't work in Excel with diacritic charsPROPOSAL - prepend the BOM for UTF8 "\xEF\xBB\xBF" so csv's exported from excel
Discussion (note this is the same writeup on the PR - creating a gitlab too as I suspect there might be discussion).
There is a long-standing issue ...PROPOSAL - prepend the BOM for UTF8 "\xEF\xBB\xBF" so csv's exported from excel
Discussion (note this is the same writeup on the PR - creating a gitlab too as I suspect there might be discussion).
There is a long-standing issue whereby files exported from CiviCRM with diacritic characters - eg.
ę are mangled when opened in MS Excel.
The underlying issue is that the characters are UTF-8 encoded & MS Excel by default does not assume UTF8.
In order to do so it needs a BOM - Byte Order Marker - which is a few hex characters at the start of
the csv.
The BOM indicator is 'not recommended' .... unless you want your csv to work with
MS Excel. Since MS Excel is a major use case for csvs I think it's pretty clear that all
things being equal we want to support it... I note that there are extensions to export to excel
natively but I don't think that replaces this. The number one reason to want to export
a csv is to work with it in a spreadsheeting programme & unless we can't safely make it
work in core we shouldn't require an extension for that.
There are various recommendations over time but it seems things have improved in the MS
Excel world and what works on Windows now appears to work on Mac too - at least on a recent version.
This link https://donatstudios.com/CSV-An-Encoding-Nightmare is a pretty good discussion.
While the above link and others say that you need a different BOM for MAC than Windows my
testing shows that the one recommended for Windows works fine on Mac Excel (while you would need
to convert to UTF 16 to follow the Mac recommendation. (https://csv.thephpleague.com/9.0/interoperability/encoding/)
Other links
https://stackoverflow.com/questions/35294443/does-excel-for-mac-2016-properly-import-unicode-in-csv-files
https://stackoverflow.com/questions/2223882/whats-the-difference-between-utf-8-and-utf-8-without-bom/2223926#2223926
Notably this summary :
" When should you encode with a BOM?
If you're unable to record the metadata in any other way (through a charset tag or file system meta), and the programs being used like BOMs, you should encode with a BOM. This is especially true on Windows where anything without a BOM is generally assumed to be using a legacy code page. The BOM tells programs like Office that, yes, the text in this file is Unicode; here's the encoding used.
When it comes down to it, the only files I ever really have problems with are CSV. Depending on the program, it either must, or must not have a BOM. For example, if you're using Excel 2007+ on Windows, it must be encoded with a BOM if you want to open it smoothly and not have to resort to importing the data."
So the argument for a BOM is - if you want it to be compatible with MS Excel use a BOM. This seems to apply.
The risk is that perhaps there is some variant of csv viewing programme that can't cope - the risk of this is rather mitigited by
1) the fact that MS Excel adds the BOM to the start of any files it saves as UTF-8 encoded csv so any programme that expects to open MS Excel generated
csvs would need to be able to cope with it. In addition it is a standard, if not required, file indicator so it feels like the programmes
that were legacy in 2016 writeups might be of no concern now.
2) There really is no programatic way to export these csvs so the risk that this is being parsed by code is close to zero
There are 2 other things we could do to mitigate
1) test on more platforms - I've tested with MS Excel for Mac (Office 365 v 16.31) and Libre Office and MS Numbers
& MS word, cot editor & notes
2) We could add a setting. I'm generally a bit loath on settings as they are a bit of a maintenance nightmare.
However, perhaps it would be a setting like 'export csvs in legacy format & there could be a link
to the gitlab to explain your use-case if you feel the need to set it. If no-one does then we could later remove.
Final note - csv tables are created with the CRM_Core_Report_Excel (spot the irony) from 3 places
- the main export, the export for custom fields & a third place which I believe to be unreachable. This is one path
only but I can look at centralising for the custom fields export path.5.22.0https://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/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/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/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/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>https://lab.civicrm.org/dev/core/-/issues/1678Export time out avoidance2023-03-01T05:03:23ZeileenExport time out avoidanceSites have a php time out to prevent long running processes & out of control loops. However, there are some processes that are known to need more time that others & we can extend the timeout within php to keep them alive. This is pretty ...Sites have a php time out to prevent long running processes & out of control loops. However, there are some processes that are known to need more time that others & we can extend the timeout within php to keep them alive. This is pretty safe because it's within a loop so we extend a little each row rather a huge time extension
Basically we can check each iteration here & if less than 10 seconds remains we can add 10 seconds as a sort of heartbeat https://www.php.net/manual/en/function.set-time-limit.php