Type:
Bug
Priority:
Blocker
Resolution:
Fixed
Affects Version/s:
10.6
The syntax for creating an ignorable index, according to MDEV_7317, is KEY .. IGNORE . However, the resulting SHOW CREATE TABLE shows "IGNORED" instead, and an attempt to use it causes a syntax error:
10.6 03ff588d1
MariaDB [test]> CREATE TABLE t (a INT , KEY (a) IGNORE );
Query OK, 0 rows affected (0.046 sec)
MariaDB [test]> SHOW CREATE TABLE t;
+ -------+---------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+ -------+---------------------------------------------------------------------------------------------------------------+
| t | CREATE TABLE `t` (
`a` int (11) DEFAULT NULL ,
KEY `a` (`a`) IGNORED
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+ -------+---------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)
MariaDB [test]> DROP TABLE t;
Query OK, 0 rows affected (0.031 sec)
MariaDB [test]> CREATE TABLE `t` (
-> `a` int (11) DEFAULT NULL ,
-> KEY `a` (`a`) IGNORED
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') ENGINE=InnoDB DEFAULT CHARSET=latin1' at line 4
is caused by
MDEV-7317
Make an index ignorable to the optimizer
Closed
{"report":{"fcp":1251.8999998569489,"ttfb":348.59999990463257,"pageVisibility":"visible","entityId":97650,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":0.5,"journeyId":"5d6dcf9a-3421-4a10-8fbc-92c7fad0d10c","navigationType":0,"readyForUser":1364.8999998569489,"redirectCount":0,"resourceLoadedEnd":1512,"resourceLoadedStart":356.7000000476837,"resourceTiming":[{"duration":394.69999980926514,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":356.7000000476837,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":356.7000000476837,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":751.3999998569489,"responseStart":0,"secureConnectionStart":0},{"duration":394.80000019073486,"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":356.89999985694885,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":356.89999985694885,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":751.7000000476837,"responseStart":0,"secureConnectionStart":0},{"duration":404,"initiatorType":"script","name":"https://jira.mariadb.org/s/0917945aaa57108d00c5076fea35e069-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":357.09999990463257,"connectEnd":357.09999990463257,"connectStart":357.09999990463257,"domainLookupEnd":357.09999990463257,"domainLookupStart":357.09999990463257,"fetchStart":357.09999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":357.09999990463257,"responseEnd":761.0999999046326,"responseStart":761.0999999046326,"secureConnectionStart":357.09999990463257},{"duration":507.10000014305115,"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":357.39999985694885,"connectEnd":357.39999985694885,"connectStart":357.39999985694885,"domainLookupEnd":357.39999985694885,"domainLookupStart":357.39999985694885,"fetchStart":357.39999985694885,"redirectEnd":0,"redirectStart":0,"requestStart":357.39999985694885,"responseEnd":864.5,"responseStart":864.5,"secureConnectionStart":357.39999985694885},{"duration":511,"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":357.5,"connectEnd":357.5,"connectStart":357.5,"domainLookupEnd":357.5,"domainLookupStart":357.5,"fetchStart":357.5,"redirectEnd":0,"redirectStart":0,"requestStart":357.5,"responseEnd":868.5,"responseStart":868.5,"secureConnectionStart":357.5},{"duration":511.19999980926514,"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":357.7000000476837,"connectEnd":357.7000000476837,"connectStart":357.7000000476837,"domainLookupEnd":357.7000000476837,"domainLookupStart":357.7000000476837,"fetchStart":357.7000000476837,"redirectEnd":0,"redirectStart":0,"requestStart":357.7000000476837,"responseEnd":868.8999998569489,"responseStart":868.8999998569489,"secureConnectionStart":357.7000000476837},{"duration":511.2000000476837,"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":358,"connectEnd":358,"connectStart":358,"domainLookupEnd":358,"domainLookupStart":358,"fetchStart":358,"redirectEnd":0,"redirectStart":0,"requestStart":358,"responseEnd":869.2000000476837,"responseStart":869.2000000476837,"secureConnectionStart":358},{"duration":550.0999999046326,"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":358.2000000476837,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":358.2000000476837,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":908.2999999523163,"responseStart":0,"secureConnectionStart":0},{"duration":511.30000019073486,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":358.39999985694885,"connectEnd":358.39999985694885,"connectStart":358.39999985694885,"domainLookupEnd":358.39999985694885,"domainLookupStart":358.39999985694885,"fetchStart":358.39999985694885,"redirectEnd":0,"redirectStart":0,"requestStart":358.39999985694885,"responseEnd":869.7000000476837,"responseStart":869.7000000476837,"secureConnectionStart":358.39999985694885},{"duration":549.7999999523163,"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":358.59999990463257,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":358.59999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":908.3999998569489,"responseStart":0,"secureConnectionStart":0},{"duration":511.69999980926514,"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":358.7000000476837,"connectEnd":358.7000000476837,"connectStart":358.7000000476837,"domainLookupEnd":358.7000000476837,"domainLookupStart":358.7000000476837,"fetchStart":358.7000000476837,"redirectEnd":0,"redirectStart":0,"requestStart":358.7000000476837,"responseEnd":870.3999998569489,"responseStart":870.3999998569489,"secureConnectionStart":358.7000000476837},{"duration":802,"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":362.09999990463257,"connectEnd":362.09999990463257,"connectStart":362.09999990463257,"domainLookupEnd":362.09999990463257,"domainLookupStart":362.09999990463257,"fetchStart":362.09999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":362.09999990463257,"responseEnd":1164.0999999046326,"responseStart":1164.0999999046326,"secureConnectionStart":362.09999990463257},{"duration":1142.2999999523163,"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":362.09999990463257,"connectEnd":362.09999990463257,"connectStart":362.09999990463257,"domainLookupEnd":362.09999990463257,"domainLookupStart":362.09999990463257,"fetchStart":362.09999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":362.09999990463257,"responseEnd":1504.3999998569489,"responseStart":1504.3999998569489,"secureConnectionStart":362.09999990463257},{"duration":247.39999985694885,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":921,"connectEnd":921,"connectStart":921,"domainLookupEnd":921,"domainLookupStart":921,"fetchStart":921,"redirectEnd":0,"redirectStart":0,"requestStart":921,"responseEnd":1168.3999998569489,"responseStart":1168.3999998569489,"secureConnectionStart":921},{"duration":312.10000014305115,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2cib/820016/12ta74/e65b778d185daf5aee24936755b43da6/_/download/contextbatch/js/browser-metrics-plugin.contrib,-_super,-project.issue.navigator,-jira.view.issue,-atl.general/batch.js?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&slack-enabled=true&whisper-enabled=true","startTime":1199.8999998569489,"connectEnd":1199.8999998569489,"connectStart":1199.8999998569489,"domainLookupEnd":1199.8999998569489,"domainLookupStart":1199.8999998569489,"fetchStart":1199.8999998569489,"redirectEnd":0,"redirectStart":0,"requestStart":1199.8999998569489,"responseEnd":1512,"responseStart":1511.8999998569489,"secureConnectionStart":1199.8999998569489},{"duration":291.90000009536743,"initiatorType":"script","name":"https://www.google-analytics.com/analytics.js","startTime":1245.0999999046326,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":1245.0999999046326,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1537,"responseStart":0,"secureConnectionStart":0}],"fetchStart":0,"domainLookupStart":0,"domainLookupEnd":0,"connectStart":0,"connectEnd":0,"requestStart":165,"responseStart":349,"responseEnd":356,"domLoading":352,"domInteractive":1566,"domContentLoadedEventStart":1566,"domContentLoadedEventEnd":1611,"domComplete":1942,"loadEventStart":1942,"loadEventEnd":1943,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":1538.3999998569489},{"name":"bigPipe.sidebar-id.end","time":1539.2000000476837},{"name":"bigPipe.activity-panel-pipe-id.start","time":1539.3999998569489},{"name":"bigPipe.activity-panel-pipe-id.end","time":1540.7000000476837},{"name":"activityTabFullyLoaded","time":1625.5}],"measures":[],"correlationId":"ffab5ab8ac086e","effectiveType":"4g","downlink":10,"rtt":0,"serverDuration":98,"dbReadsTimeInMs":14,"dbConnsTimeInMs":24,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}
Also, since the new ALTER INDEX sub-command took a form ALTER INDEX <name> <attribute>, it's better to make it consistent with CREATE TABLE and SHOW CREATE TABLE and use IGNORED in all of them, it's the more natural variant: