Adobe Commerce database numeric value out of range, INT to BIGINT

WARNING

Before implementing the solution in this article (INT to BIGINT schema update) merchants must always check that the field they are going to change DOES NOT have any foreign-key relationships to another table. If the field does have foreign-key relationships to another table, there will issues because the related field is still INT. They can use the following query to verify this. This query lists the foreign-key relationships available in the database for the given table field:

SELECT
   TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME,REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
   REFERENCED_TABLE_SCHEMA = '<database_name>' AND
   REFERENCED_TABLE_NAME = '<table_name>' AND
   REFERENCED_COLUMN_NAME = '<table_field>';

Affected products and versions

This article provides solutions for when you are unable to save a product update, like a price change, or deleting, and duplicating a product.
You may see the error message The stock item was unable to be saved. Please try again. You might fail to deploy after a product update. You may also see the following MySQL error message when you run php bin/magento setup:upgrade (on Adobe Commerce on cloud infrastruture this error shows in the deployment logs):

SQLSTATE[22003]: Numeric value out of range: 167 Out of range value for column 'value_id' at row 1, query was: INSERT INTO `catalog_product_entity_decimal` (`attribute_id`,`store_id`,`row_id`,`value`) VALUES (?, ?, ?, ?) ON DUPLICATE KEY UPDATE `attribute_id` = VALUES(`attribute_id`), `store_id` = VALUES(`store_id`), `row_id` = VALUES(`row_id`), `value` = VALUES(`value`)

The solutions described in the article are:

  • Update the [ AUTO_INCREMENT ] to the next value from the table or
  • INT to BIGINT schema update

Which solution you use depends on what has caused the issue. Refer to the steps below to isolate the cause.

Steps to check the cause

Check the highest value of the primary key by running the following command in the terminal: SELECT MAX(value_id) FROM catalog_product_entity_int;

If the max(value_id) is lower than the max int(11) [ 4294967296 ], and the [ AUTO_INCREMENT ] has a value greater than or equal to the max int(11) [ 4294967296 ], then consider updating the [ AUTO_INCREMENT ] to the next value from the table. Otherwise, consider a INT to BIGINT schema update.

Update the AUTO_INCREMENT to the next value from the table

WARNING

Perform a database backup before altering the tables. Also, put the site into maintenance mode. In addition, it is also recommended to run the MYSQL optimize command on the database tables (only to tables where changes were made) after making the changes.

NOTE

The site should be in maintenance mode while running optimize command on specific tables. This completely rebuilds tables and will free space after deleting data from tables.

If the value shown is lower than max int(11) [ 4294967296 ] as shown in the below example terminal output, than a table [ AUTO_INCREMENT ] has changed to a number bigger or equal to the max [ int(11) ] value.

MariaDB [xxx]> SELECT MAX(value_id) FROM catalog_product_entity_int;
+---------------------+
| MAX(source_item_id) |
+---------------------+
|          4283174130 |
+---------------------+

To check if this has occured run the following command in the terminal:

MariaDB [xxx]> show create table catalog_product_entity_int;

...
) ENGINE=InnoDB AUTO_INCREMENT=4294967297 DEFAULT CHARSET=utf8 COMMENT='Catalog Product Integer Attribute Backend Table';

As you can see in the above example output the table [ AUTO_INCREMENT ] has changed to a bigger number than the max int(11) [ 4294967296 ]. The solution is to update the [ AUTO_INCREMENT] to the next value from the table:

ALTER TABLE catalog_product_entity_int AUTO_INCREMENT = 4283174131;

INT to BIGINT schema update

However, if when running the following query SELECT MAX(value_id) FROM catalog_product_entity_int; the value shown is higher than max int(11) [ 4294967296 ] consider doing a INT to BIGINT schema update. The datatype BIGINT has a larger range of values.

To do so:

  1. Create a custom module inside the app/code/ directory.
  2. In the custom module create a db_schema.xml. In db_schema.xml you will set the datatype to BIGINT.
  3. Add the following content and then execute bin/magento setup:upgrade to apply the above changes to the corresponding table.
<?xml version="1.0"?>
<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table name="catalog_product_entity_int">
        <column xsi:type="bigint" name="value_id" unsigned="false" nullable="false" identity="true"
                comment="Value ID"/>
    </table>
</schema>

On this page