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

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Incomplete
    • 10.1.38, 10.2.27, 10.2.28, 10.3.20
    • N/A

    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

              marko Marko Mäkelä
              scriptkidi Brian Sok
              Votes:
              2 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.