Screenshot 2023-10-25 at 10.48.41 PM.png
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
image7f7d4e.png

By

Pratik Vyas

·

Apr, 21 2015

·

2

min read

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!

Emoji example

More Reading

Limits on Table Column Count and Row Size

Published by

Pratik Vyas

on

Apr, 21 2015
0

Share

Add your comment

Success!

Error

Comments

No comments, yet.

Discussion

image7f7d4e.png

Paul Mugambi

·

3 days

ago

Beautiful read, and an insight into an individual I respect and have learned a lot from. Am inspired to trust the process and never give up.

image4c43d6.png

Anna Dane

·

5 days

ago

I must say this is a really amazing post, and for some of my friends who provide Best British Assignment Help, I must recommend this post to them.

Add your comment

Comment