1

I am trying to save product attributes in my Magento2 system and there may be 100 thousands of attributes.

However, I see that the value specified as auto increment in the eav_attribute table is defined as smallint.

In this case, I get an error when I reach the value 65535 while saving these product attributes.

How can I solve this problem?

1 Answer 1

0

To change the data type for an existing column. You can create a new module and redefine the data type for it in db_schema.xml file.
In your case, we'll change the data type for the attribute_id column in the eav_attribute table from small_int (has a maximum value signed is 32767) to int (has a maximum value signed is 2147483647).

Assume the Vendor name is TuVan and the module name is ChangeEAVAtrributeTableColumnType, take the following steps:

Step 1: Create app/code/TuVan/ChangeEAVAtrributeTableColumnType/registration.php

<?php

use Magento\Framework\Component\ComponentRegistrar;

ComponentRegistrar::register(ComponentRegistrar::MODULE, 'TuVan_ChangeEAVAtrributeTableColumnType', __DIR__);

Step 2: Create app/code/TuVan/ChangeEAVAtrributeTableColumnType/etc/module.xml

<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Module/etc/module.xsd">
    <module name="TuVan_ChangeEAVAtrributeTableColumnType">
        <sequence>
            <module name="Magento_Eav"/>
        </sequence>
    </module>
</config>

Step 3: Create app/code/TuVan/ChangeEAVAtrributeTableColumnType/etc/db_schema.xml

<?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="eav_attribute">
        <column xsi:type="int" name="attribute_id" unsigned="true" nullable="false" identity="true"
                comment="Attribute ID"/>
    </table>
</schema>

Step 4: Run the setup upgrade command to install and enable the module:

bin/magento setup:upgrade

Step 5: Run compilation code command:
bin/magento setup:di:compile

Step 6: Run deploy static content command:
bin/magento setup:static-content:deploy -f

You're done!

Since other tables also refer to the eav_attribute column, you'll need to find them and make the change for them as well.

You can run the following SQL query to get all references for a database column.

SELECT 
  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_SCHEMA = '<database>' AND
  REFERENCED_TABLE_NAME = '<table>' AND
  REFERENCED_COLUMN_NAME = '<column>';
7
  • Thanks for your reply. Will this change work automatically for related tables that use the attribute_id field as a foreign key?
    – Zalmoxis
    Jun 5 at 7:21
  • I'm a little confused about what you're trying to say. Could you please provide more information or context?
    – Tu Van
    Jun 5 at 8:05
  • For example some tables has foreign key eav_attribute.attribute_id. And these field type should be same. Will they all be updated when I make this change?
    – Zalmoxis
    Jun 5 at 8:15
  • magento.eav_entity_attribute.EAV_ENTITY_ATTRIBUTE_ATTRIBUTE_ID_EAV_ATTRIBUTE_ATTRIBUTE_ID like this.
    – Zalmoxis
    Jun 5 at 8:41
  • I understand your point, the answer is no, this change only applies to the eav_attribute table. Therefore, you should find all related tables that are referred to the eav_attribute column and make the change for them as well. I've updated my answer to add the SQL query to get all references for a database column.
    – Tu Van
    Jun 5 at 9:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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