Exporting in localized civicrm crashes due to long translations in columns T_Boolean

Overview

Export tmp tables are created with varchar(16) for T_BOOLEAN types. This may be sufficient for the short-worded language english but if translated we get longer export values. So this should be 32 or more

To fix for example change ExportProcessor.php, L1454, to

      return "`$fieldName` varchar(64)";

Overview

Very recently we discovered a similar problem with the "default" contacts export functionality of CiviCRM.

The export fails with the following message: "DB Error: unknown error" in the logs we noticed the error below:

Nov 04 09:42:03 [debug] $backTrace = #0 /var/www/html/vendor/civicrm/civicrm-core/CRM/Core/Error.php(205): CRM_Core_Error::backtrace("backTrace", TRUE) #1 /var/www/html/vendor/pear/pear-core-minimal/src/PEAR.php(944): CRM_Core_Error::handle(Object(DB_Error)) #2 /var/www/html/vendor/civicrm/civicrm-core/packages/DB.php(998): PEAR_Error->__construct("DB Error: unknown error", -1, 16, (Array:2), "\nINSERT INTO civicrm_tmp_d_export_4db4a46e9ff05fd48744e67549e545d5 (id, c...") #3 (closed) /var/www/html/vendor/pear/pear-core-minimal/src/PEAR.php(575): DB_Error->__construct(-1, 16, (Array:2), "\nINSERT INTO civicrm_tmp_d_export_4db4a46e9ff05fd48744e67549e545d5 (id, c...") #4 /var/www/html/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_4db4a46e9ff05fd48744e67549e545d5 (id, c...", "DB_Error", TRUE) #5 /var/www/html/vendor/civicrm/civicrm-core/packages/DB/common.php(1925): PEAR->__call("raiseError", (Array:7)) #6 (closed) /var/www/html/vendor/civicrm/civicrm-core/packages/DB/mysqli.php(936): DB_common->raiseError(-1, NULL, NULL, "\nINSERT INTO civicrm_tmp_d_export_4db4a46e9ff05fd48744e67549e545d5 (id, c...", "1406 ** Data too long for column 'on_hold' at row 95")

[nativecode=1406 ** Data too long for column 'on_hold' at row 95]"]

Reproduction steps

1. Click on Search -> Find contacts.
2. Press Search
3. Click Select [*] records and choose Export contacts from the actions dropdown
4. Click Export PRIMARY fields and Continue
5. Got an error DB Error: unknown error

Environment information

• CiviCRM: CiviCRM 5.30.1
• PHP: 7.3
• CMS: Drupal 7.30
• Database: MySQL 5.7.7
• Web Server: Nginx 1.16

We (@wannesderoy) have created the following patch on github containing the solution as described above.

https://github.com/civicrm/civicrm-core/pull/18920

changed the description

added comp:Export git:civicrm-core sig:bug sig:translation triaged labels

This is still a relevant issue, last observed with 5.35.1. Here the boolean field on_hold (email) is exported as

On Hold Bounce

which (with its 14 characters) fits nicely into the export temp table that allows for 16 (see PR linked above).

However the German translation

Inaktiv wegen Unerreichbarkeit

with 30 characters triggers the Data too long for column x exception. I know, I know, we're a complicated people with unnecessarily long words... but I feel we should be able to export our data anyway :D

I really recommend to pull the PR above into CORE, even though it's 'just' a mitigation and not really fixing the underlying issue. To prevent this from happening ever again, we could additionally truncate the (translated) values to the 64 characters.

@eileen, @seamuslee any objections?

16 to 64 isn't going to blow the table width out that much (which also causes crashes) but the tests are failing on the PR at the moment

Thanks @eileen, I'll have a look at that.

mentioned in issue #2500 (closed)

I've refactored the patch, see PR-19957. Let's see if it passes...

not yet

I updated it again, let's see if third time's the charm...

looks good now.

changed milestone to %5.37.0

closed

mentioned in issue #2469 (closed)

mentioned in issue #2645 (closed)