Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.4.4, 10.5.0, 10.6.0
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
- is caused by
-
MDEV-12836 Avoid table rebuild when removing of auto_increment settings
-
- Closed
-
- relates to
-
-
- Closed
-
-
-
- Closed
-