Before implementing the solution in this article (
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>';
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: 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:
[ AUTO_INCREMENT ]to the next value from the table or
Which solution you use depends on what has caused the issue. Refer to the steps below to isolate 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;
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
BIGINT schema update.
AUTO_INCREMENTto the next value from the table
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.
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;
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
BIGINT schema update. The datatype
BIGINT has a larger range of values.
To do so:
bin/magento setup:upgradeto 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>