Mysql – thesql – can a replica have a different primary key than the source

MySQLprimary-keyreplication

I have a table with PRIMARY KEY (`id`) and I want to change it to PRIMARY KEY (`username`, `id`). These columns are defined as:

  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL DEFAULT '',

This table is within a primary/secondary MySQL topology with binary row replication. Can I get away with taking the replica offline, changing the primary key, and reconnect it to the source without changing the source? For clarity, only the primary key index would be different between the source/replica. All other columns/order of columns would be the same.

Best Answer

Depends on the Storage Engine

InnoDB

The auto_increment column will behave but the Replica will become very bloated. Why ???

For any given non-unique index in an InnoDB table, each entry in s secondary index will have a copy of the PRIMARY KEY as part of the entry.

If your InnoDB table has PRIMARY KEY (id) (which is 8 bytes), then each secondary index entry will have the id attached.

Now if you change the PRIMARY KEY to (username,id), then each secondary index entry will have the (username,id) attached. That can be up to 28 (8 + 20) bytes to each entry.

This will make INSERTs slower on the Replica.

This also allow duplicates of username.

You are better off creating a UNIQUE KEY on username.

MyISAM

For those still using MyISAM, PLEASE DO NOT ATTEMPT THIS UNDER ANY CIRCUMSTANCES !!! Why ???

Any MyISAM table that has a compound key that involves an auto_increment column with increment based on the presences of the other columns

EXAMPLE from the MySQL Documentation

CREATE TABLE animals (
    grp ENUM('fish','mammal','bird') NOT NULL,
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id)
) ENGINE=MyISAM;
INSERT INTO animals (grp,name) VALUES
    ('mammal','dog'),('mammal','cat'),
    ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ('bird','ostrich');
SELECT * FROM animals ORDER BY grp,id;

which prints out

+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+

Do you see how id increments on a common value when grp already exists in the table ?

The Source and the Replica will be so horribly out-of-sync. There will be no way to know which row in the Source belongs to a row in the Replica, and vice versa.

This makes changing the PRIMARY KEY of a MyISAM table totally dangerous for a Replica.

I have discussed this subject over 6 years ago

UPDATE 2019-04-20 08:52 EDT

Of course, it is possible but BEWARE !!! : My post was just about the dangers of doing so. In the event of problems, MyISAM would be an absolute nightmare to unravel. InnoDB would be somewhat OK but you are messing with the PRIMARY KEY.

Back on Aug 14, 2012, I have recommended changing the storage engine and adding an index on a replica (Can I have an InnoDB master and MyISAM slaves with Full-Text for searching?). I still recommend do something like this for older versions of MySQL (When FULLTEXT indexes for InnoDB did not exist yet). The PRIMARY KEY can be a different story.

I can easily see the EXPLAIN plan of a SELECT being radically different on a replica from the EXPLAIN plan of its source given a completely different order of PRIMARY KEY columns.

You could never use a tool like pt-table-checksum and pt-table-sync to reconcile differences should the table become out of sync or replication breaking for unrelated reasons. Why ??? Both tools rely on the PRIMARY KEY columns. Even with plain MySQL, just running CHECKSUM TABLE table_name; would always be different between Source and Replica.

Your hoping for better performance runs the risk of MySQL interpreting the table as not being the same (because it simply won't be the same).

If you looking for the username to be unique, FORGET IT !!! Having (username,id) as a PRIMARY KEY allows for it. If you already have a UNIQUE KEY on username on the Source, then the Replica already has the key it needs.

SUGGESTION

Make sure the Source has a UNIQUE KEY on the username.

Don't do anything to the PRIMARY KEY on the Replica. If you want to the Source to have it, then do it on the Source so it transfers to the Replica.

EPILOGUE

Giving you a simple it's possible or it's not possible answer would be absolutely unfair to you given the bullet-riddled problems it would produce down the road for anyone who tries.

I would never recommend changing the PRIMARY KEY of a Replica regardless of storage engine or DBMS. If you are still willing to accept the responsibility of changing the PRIMARY KEY on a Replica after reading this, Godspeed, Spiderman !!!

Related Question