Maximum Number of Fields in a Form

Sometimes while creating custom fields, you might have faced an error message that looks like this:

Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs.

What does it mean?

In simple terms, it means that you have reached the limit of the maximum number of fields for the specific form/doctype. So, what is the maximum limit of fields?

In MySQL, there is a hard limit of 4096 columns per table, but the effective maximum may be less for a given table. The exact limit depends on several interacting factors.

Every table (regardless of storage engine) has a maximum row size of 65,535 bytes. Storage engines may place additional constraints on this limit, reducing the effective maximum row size.

The maximum row size constrains the number (and possibly size) of columns because the total length of all columns cannot exceed this size (65,535 bytes). For example, utf8mb3 characters require up to 3 bytes per character, so for a VARCHAR(140) column, the server must allocate 140 × 3 = 420 bytes per value. Consequently, a table cannot contain more than 65,535 / 420 = 156 such columns.

In iVend Framework, VARCHAR(140) type columns are created based on "Data", "Link", "Select", "Dynamic Link", "Password" and "Read Only" field types. Hence, you can create approximately 156 such columns in the system.

Solution:

To add more fields to the system, you can do some changes.

Convert some of the fields to "Text", "Small Text", "Text Editor" or "Code" type field. In MySQL, BLOB and TEXT columns count from one to four plus eight bytes each toward the row-size limit because their contents are stored separately from the rest of the row. So, converting to those field types will free up some spaces and will allow addition of some more fields.
Set smaller value in the "Length" property while creating fields, the default value is 140. The System sets the length of VARCHAR based on this property and allocates size for those columns. Hence, smaller Length leads to add more fields.
Custom fields are not deleted from database automatically. If you're sure that you don't need data from any of the custom fields you can trim the table using trim-tables command

Usage

bench trim-tables [OPTIONS]

Description

Docfields removed from a particular DocType may not be deleted from their Database tables. This is by design to prevent premature data loss in Frappe. This won't be problematic for the most part, however, at some point you may face issues due to this lingering data.

Some benefits of regular table trimming are:

Smaller backup sizes
Reduced time taken to backup sites
Reduced Site Database Usages
Optimized queries in case of SELECT *
Database is clean and doesn't have anything hidden or redundant data

This command modifies the schema of tables in your site's database. It will by default, take a full backup of your entire database before modifying them. In case, these tables were modified errenously, you can restore your site to it's original state using the [restore command.

Options

--format,-fSet output format. Available options are JSON and TEXT. Defaults to TEXT.

Flags

--dry-run Show what would be deleted
--no-backup Do not backup the site. This is not recommended since this is a destructive operation.

Examples

There maybe a lot of lingering columns taking up the space. Perhaps you figured this out when you got an error that row size limit has reached while customizing your DocType. To be sure that there aren't any ghost columns, or old hidden fields taking up the space, you can be sure by running

bench --site {site} trim-tables --dry-run