-
Notifications
You must be signed in to change notification settings - Fork 9.3k
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
AUTO_INCREMENT grows on every INSERT ... ON DUPLICATE on InnoDB tables #28387
Comments
Hi @ilnytskyi. Thank you for your report.
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:
For more details, please, review the Magento Contributor Assistant documentation. Please, add a comment to assign the issue:
|
At first growing auto_increment will cause the issue
Then devs might consider changing the column type to allocate bigger ints
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: |
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. |
Hmm, let's remove the stale issue label, this sounds pretty important. @sidolov: can somebody verify this and if verified add some priorities? |
Hi @engcom-Delta. Thank you for working on this issue.
|
✅ Confirmed by @engcom-Delta 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. |
✅ Confirmed by @engcom-Hotel. Thank you for verifying the issue. |
Just hit this on a staging instance (thankfully) |
@convenient What version of M2 are you running? |
@pmonosolo It was a 2.4.2 instance |
@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. |
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? |
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 |
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) |
Hello, As I can see this issue got fixed in the scope of the internal Jira ticket ACP2E-1358 by the internal team Based on the Jira ticket, the target version is 2.4.7-beta1. Thanks |
Preconditions (*)
see method
\Magento\Eav\Model\Entity\AbstractEntity::_processAttributeValues
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 statementsINSERT 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 (*)
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 (*)
Actual result (*)
Workaround: (*)
Set parametr in my.cnf
or recreate column
Please provide Severity assessment for the Issue as Reporter. This information will help during Confirmation and Issue triage processes.
The text was updated successfully, but these errors were encountered: