[MDEV-11253] Partition name randomly fails. Created: 2016-11-08  Updated: 2016-11-14  Resolved: 2016-11-14

Status: Closed
Project: MariaDB Server
Component/s: Documentation
Affects Version/s: 10.1.18, 5.5, 10.0, 10.1, 10.2
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Michael Van Der Beek Assignee: Ian Gilfillan
Resolution: Fixed Votes: 0
Labels: None
Environment:

Centos 6, centos 7. Amazon centos os.


 Description   

New partitioning problem with MariaDB Server version: 5.5.5-10.0.24-MariaDB MariaDB Server
This was repeatable on MariaDB-server-10.1.18-1.el7.centos.x86_64

This is the table schema. The point was to partition based on nasidentifier which is a UUID number
followed by storing the session records in subpartitions.

CREATE TABLE `a_radacct2` (
 
  `radacctid` bigint(20) unsigned NOT NULL,
 
  `acctstarttime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 
  `nasidentifier` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
 
  `acctsessionid` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
 
  `acctuniqueid` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
 
  `username` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
 
  `groupname` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
 
  `realm` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
 
  `nasipaddress` varchar(15) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
 
  `nasportid` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
 
  `nasporttype` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
 
  `acctstoptime` datetime DEFAULT NULL,
 
  `acctsessiontime` int(10) unsigned DEFAULT NULL,
 
  `acctinputoctets` bigint(20) unsigned DEFAULT NULL,
 
  `acctoutputoctets` bigint(20) unsigned DEFAULT NULL,
 
  `calledstationid` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
 
  `callingstationid` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
 
  `acctterminatecause` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
 
  `servicetype` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
 
  `framedprotocol` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
 
  `framedipaddress` varchar(15) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
 
  `acctstartdelay` int(11) DEFAULT NULL,
 
  `acctstopdelay` int(11) DEFAULT NULL,
 
  `acctupdatetime` datetime DEFAULT NULL,
 
  `user_id` bigint(20) unsigned DEFAULT NULL,
 
  `user_token` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
 
  `user_agent` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
 
  `device` varchar(30) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
 
  `browser` varchar(30) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
 
  `os` varchar(30) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
 
  `acctstarttime_local` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 
  PRIMARY KEY (`radacctid`,`acctstarttime`,`nasidentifier`),
 
  KEY `user_id` (`user_id`),
 
  KEY `user_token` (`user_token`),
 
  KEY `nasidentifier` (`nasidentifier`),
 
  KEY `acctstarttime` (`acctstarttime`),
 
  KEY `device` (`device`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
 
/*!50500 PARTITION BY LIST  COLUMNS(nasidentifier)
 
SUBPARTITION BY HASH (mod(YEAR(acctstarttime),2)*12 +MONTH(acctstarttime)-1)
 
(PARTITION a2275d3c_a177_11e6_b30a_06af084b4d3d VALUES IN ('a2275d3c-a177-11e6-b30a-06af084b4d3d')
 
 (SUBPARTITION a2275d3c_a177_11e6_b30a_06af084b4d3d_0 ENGINE = InnoDB,
 
  SUBPARTITION a2275d3c_a177_11e6_b30a_06af084b4d3d_1 ENGINE = InnoDB,
 
  SUBPARTITION a2275d3c_a177_11e6_b30a_06af084b4d3d_2 ENGINE = InnoDB,
 
  SUBPARTITION a2275d3c_a177_11e6_b30a_06af084b4d3d_3 ENGINE = InnoDB,
 
  SUBPARTITION a2275d3c_a177_11e6_b30a_06af084b4d3d_4 ENGINE = InnoDB,
 
  SUBPARTITION a2275d3c_a177_11e6_b30a_06af084b4d3d_5 ENGINE = InnoDB,
 
  SUBPARTITION a2275d3c_a177_11e6_b30a_06af084b4d3d_6 ENGINE = InnoDB,
 
  SUBPARTITION a2275d3c_a177_11e6_b30a_06af084b4d3d_7 ENGINE = InnoDB,
 
  SUBPARTITION a2275d3c_a177_11e6_b30a_06af084b4d3d_8 ENGINE = InnoDB,
 
  SUBPARTITION a2275d3c_a177_11e6_b30a_06af084b4d3d_9 ENGINE = InnoDB,
 
  SUBPARTITION a2275d3c_a177_11e6_b30a_06af084b4d3d_10 ENGINE = InnoDB,
 
  SUBPARTITION a2275d3c_a177_11e6_b30a_06af084b4d3d_11 ENGINE = InnoDB,
 
  SUBPARTITION a2275d3c_a177_11e6_b30a_06af084b4d3d_12 ENGINE = InnoDB,
 
  SUBPARTITION a2275d3c_a177_11e6_b30a_06af084b4d3d_13 ENGINE = InnoDB,
 
  SUBPARTITION a2275d3c_a177_11e6_b30a_06af084b4d3d_14 ENGINE = InnoDB,
 
  SUBPARTITION a2275d3c_a177_11e6_b30a_06af084b4d3d_15 ENGINE = InnoDB,
 
  SUBPARTITION a2275d3c_a177_11e6_b30a_06af084b4d3d_16 ENGINE = InnoDB,
 
  SUBPARTITION a2275d3c_a177_11e6_b30a_06af084b4d3d_17 ENGINE = InnoDB,
 
  SUBPARTITION a2275d3c_a177_11e6_b30a_06af084b4d3d_18 ENGINE = InnoDB,
 
  SUBPARTITION a2275d3c_a177_11e6_b30a_06af084b4d3d_19 ENGINE = InnoDB,
 
  SUBPARTITION a2275d3c_a177_11e6_b30a_06af084b4d3d_20 ENGINE = InnoDB,
 
  SUBPARTITION a2275d3c_a177_11e6_b30a_06af084b4d3d_21 ENGINE = InnoDB,
 
  SUBPARTITION a2275d3c_a177_11e6_b30a_06af084b4d3d_22 ENGINE = InnoDB,
 
  SUBPARTITION a2275d3c_a177_11e6_b30a_06af084b4d3d_23 ENGINE = InnoDB),
 
 PARTITION a2275d3c_a177_11e6_b30a_06af084b4d34 VALUES IN ('a2275d3c-a177-11e6-b30a-06af084b4d34')
 
 (SUBPARTITION a2275d3c_a177_11e6_b30a_06af084b4d34_0 ENGINE = InnoDB,
 
  SUBPARTITION a2275d3c_a177_11e6_b30a_06af084b4d34_1 ENGINE = InnoDB,
 
  SUBPARTITION a2275d3c_a177_11e6_b30a_06af084b4d34_2 ENGINE = InnoDB,
 
  SUBPARTITION a2275d3c_a177_11e6_b30a_06af084b4d34_3 ENGINE = InnoDB,
 
  SUBPARTITION a2275d3c_a177_11e6_b30a_06af084b4d34_4 ENGINE = InnoDB,
 
  SUBPARTITION a2275d3c_a177_11e6_b30a_06af084b4d34_5 ENGINE = InnoDB,
 
  SUBPARTITION a2275d3c_a177_11e6_b30a_06af084b4d34_6 ENGINE = InnoDB,
 
  SUBPARTITION a2275d3c_a177_11e6_b30a_06af084b4d34_7 ENGINE = InnoDB,
 
  SUBPARTITION a2275d3c_a177_11e6_b30a_06af084b4d34_8 ENGINE = InnoDB,
 
  SUBPARTITION a2275d3c_a177_11e6_b30a_06af084b4d34_9 ENGINE = InnoDB,
 
  SUBPARTITION a2275d3c_a177_11e6_b30a_06af084b4d34_10 ENGINE = InnoDB,
 
  SUBPARTITION a2275d3c_a177_11e6_b30a_06af084b4d34_11 ENGINE = InnoDB,
 
  SUBPARTITION a2275d3c_a177_11e6_b30a_06af084b4d34_12 ENGINE = InnoDB,
 
  SUBPARTITION a2275d3c_a177_11e6_b30a_06af084b4d34_13 ENGINE = InnoDB,
 
  SUBPARTITION a2275d3c_a177_11e6_b30a_06af084b4d34_14 ENGINE = InnoDB,
 
  SUBPARTITION a2275d3c_a177_11e6_b30a_06af084b4d34_15 ENGINE = InnoDB,
 
  SUBPARTITION a2275d3c_a177_11e6_b30a_06af084b4d34_16 ENGINE = InnoDB,
 
  SUBPARTITION a2275d3c_a177_11e6_b30a_06af084b4d34_17 ENGINE = InnoDB,
 
  SUBPARTITION a2275d3c_a177_11e6_b30a_06af084b4d34_18 ENGINE = InnoDB,
 
  SUBPARTITION a2275d3c_a177_11e6_b30a_06af084b4d34_19 ENGINE = InnoDB,
 
  SUBPARTITION a2275d3c_a177_11e6_b30a_06af084b4d34_20 ENGINE = InnoDB,
 
  SUBPARTITION a2275d3c_a177_11e6_b30a_06af084b4d34_21 ENGINE = InnoDB,
 
  SUBPARTITION a2275d3c_a177_11e6_b30a_06af084b4d34_22 ENGINE = InnoDB,
 
  SUBPARTITION a2275d3c_a177_11e6_b30a_06af084b4d34_23 ENGINE = InnoDB),
 
 PARTITION 8ed0736e_904c_11e6_a493_02f82aee6xxx VALUES IN ('8ed0736e_904c_11e6_a493_02f82aee6xxx')
 
 (SUBPARTITION 8ed0736e_904c_11e6_a493_02f82aee6xxx_0 ENGINE = InnoDB,
 
  SUBPARTITION 8ed0736e_904c_11e6_a493_02f82aee6xxx_1 ENGINE = InnoDB,
 
  SUBPARTITION 8ed0736e_904c_11e6_a493_02f82aee6xxx_2 ENGINE = InnoDB,
 
  SUBPARTITION 8ed0736e_904c_11e6_a493_02f82aee6xxx_3 ENGINE = InnoDB,
 
  SUBPARTITION 8ed0736e_904c_11e6_a493_02f82aee6xxx_4 ENGINE = InnoDB,
 
  SUBPARTITION 8ed0736e_904c_11e6_a493_02f82aee6xxx_5 ENGINE = InnoDB,
 
  SUBPARTITION 8ed0736e_904c_11e6_a493_02f82aee6xxx_6 ENGINE = InnoDB,
 
  SUBPARTITION 8ed0736e_904c_11e6_a493_02f82aee6xxx_7 ENGINE = InnoDB,
 
  SUBPARTITION 8ed0736e_904c_11e6_a493_02f82aee6xxx_8 ENGINE = InnoDB,
 
  SUBPARTITION 8ed0736e_904c_11e6_a493_02f82aee6xxx_9 ENGINE = InnoDB,
 
  SUBPARTITION 8ed0736e_904c_11e6_a493_02f82aee6xxx_10 ENGINE = InnoDB,
 
  SUBPARTITION 8ed0736e_904c_11e6_a493_02f82aee6xxx_11 ENGINE = InnoDB,
 
  SUBPARTITION 8ed0736e_904c_11e6_a493_02f82aee6xxx_12 ENGINE = InnoDB,
 
  SUBPARTITION 8ed0736e_904c_11e6_a493_02f82aee6xxx_13 ENGINE = InnoDB,
 
  SUBPARTITION 8ed0736e_904c_11e6_a493_02f82aee6xxx_14 ENGINE = InnoDB,
 
  SUBPARTITION 8ed0736e_904c_11e6_a493_02f82aee6xxx_15 ENGINE = InnoDB,
 
  SUBPARTITION 8ed0736e_904c_11e6_a493_02f82aee6xxx_16 ENGINE = InnoDB,
 
  SUBPARTITION 8ed0736e_904c_11e6_a493_02f82aee6xxx_17 ENGINE = InnoDB,
 
  SUBPARTITION 8ed0736e_904c_11e6_a493_02f82aee6xxx_18 ENGINE = InnoDB,
 
  SUBPARTITION 8ed0736e_904c_11e6_a493_02f82aee6xxx_19 ENGINE = InnoDB,
 
  SUBPARTITION 8ed0736e_904c_11e6_a493_02f82aee6xxx_20 ENGINE = InnoDB,
 
  SUBPARTITION 8ed0736e_904c_11e6_a493_02f82aee6xxx_21 ENGINE = InnoDB,
 
  SUBPARTITION 8ed0736e_904c_11e6_a493_02f82aee6xxx_22 ENGINE = InnoDB,
 
  SUBPARTITION 8ed0736e_904c_11e6_a493_02f82aee6xxx_23 ENGINE = InnoDB),
 
 PARTITION 87d0736e_904c_11e6_a493_02f82aee6xxx VALUES IN ('87d0736e_904c_11e6_a493_02f82aee6xxx')
 
 (SUBPARTITION 87d0736e_904c_11e6_a493_02f82aee6xxx_0 ENGINE = InnoDB,
 
  SUBPARTITION 87d0736e_904c_11e6_a493_02f82aee6xxx_1 ENGINE = InnoDB,
 
  SUBPARTITION 87d0736e_904c_11e6_a493_02f82aee6xxx_2 ENGINE = InnoDB,
 
  SUBPARTITION 87d0736e_904c_11e6_a493_02f82aee6xxx_3 ENGINE = InnoDB,
 
  SUBPARTITION 87d0736e_904c_11e6_a493_02f82aee6xxx_4 ENGINE = InnoDB,
 
  SUBPARTITION 87d0736e_904c_11e6_a493_02f82aee6xxx_5 ENGINE = InnoDB,
 
  SUBPARTITION 87d0736e_904c_11e6_a493_02f82aee6xxx_6 ENGINE = InnoDB,
 
  SUBPARTITION 87d0736e_904c_11e6_a493_02f82aee6xxx_7 ENGINE = InnoDB,
 
  SUBPARTITION 87d0736e_904c_11e6_a493_02f82aee6xxx_8 ENGINE = InnoDB,
 
  SUBPARTITION 87d0736e_904c_11e6_a493_02f82aee6xxx_9 ENGINE = InnoDB,
 
  SUBPARTITION 87d0736e_904c_11e6_a493_02f82aee6xxx_10 ENGINE = InnoDB,
 
  SUBPARTITION 87d0736e_904c_11e6_a493_02f82aee6xxx_11 ENGINE = InnoDB,
 
  SUBPARTITION 87d0736e_904c_11e6_a493_02f82aee6xxx_12 ENGINE = InnoDB,
 
  SUBPARTITION 87d0736e_904c_11e6_a493_02f82aee6xxx_13 ENGINE = InnoDB,
 
  SUBPARTITION 87d0736e_904c_11e6_a493_02f82aee6xxx_14 ENGINE = InnoDB,
 
  SUBPARTITION 87d0736e_904c_11e6_a493_02f82aee6xxx_15 ENGINE = InnoDB,
 
  SUBPARTITION 87d0736e_904c_11e6_a493_02f82aee6xxx_16 ENGINE = InnoDB,
 
  SUBPARTITION 87d0736e_904c_11e6_a493_02f82aee6xxx_17 ENGINE = InnoDB,
 
  SUBPARTITION 87d0736e_904c_11e6_a493_02f82aee6xxx_18 ENGINE = InnoDB,
 
  SUBPARTITION 87d0736e_904c_11e6_a493_02f82aee6xxx_19 ENGINE = InnoDB,
 
  SUBPARTITION 87d0736e_904c_11e6_a493_02f82aee6xxx_20 ENGINE = InnoDB,
 
  SUBPARTITION 87d0736e_904c_11e6_a493_02f82aee6xxx_21 ENGINE = InnoDB,
 
  SUBPARTITION 87d0736e_904c_11e6_a493_02f82aee6xxx_22 ENGINE = InnoDB,
 
  SUBPARTITION 87d0736e_904c_11e6_a493_02f82aee6xxx_23 ENGINE = InnoDB),
 
 PARTITION 88ef2966_a03d_11e6_985e_06564ba3dec9 VALUES IN ('88ef2966-a03d-11e6-985e-06564ba3dec9')
 
 (SUBPARTITION 88ef2966_a03d_11e6_985e_06564ba3dec9_0 ENGINE = InnoDB,
 
  SUBPARTITION 88ef2966_a03d_11e6_985e_06564ba3dec9_1 ENGINE = InnoDB,
 
  SUBPARTITION 88ef2966_a03d_11e6_985e_06564ba3dec9_2 ENGINE = InnoDB,
 
  SUBPARTITION 88ef2966_a03d_11e6_985e_06564ba3dec9_3 ENGINE = InnoDB,
 
  SUBPARTITION 88ef2966_a03d_11e6_985e_06564ba3dec9_4 ENGINE = InnoDB,
 
  SUBPARTITION 88ef2966_a03d_11e6_985e_06564ba3dec9_5 ENGINE = InnoDB,
 
  SUBPARTITION 88ef2966_a03d_11e6_985e_06564ba3dec9_6 ENGINE = InnoDB,
 
  SUBPARTITION 88ef2966_a03d_11e6_985e_06564ba3dec9_7 ENGINE = InnoDB,
 
  SUBPARTITION 88ef2966_a03d_11e6_985e_06564ba3dec9_8 ENGINE = InnoDB,
 
  SUBPARTITION 88ef2966_a03d_11e6_985e_06564ba3dec9_9 ENGINE = InnoDB,
 
  SUBPARTITION 88ef2966_a03d_11e6_985e_06564ba3dec9_10 ENGINE = InnoDB,
 
  SUBPARTITION 88ef2966_a03d_11e6_985e_06564ba3dec9_11 ENGINE = InnoDB,
 
  SUBPARTITION 88ef2966_a03d_11e6_985e_06564ba3dec9_12 ENGINE = InnoDB,
 
  SUBPARTITION 88ef2966_a03d_11e6_985e_06564ba3dec9_13 ENGINE = InnoDB,
 
  SUBPARTITION 88ef2966_a03d_11e6_985e_06564ba3dec9_14 ENGINE = InnoDB,
 
  SUBPARTITION 88ef2966_a03d_11e6_985e_06564ba3dec9_15 ENGINE = InnoDB,
 
  SUBPARTITION 88ef2966_a03d_11e6_985e_06564ba3dec9_16 ENGINE = InnoDB,
 
  SUBPARTITION 88ef2966_a03d_11e6_985e_06564ba3dec9_17 ENGINE = InnoDB,
 
  SUBPARTITION 88ef2966_a03d_11e6_985e_06564ba3dec9_18 ENGINE = InnoDB,
 
  SUBPARTITION 88ef2966_a03d_11e6_985e_06564ba3dec9_19 ENGINE = InnoDB,
 
  SUBPARTITION 88ef2966_a03d_11e6_985e_06564ba3dec9_20 ENGINE = InnoDB,
 
  SUBPARTITION 88ef2966_a03d_11e6_985e_06564ba3dec9_21 ENGINE = InnoDB,
 
  SUBPARTITION 88ef2966_a03d_11e6_985e_06564ba3dec9_22 ENGINE = InnoDB,
 
  SUBPARTITION 88ef2966_a03d_11e6_985e_06564ba3dec9_23 ENGINE = InnoDB),
 
 PARTITION 063efccc_9768_11e6_8eb0_02ad3256f973 VALUES IN ('063efccc-9768-11e6-8eb0-02ad3256f973')
 
 (SUBPARTITION 063efccc_9768_11e6_8eb0_02ad3256f973_0 ENGINE = InnoDB,
 
  SUBPARTITION 063efccc_9768_11e6_8eb0_02ad3256f973_1 ENGINE = InnoDB,
 
  SUBPARTITION 063efccc_9768_11e6_8eb0_02ad3256f973_2 ENGINE = InnoDB,
 
  SUBPARTITION 063efccc_9768_11e6_8eb0_02ad3256f973_3 ENGINE = InnoDB,
 
  SUBPARTITION 063efccc_9768_11e6_8eb0_02ad3256f973_4 ENGINE = InnoDB,
 
  SUBPARTITION 063efccc_9768_11e6_8eb0_02ad3256f973_5 ENGINE = InnoDB,
 
  SUBPARTITION 063efccc_9768_11e6_8eb0_02ad3256f973_6 ENGINE = InnoDB,
 
  SUBPARTITION 063efccc_9768_11e6_8eb0_02ad3256f973_7 ENGINE = InnoDB,
 
  SUBPARTITION 063efccc_9768_11e6_8eb0_02ad3256f973_8 ENGINE = InnoDB,
 
  SUBPARTITION 063efccc_9768_11e6_8eb0_02ad3256f973_9 ENGINE = InnoDB,
 
  SUBPARTITION 063efccc_9768_11e6_8eb0_02ad3256f973_10 ENGINE = InnoDB,
 
  SUBPARTITION 063efccc_9768_11e6_8eb0_02ad3256f973_11 ENGINE = InnoDB,
 
  SUBPARTITION 063efccc_9768_11e6_8eb0_02ad3256f973_12 ENGINE = InnoDB,
 
  SUBPARTITION 063efccc_9768_11e6_8eb0_02ad3256f973_13 ENGINE = InnoDB,
 
  SUBPARTITION 063efccc_9768_11e6_8eb0_02ad3256f973_14 ENGINE = InnoDB,
 
  SUBPARTITION 063efccc_9768_11e6_8eb0_02ad3256f973_15 ENGINE = InnoDB,
 
  SUBPARTITION 063efccc_9768_11e6_8eb0_02ad3256f973_16 ENGINE = InnoDB,
 
  SUBPARTITION 063efccc_9768_11e6_8eb0_02ad3256f973_17 ENGINE = InnoDB,
 
  SUBPARTITION 063efccc_9768_11e6_8eb0_02ad3256f973_18 ENGINE = InnoDB,
 
  SUBPARTITION 063efccc_9768_11e6_8eb0_02ad3256f973_19 ENGINE = InnoDB,
 
  SUBPARTITION 063efccc_9768_11e6_8eb0_02ad3256f973_20 ENGINE = InnoDB,
 
  SUBPARTITION 063efccc_9768_11e6_8eb0_02ad3256f973_21 ENGINE = InnoDB,
 
  SUBPARTITION 063efccc_9768_11e6_8eb0_02ad3256f973_22 ENGINE = InnoDB,
 
  SUBPARTITION 063efccc_9768_11e6_8eb0_02ad3256f973_23 ENGINE = InnoDB),

and so on..

What I found was the partition name sometimes can fail based on the name itself.
For example.

mysql -h test -u xxxx -pxxxx social -e 'alter table a_radacct2 ADD PARTITION(    PARTITION 382e7854_a579_11e6_82d3_06af084b4d40 VALUES in ('\''382e7854-a579-11e6-82d3-06af084b4d40'\'')    ( 	SUBPARTITION 382e7854_a579_11e6_82d3_06af084b4d40_0, 	SUBPARTITION 382e7854_a579_11e6_82d3_06af084b4d40_1, 	SUBPARTITION 382e7854_a579_11e6_82d3_06af084b4d40_2, 	SUBPARTITION 382e7854_a579_11e6_82d3_06af084b4d40_3, 	SUBPARTITION 382e7854_a579_11e6_82d3_06af084b4d40_4, 	SUBPARTITION 382e7854_a579_11e6_82d3_06af084b4d40_5, 	SUBPARTITION 382e7854_a579_11e6_82d3_06af084b4d40_6, 	SUBPARTITION 382e7854_a579_11e6_82d3_06af084b4d40_7, 	SUBPARTITION 382e7854_a579_11e6_82d3_06af084b4d40_8, 	SUBPARTITION 382e7854_a579_11e6_82d3_06af084b4d40_9, 	SUBPARTITION 382e7854_a579_11e6_82d3_06af084b4d40_10, 	SUBPARTITION 382e7854_a579_11e6_82d3_06af084b4d40_11, 	SUBPARTITION 382e7854_a579_11e6_82d3_06af084b4d40_12, 	SUBPARTITION 382e7854_a579_11e6_82d3_06af084b4d40_13, 	SUBPARTITION 382e7854_a579_11e6_82d3_06af084b4d40_14, 	SUBPARTITION 382e7854_a579_11e6_82d3_06af084b4d40_15, 	SUBPARTITION 382e7854_a579_11e6_82d3_06af084b4d40_16, 	SUBPARTITION 382e7854_a579_11e6_82d3_06af084b4d40_17, 	SUBPARTITION 382e7854_a579_11e6_82d3_06af084b4d40_18, 	SUBPARTITION 382e7854_a579_11e6_82d3_06af084b4d40_19, 	SUBPARTITION 382e7854_a579_11e6_82d3_06af084b4d40_20, 	SUBPARTITION 382e7854_a579_11e6_82d3_06af084b4d40_21, 	SUBPARTITION 382e7854_a579_11e6_82d3_06af084b4d40_22, 	SUBPARTITION 382e7854_a579_11e6_82d3_06af084b4d40_23    ) );'


ERROR 1064 (42000) at line 1: 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 '382e7854_a579_11e6_82d3_06af084b4d40 VALUES in ('382e7854-a579-11e6-82d3-06af084' at line 1

What was interesting was with the partition name 382e7854_a579_11e6_82d3_06af084b4d40 and sub partition 382e7854_a579_11e6_82d3_06af084b4d40_0 etc
it will fail.
But with a82e7854_a579_11e6_82d3_06af084b4d40 (same but starting with letter 'a') works.
All these will fail

082e7854_a579_11e6_82d3_06af084b4d40
 
182e7854_a579_11e6_82d3_06af084b4d40
 
282e7854_a579_11e6_82d3_06af084b4d40
 
382e7854_a579_11e6_82d3_06af084b4d40
 
482e7854_a579_11e6_82d3_06af084b4d40
 
582e7854_a579_11e6_82d3_06af084b4d40
 
682e7854_a579_11e6_82d3_06af084b4d40
 
782e7854_a579_11e6_82d3_06af084b4d40
 
882e7854_a579_11e6_82d3_06af084b4d40
 
982e7854_a579_11e6_82d3_06af084b4d40

But starting with alphabets works for this number.

a82e7854_a579_11e6_82d3_06af084b4d40
 
b82e7854_a579_11e6_82d3_06af084b4d40
 
c82e7854_a579_11e6_82d3_06af084b4d40
 
d82e7854_a579_11e6_82d3_06af084b4d40
 
e82e7854_a579_11e6_82d3_06af084b4d40

For other numbers like in the table structure I shown above it works.

So it seems for some random starting part of the partition name it fails.

I'm not sure what sort of work around that I can do. Adding a prefix to the partition name eg (AC_082e7854_a579_11e6_82d3_06af084b4d40)
I'm not sure if this will create other random failures.

But my guess is that is a hash function of the partition name. Somehow, some combinations fail the has function and create a syntax error.

Thanks!

Regards,

Michael

 Comments   
Comment by Elena Stepanova [ 2016-11-12 ]

It's unrelated to partitioning, this is a general identifier problem.

Here is an ancient MySQL bug report where this behavior is somewhat explained:
http://bugs.mysql.com/bug.php?id=10779

you can quote identifiers, as a workaround.

The problem is that when you write 8981e56cce5d, MySQL starts parsing the string, and see
8981e56 and thinks it's a floating point number in the scientific notation. When it reads the next character 'c' it's a syntax error.

In general any name that starts with digits, and 'e' will be a problem.

The report was closed as not-a-bug, and the problem is still there.
As the comment says, a simple workaround which also ensures consistent and predictable behavior is quoting identifiers. MySQL has gone far enough allowing identifiers to start with a digit, I don't think it's necessary to further relax rules in this regard.

However, I think this problem should be addressed in documentation, now both MySQL manual MariaDB KB claim that

Identifier names may begin with a numeral, but can't only contain numerals unless quoted.

which, as we can see, is not true.

Comment by Ian Gilfillan [ 2016-11-14 ]

Added a note and example to the docs

Generated at Thu Feb 08 07:48:30 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.