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



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


      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.


        Issue Links



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



                Git Integration

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