[MDEV-31796] Phantom Keys Created: 2023-07-29  Updated: 2023-07-29  Resolved: 2023-07-29

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table, Data Definition - Create Table
Affects Version/s: 11.0.2
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Marcos Ramos Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: CONSTRAINT, UNIQUE, foreign-keys, primary_key
Environment:

Arch-based Linux


 Description   

There is a primary key that I cannot drop, and a unique key that I cannot see.

Describe Table

MariaDB [My_Data_Base]> describe Languages;
 
+-----------+----------+------+-----+---------+-------+
 
| Field     | Type     | Null | Key | Default | Extra |
 
+-----------+----------+------+-----+---------+-------+
 
| Name      | char(49) | YES  |     | NULL    |       |
 
| ISO_639_1 | char(2)  | NO   | PRI | NULL    |       |
 
+-----------+----------+------+-----+---------+-------+
 
2 rows in set (0,001 sec)

Cannot drop Primary Key

MariaDB [My_Data_Base]> ALTER TABLE Languages DROP PRIMARY KEY;
 
ERROR 1091 (42000): Can't DROP INDEX `PRIMARY`; check that it exists

A total mess

MariaDB [My_Data_Base]> select constraint_name, constraint_type from information_schema.table_constraints where table_name = 'Languages';
 
+-----------------+-----------------+
 
| constraint_name | constraint_type |
 
+-----------------+-----------------+
 
| Languages_UN    | UNIQUE          |
 
+-----------------+-----------------+
 
1 row in set (0,001 sec)


MariaDB [My_Data_Base]> describe Languages;
 
+-----------+----------+------+-----+---------+-------+
 
| Field     | Type     | Null | Key | Default | Extra |
 
+-----------+----------+------+-----+---------+-------+
 
| Name      | char(49) | YES  |     | NULL    |       |
 
| ISO_639_1 | char(2)  | NO   | PRI | NULL    |       |
 
+-----------+----------+------+-----+---------+-------+
 
2 rows in set (0,001 sec)

I can see what shouldn't be there and I cannot see what should be there. I also cannot interact with what shouldn't be there.

I have tried recreating the primary key and dropping it, but it didn't work.

 Comments   
Comment by Elena Stepanova [ 2023-07-29 ]

Please paste the output of SHOW CREATE TABLE Languages.

Comment by Marcos Ramos [ 2023-07-29 ] 

+-----------+------------------------+
 
| Table     | Create Table                                                                                                                                                                                                                                                                                       |
 
+-----------+------------------------+
 
| Languages | CREATE TABLE `Languages` (
 
  `Name` char(49) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
 
  `ISO_639_1` char(2) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
 
  UNIQUE KEY `Languages_UN` (`ISO_639_1`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin |
 
+-----------+------------------------+

This made me realize, that the CHASTER and COLLATE values were different from the rest of my database. I have corrected that, but the bug persists. Actually, this is happening with every constraint I create be it UNIQUE, PRIMARY or FOREIGN. If I create them and delete them, they stay as phantom constraints in the description of a table.

Comment by Elena Stepanova [ 2023-07-29 ]

Right. So, from SHOW CREATE above it is hopefully obvious what is happening.
Your table has a unique key named Languages_UN on the column ISO_639_1, and does not have a primary key.

When you query information_schema.table_constraints, you get information about this key.
However, DESCRIBE command does not return a list of keys, it only describes columns in a table (it is a shortcut of SHOW COLUMNS command), and Key there is not a name of the key, it's an attribute which is given to a column depending on the key structure in the table.

The way the attribute is set is described both in MariaDB KB and MySQL manual. The relevant part there is

A UNIQUE index may be displayed as PRI if it cannot contain NULL values and there is no PRIMARY KEY in the table.

But

If I create them and delete them, they stay as phantom constraints in the description of a table.

If you drop all unique keys from the table and a column is still shown as PRI or UNI in the DESCRIBE output, it is surely a problem. Can you paste an unabridged client output of it happening?

Comment by Marcos Ramos [ 2023-07-29 ]

Right, that matches now. Thank you! And I have tried dropping it, and then the fields clear.

MariaDB [my_db]> select constraint_name, constraint_type from information_schema.table_constraints where table_name = 'Languages';
 
+-----------------+-----------------+
 
| constraint_name | constraint_type |
 
+-----------------+-----------------+
 
| PRIMARY         | PRIMARY KEY     |
 
+-----------------+-----------------+
 
1 row in set (0,001 sec)
 
 
 
MariaDB [my_db]> describe Languages;
 
+-----------+----------+------+-----+---------+-------+
 
| Field     | Type     | Null | Key | Default | Extra |
 
+-----------+----------+------+-----+---------+-------+
 
| Name      | char(49) | YES  |     | NULL    |       |
 
| ISO_639_1 | char(2)  | NO   | PRI | NULL    |       |
 
+-----------+----------+------+-----+---------+-------+
 
2 rows in set (0,001 sec)

But the description of 'Pupils' still shows that a foreign key is there for 'Learning' that is not really listed as a constrain for this table.

MariaDB [my_db]> select constraint_name, constraint_type from information_schema.table_constraints where table_name = 'Pupils';
 
+-----------------------+-----------------+
 
| constraint_name       | constraint_type |
 
+-----------------------+-----------------+
 
| PRIMARY               | PRIMARY KEY     |
 
| Pupils_Pupil_ID_Match | FOREIGN KEY     |
 
+-----------------------+-----------------+
 
2 rows in set (0,001 sec)
 
 
 
MariaDB [my_db]> describe Pupils;
 
+----------+------------------+------+-----+---------+-------+
 
| Field    | Type             | Null | Key | Default | Extra |
 
+----------+------------------+------+-----+---------+-------+
 
| Pupil_ID | int(11) unsigned | NO   | PRI | NULL    |       |
 
| Group    | varchar(50)      | NO   |     | NULL    |       |
 
| Learning | char(2)          | NO   | MUL | NULL    |       |
 
+----------+------------------+------+-----+---------+-------+

Comment by Elena Stepanova [ 2023-07-29 ]

Yes, as you can see from the same pages above, foreign keys don't play any role in defining the key attribute for a column. It is a different type of constraint altogether.

Comment by Marcos Ramos [ 2023-07-29 ]

Right, but in this case, they key attribute 'MUL' stays even though there is nothing that would justify its presence. Am I understanding the situation correctly? And it prevents me from creating a constraint that would justify its presence.

And I have to manually drop the index. This is despite the fact that the trigger for creating the index is creating the foreign key. I think this was automatic in previous versions of MariaDB. I never saw myself having to do this before.

Comment by Georg Richter [ 2023-07-29 ]

The problem is, that metadata flags are wrong. Flags contain PRI (mary) key, if a primary key was previously defined and replaced by a unique key (also happens with MySQL):

CREATE OR REPLACE TABLE t1 (a int primary key);
 
ALTER TABLE t1 DROP PRIMARY KEY, ADD UNIQUE INDEX(a);
 
DESCRIBE t1;
 
+-------+---------+------+-----+---------+-------+
 
| Field | Type    | Null | Key | Default | Extra |
 
+-------+---------+------+-----+---------+-------+
 
| a     | int(11) | NO   | PRI | NULL    |       |
 
+-------+---------+------+-----+---------+-------+
 
SHOW CREATE TABLE t1\G
 
*************************** 1. row ***************************
 
       Table: t1
 
Create Table: CREATE TABLE `t1` (
 
  `a` int(11) NOT NULL,
 
  UNIQUE KEY `a` (`a`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
 
CREATE OR REPLACE TABLE t2(a int unique key);
 
DESCRIBE t2;
 
+-------+---------+------+-----+---------+-------+
 
| Field | Type    | Null | Key | Default | Extra |
 
+-------+---------+------+-----+---------+-------+
 
| a     | int(11) | YES  | UNI | NULL    |       |
 
+-------+---------+------+-----+---------+-------+
 
SHOW CREATE TABLE t2\G
 
*************************** 1. row ***************************
 
       Table: t2
 
Create Table: CREATE TABLE `t2` (
 
  `a` int(11) DEFAULT NULL,
 
  UNIQUE KEY `a` (`a`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

Comment by Elena Stepanova [ 2023-07-29 ]

Once again, same quote from the same manual.

A UNIQUE index may be displayed as PRI if it cannot contain NULL values and there is no PRIMARY KEY in the table.

Your t1 doesn't have a primary key, has a unique key on a, and a is a non-null column, so it fits the description above, thus PRI.
In t2 the column a is null-able, so it doesn't fit the decription above, thus UNI.

Generated at Thu Feb 08 10:26:32 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.