Answers.Body: <p>Ahh it's a field of type number, which means it's getting set here: <a href="https://github.com/civicrm/civicrm-core/blob/77c3b7089c290880a7599cc77cdbe87fa6958c13/CRM/Export/BAO/ExportProcessor.php#L1498" rel="nofollow noreferrer">https://github.com/civicrm/civicrm-core/blob/77c3b7089c290880a7599cc77cdbe87fa6958c13/CRM/Export/BAO/ExportProcessor.php#L1498</a>.</p>
  <p>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 <a href="https://lab.civicrm.org/groups/dev/-/issues" rel="nofollow noreferrer">https://lab.civicrm.org/groups/dev/-/issues</a></p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-03-23T04:08:55.260Z
Answers.LastActivityDate: 2021-03-23T04:08:55.260Z
Answers.UserDisplayName: Demerit
Body: <p>A user is trying to export data from an advanced query on custom fields to a CSV file using the &quot;Export contacts&quot; action. This fails with a backtrace which is attached below. The error message is:</p>
  <pre><code>#6 /var/www/libertarian/vendor/pear/db/DB/mysqli.php(936): DB_common-&gt;raiseError(-1, NULL, NULL, &quot;\nINSERT INTO civicrm_tmp_d_export_1d387c75256673080ad64346b3c095c7 (`id`, `l...&quot;, &quot;1406 ** Data too long for column 'custom_49' at row 13&quot;)
  </code></pre>
  <p>The <code>custom_49</code> 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.</p>
  <p>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)</p>
  <h3>Details</h3>
  <p>If I select the schema of the table <code>civicrm_tmp_d_export_1d387c75256673080ad64346b3c095c7</code> it reports the table structure as:</p>
  <pre><code>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
  </code></pre>
  <p>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.</p>
  <pre><code>SELECT label,CHAR_LENGTH(label) as length FROM civicrm.civicrm_option_value WHERE (option_group_id=103) AND (CHAR_LENGTH(label) &gt; 32)
  </code></pre>
  <p>returns</p>
  <pre><code>| 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     |
  </code></pre>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2021-03-22T01:57:14.397Z
Comments.Text: In the custom field definition in the UI what do you have for the "database field length" field? Also what version of civi?
Comments.UserDisplayName: Demerit
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2021-03-23T04:00:14.937Z
Comments.Text: 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.
Comments.UserDisplayName: Bond Keevil
Tags: <custom-fields><export><csv>
Title: Export of custom multiple choice options to CSV files limited to 32 characters
ThreadText: # Export of custom multiple choice options to CSV files limited to 32 characters
  
  (Asked by: username_0 on 2021-03-21)
  
  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     |
  
  ```
  
  **Tags:** custom-fields, export, csv
  
  > **Comments:**
  > **username_1**: In the custom field definition in the UI what do you have for the "database field length" field? Also what version of civi?  
  > **username_0**: 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.  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2021-03-23. Score: 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>
  
  
  ---
  
  