Tips for Custom Fields and InnoDB Row Size Limit

Custom fields can be added to a Calem standard table. The screenshot shows the Calem Designer form with the menu to add custom fields to asset table. This blog discusses the best practice in adding custom fields in Calem. The tips are most useful when there are many custom fields to be designed and added.

1. InnoDB Row Size Limit

InnoDB engine is used by Calem in MariaDB and MySQL. It allows row based locking and transactional management which is critical for enterprise grade applications such as Calem Enterprise. There are row size limits in InnoDB. Database admins need to take this into consideration when designing custom fields.

The maximum row size limit is 65,535 bytes.
- The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead. Changing some columns to TEXT will help with this limit.
The maximum row size for columns stored locally in a row is 8126 bytes.
- The maximum row size for columns locally in a row is smaller. We will discuss rules to mitigate this restriction below. When this limit is reached you will get an error message such as "ERROR 1118 (42000): Row size too large (> 8126)".

2. Rules for Custom Fields

The rules below may be followed when designing custom fields in Calem. They help reduce the chances of running into the size limits discussed above.

2.1 Dropdown and Lookup Fields

Both dropdown and lookup types are supported in Calem custom fields. They are of the most efficient ways to add custom fields regarding size limit. Additionally, they are important for better data integrity. See this blog for more info.

Use dropdown type for a custom field when possible.
- A custom field with values from a short list (such as a dozen or so values) is a good fit for dropdown.
- A custom dropdown can be added by Calem admin.
- A dropdown field takes 36 bytes.
Use lookup type for a custom field when possible.
- A lookup field takes its value from another table.
- A lookup field takes 36 bytes.

2.2 TEXT Fields

Select TEXT field type for varchar of 500 or more bytes.
- TEXT can hold up to 65,535 characters. It is counted as fixed 20 bytes in row sizing.
- Calem has a Long Text (Text) format for this purpose.

2.3 Field Length for Varchar Types

This point may be counterintuitive. Increasing field length for varchars type fields allows one to add more fields.

For MariaDB implementation use "varchar (86)" for varchar fields with lengths of 10 or greater.
- Calem uses UTF-8 encoding so each varchar takes 3 bytes. "Varchar (86)" will take up to 258 bytes (3 bytes a character) and will be stored off row so only 20 bytes are counted in local row storage.

For MySQL implementation use varchar (256) for variable fields with lengths of 10 or greater.
- For UTF-8 encoding varchar(256) will take up to 768 bytes (3 bytes a character) and will be stored off row so only 20 bytes are counted in local row storage. MySQL will store a varchar field off local storage when the size is over 768 bytes.

2.4 Dynamic Row Format

Row format is the first step to address the issue.

Make sure the row format for your InnoDB is Dynamic which allows storing fields off-row to overcome the column size limit (8126 bytes).
The setting can be checked from MySQL console.
Privileges may be required to modify this configuration. Service console access may be required for cloud database services.

2.5 Strict Mode

InnoDB strict mode (innodb_strict_mode) will turn warnings into errors. It is enabled by default.

Turn the strict mode off will resolve the error message.
The mode can be checked from MySQL console.
Privileges may be required to modify this configuration. Service console access may be required for cloud database services.

Additional resources