Monday, March 12, 2012

Sort KEY and CONSTRAINT in mysqldump for data migration scripts with gawk

No matter what you do you have to persist the data and your storage metadata or schema will change as the project evolves.

Managing the changes can be tricky and challenging costing your team endless hours spent reconciling what is in production against local and integration environments in order to push model changes to your staging environment.

As we currently generate most of our model directly from JPA as I posted before there is a serious need to see what is different from current proposed model and existing database schema.

In MySQL you have mysqldump tool however there is an important feature request which has not be addressed yet at the time of this writing.

Basically KEY and CONSTRAINT are not ordered and so as you modify the schema your diff will be alerting about false positives. You end up spending simply too much time inspecting differences.

I have decided to spend some time to make sure I get a consistent ordered mysqldump that my team can use to reconcile data from production with any other environment. That is why I coded a gawk script to sort mysqldump output.

Here is how to use the script to understand how unsorted your local mysql DATABASE is:
$ mysqldump --routines --no-data -h localhost -u USERNAME -pPASSWORD DATABASE > local.sql
$ ./mysqldump_sort.gawk < local.sql > local_sorted.sql
$ diff local.sql local_sorted.sql


gaRex said...

Pls update your script to something like

1. Commas always last
2. KEYS and CONSTRAINTS are in same array

Nestor Urquiza said...

gaRex thanks for your feedback. I have corrected the commas issues in github. In fact I even added sorting for keys, unique and primary as well. I see no value on having all constraints in just one array though. I believe it makes sense to be able to sort , PRIMARY, UNIQUE, KEY, CONSTRAINT as we please. I personally try to keep the same order mysqldump uses which is precisely that order. After all we are just trying to address an issue with the current mysqldump tool.