3

We're trying to get External Identifier working for an external system that uses case-sensitive IDs (such that external contact "abc123" John Smith is distinct from external contact "ABC123" Alice Brown).

By design, CiviCRM treats External Identifier as case-insensitive, and then it requires that External Identifier be unique across contacts; it also uses External Identifier as an identifying key on contact import; and probably other things I'm not thinking of at the moment.

We could, in mysql, alter the external_identifier column to use a case-sensitive collation such as utf8mb4_unicode_bin in order to achieve case-sensitivity in the unique index, and to provide case-sensitive matching in WHERE clauses on that column.

What kind of a can of worms would we be opening here? Are there likely to be pathways in CiviCRM's PHP execution that would break outright on such changes?

2 Answers 2

2

My experiments with setting external_identifier column to bin were not successful. It crashed basic functions of Civi for reasons unclear.

Better is to add a hash to the data itself, thanks for the idea Demerit. I found this VBA script that does so through Excel: https://codedocu.com/Office-365/Excel/Templates/Excel-_colon_-Encoding-values-with-HASH-functions-SHA256?2482 Note that installing the .NET framework in Windows and enabling .NET as Windows Feature is required.

Also note that the external_identifier varchar(64) table is limited to 64 characters. Either you can truncate the hash or alter the Civi table.

2

You'd want to rebuild triggers if using trigger logging. If you have code that does a SQL join or compare on this field with another you might want to check that still works, but if it did before it still should. However I'd lean towards _bin rather than one of the _cs collations, since that should automatically cast mismatches, whereas _cs might error.

The main danger isn't bin it's if you have an extension with utf8 and core with utf8mb4 and there's some comparison or join made on the field.


An alternate idea if you're ok with editing input files before every import: Use a hash of the external id as the external id. This could be simple or complicated, i.e.

  • Add an import file column that's the output of md5 or sha256 of the external id. Use that for import. I think md5 would be sufficient here unless you have a ton of rows.
  • Add an import file column that's the first, say, 10 chars of the sha256 of the external id plus the actual external id. This would be unique and then also have the real external id visible if that's a need.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.