CiviCRM Core issueshttps://lab.civicrm.org/dev/core/-/issues2023-12-06T23:20:03Zhttps://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/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/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/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.