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

InnoDB fails to remove AUTO_INCREMENT attribute

    XMLWordPrintable

    Details

      Description

      MDEV-6076 introduced persistent AUTO_INCREMENT values in InnoDB tables. The values are stored in the clustered index root page.

      While analyzing MDEV-19272, I noticed that InnoDB fails to remove an AUTO_INCREMENT attribute on a column in the following case:

      --source include/have_innodb.inc
      CREATE TABLE t (c INT AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB;
      ALTER TABLE t MODIFY c INT NOT NULL;
      INSERT INTO t SET c=1;
      DROP TABLE t;
      

      The value in the page would be wrongly updated like this:

      10.4 7ffa801cf2a7d0a4e55b84908dede6493c7ae73d

      #0  mlog_write_ull (ptr=0x7f4580618038 "", val=1, mtr=0x7f4580052248, mtr@entry=0x2) at /mariadb/10.4/storage/innobase/mtr/mtr0log.cc:304
      #1  0x0000558c761fe113 in page_set_autoinc (block=<optimized out>, index=<optimized out>, index@entry=0x7f456802f290, autoinc=<optimized out>, mtr=<optimized out>, mtr@entry=0x7f4580052248, reset=false)
          at /mariadb/10.4/storage/innobase/page/page0page.cc:250
      #2  0x0000558c763962fd in btr_cur_seaMajorrch_to_nth_level_func (index=<optimized out>, level=<optimized out>, level@entry=0, tuple=<optimized out>, tuple@entry=0x7f456802f290, mode=<optimized out>, 
          mode@entry=PAGE_CUR_LE, latch_mode=latch_mode@entry=2, cursor=<optimized out>, cursor@entry=0x7f4580051e00, ahi_latch=<optimized out>, file=<optimized out>, line=<optimized out>, mtr=<optimized out>, 
          autoinc=<optimized out>) at /mariadb/10.4/storage/innobase/btr/btr0cur.cc:2462
      #3  0x0000558c7625e521 in btr_pcur_open_low (index=<optimized out>, index@entry=0x7f456802f290, level=0, tuple=<optimized out>, tuple@entry=0x558c7693ee46, mode=mode@entry=PAGE_CUR_LE, 
          latch_mode=latch_mode@entry=2, cursor=0x7f4580051e00, cursor@entry=0x7f4580051df0, file=0x5 <error: Cannot access memory at address 0x5>, line=line@entry=2634, autoinc=1, mtr=0x7f4580052248)
          at /mariadb/10.4/storage/innobase/include/btr0pcur.ic:441
      #4  0x0000558c76263c8d in row_ins_clust_index_entry_low (flags=<optimized out>, flags@entry=0, mode=mode@entry=2, index=index@entry=0x7f456802f290, n_uniq=n_uniq@entry=1, entry=<optimized out>, 
          entry@entry=0x7f456802d830, n_ext=<optimized out>, n_ext@entry=0, thr=0x7f45680461f0) at /mariadb/10.4/storage/innobase/row/row0ins.cc:2633
      #5  0x0000558c7626a42b in row_ins_clust_index_entry (index=0x7f456802f290, entry=0x7f456802d830, thr=0x7f45680461f0, n_ext=n_ext@entry=0) at /mariadb/10.4/storage/innobase/row/row0ins.cc:3217
      #6  0x0000558c7626ca51 in row_ins_index_entry (index=0x7f456802f290, entry=0x7f456802d830, thr=0x7f45680461f0) at /mariadb/10.4/storage/innobase/row/row0ins.cc:3343
      #7  row_ins_index_entry_step (node=0x7f4568045fc8, thr=0x7f45680461f0) at /mariadb/10.4/storage/innobase/row/row0ins.cc:3512
      #8  row_ins (node=0x7f4568045fc8, thr=0x7f45680461f0) at /mariadb/10.4/storage/innobase/row/row0ins.cc:3671
      #9  row_ins_step (thr=<optimized out>, thr@entry=0x7f45680461f0) at /mariadb/10.4/storage/innobase/row/row0ins.cc:3821
      #10 0x0000558c7628c0a0 in row_insert_for_mysql (mysql_rec=<optimized out>, mysql_rec@entry=0x7f456802b058 "\377\001", prebuilt=<optimized out>, ins_mode=<optimized out>)
          at /mariadb/10.4/storage/innobase/row/row0mysql.cc:1420
      #11 0x0000558c76107803 in ha_innobase::write_row (this=0x7f456803d280, record=0x7f456802b058 "\377\001") at /mariadb/10.4/storage/innobase/handler/ha_innodb.cc:8122
      #12 0x0000558c75f289e6 in handler::ha_write_row (this=0x7f456803d280, buf=0x7f456802b058 "\377\001") at /mariadb/10.4/sql/handler.cc:6757
      #13 0x0000558c75c71cfb in write_record (thd=thd@entry=0x7f4568018d08, table=table@entry=0x7f456803c488, info=info@entry=0x7f45800531e0) at /mariadb/10.4/sql/sql_insert.cc:2061
      #14 0x0000558c75c6f328 in mysql_insert (thd=<optimized out>, thd@entry=0x7f4568018d08, table_list=<optimized out>, fields=<optimized out>, values_list=<optimized out>, update_fields=<optimized out>, 
          update_values=<optimized out>, duplic=DUP_ERROR, ignore=<optimized out>) at /mariadb/10.4/sql/sql_insert.cc:1078
      #15 0x0000558c75cada2a in mysql_execute_command (thd=thd@entry=0x7f4568018d08) at /mariadb/10.4/sql/sql_parse.cc:4600
      #16 0x0000558c75ca5874 in mysql_parse (thd=thd@entry=0x7f4568018d08, rawbuf=0x7f4568024090 "INSERT INTO t SET c=1", length=<optimized out>, parser_state=<optimized out>, parser_state@entry=0x7f4580054680, 
          is_com_multi=false, is_next_command=<optimized out>) at /mariadb/10.4/sql/sql_parse.cc:7992
      

      Note: in the native ALTER TABLE API there is no flag for removing the AUTO_INCREMENT attribute. In this case, ha_innobase::check_if_supported_inplace_alter() would observe ha_alter_info->handler_flags == ALTER_CHANGE_COLUMN_DEFAULT.

      InnoDB can support ALGORITHM=INSTANT removal of the AUTO_INCREMENT attribute, but it needs to remove the attribute from dict_table_t in commit_cache_norebuild(). For a table rebuild, there is no issue: after

      ALTER TABLE t MODIFY c INT NOT NULL, FORCE;
      

      the function page_set_autoinc() will not be invoked.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              kevg Eugene Kosov
              Reporter:
              marko Marko Mäkelä
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration