magento2: AUTO_INCREMENT grows on every INSERT ... ON DUPLICATE on InnoDB tables

Preconditions (*)

1. any magento version, mysql or mariaDB database
2. Magento 2.4-develop
3. InnoDB tables
4. Magento uses queries like this to save new values or update existing in tables see method \Magento\Eav\Model\Entity\AbstractEntity::_processAttributeValues

INSERT INTO `catalog_product_entity_int` (`attribute_id`, `store_id`, `entity_id`, `value`)
VALUES (97, 1, 123, 1)
ON DUPLICATE KEY UPDATE `value` = VALUES(`value`);

The problem causes that the table reaches max autoincrement value too fast that leads to errors. Changing column type to bigint just temporalny resolves the problem but autoincrement still grows to infinity Problem is noticeable when products or some attributes are updated often and new products/attribute values (e.g. in store) added. New attributes have bigger autoincrement than expected. Moreover, the method \Magento\Eav\Model\Entity\AbstractEntity::_processAttributeValues always uses statements INSERT INTO ... ON DUPLICATE KEY UPDATE to insert new value or update existing. So updating existing values this way when innodb_autoinc_lock_mode > 0 causes the issue.

Steps to reproduce (*)

1. Take auto_increment value from table catalog_product_entity_int
2. Remember that value (e.g. 1000)
3. Save one new product attribute (e.g. int type).

$this->productResourceModel->saveAttribute($product, 'status');

it generates query like above. INSERT INTO … ON DUPLICATE KEY UPDATE 4. New value inserted and auto_increment is 1001 5. Run the same query, or save the same attribute 4 more times 6. Save the same attribute under store (important that value not exist before insertion) 7. Check the auto_increment value of the table or MAX(value_id)

Expected result (*)

1. The auto_increment value or MAX(value_id) is 1002
2. Only one new record inserted that increased auto_increment by 1

Actual result (*)

1. The auto_increment value or MAX(value_id) is 1006
2. Only one new record inserted but auto_increment increased by 5

Workaround: (*)

Set parametr in my.cnf

innodb_autoinc_lock_mode=0

or recreate column

ALTER TABLE catalog_product_entity_int
drop column value_id;

ALTER TABLE catalog_product_entity_int
ADD value_id BIGINT(11) AUTO_INCREMENT NOT NULL FIRST,
ADD PRIMARY KEY (value_id);

---

Please provide Severity assessment for the Issue as Reporter. This information will help during Confirmation and Issue triage processes.

• Severity: S0 - Affects critical data or functionality and leaves users without workaround.
• Severity: S1 - Affects critical data or functionality and forces users to employ a workaround.
• Severity: S2 - Affects non-critical data or functionality and forces users to employ a workaround.
• Severity: S3 - Affects non-critical data or functionality and does not force users to employ a workaround.
• Severity: S4 - Affects aesthetics, professional look and feel, “quality” or “usability”.