Frappe Custom Fields Hit MariaDB's 'max 64 keys allowed': Finding the Excess Indexes

The migration was routine. You added one more custom field to the Item DocType, ran bench migrate, and it stopped dead with a MariaDB error that has nothing obviously to do with the field you just added:

This is one of those errors where the message is technically precise and completely unhelpful about the cause. You did not ask for 64 of anything. But somewhere along the years of customising this DocType, you and everyone before you have been quietly creating database indexes without ever seeing the word "index", and you have finally run out.

The good news: nothing is corrupt, no data is at risk, and the fix is a few minutes of listing indexes and unticking the ones that never needed to exist.

The exact error

pymysql.err.OperationalError: (1069, 'Too many keys specified; max 64 keys allowed')

You will see it during bench migrate, during a schema sync when saving a DocType, or when Frappe tries to add an index for a newly flagged field. The number is not negotiable: MariaDB (and MySQL) permit a maximum of 64 keys, meaning 64 non-primary indexes, on a single table.

Why this happens: Frappe creates indexes you never asked for

Frappe maps each DocType to a table named tab followed by the DocType name, so Item becomes tabItem. Several field settings that look purely like UI behaviour actually translate into a database index on the next migration:

• Unique creates a unique index to enforce the constraint.
• Set as In Standard Filter adds an index so the filter is fast in list view.
• A field with search index enabled gets its own index.
• Some link and select fields get indexed to keep lookups quick.

None of these say "I am adding an index" in the UI. So on a core DocType like Item or Customer, which already ships with a fair number of standard indexes, a few years of well-meaning customisation, ten fields marked In Standard Filter here, three Unique codes there, quietly walks the table up towards the ceiling. The migration that finally tips it over 64 is rarely the one that caused the problem. It is just the one holding the parcel when the music stops.

Step 1: List every index on the table

Open a database console with bench --site your-site.local mariadb and ask information_schema exactly what is on the table. Grouping by index name collapses multi-column indexes into one row so the count is honest:

SELECT index_name,
       GROUP_CONCAT(column_name ORDER BY seq_in_index) AS columns,
       non_unique
FROM information_schema.statistics
WHERE table_schema = DATABASE()
  AND table_name = 'tabItem'
GROUP BY index_name, non_unique
ORDER BY index_name;

To get the count that MariaDB is actually enforcing, count distinct index names excluding the primary key:

SELECT COUNT(DISTINCT index_name) AS total_keys
FROM information_schema.statistics
WHERE table_schema = DATABASE()
  AND table_name = 'tabItem'
  AND index_name != 'PRIMARY';

Expected output is a single number. If it is 63 or 64, you have found your wall. Now you need to know which of those indexes back a custom field you do not really need indexed.

Step 2: Find the wasteful custom fields

Cross-reference the index list against your custom fields. A field marked Unique or searchable will have an index whose name matches the field's fieldname. Ask the Custom Field table which fields carry those flags:

SELECT fieldname, label, `unique`, search_index, in_standard_filter
FROM `tabCustom Field`
WHERE dt = 'Item'
  AND (`unique` = 1 OR search_index = 1 OR in_standard_filter = 1)
ORDER BY fieldname;

Now apply judgement. Ask, for each one:

• Does this field genuinely need a Unique constraint at the database level, or was it ticked defensively? A free-text note field marked Unique is almost always a mistake.
• Is this field actually used as a filter in list view often enough to justify In Standard Filter, or was it enabled once and forgotten?
• Does anyone search on this field, or is the search index dead weight?

Every "no" is an index you can reclaim. You typically only need to free one or two to get past the wall and buy years of headroom.

Step 3: Remove the index without losing the field

Do this through Frappe, not with a raw DROP INDEX, so the schema and the DocType definition stay in agreement. Go to Customize Form, select the DocType (Item), open the field, and untick Unique, Set as In Standard Filter, or the search index option as appropriate. Save.

Then run the migration so Frappe reconciles the database to the new definition:

bench --site your-site.local migrate

Frappe drops the now-unneeded index during the schema sync. The field, its label, and every stored value are untouched, because you removed the index flag, not the column. If your original failing migration was adding a brand-new field, it will now succeed because there is room for its index.

This is the same "let the framework own the schema" principle I stress in adding custom fields to existing DocTypes in ERPNext: reach for Customize Form, not a hand-written ALTER TABLE, so migrations remain reproducible.

Verification

Re-run the count from Step 1 and confirm it dropped:

SELECT COUNT(DISTINCT index_name) AS total_keys
FROM information_schema.statistics
WHERE table_schema = DATABASE()
  AND table_name = 'tabItem'
  AND index_name != 'PRIMARY';

A number below 64, with margin, means you are clear. Then confirm the field itself survived with its data:

SELECT COUNT(*) FROM `tabItem` WHERE your_custom_field IS NOT NULL;

A non-zero count where you expect data proves you dropped only the index, not the column.

What people get wrong

Trying to raise the limit. There is no my.cnf setting for this. The 64-key ceiling is baked into the storage engine, not exposed as a tunable. Searching for a config flag is time you will not get back.

Dropping indexes with raw SQL. A manual DROP INDEX ... ON tabItem gets you past today's migration, but the DocType definition still thinks the field is Unique or filtered, so the very next bench migrate recreates the index and you are back to 64. Always change the flag in Customize Form so the definition and the schema agree.

Deleting the field to make room. Panicked teams sometimes delete a custom field to free an index, losing its data. That is throwing away the baby to lose the bathwater. Unticking the index flag frees exactly the same slot and keeps every value.

Marking everything Unique "to be safe". Unique is a database constraint with a real cost, not a nicety. Reserve it for fields that truly must not repeat (a serial code, a tax number), and let application logic handle softer uniqueness where a hard constraint is not warranted.

When it is still broken

• You freed indexes but migrate still fails. Check you edited the right DocType. A field can be added to Item via a Customize Form fixture in a custom app, so confirm which app owns it. Also confirm you are on the site you think you are; run the count against the actual site database.
• The count will not drop after migrate. Look for a fixture or a custom app that re-applies the Unique or filter flag on install. If the flag keeps coming back, the source of truth is in that app's custom_field fixtures, and you must change it there.
• You are genuinely at 64 legitimate indexes. This is rare, but if every index earns its keep, the DocType is doing too much. Consider whether some custom fields belong on a child DocType or a linked DocType instead of piling onto one wide table. Splitting responsibilities also helps query performance, in the same spirit as the field guide in indexes that actually work on tabGL Entry and tabStock Ledger Entry.

The 64-key limit is not a Frappe defect, it is MariaDB drawing a sensible line. The real lesson is to treat "Unique" and "In Standard Filter" as the index-creating operations they quietly are, and to spend those slots deliberately rather than by accident.