Export Primary fields return DB unknown error
UPDATE - Can be fixed by cv api System.utf8conversion
. Details in #2780 (comment 63396)
Export Contacts, Membership (probably other entities too) returns a DB: unknown error when the primary fields option is chosen.
To replicate -
- Find Contacts.
- Select 1 row.
- Select
Export Contacts
from the task dropdown. - Select
Export PRIMARY fields
and submit. => DB Error
Error Message -
Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline
The issue replicates only on Maria DB, The details are
- Version - 10.3.29-MariaDB-0+deb10u1-log.
- innodb_strict_mode = ON.
- innodb_page_size = 16kb
Failing Query is formed at - https://github.com/civicrm/civicrm-core/blob/master/CRM/Export/BAO/ExportProcessor.php#L2142
Click to see full Query
CREATE TABLE `civicrm_tmp_d_export_1b49500f15bbf439d2e1d2b7d8d2e4de` ( id int unsigned NOT NULL AUTO_INCREMENT, `civicrm_primary_id` varchar(64),
`contact_type` varchar(64),
`contact_sub_type` varchar(255),
`do_not_email` varchar(64),
`do_not_phone` varchar(64),
`do_not_mail` varchar(64),
`do_not_sms` varchar(64),
`do_not_trade` varchar(64),
`is_opt_out` varchar(64),
`legal_identifier` varchar(32),
`external_identifier` varchar(64),
`sort_name` varchar(128),
`display_name` varchar(128),
`nick_name` varchar(128),
`legal_name` varchar(128),
`image_url` longtext,
`preferred_communication_method` varchar(255),
`preferred_language` varchar(4),
`preferred_mail_format` varchar(16),
`hash` varchar(32),
`contact_source` varchar(255),
`first_name` varchar(64),
`middle_name` varchar(64),
`last_name` varchar(64),
`prefix_id` varchar(255),
`suffix_id` varchar(255),
`formal_title` varchar(64),
`communication_style_id` varchar(255),
`email_greeting_id` varchar(64),
`postal_greeting_id` varchar(64),
`addressee_id` varchar(64),
`job_title` varchar(255),
`gender_id` varchar(255),
`birth_date` varchar(32),
`is_deceased` varchar(64),
`deceased_date` varchar(32),
`household_name` varchar(128),
`organization_name` varchar(128),
`sic_code` varchar(8),
`user_unique_id` varchar(255),
`current_employer_id` varchar(64),
`contact_is_deleted` varchar(64),
`created_date` varchar(32),
`modified_date` varchar(32),
`addressee` varchar(255),
`email_greeting` varchar(255),
`postal_greeting` varchar(255),
`current_employer` varchar(255),
`location_type` varchar(255),
`address_id` varchar(64),
`street_address` varchar(96),
`street_number` varchar(64),
`street_number_suffix` varchar(8),
`street_name` varchar(64),
`street_unit` varchar(16),
`supplemental_address_1` varchar(96),
`supplemental_address_2` varchar(96),
`supplemental_address_3` varchar(96),
`city` varchar(64),
`postal_code_suffix` varchar(12),
`postal_code` varchar(64),
`geo_code_1` varchar(32),
`geo_code_2` varchar(32),
`manual_geo_code` varchar(64),
`address_name` varchar(255),
`master_id` varchar(128),
`county` varchar(64),
`state_province` varchar(64),
`country` varchar(64),
`phone` varchar(32),
`phone_ext` varchar(16),
`phone_type_id` varchar(64),
`phone_type` varchar(255),
`email` varchar(254),
`on_hold` varchar(64),
`is_bulkmail` varchar(64),
`signature_text` longtext,
`signature_html` longtext,
`im_provider` varchar(255),
`im` varchar(64),
`openid` varchar(255),
`world_region` varchar(128),
`url` varchar(128),
`groups` longtext,
`tags` longtext,
`notes` longtext,
PRIMARY KEY ( id )
) ENGINE=InnoDB COLLATE utf8_unicode_ci