1

I have a CiviCRM install on Windows shared server operated by a client. The installation was originally set up on a dedicated Ubuntu Linux virtual server, but was transferred to the Windows server at their request.

Since the transfer from Linux to Windows I am getting frequent MySQL errors when trying to edit or add custom fields.

The following is a typical error in the CiviCRM logs:

ALTER TABLE civicrm_value_mycustomfield_8
  ADD COLUMN `myfield_year_142` double DEFAULT '\x012021\x01',
  ADD INDEX INDEX_myfield_year_142 ( myfield_year_142 )
  [nativecode=1067 ** Invalid default value for 'myfield_year_142']

As you can see, CiviCRM is creating queries that try to insert hexadecimal character codes into numeric fields. The default being set should be '2021'.

The MySQL database on the server being used I note is cp1252 West European (latin1), although I wouldn't usually expect that to cause problems. Table collation (which doesn't affect numeric tables anyway) is utf8_unicode_ci.

  • CiviCRM version 5.37.2
  • MySQL 5.7.34
  • PHP 7.3

I've resorted to manually running MySQL queries to get custom fields established properly, but obviously there are all sorts of reasons why that is not a great idea.

Does anybody know what's going on here, and how to resolve it?

Improve this question
2
  • 1
    I would check the civicrm_custom_field table for the field in question and check if the default value is set correctly. It may have become encoded incorrectly during the move to windows and civicrm stores the default values in this table. The other possibility is that you have some customisations in code and those files are encoded incorrectly. I would eliminate the civicrm_custom_field table first and take it from there.
    – Parvez Saleh
    May 20, 2021 at 10:31
  • 1
    It looks like that field is a multi-valued field? But I'm not sure how you'd create that if it's of type Number.
    – Demerit
    May 20, 2021 at 11:26

1 Answer 1

Reset to default
1

I did a bunch more work around this issue, and it appears to relate to a bug in how the values stored in the database civicrm_custom_field.serialize are handled when testing whether a change to serialization is required.

When dealing with INT columns this would lead to an attempt to serialize a single value in and INT column, which results in an attempt to insert a string value to the INT field, triggering a MySQL error.

See bug report.

See bug fix pull request by colemanw here

Improve this answer

Your Answer

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

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