How to handle unique indexes with MySQL master master replication
<p>how do you handle UNIQUE indexes in a master-master active-active replication environment?</p>

<p>SQL schema example:</p>

<pre><code>CALLS
  id INTEGER PRIMARY AUTO_INCREMENT
  caller_id INTEGER + INDEX
  start_date datetime
  end_date datetime
  ...

CALLERS
  id INTEGER PRIMARY AUTO_INCREMENT
  phone VARCHAR UNIQUE
  country_id CHAR(2)
  city_id INTEGER
  ...
</code></pre>

<p>In a heavy loaded (lot of inserts) environment, requests can be sent to both master (for perf, high availability, 0 downtime schema changes reasons).</p>

<p>if 2 CALLERS records are created at the same time (yes it can) on both masters:</p>

<ul>
<li>In a replication mode=row the replication breaks with a DUPLICATE event</li>
<li><p>In a replication mode=row with a ON DUPLICATE KEY UPDATE the replication breaks too (since the ON DUPLICATE KEY UPDATE is not applied on the opposite master)</p></li>
<li><p>In a replication mode=statement the replication breaks too</p></li>
<li><p>In a replication mode=statement with ON DUPLICATE KEY UPDATE the replication keep working... but we can have inconsistent records: </p>

<p>if 2 CALLERS are created at the same time on both masters, </p>

<ul>
<li>master1.CALLERS.id=1 masters1.CALLS.caller_id = 1</li>
<li>master2.CALLERS.id=2 master2.CALLS.caller_id = 2</li>
<li>on both master, once replicated, the replicated CALLS records will have a caller_id columns pointing on a non-existent records</li>
</ul></li>
</ul>

<p>What are the best solutions to handle this really simple case?</p>

<p>I thought to solutions like this, but they have big drawbacks</p>

<ul>
<li><p>don't use UNIQUE indexe in these cases  by merging CALLERS and CALLS table, but this is a huge functional loss... and keep using ROW based replication. but: </p>

<ul>
<li>this is not applicable to all UNIQUE indexes cases!</li>
</ul></li>
<li><p>don't use AUTO_INCREMENT (don't let MySQL choose the id values), but:</p>

<ul>
<li>how will you generate alternative unique values without SPOF and without performances degradations? (something that could fit in a 4 Bytes column)</li>
<li>have to change all the application (more than hundred tables in the real one)</li>
</ul></li>
</ul>