1

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     |
2
  • In the custom field definition in the UI what do you have for the "database field length" field? Also what version of civi? – Demerit Mar 22 at 1:57
  • The datatype is number so there is no database field length for the field. The multiple choice option set maps strings to numbers and those numbers are stored in the database. 5.33.1 originally but now 5.35.1. – Bond Keevil Mar 23 at 4:00
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.