Automated DB Updater Release First Release

Initial version of Automated DB Updater Release ADU

With this blog I’m releasing the intial version of a python utility to provide automated db updates across various environments for different components.

The code for this utility is hosted on github
https://github.com/sandy724/ADU

You can clone the read only copy of this codebase by url given below
https://github.com/sandy724/ADU.git

To understand the basic idea about this utility go thorugh this blog
http://sandy4blogs.blogspot.in/2013/07/automated-db-updater.html

How to use this utility
Checkout the code at some directory, add the path of this directory in PYTHONPATH environment variable
Create a database with a script’s metadata table with given below ddl

CREATE TABLE `script_metadata` (
  `name` varchar(100) DEFAULT NOT NULL,
  `version` int(11) DEFAULT NOT NULL,
  `executed` tinyint(1) NOT NULL DEFAULT ‘0’,
  `env` varchar(30) DEFAULT NOT NULL,
  `releas` varchar(30) DEFAULT NOT NULL,
  `component` varchar(30) DEFAULT NOT NULL
)
Create a database.properties, containing connection properties of each environment database

[common_db]
dbHost=localhost
dbPort=3306
dbUser=root
dbPwd=root
db=test
 
 
[env1]
dbHost=localhost
dbPort=3306
dbUser=root
dbPwd=root
db=test

Here common_db represents connection to database which will contain metadata of scripts for monitoring

Now execute the pythong utility
Copy the client(updateDB.py) to directory of your choice, make sure that property configration file should also be at this directory
python updateDB.py -f -r –env

Build & Release Challenges : Manual DB Updates Part 2

Previos

This blog was supposed to be about the new system, I thought of building to solve the problem that I discussed in my previous blog. Well for your disappointment this blog will be not about that, the reason is scope of the problem changed. In this blog I’ll be discussing about the new scope and how discussion moved forward about it and what is the current state, which means that I’m still not able to solve this problem & suggestions are welcome :).

I’ll again state the problem which was very simple enough, “database updates were not automated in non prod environments as same db scripts were modified during development“. You need to refer to previous blog for more details about this problem. To solve this problem I came up with incremental db update approach, as per this approach all new modifications will be done as a new sql update which means that let’s say you had a file 1.sql, if you need to do any modification a new file 1′.sql should be committed. In this way the system don’t have to track the files, it just have to maintain what all files got executed, the new files which needs to be executed and execute the new files only. This solution can work in a normal setup very well, in fact in my last assignment I was using this approach only to have automated db updates across all environments.

The incremental db updates can’t be run in current setup, the reason for that is we have very huge database order of 100GB, you can easily imagine that we can’t afford to run same script with slight modifications i.e first script adding a column of size 20 then another script to change it’s size to 40 finally renaming it to some other name. Instead of that a single script should be created after consolidating all these scripts.

The first solution that came to my mind after this new issue emerged, during non prod deployments we should already have database dump of previous release, more preferably cold dump. During deployment 3 steps would be performed first load previous release db dump, run all the consolidated scripts which will be consolidated & do the code deployment. Initially this solution looked fine enough but QA team raised concern as loading previous release dump meant that all the test data  they have created on the QA server would be lost and I was at the beginning of square :).

Another solution that could be implemented  was to have rollback script for each & every script committed. This convention will have an advantage of supporting incremental update i.e whenever a script will be updated first it’s corresponding rollback script will be executed & then the script can be executed. This solution has it’s own challenges the first challenge was it’s really difficult to write rollback script of each & every script, another issues was you have to carefully manage the script files so that there will be no tight coupling between them as execution of rollback of one script will impact another script. Third issue although less significant is that you have to deal with data loss

We could also have used a hybrid approach that is combination of incremental & full db updates. Till QA phase we can use incremental db update mechanism in which all new script modifications are done as a new script and then they can be executed incrementally but for staging & production deployment db updates will be done as a full update which requires a human intervention i.e consolidation of scripts. This approach had 2 challenges the first & foremost was that it had manual intervention & second major issue was that we were duplicating the db scripts.

So these were the few approaches that we thought of & none was able to solve our problem completely, so we are still struggling with fully automating the db update process. Again any suggestions are most welcome 🙂

Automated Database Update Or Rollback

One of the important step during release is doing database update and rollback in case something goes wrong, usually people perform this operation manually. In this blog I’ll talk about how we can automate this process by following some convention.

Here I’m taking mysql database as an example we can have same conventions for other databases also

Convention to manage rollback/updates of a release

  • Each project codebase at it’s root will have a folder database_scripts
  • The database_scripts folder will contain folder for each release i.e Release1_1, Release2_0…
  • The database scripts release folder will in turn contains two folders update & rollback which will contain updates & rollbacks scripts for a release.

Automating the rollback/update

  • The update folder will have a source input file FileSequencer.txt. This file will point to all the update scripts in correct order that needs to be executed for the release
  • In the similar manner rollback folder will have a source input file FileSequencer.txt. This file will point to all the rollback scripts in correct order that needs to be executed for the release
  • At last we will have a utility shell script, this script will take db details and execute all the scripts referred in FileSequencer.txt using mysql command