Check for missing primary or unique keys

[macbre]

• always have a primary/unique key in InnoDB table - the lack of primary (or unique) key can cause serious replication issues
• multiple primary/unique indexes can be problematic (INSERT .. ON DUPLICATE UPDATE)

INSERT ... ON DUPLICATE KEY UPDATE statements on tables with multiple primary or unique keys. When executed against a table that contains more than one primary or unique key, this statement is considered unsafe, being sensitive to the order in which the storage engine checks the keys, which is not deterministic, and on which the choice of rows updated by the MySQL Server depends.

An INSERT ... ON DUPLICATE KEY UPDATE statement against a table having more than one unique or primary key is marked as unsafe for statement-based replication beginning with MySQL 5.6.6. (Bug #11765650, Bug #58637)

https://dev.mysql.com/doc/refman/5.6/en/replication-rbr-safe-unsafe.html

An example

Due to a MySQL 5.6 bug InnoDB tables that do not have primary key defined has problems with replication of heavy transactions (with tens of thousands of rows involved).

We noticed this behavior during updateSpecialPages.php script run which performs heavy (DELETE + INSERT queries) operations on querycache table. This was quite visible when ruvlab wiki was processed - the maintenance script was performing a transaction with delete and insert of 75k rows causing huge lag on DB cluster.

Wikia/app#9863

CREATE TABLE `querycache` (
  `qc_type` varbinary(32) NOT NULL,
  `qc_value` int(10) unsigned NOT NULL DEFAULT '0',
  `qc_namespace` int(11) NOT NULL DEFAULT '0',
  `qc_title` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
  KEY `qc_type` (`qc_type`,`qc_value`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1