0

In adding new custom fields to an existing set, things recently went wrong. New entries were added in the civicrm_custom_field table, but the corresponding columns were not added to that field set's table. This resulted in DB Error: no such field breaking the UI display of existing cases. Deleting the rows from the custom_field table solved the errors so that existing cases can be viewed.

Now new cases cannot be created. When the new case form is submitted the case gets partially added to the db (but is not visible via the UI) and a blank error screen with message DB Constraint Violation - case_type_id should possibly be marked as mandatory for Case,get API. If so, please raise a bug report. appears. Creating a new case (or trying to) adds 83000 new lines to the log file and near the top of that entry is [nativecode=1054 ** Unknown column 'a_to_civicrm_value_special_case_fields_8._name_of_deleted_field_157' in 'field list']. That seems confusing since "a_to_" is not actually part of the table's name, and "_name_of_deleted_field_157" isn't in the civicrm_custom_field table. Are custom fields recorded/stored anywhere aside from civicrm_custom_field and the fieldset they're contained by?

Also, the field and case id's seem to have their incrementing messed up... the id#s appear to increment by 1 but there are several numbers skipped. Is that a problem? With the fields I deleted the row and column back to the last increment, 148, but when adding a new field its id was 155. Can missing id's (149-154 in this case) be problematic?

Is there anything you can suggest or point me towards in getting this working?


edits:

  • 131 rows in civicrm_custom_field table
  • CiviCRM 5.13.4
  • How many custom fields do you have? – Demerit Oct 24 at 1:03
  • Missing IDs aren't problematic; this is normal behavior when a custom field is deleted. I can't speak to the rest since I don't use CiviCase but maybe edit your question to include CiviCRM version? – Jon G - Megaphone Tech Oct 24 at 4:55
  • 131 fields - take a look at lab.civicrm.org/dev/core/issues/1330 which seems like a similar issue with the same error – Demerit Oct 24 at 14:45
  • That's 131 fields total (so that table has 131 rows, but only 33 columns). The largest field group is 65 fields (so its table has 65 columns, 28 fewer than the threshold they were finding in that issue on gitlab) ...do you still suspect this could be related to that as a MySql limitation? – alec Oct 24 at 16:34
  • Maybe, since row size can also depend on field name length, data type, etc. If you can do it in smaller groups it would be worth trying. – Demerit Oct 24 at 18:10
1

As Demerit mentioned, you have the same issues I reported. I don't think it's about the number of fields that's the issue, but the combination of data types. If you are receiving that error, be careful if your site is live because some of your cases will hit an error and you won't be able to view the case page.

Here's what I did:

If possible, you will want to delete all the custom fields and the custom group itself. Do it in phpmyadmin because it's faster. Once you have cleared the data, split the 65 fields into two custom groups. The error should go away.

If you can't delete the custom group because you already have data, delete the custom fields and then create a new custom group to split off some of those fields.

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.