EAV_ATTRIBUTE auto increment issue

Question

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?

Answer 1

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>';