Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.3.10, 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
Description
Our documentation says:
"For UNIQUE keys, PRIMARY KEYs and FOREIGN KEYs, you can specify a name for the constraint, using the CONSTRAINT keyword. That name will be used in error messages."
<https://mariadb.com/kb/en/library/create-table/>
But for primary keys this is not true, the constraint name is just silently ignored:
This CREATE succeeds without warning:
CREATE TABLE t1 (
|
id1 INT,
|
id2 INT,
|
CONSTRAINT `foo` PRIMARY KEY (id1),
|
CONSTRAINT `bar` UNIQUE KEY(id2)
|
);
|
When verifying with SHOW CREATE TABLE the constraint name
for the UNIQUE key is shown, but not that of the PRIMARY key:
MariaDB [test]> show create table t1\G
|
*************************** 1. row ***************************
|
Table: t1
|
Create Table: CREATE TABLE `t1` (
|
`id1` int(11) NOT NULL,
|
`id2` int(11) DEFAULT NULL,
|
PRIMARY KEY (`id1`),
|
UNIQUE KEY `bar` (`id2`)
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1
|
INFORMATION_SCHEMA.TABLE_CONSTRAINTS also does not show the
chosen name `foo` for the primary key:
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
|
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE |
|
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
|
| def | test | PRIMARY | test | t1 | PRIMARY KEY |
|
| def | test | bar | test | t1 | UNIQUE |
|
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
|
And the name is not shown in primary key related error messages either, contrary to what the knowledge base claims:
MariaDB [test]> insert into t1 values(1,1);
|
Query OK, 1 row affected (0.004 sec)
|
|
|
MariaDB [test]> insert into t1 values(2,1);
|
ERROR 1062 (23000): Duplicate entry '1' for key 'bar'
|
|
|
MariaDB [test]> insert into t1 values(1,2);
|
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
|
My main concern though is that there is no warning on CREATE about an unsupported feature being used and the chosen name being discarded in favour of the hardcoded "PRIMARY" name string.
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Assignee | Ian Gilfillan [ greenman ] |
| Status | Open [ 1 ] | Confirmed [ 10101 ] |
| Fix Version/s | 10.3 [ 22126 ] | |
| Fix Version/s | 10.4 [ 22408 ] | |
| Affects Version/s | 5.5 [ 15800 ] | |
| Affects Version/s | 10.0 [ 16000 ] | |
| Affects Version/s | 10.1 [ 16100 ] | |
| Affects Version/s | 10.2 [ 14601 ] | |
| Affects Version/s | 10.3 [ 22126 ] | |
| Affects Version/s | 10.4 [ 22408 ] | |
| Assignee | Sergei Golubchik [ serg ] | |
| Labels | upstream |
| Priority | Major [ 3 ] | Minor [ 4 ] |
| Assignee | Sergei Golubchik [ serg ] | Alexey Botchkov [ holyfoot ] |
| Priority | Minor [ 4 ] | Major [ 3 ] |
| Comment | [ customer issues needs this to be fixed since 4 months, hence moving the priority to critical to ensure we fix it sooner now. ] |
| Priority | Major [ 3 ] | Critical [ 2 ] |
| Status | Confirmed [ 10101 ] | In Progress [ 3 ] |
| Assignee | Alexey Botchkov [ holyfoot ] | Sergei Golubchik [ serg ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Priority | Critical [ 2 ] | Major [ 3 ] |
| Assignee | Sergei Golubchik [ serg ] | Alexey Botchkov [ holyfoot ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
This is good, just one detail — you cannot add new errors or warnings to 10.3, because 10.4 is already GA, and adding new warnings to 10.3 would cause 10.4 error numbers to change.
So, just use, say,
push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN,
|
ER_WRONG_NAME_FOR_INDEX,
|
"Name '%-.100s' ignored for PRIMARY key.", |
key->name.str);
|
| Comment | [ A comment with security level 'Developers' was removed. ] |
| Priority | Major [ 3 ] | Critical [ 2 ] |
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
| issue.field.resolutiondate | 2019-07-31 12:56:21.0 | 2019-07-31 12:56:21.942 |
| Fix Version/s | 10.3.17 [ 23411 ] | |
| Fix Version/s | 10.4.7 [ 23720 ] | |
| Fix Version/s | 10.3 [ 22126 ] | |
| Fix Version/s | 10.4 [ 22408 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | In Progress [ 3 ] | Closed [ 6 ] |
| Fix Version/s | 10.4.8 [ 23721 ] | |
| Fix Version/s | 10.3.18 [ 23719 ] | |
| Fix Version/s | 10.3.17 [ 23411 ] | |
| Fix Version/s | 10.4.7 [ 23720 ] |
| Workflow | MariaDB v3 [ 90244 ] | MariaDB v4 [ 155107 ] |
| Zendesk Related Tickets | 164209 |
I edited the CREATE TABLE page in the documentation to clarify that for PRIMARY KEY the name is accepted but ignored. This should be also explained at CONSTRAINT page https://mariadb.com/kb/en/library/constraint/ where the syntax does not even mention index_name can be specified for PRIMARY KEY
The server definitely should return warning so it is not documentation only problem.