MariaDB changes in Version 5
We got a bug report from one of our customers that they we were unable to add any more custom fields. The solution to this added support for
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_file_format is required to be
Barracuda. The Barracuda file format has support for new row formats viz
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!
Pratik takes care of Frappecloud and nags everyone about blasphemous engineering practices. He's also responsible for any cryptic responses and texts related to frappe and erpnext that you may find.