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
Walk through the rules below when designing custom fields in Calem.
- Make sure the row format for your InnoDB is Dynamic which allows storing fields off-row to overcome the column size limit (8126 bytes). This can be checked from MySQL console.
- 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.
- For MariaDB implementation use varchar(86) for varchar fields with lengths of 20 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 20 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.