2014/05/10

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:




idsomeFieldurn
1My Data37ef6bac-d85b-11e3-a227-bc764e08452a



But on the slave you might have:




idsomeFieldurn
1My Data3216702f-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!

ProtoStore UUID Storage Under MySQL

Over the last year or so using ProtoStore to handle object serialisation has saved me a huge amount of time and countless SQL related bugs. Obviously this is why technologies like EJB exist!

One thing that I have been thinking about for a while is the performance of the UUID based keying due to wasteful storage of the UUID as a string. I have finally found some good data on this UUID storage benchmark.

The headline seems to be that on insert you can get a 4X insert speed-up and a 10X select speed-up by using binary storage of the UUID when using InnoDB engine.

The question is can this be achieved without breaking anything in the way that the current system works? The short answer seems to be YES! By detecting the column type it should be OK to just add this as a 'feature'...

As an aside the protobuf jar is still only 39K but I am going to see if I can further reduce it's size, obviously it doesn't have the features of hibernate core but it does still solve my CRUD storage needs for a fraction of the 5MB cost....