[MDEV-26224] InnoDB fails to remove AUTO_INCREMENT attribute - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.4.4, 10.5.0, 10.6.0
Fix Version/s: 10.4.25, 10.5.16, 10.6.8, 10.7.4, 10.8.3, 10.9.1
Component/s: Storage Engine - InnoDB
Labels:
- regression-10.4

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

MDEV-6076 Persistent AUTO_INCREMENT for InnoDB

Closed

MDEV-19272 virtual enum_alter_inplace_result ha_innobase::check_if_supported_inplace_alter(TABLE*, Alter_inplace_info*): Assertion `((((*af)->unireg_check) & 127U) == Field::NEXT_NUMBER) == (((f->unireg_check) & 127U) == Field::NEXT_NUMBER)' failed.

Closed

Activity

Ascending order - Click to sort in descending order

Marko Mäkelä created issue - 2021-07-23 10:26

Marko Mäkelä made changes - 2021-07-23 10:26

Field	Original Value	New Value
Link		This issue relates to ~~MDEV-19272~~ [ ~~MDEV-19272~~ ]

Marko Mäkelä made changes - 2021-07-23 10:26

Link

This issue relates to ~~MDEV-6076~~ [ ~~MDEV-6076~~ ]

Marko Mäkelä made changes - 2021-07-23 10:48

Link

This issue is caused by ~~MDEV-15563~~ [ ~~MDEV-15563~~ ]

Marko Mäkelä made changes - 2021-07-23 10:48

Fix Version/s	10.2 [ 14601 ]
Fix Version/s	10.3 [ 22126 ]
Affects Version/s		10.6.0 [ 24431 ]
Affects Version/s		10.5.0 [ 23709 ]
Affects Version/s		10.4.3 [ 23230 ]
Affects Version/s	10.2 [ 14601 ]
Affects Version/s	10.0 [ 16000 ]
Affects Version/s	10.1 [ 16100 ]
Affects Version/s	10.3 [ 22126 ]
Affects Version/s	10.4 [ 22408 ]
Affects Version/s	10.5 [ 23123 ]
Affects Version/s	10.6 [ 24028 ]
Labels		regression-10.4

Marko Mäkelä made changes - 2021-07-23 12:33

Link

This issue is caused by ~~MDEV-12836~~ [ ~~MDEV-12836~~ ]

Marko Mäkelä made changes - 2021-07-23 12:33

Affects Version/s		10.4.4 [ 23310 ]
Affects Version/s	10.4.3 [ 23230 ]

Marko Mäkelä made changes - 2021-07-23 12:33

Link

This issue is caused by ~~MDEV-15563~~ [ ~~MDEV-15563~~ ]

Marko Mäkelä made changes - 2021-07-23 13:45

Assignee	Marko Mäkelä [ marko ]	Eugene Kosov [ kevg ]
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: {code:sql} --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; {code} The value in the page would be wrongly updated like this: {noformat:title=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_search_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 {noformat} 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 {code:sql} ALTER TABLE t MODIFY c INT NOT NULL, FORCE; {code} the function {{page_set_autoinc()}} will not be invoked.	~~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: {code:sql} --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; {code} The value in the page would be wrongly updated like this: {noformat:title=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 {noformat} 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 {code:sql} ALTER TABLE t MODIFY c INT NOT NULL, FORCE; {code} the function {{page_set_autoinc()}} will not be invoked.
Priority	Major [ 3 ]	Critical [ 2 ]

Sergei Golubchik made changes - 2021-12-06 21:35

Workflow

MariaDB v3 [ 123818 ]

MariaDB v4 [ 143027 ]

Marko Mäkelä made changes - 2022-02-21 13:21

Assignee

Eugene Kosov [ kevg ]

Vladislav Lesin [ vlad.lesin ]

Vladislav Lesin made changes - 2022-04-05 12:05

Status

Open [ 1 ]

In Progress [ 3 ]

Vladislav Lesin made changes - 2022-04-19 14:14

Status

In Progress [ 3 ]

In Review [ 10002 ]

Vladislav Lesin made changes - 2022-04-19 14:17

Assignee

Vladislav Lesin [ vlad.lesin ]

Marko Mäkelä [ marko ]

Marko Mäkelä made changes - 2022-04-20 07:19

Assignee	Marko Mäkelä [ marko ]	Vladislav Lesin [ vlad.lesin ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Vladislav Lesin made changes - 2022-04-21 13:16

Fix Version/s		10.4.25 [ 27510 ]
Fix Version/s		10.5.16 [ 27508 ]
Fix Version/s		10.6.8 [ 27506 ]
Fix Version/s		10.7.4 [ 27504 ]
Fix Version/s		10.8.3 [ 27502 ]
Fix Version/s		10.9.1 [ 27114 ]
Fix Version/s	10.4 [ 22408 ]
Fix Version/s	10.5 [ 23123 ]
Fix Version/s	10.6 [ 24028 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

People

Assignee:: Vladislav Lesin

Reporter:: Marko Mäkelä

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2021-07-23 10:26

Updated:: 2022-04-26 11:07

Resolved:: 2022-04-21 13:16

Git Integration

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

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration