Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-20892

AUTO_INCREMENT is set lower than the max value of the primary_key

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.1.38, 10.2.27, 10.2.28, 10.3.20
    • Fix Version/s: 10.2
    • Environment:

      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

            Activity

              People

              • Assignee:
                alice Alice Sherepa
                Reporter:
                scriptkidi Brian Sok
              • Votes:
                1 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated: