Table refactoring & application version upgrades, Part I

A developer’s major concern is: How do I do application & database upgrades with minimal downtime? How do I synchronize between a DB’s version upgrade and an application’s version upgrade?

I will break down the discussion into types of database refactoring operations, and I will limit to single table refactoring. The discussion will try to understand the need for refactoring and will dictate the steps towards a successful upgrade.

Reader prerequisites

I will assume MySQL to be the underlying database. To take a major component out of the equation: we may need to deal with very large tables, for which an ALTER command may take long hours. I will assume familiarity with Master-Master (Active-Passive) replication, with possible use of MMM for MySQL. When I describe “Failover from M1 to M2“, I mean “Make the ALTER changes on M2 (passive), then switch your application from M1 to M2 (change of IPs, VIP, etc.), promoting M2 to active position, then apply same changes on M1 (now passive) or completely rebuild it”.

Phew, a one sentence description of M-M usage…

I also assume the reader’s understanding that a table’s schema can be different on master & slave, which is the basis for the “use replication for refactoring” trick. But it cannot be too different, or, to be precise, the two schemata must both support the ongoing queries for the table.

A full discussion of the above is beyond the scope of this post.

Types of refactoring needs

As I limit this discussion to single table refactoring,we can look at major refactoring operations and their impact on application & upgrades. We will discuss ADD/DROP COLUMN, ADD/DROP INDEX, ADD/DROP UNIQUE INDEX, ADD/DROP FOREIGN KEY, ADD/DROP TABLE.

We will assume the database and application are both in Version #1 (V1), and need to be upgraded to V2 or greater.

ADD INDEX

Starting with the easier actions. Why would you add an index? Either:

1. There is some existing query which can be optimized by the new query
2. Or there is some new functionality which issues a query for which the new index is required.

Adding an index is an easy action in that the table’s data does not really change.

In case #1, all you need to do is to add the new index (if the table is large, fail over from M1 to M2). There is no application upgrade, so all that happens is that the database upgrades V1 -> V2.

In case #2, the database must be prepared with new schema before the new functionality/query is introduced (since it depends on the existence of the index). The steps, therefore, are:

1. DB: V1 -> V2 (possibly failover from M1 to M2)
2. (Sometime later) App: V1 -> V2. Application will issue queries which utilize the new index.

The application does not have to be upgraded at the same instant the DB gets upgraded. In fact, we’ll see that this is a typical scenario: we can separate upgrades into smaller steps, which allow for time lapse. One could work out steps 1 & 2 together, but that would take an extra effort.

ADD COLUMN

This must be one of the most common table schema upgrades: a new property is needed on the application side. It must be supported by the database. Perhaps a new field in some Java Object, with Hibernate mapping that field onto a new column. Or maybe the new column is there for purpose of de-normalization.

This is also a more complicated task. Let’s look at the required steps:

1. DB: V1 -> V2 (possibly failover from M1 to M2), change is ADD COLUMN.
2. App: V1 -> V2. Change is: provide column value for newly INSERTed rows.
3. If needed, retroactively update column values for all pre-existing rows.
4. App: V2 -> V3. Application begins to use (read, SELECT) new column.

The above procedure assumes that the new column must have some calculated value. A 10-million rows table must now be updated, to have the correct values filled in. So we ask of the application to start filling in data for new rows, which makes the invalid row set static. We can just take a “from row” and a “to row” and fill in the missing column’s value for those rows. Only when all rows contain valid values can we let the application start using that row. This makes for two application upgrades.

If you’re content with just a static DEFAULT value, then step 3 can be skipped, and step 4 can be merged with step 2.

ADD UNIQUE INDEX

This is an altogether different case than the normal ADD INDEX, even though they may seem similar. And the case is particularly different when using Master-Slave failover for rebuilding the table.

Consider the case where we add a UNIQUE INDEX on a slave. Some INSERT query executes on the master, successfully, and is logged to the binary log. The slave picks it up, tries to execute it, to find that it fails on a DUPLICATE KEY error.

The UNIQUE INDEX is a constraint, and it makes the slave more constrained than the master. This is a delicate situation. Here how to (mostly) work it out:

1. App: V1 -> V2. Change INSERT queries on relevant table to INSERT IGNORE or REPLACE queries, whichever is more appropriate.
2. DB: V1 -> V2 (possibly failover from M1 to M2), change is ADD UNIQUE KEY (and while at it, a tip: are you aware of ALTER IGNORE TABLE?)

The change of query ensures that the query will succeed on the slave (either by silently doing nothing or by actually replacing content). It also means that the slave can now have different data than the master. Of course, it you trust your application to never INSERT duplicates, you can sleep better.

We do not handle UPDATE statements here.

ADD CONSTRAINT FOREIGN KEY

As with ADD UNIQUE INDEX, there is a new constraint here. A slave becomes more constrained than the master. But we now have to make sure INSERT, UPDATE and DELETE statements all go peacefully (well, it also depends on the type of ON DELETE and ON UPDATE property of the FK).

The steps would be:

1. DB: V1 -> V2 (possibly failover from M1 to M2), change is ADD CONSTRAINT FOREIGN KEY.

And then cross your fingers or have trust in your application. If the table is small enough, one does not have to use replication to do the refactoring, and life is simpler. Just execute the ALTER on the active master, and continue with your life.

CREATE TABLE

This is a simple case, since the table is new. The steps are:

1. DB: V1 -> V2 (no need to use slaves here)
2. App: V1 -> V2. Application will start using new table.

Conslusion

Having such steps formalized help with development management and database management. It makes clear what is expected of the application, and what is expected of the database. The breaking down of these operations into sequential steps allows us to work more slowly; make preparation work; work within our own working hours; get a chance to see the family.

In this post we took a look at “creation” refactoring changes. New columns, new keys, new constraints. In the next part of this article, we’ll discuss DROP operations.