Open Source CiviCRM Extension for Custom Field Limit Notifications
Skvare is proud to announce the release of the CiviCRM - Database Custom Field Check. The DBCF extension notifies the user when no additional custom fields can be added to a custom field group because the table size has reached its limit. This prevents the user from creating a field that cannot be used, saving time and frustration. By notifying users when no additional custom fields can be added to a custom field group, the extension helps prevent wasted effort and ensures that data is accurately stored and used.
Custom fields are a crucial feature of CiviCRM, allowing users to store additional data beyond what is included in the default contact fields. However, users may encounter an issue where they cannot add more custom fields to a group, as the MySQL table has a maximum row size limit of 65,535 bytes, even if the storage engine is capable of supporting larger rows. BLOB and TEXT columns only contribute 9 to 12 bytes toward the row size limit because their contents are stored separately from the rest of the row. Until now, the end user would not be aware of an issue since the fields would be created but could not be used as they would not be mapped in the database.
The extension is available to anyone with a CiviCRM installation. At Skvare, we are committed to developing open source software that empowers our clients and the wider community. This extension will be especially beneficial for organizations that have more complex CiviCRM installations and data tracking needs.
Download it today: https://github.com/Skvare/com.skvare.dbcfcheck
Are you a developer and want to know more?
Here is additional technical reasoning to further understand the issue:
MySQL table has a maximum row size limit of 65,535 bytes.
Nowadays civicrm is using COLLATION type on fields column:
utf8_unicode_ci : (bytes per char: 3) utf8mb4_unicode_ci : (bytes per char: 4)
The storage size per character uses 3 or 4 bytes. This ultimately reduces the possibility of the number of columns. A commonly used custom field type is “text field” and allows for up to 255 char (255 * 4 = 1020 is the row size of the column). If we go this way, the approximate maximum number we can add is around 60 columns (fields) into the custom group.