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
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:
- Create a custom module inside the app/code/ directory.
- In the custom module create a db_schema.xml. In db_schema.xml you will set the datatype to
BIGINT
. - 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>
Related reading
- General MySQL guidelines in the Commerce Installation Guide
- Database upload loses connection to MySQL in our support knowledge base
- Database best practices for Adobe Commerce on cloud infrastructure in our support knowledge base
- Most common database issues in Adobe Commerce on cloud infrastructure in our support knowledge base
- Best practices for modifying database tables in the Commerce Implementation Playbook