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

Strange auto_increment counter setting changes on direct upgrade from 10.1 to 10.3

Details

    Description

      After a direct upgrade from 10.1.40 to 10.3.17, SHOW CREATE TABLE output in mysqldump shows correct AUTO_INCREMENT counter values on the first two tables, but then flips to

      Before the upgrade:

      ENGINE=InnoDB AUTO_INCREMENT=2284
      		 
      ENGINE=InnoDB AUTO_INCREMENT=4763
      		 
      ENGINE=InnoDB AUTO_INCREMENT=1621982
      		 
      ENGINE=InnoDB AUTO_INCREMENT=3
      		 
      ENGINE=InnoDB AUTO_INCREMENT=1395246
      		 
      ENGINE=InnoDB AUTO_INCREMENT=927
      		 
      ...

      becomes this after upgrade:

      ENGINE=InnoDB AUTO_INCREMENT=2284
      		 
      ENGINE=InnoDB AUTO_INCREMENT=4763
      		 
      ENGINE=InnoDB AUTO_INCREMENT=4784423209
      		 
      ENGINE=InnoDB AUTO_INCREMENT=4784423220
      		 
      ENGINE=InnoDB AUTO_INCREMENT=4784423231
      		 
      ENGINE=InnoDB AUTO_INCREMENT=4784423242
      		 
      ...

      Note how starting with the third auto-inrement table the counter becomes way higher then originally, and then gets incremented by 11 for each following table.

      Attachments

        Issue Links

          is part of

          Task - A task that needs to be done. MDEV-22357 Clearing InnoDB autoincrement counter when upgrading from MariaDB < 10.2.4

          • Major - Major loss of function.
          • Open
          relates to

          Task - A task that needs to be done. MDEV-6076 Persistent AUTO_INCREMENT for InnoDB

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-22167 auto_increment counters changed weirdly after direct 10.1 -> 10.3 upgrade

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            marko Marko Mäkelä added a comment -

            hholzgra, I think that this should affect an upgrade to 10.2 already, due to MDEV-6076.

            I think that the plausible explanation is that the PAGE_MAX_AUTOINC field in some clustered index root pages was not 0, but garbage, before the upgrade. MDEV-6076 repurposed the field PAGE_MAX_TRX_ID, which was previously only used on secondary index leaf pages. Before a bug fix in MySQL 5.1.48, InnoDB could write garbage to unused data fields; since then, such fields are always written as 0.

            It should be possible to reset the AUTO_INCREMENT value by a statement like this:

            ALTER TABLE t AUTO_INCREMENT = 1;

            This should reset the counter to a value that is not smaller than the current maximum in the table.

            I will try to double-check if the PAGE_MAX_TRX_ID was always zero-initialized. I seem to remember that I concluded during MDEV-6076 development that it is the case.

            marko Marko Mäkelä added a comment - hholzgra , I think that this should affect an upgrade to 10.2 already, due to MDEV-6076 . I think that the plausible explanation is that the PAGE_MAX_AUTOINC field in some clustered index root pages was not 0, but garbage, before the upgrade. MDEV-6076 repurposed the field PAGE_MAX_TRX_ID , which was previously only used on secondary index leaf pages. Before a bug fix in MySQL 5.1.48 , InnoDB could write garbage to unused data fields; since then, such fields are always written as 0. It should be possible to reset the AUTO_INCREMENT value by a statement like this: ALTER TABLE t AUTO_INCREMENT = 1; This should reset the counter to a value that is not smaller than the current maximum in the table. I will try to double-check if the PAGE_MAX_TRX_ID was always zero-initialized. I seem to remember that I concluded during MDEV-6076 development that it is the case.
            marko Marko Mäkelä added a comment -

            As a starting point, I checked out the MySQL 5.1.47 source code. Side note: I had forgotten a glaring bug: changes of the PAGE_MAX_TRX_ID were not being redo-logged in the built-in InnoDB of MySQL 5.1. It was only fixed in the InnoDB Plugin, which became the basis of the InnoDB in the 5.5 release.

            In MySQL 5.1.47, page_create() (which is invoked for creating B-tree pages) did always initialize the PAGE_MAX_TRX_ID, so we do know that whenever the field was unused on a B-tree page, it must have been 0:

            	page_set_max_trx_id(page, ut_dulint_zero);

            This call was present in the very beginning of the InnoDB revision history.

            hholzgra, I am afraid that I cannot explain the observed anomaly with a software bug. I would need more information at the very least.

            marko Marko Mäkelä added a comment - As a starting point, I checked out the MySQL 5.1.47 source code. Side note: I had forgotten a glaring bug: changes of the PAGE_MAX_TRX_ID were not being redo-logged in the built-in InnoDB of MySQL 5.1. It was only fixed in the InnoDB Plugin , which became the basis of the InnoDB in the 5.5 release. In MySQL 5.1.47, page_create() (which is invoked for creating B-tree pages) did always initialize the PAGE_MAX_TRX_ID , so we do know that whenever the field was unused on a B-tree page, it must have been 0: page_set_max_trx_id(page, ut_dulint_zero); This call was present in the very beginning of the InnoDB revision history . hholzgra , I am afraid that I cannot explain the observed anomaly with a software bug. I would need more information at the very least.
            serg Sergei Golubchik added a comment -

            hholzgra, any chance you could provide more infore or should we close it?

            serg Sergei Golubchik added a comment - hholzgra , any chance you could provide more infore or should we close it?

            People

              marko Marko Mäkelä
              hholzgra Hartmut Holzgraefe
              Votes:
              0 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.