Adding UUID column to a Statement MySQL Replicated Database

UUIDs can be a very nice alternative to Auto IDs for keys in database tables but there is one problem with them when using statement based mysql replication.

Lets consider the scenario where you want to add a new UUID column to an existing table which you want to use as a key. To be able to use the key you need to initialise it and the obvious way is to do a:

Given the context of a table like:


CREATE TABLE MyTable (
id INT AUTO_INCREMENT PRIMARY KEY,
someField VARCHAR(255));

INSERT INTO MyTable(someField) VALUES ('My Data');


When you run your alter table command:


ALTER TABLE MyTable
ADD COLUMN urn VARCHAR(36);

UPDATE MyTable
SET urn = uuid();


(Ignoring the implications around storage cost of putting a UUID in a varchar field) The problem is that when the statement executes on the replica it will have a different value. E.G.

On the master you could end up with:

id	someField	urn
1	My Data	37ef6bac-d85b-11e3-a227-bc764e08452a

But on the slave you might have:

id	someField	urn
1	My Data	3216702f-d85b-11e3-a9c8-bc764e082848

The answer is to use a UUID initialisation that will assign the same values on the master and the slave, your id field can also be part of the answer to help you get going...


UPDATE BasketLineItem
SET urn = concat('00000000-d411-0000-0000-', lpad(hex(id), 12, '0'));


As long as you have less than 16 ^ 12 (2.8147498e+14) records then you you just migrated to UUID. If you don't have an auto incremented ID already you can also rely on database storage ordering which results in records being evaluated in the same order on the master and the slave (note that this was tested on MySql 5.5.37 with InnoDB storage - but it may not for you so make sure you check):


SET @counter = 0;
UPDATE BasketLineItem
SET id = concat('00000000-d411-0000-0000-', lpad(hex(@counter := @counter + 1), 12, '0'));


Enjoy your UUID keys!