Answers.Body: <p>Ok so based on comments it's that using substr() still results in the byte length being too long. Have added <a href="https://lab.civicrm.org/dev/core/-/issues/2146" rel="nofollow noreferrer">https://lab.civicrm.org/dev/core/-/issues/2146</a> for now.</p>
  <p>EDIT: Will be fixed in version 5.32.0 via <a href="https://github.com/civicrm/civicrm-core/pull/18862" rel="nofollow noreferrer">https://github.com/civicrm/civicrm-core/pull/18862</a></p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-10-26T15:28:40.443Z
Answers.Text: changing lines 161 and 164 of civicrm/CRM/Contact/BAO/Contact.php so it uses mb_substr instead substr does solve the problem
Answers.UserDisplayName: Marko
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-10-26T15:35:15.470Z
Answers.Text: 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.
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-10-26T16:24:35.623Z
Answers.Text: 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.
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-10-26T18:25:40.953Z
Answers.Text: 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.
Answers.UserDisplayName: Marko
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-10-26T14:14:31.920Z
Answers.LastActivityDate: 2020-10-27T22:22:04.223Z
Answers.LastEditDate: 2020-10-27T22:22:04.223Z
Answers.UserDisplayName: Demerit
Body: <p>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 <strong>nativecode=1406 ** Data too long for column 'sort_name' at row 1</strong> DB error. I presume it has something to do with how sortname shortening works and the number of bytes per character.</p>
  <p>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.</p>
  <p>How can I resolve this issue?</p>
  <p>Here is the backTrace</p>
  <pre><code>#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-&gt;__construct(&quot;DB Error: unknown error&quot;, -1, 16, (Array:2), &quot;INSERT INTO `civicrm_contact` (`contact_type` , `do_not_email` , `do_not_phon...&quot;)
  #3 /var/www/civi/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(575): DB_Error-&gt;__construct(-1, 16, (Array:2), &quot;INSERT INTO `civicrm_contact` (`contact_type` , `do_not_email` , `do_not_phon...&quot;)
  #4 /var/www/civi/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(223): PEAR-&gt;_raiseError(Object(DB_mysqli), NULL, -1, 16, (Array:2), &quot;INSERT INTO `civicrm_contact` (`contact_type` , `do_not_email` , `do_not_phon...&quot;, &quot;DB_Error&quot;, TRUE)
  #5 /var/www/civi/sites/all/modules/civicrm/packages/DB/common.php(1925): PEAR-&gt;__call(&quot;raiseError&quot;, (Array:7))
  #6 /var/www/civi/sites/all/modules/civicrm/packages/DB/mysqli.php(936): DB_common-&gt;raiseError(-1, NULL, NULL, &quot;INSERT INTO `civicrm_contact` (`contact_type` , `do_not_email` , `do_not_phon...&quot;, &quot;1406 ** Data too long for column 'sort_name' at row 1&quot;)
  #7 /var/www/civi/sites/all/modules/civicrm/packages/DB/mysqli.php(406): DB_mysqli-&gt;mysqliRaiseError()
  #8 /var/www/civi/sites/all/modules/civicrm/packages/DB/common.php(1231): DB_mysqli-&gt;simpleQuery(&quot;INSERT INTO `civicrm_contact` (`contact_type` , `do_not_email` , `do_not_phon...&quot;)
  #9 /var/www/civi/sites/all/modules/civicrm/packages/DB/DataObject.php(2696): DB_common-&gt;query(&quot;INSERT INTO `civicrm_contact` (`contact_type` , `do_not_email` , `do_not_phon...&quot;)
  #10 /var/www/civi/sites/all/modules/civicrm/packages/DB/DataObject.php(1245): DB_DataObject-&gt;_query(&quot;INSERT INTO `civicrm_contact` (`contact_type` , `do_not_email` , `do_not_phon...&quot;)
  #11 /var/www/civi/sites/all/modules/civicrm/CRM/Core/DAO.php(620): DB_DataObject-&gt;insert()
  #12 /var/www/civi/sites/all/modules/civicrm/CRM/Contact/BAO/Contact.php(189): CRM_Core_DAO-&gt;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-&gt;postProcess()
  #16 /var/www/civi/sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Upload.php(153): CRM_Core_Form-&gt;mainProcess()
  #17 /var/www/civi/sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Upload.php(120): CRM_Core_QuickForm_Action_Upload-&gt;realPerform(Object(CRM_Contact_Form_Contact), &quot;upload&quot;)
  #18 /var/www/civi/sites/all/modules/civicrm/packages/HTML/QuickForm/Controller.php(203): CRM_Core_QuickForm_Action_Upload-&gt;perform(Object(CRM_Contact_Form_Contact), &quot;upload&quot;)
  #19 /var/www/civi/sites/all/modules/civicrm/packages/HTML/QuickForm/Page.php(103): HTML_QuickForm_Controller-&gt;handle(Object(CRM_Contact_Form_Contact), &quot;upload&quot;)
  #20 /var/www/civi/sites/all/modules/civicrm/CRM/Core/Controller.php(347): HTML_QuickForm_Page-&gt;handle(&quot;upload&quot;)
  #21 /var/www/civi/sites/all/modules/civicrm/CRM/Utils/Wrapper.php(98): CRM_Core_Controller-&gt;run()
  #22 /var/www/civi/sites/all/modules/civicrm/CRM/Core/Invoke.php(285): CRM_Utils_Wrapper-&gt;run(&quot;CRM_Contact_Form_Contact&quot;, &quot;New Contact&quot;, (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(&quot;contact&quot;, &quot;add&quot;)
  #27 /var/www/civi/index.php(21): menu_execute_active_handler()
  #28 {main}
  </code></pre>
  <p>And here is the db error</p>
  <pre><code> [error] $Fatal Error Details = Array
  (
      [callback] =&gt; Array
          (
              [0] =&gt; CRM_Core_Error
              [1] =&gt; handle
          )
  
      [code] =&gt; -1
      [message] =&gt; DB Error: unknown error
      [mode] =&gt; 16
      [debug_info] =&gt; 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] =&gt; DB_Error
  </code></pre>
  <p>CiviCRM version 5.28.3</p>
  <p>PHP version 7.1.33-19</p>
  <p>MySQL version  5.7.31</p>
  <p>Engine: InnoDB</p>
  <p>Collation: utf8_unicode_ci</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-10-21T17:40:49.007Z
Comments.Text: Do you have the innodb settings in your my.conf as described at https://docs.civicrm.org/installation/en/latest/general/requirements/#__code_2, i.e. `innodb_large_prefix=true` `innodb_file_format=barracuda` `innodb_file_per_table=true`
Comments.UserDisplayName: Demerit
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-10-21T21:42:38.133Z
Comments.Text: 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).
Comments.UserDisplayName: Marko
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-10-21T22:20:45.090Z
Comments.Text: 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.
Comments.UserDisplayName: Demerit
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-10-21T22:23:55.493Z
Comments.Text: Thanks for the suggestion. I've been delaying the switch to utf8mb4 for too long now. I will try that tomorrow and report back.
Comments.UserDisplayName: Marko
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-10-26T09:46:46.983Z
Comments.Text: 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.
Comments.UserDisplayName: Marko
Tags: <contacts><database><php><mysql>
Title: DB error for sort_name when adding contacts with long names in 2 bytes UTF8 characters
ThreadText: # DB error for sort_name when adding contacts with long names in 2 bytes UTF8 characters
  
  (Asked by: username_0 on 2020-10-21)
  
  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
  
  **Tags:** contacts, database, php, mysql
  
  > **Comments:**
  > **username_1**: Do you have the innodb settings in your my.conf as described at https://docs.civicrm.org/installation/en/latest/general/requirements/#__code_2, i.e. `innodb_large_prefix=true` `innodb_file_format=barracuda` `innodb_file_per_table=true`  
  > **username_0**: 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).  
  > **username_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.  
  > **username_0**: Thanks for the suggestion. I've been delaying the switch to utf8mb4 for too long now. I will try that tomorrow and report back.  
  > **username_0**: 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.  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2020-10-26. Score: 1 | Accepted answer
  
  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>
  
  > **Comments:**
  > **username_0**: changing lines 161 and 164 of civicrm/CRM/Contact/BAO/Contact.php so it uses mb_substr instead substr does solve the problem  
  > **username_1**: 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.  
  > **username_1**: 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.  
  > **username_0**: 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.  
  
  ---
  
  