Continuing Table refactoring & application version upgrades, Part I, we now discuss code & database upgrades which require DROP operations. As before, we break apart the upgrade process into sequential steps, each involving either the application or the database, but not both.
As I’ll show, DROP operations are significantly simpler than creation operations. Interestingly, it’s the same as in life.
DROP COLUMN
A column turns to be redundant, unused. Before it is dropped from the database, we must ensure no one is using it anymore. The steps are:
- App: V1 -> V2. Remove all references to column; make sure no queries use said column.
- DB: V1 -> V2 (possibly failover from M1 to M2), change is DROP COLUMN.
DROP INDEX
A possibly simpler case here. Why would you drop an index? Is it because you found out you never use it anymore? Then all you have to do is just drop it.
Or perhaps you don’t need the functionality the index supports anymore? Then first drop the functionality:
- (optional) App: V1 -> V2. Discard using functionality which relies on index.
- DB: V1 -> V2 (possibly failover from M1 to M2), change is DROP INDEX. Check out InnoDB Plugin here.
DROP UNIQUE INDEX
When using Master-Slave failover for table refactoring, we’re now removing a constraint from the slave. Since the master is more constrained than the slave, there is no problem here. It’s mostly the same as with a normal DROP INDEX, with a minor addition:
- (optional) App: V1 -> V2. Discard using functionality which relies on index.
- DB: V1 -> V2 (possibly failover from M1 to M2), change is DROP INDEX.
- (optional) App: V2 -> V3. Enable functionality that inserts duplicates.
DROP FOREIGN KEY
Again, we are removing a constraint.
- DB: V1 -> V2 (possibly failover from M1 to M2), change is DROP INDEX.
- (optional) App: V2 -> V3. Enable functionality that conflicts with removed constraint. I mean, if you really know what you are doing.
DROP TABLE
The very simple steps are:
- App: V1 -> V2. Make sure no reference to table is made.
- DB: V1 -> V2. Issue a DROP TABLE.
With ext3 dropping a large table is no less than a nightmare. Not only does the action take long time, it also locks down the table cache, which very quickly leads to having dozens of queries hang. xfs is a good alternative.
Conclusion
We looked at single table operations, coupled with application upgrades. By carefully looking at the process breakdown, multiple changes can be addressed with ease and safety. Not all operations are completely safe when used with replication failover. But they are mostly safe if you have some trust in your code.