Run the following statements, in which the ALTER statement is expected to execute successfully. However, the ALTER statement failed, and it seems that the table `mysql.innodb_table_stats` does not timely update its info.
John Jove
added a comment - By the way, If I create the same table t1 as follows, the execution results become valid.
CREATE TABLE t1 (c1 INT ) ENGINE=InnoDB STATS_PERSISTENT=0;
CREATE TABLE t2 (c1 INT ) ENGINE=InnoDB;
DROP TABLE IF EXISTS t1;
ALTER TABLE t2 RENAME t1; -- Normal
Thank you for the simple test case. I can reproduce this:
10.6 2d5cba22a98e54c0c8975d8d7fe7e04981d73c8a
mysqltest: At line 6: query 'ALTER TABLE t2 RENAME t1' failed: ER_DUP_KEY (1022): Can't write; duplicate key in table 'mysql.innodb_table_stats'
The line number is off by one, because I added --source include/have_innodb.inc to make the input valid for our regression test tool.
I think that the problem is that ALTER TABLE t1 STATS_PERSISTENT 0 fails to delete the statistics.
Marko Mäkelä
added a comment - Thank you for the simple test case. I can reproduce this:
10.6 2d5cba22a98e54c0c8975d8d7fe7e04981d73c8a
mysqltest: At line 6: query 'ALTER TABLE t2 RENAME t1' failed: ER_DUP_KEY (1022): Can't write; duplicate key in table 'mysql.innodb_table_stats'
The line number is off by one, because I added --source include/have_innodb.inc to make the input valid for our regression test tool.
I think that the problem is that ALTER TABLE t1 STATS_PERSISTENT 0 fails to delete the statistics.
I ended up fixing this so that ALTER TABLE t1 STATS_PERSISTENT=0 will delete the statistics.
In DROP TABLE I would not try to drop InnoDB persistent statistics if they had not been enabled for the table. The reason is that there could be locking conflicts with other DDL statements that would make the operation fail, and many applications might not be prepared to deal with a failing DROP TABLE operation.
Marko Mäkelä
added a comment - I ended up fixing this so that ALTER TABLE t1 STATS_PERSISTENT=0 will delete the statistics.
In DROP TABLE I would not try to drop InnoDB persistent statistics if they had not been enabled for the table. The reason is that there could be locking conflicts with other DDL statements that would make the operation fail, and many applications might not be prepared to deal with a failing DROP TABLE operation.
People
Marko Mäkelä
John Jove
Votes:
0Vote for this issue
Watchers:
3Start 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.
{"report":{"fcp":1284.3000000715256,"ttfb":303.40000009536743,"pageVisibility":"visible","entityId":129226,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":0.5,"journeyId":"345576d9-64e0-49ab-bac6-b0b57be08be6","navigationType":0,"readyForUser":1392.6000000238419,"redirectCount":0,"resourceLoadedEnd":1475.3000000715256,"resourceLoadedStart":309.7000000476837,"resourceTiming":[{"duration":338.2999999523163,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":309.7000000476837,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":309.7000000476837,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":648,"responseStart":0,"secureConnectionStart":0},{"duration":338.3000000715256,"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":310,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":310,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":648.3000000715256,"responseStart":0,"secureConnectionStart":0},{"duration":347.39999997615814,"initiatorType":"script","name":"https://jira.mariadb.org/s/0917945aaa57108d00c5076fea35e069-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":310.2000000476837,"connectEnd":310.2000000476837,"connectStart":310.2000000476837,"domainLookupEnd":310.2000000476837,"domainLookupStart":310.2000000476837,"fetchStart":310.2000000476837,"redirectEnd":0,"redirectStart":0,"requestStart":310.2000000476837,"responseEnd":657.6000000238419,"responseStart":657.6000000238419,"secureConnectionStart":310.2000000476837},{"duration":422.1999999284744,"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":310.40000009536743,"connectEnd":310.40000009536743,"connectStart":310.40000009536743,"domainLookupEnd":310.40000009536743,"domainLookupStart":310.40000009536743,"fetchStart":310.40000009536743,"redirectEnd":0,"redirectStart":0,"requestStart":310.40000009536743,"responseEnd":732.6000000238419,"responseStart":732.6000000238419,"secureConnectionStart":310.40000009536743},{"duration":426.2000000476837,"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":310.5,"connectEnd":310.5,"connectStart":310.5,"domainLookupEnd":310.5,"domainLookupStart":310.5,"fetchStart":310.5,"redirectEnd":0,"redirectStart":0,"requestStart":310.5,"responseEnd":736.7000000476837,"responseStart":736.7000000476837,"secureConnectionStart":310.5},{"duration":427,"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":310.8000000715256,"connectEnd":310.8000000715256,"connectStart":310.8000000715256,"domainLookupEnd":310.8000000715256,"domainLookupStart":310.8000000715256,"fetchStart":310.8000000715256,"redirectEnd":0,"redirectStart":0,"requestStart":310.8000000715256,"responseEnd":737.8000000715256,"responseStart":737.8000000715256,"secureConnectionStart":310.8000000715256},{"duration":427.40000009536743,"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":311,"connectEnd":311,"connectStart":311,"domainLookupEnd":311,"domainLookupStart":311,"fetchStart":311,"redirectEnd":0,"redirectStart":0,"requestStart":311,"responseEnd":738.4000000953674,"responseStart":738.4000000953674,"secureConnectionStart":311},{"duration":498.8000000715256,"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":311.10000002384186,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":311.10000002384186,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":809.9000000953674,"responseStart":0,"secureConnectionStart":0},{"duration":427.60000002384186,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":311.3000000715256,"connectEnd":311.3000000715256,"connectStart":311.3000000715256,"domainLookupEnd":311.3000000715256,"domainLookupStart":311.3000000715256,"fetchStart":311.3000000715256,"redirectEnd":0,"redirectStart":0,"requestStart":311.3000000715256,"responseEnd":738.9000000953674,"responseStart":738.9000000953674,"secureConnectionStart":311.3000000715256},{"duration":498.60000002384186,"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":311.5,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":311.5,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":810.1000000238419,"responseStart":0,"secureConnectionStart":0},{"duration":428.3000000715256,"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":311.60000002384186,"connectEnd":311.60000002384186,"connectStart":311.60000002384186,"domainLookupEnd":311.60000002384186,"domainLookupStart":311.60000002384186,"fetchStart":311.60000002384186,"redirectEnd":0,"redirectStart":0,"requestStart":311.60000002384186,"responseEnd":739.9000000953674,"responseStart":739.9000000953674,"secureConnectionStart":311.60000002384186},{"duration":694,"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":312.40000009536743,"connectEnd":312.40000009536743,"connectStart":312.40000009536743,"domainLookupEnd":312.40000009536743,"domainLookupStart":312.40000009536743,"fetchStart":312.40000009536743,"redirectEnd":0,"redirectStart":0,"requestStart":312.40000009536743,"responseEnd":1006.4000000953674,"responseStart":1006.4000000953674,"secureConnectionStart":312.40000009536743},{"duration":1157.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":312.40000009536743,"connectEnd":312.40000009536743,"connectStart":312.40000009536743,"domainLookupEnd":312.40000009536743,"domainLookupStart":312.40000009536743,"fetchStart":312.40000009536743,"redirectEnd":0,"redirectStart":0,"requestStart":312.40000009536743,"responseEnd":1469.5,"responseStart":1469.5,"secureConnectionStart":312.40000009536743},{"duration":188.09999990463257,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":823.4000000953674,"connectEnd":823.4000000953674,"connectStart":823.4000000953674,"domainLookupEnd":823.4000000953674,"domainLookupStart":823.4000000953674,"fetchStart":823.4000000953674,"redirectEnd":0,"redirectStart":0,"requestStart":823.4000000953674,"responseEnd":1011.5,"responseStart":1011.5,"secureConnectionStart":823.4000000953674},{"duration":315.8000000715256,"initiatorType":"link","name":"https://jira.mariadb.org/s/d5715adaadd168a9002b108b2b039b50-CDN/lu2cib/820016/12ta74/be4b45e9cec53099498fa61c8b7acba4/_/download/contextbatch/css/jira.project.sidebar,-_super,-project.issue.navigator,-jira.general,-jira.browse.project,-jira.view.issue,-jira.global,-atl.general,-com.atlassian.jira.projects.sidebar.init/batch.css?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&slack-enabled=true&whisper-enabled=true","startTime":1159.5,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":1159.5,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1475.3000000715256,"responseStart":0,"secureConnectionStart":0}],"fetchStart":0,"domainLookupStart":0,"domainLookupEnd":0,"connectStart":0,"connectEnd":0,"requestStart":153,"responseStart":303,"responseEnd":307,"domLoading":307,"domInteractive":1505,"domContentLoadedEventStart":1505,"domContentLoadedEventEnd":1557,"domComplete":2247,"loadEventStart":2247,"loadEventEnd":2248,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":1480.4000000953674},{"name":"bigPipe.sidebar-id.end","time":1481.2000000476837},{"name":"bigPipe.activity-panel-pipe-id.start","time":1481.4000000953674},{"name":"bigPipe.activity-panel-pipe-id.end","time":1483},{"name":"activityTabFullyLoaded","time":1573.3000000715256}],"measures":[],"correlationId":"665d6379c95e22","effectiveType":"4g","downlink":9.1,"rtt":0,"serverDuration":93,"dbReadsTimeInMs":13,"dbConnsTimeInMs":22,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}
By the way, If I create the same table t1 as follows, the execution results become valid.