We got a bug report from one of our customers that they we were unable to add any more custom fields.
The traceback showed,
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs
It turns out that there is a limit to number of columns in an MySQL table and it depends on the MySQL engine, column types and the encoding. (eg, in case of varchar ie. it may take upto three bytes to store a utf-8 character).
The solution is to use the following options in MariaDB configuration:
- innodb_large_prefix: This increases the index prefix size to 3072, thereby increasing support for more number of columns in a table for the same column size.
- innodb_file_format: To use innodb_large_prefix, the innodb_file_format is required to be Barracuda. The Barracuda file format has support for new row formats viz DYNAMIC and COMPRESSED. With the COMPRESSED row format, the database size on disk for our ERPNext account reduced from 1.6G to 600M.
- file_per_table: To use the Barracuda file format, it is required to enable file_per_table. With this option enabled, MariaDB creates a new file for every tablespace instead of using the common ibdata1 file and thus reduces fragmentation. Also, with Barracuda, it is possible to support the utf8mb4 encoding which supports all characters of the Japanese language as demanded from our growing community in Japan.
Thus, with version 5, we decided to force migrate to Barracuda + utf8mb4. The patch for this takes a lot of time as it effectively rewrites every row in the database.
Now, if you're wondering that if you don't want so many columns and are never going to write Japanese in ERPNext, why bother with such a long migration?
I've a convincing answer. Now, you can store emojis in text fields!
More Reading
