I am now in an airport, waiting for one of the four flights that will bring me to Percona Live Santa Clara 2018.  This is a good time to write some details about my tutorial on parallel replication.  But before talking about Percona Live, I will share thoughts on MySQL/MariaDB bugs that caught my attention in the last weeks/months (Valeriy: you clearly have an influence on me).

MySQL/MariaDB

TL;DR: unless you know what you are doing, you should always have a primary key on your tables when replicating in RBR (and maybe even all the time).

TL;DR2: MariaDB 10.1 has an interesting way to protect against missing a primary key (innodb_force_primary_key) but it could be improved.

A few weeks ago, I was called off hours because replication delay on all the slaves from a replication chain

A funny replication breakage kept me at the office longer than expected today (Friday 13 is not kind with me).

So question of the day: can you guess what the below UPDATE statement does (or what is wrong with it)?

> CREATE TABLE test_jfg ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, status ENUM('a','b') NOT NULL DEFAULT 'a', txt TEXT); Query OK, 0

The other day, I was discussing high availability (HA) with other people.  The discussion was going in the direction of, as the application was only using INSERT ... ON DUPLICATE KEY UPDATE (ODKU) and traffic could be replayed, promoting a slave in case of the failure of the master was easy: replaying traffic would make all the slaves converge back to a common state.  It looks as this could work but it might not be as simple.  Before going into the details, let's take a step back and look into HA in general.

tl;dr: be careful when using row-based replication when replicating ODKU as slaves will stop replicating if a row is present on the master and missing on slave (or vice-versa).

High Availability by Promoting a Slave as the new Master
Let's suppose we are working …

Whither your rollback plan?

MySQL 5.6 upgrades are in full swing these days and knowing how to safely upgrade from MySQL 5.5 to 5.6 is important. When upgrading a replication environment, it’s important that you can build a migration plan that safely allows for your upgrade with minimal risk — rollback is often a very important component to this.

For many people this means upgrading slaves first and then the master.  The strategy of an older master replicating to a newer slave is well known and has been supported in MySQL replication for a very long time.  To be specific:  you can have a MySQL 5.6 slave of a 5.5 master and this should work fine until you upgrade your master and/or promote one of the slaves to be the master.

However, there are those of us who like to live on the edge and do …

It is everywhere in the world of MySQL that if your replication is broken because an event caused a duplicate key or a row was not found and it cannot be updated or deleted, then you can use ‘

STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;

 ’ and be done with it. In some cases this is fine and you can repair the offending row or statements later on. But what if the statement is part of a multi-statement transaction? Well, then it becomes more interesting, because skipping the offending statement will cause the whole transaction to be skipped. This is well documented in the manual by the way. So here’s a quick example.

3 rows on the master:

master> select * from t;
+----+-----+
| id | pid |
+----+-----+
|  1 |   1 |
|  2 |   2 |
|  3 |   3 |
+----+-----+
3 rows in set (0.00 …