2

The problem occurs when I try to add a contact (in the example it's an organization) with long name in Cyrillic (multiple words in it's name). I get the nativecode=1406 ** Data too long for column 'sort_name' at row 1 DB error. I presume it has something to do with how sortname shortening works and the number of bytes per character.

I haven't been able to replicate this on the Agiliway's demo site, so I suspect it has something to do with my MySQL/php configuration.

How can I resolve this issue?

Here is the backTrace

#0 /var/www/civi/sites/all/modules/civicrm/CRM/Core/Error.php(148): CRM_Core_Error::backtrace()
#1 /var/www/civi/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(922): CRM_Core_Error::handle(Object(DB_Error))
#2 /var/www/civi/sites/all/modules/civicrm/packages/DB.php(998): PEAR_Error->__construct("DB Error: unknown error", -1, 16, (Array:2), "INSERT INTO `civicrm_contact` (`contact_type` , `do_not_email` , `do_not_phon...")
#3 /var/www/civi/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(575): DB_Error->__construct(-1, 16, (Array:2), "INSERT INTO `civicrm_contact` (`contact_type` , `do_not_email` , `do_not_phon...")
#4 /var/www/civi/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(223): PEAR->_raiseError(Object(DB_mysqli), NULL, -1, 16, (Array:2), "INSERT INTO `civicrm_contact` (`contact_type` , `do_not_email` , `do_not_phon...", "DB_Error", TRUE)
#5 /var/www/civi/sites/all/modules/civicrm/packages/DB/common.php(1925): PEAR->__call("raiseError", (Array:7))
#6 /var/www/civi/sites/all/modules/civicrm/packages/DB/mysqli.php(936): DB_common->raiseError(-1, NULL, NULL, "INSERT INTO `civicrm_contact` (`contact_type` , `do_not_email` , `do_not_phon...", "1406 ** Data too long for column 'sort_name' at row 1")
#7 /var/www/civi/sites/all/modules/civicrm/packages/DB/mysqli.php(406): DB_mysqli->mysqliRaiseError()
#8 /var/www/civi/sites/all/modules/civicrm/packages/DB/common.php(1231): DB_mysqli->simpleQuery("INSERT INTO `civicrm_contact` (`contact_type` , `do_not_email` , `do_not_phon...")
#9 /var/www/civi/sites/all/modules/civicrm/packages/DB/DataObject.php(2696): DB_common->query("INSERT INTO `civicrm_contact` (`contact_type` , `do_not_email` , `do_not_phon...")
#10 /var/www/civi/sites/all/modules/civicrm/packages/DB/DataObject.php(1245): DB_DataObject->_query("INSERT INTO `civicrm_contact` (`contact_type` , `do_not_email` , `do_not_phon...")
#11 /var/www/civi/sites/all/modules/civicrm/CRM/Core/DAO.php(620): DB_DataObject->insert()
#12 /var/www/civi/sites/all/modules/civicrm/CRM/Contact/BAO/Contact.php(189): CRM_Core_DAO->save()
#13 /var/www/civi/sites/all/modules/civicrm/CRM/Contact/BAO/Contact.php(290): CRM_Contact_BAO_Contact::add((Array:128))
#14 /var/www/civi/sites/all/modules/civicrm/CRM/Contact/Form/Contact.php(980): CRM_Contact_BAO_Contact::create((Array:128), TRUE, FALSE, TRUE)
#15 /var/www/civi/sites/all/modules/civicrm/CRM/Core/Form.php(504): CRM_Contact_Form_Contact->postProcess()
#16 /var/www/civi/sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Upload.php(153): CRM_Core_Form->mainProcess()
#17 /var/www/civi/sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Upload.php(120): CRM_Core_QuickForm_Action_Upload->realPerform(Object(CRM_Contact_Form_Contact), "upload")
#18 /var/www/civi/sites/all/modules/civicrm/packages/HTML/QuickForm/Controller.php(203): CRM_Core_QuickForm_Action_Upload->perform(Object(CRM_Contact_Form_Contact), "upload")
#19 /var/www/civi/sites/all/modules/civicrm/packages/HTML/QuickForm/Page.php(103): HTML_QuickForm_Controller->handle(Object(CRM_Contact_Form_Contact), "upload")
#20 /var/www/civi/sites/all/modules/civicrm/CRM/Core/Controller.php(347): HTML_QuickForm_Page->handle("upload")
#21 /var/www/civi/sites/all/modules/civicrm/CRM/Utils/Wrapper.php(98): CRM_Core_Controller->run()
#22 /var/www/civi/sites/all/modules/civicrm/CRM/Core/Invoke.php(285): CRM_Utils_Wrapper->run("CRM_Contact_Form_Contact", "New Contact", (Array:1))
#23 /var/www/civi/sites/all/modules/civicrm/CRM/Core/Invoke.php(68): CRM_Core_Invoke::runItem((Array:13))
#24 /var/www/civi/sites/all/modules/civicrm/CRM/Core/Invoke.php(36): CRM_Core_Invoke::_invoke((Array:3))
#25 /var/www/civi/sites/all/modules/civicrm/drupal/civicrm.module(454): CRM_Core_Invoke::invoke((Array:3))
#26 /var/www/civi/includes/menu.inc(527): civicrm_invoke("contact", "add")
#27 /var/www/civi/index.php(21): menu_execute_active_handler()
#28 {main}

And here is the db error

 [error] $Fatal Error Details = Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -1
    [message] => DB Error: unknown error
    [mode] => 16
    [debug_info] => INSERT INTO `civicrm_contact` (`contact_type` , `do_not_email` , `do_not_phone` , `do_not_mail` , `do_not_sms` , `do_not_trade` , `is_opt_out` , `external_identifier` , `sort_name` , `display_name` , `nick_name` , `legal_name` , `preferred_communication_method` , `preferred_language` , `preferred_mail_format` , `hash` , `source` , `communication_style_id` , `email_greeting_custom` , `postal_greeting_custom` , `addressee_custom` , `organization_name` , `sic_code` , `modified_date` ) VALUES ('Organization' ,  0 ,  0 ,  0 ,  0 ,  0 ,  0 ,  NULL , 'асдадасда шшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшш�' , 'асдадасда шшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшш�' ,  NULL ,  NULL ,  NULL , 'en_US' , 'Both' , 'a41da843b02fd831936f9bb2107d1d53' ,  NULL ,  1 ,  NULL ,  NULL ,  NULL , 'асдадасда шшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшшш' ,  NULL ,  NULL )  [nativecode=1406 ** Data too long for column 'sort_name' at row 1]
    [type] => DB_Error

CiviCRM version 5.28.3

PHP version 7.1.33-19

MySQL version 5.7.31

Engine: InnoDB

Collation: utf8_unicode_ci

  • Do you have the innodb settings in your my.conf as described at docs.civicrm.org/installation/en/latest/general/requirements/…, i.e. innodb_large_prefix=true innodb_file_format=barracuda innodb_file_per_table=true – Demerit Oct 21 at 17:40
  • I don't have those settings in mysqd.cnf, but those settings are default in my version of sql, and are active (I confirmed that through mysqld --verbose --help |grep innodb). – Marko Oct 21 at 21:42
  • 1
    Ok then one other thing you could try is going to api explorer and running the 'System' - 'utf8conversion' script to convert to utf8mb4 (make a db backup first!). If that doesn't work then I think you're right it's probably that substr() doesn't take into account the final byte size. – Demerit Oct 21 at 22:20
  • Thanks for the suggestion. I've been delaying the switch to utf8mb4 for too long now. I will try that tomorrow and report back. – Marko Oct 21 at 22:23
  • I've just tested it and it doesn't work. Conversion goes through nicely, but when I try to add an organization with the string above I get the same error on utf8mb4_unicode_ci as I do on utf8_unicode_ci. – Marko Oct 26 at 9:46
1

Ok so based on comments it's that using substr() still results in the byte length being too long. Have added https://lab.civicrm.org/dev/core/-/issues/2146 for now.

EDIT: Will be fixed in version 5.32.0 via https://github.com/civicrm/civicrm-core/pull/18862

| improve this answer | |
  • changing lines 161 and 164 of civicrm/CRM/Contact/BAO/Contact.php so it uses mb_substr instead substr does solve the problem – Marko Oct 26 at 15:28
  • Hmm that's interesting because if anything mb_substr should return a longer string. It did when I tried it although I was just checking command line. – Demerit Oct 26 at 15:35
  • Oh I think I see part of it. With utf8mb4 you can actually get a different error with substr, where it's creating an invalid utf8 string because a char gets split. So yes maybe mb_substr() is the right answer, just it's not always available in all installs. – Demerit Oct 26 at 16:24
  • I tried to see what happens when I use the default substr but decrease the number of character to 127 instead of 128. With the same string as above, it gets saved no problem, but in the database the saved string isnow 64 characters, while the string saved using mb_substring in the database is 125 characters. – Marko Oct 26 at 18:25

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.