Starting with 10.2 (not sure which minor version) the mysql client is wrapping partition names and columns in partition functions inside backticks.
This is not standard behaviour for the client and breaks partitioning scripts.
10.1 output:
MariaDB [test]> show create table actor\G
|
*************************** 1. row ***************************
|
Table: actor
|
Create Table: CREATE TABLE `actor` (
|
`actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
|
`first_name` varchar(45) NOT NULL,
|
`last_name` varchar(45) NOT NULL,
|
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
PRIMARY KEY (`actor_id`,`last_update`),
|
KEY `idx_actor_last_name` (`last_name`),
|
KEY `last_update` (`last_update`)
|
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8
|
/*!50100 PARTITION BY RANGE (unix_timestamp(last_update))
|
(PARTITION p0 VALUES LESS THAN (1522900800) ENGINE = InnoDB,
|
PARTITION p1 VALUES LESS THAN (1522987200) ENGINE = InnoDB,
|
PARTITION p2 VALUES LESS THAN (1523073600) ENGINE = InnoDB,
|
PARTITION p3 VALUES LESS THAN (1523160000) ENGINE = InnoDB,
|
PARTITION p4 VALUES LESS THAN (1523246400) ENGINE = InnoDB,
|
PARTITION p5 VALUES LESS THAN (1523332800) ENGINE = InnoDB,
|
PARTITION p6 VALUES LESS THAN (1523419200) ENGINE = InnoDB,
|
PARTITION p7 VALUES LESS THAN (1523505600) ENGINE = InnoDB,
|
PARTITION p8 VALUES LESS THAN (1523592000) ENGINE = InnoDB,
|
PARTITION p9 VALUES LESS THAN (1523678400) ENGINE = InnoDB,
|
PARTITION p10 VALUES LESS THAN (1523764800) ENGINE = InnoDB,
|
PARTITION p11 VALUES LESS THAN (1523851200) ENGINE = InnoDB,
|
PARTITION p12 VALUES LESS THAN (1523937600) ENGINE = InnoDB,
|
PARTITION p13 VALUES LESS THAN (1524024000) ENGINE = InnoDB,
|
PARTITION p14 VALUES LESS THAN (1524110400) ENGINE = InnoDB,
|
PARTITION p15 VALUES LESS THAN (1524196800) ENGINE = InnoDB,
|
PARTITION p16 VALUES LESS THAN (1524283200) ENGINE = InnoDB,
|
PARTITION p17 VALUES LESS THAN (1524369600) ENGINE = InnoDB) */
|
1 row in set (0.00 sec)
|
10.2 output:
MariaDB [test]> show create table actor\G
|
*************************** 1. row ***************************
|
Table: actor
|
Create Table: CREATE TABLE `actor` (
|
`actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
|
`first_name` varchar(45) NOT NULL,
|
`last_name` varchar(45) NOT NULL,
|
`last_update` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
|
PRIMARY KEY (`actor_id`,`last_update`),
|
KEY `idx_actor_last_name` (`last_name`),
|
KEY `last_update` (`last_update`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8
|
PARTITION BY RANGE (unix_timestamp(`last_update`))
|
(PARTITION `p0` VALUES LESS THAN (1522900800) ENGINE = InnoDB,
|
PARTITION `p1` VALUES LESS THAN (1522987200) ENGINE = InnoDB,
|
PARTITION `p2` VALUES LESS THAN (1523073600) ENGINE = InnoDB,
|
PARTITION `p3` VALUES LESS THAN (1523160000) ENGINE = InnoDB,
|
PARTITION `p4` VALUES LESS THAN (1523246400) ENGINE = InnoDB,
|
PARTITION `p5` VALUES LESS THAN (1523332800) ENGINE = InnoDB,
|
PARTITION `p6` VALUES LESS THAN (1523419200) ENGINE = InnoDB,
|
PARTITION `p7` VALUES LESS THAN (1523505600) ENGINE = InnoDB,
|
PARTITION `p8` VALUES LESS THAN (1523592000) ENGINE = InnoDB,
|
PARTITION `p9` VALUES LESS THAN (1523678400) ENGINE = InnoDB,
|
PARTITION `p10` VALUES LESS THAN (1523764800) ENGINE = InnoDB,
|
PARTITION `p11` VALUES LESS THAN (1523851200) ENGINE = InnoDB,
|
PARTITION `p12` VALUES LESS THAN (1523937600) ENGINE = InnoDB,
|
PARTITION `p13` VALUES LESS THAN (1524024000) ENGINE = InnoDB,
|
PARTITION `p14` VALUES LESS THAN (1524110400) ENGINE = InnoDB,
|
PARTITION `p15` VALUES LESS THAN (1524196800) ENGINE = InnoDB,
|
PARTITION `p16` VALUES LESS THAN (1524283200) ENGINE = InnoDB,
|
PARTITION `p17` VALUES LESS THAN (1524369600) ENGINE = InnoDB)*/
|
- is caused by
-
MDEV-13089
identifier quoting in partitioning
-
-
Closed
{"report":{"fcp":915.5,"ttfb":221.10000002384186,"pageVisibility":"visible","entityId":67550,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":0.5,"journeyId":"465efac2-856d-446a-b2b4-5785f394ef84","navigationType":0,"readyForUser":1001.3000000715256,"redirectCount":0,"resourceLoadedEnd":461,"resourceLoadedStart":228,"resourceTiming":[{"duration":72.39999997615814,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":228,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":228,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":300.39999997615814,"responseStart":0,"secureConnectionStart":0},{"duration":72.39999997615814,"initiatorType":"link","name":"https://jira.mariadb.org/s/7ebd35e77e471bc30ff0eba799ebc151-CDN/lu2cib/820016/12ta74/2bf333562ca6724060a9d5f1535471f6/_/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","startTime":228.30000007152557,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":228.30000007152557,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":300.7000000476837,"responseStart":0,"secureConnectionStart":0},{"duration":226.10000002384186,"initiatorType":"script","name":"https://jira.mariadb.org/s/0917945aaa57108d00c5076fea35e069-CDN/lu2cib/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":228.5,"connectEnd":228.5,"connectStart":228.5,"domainLookupEnd":228.5,"domainLookupStart":228.5,"fetchStart":228.5,"redirectEnd":0,"redirectStart":0,"requestStart":389,"responseEnd":454.60000002384186,"responseStart":430.89999997615814,"secureConnectionStart":228.5},{"duration":232.39999997615814,"initiatorType":"script","name":"https://jira.mariadb.org/s/2d8175ec2fa4c816e8023260bd8c1786-CDN/lu2cib/820016/12ta74/2bf333562ca6724060a9d5f1535471f6/_/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","startTime":228.60000002384186,"connectEnd":228.60000002384186,"connectStart":228.60000002384186,"domainLookupEnd":228.60000002384186,"domainLookupStart":228.60000002384186,"fetchStart":228.60000002384186,"redirectEnd":0,"redirectStart":0,"requestStart":384.39999997615814,"responseEnd":461,"responseStart":397.60000002384186,"secureConnectionStart":228.60000002384186},{"duration":195.39999997615814,"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":228.80000007152557,"connectEnd":228.80000007152557,"connectStart":228.80000007152557,"domainLookupEnd":228.80000007152557,"domainLookupStart":228.80000007152557,"fetchStart":228.80000007152557,"redirectEnd":0,"redirectStart":0,"requestStart":389.10000002384186,"responseEnd":424.2000000476837,"responseStart":423.60000002384186,"secureConnectionStart":228.80000007152557},{"duration":199.10000002384186,"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":229,"connectEnd":229,"connectStart":229,"domainLookupEnd":229,"domainLookupStart":229,"fetchStart":229,"redirectEnd":0,"redirectStart":0,"requestStart":389.89999997615814,"responseEnd":428.10000002384186,"responseStart":427.5,"secureConnectionStart":229},{"duration":194.39999997615814,"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":229.10000002384186,"connectEnd":229.10000002384186,"connectStart":229.10000002384186,"domainLookupEnd":229.10000002384186,"domainLookupStart":229.10000002384186,"fetchStart":229.10000002384186,"redirectEnd":0,"redirectStart":0,"requestStart":389.60000002384186,"responseEnd":423.5,"responseStart":422.89999997615814,"secureConnectionStart":229.10000002384186},{"duration":86.19999992847443,"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":229.30000007152557,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":229.30000007152557,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":315.5,"responseStart":0,"secureConnectionStart":0},{"duration":219,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":229.39999997615814,"connectEnd":229.39999997615814,"connectStart":229.39999997615814,"domainLookupEnd":229.39999997615814,"domainLookupStart":229.39999997615814,"fetchStart":229.39999997615814,"redirectEnd":0,"redirectStart":0,"requestStart":389.8000000715256,"responseEnd":448.39999997615814,"responseStart":447.7000000476837,"secureConnectionStart":229.39999997615814},{"duration":106.70000004768372,"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":229.60000002384186,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":229.60000002384186,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":336.3000000715256,"responseStart":0,"secureConnectionStart":0},{"duration":183.89999997615814,"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":229.70000004768372,"connectEnd":398.39999997615814,"connectStart":398.39999997615814,"domainLookupEnd":398.39999997615814,"domainLookupStart":398.39999997615814,"fetchStart":229.70000004768372,"redirectEnd":0,"redirectStart":0,"requestStart":398.89999997615814,"responseEnd":413.60000002384186,"responseStart":410,"secureConnectionStart":398.39999997615814},{"duration":208.5,"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":241.80000007152557,"connectEnd":241.80000007152557,"connectStart":241.80000007152557,"domainLookupEnd":241.80000007152557,"domainLookupStart":241.80000007152557,"fetchStart":241.80000007152557,"redirectEnd":0,"redirectStart":0,"requestStart":437.60000002384186,"responseEnd":450.3000000715256,"responseStart":449.2000000476837,"secureConnectionStart":241.80000007152557},{"duration":216.19999992847443,"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":242.20000004768372,"connectEnd":242.20000004768372,"connectStart":242.20000004768372,"domainLookupEnd":242.20000004768372,"domainLookupStart":242.20000004768372,"fetchStart":242.20000004768372,"redirectEnd":0,"redirectStart":0,"requestStart":446.2000000476837,"responseEnd":458.39999997615814,"responseStart":457.60000002384186,"secureConnectionStart":242.20000004768372},{"duration":169.79999995231628,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":662.6000000238419,"connectEnd":662.6000000238419,"connectStart":662.6000000238419,"domainLookupEnd":662.6000000238419,"domainLookupStart":662.6000000238419,"fetchStart":662.6000000238419,"redirectEnd":0,"redirectStart":0,"requestStart":795.8000000715256,"responseEnd":832.3999999761581,"responseStart":831.7000000476837,"secureConnectionStart":662.6000000238419},{"duration":133.79999995231628,"initiatorType":"script","name":"https://www.google-analytics.com/analytics.js","startTime":909.3000000715256,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":909.3000000715256,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1043.1000000238419,"responseStart":0,"secureConnectionStart":0},{"duration":169.5,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":918.3000000715256,"connectEnd":918.3000000715256,"connectStart":918.3000000715256,"domainLookupEnd":918.3000000715256,"domainLookupStart":918.3000000715256,"fetchStart":918.3000000715256,"redirectEnd":0,"redirectStart":0,"requestStart":1054,"responseEnd":1087.8000000715256,"responseStart":1086.3999999761581,"secureConnectionStart":918.3000000715256}],"fetchStart":0,"domainLookupStart":13,"domainLookupEnd":19,"connectStart":19,"connectEnd":43,"secureConnectionStart":27,"requestStart":43,"responseStart":221,"responseEnd":242,"domLoading":226,"domInteractive":1072,"domContentLoadedEventStart":1072,"domContentLoadedEventEnd":1121,"domComplete":1267,"loadEventStart":1267,"loadEventEnd":1268,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":1045.8999999761581},{"name":"bigPipe.sidebar-id.end","time":1046.6000000238419},{"name":"bigPipe.activity-panel-pipe-id.start","time":1046.8000000715256},{"name":"bigPipe.activity-panel-pipe-id.end","time":1048.5},{"name":"activityTabFullyLoaded","time":1147.5}],"measures":[],"correlationId":"548dd68627d87a","effectiveType":"4g","downlink":10,"rtt":0,"serverDuration":102,"dbReadsTimeInMs":13,"dbConnsTimeInMs":21,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}
This was an intentional change, a bug fix for
MDEV-13089.The standard behavior is to quote (or not) all identifiers, depending on sql_quote_show_create variable, and to use backticks or double quotes based on the sql_mode. Before
MDEV-13089was fixed, partitioning wasn't using this rule which made the output of SHOW CREATE TABLE unusable if partition names were reserved words or in the ANSI_QUOTES mode. And, depending on partition names and sql_mode you could've ended up with the corrupted table that could not be opened at all (because partition definition is saved as an excerpt from SHOW CREATE TABLE). The fix was to use quoting rules consistently for all identifiers in SHOW CREATE TABLE.