primary key

This week I came across an old known issue reported in May 2010: Master/Slave Replication with binlog_format = ROW and tables without a Primary Key is a bad idea! Especially if these tables are huge.

Why this is a bad idea is described in the bug report #53375:

Most (relational) DBAs and DB application developers know the concept of a primary key ("PK") and what it is good for. However, much too often one still encounters table definitions without a PK. True, the relational theory based on sets does not need a PK, and all operations (insert, select, update, delete) can also be done on tables for which no PK was defined. If performance doesn't matter (or the data volume is small, a typical situation in tests), the lack of a PK does not immediately cause negative consequences.

Recently a customer has shown up with some deadlocks occurring frequently. They were of the following type (I have shortened the output a bit):

We recently had the case with one of our customers where we got externally generated random hash values (up to 70 bytes) and they were used as Primary Keys in InnoDB.

As we know, this is not a very good idea because the size of all secondary indexes becomes large and because a random hash value as a Primary Key gives us a bad locality of our rows in the table ¹.