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

ilnytskyi commented 4 years ago

Preconditions (*)

any magento version, mysql or mariaDB database
Magento 2.4-develop
InnoDB tables

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 (*)

Take auto_increment value from table catalog_product_entity_int
Remember that value (e.g. 1000)
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
New value inserted and auto_increment is 1001
Run the same query, or save the same attribute 4 more times
Save the same attribute under store (important that value not exist before insertion)
Check the auto_increment value of the table or MAX(value_id)

Expected result (*)

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

Actual result (*)

The auto_increment value or MAX(value_id) is 1006
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.
[x] 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”.

m2-assistant[bot] commented 4 years ago

Hi @ilnytskyi. Thank you for your report. To help us process this issue please make sure that you provided the following information:

Summary of the issue
Information on your environment
Steps to reproduce
Expected and actual results

Please make sure that the issue is reproducible on the vanilla Magento instance following Steps to reproduce. To deploy vanilla Magento instance on our environment, please, add a comment to the issue:

@magento give me 2.4-develop instance - upcoming 2.4.x release

For more details, please, review the Magento Contributor Assistant documentation.

Please, add a comment to assign the issue: @magento I am working on this

Join Magento Community Engineering Slack and ask your questions in #github channel.

ilnytskyi commented 4 years ago

At first growing auto_increment will cause the issue

SQLSTATE[22003]: Numeric value out of range: 167 Out of range value for column 'value_id' at row 1, query was: INSERT INTO catalog_product_entity_int

Then devs might consider changing the column type to allocate bigger ints

ALTER TABLE catalog_product_entity_int MODIFY value_id bigint(20) unsigned NOT NULL auto_increment COMMENT 'Value ID';

but the value will be growing

It seems to be DB issue or db configuration issue, however It is not mentioned or documented by Magento.

Additionally, magento does not chose between INSERT or UPDATE when add or modify values so the issue happens.

https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html

Related issue in other repo: https://github.com/dweeves/magmi-git/issues/320

stale[bot] commented 3 years ago

This issue has been automatically marked as stale because it has not had recent activity. It will be closed after 14 days if no further activity occurs. Thank you for your contributions.

hostep commented 3 years ago

Hmm, let's remove the stale issue label, this sounds pretty important.

@sidolov: can somebody verify this and if verified add some priorities?

m2-assistant[bot] commented 3 years ago

Hi @engcom-Delta. Thank you for working on this issue. In order to make sure that issue has enough information and ready for development, please read and check the following instruction: :point_down:

[ ] 1. Verify that issue has all the required information. (Preconditions, Steps to reproduce, Expected result, Actual result).
Details
If the issue has a valid description, the label Issue: Format is valid will be added to the issue automatically. Please, edit issue description if needed, until label Issue: Format is valid appears.
[ ] 2. Verify that issue has a meaningful description and provides enough information to reproduce the issue. If the report is valid, add Issue: Clear Description label to the issue by yourself.
[ ] 3. Add Component: XXXXX label(s) to the ticket, indicating the components it may be related to.
[ ] 4. Verify that the issue is reproducible on 2.4-develop branch
Details
- Add the comment @magento give me 2.4-develop instance to deploy test instance on Magento infrastructure.
- If the issue is reproducible on 2.4-develop branch, please, add the label Reproduced on 2.4.x.
- If the issue is not reproducible, add your comment that issue is not reproducible and close the issue and stop verification process here!
[ ] 5. Add label Issue: Confirmed once verification is complete.
[ ] 6. Make sure that automatic system confirms that report has been added to the backlog.

magento-engcom-team commented 3 years ago

:white_check_mark: Confirmed by @engcom-Delta Thank you for verifying the issue. Based on the provided information internal tickets MC-40808 were created

Issue Available: @engcom-Delta, You will be automatically unassigned. Contributors/Maintainers can claim this issue to continue. To reclaim and continue work, reassign the ticket to yourself.

NateSwanson7 commented 3 years ago

Any progress on this? This seems like an issue that could potentially start affecting many instances since M2 has been around for a few years now.

JelleGe commented 3 years ago

Any update on this? We face this issue also and now reached the max of int on table "catalog_product_entity_varchar"

ilnytskyi commented 3 years ago

@JelleGe try to recreate a column and change autoincrement lock mode to 0 as I described in workaround.

JelleGe commented 3 years ago

It's a fine temporary solution that works, but it's a matter of time before it returns. So will there become a solution in a future Magento core update?

JesKingDev commented 3 years ago

@JelleGe I created a gist for getting the database table back in shape if you've maxed out the auto_increment. https://gist.github.com/JesKingDev/af70e92740215009ab1bebe5a2cdc17f It's very thorough and has a lot of checkpoints to ensure you know what's being done.

This isn't a long-term solution, but should definitely buy you back some time while the root cause is fixed.

hostep commented 3 years ago

@engcom-Delta, @sdzhepa: why did this issue got closed without any sort of extra information?

bramstroker commented 3 years ago

This is a major issue in lot of Magento installations. Quite concerning this just gets closed without further explanation.

veloraven commented 3 years ago

This issue was closed because we were not able to reproduce it on the latest code. After product update I can see in the catalog_product_entity_int table that attribute value was updated, but AUTO_INCREMENT for this table was not updated. This value was only increased during product creation, but not during product update. If you can provide exact steps which allow to reproduce problem on Clean Magento instance, you can reopen this issue.

ilnytskyi commented 3 years ago

@veloraven Here is the reproducer https://github.com/magento/magento2/pull/34339

I think that by default magento should suggest to use innodb_autoinc_lock_mode = 0 as insertOnDuplicate is widely used.

PS. Confirmed issues should not be closed by bots

pankajpragma commented 3 years ago

Thank You @ilnytskyi, You saved my life. I found the same issue on my project. This is a major issue in the Magento & should be considered on priority.

pmonosolo commented 2 years ago

Looks like this is related to this issue posted all the way in 2019: https://github.com/magento/magento2/issues/21890

I'm hitting the same limit on 2.4.3

These values are jumping sometimes by 10s of thousands like you see in this screenshot:

pmonosolo commented 2 years ago

@JelleGe I created a gist for getting the database table back in shape if you've maxed out the auto_increment. https://gist.github.com/JesKingDev/af70e92740215009ab1bebe5a2cdc17f It's very thorough and has a lot of checkpoints to ensure you know what's being done.

This isn't a long-term solution, but should definitely buy you back some time while the root cause is fixed.

Step 8 (truncate / reset auto_increment) did not work for me in my testing environment.

For some reason the auto_increment stays the same even after ALTER TABLE catalog_product_entity_varchar AUTO_INCREMENT = 1;

convenient commented 2 years ago

Oh yeah @pmonosolo that does indeed look to be the same symptoms as my issue from #21890

In that case it was fixed in 2.2, but it seems like some kind of regression since then.

Thanks for flagging this to me! 😱

JelleGe commented 2 years ago

Is there any update on this issue?

szagabesz commented 2 years ago

Any update on this issue? Just have run into it with a high-traffic Magento instance.

m2-assistant[bot] commented 2 years ago

Hi @engcom-Hotel. Thank you for working on this issue. In order to make sure that issue has enough information and ready for development, please read and check the following instruction: :point_down:

[ ] 1. Verify that issue has all the required information. (Preconditions, Steps to reproduce, Expected result, Actual result).
Details
If the issue has a valid description, the label Issue: Format is valid will be added to the issue automatically. Please, edit issue description if needed, until label Issue: Format is valid appears.
[ ] 2. Verify that issue has a meaningful description and provides enough information to reproduce the issue. If the report is valid, add Issue: Clear Description label to the issue by yourself.
[ ] 3. Add Component: XXXXX label(s) to the ticket, indicating the components it may be related to.
[ ] 4. Verify that the issue is reproducible on 2.4-develop branch
Details
- Add the comment @magento give me 2.4-develop instance to deploy test instance on Magento infrastructure.
- If the issue is reproducible on 2.4-develop branch, please, add the label Reproduced on 2.4.x.
- If the issue is not reproducible, add your comment that issue is not reproducible and close the issue and stop verification process here!
[ ] 5. Add label Issue: Confirmed once verification is complete.
[ ] 6. Make sure that automatic system confirms that report has been added to the backlog.

engcom-Hotel commented 2 years ago

Hello @ilnytskyi,

Thanks for the report and collaboration!

We have tried to reproduce the issue in Magento's latest development branch ie 2.4-develop and the issue is still reproducible. We have followed the exact steps and refer to this PR https://github.com/magento/magento2/pull/34339 to reproduce the issue.

Hence confirming the issue.

Thanks

github-jira-sync-bot commented 2 years ago

:white_check_mark: Jira issue https://jira.corp.adobe.com/browse/AC-6628 is successfully created for this GitHub issue.

m2-assistant[bot] commented 2 years ago

:white_check_mark: Confirmed by @engcom-Hotel. Thank you for verifying the issue.
Issue Available: @engcom-Hotel, You will be automatically unassigned. Contributors/Maintainers can claim this issue to continue. To reclaim and continue work, reassign the ticket to yourself.

convenient commented 2 years ago

Just hit this on a staging instance (thankfully)

pmonosolo commented 2 years ago

Just hit this on a staging instance (thankfully)

@convenient What version of M2 are you running?

convenient commented 2 years ago

@pmonosolo It was a 2.4.2 instance

pmonosolo commented 2 years ago

@convenient hmm. Since 2021, I've been using lock_mode = 0 and its been holding up. Its not perfect - I'm having issues with DB locking - possibly because of having to use lock_mode = 0.

convenient commented 2 years ago

Thanks @pmonosolo

We're just building a module that will fire an alert when we are over 90% capacity on auto increment IDs, to allow us to do maintenance.

It's only every few years we've encountered it

pmonosolo commented 2 years ago

Thanks @pmonosolo

We're just building a module that will fire an alert when we are over 90% capacity on auto increment IDs, to allow us to do maintenance.

It's only every few years we've encountered it

Whats the plan when you do hit the limit?

Change to differen field type or there is some way of purging the table?

convenient commented 2 years ago

@pmonosolo https://github.com/magento/magento2/issues/21890

The "workaround" section on that is largely valid

Due to declarative schema we need to drop the constraints on the existing table, and add properly named constraints on the new table before swapping the table names around.

But this way we end up back at auto_increment=1 with the original Magento schema intact

TomJ1588 commented 1 year ago

Any update on this issue? Just ran into this in production. Any timeframe on a fix?

tuey30716 commented 1 year ago

Any update on this issue? Just ran into this in production. Any timeframe on a fix?

change column type on value_id to bigint(20) or following this step https://gist.github.com/JesKingDev/af70e92740215009ab1bebe5a2cdc17f#2-get-the-current-max-value_id-used-in-the-table

engcom-Bravo commented 1 year ago

Hello,

As I can see this issue got fixed in the scope of the internal Jira ticket ACP2E-1358 by the internal team Related commits:https://github.com/search?q=repo%3Amagento%2Fmagento2+ACP2E-1358&type=commits

Based on the Jira ticket, the target version is 2.4.7-beta1.

Thanks

mahesh-singh-pinja commented 4 months ago

This issues doesn't looks fixed as changes reverted here: https://github.com/magento/magento2/commit/2eb09dbee39fcc98a1d270cb2ad3b9fd2247f021

So when attributes update via action: https://github.com/magento/magento2/blob/2.4.7-p1/app/code/Magento/Catalog/Model/ResourceModel/Product/Action.php#L138

It will still use insertOnDuplicate and the same issue happens again.

@sidolov @ilnytskyi

magento / magento2