Fixing the “SQLSTATE[22003]: Numeric value out of range” entity_id Error in Magento 2 (Reliable Fix + SQL Repair)

One of the more alarming Magento 2 failures is a category that suddenly won't save, or a product import that dies mid-run, throwing SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for column 'entity_id' at row 1. You'll find the same Out of range value for column 'entity_id' fragment buried in var/log/exception.log and the var/report files. Until it's fixed, every new category-to-product assignment fails — products fall out of categories, imports abort, and your storefront silently shows the wrong catalog.

The surprising part: the table is usually small. This isn't a "too many rows" problem. It's an AUTO_INCREMENT counter that has burned through the range of a column declared too narrow — almost always on a store that has run high-volume product imports for years.

Why Magento 2 throws this error

Stock Magento declares catalog_category_product.entity_id as a signed INT identity column (unsigned="false" identity="true" in module-catalog/etc/db_schema.xml). The PRIMARY KEY on this table is composite — (entity_id, category_id, product_id) — and there's a separate UNIQUE key on (category_id, product_id). The entity_id is just a surrogate counter; the logical link is identified by (category_id, product_id). That distinction matters, because it's what lets the same logical link be handed a brand-new entity_id every time it's deleted and re-inserted, even though the live row count never grows.

Two things have to be true for this to bite. The column is signed (so the ceiling is the signed-INT max, 2,147,483,647 — half of what an unsigned INT would give), and something is churning the AUTO_INCREMENT counter far faster than the row count grows. To be clear about what does and does not churn it:

• The admin category-edit path does NOT churn it. Magento\Catalog\Model\ResourceModel\Category::_saveCategoryProducts is diff-based: it array_diff_keys the new and old member sets, INSERTs only net-new links, DELETEs only removed links, and runs in-place position UPDATEs for the rest. Re-saving a category with the same products consumes zero new IDs. Ordinary catalog editing will not overflow this column.
• The product CSV importer DOES churn it. The high-volume path is Magento\CatalogImportExport\Model\Import\Product::_saveProductCategories, which on the stock importer DELETEs the existing category links for the affected products and then re-inserts them via insertOnDuplicate (INSERT ... ON DUPLICATE KEY UPDATE).
• INSERT ... ON DUPLICATE KEY UPDATE wastes an AUTO_INCREMENT value on every execution — even when only the UPDATE branch fires — whenever innodb_autoinc_lock_mode > 0 (the default in modern MySQL). This is the well-documented behavior behind Magento GitHub issue #28387 and MySQL's own AUTO_INCREMENT handling notes. So a store that re-imports its catalog nightly burns counter values on every run with little or no net row growth.
• The column is signed, halving the usable range and bringing the wall forward to ~2.147B instead of the ~4.29B an unsigned INT would reach.

Put those together — years of frequent re-imports against the stock importer, on a signed-INT counter — and a table with a modest number of rows can quietly march its Auto_increment past 2.147B. The next INSERT can't be represented and MySQL rejects it with error 1264. Note that on this column the upstream community discussions about AUTO_INCREMENT exhaustion (issues #21890, #28387, #40269) actually center on value_id in the EAV value tables (catalog_product_entity_int, _varchar, _datetime). catalog_category_product.entity_id is the same class of problem reached by the same mechanism; the BIGINT-UNSIGNED widening below is the community workaround for those EAV columns, applied here to a column that hits the same wall.

1. Confirm the column type and the counter ceiling

Start at the database. You want two facts: the column is signed INT, and the counter is near its wall while the data is not.

SELECT COLUMN_TYPE FROM information_schema.COLUMNS
 WHERE TABLE_SCHEMA = DATABASE()
   AND TABLE_NAME = 'catalog_category_product'
   AND COLUMN_NAME = 'entity_id';
SHOW TABLE STATUS LIKE 'catalog_category_product';
SELECT MAX(entity_id) AS max_id, COUNT(*) AS rows_now FROM catalog_category_product;

COLUMN_TYPE reads int (signed); Auto_increment sits at or near 2147483647; and max_id is close to the ceiling while rows_now is a small fraction of it. That gap is the root cause confirmed — a counter exhausted by churn, not a table full of data.

2. Decide: reset the counter, or widen the column

Which fix you need depends on MAX(entity_id) from the previous step.

If MAX(entity_id) is comfortably below the INT ceiling — i.e. the live data has not actually exceeded the INT range, the counter has just drifted up into gaps — you can recover without any schema change by resetting AUTO_INCREMENT to one past the real max:

-- Only when MAX(entity_id) is well below 2,147,483,647.
-- Reads the current max and resets the counter to MAX+1.
SELECT @next := MAX(entity_id) + 1 FROM catalog_category_product;
SET @sql = CONCAT('ALTER TABLE catalog_category_product AUTO_INCREMENT = ', @next);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

This is the lighter remediation Adobe recommends when the counter — not the data — has overflowed, and it buys you headroom immediately. But if your churn rate is high it will simply happen again, and if MAX(entity_id) is itself at or near the INT ceiling, a reset can't help — the values genuinely don't fit in INT, and you must widen the column (steps 3–5).

3. Create a declarative-schema module that widens the column

Magento's declarative schema lets you merge a table fragment over core's. Redeclare only entity_id as bigint unsigned, and sequence your module after Magento_Catalog so your type wins. Leave the composite PRIMARY KEY and the unique key alone — you're only widening one column.

// app/code/Vendor/Module/etc/module.xml
<module name="Vendor_Module">
    <sequence><module name="Magento_Catalog"/></sequence>
</module>

// app/code/Vendor/Module/etc/db_schema.xml
<table name="catalog_category_product" resource="default" engine="innodb">
    <column xsi:type="bigint" name="entity_id" unsigned="true"
            nullable="false" identity="true" comment="Entity ID"/>
</table>

Add the usual registration.php. BIGINT UNSIGNED is a strict superset of INT for an AUTO_INCREMENT column that only ever holds positive values, so this is non-destructive — you're widening, never narrowing, and no existing value is lost. (This particular column is also safe because nothing references it with a foreign key. That's not true of every identity column — see the deeper-problem section.)

4. Enable the module and preview the change

Never apply schema blind. Generate the whitelist and dry-run first. Magento's declarative-schema dry-run does not print its DDL to the terminal — it writes the generated statements to var/log/dry-run-installation.log, regenerating that file on each run. So inspect the log, not stdout.

php bin/magento module:enable Vendor_Module
php bin/magento setup:db-declaration:generate-whitelist --module-name=Vendor_Module
php bin/magento setup:upgrade --dry-run
grep -iE 'catalog_category_product|entity_id' var/log/dry-run-installation.log

Note the -E flag — without it, grep treats catalog_category_product|entity_id as a literal string containing a pipe and matches nothing. With it, you should see a single pending statement of the form ALTER TABLE catalog_category_product MODIFY ... entity_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT — one column, nothing destructive.

5. Apply the change (back up; expect a table lock)

The ALTER rewrites the table and locks it during the rebuild. On large catalogs, run this in a maintenance window.

mysqldump <db> catalog_category_product > /tmp/ccp_backup.sql
php bin/magento maintenance:enable
php bin/magento setup:upgrade
php bin/magento maintenance:disable

Afterward, SHOW CREATE TABLE catalog_category_product shows entity_id as bigint unsigned (older MySQL and MariaDB still render the display width as bigint(20) unsigned; MySQL 8.0.19+ dropped it). The counter can now climb to roughly 1.8e19 — INSERTs stop overflowing.

6. Verify assignments work again

Prove it with a throwaway row past the old ceiling, then clean up.

INSERT INTO catalog_category_product (category_id, product_id, position) VALUES (2, 1, 0);
SELECT entity_id FROM catalog_category_product ORDER BY entity_id DESC LIMIT 1;
DELETE FROM catalog_category_product WHERE category_id = 2 AND product_id = 1;

The INSERT succeeds and the SELECT returns an entity_id above 2,147,483,647 — no SQLSTATE[22003]. Re-save a category in admin and the same holds.

When this points to a deeper problem

A widened column fixes this table, but the underlying pattern repeats. Watch for:

• Other signed-INT identity columns — the documented upstream auto-increment-exhaustion victims are the EAV value_id columns (catalog_product_entity_int, _varchar, _datetime), driven by the same INSERT ... ON DUPLICATE KEY UPDATE + innodb_autoinc_lock_mode > 0 behavior during mass imports (issues #21890 / #28387 / #40269). The BIGINT-UNSIGNED widening is the community workaround there too.
• The FK caveat for the general technique. catalog_category_product.entity_id is safe to widen alone because nothing has a foreign key pointing at it. That is not universal: widening an FK-referenced primary key (e.g. catalog_product_entity.entity_id) requires widening every referencing column in the same change, or the ALTER and FK re-creation will fail. Always check referenceColumn usage before extending this to another table.
• Import pipelines that delete-then-insert entire relationships every run, burning counters orders of magnitude faster than organic growth. The right structural fix is an incremental importer that DELETEs only removed links and INSERTs only added ones, so re-importing unchanged data consumes no counter values — exactly what the stock CSV importer does not do.
• Silent approach to the ceiling — nothing warns you, then it surfaces as a generic DB error far from its cause.

We've seen this most often on stores that have run high-frequency catalog imports for years on the stock importer — high scale and high churn, not ordinary category edits. The durable fix is an audit: compare every heavy table's Auto_increment against its column-type max, flag anything past ~70% of its ceiling, decide per table whether a counter reset or a widening is warranted, and pre-emptively address high-churn link and value tables rather than waiting for the next outage.

You've got a confirmed cause, a lighter reset option, a reversible widening, and a verification step. Pick the remediation your MAX(entity_id) calls for, apply it in a maintenance window, and your category saves and imports will run clean again.