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

Auto_increment values changed after restart

Details

    Description

      I imported sql dump from MySQL 5.5.62 to MariaDB. Imported file has about 2GB. Import was successful, but after MariaDB restart AUTO_INCREMENT values on some tables changed (for example: on table with max ID 11504 AUTO_INCREMENT changed from 11505 to 2).

      After some testing I figured out that removing UNIQUE_CHECKS option from import file fixes this. I cannot see any common cause, some affected tables have unique keys and some doesn't. There are no errors on log or anywhere. Import was tested on newly installed Ubuntu server without any additional software running.

      What other information should I provide?

      SQL dump was created with mysqldump Ver 10.13 Distrib 5.5.62:

      mysqldump -p --single-transaction --no-create-db --no-create-info --skip-add-drop-table --skip-comments "database_name" > db.sql
      

      Command for file import:

      mariadb -u root "database_name" < "db.sql"
      

      Using default configuration with these changes:

      Attachments

        1. 1-create-tables.sql
          0.7 kB
        2. 2-import-broken.sql
          2 kB
        3. 3-import-fixed.sql
          2 kB
        4. image-2023-03-06-14-09-34-196.png
          image-2023-03-06-14-09-34-196.png
          15 kB
        5. table-status-result.png
          table-status-result.png
          32 kB

        Issue Links

          Activity

            Transition Time In Source Status Execution Times
            Sergei Golubchik made transition -
            Open Needs Feedback
            24d 1h 11m 1
            Elena Stepanova made transition -
            Needs Feedback Open
            80d 9h 30m 1
            Alice Sherepa made transition -
            Open Confirmed
            14h 14m 1
            Thirunarayanan Balathandayuthapani made transition -
            Confirmed In Progress
            35d 1h 40m 1
            Thirunarayanan Balathandayuthapani made transition -
            In Progress In Review
            39s 1
            Marko Mäkelä made transition -
            In Review Stalled
            1d 14h 43m 1
            Thirunarayanan Balathandayuthapani made transition -
            Stalled Closed
            6h 57m 1

            People

              thiru Thirunarayanan Balathandayuthapani
              martin.stepar Martin Štěpař
              Votes:
              0 Vote for this issue
              Watchers:
              5 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.