Details
-
Type: Bug
-
Status: Done/Fixed
-
Priority: Major
-
Resolution: Won't Fix
-
Affects Version/s: 3.4.8
-
Fix Version/s: Unscheduled
-
Component/s: Core CiviCRM
-
Labels:None
-
Versioning Impact:Patch (backwards-compatible bug fixes)
-
Documentation Required?:None
-
Funding Source:Needs Funding
Description
Recently, a client added a lot of fields to a custom data group. Out of habit, they indicated every single field should be searchable while creating each field.
For every field that is searchable, Civi creates a btree index on the custom group table. This does give us a big performance improvement when searching, but Civi does not check for the number of existing indicies on the mysql table first. By default, mysql only allows 64 indicies per table, and you can only change this as a compile-time option to a maximum of 128, so changes to mysql are not an option. When civi tries to save the field 62nd or 63rd (there are already two indicies per table--a primary, and a unique) field to the db (which creates the index at the same time), the db throws an error and doesn't create the field. As civi has already written that the field exists to the custom_field table, and does not detect that the alter failed (which, just gets displayed to the end user as a big nasty message), civi does not roll the custom data back to reflect the correct state of the table.
Then, whenever that failed field is used (contact summary, possibly profiles,etc), the site throws another big ugly db error.
Ideally, we should:
1. Check for alter table failures
2. Check for the number of indicies on a table before trying to allow the field to be searchable. Then we can deliver a better status error to the user telling them they need to disable the searchability of other fields in that data group before being allowed to mark the new field as searchable.