Export of custom multiple choice options to CSV files limited to 32 characters

Question

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     |

Answer 1

Ahh it's a field of type number, which means it's getting set here: https://github.com/civicrm/civicrm-core/blob/77c3b7089c290880a7599cc77cdbe87fa6958c13/CRM/Export/BAO/ExportProcessor.php#L1498.

So yes if it's an option list then there's a mismatch. You could work around it by defining the field as type string instead and still use numbers as the values. But yes in general it seems like a bug - you could file a ticket at https://lab.civicrm.org/groups/dev/-/issues