Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Incomplete
-
10.1.38, 10.2.27, 10.2.28, 10.3.20
-
It's running in a 2 nodes galera cluster on mariadb 10.2.27 and debian 9. Both nodes have 16 cores (32 threads) and 196GB ram with 4TB of disk space. One node is the primary node that handles all the connections and the other is the hot standby.
UPDATE: This problem is also happening on 10.3.20 and various server setupsIt's running in a 2 nodes galera cluster on mariadb 10.2.27 and debian 9. Both nodes have 16 cores (32 threads) and 196GB ram with 4TB of disk space. One node is the primary node that handles all the connections and the other is the hot standby. UPDATE: This problem is also happening on 10.3.20 and various server setups
Description
In our situation with galera we have a problem that on random moments the `AUTO_INCREMENT` gets set to a lower value than the maximum value of the primary_key of that table. We have a total of ~700 databases with a total size of ~180GB and in about 24 hours of handling requests 20 tables change to having this problem.
For example:
Database testdb1 has two module tables for car parts: module_cars and module_car_parts. The table module_cars has 100 records and has a AUTO_INCREMENT of 102 (+2 cause of the offset in a node 2 cluster). When I check this table 24 hours later the table got changed to still having 100 records but having a AUTO_INCREMENT of 89. This value can vary from 90% to 10% lower than the max value of the primary_key.
I can't find why this is happening. I found https://jira.mariadb.org/browse/MDEV-6076 where there was a problem with handling AUTO_INCREMENT's but that should've be fixed a long time ago.
See attachment for the `my.cnf`
UPDATE: I have upgraded mariadb to 10.3.20 and removed galera from our setup but without any change. Even our other servers without replication and with master-slave replication keep getting this issue where the AUTO_INCREMENT gets set lower then the max value of the primary_key with AUTO_INCREMENT. This causes major data corruption if this is not contained. Galera seems not to be the only issue here.
Attachments
Issue Links
- relates to
-
-
- Closed
-
-
-
- Confirmed
-
-
MDEV-6076 Persistent AUTO_INCREMENT for InnoDB
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
Activity
sanja: I had to bounce it to as it's clear auto-increment behaviour in the server. To speed up grasping the problem consider to read through https://jira.mariadb.org/browse/MDEV-20892?focusedCommentId=143383&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-143383.
We have had similar reports in the past, for example, MDEV-15400, but sadly without enough details to reproduce the problem.
scriptkidi, jcapcik can you please provide more information:
- Does CHECK TABLE report any errors before or after the upgrade?
- Can you repeat this problem when the table is created in 10.2 in the first place?
MDEV-6076repurposed a data field that I thought should always have been written as 0. - Show od -Ax -t x1 -N 8 -j 0xc038 tablename.ibd both before and after the upgrade, if possible. That should be the persistent value of the AUTO_INCREMENT field.
If you have some nonzero garbage (unfortunately, old versions of InnoDB wrote uninitialized data to unused fields), it would be interpreted as the AUTO_INCREMENT value.
We have some claims that the AUTO_INCREMENT would be slightly off, but no test case to back those claims. In MDEV-20357 the user seems to have caused corruption by questionable actions ("renamed innodb_logfiles"). In MDEV-6076, the PAGE_MAX_AUTO_INC value in the clustered index root page is being updated atomically in the same mini-transaction that will update the leaf page. It should only be able to get out of sync if data files are being copied in an unsafe way while the server is running, or if there is a bug in Mariabackup, or proper log-based recovery is being prevented.
One more idea: Could everything be working as designed, but you are confused due to MDEV-13094?
A possible explanation of this bug could be that the adjustment MDEV-12123 for MDEV-6076 was not present when the data files were originally written. The commit message mentions ALTER TABLE…IMPORT TABLESPACE, but similar issues would occur when upgrading.
I think that a possible work-around could be to execute
ALTER TABLE t AUTO_INCREMENT=1; |
on every InnoDB table that contains AUTO_INCREMENT columns.
Is there any update on this?