| Bug #118476 | Unable to create partition with name '1e3fRQM2GhI' | ||
|---|---|---|---|
| Submitted: | 18 Jun 8:45 | Modified: | 18 Jun 9:01 |
| Reporter: | Li Yirong (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DDL | Severity: | S2 (Serious) |
| Version: | 8.0, 8.4 | OS: | Red Hat |
| Assigned to: | CPU Architecture: | x86 | |
| Tags: | Contribution | ||
[18 Jun 9:01]
MySQL Verification Team
Hello! Thank you for the report and feedback. regards, Umesh
[20 Jun 20:10]
MySQL Verification Team
create table t(a int) engine=innodb partition by list columns(a)( partition p1 values in (1) ); alter table t add partition (partition `1a1` values in (3)); -- works alter table t add partition (partition `111` values in (4)); -- works alter table t add partition (partition `1e1` values in (5)); -- syntax error
[26 Jun 13:16]
huahua xu
The sql_lex.cc could not parse `1e3fRQM2GhI` as an identifier by analyzing the source code.
```
case MY_LEX_NUMBER_IDENT: // number or ident which num-start
if (lip->yyGetLast() == '0') {
c = lip->yyGet();
if (c == 'x') {
while (my_isxdigit(cs, (c = lip->yyGet())))
;
if ((lip->yyLength() >= 3) && !ident_map[c]) {
/* skip '0x' */
yylval->lex_str = get_token(lip, 2, lip->yyLength() - 2);
return (HEX_NUM);
}
lip->yyUnget();
state = MY_LEX_IDENT_START;
break;
} else if (c == 'b') {
while ((c = lip->yyGet()) == '0' || c == '1')
;
if ((lip->yyLength() >= 3) && !ident_map[c]) {
/* Skip '0b' */
yylval->lex_str = get_token(lip, 2, lip->yyLength() - 2);
return (BIN_NUM);
}
lip->yyUnget();
state = MY_LEX_IDENT_START;
break;
}
lip->yyUnget();
}
while (my_isdigit(cs, (c = lip->yyGet())))
;
if (!ident_map[c]) { // Can't be identifier
state = MY_LEX_INT_OR_REAL;
break;
}
if (c == 'e' || c == 'E') {
// The following test is written this way to allow numbers of type 1e1
if (my_isdigit(cs, lip->yyPeek()) || (c = (lip->yyGet())) == '+' ||
c == '-') { // Allow 1E+10
if (my_isdigit(cs,
lip->yyPeek())) // Number must have digit after sign
{
lip->yySkip();
while (my_isdigit(cs, lip->yyGet()))
;
yylval->lex_str = get_token(lip, 0, lip->yyLength());
return (FLOAT_NUM);
}
}
lip->yyUnget();
}
```
Suggested fix: the strings starting with floating-point numbers should be required quotes in the method `require_quotes`.
[2 Jul 1:06]
Li Yirong
Fix this bug (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: 0001-Fix-partition-name-bug.patch (application/octet-stream, text), 19.21 KiB.
[2 Jul 14:47]
MySQL Verification Team
Thank you, for your Contribution. regards, Umesh
Description: Cannot create partition with name '1e3fRQM2GhI'. More seriously, adding a partition named '1e3fRQM2GhI' using the Alter statement in MySQL 5.7 will cause the table to be unable to be opened or dropped later. How to repeat: this SQL will fail in MySQL5.7 and MySQL8.0 CREATE TABLE `ep_xxx` ( `id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, `status` varchar(255) NOT NULL, `tenant_id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, `created_date` datetime NOT NULL, `project_id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, PRIMARY KEY (`id`,`project_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC PARTITION BY LIST COLUMNS(project_id) ( PARTITION `1HkZbSJOALI` VALUES IN ('1HkZbSJOALI') ENGINE = InnoDB, PARTITION `1cUPlKUTy1R` VALUES IN ('1cUPlKUTy1R') ENGINE = InnoDB ); this SQL will fail in MySQL 8.0 and will cause the table to be unable to be opened in MySQL5.7. CREATE TABLE `ep_xxxx` ( `id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, `status` varchar(255) NOT NULL, `tenant_id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, `created_date` datetime NOT NULL, `project_id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, PRIMARY KEY (`id`,`project_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC PARTITION BY LIST COLUMNS(project_id) (PARTITION `1HkZbSJOALI` VALUES IN ('1HkZbSJOALI') ENGINE = InnoDB, PARTITION `1cUPlKUTy1R` VALUES IN ('1cUPlKUTy1R') ENGINE = InnoDB); ALTER TABLE `ep_xxxx` ADD PARTITION (PARTITION `1e3fRQM2GhI` VALUES IN ('1e3fRQM2GhI') ENGINE = InnoDB); Suggested fix: 1. All partition names should be enclosed in backticks. 2. There exists a bug in function `require_quotes`. This function does not return non-zero even if the parser cannot correctly parse an identifier.