1

Magento 2.3.5 Mariadb 10.2 PHP 7.2

We are hitting the constraint violation error below when running: php bin/magento setup:upgrade catalog_product_entity_varchar ran out of space at 2147483647 so we increased the value_id int to Bigint.

We are able to edit and add products again. But now have the constraint error coming up when it reaches schema / update. What is the best way to correct this, can we change the constraint rule?

SQLSTATE[23000]: Integrity constraint violation: 1062 ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '2147483647' for key 'PRIMARY', query was: ALTER TABLE catalog_product_entity_varchar MODIFY COLUMN value_id int(11) NOT NULL AUTO_INCREMENT COMMENT "Value ID"

Thanks so much.

4
  • I think the path to change the Rule is here: /public_html/magento/module-rule/Model/Resource/Model/Rule/Collection/AbstractCollection.php? But out of my depth. Basically we now have a site that cannot be upgraded. If anyone can point me in the right direction. Not having changed Integer would have meant the site was not longer usable. We are willing to pay to have this resolved.
    – Tonygo
    Feb 28, 2021 at 10:21
  • 1
    Ok, think I have found the answer, will try this shortly and update here: grep -r -l "catalog_product_entity_varchar" Found: vendor/magento/module-catalog/etc/db_schema.xml Located the table causing the issue: <table name="catalog_product_entity_varchar" resource="default" engine="innodb" comment="Catalog Product Varchar Attribute Backend Table"> <column xsi:type="int" name="value_id" padding="11" unsigned="false" nullable="false" identity="true" comment="Value ID"/> So changing int to bigint should fix the problem. Just one question, what could be the consequences?
    – Tonygo
    Mar 1, 2021 at 9:40
  • Problem fixed, hope this helps someone. We have 187885 products and 408 attributes. We certainly didn't expect the catalog_product_entity_varchar table to max out.
    – Tonygo
    Mar 2, 2021 at 19:00
  • Same issue, any solution? Mar 15, 2021 at 15:31

2 Answers 2

1

@Tonygo Thanks

Found: vendor/magento/module-catalog/etc/db_schema.xml Located the table causing the issue:

<table name="catalog_product_entity_varchar" resource="default" engine="innodb" comment="Catalog Product Varchar Attribute Backend Table"> 
<column xsi:type="int" name="value_id" padding="11" unsigned="false" nullable="false" identity="true" comment="Value ID"/> 

So changing int to bigint should fix the problem.

Its look like

 <column xsi:type="bigint " name="value_id" padding="11" unsigned="false" nullable="false" identity="true" comment="Value ID"/>
1
  • To do this in a custom extension, simply create a db_schema.xml for that extension and make sure the extension depends on Magento_Catalog, to overwrite the field definition.
    – Louis B.
    Jan 25 at 8:52
1

@MHK Developer is correct by increasing the type to bigint however I would suggest additionally changing to unsigned instead of signed doubling the amount of records in the table can hold.

value_id is an MySQL auto increment, and therefore will the entire space below 0 is wasted.

<?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_varchar" resource="default">
        <column xsi:type="bigint" name="value_id" unsigned="true" nullable="false" identity="true" comment="Value ID"/>
    </table>
</schema>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.