A novel constraint specifies,
a number of columns as distinctive it identifies.
It’s glad solely when no two rows retailer
the identical non-null values at its core.A major key constraint is a singular one that can say
PRIMARY KEY in its outlined approach.
It’s glad solely when rows unfold,
and none could also be null within the columns they maintain.
The ISO/IEC 9075 Distinctive and Main Keys Poem
Pep Pla & ChatGPT
What’s in a Main Key?
The ISO commonplace defines major keys as distinctive keys that may’t retailer null values. There is no such thing as a distinction between a major key and a singular key on columns that may’t comprise null values. There is just one restriction: you may outline only one major key per desk, whereas you’ll have any variety of distinctive keys with or with out null values.
Main keys are extraordinarily helpful, and I’m not going to go over the advantages of major keys right here. Nevertheless, in some instances, they’re greater than useful; they’re required to take care of efficiency. MySQL replication is one such case, as a result of it’s logical replication, even when utilizing the row-based binary log format. Main keys are additionally a requirement for Percona XtraDB Cluster and Group Replication for a similar causes.
MySQL 8.0.13 launched the variable *sql_require_primary_key* to implement the presence of major keys on all tables created. Nevertheless, this variable doesn’t confirm if tables that existed beforehand had been created with a major key. The parameter additionally requires you to specify the first key, not a singular key on non-nullable columns.
Belief Distinctive Keys not bearing Null Values
However what occurs when you will have tables which have a singular key on non-nullable columns? Nicely, the issue right here is that, even when these tables have a non-nullable distinctive key, since there isn’t a precise major key outlined, actions like operating pt-online-schema-change, gh-ost, or simply “create desk like” will fail. You’ll need to set sql_require_primary_key to off on the periods that run these instructions.
Wouldn’t or not it’s nice if we might inform MySQL to rename that non-nullable distinctive key right into a major key? It could. However what if I inform you that that key’s the first key for InnoDB? For InnoDB, every desk is a particular index referred to as the Clustered Index. The keys of that index are the first keys, but when InnoDB is unable to discover a major key, it would use the “first UNIQUE index with all key columns outlined as NOT NULL.” So, in case your desk has a singular index and all of the columns of the index are usually not null, InnoDB will use that index as the first key.
However, though InnoDB makes use of that index as the first key, MySQL doesn’t acknowledge it and considers the desk as one and not using a major key.
The Sting
In principle, the one resolution is to rebuild the desk to alter from a non-nullable distinctive key to a major key. However we are able to trick MySQL into migrating an InnoDB desk from NNUK to PK. I’ll present you ways.
Following we now have the desk that we plan emigrate:
|
CREATE TABLE `sbtest4` ( `id` int NOT NULL, `okay` int NOT NULL DEFAULT ‘0’, `c` char(120) NOT NULL DEFAULT ”, `pad` char(60) NOT NULL DEFAULT ”, `id2` int NOT NULL, UNIQUE KEY `id` (`id`), UNIQUE KEY `id2` (`id2`), KEY `k_1` (`okay`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED |
As you may see, there isn’t a major key, however InnoDB ought to be utilizing certainly one of my distinctive keys as the first key. Let’s attempt to discover out which one:
|
SELECT i.title FROM information_schema.innodb_indexes i INNER JOIN information_schema.innodb_tables t USING (table_id) WHERE kind = 3 AND t.title = Concat(DATABASE(), ‘/’, ‘sbtest4’);
+——+ | title | +——+ | id | +——+ 1 row in set (0.00 sec) |
What the earlier question does is use which index in sbtest4 is of kind 3, or which index is the clustered index, the index that InnoDB considers adequate to turn out to be a major key. On this case, the index is id on the column with the identical title.
Now, we’ll create a short lived desk with the identical construction as sbtest4 and exchange the id index with the first key.
|
CREATE TABLE sbtest4_new LIKE sbtest4; Question OK, 0 rows affected (0.02 sec)
ALTER TABLE sbtest4_new DROP KEY id, ADD PRIMARY KEY (id); Question OK, 0 rows affected (0.03 sec) Data: 0 Duplicates: 0 Warnings: 0 |
Now, we now have a desk with the identical construction as the unique desk, however with a major key as an alternative of a singular index. Now it’s time to discard its tablespace.
|
ALTER TABLE sbtest4_new DISCARD TABLESPACE; |
And flush the earlier desk for export. This step blocks sbtest4, so this process should happen throughout a upkeep window.
|
FLUSH TABLE sbtest4 FOR EXPORT; |
We’ll execute the subsequent operation within the working system, however DO NOT CLOSE this MySQL connection as sbtest4 is locked, and we wish it to stay like this.
|
cd /var/lib/mysql/sbtest mv sbtest4.ibd sbtest4_new.ibd |
What we’ve achieved now’s to rename the IBD file from sbtest4 to sbtest4_new. Now, we might import the tablespace into sbtest4_new in a brand new connection.
|
USE sbtest; ALTER TABLE sbtest4_new IMPORT TABLESPACE; Question OK, 0 rows affected, 1 warning (0.30 sec) SHOW WARNINGS; +———+——+—————————————————————————————————————————————————-+ | Stage | Code | Message | +———+——+—————————————————————————————————————————————————-+ | Warning | 1810 | InnoDB: IO Learn error: (2, No such file or listing) Error opening ‘./sbtest/sbtest4_new.cfg’, will try to import with out schema verification | +———+——+—————————————————————————————————————————————————-+ 1 row in set (0.00 sec) |
This operation will return a warning, however we should always have imported the desk appropriately. You’ll be able to confirm it by accessing the desk.
|
mysql> SELECT * FROM sbtest4_new LIMIT 10; +—-+———-+————————————————————————————————————————-+————————————————————-+—–+ | id | okay | c | pad | id2 | +—-+———-+————————————————————————————————————————-+————————————————————-+—–+ | 1 | 2141642 | 83868641912–28773972837–60736120486–75162659906–27563526494–20381887404–41576422241–93426793964–56405065102–33518432330 | 67847967377–48000963322–62604785301–91415491898–96926520291 | 0 | … +—-+———-+————————————————————————————————————————-+————————————————————-+—–+ 10 rows in set (0.00 sec) |
Now it’s time to unlock sbtest4 and drop it, because it has no knowledge file. To finish the migration, we rename the brand new desk to sbtest4.
|
FLUSH TABLE sbtest4 FOR EXPORT; Question OK, 0 rows affected (0.01 sec)
mysql> UNLOCK TABLES; Question OK, 0 rows affected (0.00 sec)
mysql> DROP TABLE sbtest4; Question OK, 0 rows affected (0.01 sec)
mysql> RENAME TABLE sbtest4_new TO sbtest4; Question OK, 0 rows affected (0.01 sec) |
By operating these steps, sbtest4 now has a major key as an alternative of a singular key on non-nullable columns.
Disclaimer
Two last notes:
- That is an unsupported process. Take a look at it together with your MySQL model and by no means carry out a upkeep operation like this and not using a correct database backup.
- In a replication setup, it’s essential repeat the
flush tables for exportand the file rename on every duplicate earlier than operating thealter desk import tablespace. Another choice is to carry out the operation in a rolling vogue withsql_log_binset to 0 to keep away from writing the modifications to the binlog.
