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.
{"report":{"fcp":2590.399999856949,"ttfb":321,"pageVisibility":"visible","entityId":101478,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":0.5,"journeyId":"df7c51cd-5d3b-4df8-bd98-794097fae0b2","navigationType":0,"readyForUser":2776.199999809265,"redirectCount":0,"resourceLoadedEnd":2260.699999809265,"resourceLoadedStart":344.09999990463257,"resourceTiming":[{"duration":272.2000000476837,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":344.09999990463257,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":344.09999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":616.2999999523163,"responseStart":0,"secureConnectionStart":0},{"duration":272.7000000476837,"initiatorType":"link","name":"https://jira.mariadb.org/s/7ebd35e77e471bc30ff0eba799ebc151-CDN/lu2cib/820016/12ta74/494e4c556ecbb29f90a3d3b4f09cb99c/_/download/contextbatch/css/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.css?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&slack-enabled=true&whisper-enabled=true","startTime":344.2999999523163,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":344.2999999523163,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":617,"responseStart":0,"secureConnectionStart":0},{"duration":926.0999999046326,"initiatorType":"script","name":"https://jira.mariadb.org/s/0917945aaa57108d00c5076fea35e069-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":344.59999990463257,"connectEnd":344.59999990463257,"connectStart":344.59999990463257,"domainLookupEnd":344.59999990463257,"domainLookupStart":344.59999990463257,"fetchStart":344.59999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":619.5,"responseEnd":1270.6999998092651,"responseStart":636.6999998092651,"secureConnectionStart":344.59999990463257},{"duration":1749.6000001430511,"initiatorType":"script","name":"https://jira.mariadb.org/s/2d8175ec2fa4c816e8023260bd8c1786-CDN/lu2cib/820016/12ta74/494e4c556ecbb29f90a3d3b4f09cb99c/_/download/contextbatch/js/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.js?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&locale=en&slack-enabled=true&whisper-enabled=true","startTime":344.69999980926514,"connectEnd":344.69999980926514,"connectStart":344.69999980926514,"domainLookupEnd":344.69999980926514,"domainLookupStart":344.69999980926514,"fetchStart":344.69999980926514,"redirectEnd":0,"redirectStart":0,"requestStart":619,"responseEnd":2094.2999999523163,"responseStart":631.1999998092651,"secureConnectionStart":344.69999980926514},{"duration":357,"initiatorType":"script","name":"https://jira.mariadb.org/s/a9324d6758d385eb45c462685ad88f1d-CDN/lu2cib/820016/12ta74/c92c0caa9a024ae85b0ebdbed7fb4bd7/_/download/contextbatch/js/atl.global,-_super/batch.js?locale=en","startTime":344.89999985694885,"connectEnd":344.89999985694885,"connectStart":344.89999985694885,"domainLookupEnd":344.89999985694885,"domainLookupStart":344.89999985694885,"fetchStart":344.89999985694885,"redirectEnd":0,"redirectStart":0,"requestStart":661.5,"responseEnd":701.8999998569489,"responseStart":685.5999999046326,"secureConnectionStart":344.89999985694885},{"duration":358.2999999523163,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-en/jira.webresources:calendar-en.js","startTime":345.09999990463257,"connectEnd":345.09999990463257,"connectStart":345.09999990463257,"domainLookupEnd":345.09999990463257,"domainLookupStart":345.09999990463257,"fetchStart":345.09999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":663.3999998569489,"responseEnd":703.3999998569489,"responseStart":695.3999998569489,"secureConnectionStart":345.09999990463257},{"duration":357.2999999523163,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-localisation-moment/jira.webresources:calendar-localisation-moment.js","startTime":345.2999999523163,"connectEnd":345.2999999523163,"connectStart":345.2999999523163,"domainLookupEnd":345.2999999523163,"domainLookupStart":345.2999999523163,"fetchStart":345.2999999523163,"redirectEnd":0,"redirectStart":0,"requestStart":663.1999998092651,"responseEnd":702.5999999046326,"responseStart":687.2999999523163,"secureConnectionStart":345.2999999523163},{"duration":316.2000000476837,"initiatorType":"link","name":"https://jira.mariadb.org/s/b04b06a02d1959df322d9cded3aeecc1-CDN/lu2cib/820016/12ta74/a2ff6aa845ffc9a1d22fe23d9ee791fc/_/download/contextbatch/css/jira.global.look-and-feel,-_super/batch.css","startTime":345.59999990463257,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":345.59999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":661.7999999523163,"responseStart":0,"secureConnectionStart":0},{"duration":358.7000000476837,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":345.69999980926514,"connectEnd":345.69999980926514,"connectStart":345.69999980926514,"domainLookupEnd":345.69999980926514,"domainLookupStart":345.69999980926514,"fetchStart":345.69999980926514,"redirectEnd":0,"redirectStart":0,"requestStart":663.5,"responseEnd":704.3999998569489,"responseStart":698.3999998569489,"secureConnectionStart":345.69999980926514},{"duration":317.10000014305115,"initiatorType":"link","name":"https://jira.mariadb.org/s/3ac36323ba5e4eb0af2aa7ac7211b4bb-CDN/lu2cib/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/css/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.css?jira.create.linked.issue=true","startTime":345.89999985694885,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":345.89999985694885,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":663,"responseStart":0,"secureConnectionStart":0},{"duration":402.5,"initiatorType":"script","name":"https://jira.mariadb.org/s/5d5e8fe91fbc506585e83ea3b62ccc4b-CDN/lu2cib/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/js/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.js?jira.create.linked.issue=true&locale=en","startTime":346.09999990463257,"connectEnd":346.09999990463257,"connectStart":346.09999990463257,"domainLookupEnd":346.09999990463257,"domainLookupStart":346.09999990463257,"fetchStart":346.09999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":714.6999998092651,"responseEnd":748.5999999046326,"responseStart":745.2999999523163,"secureConnectionStart":346.09999990463257},{"duration":1909,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-js/jira.webresources:bigpipe-js.js","startTime":348.59999990463257,"connectEnd":348.59999990463257,"connectStart":348.59999990463257,"domainLookupEnd":348.59999990463257,"domainLookupStart":348.59999990463257,"fetchStart":348.59999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":1081.1999998092651,"responseEnd":2257.5999999046326,"responseStart":2219.399999856949,"secureConnectionStart":348.59999990463257},{"duration":1912.0999999046326,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-init/jira.webresources:bigpipe-init.js","startTime":348.59999990463257,"connectEnd":348.59999990463257,"connectStart":348.59999990463257,"domainLookupEnd":348.59999990463257,"domainLookupStart":348.59999990463257,"fetchStart":348.59999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":1454.5,"responseEnd":2260.699999809265,"responseStart":2245.0999999046326,"secureConnectionStart":348.59999990463257},{"duration":600.7999999523163,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":1795.3999998569489,"connectEnd":1795.3999998569489,"connectStart":1795.3999998569489,"domainLookupEnd":1795.3999998569489,"domainLookupStart":1795.3999998569489,"fetchStart":1795.3999998569489,"redirectEnd":0,"redirectStart":0,"requestStart":2361.5999999046326,"responseEnd":2396.199999809265,"responseStart":2395.5999999046326,"secureConnectionStart":1795.3999998569489}],"fetchStart":0,"domainLookupStart":0,"domainLookupEnd":0,"connectStart":0,"connectEnd":0,"requestStart":128,"responseStart":321,"responseEnd":341,"domLoading":330,"domInteractive":2926,"domContentLoadedEventStart":2926,"domContentLoadedEventEnd":2976,"domComplete":4626,"loadEventStart":4626,"loadEventEnd":4627,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":2875.0999999046326},{"name":"bigPipe.sidebar-id.end","time":2884.699999809265},{"name":"bigPipe.activity-panel-pipe-id.start","time":2884.7999999523163},{"name":"bigPipe.activity-panel-pipe-id.end","time":2889},{"name":"activityTabFullyLoaded","time":2991.2999999523163}],"measures":[],"correlationId":"f227488f366ce5","effectiveType":"4g","downlink":10,"rtt":0,"serverDuration":104,"dbReadsTimeInMs":15,"dbConnsTimeInMs":24,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}
Bug
Critical
MDEV-12836 Avoid table rebuild when removing of auto_increment settings
