Answers.Body: <p>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 <code>civicrm_custom_field.serialize</code> are handled when testing whether a change to serialization is required.</p>
  <p>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.</p>
  <p><a href="https://lab.civicrm.org/dev/core/-/issues/2762" rel="nofollow noreferrer">See bug report</a>.</p>
  <p><a href="https://github.com/civicrm/civicrm-core/pull/21160" rel="nofollow noreferrer">See bug fix pull request by colemanw here</a></p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-08-16T19:45:26.283Z
Answers.LastActivityDate: 2021-08-18T15:39:35.507Z
Answers.LastEditDate: 2021-08-18T15:39:35.507Z
Answers.UserDisplayName: fred2
Body: <p>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.</p>
  <p>Since the transfer from Linux to Windows I am getting frequent MySQL errors when trying to edit or add custom fields.</p>
  <p>The following is a typical error in the CiviCRM logs:</p>
  <pre><code>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']
  </code></pre>
  <p>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'.</p>
  <p>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.</p>
  <ul>
  <li>CiviCRM version 5.37.2</li>
  <li>MySQL 5.7.34</li>
  <li>PHP 7.3</li>
  </ul>
  <p>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.</p>
  <p>Does anybody know what's going on here, and how to resolve it?</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2021-05-20T10:31:07.637Z
Comments.Text: 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.
Comments.UserDisplayName: Parvez Saleh
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2021-05-20T11:26:29.383Z
Comments.Text: 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.
Comments.UserDisplayName: Demerit
Tags: <custom-fields><mysql><windows><mysql-5.7>
Title: Illegal text in MySQL queries
ThreadText: # Illegal text in MySQL queries
  
  (Asked by: username_0 on 2021-05-20)
  
  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?
  
  **Tags:** custom-fields, mysql, windows, mysql-5.7
  
  > **Comments:**
  > **username_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.  
  > **username_2**: 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.  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_0 on 2021-08-16. Score: 1 | Accepted answer
  
  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](https://lab.civicrm.org/dev/core/-/issues/2762).
  
  [See bug fix pull request by colemanw here](https://github.com/civicrm/civicrm-core/pull/21160)
  
  
  ---
  
  